SQLD 2과목 SQL 기본 및 활용

sqld

SQLD 2과목 SQL 기본 및 활용

svdjcuwg4638 2023. 6. 2. 20:19

제1장 SQL 기본

SQL 문장들의 종류

DDL(Data Definition Language)

테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어 구조변경, 테이블 이름 변경, 테이블 삭제

CREATE, ALTER, DROP, RENAME

DCL(Data Control Language)

권한을 주고 회수하는 명령어

GRANT 예시문

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO user1;

REVOKE 예시문

REVOKE SELECT, INSERT, UPDATE, DELETE ON orders FROM user1;

DML(Data Manipulation Language)

데이터베이스 조회, 추가, 삭제, 수정

SELECT, INSTRT, UPDATE, DELETE

TCL(Transaction Control Language)

DML에 의해 조작된 결과를 작업단위(트랜젝션) 별로 제어하는 명령어

COMMIT ROLLBACK

데이터 조작어

비절자척 데이터 조작어( As - Is )

사용자가 무슨 데이터를 원하는지만 명세함

절차적 데이터 조작어( To - Be )

어떻게 데이터를 접근해야하는지 명세한다 PL/SQL(오라클), T-SQL(SQL Server 등)

ALTER 테이블 컬럼에 대한 정의 변경 명령문(Oracle)

수정

ALTER TABLE (테이블명) MODIFY ((컬럼명) (데이터유형));

ALTER TABLE employees MODIFY (salary NUMBER(10, 2));

추가

ALTER TABLE (테이블명) ADD ((컬럼명) (데이터유형));

ALTER TABLE employees ADD (birthdate DATE);

삭제

ALTER TABLE (테이블명) DROP ((컬럼명) (데이터유형));

ALTER TABLE employees DROP birthdate;

제약조건의 종류

PRIMATY KEY(기본키)
NOT NULL + UNIQUE KEY
UNIQUE KEY(고유키)
중복되는 값은 없지만 NULL은 입력가능하다
NOT NULL
값이 무조건 있어야함
CHECK
조건안에 있는 값만 허용
FOREGN KEY(외래키)
다른테이블에 컬럼값을 참조하는 키이며
DB추가시 참조하는 테이블에 입력할려는 값이 있어야 생성 가능하다.
NULL값을 가질수도 있다.

테이블명 생성시

"_", "$", "#", 과 영대소문자, 숫자만 허용한다

테이블명 변경시 명령어

RENAME (OLD테이블명) TO (NEW 테이블명);

RENAME employees TO staff;

참조동작

- Delete Action

cascade : Master 삭제 시 child 같이 삭제

- Insert Action

Automatic : Master 테이블에 PK가 없는 경우 MASTER PK를 생성 후 child 입력
Dependent : Master 테이블에 PK가 존재할 때만 Child 입력 허용

UPDATE 명령문

UPDATE 테이블명 SET 수정될컬럼명='수정될값' WHERE 조건

UPDATE TEST1 SET NAME='김대현' WHERE IDX = 5

WHERE조건이 없다면 그 컬럼에 모든값이 변경된다.

삭제문법중 로그를 남기는것

로그를 남기고 그 테이블의 데이터를 없애고 싶다면 DELETE FROM 컬럼명

(DROP와 TRUNCATE는 로그를 남기지 않는다)

DISTINCT

중복된 데이터가 있는 경우 1건으로 처리해서 출력한다(보통 SELECT뒤에 붙게된다)

SELECT DISTINCT 거주지, 근무지 FORM 고객지역

두개의 컬럼 기준으로 중복된값없이 값을 보여준다.

Auto Commit과 Rollback

DROP	TRUNCATE	DELETE
DDL	DDL	DML
Rollback 불가능	Rollback불가능	Commit이전 Rollback 가능
Auto Commit	Auto commit	사용자 Commit

트렌젝션(Transaction)

데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한개 이상의 데이터 베이스 조작을 가리킨다

대표적인 명령어로 Commit와 Rollback이 존재한다.

트랜젝션의 특성

원자성(atomicity)

연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.

일관성(consistency)

트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

