2020 정보처리기사 필기 - 3.3 SQL 응용(2)
▶ 104 DML
DML(Data Manipulation Language, 데이터 조작어)
데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
1. 삽입문(INSERT INTO~)
INSERT INTO 테이블명([속성명1, 속성명2, .....])
VALUES(데이터1, 데이터2, ...);
ex1) <사원>테이블에 (이름-홍길동, 부서-인터넷)을 삽입
INSERT INTO 사원(이름, 부서) VALUES('홍길동', '인터넷');
ex2) <사원>테이블에 (장보고, 기획, 05/03/73, 옥계동, 90)을 삽입
INSERT INTO 사원 VALUES('장보고', '기획', #05/03/73#, '옥계동', 90);
ex3) <사원>테이블에 있는 편집부의 모든 튜플을 <편집부원(이름, 생일, 주소, 기본급)> 테이블에 삽입
INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급 FROM 사원 WHERE 부서 = '편집';
2. 삭제문(DELETE FROM~)
DELETE FROM 테이블명 [WHERE조건];
- 모든 레코드 삭제 시 WHERE절 생략
☞ DELETE문은 테이블 내의 튜플들만 삭제, DROP문은 테이블 자체를 삭제
ex1) <사원>테이블에서 "황진이"에 대한 튜플을 삭제
DELETE FROM 사원 WHERE 이름 = '황진이';
ex2) <사원>테이블의 모든 레코드 삭제
DELETE FROM 사원;
3. 갱신문(UPDATE~ SET~)
UPDATE 테이블명 SET 속성명 = 데이터, [속성명=데이터, .....] [WHERE 조건];
ex1) <사원>테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정
UPDATE 사원 SET 주소='수색동' WHERE 이름='홍길동';
ex2) <사원>테이블에서 "홍길동"의 '부서'를 "기획부"로 변경하고 '기본급'을 5만원으로 인상시키시오
UPDATE 사원 SET 부서='기획', 기본급=기본급+5 WHERE 이름='홍길동';
☞ 데이터 조작어 4가지 유형
SELECT~ FROM~ WHERE~
INSERT INTO~ VALUES~
DELETE~ FROM~ WHERE~
UPDATE~ SET~ WHERE~
▶ 105 DML - SELECT(1)
검색문(SELECT~ FROM~)
SELECT [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,
[그룹함수(속성명)],
[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]
FROM 테이블명1, 테이블명2, ...
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
- PREDICATE : 불러올 튜플 수를 제한할 명령어 기술
> ALL : 모든 튜플 검색, 주로 생략
> DISTINCT : 중복된 튜플이 있으면 그중 첫 번째 한 개만 검색
☞ LIKE 연산자
대표문자 |
% |
_ |
# |
의미 |
모든 문자를 대표 |
문자 하나를 대표 |
숫자 하나를 대표 |
1. 기본 검색
ex1) <사원>테이블의 모든 튜플 검색
SELECT * FROM 사원;
SELECT 사원.* FROM 사원;
ex2) <사원>테이블에서 '주소'만 검색하되 같은 '주소'는 한 번만 출력하시오.
SELECT DISTINCT 주소 FROM 사원;
ex3) <사원>테이블의 모든 튜플 검색
SELECT * FROM 사원;
SELECT 사원.* FROM 사원;
2. 조건 지정 검색
ex1) <사원>테이블에서 "기획"부서의 모든 튜플 검색
SELECT * FROM 사원 WHERE 부서='기획';
ex2) <사원>테이블에서 "기획"부서에 근무하면서 "대흥동"에 사는 사람의 튜플 검색
SELECT * FROM 사원 WHERE 부서='기획' AND 주소='대흥동';
ex3) <사원>테이블에서 '부서'가 "기획"이거나 "인터넷"인 튜플 검색
SELECT * FROM 사원 WHERE 부서='기획' OR 부서='인터넷';
ex4) <사원>테이블에서 성이 "김"인 사람의 튜플 검색.
SELECT * FROM 사원 WHERE 이름 LIKE "김%";
ex5) <사원>테이블에서 '생일'이 '01/01/69'에서 '12/31/73' 사이인 튜플 검색
SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#;
ex6) <사원>테이블에서 '주소'가 NULL인 튜플 검색
SELECT * FROM 사원 WHERE 주소 IS NULL;
3. 정렬 검색
ex1) <사원>테이블에서 '주소'를 기준으로 내림차순 정렬시켜 검색
SELECT * FROM 사원 ORDER BY 주소 DESC;
ex2) <사원>테이블에서 '부서'를 기준으로 오름차순 정렬, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜 검색
SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;
4. 하위 질의
ex1) '취미'가 "나이트댄스"인 사원의 '이름'과 '주소' 검색
SELECT 이름, 주소 FROM 사원 WHERE 이름=(SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스');
ex2) 취미활동을 하지 않는 사원들 검색
SELECT * FROM 사원 WHERE 이름 NOT IN(SELECT 이름 FROM 여가활동);
5. 복수 테이블 검색
ex1) '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력' 검색
SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원, 여가활동
WHERE 여가활동.경력>=10 AND 사원.이름=여가활동.이름;
▶ 107 DML - SELECT(2)
검색문(SELECT~ FROM~)
SELECT [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,
[그룹함수(속성명)],
[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]
FROM 테이블명1, 테이블명2, ...
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];
- 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값 집계
> COUNT(속성명) : 그룹별 튜플 수
> AVG(속성명) : 그룹별 평균
> SUM(속성명) : 그룹별 합계
- WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값 집계
> ROW_NUMBER( ) : 윈도우별로 각 레코드에 대한 일련 번호 반환
> RANK( ) : 윈도우별로 순위 반환, 공동 순위 반영
> DENSE_RANK( ) : 윈도우별로 순위 반환, 공동 순위 무시하고 순위 부여
- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 허용. 그룹합수와 함께 사용
- HAVING절 : GROUP BY와 함께 사용, 그룹에 대한 조건 지정
1. WINDOW 함수 이용 검색
ex1) <상여금>테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오. (단, 순서는 내림차순, 속성명은 'NO'로 할것)
SELECT 상여내역, 상여금
ROW_NUMBER( ) OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금
ex2) <상여금>테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오. (단, 순서는 내림차순, 속성명은 '순위'로 할것)
SELECT 상여내역, 상여금
RANK( ) OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 순위
FROM 사원 WHERE 부서='기획' AND 주소='대흥동';
2. 그룹 지정 검색
ex1) <상여금>테이블에서 '부서'별 '상여금'의 평균을 구하시오.
SELECT 부서, AVG(상여금) AS 평균 FROM 상여금
GROUP BY 부서;
ex2) <상여금>테이블에서 부서별 튜플 수 검색
SELECT 부서, COUNT(*) AS 사원수 FROM 상여금
GROUP BY 부서;
ex3) <상여금>테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오
SELECT 부서, COUNT(*) AS 사원수 FROM 상여금
WHERE 상여금>=100
GROUP BY 부서
HAVING COUNT(*)>=2;
검색문(SELECT~ FROM~)
SELECT 속성명1, 속성명2, ......,
FROM 테이블명1, 테이블명2, ...
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ......
FROM 테이블명1, 테이블명2, ...
[ORDER BY 속성명 [ASC | DESC]];
- 집합 연산자의 종류
> UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행 한번만 출력
> UNION ALL : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행도 그대로 출력
> INTERSECT : 두 SELECT문의 조회 결과 중 공통된 행만 출력
> EXCEPT : 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행 출력
3. 집합 연산자를 이용한 통합 질의
ex1) <사원>테이블과 <직원>테이블을 통합하는 질의문을 작성하시오 (단, 같은 레코드 중복 X)
SELECT * FROM 사원
UNION
SELECT * FROM 직원;