[데이터베이스 - 6] SQL

SQL

SQL(Structured Query Language)는 관계 DB의 표준 질의어이다. SQL에는 데이터 정의어, 조작어, 제어어를 모두 포함하며 사용자는 SQL로 RDB에 지시를 내린다. 모든 SQL문은 세미콜론(;)으로 문장 끝을 표시하며 INSERT, SELECT와 같은 키워드는 대소문자를 구분하지 않는다.

테이블 생성

SQL 테이블 생성문이다. [ ] 로 표시한 항목은 생략 가능하다.

CREATE TABLE 테이블_이름 (
			1️⃣ 속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본값]
        	2️⃣ [PRIMARY KEY (속성_리스트)]
        	3️⃣ [UNIQUE (속성_리스트)]
        	4️⃣ [FOREIGN KEY (속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
        	[ON DELETE 옵션] [ON UPDATE 옵션]
        	5️⃣ [CONSTRAINTS 이름] [CHECK(조건)]
);

1. 속성 정의 : 속성 이름, 데이터 타입(도메인), 널 값 허용 여부, 기본 값 허용 여부를 정의한다. NOT NULL을 설정하면 해당 속성은 NULL 값을 가질 수 없다. 또한 사용자가 속성에 값을 입력하지 않았을 때 해당 속성에 NULL 값이 기본으로 저장되는데 DEFAULT 값을 설정했다면 이 기본 값으로 저장된다.

 

2. 기본키 지정 : PRIMARY KEY 키워드로 기본키를 지정한다.

 

3. 대체키 지정 : UNIQUE 키워드로 대체키를 지정한다.

 

4. 외래키 지정 : FOREIGN KEY 키워드로 외래키를 지정하고 REFERENCES 키워드로 어떤 테이블의 무슨 속성을 참조하는지 명시한다. 참조되는 테이블의 튜플이 삭제되거나 변경될 때 처리 방법을 ON DELETE 옵션과, ON UPDATE 옵션으로 선택한다.

2개의 테이블

참조되는 테이블의 튜플이 삭제될 때 처리 방법을 ON DELETE 옵션으로 선택한다. 선택하지 않으면 ON DELETE NO ACTION이 기본으로 선택된다.

• ON DELETE NO ACTION : 튜플을 삭제하지 못하게 한다. 부서번호가 3인 튜플을 참조하는 사원 튜플(정소화)이 있으므로 부서번호가 3인 부서 튜플을 삭제하지 못하게 한다.
• ON DELETE CASCADE : 관련 튜플을 함께 삭제한다. 부서번호가 3인 튜플을 삭제하면 그 값을 참조하고 있는 정소화 사원의 튜플도 함께 삭제한다.
• ON DELETE SET NULL : 관련 튜플의 외래키 값을 NULL로 변경한다. 부서번호가 3인 튜플을 삭제하면 그 값을 참조하고 있는 정소화 사원의 소속부서 속성 값을 NULL로 설정한다.
• ON DELETE SET DEFAULT : 관련 튜플의 외래키 값을 미리 저장한 기본 값으로 변경한다. 부서번호가 3인 튜플을 삭제하면 그 값을 참조하고 있는 정소화 사원의 소속부서 속성 값을 미리 지정한 기본 값으로 변경한다.

참조되는 테이블의 튜플이 변경될 때 처리 방법을 ON UPDATE 옵션으로 선택한다. 선택하지 않으면 ON UPDATE NO ACTION이 기본으로 선택된다.

• ON UPDATE NO ACTION : 투플을 변경하지 못하게 한다.
• ON UPDATE CASCADE : 관련 투플을 함께 변경한다.
• ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
• ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

5. CHECK 키워드를 사용해 특정 속성에 대한 제약 조건을 지정하고 CONSTRAINTS 키워드로 그 제약 조건의 이름을 부여한다.

SELECT

아래는 select 실행 순서이다.

7️⃣ SELECT 속성_리스트
1️⃣ FROM 테이블_리스트
2️⃣ [On 절]
3️⃣ [WHERE 절]
4️⃣ [GROUP BY 속성_리스트 [5️⃣ HAVING 조건]]
6️⃣ [ORDER BY 속성_리스트 [ASC | DESC]];

2️⃣ : 조인 대상을 필터링하고 조인한다. 참고로 Inner Join의 On 절은 where 절을 사용할 때와 같으므로 보통 On 절은 Outer Join에서만 사용된다.

3️⃣  : 개개의 투플에 조건을 건다.

4️⃣, 5️⃣ : 그룹화 한 후 개개의 그룹에 조건을 걸때는 HAVING 조건을 사용한다. 보통 집계함수와의 비교식으로 쓰인다.  GROUP BY 사용 시 튜플의 개수는 그룹의 개수로 압축된다. 이 때문에 HAVING, SELECT, ORDER BY절에는 그룹 기준 속성 또는 그룹에 대한 집계 함수 속성을 사용하는 것이 좋다.

 

각 단계를 거치면서 결과 테이블이 어떻게 될지 예측된다면 SELECT 문을 쉽게 작성할 수 있다. 

 

• SELECT문 기본 형식

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트;

검색 결과를 테이블로 가져온다. 테이블이기 때문에 투플이 중복될 수 있다. 투플의 중복을 제거하려면 DISTINCT 키워드를 명시하며 디폴트는 ALL이다.

 

• 조건 검색(WHERE)

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건];

