KITRI 공공데이터 기반 JAVA Programming과정 4일차

-Query :SELECT

        함수

         그룹

         JOIN

       SUBQuery

 

-DML : INSERT INTO  VALUES

       UPDATE

       DELETE

 

-DDL : CREATE

       ALTER

       DROP

       제약조건CONSTRAINT

 

DESC CUSTOMER

 

SELECT *

FROM customer

 

DROP TABLE customer

 

--NULL값을 가질 수 없게 NOT NULL 제약조건을 이용한다.

CREATE TABLE Customer(

  id varchar2(20)  NOT NULL,

  password varchar(20),

  name     varchar2(20),

  phone_no varchar2(20),

  zip_code char(6),

  address  varchar2(50),

  gendar   char(1) default 'M'  NOT NULL ,

  birth_date varchar2(8),

 reg_date  date  default SYSDATE   NOT NULL

)

 

DESC customer

 

--NOT NULL제약조건에 NULL값을 넣으려고 하면 에러표시

INSERT INTO customer(id, password, gendar) VALUES ( null, null, '')

 

--제약조건의 위치상 종류

 1)컬럼레벨 제약조건

    NOT NULL제약조건은 컬럼레벨로만 설정가능

CREATE TABLE Customer(

  id varchar2(20)  NOT NULL PRIMARY KEY(id)            

)

 

 2)테이블레벨 제약조건

CREATE TABLE Customer(

  id varchar2(20)  NOT NULL,

  CONSTRAINT customer_pk PRIMARY KEY(id)            

)

 

--제약조건의 종류

1)NOT NULL : NULL값 불가

2)UNIQUE : 중복불가

3)PRIMARY KEY : NOT NULL + UNIQUE -> NULL값, 중복 불가, 식별자역할PK 한다

4)CHECK : 허용된값외의 값은 불가

5)FOREIGN KEY

 

--기존 테이블 존재할경우 제약조건 추가

ALTER TABLE customer

ADD CONSTRAINT customer_pk PRIMARY KEY(id) 

 

DESC customer

 

INSERT INTO customer(id)

VALUES ('test')

 

SELECT *

FROM customer

 

INSERT INTO customer(id)

VALUES ('test') --아이디중복

 

ALTER TABLE customer

ADD CONSTRAINT customer_ck CHECK (gendar IN ('M', 'F'))

 

INSERT INTO customer(id, gendar) VALUES ('test1', 'X')

 

--제약조건 확인하기

SELECT *

FROM user_constraints

 

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, table_name, search_condition

FROM user_constraints

WHERE table_name = 'CUSTOMER'

 

--컬럼명 바꿔도 제약조건은 남아있다.

ALTER TABLE customer

RENAME COLUMN gendar TO gender

 

INSERT INTO customer(id, gender) VALUES ('test1', 'm')

 

--주문테이블 생성하기

create table order_info(

order_no number, --주문번호

order_id varchar2(10), --주문자아이디

order_time timestamp DEFAULT SYSTIMESTAMP--주문시간

)

 

DESC order_info

 

--order_no컬럼에 primary key제약조건 추가하기

ALTER TABLE order_info

ADD CONSTRAINT order_info_pk PRIMARY KEY(order_no)

 

DESC order_info

 

INSERT INTO order_info(order_no) VALUES (1)

INSERT INTO order_info(order_no) VALUES (1)

INSERT INTO order_info(order_no) VALUES (NULL)

 

SELECT *

 FROM customer

 

--order_id컬럼은  customer의 id컬럼값만 가질 수 있다.

INSERT INTO order_info(order_no, order_id) VALUES (2, 'aaa') --무결성제약조건위배

DELETE FROM order_info WHERE order_id = 'aaa'

 

--order_info테이블의 order_id컬럼은 customer테이블의 id컬럼을 참조한다 (고객만 주문 가능하게 한다.)

ALTER TABLE order_info

ADD CONSTRAINT order_info_fk FOREIGN KEY(order_id) REFERENCES customer(id)

 

INSERT INTO order_info(order_no, order_id) VALUES (2, 'aaa') --무결성제약조건위배

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

 

SELECT *

FROM order_info

 

DESC order_info

 

--CASCADE 옵션으로 FOREING KEY관계무시

DELETE FROM customer WHERE id='test' –무결성 제약조건(HR.ORDER_INFO_FK)이 위배되었습니다 오류발생됨

 

ALTER TABLE order_info

DROP CONSTRAINT order_info_fk

 

ALTER TABLE order_info

ADD CONSTRAINT order_info_fk FOREIGN KEY(order_id) REFERENCES customer(id)

ON DELETE CASCADE

 

DELETE FROM customer WHERE id='test'

SELECT * FROM customer

SELECT * FROM order_info --'test'고객의 주문정보도 제거됨

 

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

 

SELECT *

FROM customer

 

--숫자형위치에 문자형값을 넣으면 숫자형으로 자동형변환됨

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

--002숫자는 숫자2로 저장됨

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

--오류발생

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

 

SELECT *

FROM order_info

 

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

--계좌테이블

테이블이름 : ACCOUNT

컬럼 : account_no char(3)--계좌번호

       balance  number(10)--잔액

