19.10.01. (LOCK/DDL/CREATE 테이블 생성 및 기존 테이블 복사/ALTER/DROP/제약조건)

(1) 데이터 락(Lock)

​

병행처리의 문제를 해결하기 위해 사용한다.

한명의 사용자가 아니라 다른 사용자도 사용할 경우 commit을 완료해야 변경된 내용이 반영된다.

​

(2) 데이터 정의어(DDL)

​

-SQL DDL(Table Create, Alter, Drop)

​

-생성할 수 있는 객체 종류

1.테이블 : 기본적인 데이터 저장 단위

2.뷰 : 데이터의 부분집합

3.시퀀스 : 고유번호를 자동으로 생성

4.인덱스 : 빠른 검색을 위함

​

​

(3)CREATE TABLE

​

테이블명은 ORACLE의 예약어를 쓸 수 없다.

테이블명은과 칼럼 명은 반드시 문자로 시작, 최대 30자까지 허용

​

-SELECT * FROM TAB; (내가 만든 테이블 모두 확인 가능)

-SELECT TABLE_NAME FROM USER_TABLES; (내가 만든 테이블 모두 확인 가능)

​

-TABLE 새로 만들기

​

CREATE TABLE TEST(

U_ID VARCHAR2(10) PRIMARY KEY,

U_DATE DATE

);

​

- 기존 테이블 활용해서 타입과 컬럼만 복사하기

​

(WHERE 1 = 0 데이터는 안 넣고, 타입이랑 컬럼만 복사하고 싶은 경우 사용)

CREATE TABLE ACCOUNT2

AS SELECT * FROM ACCOUNT

WHERE 1 = 0;

​

- 기존 테이블 활용해서 똑같은 데이터로 복사하기

​

CREATE TABLE ACCOUNT3

AS SELECT * FROM ACCOUNT;

​

문1 ACCOUNT 테이블을 생성한다.

ACCOUNT_NO : 숫자 4자리,PK

ACCOUNT_DATE : 날짜

CODE : 가변문자 14자리

WITHDRAWAR : 숫자 8자리

DEPOSIT : 숫자 10자리

BALANCE : 숫자 12자리

BRANCH_CODE : 문자 8자리

​

​

CREATE TABLE ACCOUNT

(ACCOUNT_NO NUMBER(4) PRIMARY KEY,

ACCOUNT_DATE DATE DEFAULT SYSDATE, **DEFAULT SYSDATE(데이터를 넣지 않아도 현재시간으로 자동으로 입력됨)

CODE VARCHAR2(14),

WITHDRAWAR NUMBER(8),

DEPOSIT NUMBER(10),

BALANCE NUMBER(12),

BRANCH_CODE CHAR(8));

​

(4) DROP : 테이블 자체를 삭제 VS DELETE : 데이터를 삭제

​

DROP TABLE [테이블명]; VS DELETE FROM TABLE;

​

(5) RENAME : 테이블 이름 바꾸기

​

RENAME [이전 테이블명] TO [이후 테이블명];

​

RENAME S_STU TO S_STUDENT;

​

(6) ALTER TABLE

​

① 테이블 컬럼 이름 변경하기

ALTER TABLE [테이블명]

RENAME COLUMN 기존컬럼명 TO 새컬럼명;

​

ex) ALTER TABLE S_STU

RENAME COLUME BMI TO BMIs;

​

② 테이블 열 추가

ALTER TABLE [테이블명]

ADD ([칼럼명] [칼럼타입]) ;

​

ex)ALTER TABLE S_STUDENT

ADD (BMI NUMBER(4,1));

​

③ 테이블 열 타입 변경

ALTER TABLE [테이블명]

MODIFY ([칼럼명] [이후 변경타입]);

(문자에서 숫자로 or 자릿수를 이전보다 작게 하는 것은 기존 데이터에 문제를 발생시켜 오류)

​

ex)ALTER TABLE S_STU

MODIFY (BMIs NUMBER);

​

④ 테이블 칼럼 자체를 삭제

ALTER TABLE [테이블명]

DROP COLUMN [컬럼명];

​

ex)ALTER TABLE T_STUDENT

DROP COLUMN STU_DEPT;

​

(7) TRUNCATE : 테이블내의 데이터 삭제

(DELETE와 같지만 TRUNCATE는 자동 COMMIT 되어 데이터를 잘못 삭제하더라도 ROLLBACK을 할 수 없다.)

(DELETE는 로그를 쌓지만 TRUNCATE는 로그가 쌓이지 않기 때문에, 속도면에서 월등히 빠르다.)

