📒

[Database Study] 01-1. SQL - DDL, DML, DCL

상태

수정중

수업

Database Study

주제

SQL

4 more properties

참고

DML (Select, Insert, Update, Delete)

DML - INSERT(추가)

DML - UPDATE(수정)

DML - DELETE(삭제)

RDBMS

NOTE

docker run --name my-postgres -e POSTGRES_PASSWORD=961596 -p 5432:5432 -d postgres

•

관계형(Relational) 데이터베이스 시스템

•

테이블 기반 DBMS (포트 : 3306)

◦

데이터를 테이블 단위로 관리 (하나의 테이블은 여러 개의 컬럼으로 구성)

◦

중복 데이터를 최소화 시킴

▪

같은 데이터가 여러 컬럼 또는 테이블에 존재 했을 경우

▪

데이터를 수정 시 문제가 발생할 가능성이 높아짐 - 정규화

◦

여러 테이블에 분산되어 있는 데이터를 검색 시 테이블 간의 관계(join)을 이용하여필요한 데이터를 검색

SQL

NOTE

•

Database에 있는 정보를 사용할 수 있도록 지원하는 언어

•

모든 DBMS(ANSI)에서 사용 가능 

•

대소문자는 구별하지 않음 ( 단 데이터는 대소문자 구분)

Data Type

NOTE

•

문자형 데이터 타입

VARCHAR은 10의 크기를 주고 5를 준다면 5만 할당

CHAR은 10의 크기를 주고 5를 준다면 10을 모두 할당함

•

숫자형 데이터 타입

DECIMAL의 경우 (5,2) → 전체자리수 5, 소수점 자리 2 (전체자리가 소수점도 포함)

•

날짜형 데이터 타입

DDL - 테이블 생성

NOTE

•

제약조건

◦

NOT NULL : 컬럼에 NULL값 저장 불가

◦

UNIQUE : 컬럼에 중복된 값 허용불가 ( NULL은 허용함 )

◦

PRIMARY KEY : 컬럼에 중복된 값 저장 불가, NULL도 허용하지 않음, ROW를 구분하기 위한 유일한 값을 저장할때 사용함

◦

