[SQLD자격증] # 1. (2과목) SQL 기본 및 활용 - SQL 기본 요점 정리

해당 글은 https://cafe.naver.com/sqlpd/7810 을 참조하고
기출을 풀며, 내용 추가하였습니다.

SQL 문장들의 종류
DDL (데이터 정의어)	CREATE,ALTER,RENAME, DROP, TRUNCATE
DML (데이터 조작어)	SELECT, INSERT, DELETE, UPDATE   (COMMIT 입력해야 함)
DCL (데이터 제어어)	GRANT(권한 부여), REVOKE(권한 취소)
TCL (트랜잭션 제어어)	COMMIT(DB에 반영), ROLLBACK(트랜잭션 이전의 상태로 되돌림), SAVEPOINT(저장 지점) [Oracle] SAVEPOINT SVPT;  ROLLBACK TO SVPT; [SQL Server] SAVE TRAN SVPT; ROLLBACK TRAN SVPT;

 

DDL - 제약조건 : 데이터의무결성유지
PRIMARY KEY(기본키)	테이블 당 한 개만 생성 가능, 자동으로 인덱스 생성
UNIQUE KEY(고유키)	컬럼에 중복된 값을 허용하진 않지만 NULL값은 포함됨
FOREIGN KEY(외래키)	다른 테이블의 기본키 참조, NULL 가능, 여러 속성 가능
NOT NULL	컬럼에 NULL값을 입력할 수 없도록 함. 기본키의 경우 별도로 지정X
CHECK	입력 값 범위 제한

DDL - 테이블, 뷰, 인덱스 등  생성 / 변경 / 제약조건 SQL문
테이블 생성	CREATE TABLE 테이블명 (컬럼명1 CHAR(10) NOT NULL,  컬럼명2 VARCHAR2(20) NOT NULL);
테이블명 변경	RENAME 이전_테이블명 TO 이후_테이블명 💡 테이블명과 컬럼명은 반드시 문자로 시작! 특수문자는 _와 $만 이용 가능
테이블 삭제	DROP TABLE 테이블명
테이블 데이터 삭제	TRUNCATE TABLE 테이블명
테이블 컬럼 추가	ALTER TABLE 테이블명 ADD(컬럼명 VARCHAR2(20));
테이블 컬럼 수정	(Oracle) ALTER TABLE 테이블명 MODIFY (컬럼명 VARCHAR2(20) DEFAULT 'A' NOT NULL); (SQL Server) ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 VARCHAR(10) NOT NULL;
테이블 컬럼 삭제	ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
제약조건 추가	ALTER TABLE 테이블명 ADD CONSTRAINT 조건명 조건(컬럼명); ALTER TABLE 테이블명 ADD CONSTRAINT 테이블명_PK PRIMARY KEY (A);
제약조건 삭제	DROP CONSTRAINT 조건명
참조되는 제약조건 삭제	❔ DROP TABLE 테이블명 [CASCADE CONSTRAINT] CASCADE CONSTRAINT

◾ Check 제약조건은 DB에서 데이터의 무결성을 유지하기 위해 테이블의 특정 컬럼에 설정하는 제약
 
◾ PK 제약조건 생성
1. CREATE TABLE 테이블명 (PK명 VARCHAR NOT NULL, ..., 
                                             CONSTRAINT 테이블_PK PRIMARY KEY (PK명));
2. CREATE TABLE 테이블명 (PK명 VARCHAR2(10) NOT NULL, ...)
    ALTER TABLE 테이블명 ADD CONSTARINT 테이블명_PK PRIMARY KEY (PK명)
 

◾ 인덱스생성
1. CREATE TABLE TBL (A VARCHAR2(10) PRIMARY KEY, ...)
    CREATE INDEX IDX_A ON TBL (B);
2. CREATE TABLE 테이블명 (PK명 VARCHAR2(10) NOT NULL, ...)
    ALTER TABLE 테이블명 ADD CONSTARINT 테이블명_PK PRIMARY KEY (PK명)
    CREATE INDEX IDX_A ON TBL (B);
 
◾ DELETE/MODIFY Action : 
1. Cascade : Master 삭제 시 Child 같이 삭제
2. Set Null : Master 삭제 시 Child 해당 필드 Null
3. Set Default : Master 삭제 시 Child 해당 필드 Default 값으로 설정
4. Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
5. No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음
 