하지만, 잘못 날렸을 경우 ROLLBACK이 되지 않기 때문에 굉장히 신중히 날려야 하는 쿼리다. )

​

(8) 제약조건(Constraint) : 부적절한 자료가 입력되는 것을 방지하기 위해서 사용

- 그냥 컬럼명, 컬럼타입 뒤에 제약조건만 적어도 되지만, 제약 조건명을 명시하면 추후에 어떤 제약 조건을 위해했는지 알 수 있게 되기 때문에 편리하다.

​

-Oracle에서 제공하는 제약조건

(DESC명령어로는 NOT NULL 제약조건만 확인 가능)

확인하고 싶다면?

=(DESC USER_CONSTRAINTS;)

​

1. NOT NULL : NULL 값의 입력을 금지, 필수 입력을 위한 컬럼에 사용

​

CREATE TABLE STUDENT_1 (

STU_NO CHAR(9) NOT NULL,

STU_NAME VARCHAR2(20) NOT NULL

);

​

2. UNIQUE : 해당 칼럼의 값이 테이블 안에서 유일한 값이 되도록 하되 NULL을 허용.

: 유일 키 제약을 작성하면 자동으로 유일 인덱스를 생성한다.

​

CREATE TABLE STUDENT_2 (

STU_NO CHAR(9) UNIQUE,

STU_NAME VARCHAR2(20) UNIQUE

);

​

3. CHECK : 입력할 수 있는 값의 범위 등을 제한한다 (급여 컬럼을 500에서 5000사이 값만 입력

or

성별 입력시 사용)

​

CREATE TABLE STUDENT_3 (

STU_NO CHAR(9),

STU_NAME VARCHAR2(20) CONSTRAINT u_stu_name NOT NULL

STU_GENDER CONSTRAINT c_stu_gender CHECK (STU_GENDER IN ('M','F')

);

​

4. DEFAULT : 데이터를 입력하지 않아도 지정된 값이 기본으로 입력.

(DEFAULT 값을 지정하지 않으면 NULL로 출력)

ex) LOC VARCHAR2(13) DEFAULT 'SEOUL'

​

5. PRIMARY KEY : (UNIQUE, NOT NULL) 두가지 제약조건이 걸린 키

: 데이터 중복이 있어서는 안된다!!

: 사원번호 OR 학번 등에 적용

​

6. FOREIGN KEY : 테이블 다 만들고 추가하는 것이 좋다 (부모 테이블과 자식 테이블 설정을 위해서)

: 부모 키가 되기 위한 컬럼은 반드시 부모 테이블의 PRIMARY KEY로 설정되어야 함

(DEPT 테이블에 DEPTNO는 10,20,30,40인 부서가 존재한다.

EMP 테이블에 DEPTNO는 10,20,30으로만 기록되어 있다.

이처럼, 만일 DEPT 테이블에 존재하지 않는 부서번호가 EMP 테이블에 지정된다면 이치에 맞지 않는다.

그러므로, EMP 테이블에 부서번호를 입력 할 때 DEPT 테이블에 존재하는 부서번호만 입력하도록 해야 하므로

이때, EMP 테이블의 DEPTNO에 FOREIGN KEY를 사용하는 것이다.)

​

CREATE TABLE T_EMP(

EMPNO CHAR(3) PRIMARY KEY,

ENAME VARCHAR2(30),

DEPTNO CHAR(2) CONSTRAINT fk_deptno

REFERENCE DEPT(DEPTNO)

);

​

ALTER TABLE T_EMP

ADD CONSTRAINT fk_deptno REFERENCE DEPT (DEPTNO);

​

- 제약조건 확인

​

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'STUDENT_1';

​

문2 다음 테이블(nike)을 만드시오.

​

id : 문자 10자리 (pk)

style : 문자 20자리

color : 가변문자 20자리

owner : 가변문자 20자리

c_size : XS, S, M, L, XL, XXL만 입력가능

gender : M, F만 입력 가능

count : NUMBER (1~9999 입력 가능)

m_date : DATE

​

CREATE TABLE NIKE(

ID CHAR(10) CONSTRAINT pk_id PRIMARY KEY,

STYLE CHAR(20),

COLOR VARCHAR2(20),

OWNER VARCHAR2(20),

C_SIZE CHAR(3),

CONSTRAINT C_C_SIZE CHECK (C_SIZE IN ('XS','S','M','L','XL','XXL')),

GENDER CHAR(1)

CONSTRAINT C_GENDER CHECK (GENDER IN ('M','F')),

COUNT NUMBER

CONSTRAINT C_COUNT CHECK (COUNT BETWEEN 1 AND 9999),

M_DATE DATE

);

