22일차 SQL
22일차
220830
평가 대비
Integrity : 무결성 violation : 위반, 위배 constraints : 제약 조건 insufficient : 불충분한 privileges : 권한
DDL (Data Definition Language; 데이터 정의어)
객체를 수정, 삭제, 생성하는 명령어
create / drop / alter
Create
객체를 생성함
CREATE TABLE table_name(
-- 제약 조건들
-- 컬럼명 자료형 제약조건
)
SQL 자료형
- number; 숫자
- varchar; 문자열
- 자료형의 크기를 꼭 기입해주어야 함
varchar(20) - 5바이트를 저장하게 될 시 남은 15바이트를 자동으로 줄여버림
- 일반적인 문자열 → char() : 하나의 컬럼에 값이 수시로 바뀌는 경우
- 자료형의 크기를 꼭 기입해주어야 함
- date, timestamp; 시간 자료형
제약 조건 (Constraint)
입력되야 할 데이터의 조건을 주는 문법
Constraint
시스템 카탈로그로 부터 유저가 생성한 제약조건 확인하기
select * from user_constraints;
제약 조건 명 지정하기
id number constraint id_pk primary key; -- 제약 조건 명 설정 id_pk
-- 여러 제약조건이 붙어도 똑같이 만들 수 있다.
NOT NULL
해당 컬럼의 값이 NULL 이 되지 못하게 함
column1 varchar(20) NOT NULL -- 비울 수 없어용
-- cannot insert NULL into ("KH"."CAFE_MENU"."PPRICE") error
-- 계정. 테이블. 컬럼
PRIMARY KEY (기본키, 식별자 컬럼, PK)
기본키
- 한 테이블에서 한 개의 컬럼에만 설정가능
- 개체 무결성 제약조건을 가짐
- 개체를 식별하기 위해 오류가 없도록 하기 위한 제약조건
- 기본키는 NULL 값을 가져선 안되고, 중복될 수 없음
- 최소성을 만족해야 함
- 최소성이란, 키를 구성하는 속성들 중 가장 최소로 필요한 속성들로만 키를 구성하는 성질을 말한다. 쉽게 말해, 키를 구성하고 있는 속성들이 진짜 각 튜플을 구분하는 데 꼭 필요한 속성들로만 구성되어 있는지를 의미한다
pk_column number primary key
-- 컬럼명 자료형 primary key
Sequence
특정 단일 값을 규칙에 따라 증가시키며 기억하는 객체
CREATE sequence tb_name_seq
START WITH 1001 -- 시작지점
increment BY 1 -- 증가값
nomaxvalue -- 최댓값
nocache; -- 캐시 사용 안함 (성능이슈)
select TB_NAME_SEQ.NEXTVAL from dual; -- 다음값을 가져다줘
-- 1001 리턴후 시퀀스는 ++
-- 시퀀스는 값을 리턴해준 후 값을 1 증가시킨다.
select TB_NAME_SEQ.CURRVAL from dual;
-- 시퀀스의 현재값 확인
사용 예
insert into cafe_menu
values(cafe_menu_seq.nextval, 'Cafe_Mocha', 3500, 'N');
-- PK 값에 시퀀스를 넣어줌
Foreign key (외래 키, 참조 키)
- 기본키(Primary Key) 값이 있는테이블을 다른 테이블 간 연결을 설정할때 사용된다.
- PK(Primary Key 기본키)를 가지고 오는 테이블이 외래키(FK Foreign Key)를 가지게 된다.
- 기본키(PK)를 가지고 있는 테이블(부모테이블)이 생성되어야, 외래키(FK)를 가지는 테이블(자식테이블)을 생성할 수 있다.
- Foreign Key의 이름은 중복되서는 안된다.
참조 무결성
참조 무결성(referential integrity)은 관계 데이터베이스 관계 모델에서 2개의 관련 있던 관계 변수(테이블) 간의 일관성(데이터 무결성)을 말한다
create table sales_record( -- 자식 테이블 생성
sid number primary key, -- 거래내역번호
pid references cafe_menu(pid), -- 외래키 , 참조키(다른 테이블의 기본 키)
sdate timpstamp default sysdate not null -- 기본값으로 현재 시간 설정
);
-------------------------------------------------
create table sales_record(
sid number primary key, -- 거래내역번호
pid number not null,
constraint fk_pid foreign key (pid) references cafe_menu (pid)
--CONSTRAINT [FK명] foreign key([FK가 될 컬럼명]) references [PK가 위치하는 테이블] ([PK컬럼명])
);
-
개발 단계에서의 foreign key
데이터 수정 삭제의 제한이있기 때문에 개발단계에서는 외래키를 설정 하지 않는다.
개발 막바지 단계에서 외래키 설정을 해줍니다.
UNIQUE
중복성을 제약하는 제약 조건
- PK 와 다르게 NULL 값을 허용한다.
- null 값을 제약 하려면
not null을 함께 사용해준다.
column_name varchar(20) unique
-- unique constraint violated
Check
입력 데이터 값을 제한하는 제약조건
YN_Column char(1) check (YN_Column in ('Y', 'N')) not null
Drop
객체를 삭제함
DROP TABLE table_name;
테이블의 키가 참조되고 있다면 테이블은 드랍 될 수 없음. CASCADE 를 통해 연쇄 삭제 가능
Alter
객체를 수정하는 명령어
alter table table_name add(컬럼명, 컬럼 속성, 제약 조건); -- 새로운 컬럼 추가
-- default not null 속성을 주어야 기존값과 충돌하지 않으며 에러가 나지 않는다.
alter table table_name add constraint constraint_alies 제약조건(컬럼명); -- 새 제약조건 추가
alter table table_name modify(컬럼명, 컬럼 속성, 제약 조건); -- 기존 속성 변경
alter table table_name rename column column_title to changed_column_name; -- 기존 컬럼의 이름 변경
alter table table_name drop column column_title; -- 기존 컬럼 삭제
alter table table_name drop constraint column_constraint; -- 컬럼 제약조건 삭제
DML (Data Manipulation Language; 데이터 조작어)
Insert
해당 컬럼에 값을 삽입함.
insert into table_name values()
-- 모든 컬럼 값을 다 입력할 때 문법
insert into table_name (column1, column3) values(1002, 1003)
-- 일부 컬럼 값을 선택적으로 입력할 때 문법
-- 외래키 참조 삽입
insert into sales_record values(sales_record_seq.nextval, FK , default);
Delete
튜플(행)을 삭제함
Delete from table_title where 조건;
고유키 또는 유니크 키가 참조되고 있다면 DELETE 할 수 없음. 종속옵션을 통해 삭제 가능
Update
데이터의 속성을 갱신하는 작업.
UPDATE table_name SET column_name = '변경값' where 조건;
-- 변경할 데이터는 콤마(,) 로 여러 값을 변경할 수 있다.
종속 옵션
CASCADE
연쇄적인 옵션을 넣어줍니다.
pid references cafe_menu(pid) on delete cascade -- 부모키 삭제 시 종속된 키 연쇄 삭제
pid references cafe_menu(pid) on update cascade
SET NULL
부모키 삭제 및 변경시 자식키 옵션에서 해당 키 값을 가진 속성을 NULL 로 변경
pid references cafe_menu(pid) on delete set null -- 부모키 삭제 시 NULL 값으로 변경
DICTIONARY (데이터 사전, 시스템 카탈로그)
데이터 사전(Data Dictionary)은 데이터베이스 관리 시스템(Database Management System, 이하 DBMS)을 효율적으로 사용하기 위해 데이터베이스에 저장된 정보를 요약한 것이다
오라클 내의 저장된 시스템 테이블로, 사용자가 내린 쿼리 명령에 의해서 발생하는 부가적인 내용을 저장하는 테이블
ORACLE 의 DICTIONARY
DICTIONARY 의 권한
TCL(Transaction Control Language; 트랜잭션 제어 언어)
Transaction(트랜잭션, Tx)
| 데이터베이스의 상태를 변화시키기 해서 수행하는 작업의 단위 |
- 트랜잭션의 특징
commit
트랜잭션을 DB에 저장시키는 작업
commit;
트랜잭션 자동 커밋
DML 을 제외한 language 를 하게 된다면 자동으로 커밋이 되어버림 (DDL)
Rollback
현재 진행중인 트랜잭션을 취소하고 가장 커밋이 완료된 시점으로 DB의 상태를 되돌림
rollback;
Savepoint
분기점을 생성한다.
savepoint abc;
rollback abc;
한 계정이 트랜잭션중에 commit이 완료 될 때 까지 다른 계정은 대기상태가 되어 버린다.
VIEW
뷰(view)는 데이터베이스에 존재하는 일종의 가상 테이블을 의미합니다. 이러한 뷰는 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않습니다.
특정 테이블 (or 테이블 들) 에서 추려낸 정보로 만들어지는 하나의 링크형 임시 테이블 only read
create view view_name
as
조건문(select 문) from 테이블;
-- view 를 하기 위한 권한 부여
-- insufficient privileges errors
grant create view to 계정명;
DCL (Data Control Language; 데이터 제어어)
Grant
각종 권한을 부여하는 명령어
Grant 각종 권한 on 테이블 to 계정명;
Revoke
권한 부여를 취소하는 명령어
revoke
실습
sales_record_seq 테이블의 sid 컬럼용 sequence 생성하기
create sequence sales_record_seq
start with 1
increment by 1
nomaxvalue
nocache;