SQL 기본 및 활용 - 1장

🔍 1장 SQL 기본 🔍

---

1. 🎈SQL 문장 종류🎈

명령어 종류	명령어	설명
데이터 조작어(DML)	SELECT	데이터 베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어. RETRIEVE라고도 함.
	INSERT UPDATE DELETE	데이터베이스이 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어.
데이터 정의어(DDL)	CREATE ALTER DROP RENAME	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어. 생성, 변경, 삭제 그리고 이름을 변경하는 데이터 구조와 관련된 명령어.
데이터 제어어(DCL)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어.
트랜잭션 제어어(TCL)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말함.

---

2. 🎈DML🎈

​

비절차적 데이터 조작어 (Nonprocedural DML) : 사용자가 무슨(What) 데이터를 원하는 지만을 명세함. - As-Is

절차적 데이터 조작어 (Procedural DML) : 어떻게(How) 데이터를 접근해야 하는지 명세함. - To-Be

절차적 데이터 조작어의 예 ( PL/SQL(오라클) , T-SQL(SQL Server) 등 )

​

호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(Data Sub Language)라고 함.

---

3. 📌PK 지정하는 ALTER TABLE 문장

 

      

      

      

    
    

      <sql />
    
ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY (column_name);

PK = UNIQUE & NOT NULL의 특징.

UNIQUE - 테이블 내에서 중복되는 값이 없지만 NULL 입력이 가능함.

---

4. 📌외래키 (FK, FOREIGN KEY)

- 테이블 생성시 설정 가능

- NULL값 가질 수 있음

- 한 테이블 내에 여러개 존재 가능

- 외래키 값은 참조 무결성 제약을 받을 수 있음

---

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

 

      

      

      

    
    

      <sql />
    
#Oracle
ALTER TABLE 테이블명 MODIFY
(컬럼명1 데이터유형 [DEFAULT 식] [NOT NULL 분류],
컬럼명2 데이터유형 [DEFAULT 식] [NOT NULL 분류], ...);

 

      

      

      

    
    

      <sql />
    
#SQL server
ALTER TABLE 테이블명 ALTER COLUMN 컬럼명1 데이터유형 [DEFAULT 식] [NOT NULL 분류];
ALTER TABLE 테이블명 ALTER COLUMN 컬럼명2 데이터유형 [DEFAULT 식] [NOT NULL 분류];

SQLServer

​

- 괄호사용 x

- 여러개의 컬럼을 동시에 수정하는 구문 지원 x

- NOT NULL 구문 지정하지 않을 시 제약조건 Default는 NULL

---

6. 테이블 컬럼 삭제

      

      

      

    
    

      <sql />
    
ALTER TABLE 테이블명 DROP COLUMN 컬럼명

---

​

7. 📌RENAME

      

      

      

    
    

      <sql />
    
RENAME 바꿀 테이블명 TO 바꾸고 싶은 테이블 명​

---

8. 📌참조동작(Referential Action)

​

1. Delete(Modify) Action

- ​Cascade​ : Master 삭제 시 Child 같이 삭제

- Set Null : Master 삭제 시 Child 해당 필드 Null

- Set Default : Master 삭제 시 Child 해당 필드 Default 값으로 설정

- Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용

- No Action : 참조무결성을 삭제 / 수정 액션을 취하지 않음

​

2. Insert Action

- Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력

- Set Null : Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리

- Set Default : Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력

- Dependent: Master 테이블에 PK가 존재할 때만 Child 입력 허용

- No Action : 참조 무결성을 위반하는 입력 액션을 취하지 않음

---

9. 📌TRUNCATE / DROP / DELETE

​

TRUNCATE - 테이블 자체가 삭제 되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되어 저장 공간을 재사용 가능하도록 해제함

DROP - 테이블 구조를 완전히 삭제함

👉 로그를 남기지 않음

​

DELETE - 데이터 복구 가능, 로그를 남김. DELETE에 조건이 없으면 TRUNCATE와 동일하게 데이터가 삭제 되고 테이블 스키마만 남음

---

DROP	TRUNCATE	DELETE
DDL	DDL(일부 DML 성격 가짐)	DML
Rollback 불가능		Commit 이전 Rollback 가능
Auto Commit		사용자 Commit
테이블이 사용했던 Storage를 모두 Release	테이블이 사용했던 Storage중 최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음
테이블의 정의 자체를 완전히 삭제함	테이블을 최초 생성된 초기 상태로 만듬	데이터만 삭제
로그를 남기지 않음		로그를 남김

---

10. 📌SELECT [ALL/DISTINCT]

​

ALL - 중복된 데이터가 있어도 모두 출력. Defualt값임.

DISTINCT - 중복된 데이터가 있는 경우 1건으로 처리해서 출력.

---

11. 📌트랜잭션의 특성(ACID)📌