◾ 외래키
1. CREATE TABLE 테이블명 (..., PRIMARY KEY(A),
                                               FOREIGN KEY(B) REFERENCES 마스터테이블(C) ON DELETE CASCADE);
2. ALTER TABLE 테이블명 ADD CONSTARINT 테이블명_FK FOREIGN KEY (FK명)
    REFERENCES 마스터테이블(C) ON DELETE SET NULL;
* Master 테이블에 존재하지 않는 C(=FK) 입력 시 무결성 제약 오류 발생
* 만약 Master 테이블의 C가 특정 값을 삭제하려 할 때 Child 테이블의 FK를 NULL로 업데이트하려고 시도, 만약 해당 컬럼이 NOT NULL 제약조건이 있으면 실패
 
◾ 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 : 참조무결성을 위반하는 입력 액션을 취하지 않음

DML - 질의 생성하여 데이터 조작 SQL문
- 비절차적 DML은 사용자가 무슨(What) 데이터를 원하지만 명세 - 절차적 DML은 어떻게(How) 데이터를 접근해야 하는지 명세 (PL/SQL(오라클), T-SQL(SQL Server)) - 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(Data Sub Language)라고 함
데이터 삽입	INSERT INTO 테이블명(컬럼명) VALUES (값); - 삽입 컬럼을 명시하지 않을 경우 모든 컬럼을 삽입해야만 함 - 삽입 컬럼을 명시해도 만약 NOT NULL컬럼이 빠지면 안 됨
데이터 업데이트	UPDATE 테이블명 SET 컬럼명 = 값;
데이터 삭제	DELETE FROM 테이블명; (※ TRUNCATE TABLE, DROP TABLE은 로그를 남기지 않음. DELETE는 로그를 남김)
데이터 선택	SELECT 컬럼명 FROM 테이블명  - 합성 연산자 ) ||[Oracle],  +[SQL Server]

 

	DROP	TRUNCATE	DELETE
	DDL	DDL (일부 DML 성격 가짐)	DML
	Rollback 불가능	Rollback 불가능	Commit 이전 Rollback 가능
	Auto Commit	Auto Commit	사용자 Commit
	테이블이 사용했던 Storage를 모두 Release	테이블이 사용했던 Storage 중 최초 테이블 생성 시 할당된 Storage만 남기고 Release (테이블의 용량은 초기화)	데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음 (테이블의 용량은 감소되지X)
	테이블의 정의 자체를 완전히 삭제	테이블을 최초 상태로 초기상태로 만듦 (테이블의 모든 데이터를 삭제)	테이블의 모든 데이터를 삭제
	테이블의 구조를 포함한 테이블 자체가 삭제됨	특정 행 삭제 불가 삭제 데이터는 Rollback할 수 없음

* TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제 시 DELETE보다 빠르다
 

