데이터베이스 프로그래밍 정리

i009727 2021. 5. 10. 17:01

// ACCOUNTING이나 RESEARCH부서에서 일하는 사원들의 이름 출력
SELECT ename
FROM emp
WHERE deptno IN (SELECT deptno
                    FROM dept
                    WHERE dname='ACCOUNTING' OR dname='RESEARCH')

# ALTER TABLE 기본 문법

ALTER TABLE [테이블이름]
	[ADD 속성이름 데이터타입]
    [DROP CULUMN 속성이름]
    [MODIFY 속성이름 데이터타입]
    [MODIFY 속성이름 [NULL|NOT NULL]]
    [ADD PRIMARY KEY(속성이름)]
    [[ADD|DROP] 제약조건이름]

# DML 기본 문법

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

# WHERE절의 조건으로 사용가능한 연산자

	연산자	예
비교연산자	=, <>, !=, <, <=, >, <=	price < 1000
논리연산자	AND, OR, NOT	(price<200) AND (price>1000)
범위검색연산자	BETWEEN	price BETWEEN 1000 AND 2000
목록검색연산자	IN, NOT IN	price IN (100, 200, 300)
패턴매칭연산자	LIKE	bookname LIKE 'data%'
NULL비교연산자	IS NULL, IS NOT NULL	price IS NOT NULL

# 집계함수

: 여러 튜플에 적용되어 단일값을 반환

- NULL값은 집단함수 대상에서 제외된다.

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

SELECT * FROM emp;

SELECT SUM(comm)
FROM emp;

SELECT MAX(comm), MIN(comm)
FROM emp;

//COUNT(comm): comm값의 개수 중 NULL값이 아닌 튜플의 개수 출력 
//COUNT(*): 전체 튜플 개수 출력
SELECT COUNT(comm), COUNT(*)
FROM emp;

# GROUP BY

: 속성이 같은 값끼리 그룹을 생성

주의사항

- 표현식(GROUP BY문)에 집계함수 사용 불가능

- SELECT문 내 속성 중, 집계함수에 사용되지 않은 모든 속성은 표현식(GROUP BY문)에서 사용되야함

- HAVING절 뒤에는 반드시 집계함수가 와야함

//AS로 별칭을 설정할때는 큰따옴표 사용
SELECT deptno, SUM(sal) AS "급여합계"
FROM emp
GROUP BY deptno

//sal>1000인 튜플만 그루핑대상이됨
SELECT deptno, SUM(sal) AS "급여합계"
FROM emp
WHERE sal>1000
GROUP BY deptno
HAVING SUM(sal)>=9000

# ORDER BY

- ASC(1): 오름차순

- DESC(0): 내림차순

//GROUP BY문 속성이름 이후에는 ASC, DESC만 사용가능
SELECT deptno, SUM(sal) AS "급여합계"
FROM emp
GROUP BY deptno
ORDER BY "급여합계" ASC;

# 집합연산자

SELECT 문장1

집합연산자

SELECT 문장2

- SELECT문의 열의 개수가 일치해야하며, 대응되는 데이터형은 호환성을 가져야함

- 최종 결과의 열 이름은 선행문장(문장1)의 이름을 따른다

UNION	합집합
UNION ALL	합집합(중복 허용)
INTERSECT	교집합
MINUS	차집합

# JOIN연산자

: 1개 이상의 릴레이션으로부터 연관된 튜플을 결합하는것

- 속성들간의 공통된값(기본키 OR 외래키)를 이용하여 조인 실행

SELECT empno, ename, job, dname, loc
FROM emp, dept
WHERE emp.deptno=dept.deptno AND sal>=2000;

//INNER JOIN의 INNER는 생략 가능
SELECT empno, ename, job, dname, loc
FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE sal>=2000;

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, dept b
WHERE a.deptno(+) = b.deptno;

//LEFT OUTER JOIN: 왼쪽 외부조인
//FULL OUTER JOIN: 완전 외부조인

//셀프조인
SELECT worker.empno, worker.ename, manager.empno "관리자번호", manager.ename "관리자명"
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;

SELECT worker.empno, worker.ename, manager.empno "관리자번호", manager.ename "관리자명"
FROM emp worker INNER JOIN emp manager
ON worker.mgr = manager.empno;

SELECT worker.empno, worker.ename, manager.empno "관리자번호", manager.ename "관리자명"
FROM emp worker LEFT OUTER JOIN emp manager
ON worker.mgr = manager.empno;

# 서브질의

: 하나의 SQL문에 중첩된 SELECT문이 존재

유형

- SELECT절에서 사용: 스칼라 부속질의

- 서브질의의 결과는 1개의 행, 1개의 열 값만 가능

- FROM절에서 사용: 인라인뷰

- WHERE절에서 사용: 중첩질의(단일행 / 다중행 / 다중열 부속질의)

// 사원의 급여와 최대급여를 받는 사원에 대한 각 사원의 급여율
SELECT empno, sal, sal/(SELECT MAX(sal)
                        FROM emp) 급여율
FROM emp;

// 급여가 2000이상인 사원들에 대한  부서별 사원수
SELECT deptno, count(*)
FROM (SELECT empno, ename, deptno
        FROM emp
        WHERE sal>2000)
