[SQL] 20190604

DDL
- 테이블, 뷰, 시퀀스, 인덱스

* CREATE
객체를 생성

CREATE TABLE 테이블 명 (
칼럼명1 DATATYPE [DEFAULT 형식],
칼럼명2 DATATYPE [DEFAULT 형식],
...);

- CHAR(s) 고정길이 문자열 (최대 2000바이트)
- VARHACR2(s)가변길이 문자열 (최대 4000바이트)
- NUMBER(p,s) 수치 (p : 1~38, s : -84~127)
- DATE 날짜와 시각

- NUMBER PRIMARY KEY
- NUMBER CONSTRAINT dept_pk_deptno PRIMARYKEY

* ALTER
객체를 수정

* DROP
객체를 삭제


CREATE TABLE account (
account_no NUMBER(4) PRIMARY KEY,
account_date DATE,
code VARCHAR2(14),
withdrawer NUMBER(8),
deposit NUMBER(10),
balance NUMBER(12),
branch_code CHAR(8)
);

- 다음 테이블(nike)을 만드시오.
id : 문자 10자리
style :
color :
owner :
c_size : XS, S, M, L, XL, XXL만 입력 가능
gender : M, F만 입력 가능
count :
m_date : DATE

CREATE TABLE nike (
nike_id CHAR(10) 
CONSTRAINT nike_no PRIMARY KEY,
nike_style VARCHAR2(20),
color VARCHAR2(20),
nike_owner VARCHAR2(12),
c_size CHAR(3),
gender CHAR(1),
nike_count NUMBER(10),
m_date DATE
);

DROP TABLE ACCOUNT;
RENAME STUDENT_TEST TO STUDENTS;


ALTER TABLE 테이블명 ADD (칼럼 칼럼데이터타입);
ALTET TABLE player_t
ADD (comments VARCHAR2(255));

// 컬럼 이름 변경
ALTER TABLE RENAME COLUMN STU_NO TO STU_NUMBER;

// 컬럼 추가
ALTER TABLE STU_TEST
ADD (STU_BMI NUMBER(5, 2));

// 컬럼 타입 변경
ALTER TABLE STU_TEST
MODIFY (STU_BMI VARCHAR2(20));

// 컬럼 삭제
ALTER TABLE STU_TEST
DROP COLUMN STU_BMI;

// 제약조건 추가
ALTER TABLE STU_TEST
ADD CONSTRAINTS pk_stu_test PRIMARY KEY (STU_NUMBER);

// 제약조건 삭제
ALTER TABLE STU_TEST
DROP CONSTRAINT pk_stu_test;

ORACLE에서 제공하는 제약조건
NOT NULL : NULL 값의 입력을 금지
UNIQUE 
CHECK : 입력할 수 있는 값의 범위를 제한
DEFAULT : 데이터를 입력하지않아도 지정된 값이 기본으로 입력됨

제약조건은 USER_COMSTRAINTS 뷰를 통해서 확인 할 수 있음.
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP2';

UNIQUE 제약 조건 예제
- 컬럼에 UNIQUE 제약조건 생성
ALTER TABLE STU_TEST
ADD CONSTRAINT uni_stu_number UNIQUE (STU_NUMBER);

CHECK 제약 조건 예제
ALTER TABLE emp2 ADD CONSTRAINT emp2_ck_comm
CHECK (comm >= 1 AND comm <= 100);

DEFAULT 제약 조건 예제
데이터를 입력 하지 않아도 지정된 값이 기본으로 입력됨.
HIREDATE 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다.
CREATE TABLE emp3 (
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(40),
MGR NUMBER,
HIREDATE DATE DEFAULT SYSDATE);

CREATE TABLE EMP3 (
EMPNO NUMBER CONSTRAINT pk_empno PRIMARY KEY,
ENAME VARCHAR2(20) CONSTRAINT nn_ename NOT NULL,
JOB VARCHAR2(20) CONSTRAINT uni_job UNIQUE,
MGR NUMBER CONSTRAINT ck_mgr CHECK (MGR BETWEEN 1 AND 9999),
HIREDATE DATE DEFAULT SYSDATE
);

2. 다음 테이블(adidas)을 만드시오.
id : 기본키
style : unique
color : 색깔 입력은 항상 존재(not null)
owner : 제약 조건 없음
c_size : XS, S, M, L, XL, XXL만 입력 가능(check)
gender : M, F만 입력 가능(check)
count : 제약 조건 없음
m_date : default sysdate

CREATE TABLE adidas(
adidas_id CHAR(10) 
CONSTRAINT adidas_no PRIMARY KEY,
adidas_style VARCHAR2(20) CONSTRAINT ad_style UNIQUE,
color VARCHAR2(20) CONSTRAINT ad_color NOT NULL,
adidas_owner VARCHAR2(12),
c_size CHAR(3) CONSTRAINT ad_size CHECK(c_size IN ('XS', 'S', 'M', 'L', 'XL', 'XXL')),
gender CHAR(1) CONSTRAINT ad_gender CHECK(gender IN ('M', 'F')),
adidas_count NUMBER(10),
m_date DATE DEFAULT SYSDATE
);

2. 다음 테이블(adidas)을 만드시오.
id : 기본키
style : unique
color : 색깔 입력은 항상 존재(not null)
owner : 제약 조건 없음 -> not null 추가
c_size : XS, S, M, L, XL, XXL만 입력 가능(check)  -> check 조건 삭제
gender : M, F만 입력 가능(check)
count : 제약 조건 없음 -> check 조건 추가 (0<=count<=1000)
m_date : default sysdate -> unique 조건 추가

