[SQLD] 2-1. SQL 기본

📖SQL 문장 종류

DML	SELECT, INSERT, UPDATE, DELETE	데이터 조회, 검색을 하기 위한 명령어 데이터에 변형을 가하는 명령어 aka  데이터 부속어(Data Sub Language), 비절차적 데이터 조작어
DDL	CREATE, ALTER, DROP, RENAME	데이터 구조 정의, 구조 생성, 변경, 삭제 등 데이터 구조와 관련된 명령어
DCL	GRANT, REVOKE	DB에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
TCL	COMMIT, ROLLBACK	DML에 의해 조작된 결과를 트랜잭션 별로 제어하는 명령어

 

 

📖비절차적 데이터 조작어 vs 절차적 데이터 조작어

• 비절차적 : 사용자가 무슨 데이터를 원하는지
• 절차적 : 사용자가 무슨 데이터를 원하는지, 어떻게 접근해야 되는지 명세(PL/SQL, T-SQL) 

 

📖PK 지정 방법

<!-- 선언문 내부 -->
CREATE TABLE A
( AA VARCHAR2(10) NOT NULL, ...
	, CONSTRAINT A_PK PRIMARY KEY (AA) );
    
<!-- 선언문 외부 -->
ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY(AA);

 

✏️컬럼에 대한 정의 변경

<!-- ORACLE -->
ALTER TABLE A MODIFY (AA VARCHAR2(30) NOT NULL);

<!-- SQL SERVER -->
ALTER TABLE A ALTER COLUMN AA VARCHAR(30) NOT NULL;

 

📖제약조건(Constraints) 종류

• PRIMARY KEY = UNIQUE + NOT NULL
• UNIQUE KEY : 테이블 내 중복되는 값이 없음, NULL 가능
• NOT NULL
• CHECK
• FOREIGN KEY

• 초기 선언에 PRIMARY KEY라고 선언하면 NOT NULL 안걸어도 됨
• 이미 PK 지정했으면 ALTER TABLE에서 PK 재지정 X

 

✏️SQL구분

<!-- ANSI 테이블 명 수정 -->
RENAME A TO AA;

<!-- 테이블의 불필요 컬럼 삭제 -->
ALTER TABLE A DROP COLUMN AA;

 

📖참조 동작

• DELETE(/MODIFY)

1. CASCADE : MASTER 삭제 시 CHILD 같이 삭제
2. SET NULL : MASTER 삭제 시 CHILD 해당 필드 NULL
3. SET DEFAULT : MASTER 삭제 시 CHILD 해당 필드 DEFAULT 값으로 설정
4. RESTRICT : CHILD 테이블에 PK 값이 없는 경우만 MASTER 삭제 허용
5. NO ACTION : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

• INSERT ACTION

1. AUTOMATIC : MASTER 테이블에 PK가 없는 경우 MASTER PK를 생성 후 CHILD 입력
2. SET NULL : MASTER 테이블에 PK가 없는 경우 CHILD 외부키를 NULL  값으로 처리
3. SET DEFAULT : MASTER 테이블에 PK가 없는 경우 CHILD 외부키를 지정된 기본값으로 입력
4. DEPENDENT : MASTER 테이블에 PK가 존재할 때만 CHILD 입력 허용
5. NO ACTION : 참조무결성을 위반하는 입력 액션을 취하지 않음

 

📖DROP / TRUNCATE / DELETE 차이 정리

DROP	TRUNCATE	DELETE
DDL	DDL (일부 DML)	DML
ROLLBACK 불가능	ROLLBACK 불가능	COMMIT 이전 ROLLBACK
AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
STORAGE 전부 RELEASE	태초 STORAGE만 남김	데이터만 DELETE, STORAGE 온전
테이블 정의 자체 삭제	테이블을 태초 형태로 만듬	데이터만 삭제

 

 

📖트랜잭션(DB 논리적 연산단위) 특성

• 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공이던지 전혀 실행 안된 상태던지
• 일관성 : 트랜잭션 실행 전 DB 내용에 잘못이 없다면 실행 후에도 없어야함
• 고립성(격리성) : 트랜잭션이 실행되는 중 다른 트랜잭션에 영향을 받으면 안됨
• 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장

• 격리성이 낮은 경우

1. DIRTY READ : 다른 트랜잭션에 의해 수정되었지만 커밋되지 않은 데이터
2. NON-REPEATABLE READ : 한 트랜잭션이 같은 쿼리를 두 번 수행했는데, 그 사이 다른 트랜잭션이 수정이나 삭제
3. PHANTOM-READ : 두번 실행했는데 첫번째에서 없던 유령 레코드가 나타남

