DML

CREATE [객체 생성]
ALTER [객체구조 변경]
DROP [객체구조 변경]
TRUNCATE [모든 자료 제거]

테이블 생성(Create Table)

컬럼 생성(Insert Into)

INSERT INTO 테이블 명(컬럼 명) VALUES (입력할 데이터)

제약 조건 설정 방법

가독성을 위해 주로 컬럼레벨 조건을 사용한다.

1) 테이블 레벨 제약 조건 설정 방법(P398)

복합키로 기본 키를 지정할 경우 사용한다.
ALTER TABLE로 제약 조건을 추가할 때

=> 테이블을 추가할때 한번에 제약조건을 지정한다.

CREATE TABLE ab(

ab1 number(1) Constraint coco_pkpk PRIMARY KEY(ab1)

//컬럼 레벨

CREATE TABLE ab(

ab1 number(1),

CONSTRAINT coco_pk PRIMARY KEY(ab1)

//테이블

ALTER TABLE ab

ADD CONSTRAINT coco_pk PRIMARY KEY(ab1) //테이블

ALTER TABLE ab

MODIFY ab1 CONSTRAINT coco_pk PRIMARY KEY // 컬럼

2) 컬럼 레벨 제약 조건 설정 방법(P368)

NOT NULL은 컬럼 레벨 제약조건으로만 추가가 가능하다

제약조건을 제거할때, 제약 조건명보다는 제약조건의 유형

(PRIMARY KEY, CHECK 와 같은 유형을 입력한다)

CREATE

ADD MODIFY ~~~~

=> 이미 존재하는 테이블을 추가, 삭제, 변경하는 방식을 말한다.

주 식별자 지정

PRIMARY KEY

DML

INSERT [자료추가]
UPDATE [자료변경]
DELETE [자료삭제]
MERGE [자료 병합]

책정리

p258,264,368,374,383,395,398,270~281,290~299,303,311,323

예시

CREATE TABLE t1(

cchar char(10),

cvchar varchar2(10),

cnum number,

cnum1 number(10), => number(10,0) 과 같다.

cnum2 number(10, 2),

cdate DATE

)

INSERT INTO t1(cchar, cvchar) VALUES ('abc','abc' )

SELECT * FROM t1

SELECT LENGTH(cchar), LENGTH(cvchar) FROM t1

INSERT INTO t1(cnum, cnum1, cnum2)

VALUES (1234.567, 1234.567, 1234.567)

INSERT INTO t1(cdate) VALUES('16/03/28')

INSERT INTO t1(cdate) VALUES(sysdate)

SELECT TO_CHAR(cdate, 'YYYY-MM-DD HH24:MI:SS') FROM t1

--서브쿼리로 테이블 생성하기

1) 테이블 구조와 행 모두 복사 (기본키는 복사되지 않는다)

CREATE TABLE t2

SELECT * FROM employees

SELECT * FROM t2

// 서브쿼리를 수정하여 원하는 부분만 생성할 수 있다.

2) 테이블 구조만 복사

CREATE TABLE t3

SELECT employee_id, first_name FROM employees WHERE 1=0

DESC employees // 원본 테이블

DESC t3 // 복사본 테이블

--------------------------------------------------------------------------

--고객(customer) 테이블 생성

아이디, 비밀번호, 이름, 우편번호, 주소, 가입일자, 상태

아이디 : id varchar2(10)

비밀번호 : password varchar2(10)

이름 : name varchar2(20)

우편번호 : zipcode char(5)

주소 : address varchar2(60)

가입일자 : reg_dt date

상태 : status char(1)

CREATE TABLE customer

(

id varchar2(10),

password varchar2(10),

name varchar2(20)

)

INSERT INTO customer(id) VALUES('a')

INSERT INTO customer(password) VALUES('b')

SELECT * FROM customer

// 무결성 제약 조건의 필요성

(아이디가 중복되게 등록되고, 아이디가 없음에도 비밀번호가 추가되는 에러를 방지시킴)

DELETE FROM customer

--제약조건 설정방법

1)테이블 레벨 제약 조건 설정방법

문법) CONSTRAINT 제약조건명 제약조건종류(컬럼)

