[SQLD] SQL 기본 I

SQL 종류

종류	설명
DDL (Data Definition Language)	- 관계형 데이터베이스의 구조를 정의하는 명령어 - CREATE  ALTER  DROP  RENAME
DML (Data Manipulation Language)	- 테이블에서 데이터를 입력, 수정, 삭제, 조회하는 명령어 - INSERT  UPDATE  DELETE  SELECT
DCL (Date Control Language)	- 데이터베이스 사용자에게 권한을 부여하거나 회수하는 명령어 - GRANT  REVOKE  TRUNCATE
TCL (Transcation Control Language)	- 트랜잭션을 제어하는 명렁어 - COMMIT  ROLLBACK  SAVEPOINT

• DDL은 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용
• DML은 사용자가 응용프로그램이나 질의어를 통해 저장된 데이터베이스를 실직적으로 접근하는데 사용
• 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(Data Sub Language)라고 함

 

절차적 데이터 조작어 VS 비절차적 데이터 조작어

• As-Is : 비절차적 데이터 조작어(DML)은 사용자가 무슨(What) 데이터를 원하는 지만을 명세
• To-Be : 절차적 데이터 조작어는 어떻게(How) 데이터를 접근해야 하는지 명세 ➜ PL/SQL(오라클), T-SQL(SQLServer)

 

제약조건

[DB] 데이터베이스 제약조건(constraints)

제약조건을 지정하는 방식

• 컬럼레벨 : 테이블 생성 시, 각각의 컬럼을 정의하며 같이 제약조건 지정
• 테이블레벨 : 모든 컬럼 정의 후, 맨 마지막에 제약조건 지정
• ALTER 명령어 이용

💡 NOT NULL을 제외한 나머지 4개의 제약조건은 컬럼/테이블 레벨 방식 모두 지원

-- ORACLE 기준

-- 컬럼레벨
CREATE TABLE 테이블명 (
    USERID VARCHAR2(10) [CONSTRAINT 제약조건명] PRIMARY KEY,
);

-- 테이블레벨
CREATE TABLE 테이블명 (
    USERID VARCHAR2(10),

    CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명)
);

-- ALTER
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 PRIMARY KEY(컬럼명);

ALTER TABLE STUDENT 
ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(컬럼명)
REFERENCES TO DEPT(deptno);

 

ALTER 명령어

생성된 테이블에 대한 구조를 변경하는데 사용한다. 테이블에 대한 구조 변경은 컬럼의 추가,삭제 및 컬럼의 타입이나 길이 변경, 제약조건 추가,삭제 등이 가능하다.

[SQL] DDL (Data Definition Language, 데이터 정의어)

 

테이블 컬럼에 대한 정의 변경

• SQLServer 에서는 여러 개의 컬럼을 동시에 수정하는 구문은 지원하지 않음
• 수정할 때 NOT NULL 구문을 지정하지 않으면, 기존의 NOT NULL 제약조건이 NULL로 변경

-- SQLServer 기준
ALTER TABLE 테이블명
ALTER 칼럼명 데이터유형 [DEFAULT][NOT NULL];

ALTER TABLE 상품 ALTER COLUMN 상품명 VARCHAR(20) NOT NULL;

