[오답노트] 2-3. 관리구문

🎈제약조건의 종류
1. primary key(기본키)
2. unique key(고유키)
3. not null
4. check
5. foreign key(외래키)

🎈테이블 칼럼에 대한 정의 변경
1. oracle
ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터유형 [DEFAULT 식 : ADD CONSTRAIN 제약조건명 PRIMARY KEY] [NOT NULL], 컬럼명 2...)
- REFERENCE 참조할 외부키 ON DELET CASCADE : 부모테이블에서 외부키가 삭제되면 같이 삭제
- REFERENCE 참조할 외부키 ON DELET SET NULL : 부모테이블에서 외부키가 삭제되면 널값으로 처리
[delete]의 경우
- casecade : 부노 삭제시 같이 삭제
- set null : 부모 삭제시 널값
- set default : 부모 삭제시 해당 필드 default
- restrict : 자녀 테이블에 PK 값이 없는 경우만 부모삭제 가능
- not action : 참조무결성을 위반하는 삭제/수정 불가
[insert]의 경우
- automatic : 부모테이블에 PK가 없는 경우 PK먼저 생성 후 자녀테이블에 입력
- set null : 부모테이블에 PK가 없는 경우자녀값엔 널값
- set default : 부모테이블에 PK가 없는 경우 자녀값엔 기본값
- dependent : 부모테이블에 PK가 없는 경우 데이터 입력 불가(존재할때만 입력가능)
- not action : 참조무결성을 위반하는 삭제/수정 불가
 
2. SQL SERVER
ALTER TABLE 테이블 명 ALTER (컬럼명1 데이터유형 [DEFAULT 식] [NOT NULL])

🎈테이블 생성시 주의사항
1. 테이블명을 객체를 의미할 수 있는 이름을 사용하고 단수형을 권고한다
2. 테이블명은 다른 테이블의 이름과 중복되지 않는다
3. 한 테이블 내에서는 컬럼명이 중복되지 않는다
4. 테이블의 이름을 지정하고 각 컬럼들은"( )"괄호로 묶어 지정한다
5. 각 컬럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 세미콜론 ; 으로 끝난다
칼럼에 대해서는 다른 테이블까지 고려하여 데이터 베이스 내에서는 일관성있게 사용하는 것이 좋다(데이터 표준화)
6. 칼럼 뒤에 데이터유형은 꼭 지정되어야 한다
7. 테이블명과 칼럼명은 반드시 문자로 시작해, 벤더별로 길이에 대한 한계가 있다
8. 벤더에서 사전에 정의한 예약어는 쓸 수없다
9. A-Z, a-z, 0-9,_,$,# 만 사용 가능

🎈테이블에 데이터 입력 방법
1. INSERT INTO 테이블명 (COLUMN LIST) VALUES (COLUMN LIST에 넣을 데이터 들)
2. INSERT INTO 테이블명 VALUES (COLUMN 순서에 맞는 데이터 들)
-> 삽입컬럼을 명시하지 않을때는 꼭 모든 컬럼에 데이터가 입력되어야 한다

🎈입력된 데이터의 수정
UPDATE 테이블명 SET 수정되어야 할 컬럼들(=입력되는 새로운 값)

🎈불필요한 칼럼 삭제
ALTER TABLE 테이블명 DROP COLUMN

🎈TURNCATE TABLE
테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장공간을 재사용 가능
테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 수행해야 한다

 

 

1. 제약조건 생성

produce 테이블 제약 조건

- 기본키 : prod_id : varchar2(10) not null

- prod_nm : varchar2(100) not null

- reg_dt : date not null

- regr_no : number(10) null

CREATE TABLE PRODUCT #PRODUCT 테이블 만들기
(PROD_ID VARCHAR2(10) NOT NULL,
PROD_NM VARCHAR2(100) NOT NULL,
REG_DT DATE NOT NULL,
REGR_NO NUBER(10),
CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID)); #PROD_ID를 고유키로 PRODUCT_PK이름의 제약조건 만들기

 

2. 제약조건 변경 ver. SQL server 

(기관분류)  분류ID : varchar(10) not null	(기관분류) 분류ID : varchar(10) not null
분류명 : varchar(10) not null 등록일자 : varchar(10) null	분류명 : varchar(30) not null 등록일자 : varchar(10) not null

ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;
#기관분류의 테이블의 분류명 컬럼을 바꿀꺼야, VARCHAR(30) NOT NULL
ALTER TABLE 기관분류 ALTER COLUMN 등록일자 DATE NOT NULL

 

각 컬럼별 다른 쿼리를 작성해야 함