제약조건 : Primary Key - account_no

          CHECK - balance  : 잔액은 0이상의 값만 가질 수 있다.

          NOT NULL - account_no,  balance

 

CREATE TABLE account(

account_no char(3) NOT NULL,

balance number(10) NOT NULL

);

 

ALTER TABLE account

ADD CONSTRAINT account_pk PRIMARY KEY(account_no)

 

ALTER TABLE account

ADD CONSTRAINT accout_ck CHECK ( balance >= 0 )

--테이블 생성시 NOT NULL 설정 안했다면

ALTER TABLE account

MODIFY account_no char(3) NOT NULL

 

ALTER TABLE account

MODIFY balance number(10) NOT NULL

 

DESC account

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

--계좌추가

 account_no : '101' balance : 100

 account_no : '202' balance : 100

INSERT INTO account(account_no, balance)

VALUES ('101', 100)

INSERT INTO account(account_no, balance)

VALUES ( 202 , 100)

 

계좌의 잔액변경

  account_no : '101' balance : 기존balance+10

UPDATE account

SET balance = balance+10

WHERE account_no = '101'

 

 

>>  Transaction <<

SESSION 1                                             SESSION 2

ACCOUNT_NO   BALANCE 202             100 101             110

 

 

 

 

 

 

 

 

객체의 종류

 -테이블

 -뷰

 -시퀀스

 -인덱스

 

>> VIEW <<

SELECT * FROM customer

SELECT * FROM order_info

--주문번호, 주문자아이디, 주문자이름, 주문일자, 우편번호, 연락처, 주문자주소를 출력하시오.

SELECT order_no, order_id, name, order_time, zip_code, phone_no, address

FROM customer c JOIN order_info oi ON c.id = oi.order_id

 

CREATE VIEW order_info_view

AS

SELECT order_no, order_id, name, order_time, zip_code, phone_no, address

FROM customer c JOIN order_info oi ON c.id = oi.order_id

 

SELECT *

FROM order_info_view

 

--VIEW는 ALTER구문이 제공되지 않음

--기존뷰의 구조를 바꾸려면 CREATE OR REPLACE를 사용

CREATE  VIEW order_info_view

AS

SELECT order_no, order_id, name, order_time, zip_code, phone_no, address, gender

FROM customer c JOIN order_info oi ON c.id = oi.order_id  --오류발생

 

DROP VIEW order_info_view

--VIEW용 데이터 딕셔너리

SELECT *

FROM user_views

--뷰의 구조확인

DESC order_info_view

 

--하나이상의 테이블이 조인된 경우에는 insert할 수 없다

INSERT INTO order_info_view(order_no, order_id, name)

VALUES (99, 'test2', '테스트2'); --오류발생

 

CREATE OR REPLACE VIEW customer_view

AS

SELECT id, password, name, gender

FROM customer

 

INSERT INTO customer_view VALUES ('test3', 'ptest3', '테스트3', 'F')—성공

 

 

 

======================테이블 생성하기======================

Prod_price컬럼은 0원이상의 값만 갖는다

ORDER_LINE 테이블

Order_info_no컬럼은 order_info테이블의 order_no컬럼을 참조한다

Order_prod_no컬럼은 product테이블의 prod_no컬럼을 참조한다

quantity컬럼은 1이상의 값만 갖는다

--product 테이블 생성

CREATE TABLE PRODUCT(

  PROD_NO   VARCHAR2(4) NOT NULL,

  PROD_NAME VARCHAR2(10) NOT NULL,

  PROD_DETAIL VARCHAR2(10),

  PROD_PRICE NUMBER(10, 0),

  PROD_MDATE VARCHAR2(8),

  REG_DATE DATE DEFAULT SYSDATE

)

 

--primary key 생성

ALTER TABLE product

ADD CONSTRAINT product_pk PRIMARY KEY(PROD_NO)

 

--prod_price컬럼은 0원이상의 값만 갖는다.

ALTER TABLE product

ADD CONSTRAINT product_ck CHECK(prod_price >= 0)

 

SELECT *

FROM PRODUCT

 

DESC product

 

--order_line 테이블 생성

CREATE TABLE ORDER_LINE(

  order_info_no  number NOT NULL,

  order_line_no  number(2, 0) NOT NULL,

  order_prod_no  varchar2(4) NOT NULL,

  quantity       number(2, 0) default 1

)

drop table order_line

 

--primary key 생성

ALTER TABLE order_line

ADD CONSTRAINT order_line_pk PRIMARY KEY(order_info_no, order_line_no)

 

--order_info_no컬럼은 order_info테이블의 order_info_no컬럼을 참조한다.

ALTER TABLE order_line

add constraint order_line_no_fk FOREIGN KEY(order_info_no) references order_info(order_no)

 

--order_prod_no컬럼은 product테이블의 prod_no컬럼을 참조한다.

ALTER TABLE order_line

ADD CONSTRAINT order_line_fk FOREIGN KEY(order_prod_no) references product(prod_no)

 

--quantity컬럼은 1이상의 값만 갖는다.

ALTER TABLE order_line

add constraint order_line_ck CHECK(quantity >= 1)

 

SELECT *

FROM order_line

 

DESC order_line

 

order_line테이블에서 참조하는거 2개 안함.