CREATE TABLE adidas(
adidas_id CHAR(10) 
CONSTRAINT pk_adidas PRIMARY KEY,
adidas_style VARCHAR2(20) CONSTRAINT ad_style UNIQUE,
color VARCHAR2(20) CONSTRAINT nn_color NOT NULL,
owner VARCHAR2(12),
c_size CHAR(3) CONSTRAINT ck_c_size CHECK(c_size IN ('XS', 'S', 'M', 'L', 'XL', 'XXL')),
gender CHAR(1) CONSTRAINT ck_gender CHECK(gender IN ('M', 'F')),
adidas_count NUMBER(10),
m_date DATE DEFAULT SYSDATE
);

- not null 추가
ALTER TABLE adidas
MODIFY owner NOT NULL;

ALTER TABLE ADIDAS MODIFY owner CONSTRAINT nn_owner NOT NULL;


- check 조건 삭제
ALTER TABLE adidas
DROP CONSTRAINT ck_c_size;

- check 조건 추가 (0<=count<=1000)
ALTER TABLE adidas
ADD CONSTRAINT ck_count CHECK (adidas_count >= 0 AND adidas_count <= 1000);

- unique 조건 추가
ALTER TABLE adidas
ADD CONSTRAINT uni_date UNIQUE (m_date);


3. 문제
테이블 명 : students -> 1.student_t 테이블명 변경
STUDENT_NO(PK)   SURNAME        FORENAME ->2.삭제 FORENAME ->3.FORENAME 생성 4. UNIQUE 5. NOT NULL
20060101              Dickens            Charles         
20060102              ApGwilym         Dafydd          
20060103              Zola                 Emile            
20060104              Mann               Thomas          
20060105              Stevenson         Robert           
->6. students 테이블 PK 삭제 -> 7.PK 새로 추가 -> 8.STUDENT_NO에 CHECK  (20060001 ~ 20069999) 제약 설정
 
테이블 명 : modules
MODULE_CODE(PK)   MODULE_NAME
CM0001                   Databases
CM0002                   Middle Ware -> 11.컬럼추가, marks FK 추가
CM0003                   Operating Systems
CM0004                   Graphics
테이블 명: marks
STUDENT_NO  MODULE_CODE   MARK -> 9. MARKS으로 컬럼명 변경, 10. 컬럼타입 변경 (NUMBER(2,0))
20060101        CM0001                80 -> 12. 새로운 로우 데이터 추가
20060101        CM0002                65
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



->
CREATE TABLE students(
student_no CHAR(9) 
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(10) CONSTRAINT pk_code PRIMARY KEY,
module_name VARCHAR2(20)
);

INSERT INTO modules VALUES('CM0001', 'Databases');
INSERT INTO modules VALUES('CM0002', 'Middle Ware');
INSERT INTO modules VALUES('CM0003', 'Operating Systems');
INSERT INTO modules VALUES('CM0004', 'Graphics');

CREATE TABLE marks(
student_no CHAR(9),
module_code CHAR(10),
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. student_t 테이블명 변경
RENAME students RENAME TO student_t;

2. FORENAME 삭제
ALTER TABLE student_t
DROP COLUMN forename;

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

UPDATE student_t SET forename = 'charles' WHERE student_no = 20060101;
UPDATE student_t SET forename = 'charles' WHERE student_no = 20060101;
UPDATE student_t SET forename = 'charles' WHERE student_no = 20060101;
UPDATE student_t SET forename = 'charles' WHERE student_no = 20060101;
UPDATE student_t SET forename = 'charles' WHERE student_no = 20060101;

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

5. NOT NULL
ALTER TABLE student_t
MODIFY forename CONSTRAINT nn_forename NOT NULL;

ALTER TABLE student_t modify forename not null;

6. students 테이블 PK 삭제
ALTER TABLE student_t DROP PRIMARY KEY;

select *
from user_constraints
where table_name = 'STUDENT';

7. PK 새로 추가
ALTER TABLE student_t ADD CONSTRAINT pk_student_no PRIMARY KEY (student_no);

8. STUDENT_NO에 CHECK (20060001 ~ 20069999) 제약 설정
ALTER TABLE student_t
ADD CONSTRAINT ck_student_no CHECK (student_no >= 20060001 AND student_no <= 20069999);

9. MARKS으로 컬럼명 변경
RENAME COLUMN mark TO marks;

10. 컬럼타입 변경 (NUMBER(2,0))
ALTER TABLE marks
MODIFY (marks NUMBER(2,0));

11.컬럼추가, marks FK 추가
INSERT INTO modules VALUES ('CM0002', 'Middle Ware');
ALTER TABLE marks ADD CONSTRAINT mod_fk
FOREIGN KEY (module_code) REFERENCES modules(module_code);

marks테이블의 module_code 포린키
= modules 테이블의 module_code랑 값이 같아야함

12. 새로운 로우 데이터 추가
INSERT INTO marks VALUES(20060105, 'CM0006', 65);


ALTER TABLE ENROL ADD CONSTRAINT fk_stu_no
FOREIGN KEY (stu_no) REFERENCES STUDENT(stu_no);

insert into enrol
values ('101', '11111111', 100);
-> 에러

alter table enrol
drop constraint fk_stu_no;

insert into enrol
values ('101', '11111111', 100);
-> 실행됨