트랜잭션의 특성
원자성 (Atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함 - COMMIT, ROLLBACK
일관성 (Consistency)	트랜잭션 실행 전 DB 내용이 잘못 되지 않으면 실행 후도 잘못 되지 않아야 함 트랜잭션 후에도 일관성이 유지되어야 한다
고립성 (Isolation)	트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다. 트랜잭션이 실행 중에 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근할 수 없다. 부분적인 실행결과를 다른 트랜잭션이 볼 수 없다.
지속성 (Durability)	트랜잭션이 성공적으로 수행되면 DB의 내용은 영구적으로 저장된다.

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

DCL SQL문
GRANT	GRANT 권한 ON 오브젝트 TO 사용자; (GRANT SELECT, UPDATE ON 테이블명 TO 홍길동;)
REVOKE	권한 취소

TCL
Commit과 Rollback의 장점	(1) 데이터 무결성 보장 (2) 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능 (3) 논리적으로 연관된 작업을 그룹핑하여 처리 가능
Commit	- DDL의 경우, [Oracle]AUTO COMMIT[SQL Server]AUTO COMMIT x - CREATE  ~  UPDATE ~ ROLLBACK   [Oracle] DDL문장의 수행은 내부적으로 트랜잭션을 종료시키므로 테이블 생성됨  [SQL Server] CREATE 문장도 트랜잭션의 범주에 포함. ROLLBACK에 의해 최종적으로 테이블 생성 x ◼ 문제 AUTO COMMIT이 FALSE로 설정된 환경 CREATE / UPDATE / ROLLBACK  - SQL SERVER에서 ROLLBACK을 하는경우 UPDATE구는 취소됨  - ORACLE에서 ROLLBACK을 수행하면 UPDATE구는 취소됨  - AUTO COMMIT이 FALSE이므로 UPDATE구는 자동 COMMIT되지 않는다.  [ORACLE] 기본값이 auto commit off로 설정된 상태로 DDL이 수행되면 묵시적으로 COMMIT 수행  [SQL Server] AUTO COMMIT = Off 된 상태로, DDL의 수행될 경우 묵시적으로 COMMIT 이 수행 되지 않음.   즉, SET IMPLICIT TRANSACTIONS ON을, 설정하면 그 아래 CREATE문이 있어도 ROLLBACK을 하면 모두 취소됨
Rollback	- COMMIT 되지 않은 상위의 모든 트랜잭션을 모두 롤백 - CREATE TABLE 문구와 같은 DDL구는 ROLLBACK으로 취소되지 않는다.

 

연산자 종류

◾ BETWEEN a AND b : a(<=와 b (>=)값 사이 (반대의 경우 NOT BETWEEN a AND b) ◾ IN (list) : 리스트에 있는 값 중 어느 하나라도 일치 ◾ NOT IN (list) : list의 값과 일치하지 않음 - WHERE AA NOT IN (SELECT BB FROM B) = WHERE NOT EXISTS (SELECT * FROM B WHERE A.AA = B.BB)   = A RIGHT OTHER JOIN B ON A.AA=B.BB - WHERE AA NOT IN (SELECT BB FROM B) 만약 BB에 NULL이 존재하고 NULL을 NOT IN으로 조회하면 모든 조건이 FALSE가 되어  0건이 조회! ◾ IS NULL : NULL 값인 경우 ◾ IS NOT NULL : NULL 값을 갖지 않음 ◾ NULL값은 모르는 값, 값의부재, (공백문자, 0은 아님)      (ORACLE에서 ''는 NULL로 입력 → 해당 데이터는 IS NULL로 조회해야 함)    (SQL Server에서는 ''는 ''로 입력됨) ※ NULL 값과의 수치연산은 NULL값을 리턴 ※ NULL값과의 비교연산은 거짓(FALSE)를 리턴 ◾ LIKE '비교문자열'     - 'A%' : A로 시작하는 문자만 필터    - '%A%' : A로 포함하는 문자만 필터    - '%A' : A로 끝나는 문자만 필터   - '_%A' : 두 번째 문자가 A인 것만 필터   - '%@_%' EXCAPE '@' :  '_'나 '%' 앞에 ESCAPE로 특수문자 지정하면 검색 가능 '_'가 들어간 문자열 (예제) WHERE COL LIKE %우% → '관우' OK (예시) - SELECT * FROM DUAL WHERE NULL= NULL : 공집합, NULL과 NULL 비교불가

연산자 우선순위 : () → NOT → 비교연산자 → AND → OR?    산술연산자(*, /, }, -) →연결연산자(||) →비교연산자(<,>,<=,>=,<>,]) →IS NULL, LIKE, IN →BETWEEN →NOT연산자 →AND연산자 →OR 연산자

 