​

(9) 제약조건 추가

​

//NOT NULL 추가

ALTER TABLE NIKE MODIFY style NOT NULL;

ALTER TABLE NIKE MODIFY style CONSTRAINT nn_style NOT NULL;

ALTER TABLE NIKE MODIFY style NULL;

​

//CHECK, UNIQUE, PK, FK 추가

ALTER TABLE NIKE

ADD CONSTRAINT c_color CHECK (COLOR IN ('YELLOW','BLUE','RED'));

​

ALTER TABLE NIKE

ADD CONSTRAINT u_color UNIQUE (COLOR);

​

(10) 제약조건 삭제

​

ALTER TABLE NIKE

DROP CONSTRAINT c_color;

​

(11) PRIMARY KEY 삭제

​

ALTER TABLE [TABLE 명]

DROP PRIMARY KEY

​

(12) PRIMARY KEY 만들기

​

ALTER TABLE [TABLE 명]

ADD PRIMARY KEY (COL1, COL2, COL3 ...)

​

ALTER TABLE NIKE

ADD CONSTRAINT pk_id PRIMARY KEY (ID);

​

문3 다음 테이블(ADIDAS)을 만드시오.

​

ID : 기본키 (pk)

STYLE : UNIQUE

COLOR : 색깔 입력은 항상 존재

OWNER : 제약 조건 없음

C_SIZE : XS, S, M, L, XL, XXL만 입력가능

GENDER : M, F만 입력 가능

COUNT : 제약 조건 없음

M_DATE : DEFAULT SYSDATE

​

OWNER -> NOT NULL 추가

C_SIZE -> CHECK 조건 삭제

COUNT -> CHECK 조건 추가 (0 <= COUNT <= 1000)

​

CREATE TABLE ADIDAS(

ID CHAR(10) CONSTRAINT pk_id PRIMARY KEY,

STYLE CHAR(20) CONSTRAINT u_style UNIQUE,

COLOR VARCHAR2(20) CONSTRAINT n_color NOT NULL,

OWNER VARCHAR2(20),

C_SIZE CHAR(3) CONSTRAINT c_c_size CHECK (C_SIZE IN ('XS','S','M','L','XL','XXL')),

GENDER CHAR(1) CONSTRAINT c_gender CHECK (GENDER IN ('M','F')),

COUNT NUMBER,

M_DATE DATE CONSTRAINT d_m_date DEFAULT SYSDATE

);

​

ALTER TABLE ADIDAS MODIFY OWNER NOT NULL;

ALTER TABLE ADIDAS DROP CONSTRAINT c_c_size;

ALTER TABLE ADIDAS ADD CONSTRAINT c_count CHECK (COUNT BETWEEN 1 AND 1000);

​

문제4.

​

테이블 명 : students -> 1.student_t 테이블명 변경

STUDENT_NO(PK) SURNAME FORENAME -> 2.삭제 FORENAME

20060101 Dickens Charles

20060102 ApGwilym Dafydd

20060103 Zola Emile

20060104 Mann Thomas

20060105 Stevenson Robert

​

3.FORENAME 생성, 4.UNIQUE, 5.NOT NULL

charles

dafydd

emile

thomas

robert

​

-> 6.students테이블 PK 삭제

-> 7.PK 새로 추가

-> 8.STUDENT_NO 에 CHECK (20060001 ~ 20069999) 제약 설정

​

테이블 명 : modules

MODULE_CODE(PK) MODULE_NAME

CM0001 Databases

CM0003 Operating Systems

CM0004 Graphics

​

CM0002 Middle Ware -> 11. 컬럼추가

​

테이블 명: marks

STUDENT_NO MODULE_CODE MARK -> 9.MARKS으로 컬럼명 변경,

10. 컬럼타입 변경 (NUMBER(2,0))

12. marks FK 추가

​

20060101 CM0001 80

20060101 CM0002 65 (*ora-00001 무결성 제약 조건 위배됩니다 : PK 지정된 값이 중복)

20060101 CM0003 50

20060102 CM0001 75

20060102 CM0003 45

20060102 CM0004 70

20060103 CM0001 60

20060103 CM0002 75

20060103 CM0004 60

20060104 CM0001 55

20060104 CM0002 40

20060104 CM0003 45

20060105 CM0001 55

20060105 CM0002 50