GROUP BY deptno

// 사원번호가 7369인 사원과 같은 직위를 갖는 사원의 이름과 사원번호, 업무 출력	
SELECT ename, empno, job
FROM emp
WHERE job = (SELECT job
                FROM emp
                WHERE empno=7369)

// 업무별 최소급여를 받는 사원의 사원번호, 이름, 급여, 부서번호 출력
SELECT empno, ename, sal, deptno
FROM emp
WHERE (job, sal) IN (SELECT job, MIN(sal)
                        FROM emp
                        GROUP BY job)

## SELECT문이 아닌 구문에서의 서브질의 사용

// emp테이블과 동일한 emp1테이블 생성
CREATE TABLE emp1 AS (SELECT * FROM emp);

// emp1테이블에 테스트용 튜플 삽입
// 마지막 사원번호 이후의 사원번호를 갖는 데이터 삽입
INSERT INTO emp1(empno, ename) VALUES( (SELECT max(empno)+1
                                        FROM emp), 'TEST');
                                        
// comm이 null이 아닌값을 갖는 데이터 삽입(사원번호 수정)
INSERT INTO emp1(empno, ename) (SELECT empno-5000, ename
                                FROM emp
                                WHERE comm IS NOT NULL);
                                
//emp1테이블에서 이름이 test인 사원의 업무를 7369번 사원의 업무로 변경
UPDATE emp1
SET job = (SELECT job
            FROM emp
            WHERE empno=7369)
WHERE ename='TEST';

// SALES부서에서 일하거나, 부서위치가 DALLAS인 사원 삭제
DELETE FROM emp1
WHERE deptno IN (SELECT deptno
                    FROM dept
                    WHERE dname='SALES' OR loc='DALLAS');

# DUAL 테이블

- SYS사용자가 소유하는 테이블로 모든 사용자가 질의가능

- dummy라는 하나의 컬럼에 X라는 하나의 데이터만을 가짐

- 일시적 산술연산, 가상 컬럼 등의 값을 확인할때 사용

# 내장함수

문자 처리 함수	LOWER / UPPER	모든 문자를 소문자 / 대문자 변환
	INITCAP	문자열 내 단어별 첫글자를 대문자로 변환(나머지는 소문자)
	LTRIM / RTRIM	왼쪽 / 오른쪽 빈칸 삭제
	SUBSTR	문자열 잘라내기
	LENGTH	문자열 길이 계산
숫자 처리 함수	CEIL	소수점 올림
	MOD	나머지 값
	POWER	승수
	ROUND	반올림
	TRUNC	버림
날짜 처리 함수	SYSDATE	현재 날짜와 시간
	LAST_DAY	해당 월의 마지막 날짜
	MONTHS_BETWEEN	두 기간 사이의 개월 수
변환 함수	TO_CHAR	문자로 치환
	TO_NUMBER	숫자로 치환
	TO_DATE	날짜로 치환
그 외 함수	NVL, COALESCE	NULL처리 함수
	DECODE	연속 조건문
	GREATEST / LEAST	최대값 / 최소값

# 내장함수 - 문자 처리 함수

-- 기존 테이블
SELECT empno, ename
FROM emp
WHERE empno<7600;
-- 대문자 변환 테이블
SELECT empno, UPPER(ename)
FROM emp
WHERE empno<7600;
-- 소문자 변환 테이블
SELECT empno, LOWER(ename)
FROM emp
WHERE empno<7600;
-- 단어별 첫글자만 대문자로 변환한 테이블
SELECT empno, INITCAP(ename)
FROM emp
WHERE empno<7600;

--SUBSTR(CHAR, N, M): N부터 (N+M-1)까지의 글자
--SUBSTR(CHAR, N): N부터 끝까지의 글자
SELECT SUBSTR('Database Programing', 1, 3) as s1,
        SUBSTR('Database Programing', 4, 5) as s2,
        SUBSTR('Database Programing', 10) as s3
FROM DUAL;

--LTRIM(char): char의 맨 왼쪽의 공백 제거
--RTRIM(char): char의 맨 오른쪽의 공백 제거
--TRIM(char): char의 양쪽 끝의 공백 제거

# 내장함수 - 숫자 처리 함수

--CEIL(int): int의 소수이하값을 올림
--ROUND(int, n): int의 소수점 n번째 자리에서 반올림
SELECT CEIL(13.11),
        CEIL(13.6),
        ROUND(5.875, 1)
FROM DUAL;

SELECT MOD(23, 5),
        POWER(3, 2)
FROM DUAL;

# 내장함수 - 날짜 처리 함수

-- 현재날짜 반환
SELECT SYSDATE
FROM DUAL;
-- 현재날짜의 월의 마지막 날짜
SELECT LAST_DAY(SYSDATE)
FROM DUAL;

# TO_CHAR

# TO_NUMBER

# TO_DATE

# NVL

# COALESCE

# CASE문

--월급에 따른 월급수준을 GRAD속성의 데이터로 추가
SELECT ename, sal,
        CASE WHEN sal>=3000 THEN 'HIGH'
             WHEN sal>=1000 THEN 'MID'
             ELSE 'LOW'
        END AS GRAD
FROM EMP;

# 문자열 연결 연산자

SELECT ename || job
FROM emp