Database/ORACLE
SQL 명령어
타카스 류지
2015. 2. 24. 11:52
* 검색 - 오라클 명령어에서 대소문자를 가리지는 않지만, 검색시에는 구분함.
1) select
a) distinct - 중복행제거
b) * - all
c) alias - 해당 컬럼에 다른 이름 부여 : as키워드를 사용하면 명시적이고
따옴표로 묶으면, 대소문자 구분 가능
d) 컬럼값중 숫자형 데이터는 산술연산 가능
e) || - 연결연산자
f) 작은따옴표 - 컬럼에 있는 데이터는 아니지만, 따옴표사이의 내용이 모든 행에 표시된다. 연결연산자와
함께 사용하는 경우가 일반적
2) where - from 절 다음에 와야 한다.
a) and - AND 연산일 경우에는 거짓이 앞에 있는 게 좋고, OR 연산자일 경우에는 참이 앞에 있는 것이 좋다
b) or
c) between ~ and ~ - 이상, 이하의 개념임. 미만, 보다일경우는 부등호 기호 사용
d) in ( 'x', 'y', 'z' )
e) is null / not - NOT이 들어간 SQL 연산자는 사용을 하지 않는 것이 좋다. (ex. NOT IN, IS NOT NULL)
null값으로 들어간 데이터를 확인하는 용도로 쓰인다.
f) like
i) %
ii) _
iii) like 'aa\%%' escape '\'
3) order by 칼럼이름 [asc|desc]
4) group by - 테이블보다 작은 그룹으로 묶어서 값을 얻으려 할 때 사용
a) ROLLUP (a, b, c) - grouping sets( (a, b, c), (a, b), (a), ())
b) CUBE (a, b, c) - grouping sets( (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())
5) having - group by 의 수행결과에 조건을 부여해서 값을 얻으려 할 때 사용
6) grouping sets
a) UNION - 합집합 : 중복된 데이터를 제거하고 데이터를 반환
b) INTERSECT - 교집합 : 상위 select절과 하위 select 절의 중복된 데이터를 반환
c) MINUS - 차집합 : 중복된 데이터를 제거
d) UNION ALL - 합집합+교집합
7) subquery - select 문 안에 삽입된 select 문
* 기타 명령어
1) $cls - 화면정리
2) set time on | off - 시계표시
3) set timing on | off - 시간체크
* Join - 하나 이상의 테이블로부터 자료를 검색하기 위하여 조인을 사용합니다.
일반적으로 PK와 FK를 사용하여 조인하는 경우가 대부분이지만, 논리적인 값들의
연관성으로 조인하는 경우도 있습니다.
1) where절에 조인 조건을 기술한다.
2) 명확성을 위해 또는 효율성을 위해, 컬럼이름앞에 테이블 이름을 붙인다.
3) 테이블을 조인하는 경우, 같은 컬럼이 존재하면, 컬럼명앞에 테이블명을 붙인다.
a) Equijoin
b) Non-Equijoin
c) Outerjoin
d) Selfjoin
* Equijoin
조인조건에서 = 을 사용하여, 값들이 일치하는 경우에 사용하는 조인을 말한다.
단순 조인, 또는 내부 조인이라고도 부른다.
ex) select ename, dname, loc from emp e, dept d where e.deptno=d.deptno;
Select tab1.col1 [, tab2.col2, ......]
From tab1, tab2
Where tab1.col1 = tab2.col2;
* Non-Equijoin
테이블사이에 관련성이 없을 경우의 조인을 말한다. 비동등연산자를 사용 ex) between ~ and ~
ex) select ename, grade from emp, salgrade where sal between losal and hisal;
* Outerjoin
조인을 실행하다보면, 조인 조건에 맞지않아서, 화면에 나타나진 않지만, 조건만 맞으면,
바로 출력될 수 있는 그런 행들을, 조인 조건과 무관하게 화면에 나타내기위한 조인을 말한다.
ex) select ename, e.deptno, dname, loc from emp e, dept d where e.deptno(+)=d.deptno;
salgrade테이블의 deptno 40번은, emp테이블에서 접근할 때, 조인조건여하에 따라 대개는
나타나지 않는다. 이런 40번 행 조차도, 출력을 원하는 경우에 사용한다.
* Selfjoin
같은 테이블에 대해 두개의 별칭을 작성하여, 테이블을 구분함으로서, From 절에 두개의
테이블을 사용하는 것과 같은 조건을 만든다.
ex) select e1.ename, e2.ename "Manager" from emp e1, emp e2 where e1.mgr=e2.empno;
* SubQuery
다른 select 문에 내장된 또 하나의 select문이다.
1) 서브쿼리는 괄호로 묶어야 한다.
2) 연산자의 오른쪽에 위치해야한다.
3) 서브쿼리에서는 order by 절을 포함하기 힘들다.
ex)
i) select avg(sal) from emp;
ii) select * from emp where sal > 2133;
iii) select * from emp where sal > (select avg(sal) from emp);
* DB 조작명령어 구분
1) DML - Data Manipulation Language
- SELECT : 검색
- INSERT : 삽입
ex) insert into 테이블이름 [(컬럼1 [, 컬럼2, .....])] values (값1 [, 값2, ......]);
insert into 목적테이블이름 select 목적테이블컬럼1 [, 목적테이블컬럼2, ......] from 소스테이블;
insert all
[when 조건1 then] into 테이블이름1
[when 조건2 then] into 테이블이름2
[else into 디폴트테이블명]
select 목적테이블컬럼1 [, 목적테이블컬럼2, ......] from 소스테이블;
- UPDATE : 갱신
ex) update 테이블이름 set 컬럼1=값1 [, 컬럼2=값2, ......] [where 조건];
- DELETE : 삭제
ex) delete [from] 테이블이름 [where 조건];
- COMMIT : 확인
- ROLLBACK : 취소
롤백시킬 위치조절 : (savepoint 임의의이름) v.s. (savepoint to 임의의이름)
2) DDL - Data Definision Language
- CREATER : 생성
ex) create table 테이블이름 ( 컬럼명1 데이타타입 [, 컬럼명2 데이타타입, ......] );
create table 테이블이름 ( 컬럼명1 데이타타입 constraint 테이블명_pk_컬럼명 PRIMARY KEY );
create table 테이블이름 ( 컬럼명1 데이타타입 default 디폴트값 );
ex) create table emp ( hiredate date default sysdate );
a) DATA TYPE
varchar2(문자데이터길이1~4000)
number(p, s) - 전체 p자리중, 소수점이하 s자리 : 생략가능
date - 7byte, bc 4712/01/01 ~ ad 9999 12/31
clob - 대용량 문자, 1~4Gbyte
blob - 대용량 기타, 1~4Gbyte
b) 하위질의를 통한 테이블복사
ex) create table 테이블이름 as select 컬럼명1 [, 컬럼명2, ......] from 존재하는테이블이름(=기존테이블)
create table 테이블이름 (컬럼명1 [, 컬럼명2, ......]) as select (컬럼명1 [, 컬럼명2, ......]) from 기존테이블
- ALTER : 변경
ex)
a) 삽입 : alter table 테이블이름 add 컬럼이름 varchar2(10);
b) 삭제 : alter table 테이블이름 drop column 컬럼이름;
c) 변경 : alter table 테이블이름 modify 컬럼이름 number;
alter table 테이블이름 rename column 원래이름 to 바꿀이름;
ex) 제약조건없이 이미 생성한 테이블에 제약조건 추가, 삭제
a) 추가 : alter table 테이블이름 add constraint 제약조건이름_pk PRIMARY KEY(컬럼이름);
b) 삭제 : alter table 테이블이름 drop constraint 제약조건이름_pk;
- DROP : 삭제
ex) drop table 테이블이름;
a) 휴지통 비우기 : purge recyclebin;
b) 휴지통 내용보기 : show recyclebin;
c) 테이블 복원하기 : flashback table 테이블명 to before drop;
- RENAME : 기존테이블이름을 변경
ex) rename 원래테이블이름 to 바꿀테이블이름;
- TRUNCATE : 테이블절단 - 특정컬럼에 해당하지않고, 테이블의 레코드전체를 날려버림.
ex) truncate teble 컬럼이름;
3) DCL - Data Control Language
- 권한 확인 a) select role from dba_roles;
@경로\role.sql
b) select * from user_tab_privs_recd;
- Role 확인
ex) select * from user_role_privs;
- GRANT : 권한부여
a) 세션생성(=접속허용) - grant create session to 계정;
b) 테이블생성 - grant create table to 사용자;
c) 특정 사용자에게 특정 테이블 권한 부여;
ex) conn scott/tiger
grant select on 테이블이름 to 계정;
- REVOKE : 권한취소
a) 접속권한취소 - revoke create session from 계정;
b) 특정사용자에게 특정 테이블 권한 취소
ex) revoke select on 테이블이름 from 계정;
* 제약조건
* TRANSACTION - 어떤 하나의 작업단위. ex) 은행계좌이체
* Schema 예제
1) select * from SCOTT.emp; (= select * from emp;) - 자신이 소유한 객체를 언급시에는 객체명앞의
스키마를 생략할 수 있음.
2) 하지만, 다른 계정에서 emp테이블을 보려고 하면, 자신의 소유가 아니기때문에, 오류가 발생
한다. 따라서, 자신의 소유가 아닌 객체(테이블등)를 보려면, 그 객체를 소유한 사용자명을 반
드시 기술해주어야 한다.
3) 그렇지만, 일단은 타 계정의 객체들을 보려면, 권한을 먼저 얻어야한다. select 권한을 hr계정에서
scott계정에 부여해, scott계정에서 hr계정의 테이블들을 select로 열어보자.
a) conn hr/hr
b) grant SELECT on HR.jobs to SCOTT;
c) conn scott/tiger
d) select * from user_tab_privs_recd; - 권한확인
e) select * from HR.job;
* Synonym (동의어) - 다른 계정의 객체에 접근할 때, [사용자명.테이블명]으로 표현하는데, 긴 사용자명+
테이블명에 별칭을 부여하여 간단하게 접근할 수 있도록 하는 방법
* Data Dictionary - 오라클 DB내에 저장된 모든 객체(거시기) 정보를 제공합니다.
System View - (데이터 딕셔너리 뷰)와 (동적성능 뷰) 로 이루어져있으며, 데이타 딕셔너리에 대한
내용조회를 가능하게 해 준다.
Schema - 객체를 소유한 사용자명.
1) 데이터 딕셔너리 뷰 - 해당 DB에 존재하는 오브젝트 및 기타 정보에 대한 내용을 조회.
USER_ - 현재 접속한 사용자가 소유한 객체의 정보를 제공
- 사용자가 생성한 스키마의 정보를 볼 수 있다. (유저의 시각)
ALL_ - 사용자가 접근 가능한 모든 스키마(설계도) 의 정보를 제공
- 권한을 부여받았기에 가능하다. (유저의 시각을 넓혀줌)
DBA_ - 데이타베이스 관리를 위한 정보를 제공
- 모든 사용자의 스키마 정보를 볼 수 있다.(관리자의 시각)
2) 동적성능뷰 - 메모리 상태 및 현재 세션에 대한 정보를 확인할 수 있음.
데이터 딕셔너리 뷰는 DBA_, ALL_, USER_ 로 뷰이름이 시작되는 반면, 동적성능뷰는
v$로 뷰 이름이 시작된다.
3) select table_name from user_tables;
select table_name from all_tables;
system으로 접속해야만, 접근 가능한 DBA_
select table_name from dba_tables;
4) 자주 사용되는 딕셔너리 뷰와 약칭
테이블 검색 : select table_name from user_tables;
시퀀스 검색 : select sequence_name, min_value, max_value, increment_by, cycle_flag from user_sequences;
인덱스 검색 : select index_name, table_name from user_indexes;
인덱스 컬럼 검색 : select index_name, table_name, column_name from user_ind_columns;
뷰 검색 : select * from user_views;
테이블 검색 : select * from tab;
시퀀스 검색 : select * from seq;
5) 데이터 사전에 접근
전체 탐색 : select count(table_name) from dictionary;
특정 테이블 검색 : select count(table_name) from dictionary where table_name like 'USER%' and table_name like '%TABLE%';
테이블 설명 참조 : select comments from dictionary where table_name = 'USER_TABLES';
6) 활용
사용자 정보 열람 : select username, default_tablespace, temporary_tablespace from dba_users where username = &username;
사용자 테이블 정보 열람 : select table_name, tablespace_name, partitioned, nested from user_tables where table_name = &table_name;
제약조건 열람 : select constraint_name, constraint_type from user_constraints where table_name = &table_name;
View 정보 열람 : select text from user_views where view_name = &view_name;
* SEQUENCE
ex) create sequence 시퀀스이름
start with n - 시작값
increment by n - 증가값
maxvalue 99999 - 최대값
cycle | nocycle - 순환여부
cache | nocache - 디폴트는 20;
시퀀스이름.nextval | currval
수정 - alter sequence 시퀀스이름
start with n - 시작값
increment by n - 증가값
maxvalue 99999 - 최대값
cycle | nocycle - 순환여부
cache | nocache - 디폴트는 20;
삭제 - drop sequenct 시퀀스이름
* VIEW - 자체의 데이터는 없지만, 테이블의 데이터를 보거나, 변경할 수 있는 창이다.
테이블의 데이터를 선택적으로 보여줄 수 있어, 접근제한을 가할 수 있다.
복잡한 질의어를 통해 결과를 구하고, 반복할 수 있다.
1) simple view
하나의 테이블에서만 데이터가 유래된다.
DML수행가능
2) complex view
다중테이블에서 데이터가 유래된다.
DML수행불가
ex) create view 뷰이름 as 서브쿼리 [with check option] [with read only];
초록글씨 부분이 없다면, 그 이하의 문장은 일반 select 문장이다.
a) with check option - 조건변경 불가
b) with read only - DML수행 불가
3) 인라인 뷰 - 안쪽, 바깥쪽으로 쿼리문이 나누어질 때, 안 쪽 쿼리문을 별칭을 붙여 view처럼
사용한다.
ex) 문제 7번
ex) select rownum, ename, sal from ( select ename, sal from emp order by sal desc ) where rownum <= 5;
* INDEX
1) 목적 - 쿼리를 빠르게 하기 위해서
2) 내부구조 - B*트리 구조
3) 생성
ex) create index 인덱스이름 on 테이블이름(컬럼이름);
4) 인덱스 재생성
ex) alter index 인덱스이름 rebuild;
* INDEX 사용조건
1) 비추
a) 질의문의 결과가 전체 행의 10 ~15% 이상을 읽어들일 것으로 예상된다.
b) 테이블이 자주 갱신된다. 인덱스 유지에 상대적으로 시간이 많이 걸린다.
c) 테이블이 작다.
d) 해당 컬럼이 질의의 조건으로 자주 사용되지 않는다.
2) 추천
a) 테이블에 레코드가 많다.
b) 조건절에 해당 컬럼이 자주 사용된다.
c) 검색결과가 2~4%정도일 경우
d) join에 자주 사용되는 컬럼
e) null을 포함하는 컬럼이 많은 경우
* 테이블 스페이스
오라클에서 DB의 데이터를 저장하는 가장 큰 개념.
ex) select tablespace_name, status from dba_tablespaces;
select tablespace_name, file_name, bytes from dba_data_files;
* 계정
1) 사용자 계정 생성
a) 계정생성 - create user ID identified by PW;
b) 권한부여 - grant connect,resource to ID;(
2) 계정 비번바꾸기
alter user <계정이름> identified by <사용할 암호>;
3) 계정삭제하기
drop user ID;
4) 계정 잠그기/풀기
alter user <잠긴계정> account lock / unlock;
5) 모든 계정 확인하기
select * from all_users;
6) 세션 수 확인
select schemaname, count(*) from v$session group by schemaname;
7) 각 세션별 구분
select username, status, sid, serial# from v$session;
8) 특정 세션 kill
alter system kill session 'sid번호, serial#번호';
* 계정 만들기 종합 - 테이블스페이스까지 응용
1) 계정 생성 - ssr/ssr
ex) create user ssr
identified by ssr
2) 권한 부여 - 접속권한 포함하여 기본권한 role
ex) grant connect, resource to ssr;
3) 테이블 스페이스 생성 - (10M, 경로 : c:\tablespace) : 단지 ssr이름의 테이블스페이스가
만들어졌을 뿐, 아직 ssr계정과는
아무 상관없음.
ex) create tablespace ssr
datafile 'c:\tablespace\ssr.dbf' size 10m;
4) ssr계정의 디폴트 테이블 스페이스를 ssr테이블스페이스로 변경
ex) alter user ssr default tablespace ssr;
5) ssr 테이블스페이스의 용량제한을 푼다.
ex) alter user ssr quota unlimited on ssr;
6) 테이블생성
7) 시퀀스생성
* 문자코드
1) ASCII 문자코드 128문자 (7bit) - 영어
2) ISO-8859-9 256문자 (8bit) - 서유럽언어
3) 한국표준협회의 공업표준문자집합 - a) KSC-5601 : 2바이트로 완성형한글을 표현
아스키문자에 대한 고려없음.
b) KSC-5636 : 아스키문자를 고려한 표현
c) EUC-KR : 확장유닉스코드(Extended UNIX Code)
라고 하며, 영어는 b)방식으로,
한글은 a)방식으로 조합해 사용.
KSC-5861
4) 유니코드 - 두가지 인코딩 방식이 있음
a) UTF-8 : 아스키문자를 만나면, 1바이트로 인코딩하고, 다른 문자는 2바이트 또는
그 이상의 바이트로 처리하는 방식을 따름. 한글의 경우는 3바이크로 처리.
아스키 문자 체계와 호환이 가능해서, 인터넷상에서 기본적인 인코딩으로 환영받음.
XML에서도 별도의 언급이 없으면, 디폴트로 UTF-8 인코딩으로 간주함.
b) UTF-16 : 아스키건 뭐건 모두 2바이트로 처리한다. 65,356개를 처리할 수 있다.
5) XML선언에서 인코딩 방식을 "euc-kr"로 지정하는 이유 - 운영체제와 관련이 있다.
한글 운영체제의 인코딩방식이 MS의 경우(MS949 : EUC-KR과 동일방식) MS949방식을 채택
하고 있으며, 이는 euc-kr( or KSC-5601)과 동일한 인코딩방식이다. 즉 한글을 2바이트로 처리
한다는 소리다. 그런데, XML의 디폴트인 UTF-8방식은 한글을 3바이트로 처리하기때문에,
처음엔 좀 되는 듯 싶다가도, 나중에, 아귀가 안 맞으면서, 오류를 일으키게된다.
따라서, 비록, UTF-8이 인터넷의 기본 인코딩으로 환영받고 있고, XML의 디폴트도 UTF-8 이지만,
운영체제에서 파일을 저장할 때, euc-kr일 것으로 예상하는 상황인지라, 두 인코딩방식이 일치하지
않는다면, 오류가 발생하기때문에, 저장시에도 명시적으로 euc-kr, 즉 운영체제가 지원하는 한글
인코딩 방식을 따르겠다고, 선언해주어야한다.
1) select
a) distinct - 중복행제거
b) * - all
c) alias - 해당 컬럼에 다른 이름 부여 : as키워드를 사용하면 명시적이고
따옴표로 묶으면, 대소문자 구분 가능
d) 컬럼값중 숫자형 데이터는 산술연산 가능
e) || - 연결연산자
f) 작은따옴표 - 컬럼에 있는 데이터는 아니지만, 따옴표사이의 내용이 모든 행에 표시된다. 연결연산자와
함께 사용하는 경우가 일반적
2) where - from 절 다음에 와야 한다.
a) and - AND 연산일 경우에는 거짓이 앞에 있는 게 좋고, OR 연산자일 경우에는 참이 앞에 있는 것이 좋다
b) or
c) between ~ and ~ - 이상, 이하의 개념임. 미만, 보다일경우는 부등호 기호 사용
d) in ( 'x', 'y', 'z' )
e) is null / not - NOT이 들어간 SQL 연산자는 사용을 하지 않는 것이 좋다. (ex. NOT IN, IS NOT NULL)
null값으로 들어간 데이터를 확인하는 용도로 쓰인다.
f) like
i) %
ii) _
iii) like 'aa\%%' escape '\'
3) order by 칼럼이름 [asc|desc]
4) group by - 테이블보다 작은 그룹으로 묶어서 값을 얻으려 할 때 사용
a) ROLLUP (a, b, c) - grouping sets( (a, b, c), (a, b), (a), ())
b) CUBE (a, b, c) - grouping sets( (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())
5) having - group by 의 수행결과에 조건을 부여해서 값을 얻으려 할 때 사용
6) grouping sets
a) UNION - 합집합 : 중복된 데이터를 제거하고 데이터를 반환
b) INTERSECT - 교집합 : 상위 select절과 하위 select 절의 중복된 데이터를 반환
c) MINUS - 차집합 : 중복된 데이터를 제거
d) UNION ALL - 합집합+교집합
7) subquery - select 문 안에 삽입된 select 문
* 기타 명령어
1) $cls - 화면정리
2) set time on | off - 시계표시
3) set timing on | off - 시간체크
* Join - 하나 이상의 테이블로부터 자료를 검색하기 위하여 조인을 사용합니다.
일반적으로 PK와 FK를 사용하여 조인하는 경우가 대부분이지만, 논리적인 값들의
연관성으로 조인하는 경우도 있습니다.
1) where절에 조인 조건을 기술한다.
2) 명확성을 위해 또는 효율성을 위해, 컬럼이름앞에 테이블 이름을 붙인다.
3) 테이블을 조인하는 경우, 같은 컬럼이 존재하면, 컬럼명앞에 테이블명을 붙인다.
a) Equijoin
b) Non-Equijoin
c) Outerjoin
d) Selfjoin
* Equijoin
조인조건에서 = 을 사용하여, 값들이 일치하는 경우에 사용하는 조인을 말한다.
단순 조인, 또는 내부 조인이라고도 부른다.
ex) select ename, dname, loc from emp e, dept d where e.deptno=d.deptno;
Select tab1.col1 [, tab2.col2, ......]
From tab1, tab2
Where tab1.col1 = tab2.col2;
* Non-Equijoin
테이블사이에 관련성이 없을 경우의 조인을 말한다. 비동등연산자를 사용 ex) between ~ and ~
ex) select ename, grade from emp, salgrade where sal between losal and hisal;
* Outerjoin
조인을 실행하다보면, 조인 조건에 맞지않아서, 화면에 나타나진 않지만, 조건만 맞으면,
바로 출력될 수 있는 그런 행들을, 조인 조건과 무관하게 화면에 나타내기위한 조인을 말한다.
ex) select ename, e.deptno, dname, loc from emp e, dept d where e.deptno(+)=d.deptno;
salgrade테이블의 deptno 40번은, emp테이블에서 접근할 때, 조인조건여하에 따라 대개는
나타나지 않는다. 이런 40번 행 조차도, 출력을 원하는 경우에 사용한다.
* Selfjoin
같은 테이블에 대해 두개의 별칭을 작성하여, 테이블을 구분함으로서, From 절에 두개의
테이블을 사용하는 것과 같은 조건을 만든다.
ex) select e1.ename, e2.ename "Manager" from emp e1, emp e2 where e1.mgr=e2.empno;
* SubQuery
다른 select 문에 내장된 또 하나의 select문이다.
1) 서브쿼리는 괄호로 묶어야 한다.
2) 연산자의 오른쪽에 위치해야한다.
3) 서브쿼리에서는 order by 절을 포함하기 힘들다.
ex)
i) select avg(sal) from emp;
ii) select * from emp where sal > 2133;
iii) select * from emp where sal > (select avg(sal) from emp);
* DB 조작명령어 구분
1) DML - Data Manipulation Language
- SELECT : 검색
- INSERT : 삽입
ex) insert into 테이블이름 [(컬럼1 [, 컬럼2, .....])] values (값1 [, 값2, ......]);
insert into 목적테이블이름 select 목적테이블컬럼1 [, 목적테이블컬럼2, ......] from 소스테이블;
insert all
[when 조건1 then] into 테이블이름1
[when 조건2 then] into 테이블이름2
[else into 디폴트테이블명]
select 목적테이블컬럼1 [, 목적테이블컬럼2, ......] from 소스테이블;
- UPDATE : 갱신
ex) update 테이블이름 set 컬럼1=값1 [, 컬럼2=값2, ......] [where 조건];
- DELETE : 삭제
ex) delete [from] 테이블이름 [where 조건];
- COMMIT : 확인
- ROLLBACK : 취소
롤백시킬 위치조절 : (savepoint 임의의이름) v.s. (savepoint to 임의의이름)
2) DDL - Data Definision Language
- CREATER : 생성
ex) create table 테이블이름 ( 컬럼명1 데이타타입 [, 컬럼명2 데이타타입, ......] );
create table 테이블이름 ( 컬럼명1 데이타타입 constraint 테이블명_pk_컬럼명 PRIMARY KEY );
create table 테이블이름 ( 컬럼명1 데이타타입 default 디폴트값 );
ex) create table emp ( hiredate date default sysdate );
a) DATA TYPE
varchar2(문자데이터길이1~4000)
number(p, s) - 전체 p자리중, 소수점이하 s자리 : 생략가능
date - 7byte, bc 4712/01/01 ~ ad 9999 12/31
clob - 대용량 문자, 1~4Gbyte
blob - 대용량 기타, 1~4Gbyte
b) 하위질의를 통한 테이블복사
ex) create table 테이블이름 as select 컬럼명1 [, 컬럼명2, ......] from 존재하는테이블이름(=기존테이블)
create table 테이블이름 (컬럼명1 [, 컬럼명2, ......]) as select (컬럼명1 [, 컬럼명2, ......]) from 기존테이블
- ALTER : 변경
ex)
a) 삽입 : alter table 테이블이름 add 컬럼이름 varchar2(10);
b) 삭제 : alter table 테이블이름 drop column 컬럼이름;
c) 변경 : alter table 테이블이름 modify 컬럼이름 number;
alter table 테이블이름 rename column 원래이름 to 바꿀이름;
ex) 제약조건없이 이미 생성한 테이블에 제약조건 추가, 삭제
a) 추가 : alter table 테이블이름 add constraint 제약조건이름_pk PRIMARY KEY(컬럼이름);
b) 삭제 : alter table 테이블이름 drop constraint 제약조건이름_pk;
- DROP : 삭제
ex) drop table 테이블이름;
a) 휴지통 비우기 : purge recyclebin;
b) 휴지통 내용보기 : show recyclebin;
c) 테이블 복원하기 : flashback table 테이블명 to before drop;
- RENAME : 기존테이블이름을 변경
ex) rename 원래테이블이름 to 바꿀테이블이름;
- TRUNCATE : 테이블절단 - 특정컬럼에 해당하지않고, 테이블의 레코드전체를 날려버림.
ex) truncate teble 컬럼이름;
3) DCL - Data Control Language
- 권한 확인 a) select role from dba_roles;
@경로\role.sql
b) select * from user_tab_privs_recd;
- Role 확인
ex) select * from user_role_privs;
- GRANT : 권한부여
a) 세션생성(=접속허용) - grant create session to 계정;
b) 테이블생성 - grant create table to 사용자;
c) 특정 사용자에게 특정 테이블 권한 부여;
ex) conn scott/tiger
grant select on 테이블이름 to 계정;
- REVOKE : 권한취소
a) 접속권한취소 - revoke create session from 계정;
b) 특정사용자에게 특정 테이블 권한 취소
ex) revoke select on 테이블이름 from 계정;
* 제약조건
| unique | UK | 중복된 값을 불허 |
| not null | NN | null값 불허 |
| primary key | PK | unique와 not null의 조건을 만족 |
| foreign key | FK | 참조하는 테이블의 컬럼의 값에 연동 |
| check | CK | 설정한 값만 받아들인다. |
* TRANSACTION - 어떤 하나의 작업단위. ex) 은행계좌이체
* Schema 예제
1) select * from SCOTT.emp; (= select * from emp;) - 자신이 소유한 객체를 언급시에는 객체명앞의
스키마를 생략할 수 있음.
2) 하지만, 다른 계정에서 emp테이블을 보려고 하면, 자신의 소유가 아니기때문에, 오류가 발생
한다. 따라서, 자신의 소유가 아닌 객체(테이블등)를 보려면, 그 객체를 소유한 사용자명을 반
드시 기술해주어야 한다.
3) 그렇지만, 일단은 타 계정의 객체들을 보려면, 권한을 먼저 얻어야한다. select 권한을 hr계정에서
scott계정에 부여해, scott계정에서 hr계정의 테이블들을 select로 열어보자.
a) conn hr/hr
b) grant SELECT on HR.jobs to SCOTT;
c) conn scott/tiger
d) select * from user_tab_privs_recd; - 권한확인
e) select * from HR.job;
* Synonym (동의어) - 다른 계정의 객체에 접근할 때, [사용자명.테이블명]으로 표현하는데, 긴 사용자명+
테이블명에 별칭을 부여하여 간단하게 접근할 수 있도록 하는 방법
* Data Dictionary - 오라클 DB내에 저장된 모든 객체(거시기) 정보를 제공합니다.
System View - (데이터 딕셔너리 뷰)와 (동적성능 뷰) 로 이루어져있으며, 데이타 딕셔너리에 대한
내용조회를 가능하게 해 준다.
Schema - 객체를 소유한 사용자명.
1) 데이터 딕셔너리 뷰 - 해당 DB에 존재하는 오브젝트 및 기타 정보에 대한 내용을 조회.
USER_ - 현재 접속한 사용자가 소유한 객체의 정보를 제공
- 사용자가 생성한 스키마의 정보를 볼 수 있다. (유저의 시각)
ALL_ - 사용자가 접근 가능한 모든 스키마(설계도) 의 정보를 제공
- 권한을 부여받았기에 가능하다. (유저의 시각을 넓혀줌)
DBA_ - 데이타베이스 관리를 위한 정보를 제공
- 모든 사용자의 스키마 정보를 볼 수 있다.(관리자의 시각)
2) 동적성능뷰 - 메모리 상태 및 현재 세션에 대한 정보를 확인할 수 있음.
데이터 딕셔너리 뷰는 DBA_, ALL_, USER_ 로 뷰이름이 시작되는 반면, 동적성능뷰는
v$로 뷰 이름이 시작된다.
3) select table_name from user_tables;
select table_name from all_tables;
system으로 접속해야만, 접근 가능한 DBA_
select table_name from dba_tables;
4) 자주 사용되는 딕셔너리 뷰와 약칭
테이블 검색 : select table_name from user_tables;
시퀀스 검색 : select sequence_name, min_value, max_value, increment_by, cycle_flag from user_sequences;
인덱스 검색 : select index_name, table_name from user_indexes;
인덱스 컬럼 검색 : select index_name, table_name, column_name from user_ind_columns;
뷰 검색 : select * from user_views;
테이블 검색 : select * from tab;
시퀀스 검색 : select * from seq;
5) 데이터 사전에 접근
전체 탐색 : select count(table_name) from dictionary;
특정 테이블 검색 : select count(table_name) from dictionary where table_name like 'USER%' and table_name like '%TABLE%';
테이블 설명 참조 : select comments from dictionary where table_name = 'USER_TABLES';
6) 활용
사용자 정보 열람 : select username, default_tablespace, temporary_tablespace from dba_users where username = &username;
사용자 테이블 정보 열람 : select table_name, tablespace_name, partitioned, nested from user_tables where table_name = &table_name;
제약조건 열람 : select constraint_name, constraint_type from user_constraints where table_name = &table_name;
View 정보 열람 : select text from user_views where view_name = &view_name;
* SEQUENCE
ex) create sequence 시퀀스이름
start with n - 시작값
increment by n - 증가값
maxvalue 99999 - 최대값
cycle | nocycle - 순환여부
cache | nocache - 디폴트는 20;
시퀀스이름.nextval | currval
수정 - alter sequence 시퀀스이름
start with n - 시작값
increment by n - 증가값
maxvalue 99999 - 최대값
cycle | nocycle - 순환여부
cache | nocache - 디폴트는 20;
삭제 - drop sequenct 시퀀스이름
* VIEW - 자체의 데이터는 없지만, 테이블의 데이터를 보거나, 변경할 수 있는 창이다.
테이블의 데이터를 선택적으로 보여줄 수 있어, 접근제한을 가할 수 있다.
복잡한 질의어를 통해 결과를 구하고, 반복할 수 있다.
1) simple view
하나의 테이블에서만 데이터가 유래된다.
DML수행가능
2) complex view
다중테이블에서 데이터가 유래된다.
DML수행불가
ex) create view 뷰이름 as 서브쿼리 [with check option] [with read only];
초록글씨 부분이 없다면, 그 이하의 문장은 일반 select 문장이다.
a) with check option - 조건변경 불가
b) with read only - DML수행 불가
3) 인라인 뷰 - 안쪽, 바깥쪽으로 쿼리문이 나누어질 때, 안 쪽 쿼리문을 별칭을 붙여 view처럼
사용한다.
ex) 문제 7번
ex) select rownum, ename, sal from ( select ename, sal from emp order by sal desc ) where rownum <= 5;
* INDEX
1) 목적 - 쿼리를 빠르게 하기 위해서
2) 내부구조 - B*트리 구조
3) 생성
ex) create index 인덱스이름 on 테이블이름(컬럼이름);
4) 인덱스 재생성
ex) alter index 인덱스이름 rebuild;
* INDEX 사용조건
1) 비추
a) 질의문의 결과가 전체 행의 10 ~15% 이상을 읽어들일 것으로 예상된다.
b) 테이블이 자주 갱신된다. 인덱스 유지에 상대적으로 시간이 많이 걸린다.
c) 테이블이 작다.
d) 해당 컬럼이 질의의 조건으로 자주 사용되지 않는다.
2) 추천
a) 테이블에 레코드가 많다.
b) 조건절에 해당 컬럼이 자주 사용된다.
c) 검색결과가 2~4%정도일 경우
d) join에 자주 사용되는 컬럼
e) null을 포함하는 컬럼이 많은 경우
* 테이블 스페이스
오라클에서 DB의 데이터를 저장하는 가장 큰 개념.
ex) select tablespace_name, status from dba_tablespaces;
select tablespace_name, file_name, bytes from dba_data_files;
* 계정
1) 사용자 계정 생성
a) 계정생성 - create user ID identified by PW;
b) 권한부여 - grant connect,resource to ID;(
2) 계정 비번바꾸기
alter user <계정이름> identified by <사용할 암호>;
3) 계정삭제하기
drop user ID;
4) 계정 잠그기/풀기
alter user <잠긴계정> account lock / unlock;
5) 모든 계정 확인하기
select * from all_users;
6) 세션 수 확인
select schemaname, count(*) from v$session group by schemaname;
7) 각 세션별 구분
select username, status, sid, serial# from v$session;
8) 특정 세션 kill
alter system kill session 'sid번호, serial#번호';
* 계정 만들기 종합 - 테이블스페이스까지 응용
1) 계정 생성 - ssr/ssr
ex) create user ssr
identified by ssr
2) 권한 부여 - 접속권한 포함하여 기본권한 role
ex) grant connect, resource to ssr;
3) 테이블 스페이스 생성 - (10M, 경로 : c:\tablespace) : 단지 ssr이름의 테이블스페이스가
만들어졌을 뿐, 아직 ssr계정과는
아무 상관없음.
ex) create tablespace ssr
datafile 'c:\tablespace\ssr.dbf' size 10m;
4) ssr계정의 디폴트 테이블 스페이스를 ssr테이블스페이스로 변경
ex) alter user ssr default tablespace ssr;
5) ssr 테이블스페이스의 용량제한을 푼다.
ex) alter user ssr quota unlimited on ssr;
6) 테이블생성
7) 시퀀스생성
* 문자코드
1) ASCII 문자코드 128문자 (7bit) - 영어
2) ISO-8859-9 256문자 (8bit) - 서유럽언어
3) 한국표준협회의 공업표준문자집합 - a) KSC-5601 : 2바이트로 완성형한글을 표현
아스키문자에 대한 고려없음.
b) KSC-5636 : 아스키문자를 고려한 표현
c) EUC-KR : 확장유닉스코드(Extended UNIX Code)
라고 하며, 영어는 b)방식으로,
한글은 a)방식으로 조합해 사용.
KSC-5861
4) 유니코드 - 두가지 인코딩 방식이 있음
a) UTF-8 : 아스키문자를 만나면, 1바이트로 인코딩하고, 다른 문자는 2바이트 또는
그 이상의 바이트로 처리하는 방식을 따름. 한글의 경우는 3바이크로 처리.
아스키 문자 체계와 호환이 가능해서, 인터넷상에서 기본적인 인코딩으로 환영받음.
XML에서도 별도의 언급이 없으면, 디폴트로 UTF-8 인코딩으로 간주함.
b) UTF-16 : 아스키건 뭐건 모두 2바이트로 처리한다. 65,356개를 처리할 수 있다.
5) XML선언에서 인코딩 방식을 "euc-kr"로 지정하는 이유 - 운영체제와 관련이 있다.
한글 운영체제의 인코딩방식이 MS의 경우(MS949 : EUC-KR과 동일방식) MS949방식을 채택
하고 있으며, 이는 euc-kr( or KSC-5601)과 동일한 인코딩방식이다. 즉 한글을 2바이트로 처리
한다는 소리다. 그런데, XML의 디폴트인 UTF-8방식은 한글을 3바이트로 처리하기때문에,
처음엔 좀 되는 듯 싶다가도, 나중에, 아귀가 안 맞으면서, 오류를 일으키게된다.
따라서, 비록, UTF-8이 인터넷의 기본 인코딩으로 환영받고 있고, XML의 디폴트도 UTF-8 이지만,
운영체제에서 파일을 저장할 때, euc-kr일 것으로 예상하는 상황인지라, 두 인코딩방식이 일치하지
않는다면, 오류가 발생하기때문에, 저장시에도 명시적으로 euc-kr, 즉 운영체제가 지원하는 한글
인코딩 방식을 따르겠다고, 선언해주어야한다.