특성	설명
원자성 (Atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되든 아니면 전혀 실행되지 않은 상태로 남아 있어야 함 (All or Nothing)
일관성 (Consistency)	트랜잭션이 실행 되기 전의 데이터베이스 내용이 잘못 되지 않았다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안됨
고립성 (Isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
지속성 (Durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장

---

12. 📌트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점

​

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

​

ORACLE에서는 DDL 문장 수행 후 자동으로 COMMIT을 수행함

SQL Server에서는 DDL 문장 수행 후 COMMIT 수행 X

---

13. 📌NULL

​

NULL의 연산

- NULL 값과의 사칙연산은 NULL 값을 리턴

- NULL 값과의 비교연산은 FALSE를 리턴

- 특정 값보다 크다, 적다라고 표현할 수 없음

​

NULL 값을 조건절에서 사용하는 경우 IS NULL / IS NOT NULL이란 키워드를 사용해야 함

​

NULL의 특성

- NULL은 아직 정의되지 않은 값으로 0또는 공백과 다름

- 테이블 생성시 NOT NULL또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL값 포함 가능

- NULL을 포함하는 연산의 경우 결과 값도 NULL

​

단일행 NULL 관련 함수 종류

일반형 함수	함수 설명
NVL(표현식1, 표현식2) - ORACLE함수 ISNULL(표현식1, 표현식2) - SQL Server함수	표현식1의 결과값이 NULL이면 표현식2의 값을 출력. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 함.
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴.
COALESCE(표현식1, 표현식2, ...)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄. 모든 표현식이 NULL이라면 NULL을 리턴. 집계함수 COUNT(표현식), SUM, AVG는 표현식의 NULL값을 제외하고 출력함 단, COUNT(*)은 NULL값을 포함한 행의 수를 출력

집계함수 COUNT(표현식), SUM, AVG는 표현식의 NULL값을 제외하고 출력함

단, COUNT(*)은 NULL값을 포함한 행의 수를 출력

---

ORACLE에서는 ''으로 INSERT하는 경우 NULL로 입력되지만 SQL Server는 '' 그대로 입력됨

👉 데이터 조회시 ORACLE에서는 컬럼명 IS NULL 조건으로 조회하고, SQL Server에서는 컬럼명 = ''로 조회해야함

​

ORACLE에서는 NULL값을 가장 큰 값으로 간주하여 오름차순에서 정렬했을 경우 가장 마지막으로 나타남.

SQL Server에서는 NULL값을 가장 작은 값으로 간주하여 오름차순에서 정렬했을 경우 가장 먼저 나타남.

---

14. 📌함수

1. 사용자 정의 함수
2. 내장함수

- 단일행함수 : 추출되는 각 행마다 작업을 수행. 각 행마다 하나의 결과를 반환. SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용가능

데이터 타입 변경가능, 중첩해서 사용가능

- 다중행 함수 : 여러개의 행이 입력되어 하나의 값 반환. 집계함수/그룹함수/윈도우함수

---

15. 📌DATE 타입

​

ORACLE은 세기, 년, 월, 일, 시, 분, 초 형식의 날짜를 내부적으로는 숫자 형태로 저장 → 연산가능

DATE + NUMBER , DATE - NUMBER : 여기서 NUMBER은 일수를 의미함

​

1일 - SYSDATE + 1

1시간 - SYSDATE + 1/24

10분 - SYSDATE + 1/24/6

1분 - SYSDATE + 1/24/60

---

SEARCHED_CASE_EXPRESSION → SIMPLE_CASE_EXPRESSION 예시

CASE WHEN LOC='NEW YORK' THEN 'EAST' → CASE LOC WHEN 'NEW YORK' THEN 'EAST'

---

16. 📌SELECT 문장 (GROUP 함수, GROUP BY절, HAVING 절, ORDER BY절)

​

- SELECT시 HAVING절에서 그룹함수와 관련된 조건이라면 어떤 함수가 사용되든 조건으로 표현됨에 오류가 없음.

- SELECT시 그룹함수를 제외한 컬럼은 GROUP BY절에서 나타나야함.

HAVING절에서 그룹함수에 대한 조건이 아닌 경우 GROUP BY절에서 사용된 컬럼에 대한 조건이 될 때 오류가 없음.

- 중첩 그룹 함수를 사용하는 경우 GROUP BY절에서 에러가 발생함.

- ORDER BY절은 GROUP BY절과 관련하여 그 기준이 되는 컬럼들 또는 그룹 함수가 사용 가능함

- GROUP BY 절을 사용하지 않는 경우 테이블이 갖는 컬럼이라면 조회 list가 아니어도 가능

​

SELECT 문장 실행 순서 👉 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

1. FROM : 발췌 대상 테이블 참조
2. WHERE : 발췌 대상 데이터가 아닌 것은 제거
3. GROUP BY : 행들을 소그룹화 함
4. HAVING : 그룹핑 된 값의 조건에 맞는 것만을 출력
5. SELECT : 데이터 값을 출력/계산
6. ORDER BY : 데이터 정렬

---

17. 📌SQL Server의 TOP(N) 질의문

​

N에 해당 하는 값이 동일한 경우 함께 출력되도록 하는 WITH TIES 옵션을 ORDER BY 절과 함께 사용

예시) SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

---

18. 📌JOIN

​

1. 두 개 이상의 테이블 들을 연결또는 결합하여 데이터를 출력하는 것.
2. 일반적인 경우 행들은 PK나 FK값의 연관에 의해 JOIN이 성립. 하지만 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능.
3. DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 임의로 2개 정도씩 묶어서 Join을 수행
4. EQUI JOIN은 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우 사용
5. 대부분 Non EQUI JOIN을 수행할 수 있지만 때로는 설계상의 이유로 수행이 불가능한 경우도 있음