이전 not null의 제약조건을 유지한다고 해도 한번더 지정해 줘야 함

 

3. 외래키

• 테이블생성시 설정할 수 있다
• 외래키 값은 널 값을 가질 수 있다
• 한 테이블에 하나 이상 생성할 수 있다
• 외래키 값은 참조 무결성 제약을 받을 수 있다

4. 외래키의 부모테이블(기본키) 삭제조건 -> 자식테이블 반영

• REFERENCE 참조할 외부키 ON DELET  + ~~~
• Cascade : 부모삭제시 자식도 같이 삭제
• set null : 부모 삭제시 자식은 해당 필드 널값
• set default : 부모 삭제시 자식은 해당 필드 default
• restrict : 자식테이블에 기본키값이 없는경우만 부모키 삭제(일부만 삭제 가능)
• no action : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

5. 외래키의 자식테이블(기본키) 추가조건 -> 부모테이블 반영

• Automatic : 부모 테이블에서 기본키가 없는 경우 부모키에서 생성 후 자식테이블에 입력
• set null : 부모테이블에서 기본키가 없는 경우 자식 외부키를 널값으로 처리
• ser default : 부모 테이블에 기본키가 없는 경우 자식 외부키를 지정된 기본값으로 입력
• dependent : 부모테이블에 기본키가 존재할 때만 자식테이블에 입력
• no action : 참조무결성을 위반하는 액션을 취하지 않음

 

6. DELET, TURNCATE, DROP 명령

• DROP 명령어는 테이블 정의 자체를 삭제하고, TRUNCATE 명령어는 테이블을 초기 상태로 만든다
• TURNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제시 DELET보다 빠르다
• DROP 과 TURNCATE는 auto commit되고, DELETE는 사용자 COMMIT으로 수행된다

DROP	TURNCATE	DELETE
DDL	DDL(이지만 DML 성격도 있음)	DML
ROLLBACK 불가능 -> auto commit	ROLLBACK 불가능 -> auto commit	ROLLBACK 가능
테이블이 사용했던 Storage 모두 release	테이블이 사용했던 storage 중 최초 테이블 생성이 할당된 storage 만 남기고 release	release 불가
테이블의 정의 자체를 완전히 삭제	테이블을 최초 생성된 초기상태로 만듦	데이터만 삭제

🎈트랜잭션의 특징 -> 데이터베이스의 논리적 연산단위
1. 원자성 = 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되든지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다
2. 일관성 = 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않으면 트랜잭션 후에도 데이터베이스의 내용에 잘못이 있으면 안된다
3. 고립성 = 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다
4. 지속성 = 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다

🎈ROLLBACK
데이터 변경 사항이 취소되어 데이터의 이전 상태로 복귀되며, 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터를 변경할 수 있게 된다
-> 테이블 내 입력한 데이터나, 수정한 데이터에 대하여 commit 이전에는 변경사항을 취소할 수 있는데, 데이터베이스에서 사용

🎈BEGIN TRANSACTION(TRAN)
데이터베이스의 논리적 연산단위
트랜잭션을 시작하고 커밋 또는 롤백으로 종료한다.
롤백구문을 만나면 최초의 begin transaction시점까지 rollback이 수행된다

🎈저장점savepoint
정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백가능
[ORACLE]
savepoint svpt1 ;
rollback to svpt1 ;

[SQL server]
save transaction svpt1;
rollback transaction svpnt1;

🎈TOP(n) with ties 컬럼
select top(2) with ties enmane, sal
from emp
order by sal desc
-> emp테이블에서 sal 내림차순으로 상위 2개의 enmane과 sal 출력
* 동점이 있을 경우 모두 출력

 

 

1. 오라클과 SQL server의 ROLLBACK

최종 val의 ID 100의 값이 오라클에서는 200, server에서는 100으로 출력

ID(PK)	VAL
001	100
002	200

UPDATE A SET VAL = 200 WHERE ID ='001';
CREATE TABLE B (ID CHAR(3) PRIMARY KEY);
ROLLBACK;

 

• 오라클에서는 DDL문장 수행 수 자동커밋된다 
• SQL server에서는 DDL문장수행 후 자동커밋되지 않는다
• 오라클에서 DDL수행은 내부적으로 트랜잭션을 종료하므로 B테이블이 생성된다
• SQL server에서 CREAT TABLE 문장도 TRANSACTION의 번주에 포함되기 때문에 ROLLBACK문장에 의해서 최종적으로 B테이블이 생성되지 않는다

 

출처 : https://hobbylists.tistory.com/entry/SQL문법-3종류-짧고-굵게-정리-DDL-DML-DCL