[DB] 데이터베이스 언어 SQL(Structured Query Language) - DML(검색)

SQL 기능에 따른 분류

- 데이터 정의어(DDL : Data Definition Language)

• 테이블 생성, 변경, 제거
• CREATE, ALTER, DROP 문

- 데이터 조작어(DML : Data Manipulation Language)

• 생성된 테이블에 데이터를 검색, 삽입, 수정, 삭제
• SELECT, INSERT, UPDATE, DELETE 문

- 데이터 제어어(DCL : Data Control Language)

• 사용자 접근 제어, 백업과 회복, 동시성 제어 등
• GRANT, REVOKE, COMMIT, ROLLBACK 문

DML : 검색 (SELECT)

- 기본 문법

SELECT [DISTINCT] 속성이름
FROM 테이블이름
[WHERE 검색조건]
[GROUP BY 속성이름]
[HAVING 검색조건]
[ORDER BY 속성이름]

 

- SQL과 관계 대수

 

1. SELECT 절 : 데이터 검색

- 관계 DB에서 정보를 검색하는 SQL문 : 테이블에서 행과 열의 일부를 추출

- SQL문 중 가장 자주 사용

 

- SELECT / FROM

• * : 모든 속성 / 릴레이션에 있는 모든 속성 검색

SELECT * 
FROM emp;

 

SELECT *
FROM dept;

• 열들의 목록 지정 : 원하는 속성만 추출

SELECT empno, ename, job, sal
FROM emp;

• 열의 산술 연산

SELECT empno, ename, sal, sal*12
FROM emp;

• DISTINCT : 중복 행(투플) 제거

SELECT DISTINCT job
FROM emp;

 

2. WHERE 절 : 조건 연산자 사용

- 연산자 종류

	연산자	예
비교 연산자	=, <>, <, <=, >, >=	price < 20000
논리 연산자	AND, OR, NOT	(price < 20000) AND (publisher = '굿스포츠')
범위 검색 연산자	BETWEEN	price BETWEEN 10000 AND 20000
목록 검색 연산자	IN, NOT IN	price IN (10000, 20000, 30000)
문자열 패턴 매칭 연산자	LIKE	bookname LIKE '축구%'
NULL 비교 연산자	IS NULL, IS NOT NULL	price IS NULL

 

- 비교 연산자 (=, <>, <, <=, >, >=)

• <> ≒ !=

SELECT empno, job, sal
FROM emp
WHERE sal >= 1500;

 

- 논리 연산자 (AND, OR, NOT)

SELECT empno, job, sal
FROM emp
WHERE sal >= 1500 AND job = 'SALESMAN';

 

- 범위 검색 연산자 (BETWEEN)

• 형식 : 표현식 BETWEEN 최솟값 AND 최댓값

(1) 정수 데이터형

SELECT ename, job, sal
FROM emp
WHERE sal BETWEEN 3000 AND 6000;

 

(2) 문자 데이터형

SELECT ename, job, sal
FROM emp
WHERE ename BETWEEN 'BLACK' AND 'JAMES';

 

(3) 날짜 데이터형

SELECT ename, job, hiredate
FROM emp
WHERE hiredate BETWEEN '82/01/01' AND '87/12/31';

 

- 목록 검색 연산자 (IN, NOT IN)

• 목록 안의 값 중 하나와 일치하는지 여부를 파악 / 리스트 중에 있으면 출력
• 형식 : 표현식 IN(NOT IN) (값 리스트)
• 값 리스트에 서브 쿼리가 올 수도 있음

SELECT ename, job
FROM emp
WHERE job IN('MANAGER','PRESIDENT');

SELECT ename, job
FROM emp
WHERE job = 'MANAGER' OR job = 'PRESIDENT';

 

- 문자열 패턴 매칭 연산자 (LIKE)

• 소량의 문자열 검색에 사용
• 형식 : 매칭_표현식 LIKE 패턴(와일드 문자)

와일드 문자	의미
%	문자가 0개 이상인 문자열
_	단일 문자

SELECT ename, job
FROM emp
WHERE ename LIKE 'A%'; -- A로 시작하는 문자열

 