SQL 함수 정리
SELECT 문장 실행 순서 FROM  → WHERE →  GROUP BY  → HAVING → SELECT → ORDER BY ◾ 문자형과 숫자형을 비교 시 문자형을 숫자형으로 묵시적 변환하여 비교!
문자형 함수	◾ LOWER : 문자열을 소문자로 ◾ UPPER : 문자열을 대문자로 ◾ ASCII : 문자의 ASCII 값 반환 ◾ CHR/CHAR : ASCII 값에 해당하는 문자 반환 ◾ CONCAT : 문자열1, 2를 연결 ◾ SUBSTR/SUBSTRING : 문자열 중 m 위치에서 n개의 문자 반환 ◾ LENGTH/LEN : 문자열 길이를 숫자 값으로 반환 ◾ LTRIM('문자열', 'x') : 왼쪽 문자열부터 시작하여  'x'  나오면 제거하고 다른 문자열 나오면 STOP ◾ RTRIM('문자열', 'x') : 오른쪽 문자열부터 시작하여  'x'  나오면 제거하고 다른 문자열 나오면 STOP ◾ TRIM(‘x’ FROM '문자열') : 양쪽 문자열에서  'x'  나오면 제거하고 다른 문자열 나오면 STOP ◾ LPAD(대상 문자, 지정 길이, 채울문자)
숫자형 함수	◾ SIGN(n) : 숫자가 양수면1, 음수면-1, 0이면 0 반환 ◾ MOD : 숫자1을 숫자2로 나누어 나머지 반환 ◾ CEIL/CEILING(n) : 크거나 같은 최소 정수 반환 ◾ FLOOR(n) : 작거나 같은 최대 정수 리턴 ◾ ROUND : 반올림 (ROUND(실수, 자릿수)) ◾ TRUNC : 버림
날짜형 함수	◾ SYSDATE : 현재날짜 +시각 ◾ GETDATE() : 현재날짜 +시각 ◾ EXTRACT : 날짜에서 데이터 출력 (예시) EXTRACT('YEAR' | 'MONTH' | 'DAY' FROM DATE) ◾ DATEPART : 날짜에서 데이터 출력 ◾ TO_NUMBER(TO_CHAR(day,‘YYYY’)) ◾ (ADD_MONTHS(SYSDATE, 6), '월요일') : 6개월 후 첫번째 월요일
NULL 관련 함수	◾ NVL(식1,식2) : 식1의 값이 NULL 이면 식2 출력 - NVL(컬럼명, 값1)  = DECODE(컬럼명, NULL, 값1, 컬럼명)  = CASE WHEN 컬럼명 IS NULL THEN 값1 ELSE 컬럼명 END     = [SQL Server] ISNULL(컬럼명, 값)        ◾ NVL2(컬럼명, 값1, 값2) = DECODE(컬럼명, NULL, 값1, 값2) ◾ ISNULL(식1,식2) : 식1의 값이 NULL 이면 식2 출력 ◾ NULLIF(식1,식2) : 식1이 식2와 같으면 NULL을 아니면 식1을 출력 ◾ COALESCE(식1,식2) :  NULL이 아닌 첫번째 값을 리턴, 모두 NULL이면 NULL 반환
집계 함수	◾ DISTINCT : 같은 값을 하나의 데이터로 간주 옵션 ◾ COUNT(*) : NULL 포함 행의 수 ◾ COUNT(표현식) : NULL 제외 행의 수 ◾ SUM, AVG : NULL 제외 합계, 평균 연산 ◾ STDDEV : 표준 편차 ◾ VARIAN : 분산 ◾ MAX, MIN : 최대값, 최소값 ※ MAX, MIN, COUNT : 숫자 유형, 문자 유형, 날짜 유형에도 적용 가능 ※ 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행 ※ GROUP BY 절에서는 ALIAS 사용 불가 ※ HAVING 절(집계함수를 이용하여 조건 표시o)은 일반적으로 GROUP BY 뒤에 위치 ※ GROUP BY없어도 HAVING절 사용 가능 (SELECT * FROM # HAVING 집계함수)  ※ GROUP BY로 그룹핑된 컬럼에 대해 HAVING 조건절을 사용할 경우 집계된 컬럼의 FILTER조건으로 사용 가능. 이럴 경우 집계함수 없이도 사용 가능 ※ SELECT A, COUNT(*) FROM TABLE GROUP BY A ▶A컬럼에 NULL값있으면 NULL 행도 결과가 나옴 (예시) -   SELECT COUNT(PK_COL) FROM TABLE WHERE B >=값1 OR B<값1     SELECT COUNT(PK_COL) FROM TABLE     => B컬럼에 NULL이 들어있을 경우 WHERE문에서 NULL은 정상적인 비교가 되지 않아 행 조회 안됨 (예시) SELECT A, C, COUNT(B) FROM TABLE GROUP BY A,C (가능) SELECT A, COUNT(B) FROM TABLE GROUP BY A,C (가능) SELECT C, COUNT(B) FROM TABLE GROUP BY A,C (가능)
조건함수	◾ [SEARCHED_CASE_EXPRESSION] CASE WHEN 컬럼명= 값1 THEN 값2    [SIMPLE_CASE_EXPRESSION]        CASE 컬럼명 WHEN 값1 THEN 값2    같은문장 ◾ ELSE가 생략되면 ELSE 조건에서 NULL이 됨
순서 함수	※ ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능 → SELECT * FROM TABLE ORDER BY 2; (두번째컬럼으로 정렬) → ORDER BY 항목은 반드시 SELECT 목록에 있는 컬럼의 자릿수를 사용해야 함     (SELECT N1 FROM (SELECT * FROM TABLE) ORDER BY N2 ▶실행됨)     (SELECT N1 FROM (SELECT * FROM TABLE) ORDER BY 2 ▶실행 안됨)     (SELECT * FROM TABLE ORDER BY COL1 ASC, COL2, 3 DESC ▶실행됨)     (SELECT A, COUNT(*) AS CNT FROM TABLE GROUP BY A ORDER BY A ▶실행됨)     (SELECT A, COUNT(*) AS CNT FROM TABLE GROUP BY A ORDER BY CNT DESC ▶실행됨)     (SELECT A, COUNT(*) AS CNT FROM TABLE GROUP BY A ORDER BY COUNT(*)  ▶실행됨)     (SELECT A, COUNT(*) AS CNT FROM TABLE GROUP BY A ORDER BY 2)  ▶실행됨) ※ DEFAULT 값으로 오름차순(ASC)이 적용되며 DESC 옵션을 통해 내림차순으로 정렬 ※ SQL 문장의 제일 마지막에 위치 ※ SELECT 절에서 정의하지 않은 칼럼 사용 가능 ※ [Oracle] NULL을 가장 큰 값으로 취급     [SQL Server]에서는 NULL을 가장 작은 값으로 취급 ※[SQL Server]에서 TOP N 질의문에서 N에 해당하는 값이 동일한 경우 함께 출력되도록 하는 WITH TIES 옵션을 ORDER BY 절과 함께 사용 → SELECT TOP(3) WITH TIES A, B FROM 테이블 ORDER BY B DESC;
조인 함수	◾ EQUI JOIN   - 2 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용   - 대부분 PK, FK의 관계를 기반으로 함   - '=' 연산자에 의해서만 수행   - 반드시 기본키, 외래키 관계에 의해서만 성립되는 것은 아님. 조인 컬럼이 1:!로 매핑 가능하면 사용 가능 ◾ NON EQUI JOIN   - 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용   - ‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용 ※ N개 테이블을 JOIN 하기 위해서는 최소 N-1번의 JOIN  조건이 필요(항상 2개의 테이블을 짝지어 조인 수행)

 
그 외
◾ 함수의 입력 행수에 따라 단일행 함수, 다중행 함수로 구분
◾ 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용 가능
◾ 1:M 조인이더라도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용될 수 있음
◾ 다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환
◾ CHR : 주어진 ASCII 코드에 대한 문자를 반환하는 함수 (CHR(10) :줄바꿈)