CREATE TABLE customer

(

id varchar2(10),

password varchar2(10),

name varchar2(20)

CONSTRAINT customer_id PRIMARY KEY(ID)

)

2)컬럼레벨 제약조건 설정방법

(

id varchar2(10) CONSTRAINT 제약조건명 제약조건종류,

password varchar2(10),

name varchar2(20)

)

--------------------------------------------

*NOT NUL제약조건은 컬럼레벨로만 설정할 수 있다

--테이블 구조 변경하기

ALTER TABLE 테이블 명

[ADD 컬럼/ MODIFY 컬럼/ DROP컬럼] -> 추가,수정,삭제

ADD CONSTRAINT 제약조건명 제약조건종류(컬럼)

**PRIMARY KEY 제약조건 추가**

ALTER TABLE customer

ADD CONSTRAINT customer_id_pk PRIMARY KEY(id)

DELETE FROM customer

**NOT NULL 제약조건 추가**

ALTER TABLE customer

MODIFY password CONSTRAINT customer_password_nn NOT NULL

DESC customer

INSERT INTO customer(id) VALUES ('A') -- ORA-01400: NULL을 ("HR"."CUSTOMER"."PASSWORD") 안에 삽입할 수 없습니다

**기존 테이블에 컬럼 추가**

ALTER TABLE customer

ADD zipcode char(5)

ALTER TABLE c

ADD address varchar2(60)

ADD reg_dt date

desc customer

========================== 오후======================

**컬럼 삭제하기

ALTER TABLE customer

DROP COLUMN reg_dt

**컬럼의 기본값(default value)

ALTER TABLE customer

ADD reg_dt date default SYSDATE NOT NULL

ALTER TABLE customer

ADD status char(1)

--가입상태인 경우 NULL, 탈퇴인 경우 'X', 블랙리스트인 경우 'B'

**CHECK제약 조건 추가**

문법) ADD CONSTRAINT 제약 조건명 CHECK (컬럼 조건)

ALTER TABLE customer

ADD CONSTRAINT customer_status_chk CHECK (status IN ('X', 'B'))

desc customer

SELECT * FROM customer

INSERT INTO customer(id, password, status) VALUES ('a', 'b', 'c')--ORA-02290: 체크 제약조건(HR.CUSTOMER_STATUS_CHK)이 위배되었습니다

---------------------------------------------------------------------------

INSERT INTO customer(id,password, name) VALUES ('test','test','테스트')

SELECT * FROM customer

SELECT * FROM user_constraints

---------------------------------------------------------------------------

**주문정보 테이블(order_info) 생성하기

주문번호 order_no number

주문자 order_id varchar2(10)

주문일자 order_dt date

<조건> 주문번호는 주식별자 역할, 주문자는 반드시 입력, 주문일자는 SYSDATE가 기본값

CREATE TABLE order_info

(

order_no number CONSTRAINT order_info_no_pk PRIMARY KEY,

order_id varchar2(10) CONSTRAINT order_info_id_nn NOT NULL,

order_dt date default SYSDATE CONSTRAINT order_dt_sysdt NOT NULL

)

DESC order_info

INSERT INTO order_info(order_no, order_id)VALUES (1, 'test')

INSERT INTO order_info(order_no, order_id)VALUES (2, 'test')

INSERT INTO order_info(order_no, order_id)VALUES (3, 'aa')

SELECT order_no, order_id, TO_CHAR(order_dt, 'YY/MM/DD HH24:MI:SS')

FROM order_info

DELETE FROM order_info

SELECT * FROM order_info

**주문의 order_id 컬럼값은 고객의 id값을 참조한다.

FOREIGN KEY 제약 조건

ALTER TABLE order_info

ADD CONSTRAINT order_info_id_fk

FOREIGN KEY(order_id) REFERENCES customer(id)

---------------------------------------------------------------------------

CREATE TABLE product

(

prod_no varchar2(10),

prod_name varchar2(60),

prod_price number(7)

)

desc product

**product 테이블에 제조일자 컬럼(컬럼명 prod_mf_dt CHAR(6))을 추가하시오.