고립성(isolation)

트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.

지속성(durability)

트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

SAVEPOINT

oracle

SAVEPOINT SVPT1;

....

ROLLBACK TO SVPT1;

SQL Server

SAVE TRANSACTION

SVTR1;

...

ROLLBACK

TRANSACTION

SVTR1;

NULL값의 기준(My SQL과 Oracle의 차이)

Oracle에선 NULL값이 가장 큰값으로 간주되고

MySQL에선 NULL값이 가장 작은값으로 간주된다.

SELECT 문장의 실행 순서

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

프웨그해셀오

TOP TIES

TOP(숫자) 출력결과에서 원하는만큼 가져올 수 있다.

하지만 같은 급여를 가진 사원이 있으면 같이 출력하고싶을땐 TOP WITH TIES를 사용한다.

SELECT TOP(2) WITH TIES ENAME, SAL

FROM EMP ORDER BY SAL DESC

제 2장 SQL 활용

ROLLUP

1.기준 A,B 중 A와B일치 에대한 합계

2.A만 일치하는 중합계

3.전체 합계를 보여준다.

region	sale_year	total_revenue
A	2021	3000
A	2022	1500
A	NULL	4500
B	2021	2500
B	2022	1200
B	NULL	3700
NULL	NULL	8200

CUBE

1. 기준 A,B 중 A와B일치 에대한 합계

2. A만 일치하는 합계

3. B만 일치하는 합계

4. 전체 합계를 보여준다.

region	sale_year	total_revenue
A	2021	3000
A	2022	1500
B	2021	2500
B	2022	1200
A	NULL	4500
B	NULL	3700
NULL	2021	5500
NULL	2022	2700
NULL	NULL	8200

GROUPING SET

A와 B 동시일치 확인 x
1. A만 일치하는 합계

2. B만 일치하는 합계

region	sale_year	total_revenue
A	NULL	4500
B	NULL	3700
NULL	2021	5500
NULL	2022	2700

일반집합 연산자

집합 A = {가, 나, 다, 라}

집합 B = {다, 라, 마, 바}

UNION

{가, 나, 다, 라, 마, 바} 중복을 제거해줌 그러나 UNION ALL실 중복제거 안해줌

INTERSECTION

INTERSECT의 기능으로 {다, 라}의 중복되는 결과만 보여줌

DIFFERENCE

EXCEPT(Oracle은 MINUS) 기능이며 차집합을 보여준다.

ex)

SELECT column
FROM table_A

MINUS

SELECT column
FROM table_B;

앞에있는것을 기준으로 결과는 {가, 나}가오게 된다.

PRODUCT

CROSS JOIN 기능으로 구현되었고

결과는

product_Aproduct_B ( 조합할 수 있는 모든 결과를 보여준다)

가	다
가	라
가	마
가	바
나	다
나	라
나	마
나	바
다	다
다	라
다	마
다	바
라	다
라	라
라	마
라	바

PRIOR 계층형 쿼리

그래프와 같은 구조를 가진 데이터를 쿼리하는 데 사용됩니다. 계층형 쿼리에서 PRIOR 키워드는 현재 행의 부모 행을 참조합니다.

예를 들어, 각각의 직원이 어떤 매니저에게 보고하는지 보여주는 직원 테이블이 있다고 가정합시다. 이 때, 각 직원이 보고하는 매니저를 찾기 위한 계층형 쿼리를 작성하려면 PRIOR 키워드를 사용할 수 있습니다.

SQL code

SELECT employee_id, manager_id 
	FROM employees 
    	START WITH manager_id IS NULL 
        	CONNECT BY PRIOR employee_id = manager_id;

위의 쿼리에서 START WITH 절은 계층형 쿼리가 시작되는 행을 결정하며, CONNECT BY 절은 부모 행과 자식 행 간의 관계를 정의합니다. 여기서 PRIOR 키워드는 CONNECT BY 절에서 사용되며, 부모 행 (즉, 매니저)을 참조하게 합니다. 이 쿼리는 manager_id가 NULL인 행 (즉, 최상위 매니저)에서 시작하여 각 직원이 보고하는 매니저를 찾습니다.