ROWNUM	C1	LENGTH(C1)	REPLACE(C1, CHR(10))	LENGTH(REPLACE(C1, CHR(10)))
1	A A	3	AA	2
2	B B B	5	BBB	3

 
◾ 차수(Degree) : 릴레이션의 컬럼수 / 카디널리티(Cardinality) : 선택된 행들의 개수
 
◾ 날짜형 변수 : '20230101', SYSDATE-10 ok (20230101은 TO_DATE로 감싸야 함)
◾ 문자형(VARCHAR)변수 : 002 가능
◾ 특정 날짜형 변수 연산에 사용된 1/24/60 : 1분을 의미
 
[키의 종류]
◾ 기본키 : 후보키 중 엔터티 대표할 수 있는 키, 반드시 테이블 당 하나의 제약만을 정의
◾ 후보키 : 유일성과 최소성을 만족하는 키
◾ 슈퍼키 : 유일성은 만족하지만 최소성(NOT NULL)을 만족하지 않는 키
◾ 외래키 : 하나 혹은 다수의 다른 테이블의 기본키 필드를 가리키는 것으로 참조 무결성을 확인하기 위해 사용되는 키
◾ 고유키 : 지정된 모든 컬럼은 NULL을 가질 수 있음
 
[데이터베이스 키 생성여부에 따른 식별자의 종류]
◾ 내부 식별자 : 엔터티 내부에서 스스로 생성되는 식별자 (부서코드, 주문번호, 종목코드)
◾ 외부 식별자 : 다른 엔터티의 관계로 인하여 만들어진 식별자 (계좌 엔터티에 회원id)
 
◾ 명시적 트랜잭션의 시작과 끝은 모두 SQL을 실행하는 사용자가 지정
◾ 암시적(묵시적) 트랜잭션 처리는 트랜잭션이 자동으로 시작되며 자동으로 완료 혹은 취소되는 것