SQL 유형별 종류
- DDL(Data Definition Language)
1. 테이블 이름과 칼럼은 A~Z까지의 문자, 0~9까지의 숫자, 그리고 $,#,_(Under Bar)를 사용할 수 있다. 그러나 공백은 사용할 수 없다.
2. 테이블의 칼럼은 30자를 초과할 수 없고, 예약어를 사용할 수 없다.
3. 한 테이블 안에서 칼럼 이름은 같을 수 없으며 다른 테이블에서의 칼럼 이름과는 같을 수 있다.
■ CREATE
CREATE TABLE 테이블이름
( {열이름 데이터타입 [NOT NULL] [DEFALUT 값], }+
[PRIMARY KEY (열이름_리스트), ]
{[UNIQUE (열이름_리스트),]}*
{[FOREIGN KEY(열이름_리스트)
REFERENCES 기본테이블[(열이름_리스트)]
[ON DELETE 옵션]
[ON UPDATE 옵션],]}*
[CONSTRAINT 이름][CHECK(조건식)]);NOT NULL: NULL을 허용하지 않음
UNIQUE: 중복된 값을 허용하지 않고, 항상 유일한 값을 갖도록 함
PRIMARY KEY : NULL을 허용하지 않음. 중복된 값을 허용하지 않음, NOT NULL 조건과 UNIQUE 조건을 결합한 형태
UNIQUE: 조건을 결합한 형태
FOREIGN KEY: 참조되는 테이블의 칼럼의 값이 존재하면 허용
CHECK: 저장 가능한 데이터 값의 범위나 조건을 지정해 설정한 값만을 허용
■ ALTER
ALTER TABLE 테이블명
([ADD 칼럼명 데이터_타입] [DEFAULT 값] |
[DROP 칼럼명] [CASCADE] |
[ALTER 칼럼명 (DROP DEFAULT | SET DEFAULT 값)]);
■ DROP
DROP TABLE 테이블명
- DML(Data Manipulation Language)
■ SELECT
SELECT [DISTINCT] {*, 열_리스트}
FROM {테이블_리스트}
[WHERE 조건]
[ORDER BY 칼럼명 ASC | DESC]
[GROUP BY 칼럼명
HAVING 조건]
- 집계 함수 (출처: http://www.gurubee.net/)
COUNT
: COUNT 함수는 검색된 행의 수를 반환 한다.
1 2 3 4 5 6 | SELECT COUNT(deptno) FROM dept;
COUNT(DEPTNO)
4
|
MAX
: MAX 함수는 컬럼값 중에서 최대값을 반환 한다.
1 2 3 4 5 6 | SELECT MAX(sal) salary FROM emp;
SALARY
5000
|
MIN
: MIN 함수는 컬럼값 중에서 최소값을 반환 한다.
1 2 3 4 5 6 | SELECT MIN(sal) salary FROM emp;
SALARY
800
|
AVG
: AVG 함수는 평균 값을 반환 한다.
1 2 3 4 5 6 7 8 | SELECT ROUND(AVG(sal),1) salary
FROM emp
WHERE deptno = 30;
SALARY
1566.7
|
SUM
: SUM 함수는 검색된 컬럼의 합을 반환 한다.
1 2 3 4 5 6 7 8 | SELECT SUM(sal) salary
FROM emp
WHERE deptno = 30;
SALARY
9400
|
STDDEV
: STDDEV 함수는 표준편차를 반환 한다.
1 2 3 4 5 6 7 8 | SELECT ROUND(STDDEV(sal),3) salary
FROM emp
WHERE deptno = 30;
SALARY
668.331
|
집계함수 예
: 아래는 부서별 사원수, 최대급여, 최소급여, 급여합계, 평균급여를 급여합계 순으로 조회하는 예이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT deptno 부서번호, COUNT(*) 사원수,
MAX(sal) 최대급여, MIN(sal) 최소급여,
SUM(sal) 급여합계, ROUND(AVG(sal)) 평균급여
FROM emp
GROUP BY deptno
ORDER BY SUM(sal) DESC;
부서번호 사원수 최대급여 최소급여 급여합계 평균급여
20 5 3000 800 10875 2175
30 6 2850 950 9400 1567
10 3 5000 1300 8750 2917
|
GROUP BY
- : GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
- : 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
- : 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
- : 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
FROM emp
GROUP BY deptno
ORDER BY COUNT(*) DESC;
YEAR 부서번호 사원수
2005년 30 6
2005년 20 5
2005년 10 3
|
아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.
1 2 3 4 5 6 7 8 9 10 11 | SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
ROUND(SUM(sal)) "급여합계"
FROM emp
GROUP BY deptno;
DEPTNO COUNT(*) 급여평균 급여합계
30 6 1567 9400
20 5 2175 10875
10 3 2917 8750
|
아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
MAX(sal) "최고급여액", MIN(sal) "최저급여액",
SUM(sal) "급여합계"
FROM emp
GROUP BY job;
JOB 인원수 평균급여액 최고급여액 최저급여액 급여합계
CLERK 4 1037.5 1300 800 4150
SALESMAN 4 1400 1600 1250 5600
PRESIDENT 1 5000 5000 5000 5000
MANAGER 3 2758.33333 2975 2450 8275
ANALYST 2 3000 3000 3000 6000
|
- : GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
- : 아래 예제를 보면 GROUP BY는 말 그대로 그룹을 나누는 역할을 한다.
1 2 3 4 5 6 7 8 9 10 11 | SELECT deptno
FROM emp
GROUP BY deptno;
DEPTNO
30
20
10
|
DISTINCT와 GROUP BY절
- : DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
- : DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
- : GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
- : 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
- : 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
- : 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.
아래의 예제는 동일한 결과를 반환한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT DISTINCT deptno FROM emp;
SELECT deptno FROM emp GROUP BY deptno;
DEPTNO
30
20
10
|
하지만 곰곰히 생각해 보면 GROUP BY와 DISTINCT는 각자 고유의 기능이 있다
집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용 하도록 하자
1 2 3 4 5 6 7 8 9 10 11 | SELECT COUNT(DISTINCT d.deptno) "중복제거 수",
COUNT(d.deptno) "전체 수"
FROM emp e, dept d
WHERE e.deptno = d.deptno;
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno;
|
HAVING 절
- : WHERE 절에서는 집계함수를 사용 할 수 없다.
- : HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
- HAVING 절은 GROUP BY절과 함께 사용이 된다.
아래 예제는 사원수가 다섯 명이 넘는 부서와 사원수를 조회하는 예제이다.
1 2 3 4 5 6 7 8 9 10 | SELECT b.dname, COUNT(a.empno) "사원수"
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY dname
HAVING COUNT(a.empno) > 5;
DNAME 사원수
SALES 6
|
아래 예제는 전체 월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회하는 예이다. 단 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬하였다.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT job, SUM(sal) "급여합계"
FROM emp
WHERE job != 'SALES'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal) DESC;
JOB 급여합계
MANAGER 8275
ANALYST 6000
SALESMAN 5600
|
- 테이블에 별칭을 부여한 조인 연산
SELECT s.STU_NAME, c.COU_NAME, c.COU_ID
FROM STUDENT s, COURES c
WHERE s.COU_ID = c.COU_ID;
■ INSERTINSERT INTO 테이블이름 { 칼럼명 } -- 생략 할 경우, CREATE했던 순서대로 삽입 됨
VALUES({ 칼럼값 })
■ UPDATE
UPDATE 테이블이름
SET { SET 칼럼명 = 칼럼값 }
WHERE 조건
■ DELETE
DELETE FROM 테이블이름
[WHERE 조건] -- 생략 할 경우, 모든 행이 삭제