SELECT ename, job
FROM emp
WHERE ename LIKE '%M%'; -- M이 포함된 문자열

 

SELECT ename, job
FROM emp
WHERE ename LIKE '_L%'; -- 두번째 글자가 L인 문자열

 

- NULL 비교 연산자 (IS NULL, IS NOT NULL)

• NULL : 값이 없는 상태 / 숫자 0도 아니고 공백도 아님
• NULL 연산을 하기 위해서는 특수한 연산자 IS / IS NOT을 사용해야 한다

SELECT ename, mgr, comm
FROM emp
WHERE comm IS NULL;

 

※ 집계 함수 (Aggregate Function) ※

• 여러 투플들의 집단에 적용되는 함수
• 속성에 적용되어 단일 값을 반환

집계 함수	문법	사용 예
SUM	SUM(속성이름)	SUM(price)
AVG	AVG(속성이름)	AVG(price)
COUNT	COUNT(속성이름) / COUNT(*)	COUNT(price) / COUNT(*)
MAX	MAX(속성이름)	MAX(price)
MIN	MIN(속성이름)	MIN(price)

 

3. GROUP BY 

• 형식 : GROUP BY 표현식 [,.... n]
• 속성 값이 같은 값끼리 그룹을 만든다
• 표현식에 집계 함수를 사용하지 못한다
• SELECT 절의 속성 목록 중 집계 함수를 제외한 나머지 속성은 표현식에 반드시 나와야 한다
• 즉, SELECT 절에 집계 함수가 아닌 속성이 있으면 무조건 GROUP BY 절 작성

SELECT deptno, SUM(sal) AS "급여 합계"
FROM emp
GROUP BY deptno;

 

4. GROUP BY... HAVING 

• 그룹에 대한 조건 (HAVING)
• HAVING 절은 반드시 GROUP BY 절과 함께 작성해야 한다
• WHERE 절보다 뒤에 나타나야 한다
• HAVING 뒤에는 반드시 집계 함수가 와야 한다 / GROUP BY 뒤에는 반드시 집계 함수가 아니어야 한다

SELECT deptno, SUM(sal) AS "급여 합계"
FROM emp
GROUP BY deptno
HAVING SUM(sal) >= 9000;

 

5. ORDER BY : 행 정렬

• 형식 : ORDER BY 표현식 [ASC | DESC]
• 데이터가 출력되는 순서는 보장할 수 없다
• 행들을 특정 속성 기준으로 정렬하고자 할 때 사용
• ASC = 오름차순 / DESC = 내림차순 / default = ASC (생략 가능)
• ASC나 DESC는 하나의 속성에만 적용된다

SELECT job, ename, sal
FROM emp
ORDER BY job DESC, ename;

 

6. 조인 (Join)

• 1개 이상의 릴레이션으로부터 연관된 투플을 결합하는 것
• 속성들 간 공통된 값(기본 키 / 외래 키)을 사용하여 조인 실행 : 조인 속성의 이름은 달라도 도메인은 같아야 함
• 종류 : 내부 조인, 외부 조인

- 내부 조인 (Inner Join)

• 가장 일반적인 조인
• 각 테이블에서 비교 연산자에 의한 조인 조건을 만족하는 행들만 포함
• 조인하는 테이블의 2 개의 속성에서 공통된 값이 없으면 테이블로부터 행을 반환하지 않는다
• FROM 절에 INNER JOIN을 입력하려면 그다음 절에 ON 절이 나와야 한다

방법1)
SELECT empno, ename, job, sal, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno AND (sal >= 1500 AND sal <= 3000)
ORDER BY sal;

방법2)
SELECT empno, ename, job, sal, dname, loc
FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE sal >= 1500 AND sal <= 3000
ORDER BY sal;

- 외부 조인 (Outer Join)

• 정상적으로 조인 조건을 만족하지 못하는 행들을 보기 위해 외부 조인 사용

왼쪽 외부 조인

SELECT a.deptno, b.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno(+);

SELECT a.deptno, b.deptno
FROM emp a LEFT OUTER JOIN dept b
ON a.deptno = b.deptno;

 