WHERE 키워드를 사용해 조건에 만족하는 투플만 가져온다.

1. 비교 연산자 (=, <>, <, >, <=, >=) : 숫자뿐 아니라 문자나 날짜 값을 비교할 수도 있다.
2. 논리 연산자 (AND,OR,NOT)
3. NULL 검색 : 특정 속성의 값이 널 값인지 비교하려면 IS NULL 키워드를 널 값이 아닌지를 비교하려면 IS NOT NULL 키워드를 사용한다. 널 값에 1번의 비교 연산자를 적용하면 무조건 결과는 false이다.
4. LIKE 검색 : 문자열에 조건을 걸 때 유용하다.

 

• 정렬 검색(ORDER BY)

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[ORDER BY 속성_리스트 [ASC | DESC]];

결과 테이블을 정렬하려면 ORDER BY 키워드를 사용한다. ORDER BY 키워드와 함께 정렬 기준이 되는 속성을 지정한다. 널 값은 오름차순에서 맨 마지막으로 내림차순에서는 맨 처음 출력된다.

 

• 그룹별 검색(GROUP BY)

SELECT [ALL | DISTINCT] 속성_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 속성_리스트 [HAVING 조건]]
[ORDER BY 속성_리스트 [ASC | DESC]];

그룹핑을 하려면 GROUP BY 키워드를 사용한다. GROUP BY 키워드와 함께 그룹핑의 기준이 되는 속성을 지정한다. 그 후 그룹에 대한 조건을 추가하려면 HAVING 키워드와 함께 작성한다. GROUP BY 키워드가 없는 SELECT 문은 테이블 전체를 하나의 그룹으로 본다.

 

GROUP BY 사용 시 튜플의 개수는 그룹의 개수로 압축된다. 이 때문에 HAVING, SELECT, ORDER BY절에는 그룹 기준 속성 또는 그룹에 대한 집계 함수 속성을 사용하는 것이 좋다.

 

• 집계 함수를 이용한 검색

특정 속성 값을 통계적으로 계산하기 위해 집계 함수를 사용한다. 집계 함수는 널인 속성 값은 제외하고 계산한다. 집계 함수는 WHERE 절에서 사용할 수 없다.

집계 함수

SELECT SUM(재고량) AS "재고량 합계"
FROM 제품
WHERE 제조업체 = '한빛제과';

-- 오류, 성별의 투플 개수는 중복을 제거해도 최소 2개--
SELECT 성별,AVG(나이)
FROM 학생