따라서 PRIOR 키워드는 계층형 쿼리에서 상위 레벨의 데이터를 참조하는 데 사용되며, 트리 혹은 그래프 구조와 같은 계층적인 데이터를 쿼리할 때 유용합니다.

Employees 테이블:

Employee_ID	Employee_Name	Manager_ID
1	Sarah	NULL
2	John	1
3	Bob	1
4	Alice	2
5	Grace	2
6	David	3
7	Sophia	3

위 테이블에서 Manager_ID는 해당 직원이 보고하는 매니저의 Employee_ID를 나타냅니다. NULL은 상위 매니저가 없음을 의미합니다 (즉, 그 직원이 최상위 매니저임을 의미합니다).

이제 이 테이블에서 모든 직원과 그들이 보고하는 매니저를 조회하는 계층형 쿼리는 다음과 같습니다:

이 쿼리는 Manager_ID가 NULL인 행 (즉, 최상위 매니저)에서 시작하여, 각 직원이 보고하는 매니저를 찾습니다. CONNECT BY PRIOR Employee_ID = Manager_ID는 각 직원의 매니저를 찾는 연결성을 정의합니다. 여기서 PRIOR 키워드는 부모 행 (즉, 매니저)을 참조하게 합니다.

이 쿼리를 실행하면, 각 직원과 그들이 보고하는 매니저의 정보를 얻을 수 있습니다.

셀프조인

동일 테이블 사이의 조인을 말한다 따라서 FROM 절에 동일 테이블이 두번 나나타게 되며 무조건 AS를 붙여 식별이 되어야한다

서브쿼리 사용시 주의사항

서브쿼리를 괄호로 감싸서 사용한다
서브쿼리는 단일행 또는 복수 행비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건이어야 하고 복수행 비교연산자는 서브쿼리의 결과와 상관없다.
서브쿼리에서는 ORDER BY를 사용하지 못한다 ORDER BY절은 SELECT절에서 오직 한개만 올 수 있기 때문에 ORDER BY 절은 메인쿼리의 마지막 문장에 위치해야 한다

인라인뷰

FROM에서 사용되는 서브쿼리를 인라인뷰라고 한다

인라인뷰는 동적으로 생성되는 정보라 저장되지 않음

뷰(VIEW)

독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
편리성 : 복잡한 질의를 뷰로 작성함으로써 관련 질의를 단순한게 작성할 수 있다
자주 사용하는 SQL문을 뷰를 이용하면 편리하게 사용가능
보안성 : 급여정보와 같이 숨기고 싶은 정보가 존재한다면 뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보 제공 가능

RANK() 와 DENSE_RANK()

예시테이블

Employee_IDProduct_Sold

1	10
2	15
3	10
4	20
5	15
6	30
7	30

SQL문

SELECT Employee_ID, 
       Product_Sold, 
       RANK() OVER(ORDER BY Product_Sold DESC) AS Rank,
       DENSE_RANK() OVER(ORDER BY Product_Sold DESC) AS Dense_Rank
FROM Sales;

결과

Employee_ID	Product_Sold	Rank	Dense_Rank
6	30	1	1
7	30	1	1
4	20	3	2
2	15	4	3
5	15	4	3
1	10	6	4
3	10	6	4

RANK는 동일한 등수가 있다면 숫자를 뛰어넘고

DENSE_RANK는 동일한 등수가 있더라도 다음숫자가 오게된다.

ROWNUM

RANK와 DENSE_RANK는 동일한값에 같은 순위를 매기지만

ROWNUM은 동일한 값이라도 고유한 순위를 매긴다.

LAG 와 LEAD

테이블 구성

Employee_ID	Sales_Date	Product_Sold
1	2023-01-01	10
1	2023-01-02	15
1	2023-01-03	20
2	2023-01-01	12
2	2023-01-02	18
2	2023-01-03	22

SQL문