오른쪽 외부 조인

SELECT a.deptno, b.deptno
FROM emp a, dept b
WHERE a.deptno(+) = b.deptno;

SELECT a.deptno, b.deptno
FROM emp a RIGHT OUTER JOIN dept b
ON a.deptno = b.deptno;

 

완전 외부 조인

SELECT a.deptno, b.deptno
FROM emp a FULL OUTER JOIN dept b
ON a.deptno = b.deptno;

 

7. 부속 질의 (Sub Query)

• 하나의 SQL 문(주 질의)에 중첩된 SELECT 문
• 부속 질의는 주 질의 이전에 한번 실행
• 부속 질의의 결과는 주 질의에 의해 사용

- 유형

• SELECT 절에서 사용 : 스칼라 부속 질의
• FROM 절에서 사용 : 인라인 뷰
• WHERE 절에서 사용 : 중첩 질의 (단일 행 부속 질의 / 다중 행 부속질의 / 다중 열 부속 질의)

(1) SELECT 절에서 사용

• 부속 질의의 결과 값은 1개의 행, 1개의 열 값만 가능
• SELECT max(sal), min(sal) FROM emp -> 열의 개수가 1개가 아님 -> 오류
• SELECT sal FROM emp -> 행의 개수가 1개가 아님 -> 오류
• SELECT max(sal) FROM emp -> 열의 개수가 1개이고 행의 개수가 1개이다 -> 성공

-- 각 사원의 급여와 최대 급여를 받는 사원에 대한 각 사원의 급여율

SELECT empno, sal, sal/(SELECT MAX(sal) FROM emp) AS "최대 급여 대비 각 사원 급여율"
FROM emp
ORDER BY sal DESC;

 

(2) FROM 절에서 사용

-- 급여가 2000 이상인 사원들에 대한 부서번호별 사원수

SELECT emp2000.deptno, COUNT(*)
FROM (SELECT empno, ename, deptno
      FROM emp
      WHERE sal > 2000)emp2000,
      dept d
WHERE emp2000.deptno = d.deptno
GROUP BY emp2000.deptno
ORDER BY 1;

 

(3-1) WHERE 절에서 사용 : 단일 행 부속 질의

• 오직 하나의 행 반환
• 단일 행 연산자(=, <>, <, <=, >, >=)만 사용 가능

-- 사원번호가 7369인 사원과 같은 업무를 하는 사원의 이름과 사원번호, 업무

SELECT ename, empno, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE empno = 7369);

 

(3-2) WHERE 절에서 사용 : 다중 행 부속 질의

• 1개 이상의 행을 반환하는 부속 질의
• 복수 행 연산자(IN, NOT IN) 사용 가능

-- 'ACCOUNTING' 부서나 'RESEARCH' 부서에서 근무하는 사원들의 이름

SELECT ename
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE dname = 'ACCOUNTING' OR dname = 'RESEARCH');

SELECT ename
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE dname IN('ACCOUNTING', 'RESEARCH'));

 

(3-3) WHERE 절에서 사용 : 다중 열 부속 질의

• 부속 질의의 결과값이 2개 이상의 열을 반환하는 부속질의

-- 업무별 최소 급여를 받는 사원의 사원번호, 사원이름, 직책, 급여, 부서번호

SELECT empno, ename, job, sal, deptno
FROM emp
WHERE (job, sal) IN (SELECT job, MIN(sal) FROM emp GROUP BY job);

 

8. 집합 연산 (UNION, INTERSECT, MINUS)

• 형식 : SELECT 1 ~ SET연산자 SELECT 2 ~
• SELECT 문장의 열들은 개수가 일치해야 하고, 대응되는 열들의 데이터형이 서로 호환성이 있어야 함
• 최종적인 결과 집합의 열 이름은 SELECT 1의 것을 따른다
• 기본적으로 중복된 행은 제거된다
• 기본적으로 결과 집합은 첫 번째 열 값으로 정렬된다

(1) 합집합 (UNION)

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;

 

(2) 교집합 (INTERSECT)

SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

 

(3) 차집합 (MINUS)

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;