-
데이터베이스 프로그래밍 정리Univ/데이터베이스_프로그래밍 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