FOREIGN KEY : 특정 테이블의 PK 컬럼에 저장되어 있는 값만 저장함 (외래키, 참조키라고도 부름, NULL허용

◦

DEFAULT : NULL이 들어올 경우 기본 설정되는 값을 지정함

◦

CHECK  : 값의 범위나 종류를 지정

테이블 생성 코드

테이블 수정 코드

DML (Select, Insert, Update, Delete)

NOTE

-- 모든 사원의 모든 정보 검색.
SELECT DISTINCT *
FROM employees;

-- 우리 회사의 모든 부서번호
select department_id
from departments;


-- 사원이 근무하는 부서의 부서번호 검색.(중복제거)
select distinct department_id
from employees;



-- 회사에 존재하는 모든 부서.
select department_id
from departments;


-- 모든 사원의 사번, 이름, 급여 검색.
select employee_id, first_name, salary
from employees;


-- 모든 사원의 사번, 이름, 급여, 급여 * 12 (연봉) 검색.
select employee_id as 사번, first_name "이 름", salary as 급여, salary*12 연봉
from employees;


-- 모든 사원의 사번, 이름, 급여, 급여 * 12 (연봉), 커미션, 커미션포함 연봉 검색.
-- 사칙연산도 가능함
select employee_id as 사번, first_name "이 름", salary as 급여, salary*12 연봉,
commission_pct "커미션", (salary + salary * ifnull(commission_pct, 0)) * 12 "커미션포함 연봉"
from employees;
SQL
복사

•

ifnull : null 값인경우 기본값을 넣어줌 

-- 모든 사원의 사번, 이름, 급여, 급여에 따른 등급표시 검색.
-- 급여에 따른 등급
--   15000 이상 “고액연봉“      
--   8000 이상 “평균연봉”      
--   8000 미만 “저액연봉＂
select employee_id, first_name, salary,
	case 	when salary >= 15000 
			then "고액연봉"
			when salary >= 8000 
            then "평군연봉"
			else "저액연봉"
	end 연봉등급
from employees;


-- 부서번호가 50인 사원중 급여가 7000이상인 사원의
-- 사번, 이름, 급여, 부서번호
select employee_id, first_name, salary, department_id
from employees
where department_id = 50
and salary >= 7000;


-- 근무 부서번호가 50, 60, 70에 근무하는 사원의 사번, 이름, 부서번호
select employee_id, first_name, salary, department_id
from employees
where department_id = 50
or department_id = 60
or department_id = 70;



-- 근무 부서번호가 50, 60, 70이 아닌 사원의 사번, 이름, 부서번호
select employee_id, first_name, salary, department_id
from employees
where department_id != 50
and department_id != 60
and department_id != 70;

select employee_id, first_name, salary, department_id
from employees
where not(department_id = 50
or department_id = 60
or department_id = 70);

select employee_id, first_name, salary, department_id
from employees
where not(department_id in (50, 60, 70));


-- 급여가 6000이상 10000이하인 사원의 사번, 이름, 급여
SELECT employee_id, first_name, salary
from employees
where salary >= 6000
and salary <= 10000;

SELECT employee_id, first_name, salary
from employees
where salary between 6000 and 10000;


-- 근무 부서가 지정되지 않은(알 수 없는) 사원의 사번, 이름, 부서번호 검색.
-- null 자체는 비교 불가능이므로 is로 본다
select employee_id, first_name, department_id, salary
from employees
where department_id is null;


-- 부서가 있는 사람
select employee_id, first_name, department_id, salary
from employees
where department_id is not null;


-- 커미션을 받는 사원의 사번, 이름, 급여, 커미션
SELECT employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null;
SQL
복사

•

NULL은 값이 없으므로 비교가 불가능해서 IS로 판별한다

-- 이름에 'x'가 들어간 사원의 사번, 이름 ( %, _ )
-- % : 그 자리에 몇개가 들어가도 상관없음(0 ~ 무한)
-- _ : 1칸만 차지함
-- ""쓰지말고 ''로 사용해라 (오라클에서 에러남)
-- '' -> data, "" -> alias
select employee_id, first_name
from employees
where first_name like '%x%';

-- 이름의 끝에서 3번째 자리에 'x'가 들어간 사원의 사번, 이름
select employee_id, first_name
from employees
where first_name like '%x__';
SQL
복사

•

% : 해당 자리에 어떠한 글자수가 와도 상관없음

•

_ : 해당 1칸에 어떠한 글자가 와도 상관없음

쿼리 결과 정렬

--
SELECT *
FROM emp;

-- = < > <= >= ! <> 일반 연산자 지원
SELECT *
FROM emp
WHERE deptno = 10;

SELECT *
FROM emp
WHERE deptno = 10
    or comm is not null
    or sal <= 2000 and deptno=20;

SELECT *
FROM emp
WHERE (deptno = 10
    or comm is not null
    or sal <= 2000 and deptno=20)
    and deptno=20;

-- 별칭은 where에서 쓰지 못한다. (WHERE이 SELECT보다 먼저 시행되므로)
-- FROM은 WHERE보다 먼저 평가되므로 FROM 서브쿼리로 작성하면 사용가능하다.
-- SELECT sal as salary, comm as commission
-- FROM emp
-- WHERE salary < 50000;

SELECT *
FROM ( SELECT sal as salary, comm as commission
       FROM emp
       ) X
WHERE salary < 5000;

-- 내장함수로 여러 열 값 연결( ||, mysql은 concat 지원)
SELECT ename||' WORKS AS A'||job as msg
FROM emp
WHERE deptno=10;

SELECT ename, sal,
       case WHEN sal <= 2000 then  'UNDERPAID'
            WHEN sal >= 4000 then 'OVERAPID'
            else 'OK'
        end as status
FROM emp;

SELECT *
FROM emp
LIMIT 5;

-- 무작위 행 반환
SELECT ename, job
FROM emp
ORDER BY random()
LIMIT 5;

-- COALESCE 함수 (null 값 대체)
SELECT COALESCE(comm, 0)
FROM emp;

-- 패턴 검색
SELECT ename, job
FROM emp
WHERE deptno in (10, 20)
  AND (ename like '%I%' or job like '%ER');
SQL
복사

SELECT ename, job, sal
FROM emp
WHERE deptno = 10
ORDER BY sal ASC;

-- ORDER BY의 우선순위는 왼쪽에서 오른쪽
-- GROUP BY, DISTINCT 사용시 SELECT 목록에 없는 열 기준으로 정렬불가
SELECT empno, deptno, sal, ename, job
FROM emp
ORDER BY deptno, sal DESC;

-- 컬럼의 부분 문자열 정렬
SELECT ename, job
FROM emp
ORDER BY substr(job, length(job)-1);

-- null 값 정렬(CASE를 사용해 플래그 수행)
-- 추가 열의 목적은 (테이블이 아닌 쿼리에서만) null 값을 식별하여 처음/마지막 으로 정렬
SELECT ename, sal, comm
FROM (
        SELECT ename, sal, comm, CASE
                                 WHEN comm is null
                                 THEN 0
                                 ELSE 1
                                 END as is_null
        FROM emp
     ) X
ORDER BY is_null DESC, comm;


-- 데이터 종속 키 기준으로 정렬
-- job의 값에 따라 정렬기준 변경
SELECT ename, sal, job, comm
FROM emp
ORDER BY CASE WHEN job = 'SALESMAN'
              THEN comm
              ELSE sal
              END;
SQL
복사

-- UNION ALL
SELECT ename, deptno
FROM emp
WHERE deptno = 10

UNION ALL
SELECT '------', null
FROM t1

UNION ALL
SELECT dname, deptno
FROM dept;

-- 동등 조인, 조인의 결과셋은 항상 데카르트 곱을 우선생성

SELECT e.ename, d.loc
FROM EMP e,
     DEPT d
WHERE e.deptno = d.deptno
  AND E.deptno = 10;

SELECT e.ename
FROM emp e
         INNER JOIN DEPT d
                    ON e.deptno = d.deptno
WHERE e.deptno = 10;

CREATE VIEW V
AS
SELECT ename, job, sal
FROM emp
WHERE job = 'CLERK';

SELECT *
FROM V;


SELECT d.*
FROM dept d
         LEFT JOIN emp e
                   ON d.deptno = e.deptno
WHERE e.deptno is null

-- 외부 조인은 한 테이블의 모든 행과 다른 테이블의 일치하는 행 반환

-- 데카르트 곱 식별 및 방지
SELECT e.ename, d.loc
FROM EMP e,
     DEPT d
WHERE e.deptno = 10
  AND d.deptno = e.deptno;

SELECT e.empno,
       e.ename,
       e.sal,
       e.deptno,
       e.sal * CASE
                   WHEN eb.type = 1 then .1
                   WHEN eb.type = 2 then .2
                   ELSE .3
           END as bonus
FROM emp e,
     emp_bonus eb
WHERE e.empno = eb.empno
  AND e.deptno = 10;

-- 중복된 결과 방지
SELECT deptno,
       SUM(distinct sal) as total_sal,
       sum(bonus)        as total_bonus
FROM (SELECT e.empno,
             e.ename,
             e.sal,
             e.deptno,
             e.sal * CASE
                         WHEN eb.type = 1 then .1
                         WHEN eb.type = 2 then .2
                         ELSE .3
                 END as bonus
      FROM emp e,
           emp_bonus eb
      WHERE e.empno = eb.empno
        AND e.deptno = 10) x
GROUP BY deptno;

-- 중복값이 있는 경우에는, 사원의 연봉총합을 따로 구한다.
SELECT d.deptno,
       d.total_sal,
       sum(e.sal * CASE
                       WHEN eb.type = 1 then .1
                       WHEN eb.type = 2 then .2
                       ELSE .3
           END)as total_bonus
FROM emp e,
     emp_bonus eb,
     (SELECT deptno, sum(sal) as total_sal
      FROM EMP
      WHERE deptno = 10
      GROUP BY deptno) d
WHERE e.deptno = d.deptno
  AND e.empno = eb.empno
GROUP BY d.deptno, d.total_sal;

SELECT deptno,
       sum(distinct sal) as total_sal,
       sum(bonus) as total_bonus
FROM(
    SELECT e.empno,
           e.ename,
           e.sal,
           e.deptno,
           e.sal * CASE
                       WHEN eb.type = 1 then .1
                       WHEN eb.type = 2 then .2
                       ELSE .3
           END as bonus
    FROM emp e
    LEFT OUTER JOIN emp_bonus eb
                 ON e.empno = eb.empno
    WHERE e.deptno = 10
    ) a
group by deptno;

SELECT deptno,
       SUM(sal) as total_sal,
       sum(bonus)        as total_bonus
FROM (SELECT e.empno,
             e.ename,
             e.sal,
             e.deptno,
             e.sal * CASE
                         WHEN eb.type = 1 then .1
                         WHEN eb.type = 2 then .2
                         ELSE .3
                 END as bonus
      FROM emp e
      LEFT JOIN emp_bonus eb
             ON e.empno = eb.empno
      WHERE e.deptno = 10) x
GROUP BY deptno;
Java
복사

SQL
복사

DML - INSERT(추가)

NOTE

INSERT INTO ssafy_member (idx, userid,  username, userpwd, emailid, emaildomain, joindate)
values('1', 'kimssafy', '김싸피', '1234', 'kimssafy', 'ssafy.com', now());

-- auto-increament라서 idx값없어도 자동으로 증가한값 넣어줌
INSERT INTO ssafy_member (userid,  username, userpwd, emailid, emaildomain, joindate)
values('kimssafy2', '김싸피2', '1234', 'kimssafy', 'ssafy.com', now());


INSERT INTO ssafy_member (userid,  username, userpwd)
values('kimssafy', '박싸피', '1234');

-- 여러개의 데이터를 한번에 넣는것도 가능 (단 하나의 테이블에서만)
INSERT INTO ssafy_member (userid,  username, userpwd)
values('leessafy', '이싸피', '1234'), ('choissafy', '최싸피', '1234');
SQL
복사

DML - UPDATE(수정)

NOTE

주의! ( 안전모드 체크된 상태로 Update를 하면 1175에러가 발생하므로 해제하고 진행)

update ssafy_member 
set userpwd = 4321, emaildomain = 'ssafy_update.com'
where userid='choissafy'
SQL
복사

DML - DELETE(삭제)

NOTE

-- userid가 kimssafy 회원 탈퇴
delete from ssafy_member
where userid='kimssafy';

#9.	age가 1000 이상인 사용자를 삭제하시오.
delete from `user` where age >= 1000;

#10.	user 테이블에 있는 모든 data를 삭제하시오.
delete from `user`;
SQL
복사