​

20060105 CM0006 65 -> 13. 새로운 로우 데이터 추가

​

​

CREATE TABLE STUDENTS(

STUDENT_NO CHAR(8) CONSTRAINT pk_student_no PRIMARY KEY,

SURNAME VARCHAR2(10),

FORENAME VARCHAR2(10));

​

INSERT INTO STUDENTS VALUES(20060101,'Dickens','Charles');

INSERT INTO STUDENTS VALUES(20060102,'ApGwilym','Dafydd');

INSERT INTO STUDENTS VALUES(20060103,'Zola','Emile');

INSERT INTO STUDENTS VALUES(20060104,'Mann','Thomas');

INSERT INTO STUDENTS VALUES(20060105,'Stevenson','Robert');

​

CREATE TABLE modules(

MODULE_CODE CHAR(8) CONSTRAINT pk_module_code PRIMARY KEY,

MODULE_NAME VARCHAR2(20)

);

​

INSERT INTO modules VALUES('CM0001','Databases');

INSERT INTO modules VALUES('CM0003','Operating Systems');

INSERT INTO modules VALUES('CM0004','Graphics');

​

CREATE TABLE marks(

STUDENT_NO CHAR(8),

MODULE_CODE CHAR(8),

MARK NUMBER(2)

);

​

INSERT INTO marks VALUES(20060101,'CM0001',80);

INSERT INTO marks VALUES(20060101,'CM0002',65);

INSERT INTO marks VALUES(20060101,'CM0003',50);

INSERT INTO marks VALUES(20060102,'CM0001',75);

INSERT INTO marks VALUES(20060102,'CM0003',45);

INSERT INTO marks VALUES(20060102,'CM0004',70);

INSERT INTO marks VALUES(20060103,'CM0001',60);

INSERT INTO marks VALUES(20060103,'CM0002',75);

INSERT INTO marks VALUES(20060103,'CM0004',60);

INSERT INTO marks VALUES(20060104,'CM0001',55);

INSERT INTO marks VALUES(20060104,'CM0002',40);

INSERT INTO marks VALUES(20060104,'CM0003',45);

INSERT INTO marks VALUES(20060105,'CM0001',55);

INSERT INTO marks VALUES(20060105,'CM0002',50);

​

1.RENAME students TO student_t;

​

2.ALTER TABLE student_t

DROP COLUMN FORENAME;

​

3.ALTER TABLE student_t ADD (FORENAME VARCHAR2(10));

(ORA-01400: NULL을 ("C##ORA_USER"."STUDENT_T"."STUDENT_NO") 안에 삽입할 수 없습니다)

*이렇게 하면 안됨!!

INSERT INTO student_t(FORENAME) VALUES('Charles');

INSERT INTO student_t(FORENAME) VALUES('dafydd');

INSERT INTO student_t(FORENAME) VALUES('emile');

INSERT INTO student_t(FORENAME) VALUES('thomas');

INSERT INTO student_t(FORENAME) VALUES('robert');

​

*정답

UPDATE student_t

SET forename = 'charles'

WHERE student_no = '20060101';

​

UPDATE student_t

SET forename = 'dafydd'

WHERE student_no = '20060102';

​

UPDATE student_t

SET forename = 'emile'

WHERE student_no = '20060103';

​

UPDATE student_t

SET forename = 'thomas'

WHERE student_no = '20060104';

​

UPDATE student_t

SET forename = 'robert'

WHERE student_no = '20060105';

​

4.ALTER TABLE student_t ADD CONSTRAINT u_forename UNIQUE(forename);

​

5.ALTER TABLE student_t MODIFY forename NOT NULL;

​

6.ALTER TABLE student_t DROP PRIMARY KEY;

​

7.ALTER TABLE student_t ADD CONSTRAINT pk_student_t PRIMARY KEY(STUDENT_NO);

​

8.ALTER TABLE student_t ADD CONSTRAINT c_student_no CHECK(STUDENT_NO BETWEEN 20060001 AND 20069999);

​

9.ALTER TABLE marks RENAME COLUMN MARK TO MARKS;

​

10.ALTER TABLE marks MODIFY COLUMN(MARKS NUMBER(2,0));

​

11.INSERT INTO marks VALUES(CM0002,'Middle Ware');

​

12.ALTER TABLE marks ADD CONSTRAINT fk_marks FOREIGN KEY(MODULE_CODE) REFERANCE modules(MODULE_CODE);

​

13.INSERT INTO marks VALUES(20060105,'CM0006',65);