<조건>

prod_no 컬럼은 주 식별자 역할,

prod_name 컬럼은 반드시 입력,

prod_price 컬럼은 0이상의 값만 입력

ALTER TABLE product

ADD prou_mf_dt CHAR(6)

ADD CONSTRAINT prod_no_pk PRIMARY KEY(prod_no)

MODIFY prod_name CONSTRAINT product_name_nn NOT NULL

ADD CONSTRAINT prod_pr_chk CHECK (prod_price >= 0)

desc product

--------------------------------------------------------------------------------------------------

행추가

상품정보 추가 [prod_no : 'R001', prod_name : '아메리카노', prod_price : 1000]

['R002', '라떼','1500']

['R003', 'ICE아메리카노','1500']

INSERT INTO product(prod_no, prod_name, prod_price) VALUES ('R001','아메리카노','1000')

INSERT INTO product(prod_no, prod_name, prod_price) VALUES ('R002','라떼','1500')

INSERT INTO product(prod_no, prod_name, prod_price) VALUES ('R003', 'ICE아메리카노','1500')

SELECT * FROM PRODUCT

UPDATE문

**행수정

모든 상품의 상품번호에 시작문자'R'을 'C'로 변경하시오.

UPDATE product

SET prod_no = REPLACE(SUBSTR(prod_no,1,1),'R','C')||SUBSTR(prod_no,2)

UPDATE product

SET prod_no = 'C'||SUBSTR(prod_no,2)

WHERE SUBSTR(prod_no,1,1)='R'

**상품가격을 10% 인상한다.

UPDATE product

SET prod_price = prod_price+(prod_price * 0.1)

**1500원 이상의 상품들만 상품가격을 5%인하 하시오.ㄱ

UPDATE product

SET prod_price = prod_price-(prod_price * 0.05)

WHERE prod_price >= 1500

**상품명에 '아메리카노'를 포함하는 상품의 가격만 1% 인하하시오.

UPDATE product

SET prod_price = prod_price-(prod_price * 0.01)

WHERE prod_name LIKE '%아메리카노%'

SELECT * FROM PRODUCT

퀴즈

1.**상품테이블의 모든 행을 삭제하시오.

DELETE FROM product

2.**카테고리테이블을 생성하시오

테이블명: category

컬럼 : category_no char(1), category_name varchar2(20)

조건 : category_no컬럼은 주식별자역할, category_name은 반드시 입력

CREATE TABLE category

(

category_no char(1) CONSTRAINT category_no_pk PRIMARY KEY,

category_name varchar2(20) NOT NULL

)

3.**상품테이블에 컬럼을 추가하시오.

컬럼 : category_no char(1)

ALTER TABLE product

ADD category_no char(1)

4.**상품 테이블에 제약 조건 추가하시오

category 테이블의 category_no컬럼을 상품 테이블의 category_no컬럼이 참조

ALTER TABLE product

ADD CONSTRAINTS category_no_fk

FOREIGN KEY (category_no) REFERENCES category(category_no)

동일 답

MODIFY category_no CONSTRAINTS produtct_category_no_fk REFERENCES category(category_no)

5.**category 테이블에 행 추가하시오

category_no category_name

'C' '커피'

'J' '주스'

'F' '푸드'

INSERT INTO category(category_no, category_name) VALUES ('C','커피')

INSERT INTO category(category_no, category_name) VALUES ('J','주스')

INSERT INTO category(category_no, category_name) VALUES ('F','푸드')

6.**상품테이블에 행 추가하시오

[prod_no:'C001', prod_name:'아메리카노', prod_price:1000, category_no:'C' ]

[ 'C002' '라테', 1500, category_no:'C' ]

[ 'C003' 'ICE아메리카노', 1500, category_no:'C' ]

[ 'J001' '오렌지주스', 2000, category_no:'J' ]

[ 'J002' '딸기주스', 2000, category_no:'J' ]

[ 'F001' '녹차아이스크림', 2000, category_no:'F' ]

[ 'F002' '초코케익', 5000, category_no:'F' ]

[ 'F003' '클럽샌드위치', 4000, category_no:'F' ]