문제) department2 테이블 생성
department 테이블에서 deptno, dname, loc
create table deraptment2
as
select deptno, dname, loc
from depratment
1 create table department2
2 as
3 select deptno, dname, loc
4* from department
5 ;
테이블이 생성되었습니다.
SQL> select * from department2l
2
SQL> select * from department2;
DEPTNO DNAME LOC
---------- ---------------- ----------
101 컴퓨터공학과 1호관
102 멀티미디어학과 2호관
201 전자공학과 3호관
202 기계공학과 4호관
100 정보미디어학부
200 메카트로닉스학부
10 공과대학
7 개의 행이 선택되었습니다.
테이블명 변경
rename 구테이블명 to 새테이블명
SQL> rename department2 to dept2;
테이블명이 바뀌었습니다.
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPARTMENT TABLE
DEPT TABLE
DEPT2 TABLE
DUMMY TABLE
EMP TABLE
HEIGHT_INFO TABLE
MEMBER TABLE
M_INFO TABLE
PROFESSOR TABLE
SALGRADE TABLE
SGRADE TABLE
STU TABLE
STUDENT TABLE
WEIGHT_INFO TABLE
15 개의 행이 선택되었습니다.
컬럼명 변경
alter table 테이블명
renamecolumn 구컬럼명 to 새컬럼명
loc - pos
SQL> alter table dept2
2 rename column loc to pos;
테이블이 변경되었습니다.
SQL> desc dept2
이름 널? 유형
----------------------------------------------------- -------- ---------------------
DEPTNO NUMBER(4)
DNAME VARCHAR2(16)
POS VARCHAR2(10)
컬럼 추가/ 삭제
ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 DEFAULT~~:추가
DROP 컬럼명 : 삭제
MODIFY 컬럼명 테이터타입 : 변경
문제) ETC / VARCHAR2(20) 을 추가
ALTER TABLE DEPT2
ADD ETC VARCHAR2(20);
ERC /CHAR(10) 변경
SQL> ALTER TABLE DEPT2
2 ADD ETC VARCHAR2(20);
테이블이 변경되었습니다.
SQL> ALTER TABLE DEPT2
2 MODIFY ETC CHAR(10);
테이블이 변경되었습니다.
SQL> ALTER TABLE DEPT2
2 DROP COLUMN ETC;
테이블이 변경되었습니다.
SQL>
문제) STUDENT 테이블에서
STUDNO, NAME, USERID, GRADE, DEPTNO 컬럼을 복사하여
STUDENT2테이블 생성하시오
생성된 테이블의 구조를 확인하세요
SUDENT2테이블명을 SYU2로 변경하세요
SQL> CREATE TABLE STUDENT2
2 AS
3 SELECT STUDNO, NAME, USERID, GRADE, DEPTNO
4 FROM STUDENT;
테이블이 생성되었습니다.
SQL> DESC STUDENT2
이름 널? 유형
----------------------------------------------------- -------- -------------------
STUDNO NUMBER(5)
NAME VARCHAR2(10)
USERID VARCHAR2(10)
GRADE VARCHAR2(1)
DEPTNO NUMBER(4)
SQL> RENAME STUDENT2 TO STU2;
테이블명이 바뀌었습니다.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPARTMENT TABLE
DEPT TABLE
DEPT2 TABLE
DUMMY TABLE
EMP TABLE
HEIGHT_INFO TABLE
MEMBER TABLE
M_INFO TABLE
PROFESSOR TABLE
SALGRADE TABLE
SGRADE TABLE
STU TABLE
STUDENT TABLE
STU2 TABLE
WEIGHT_INFO TABLE
16 개의 행이 선택되었습니다.
SQL> DESC STU2
이름 널? 유형
----------------------------------------------------- -------- ------------------
STUDNO NUMBER(5)
NAME VARCHAR2(10)
USERID VARCHAR2(10)
GRADE VARCHAR2(1)
DEPTNO NUMBER(4)
기존 테이블에 제약조건 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 제약 조건
ADD CONSTRAINT 제약조건명 PRIMATY KEY(PK컬럼명)
ADD CONSTRAINT 제약조건명 UNIQUE(UK 컬럼명)
ADD CONSTRAINT 제약조건명 CHECK(CK 컬럼명~~~~)
ADD CONSTRAINT 제약조건명
FOREINGN KEY(FK 컬럼명) REFERENCES 부모테이블명(PK컬럼명) = FK, PK 컬럼 동일
NOT NULL 제약 조건의 경우는 MODIFY 를 쓴다.ㅣ
MODIFY (NN컬럼명 CONSTRAINT 제약조건명 NOT NULL);
STU2 테이블 제약조건 추가하기
STUDNO :PK
NAME :NN -- MODIFY 수정가능. 다른거는 추가 삭제만 가능
USERID : UK
GRADE : CK 1,2,3,4
DEPTNO : FK
STUDNO :PK
SQL> ALTER TABLE STU2
2 ADD CONSTRAINT STU2_STUDNO_PK PRIMARY KEY(STUDNO);
USERID : UK
SQL> ALTER TABLE STU2
2 ADD CONSTRAINT STU2_USERID_UK UNIQUE(USERID);
GRADE : CK 1,2,3,4
SQL> ALTER TABLE STU2
2 ADD CONSTRAINT STU2_GRADE_CK CHECK(GRADE IN('1','2','3','4'));
SQL> ALTER TABLE DEPT2
2 ADD CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY(DEPTNO);
1 ALTER TABLE TEST1
2 ADD CONSTRAINT TEST1_DEPTNO_FK
3 FOREIGN KEY(DEPTNO)
4* REFERENCES DEPT2(DEPTNO)
SQL> /
SQL> ALTER TABLE TEST1
2 ADD CONSTRAINT TEST1_DEPTNO_FK
FOREINGN KEY(DEPTNO)
3 REFERENCES DEPT2(DETPNO);
DEPTNO : FK
SQL> ALTER TABLE STU2
2 ADD CONSTRAINT STU2_DEPTNO_FK
FOREIGN KEY(DEPTNO)
REFERENCES DEPT2(DEPTNO);
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME='STU2';
CONSTRAINT_NAME C
------------------------------ -
STU2_STUDNO_PK P
STU2_USERID_UK U
STU2_GRADE_CK C
STU2_DEPTNO_FK R
NOT NULL 제약 조건의 경우는 MODIFY 를 쓴다.ㅣ
ALTER TABLE 테이블명
MODIFY (NN컬럼명 CONSTRAINT 제약조건명 NOT NULL);
NAME :NN
SQL> ALTER TABLE STU2
2 MODIFY (NAME CONSTRAINT STU2_NAME_NN NOT NULL);
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME IN('STU2','DEPT2');
CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ --------
DEPT2_DEPTNO_PK P DEPT2 ENABLED
STU2_STUDNO_PK P STU2 ENABLED
STU2_USERID_UK U STU2 ENABLED
STU2_GRADE_CK C STU2 ENABLED
STU2_DEPTNO_FK R STU2 ENABLED
STU2_NAME_NN C STU2 ENABLED
6 개의 행이 선택되었습니다.
--제약조건의 비활성화/활성화
ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약조건명 [CASCADE]
ENABLE CONSTRAINT 제약조건명[CASCADE]
SQL> ALTER TABLE STU2
2 DISABLE CONSTRAINT STU2_NAME_NN;
테이블이 변경되었습니다.
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME IN('STU2','DEPT2');
CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ --------
DEPT2_DEPTNO_PK P DEPT2 ENABLED
STU2_STUDNO_PK P STU2 ENABLED
STU2_USERID_UK U STU2 ENABLED
STU2_GRADE_CK C STU2 ENABLED
STU2_DEPTNO_FK R STU2 ENABLED
STU2_NAME_NN C STU2 DISABLED
6 개의 행이 선택되었습니다.
SQL> INSERT INTO STU2
2 VALUES(1000,NULL,'KJC','4','101');
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM STU2;
STUDNO NAME USERID G DEPTNO
---------- ---------- ---------- - ----------
10102 박미경 ansel414 1 101
10103 김영균 mandu 3 101
10201 김진영 simply 3 101
10104 지은경 Gomo00 2 101
10202 오유석 yousuk 4 102
10203 하나리 hanal 1 102
10105 임유진 YouJin12 2 101
10106 서재진 seolly 1 101
10204 윤진욱 Samba7 3 102
10107 이광훈 huriky 4 101
10108 류민정 cleanSky 2 101
1000 KJC 4 101
12 개의 행이 선택되었습니다.
* FOREINGN KEY - 상속의 개념으로 부모의 값만 상속 받는다. 다른 값은 에러 발생.
SQL> INSERT INTO STU2
2 VALUES(1001,NULL,'KCUFL','1',501);
INSERT INTO STU2
*
1행에 오류:
ORA-02291: 무결성 제약조건(CP1.STU2_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다
* 자식 테이블의 FK에 값을 입력할 때는 부모테이블의 값중에 선택
문제) 자식테이블
STU2테이블의 DEPTNORK 101인 학생의 테이터를 삭제
SQL> DELETE FROM STU2
2 WHERE DEPTNO=101;
9 행이 삭제되었습니다.
문제) 부모테이블)(DEPTNO)
DEPTNO가 101인 데이터를 삭제
SQL> DELETE FROM DEPT2
2 WHERE DEPTNO=101;
DELETE FROM DEPT2
*
1행에 오류:
ORA-02292: 무결성 제약조건(CP1.STU2_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
* 자식테이블의 참조하는 값은 부모테이블에서 삭제불가
* 자식테이블에서는 부모테이블의 값을 참조하는 데이터 삭제가 가능
* 제약조건 삭제
방법1 :외래키를 삭제한 후 - 참조키를 삭제
ALTER TABLE STU2
DROP CONSTRAIINT STU2_DEPTNO_FK;
ALTER TABLE DEPT2
DROP CONSTRAINT DEPT_DEPTNO_PK;
방법2
ALTER TABLE DEPT2
DROP CONSTRAINT DEPT2_DEPTNO_PK CASCADE;
SQL> ALTER TABLE STU2
2 ENABLE CONSTRAINT STU2_NAME_NN;
테이블이 변경되었습니다.
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, TABLE_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'STU2';
CONSTRAINT_NAME C STATUS TABLE_NAME
------------------------------ - -------- ------------------------------
STU2_STUDNO_PK P ENABLED STU2
STU2_USERID_UK U ENABLED STU2
STU2_GRADE_CK C ENABLED STU2
STU2_NAME_NN C ENABLED STU2
SQL>
sequence
SQL> INSERT INTO SEQ_TEST
2 VALUES(SEQ_NO.NEXTVAL,'들어가나?');
NO CONTENT
---------- --------------------
10 들어가나?
SQL> SELECT SEQ_NO.CURRVAL FROM DUAL;
CURRVAL
----------
12
SQL> SELECT SEQ_NO.NEXTVAL FROM DUAL;
NEXTVAL
----------
14
문제) DEPT2 테이블에 DEPTNO 컬럼에 SEQ_NO 시퀀스 입력
SQL> INSERT INTO DEPT2(DEPTNO)
2 VALUES(SEQ_NO.NEXTVAL);
*
2행에 오류:
ORA-08004: 시퀀스 SEQ_NO.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다
수정 - 스타트를 제외한 모든 것 수정 가능
SQL> INSERT INTO DEPT2(DEPTNO)
2 VALUES(SEQ_NO.NEXTVAL);
VALUES(SEQ_NO.NEXTVAL)
*
2행에 오류:
ORA-08004: 시퀀스 SEQ_NO.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다
SQL> ALTER SEQUENCE SEQ_NO
2 MAXVALUE 20;
주문번호가 변경되었습니다.
1 INSERT INTO DEPT2(DEPTNO)
2* VALUES(SEQ_NO.NEXTVAL)
SQL> /
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SEQ_TEST
2 VALUES(SEQ_NO.NEXTVAL,'이번꺼는?');
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM SEQ_TEST;
NO CONTENT
---------- --------------------
10 들어가나?
12 들어가나?
18 이번꺼는?
문제) DEPT2테이블에 DEPTNO 컬럼용으로 사용될
SEQ_DEPT2 시퀀스를 생성
502 부터 3씩 증가하되 최대 999까지 증가
NOCYCLE
DEPT2 테이블에 입력
SQL> CREATE SEQUENCE SEQ_DEPT2
2 START WITH 502
3 INCREMENT BY 3
4 MAXVALUE 999
5 NOCYCLE;
주문번호가 생성되었습니다.
SQL> INSERT INTO DEPT2(DEPTNO)
2 VALUES(SEQ_DEPT2.NEXTVAL);
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM DEPT2;
DEPTNO DNAME POS
---------- ---------------- ----------
101 컴퓨터공학과 1호관
102 멀티미디어학과 2호관
201 전자공학과 3호관
202 기계공학과 4호관
100 정보미디어학부
200 메카트로닉스학부
10 공과대학
16
502
9 개의 행이 선택되었습니다.
sequence 조회
SQL> select sequence_name, min_value, max_value, increment_by, last_number
2 from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
SEQ_DEPT2 1 999 3 562
SEQ_NO 1 20 2 22
sequence 삭제
SQL> drop sequence seq_dept2;
주문번호가 삭제되었습니다.
SQL> drop sequence seq_no;
주문번호가 삭제되었습니다.
SQL>