말 그대로 기본적인 쿼리문들 테이블 생성, 삭제, 수정 ..
'명규의 오라클' 이라는 사이트에서 본것들, 지금은 사라져 좀 아쉬움이 남네요.
기본적인 테이블 작업
test라는 테이블의 내용
SQL> select * from test;
NAME AGE SEX
-------------------- ---------- ----------
강명규 26 1
임꺽정 30 1
홍길동 25 1
김희선 20 0
고소영 25 0
이승연 22 0
하수빈 23 0
7 개의 행이 선택되었습니다.
테이블test에 컬럼misc를 추가하는 예
SQL> alter table test add (misc varchar2(20));
테이블이 변경되었습니다.
컬럼을 추가하면 모든 테이터는 null값을 갖는다.
SQL> select * from test;
NAME AGE SEX MISC
-------------------- ---------- ---------- --------------------
강명규 26 1
임꺽정 30 1
홍길동 25 1
김희선 20 0
고소영 25 0
이승연 22 0
하수빈 23 0
7 개의 행이 선택되었습니다.
not null인 컬럼은 바로 추가될 수 없다.
SQL> alter table test
2 add( imsi varchar2(20) NOT NULL);
alter table test
*
1행에 오류:
ORA-01758: NOT NULL 열을 추가하기 위해서는 테이블이 비어 있어야 합니다
NOT NULL컬럼은 default값을 할당하면서 컬럼을 ADD해야 한다.
SQL> alter table test
2 add ( imsi varchar2(20) default '임시라네..' not null );
테이블이 변경되었습니다.
SQL> select * from test;
NAME AGE SEX MISC IMSI
-------------------- ---------- ---------- ---------- --------------------
강명규 26 1 임시라네..
임꺽정 30 1 임시라네..
홍길동 25 1 임시라네..
김희선 20 0 임시라네..
고소영 25 0 임시라네..
이승연 22 0 임시라네..
하수빈 23 0 임시라네..
7 개의 행이 선택되었습니다.
추가한 IMSI라는 컬럼을 제거하자.
데이터가 존재하는 경우에도 제거가 가능하다.
(이전 글에서는 컬럼삭제가 불가능하다고 적었습니다.
이 부분에 대해 질문이 가끔 들어오는데 8.1.6이하버전에서 컬럼의 제거는 불가능했습니다.
8.1.5는 모르겠으나, 8.0.5는 안되었죠. 의견 있으신 분은 메일 주세요^^
이번에 이 사실을 알게 되어 새로 수정한 겁니다. 수정환경: 윈도2000,오라클 8.1.6)
SQL> alter table test
2 drop column imsi;
테이블이 변경되었습니다.
SQL> desc test
이름 널? 유형
----------------------------------------------------- -------- --------------
NAME VARCHAR2(20)
AGE NUMBER(2)
SEX NUMBER(1)
MISC NUMBER(20)
컬럼의 크기를 늘일 때는 문제가 없지만, 줄일때는 해당 컬럼값이 nul일때만이 가능하다
SQL> desc test
이름 Null? 유형
------------------------------- -------- ----
NAME VARCHAR2(20)
AGE NUMBER(2)
SEX NUMBER(1)
MISC VARCHAR2(30)
misc컬럼은 null값을 갖기 때문에 크기를 줄일때 문제가 없다.
SQL> alter table test
2 modify (misc varchar2(20));
테이블이 변경되었습니다.
name컬럼에는 데이터가 있으므로 크기를 줄일 수 없다.
SQL> alter table test
2 modify (name varchar2(15));
modify (name varchar2(15))
*
2행에 오류:
ORA-01441: 길이를 짧게 변경할 열의 값은 NULL이어야 합니다
misc컬럼은 varchar2형을 가지지만 number형으로 변환가능하다
SQL> alter table test
2 modify (misc number(20));
테이블이 변경되었습니다.
SQL> desc test
이름 Null? 유형
------------------------------- -------- ----
NAME VARCHAR2(20)
AGE NUMBER(2)
SEX NUMBER(1)
MISC NUMBER(20)
not null 제약조건은 modify를 통해 추가될 수 있음을 유의하자(add로 추가되는 것이 아니다.)
not null은 컬럼에 null인 테이터가 있으면 안된다.
SQL> alter table test
2 modify (name not null);
테이블이 변경되었습니다.
SQL> desc test
이름 Null? 유형
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(20)
AGE NUMBER(2)
SEX NUMBER(1)
MISC NUMBER(20)
unique constraint (add가 사용되었다)
SQL> alter table test
2 add(unique (name));
테이블이 변경되었습니다.
check constraint(역시 add가 사용되었다.)
SQL> desc test;
이름 Null? 유형
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(20)
AGE NUMBER(2)
SEX NUMBER(1)
MISC NUMBER(20)
일단 이전에 변경한 misc컬럼을 다시 varchar2형으로 변환한다
SQL> alter table test
2 modify (misc varchar2(20));
테이블이 변경되었습니다.
SQL> alter table test
2 add (check(misc in('P','S','M','L')));
테이블이 변경되었습니다.
자신이 가진 constraints를 본다
SQL> select constraint_name, table_name, r_constraint_name, status from user_constraints;
CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_BANK_ACCT_01 BANK_ACCOUNT ENABLED
FK_BANK_ACCT_01 BANK_ACCOUNT PK_EMPLOYEE_01 ENABLED
SYS_C001002 EMPLOYEE ENABLED
SYS_C001003 EMPLOYEE ENABLED
SYS_C001004 EMPLOYEE ENABLED
PK_EMPLOYEE_01 EMPLOYEE ENABLED
UK_EMPLOYEE_01 EMPLOYEE ENABLED
SYS_C001009 TEST ENABLED
SYS_C001010 TEST ENABLED
SYS_C001011 TEST ENABLED
TEST_SEX_CHECK TEST ENABLED
11 개의 행이 선택되었습니다.
컬럼column_name의 길이가 길기 때문에 일단 보기 쉽게 column길이를 줄인다
SQL> col column_name format a20
인덱스가 있는 컬럼을 보여준다
SQL> select * from user_ind_columns;
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH
------------------------------ ------------------------------ -------------------- --------------- -------------
PK_EMPLOYEE_01 EMPLOYEE EMPID 1 22
UK_EMPLOYEE_01 EMPLOYEE HOME_PHONE 1 22
PK_BANK_ACCT_01 BANK_ACCOUNT BANK_ACCT_NO 1 40
SYS_C001010 TEST NAME 1 20
위의 인덱스 pk_employee_01을 disable하려고 하지만 다른 놈(table)이 foreign key로 reference하고 있으므로 불가능하다
primary key와 unique constraint는 생성시 자동으로 index가 생긴다는 것을 상기하자.
SQL> alter table employee
2 disable constraint pk_employee_01;
alter table employee
*
1행에 오류:
ORA-02297: 제약 (KANG.PK_EMPLOYEE_01)을 사용불가하게 할 수 없음 - 종속관계가 있습니다
foreign key를 disable했다가
SQL> alter table bank_account
2 disable constraint fk_bank_acct_01;
테이블이 변경되었습니다.
enable했다
SQL> alter table bank_account
2 enable constraint fk_bank_acct_01;
테이블이 변경되었습니다.
primary key를 disable했다가
SQL> alter table bank_account
2 disable primary key;
테이블이 변경되었습니다.
enable했다
SQL> alter table bank_account
2 enable primary key;
테이블이 변경되었습니다.
다른 놈이 reference하고 있다면 cascade옵션으로 강제 적용할 수 있다
SQL> alter table employee
2 disable primary key
3 ;
alter table employee
*
1행에 오류:
ORA-02297: 제약 (KANG.PK_EMPLOYEE_01)을 사용불가하게 할 수 없음 - 종속관계가 있습니다
경 과: 00:00:00.00
SQL> alter table employee
2 disable primary key cascade;
테이블이 변경되었습니다.
경 과: 00:00:00.06
SQL> desc employee
이름 Null? 유형
------------------------------- -------- ----
EMPID NUMBER(10)
LASTNAME NOT NULL VARCHAR2(25)
FIRSTNAME NOT NULL VARCHAR2(25)
SALARY NUMBER(10,4)
HOME_PHONE NUMBER(15)
SQL> alter table test
2 disable unique(name);
테이블이 변경되었습니다.
경 과: 00:00:00.08
SQL> alter table test
2 enable unique(name);
테이블이 변경되었습니다.
SQL> alter table employee enable primary key;
테이블이 변경되었습니다.
SQL> select constraint_name, table_name, r_constraint_name, status from user_constraints;
CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_BANK_ACCT_01 BANK_ACCOUNT ENABLED
FK_BANK_ACCT_01 BANK_ACCOUNT PK_EMPLOYEE_01 DISABLED
SYS_C001002 EMPLOYEE ENABLED
SYS_C001003 EMPLOYEE ENABLED
SYS_C001004 EMPLOYEE ENABLED
PK_EMPLOYEE_01 EMPLOYEE ENABLED
UK_EMPLOYEE_01 EMPLOYEE ENABLED
SYS_C001009 TEST ENABLED
SYS_C001010 TEST ENABLED
SYS_C001011 TEST ENABLED
TEST_SEX_CHECK TEST ENABLED
11 개의 행이 선택되었습니다.
경 과: 00:00:00.01
SQL> alter table bank_account
2 enable constraint fk_bank_acct_01;
테이블이 변경되었습니다.
경 과: 00:00:00.05
SQL> select constraint_name, table_name, r_constraint_name, status from user_constraints;
CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_BANK_ACCT_01 BANK_ACCOUNT ENABLED
FK_BANK_ACCT_01 BANK_ACCOUNT PK_EMPLOYEE_01 ENABLED
SYS_C001002 EMPLOYEE ENABLED
SYS_C001003 EMPLOYEE ENABLED
SYS_C001004 EMPLOYEE ENABLED
PK_EMPLOYEE_01 EMPLOYEE ENABLED
UK_EMPLOYEE_01 EMPLOYEE ENABLED
SYS_C001009 TEST ENABLED
SYS_C001010 TEST ENABLED
SYS_C001011 TEST ENABLED
TEST_SEX_CHECK TEST ENABLED
11 개의 행이 선택되었습니다.
constraint add/drop
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BANK_ACCOUNT TABLE
EMPLOYEE TABLE
TEST TABLE
SQL> alter table bank_account
2 drop constraint fk_bank_acct_01;
테이블이 변경되었습니다.
SQL> alter table bank_account
2 add constraint fk_bank_acct_01 foreign key(empid) references employee(empid);
테이블이 변경되었습니다.
SQL> alter table bank_account
2 drop primary key;
테이블이 변경되었습니다.
SQL> alter table employee
2 drop primary key cascade;
테이블이 변경되었습니다.
테이블이름 변경
SQL> rename test to test_rename;
테이블명이 바뀌었습니다.
테이블과 컬럼에 코멘드(comment) 달기
SQL> comment on table test_rename is '원래 이름은 test인데 test_rename으로 테이블명을 바꿈';
주석이 생성되었습니다.
SQL> comment on column test_rename.name is '사람의 이름이지 뭐겠니?';
주석이 생성되었습니다.
달아놓은 주석을 보기
SQL> select * from all_tab_comments;
OWNER TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ------------------------------ ----------- ----------------------------------------
SYS ALL_ALL_TABLES VIEW Description of all object and relational
tables accessible to the user
SYS ALL_ARGUMENTS VIEW Arguments in object accessible to the us
er
중략..
KANG BANK_ACCOUNT TABLE
KANG EMPLOYEE TABLE
KANG TEST_RENAME TABLE 원래 이름은 test인데 test_rename으로 테
이블명을 바꿈
338 개의 행이 선택되었습니다.
자신이 소유한 테이블들의 코멘트를 본다
SQL> select * from user_tab_comments;
TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ----------- ----------------------------------------
BANK_ACCOUNT TABLE
EMPLOYEE TABLE
TEST_RENAME TABLE 원래 이름은 test인데 test_rename으로 테
이블명을 바꿈
자신이 소유한 테이블에서 컬럼의 코멘트를 본다
SQL> select * from user_col_comments;
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ -------------------- ----------------------------------------
BANK_ACCOUNT BANK_ACCT_NO
BANK_ACCOUNT EMPID
BANK_ACCOUNT BANK_ROUTE_NO
BANK_ACCOUNT BANK_NAME
EMPLOYEE EMPID
EMPLOYEE LASTNAME
EMPLOYEE FIRSTNAME
EMPLOYEE SALARY
EMPLOYEE HOME_PHONE
TEST_RENAME MISC
TEST_RENAME IMSI
TEST_RENAME NAME 사람의 이름이지 뭐겠니?
TEST_RENAME AGE
TEST_RENAME SEX
14 개의 행이 선택되었습니다.