-- ORACLE 기준
ALTER TABLE 테이블명
MODIFY (칼럼명1 데이터유형 [DEFAULT][NOT NULL];

ALTER TABLE 상품 MODIFY 상품명 VARCHAR(20) NOT NULL;

테이블 컬럼 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

 

테이블명 변경

RENAME 이름(구) TO 이름(신);

 

테이블 생성 시 주의 사항

• A-Z a-z _ # $
• 반드시 문자로 시작

 

참조 동작 (Referential Action)

Delete(/Modify) Action

• CASCADE : 부모 삭제 시 자식도 같이 삭제
• Set Null : 부모 삭제 시 자식의 해당 필드는 NULL값 처리
• Set Default : 부모 삭제 시 자식의 해당 필드는 Default값 설정
• Restrict : 자식 테이블에 PK가 없는 경우에만 부모 삭제 허용
• No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

Insert Action

• Automatic : 부모 테이블에 PK가 없는 경우 부모 PK를 생성 후 자식 입력
• Set Null : 부모 테이블에 PK가 없는 경우 자식 FK를 NULL값 처리
• Set Default : 부모 테이블에 PK가 없는 경우 자식 FK를 지정된 Default값 처리
• Dependent : 부모 테이블에 PK가 존재할 때만 자식 입력 허용
• No Action : 참조무결성을 위반하는 입력 액션을 취하지 않음

 

NULL

• 모르는 값, 정의되지 않은 미지의 값, 값의 부재
• 공백문자 혹은 숫자 0과는 전혀 다른 값
• NULL과의 모든 비교(IS NULL 제외)는 알 수 없음 Unknown 을 반환
• 분모에 NULL이 들어가는 경우 연산의 결과는 NULL ( 분모에 0이면 에러 )

 

NULL의 연산

• NULL값 과의 연산은 NULL값을 리턴
• NULL값 과의 비교연산은 거짓 FALSE를 리턴
• 특정 값보다 크다,적다라고 표현할 수 없음
• 결과값을 NULL이 아닌 다른 값을 얻고자 할 때, NVL ISNULL 함수 사용

 

공백에 관한 NULL 처리

INSERT INTO 서비스 VALUES('999', '');

• ORACLE : NULL로 입력. 해당 데이터를 조회하려면 IS NULL 조건으로 조회
• SQL Server : 공백으로 입력. 해당 데이터를 조회하려면 칼럼명=''로 조회

 

NULL 관련 함수

함수	설명
NVL(표현식1, 표현식2) ISNULL(표현식1, 표현식2)	표현식1의 값이 NULL이면 표현식2의 값을 출력
NVL2(MGR, 표현식1, 표현식2)	MGR칼럼이 NULL이 아니면 표현식1, NULL이면 표현식2 반환
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL, 다르면 표현식1 출력
COALESCE(표현식1, 표현식2, ...)	표현식에서 NULL이 아닌 최초의 표현식 출력 모든 표현식이 NULL이면 NULL 리턴

 

부정 비교 연산자

• != ^= <> : 같지 않다
• NOT 칼럼명 = : ~와 같지 않다
• NOT 칼럼명 > : ~보다 크지않다
• 칼럼명 IS NOT NULL : 칼럼의 값이 NULL이 아니다

 

DROP | DELETE | TRUNCATE

• DROP TABLE: 테이블의 모든 데이터를 삭제, 디스크 사용량 초기화, 스키마 정의 삭제, auto commit
• TRUNCATE TABLE : 테이블의 모든 데이터를 삭제, 디스크 사용량 초기화, auto commit
• DELETE TABLE : 테이블의 모든 데이터를 삭제, 디스크 사용량은 초기화 하지 않음

💡 TRUNCATE는 UNDO*를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제 시 DELETE보다 빠르다
💡 TRUNCATE는 테이블을 최초 생성된 초기 상태로 만듦
* UNDO : 실행 취소

 

DISTINCT

DISTINCT는 중복된 데이터가 있는 경우 하나로 처리해서 출력 ➜ 중복제거

💡 또 다른 방법으로는 GROUP BY가 있다

SELECT [ALL|DISTINCT] 칼럼명 ... 
FROM 테이블명;

 

트랜잭션의 4가지 특성

특성	설명
원자성 (atomicity)	트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지 아니면 실행되지 않은 상태로
일관성 (consistency)	데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다
고립성  (isolation)	트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다
지속성  (durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장

트랜잭션에 대한 격리성이 낮아 발생할 수 있는 문제점

• Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
• Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상
• Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

 

SAVEPOINT

저장점 SAVEPOINT을 정의하면 롤백 ROLLBACK할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SQVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다. SAVEPOINT 이름이 같을 때, 마지막으로 저장한 값으로 ROLLBACK

-- ORACLE
SAVEPOINT SP1;
ROLLBACK TO SP1;

--SQLServer
SAVE TRANSACTION SP1;
ROLLBACK TRANSACTION SP1;

💡 BEGIN TRANSACTION
ROLLBACK구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 롤백이 수행된다