ORACLE = 자동 COMMIT
SQL SERVER = 수동 COMMIT

 

 

✏️트랜잭션 용어 정리

• BEGIN TRANSACTION : 트랜잭션 시작
• COMMIT / ROLLBACK으로 종료
• ROLLBACK : 최조의 BEGIN TRANSACTION 까지 ROLLBACK 수행
• SAVEPOINT 정의하면 ROLLBACK의 일부만 가능

 

 

✏️연산 용어 정리

• WHERE절은 FROM절 다음
• NULL과의 연산은 NULL
• NULL 비교연산 불가
• !=, ^=, <> 모두 같지 않다
• IN : 리스트 있는 값 중 어느 하나라도 일치하면 된다.
• BETWEEN : 사이값이 포함됨, 지정한 숫자까지 포함

 

 

📖내장함수

• 함수의 입력 행수에 따라 단일행/다중행
• 단일행 : SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용
• 1:M 관계에서 두 테이블 조인 = M쪽에 출력 단일행 = 단일행 함수 사용 가능
• 다중행 함수도 단일행 함수와 동일하게 단일 값 반환
• 다중행 함수 : 집계 함수, 그룹 함수, 윈도우 함수

 

 

✏️단일행 함수 종류 단일행

종류	함수
문자형	LOWER, UPPER, SUBSTR/SUBSTRING, LENTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형	SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER~
변환형	TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT
NULL	NVL/ISNULL, NULLIF, COALSCE

 

 

✏️문자형 함수 종류

문자형 함수	함수 설명
LOWER	문자열의 알파벳 문자를 소문자로 바꾸어 준다
UPPER	문자열의 알파벳 문자를 대문자로 바꾸어 준다
ASCII	문자나 숫자를 아스키 코드 번호로 바꾸어 준다
CHR/CHAR	아스키 코드 번호를 문자나 숫자로 바꾸어 준다
CONCAT	ORACLE, MY SQL만 가능 문자열 연결, 합성 연산자 ORACLE'||' / MYSQL '+'를 사용
SUBSTR/SUBSTRING	문자열 중 M의 위치에서 N개의 문자 길이에 해당하는 문자를 돌려준다. N이 없을 시 마지막 문자까지
LENGTH/LEN	문자열의 개수를 숫자값으로 돌려준다
LTRIM	문자열 첫 문자부터 지정 문자가 나타나면 해당 문자 제거 *SQL SERVER은 LTRIM 지정 문자 사용 못함 = 공백만 제거 가능
RTRIM	문자열 마지막 문자부터 지정 문자가 나타나면 해당 문자 제거 *SQL SERVER은 RTRIM 지정 문자 사용 못함 = 공백만 제거 가능
TRIM	문자열에서 양쪽에 있는 지정 문자 제거 *SQL SERVER은 TRIM 지정 문자 사용 못함 = 공백만 제거 가능
REPLACE	문자열을 치환하는 함수, CHR(10)으로 바꾸면 줄바꿈 제거

 

 

✏️NULL 함수 종류

함수	설명
NVL(1,2)/ISNULL(1,2)	1의 값이 NULL이면 2의 값 출력
NULLIF(1,2)	1과 2가 같으면 NULL, 같지 않으면 1 리턴
COALSCE(1,2)	NULL이 아닌 최초의 표현식 리턴

 

✏️NOTE

• CASE WHEN A ='AA' THEN 'B' == CASE A WHEN 'AA' THEN 'B'
• ORACLE에서 NULL은 가장 큰 값
• SQL SERVER에서 NULL은 가장 작은 값
• EQUI JOIN = FROM절에 , 로 테이블 가져오고 WHERE에서 = 넣어주기 / FROM에서 JOIN 걸고 ON 쓰기

 

 

📖GROUP BY와 HAVING 특성

• GROUP BY절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• 집계 함수의 통계 정보는 NULL 값을 제외하고 수행한다.
• GROUP BY 절에서 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• 집계 함수는 WHERE절에 올 수 없다
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행을 미리 제거한다.
• HAVING 절은 GROUP BY  절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.
• GROUP BY를 사용하면 GROUP BY 표현식이 아닌 값은 기술될 수 없다.

 

📖SELECT 실행 순서

 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

 

✏️JOIN 설명

• 일반적으로 JOIN은 PK와 FK의 값 연관성에 의해 성립된다.
• DBMS 옵티마이저는 FROM절에 나열된 테이블들을 임의로 2개씩 묶어서 JOIN 처리
• EQUI JOIN은 JOIN에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다.
• EQUI JOIN은 =에 의해서만 수행되며, 그 외는 모두 NON EQUI JOIN이다
• 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 불가능한 경우도 있다.