- DDL(Data Defination Language) : Create, Drop, Alter
- DCL(Data Control Language) : grant, deny
DML vs DDL
- Transaction의 사용 가능 유무
- DML Transaction 영향 O
- DDL Transaction 영향 X
Transaction(트랜잭션)
- 작업의 최소 단위
- 임의로 설정할 수 있는 개념이다.
- 여러 SQL 문장을 하나의 논리적인 단위인 Transaction으로 설정한다.
- 사용 이유 : Transaction 설정시 DBMS가 ACID 보장
- commit(반영) or rollback(무효화)
MySQL Workbench Auto Commit Transactions
- 워크벤치 기능에 쿼리를 할 때마다 자동적으로 트랜잭션해주는 모드가 켜져 있다.
ACID
(1) Atomicity(원자성)
- All or nothing
- 모든 작업이 완료되거나 실행이 아무것도 되지 않아야 한다.
(2) Consistency(일관성) - 트랜잭션이 끝난 후, 결과가 제약조건에 위배 되지 않는 상태(correct state)이 된다.
(3) Isolation(독립성)
- 모든 트랜잭션이 다른 트랜잭션에 독립적이다.
- Thread 동기화 처리와 일치한다.
(4) Durability(지속성)
- 트랜잭션 종료 시, 해당 결과가 2차 저장소에 영구적으로 저장된다.
Ex. 이체 시스템
- A 계좌 SELECT (잔고 확인)
- A 계좌 UPDATE (잔고 수정)
- B 계좌 SELECT (입금 전 금액)
- B 계좌 UPDATE (입금 후 금액)
JOIN
(1) INNER JOIN
- 일반적인 JOIN
(2) OUTER JOIN
- LEFT, RIGHT, FULL
- JOIN 조건을 만족하지 않는 행을 포함한다.
(3) SELFT JOIN
(4) CROSS JOIN
- 일반적으로 많이 사용하지 않는다.
- 더미데이터 생성에 사용
실습 SQL문 정리
-- 평균 구하기 --
SELECT AVG(칼럼명) FROM 테이블명;
-- 사용자별 평균 구매 금액 --
SELECT userID, AVG(칼럼명) FROM 테이블명
GROUP BY userID;
-- 가장 큰 키, 작은 키 회원의 이름과 키 출력 --
SELECT 이름, 키 FROM 테이블명
WHERE 키 = (
SELECT MAX(키 칼럼) FROM 테이블명
)
OR 키 =
(
SELECT MIN(키 칼럼) FROM 테이블명
);
-- 사용자별 총 구매 금액 1000 이상 --
SELECT userID, SUM(구매금액*구매횟수)
FROM 테이블명
GROUP BY userID
HAVING SUM(구매금액*횟수) >= 1000;
-- 트랜잭션 --
SELECT distinct userID FROM 테이블명;
START TRANSACTION -- 트랜젝션의 시작 구문 --
DELETE FROM 테이블명; -- 테이블 삭제 --
DELETE FROM 테이블명 WHERE 칼럼명 = '조건' LIMIT 100; -- 상위 100개만 지우기 --
COMMIT; -- 지금 작업한 내용 데이터베이스에 적용 --
ROLLBACK; -- 무효화 --
연습문제 SQL문 정리
-- FLOOR(AVG(SAL)) : 정수만 출력, 실수 값 버리기 --
SELECT JOB as '직무', FLOOR(AVG(SAL)) as '급여 평균' FROM EMP
WHERE DEPTNO=30
GROUP BY JOB;
-- 칼럼들의 누적 합계 구하기 --
-- FORMAT(금액, '0.000') : 금액 천단위마다 콤마 출력 --
SELECT JOB as '직무명', FORMAT(SUM(SAL), '0,000') as '급여의 합' FROM EMP
GROUP BY JOB
UNION ALL
SELECT 'TOTAL', FORMAT(SUM(SAL), '0,000') FROM EMP
ORDER BY '직무명';
-- CROSS JOIN : 2개 테이블 열 기준으로 붙이기 --
SELECT * FROM A CROSS JOIN B;
-- 1. IFNULL(칼럼명, 0) : NULL 처리 (NULL + 0이상의 값 = NULL이 출력된다) --
-- 2. FORMAT()으로 금액 천단위마다 콤마 + CONCAT()으로 금액 + '원' 출력
SELECT ENAME as '직원명', CONCAT(FORMAT(SAL+IFNULL(COMM, 0), '0,000'),'원') as '급여' FROM EMP
ORDER BY SAL+IFNULL(COMM, 0) DESC;
-- DATE_FROMAT() : DATE 타입 출력 형식 --
-- CASE WHEN ~ THEN : 조건별 표현식 --
SELECT ENAME as '직원명', DATE_FORMAT(HIREDATE, '%Y년 %m월 %d일') as '입사년월일',
(CASE
WHEN HIREDATE BETWEEN '1980-01-01' AND '1980-12-31' THEN 'A'
WHEN HIREDATE BETWEEN '1981-01-01' AND '1981-12-31' THEN 'B'
WHEN HIREDATE BETWEEN '1982-01-01' AND '1982-12-31' THEN 'C'
WHEN HIREDATE BETWEEN '1983-01-01' AND '1983-12-31' THEN 'D'
END) as '등급'
FROM EMP;
-- 3중 조인 --
SELECT * FROM A
INNER JOIN B
ON A.칼럼1 = B.칼럼1
INNER JOIN C
ON B.칼럼2 = C.칼럼2;