SELECT Employee_ID, 
       Sales_Date, 
       Product_Sold, 
       LAG(Product_Sold) OVER(PARTITION BY Employee_ID ORDER BY Sales_Date) AS Prev_Day_Sales,
       LEAD(Product_Sold) OVER(PARTITION BY Employee_ID ORDER BY Sales_Date) AS Next_Day_Sales
FROM Sales;

결과

Employee_ID	Sales_Date	Product_Sold	Prev_Day_Sales	Next_Day_Sales
1	2023-01-01	10	NULL	15
1	2023-01-02	15	10	20
1	2023-01-03	20	15	NULL
2	2023-01-01	12	NULL	18
2	2023-01-02	18	12	22
2	2023-01-03	22	18	NULL

EMPLOYEE_ID기준으로 이전값( LAG ) 다음값( LEAD )로 가져온것을 확인할 수 있다

저장모듈

SQL문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다 Oracle의 저장 모듈에서는 Procedure, User Defined Function Trigger가 있다.

제3장 SQL 최적화 기본 원리

CBO(Cost Based Optimizer)

비용기반의 옵티마이저라고 불리며 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저를 CBO라고 한다.

실행계획의 실행순서

6 	NESTED LOOPS
3		HASH JOIN
1        		TABLE ACCESS (FULL) TAB1
2           		TABLE ACCESS (FULL) TAB2
5       	TABLE ACCESS (BY ROWID) TAB3
4          		INDEX (UNIQUE SCAN) PK_TAB3

보이는 바와 같이 123이 한묵음이고 45가 한묶음이라고 가정하자

위에서부터 12가실행되고 3번이 실행된다 그뒤 밑의 묶음중 안쪽의 코드먼저 실행되고 바깥쪽이 실행된다.

TIP) 실행계획이 바뀌어도 값은 변하지 않는다

SQL처리 흐름도

SQL의 실행계획을 시각화해서 표현한 것이다. SQL 처리 흐름도만 보고 실행 시간을 알수는 없다.

INDEX

조회만을 위한 오브젝트이며 삽입 삭제 갱신의 경우 오히려 부하를 가중한다
대량의 데이터를 삽입할 경우 모든 인덱스를 제거하고 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다.
SQL Server의 클러스터형 인덱스는 oracle의 IOT와 매우 유사하다.
INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.

TIP) 규칙기반의 옵티마이저에서는 적절한 인덱스가 존재한다면 항상 인덱스를 사용 하려고 한다.

TIP) 전체 테이블 스캔할때는 INDEX를 사용하지 않는게 빠르다

INDEX 구조 종류

B-TREE

브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인데스를 구성하는 컬럼의 값으로 정렬된다 일반적으로 OLTP 시스템 환경에서 가장 많이 사용된다

CLUSTERED

인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다

BITMAP

하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다.

INDEX 생성 쿼리

CREATE INDEX [IDX이름] ON [테이블명] (컬럼명) 두개도 가능

CREATE INDEX IDX_EMP_01 ON EMP (REGIST_DATE, DEPTNO) 두개인예제

SORT MERGE JOIN

칼럼을 기준으로 데이터를 정렬하여 조인을 수행

데이터가 많아 메모리에서 모든 정렬 작업을 수행하는 경우 성능이 떨어지게된다

이럴땐 HASH JOIN을 사용하는게 좋다.

TIP ) HASH JOIN은 EQUI JOIN(" = ")에서만 동작하는 조인방식이다

NL JOIN

주로 랜덤엑세스 방식을 데이터를 읽는다

선택도가 낮은(결과의 행수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리함

SEMI JOIN

EXISTS 절에 주로 나타나고 데이터가 별로 없을땐 NESTED LOOP와 사용되고 데이터가 많을땐 HASH JOIN과 사용됨

가	다
가	라
가	마
가	바
나	다
나	라
나	마
나	바
다	다
다	라
다	마
다	바
라	다
라	라
라	마
라	바

가	다
가	라
가	마
가	바
나	다
나	라
나	마
나	바
다	다
다	라
다	마
다	바
라	다
라	라
라	마
라	바

가	다
가	라
가	마
가	바
나	다
나	라
나	마
나	바
다	다
다	라
다	마
다	바
라	다
라	라
라	마
라	바