작성 순서

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

실제 실행 순서

FROM WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT/OFFSET

CASE WHEN 조건1 THEN 결과1 ... END

SELECT empno, ename 
CASE WHEN deptno=10 THEN 'ACCOUNTING' 
WHEN deptno=20 THEN 'RESEARCJ'
END AS dname
FROM emp;

// ELSE 사용
SELECT studno, total 
CASE WHEN 90<=total THEN 'A'
CASE WHEN 80<=total THEN 'B'
ELSE 'C'
END AS grade
FROM exam_01;

SUBSTR(문자열, 시작위치, 길이)

: 원하는 위치부터 일정 길이만큼 잘라냄

SELECT name, jumin
CASE WHEN SUBSTR(jumin,7,1)=1 THEN 'MAN'
WHEN SUBSTR(jumin,7,1)=3 THEN 'MAN'
WHEN SUBSTR(jumin, 7,1)=2 THEN 'WOMAN'
WHEN SUBSTR(jumin, 7,1)=4 THEN 'WOMAN'
END AS gender
FROM student;

// SUBSTR 한 번만 사용
SELECT name, jumin
CASE SUBSTR(jumin,7,1) when 1 THEN 'MAN'
WHEN 3 THEN 'MAN'
WHEN 2 THEN 'WOMAN'
WHEN 4 THEN 'WOMAN'
END AS gender
FROM student;

INSTR(문자열, 찾을문자)

: 특정 문자의 위치 찾음

SELECT name, tel
CASE SUBSTR(tel, 0, INSTR(tel, ')'-1) 
WHEN '02' THEN '서울'
WHEN '031' THEN '경기'
END AS local
FROM student;

MONTH(), BETWEEN ... AND ...

SELECT name, MONTH(birthday) 달,
CASE WHEN MONTH(birthday) BETWEEN 1 AND 3 THEN '1/4분기'
WHEN MONTH(birthday) BETWEEN 4 AND 6 THEN '2/4분기'
END AS MONTH
FROM student;

EXTRACT(단위 FROM 날짜)

단위 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER 등
: 날짜/시간 데이터에서 특정 부분을 추출

SELECT name, MONTH(birthday) 달,
CASE EXTRACT(QUARTER FROM birthday)
WHEN 1 THEN '1/4분기'
WHEN 2 THEN '2/4분기'
END 분기
FROM student;

CONCAT(str1, str2)

: 문자열을 이어붙임

SELECT name, MONTH(birthday) 달,
CASE CONCAT(EXTRACT(QUARTER FROM birthday), '/4분기') 분기
FROM student;

CURDATE()

: 현재 날짜(Current Date)를 반환하는 함수

SELECT

FORMAT(값, 소수점자릿수)

DATEDIFF(날짜1, 날짜2)

두 날짜의 차이(일단위) 계산, 정수

ORDER BY col [ASC/DESC]

DEFAULT : ASC 오름차순

• NULL 처리
  • 오름차순 시 먼저
  • 내림차순 시 마지막

제약조건

• not null : NULL 값이 들어올 수 없도록 강제
• unique : 컬럼 값이 중복되지 않도록 (NULL 허용)
• primary key : NOT NULL + UNIQUE, 각 행을 유일하게 식별, 1개
• foreign key : 다른 테이블의 PK(혹은 UNIQUE) 참조,
  • 참조 무결성 보장 : 존재하지 않는 값 참조 불가,
    참조하는 데이터 변경/삭제 불가,
    REFERENCES 키워드 사용
• check : 컬럼 값이 특정 조건을 만족해야만 허용
  • check(col IN(v1,v2,v3..))

CREATE TABLE

CREATE TABLE tmp (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age INT DEFAULT 1 CHECK(age>0)
);

INSERT INTO 테이블명 (col1, ...) VALUES (v1, ...);

INSERT INTO tmp (name, age) VALUES ('hong', -1); // 에러

AUTO_INCREMENT

: 숫자 컬럼에 자동으로 1씩 증가하는 값을 넣어줌

REFERECES

: 외래키에서 사용, 다른 테이블의 특정 컬럼을 참조

CREATE TABLE article(
num INT AUTO_INCREMENT PRIMARY KEY,
writer VARCHAR(20) REFERENCES USER(id)

UPDATE 테이블명 SET col1=v1, ... WHERE 조건;

UPDATE USER SET id='gong' WHERE 'id=hong'; // 참조무결성위반
UPDATE USER SET name='길동이' WHERE id='hong'; // 다른 컬럼 가능

DELETE FROM 테이블명 WHERE 조건;

: 조건이 없으면 모두 삭제 (구조는 남음)

DELETE FROM article WHERE writer='hong';

DROP TABLE 테이블명;

: 테이블 자체를 완전히 삭제

ALTER TABLE 테이블명

1. ALTER TABLE 테이블명 ADD COLUMN (col 타입));

컬럼 추가

2. ALTER TABLE 테이블명 ADD PRIMARY KEY(col);

제약조건 추가

3. ALTER TABLE 테이블명 ADD CONSTRAINT ARTICLE_PK PRIMARY KEY(col);

제약조건 이름 지정 추가
[ CONSTRAINT 제약조건명 ] 붙여야 함

4. ALTER TABLE 테이블명 ADD CONSTRAINT ARTICLE_FK FOREIGN KEY(col) REFERENCES 테이블명(col);

외래키 제약조건 이름 지정 추가

5. ALTER TABLE 테이블명 DROP COLUMN col;

컬럼 삭제

6. ALTER TABLE 테이블명 MODIFY COLUMN col 타입 NOT NULL;

NULL이나 NOT NULL은 컬럼 속성으로,
MODIFY COLUMN 데이터타입 NOT NULL 으로 수정

7. ALTER TABLE 테이블명 RENAME COLUMN col1 TO col2;

col1에서 col2로 이름 변경

// 변경해보기
CREATE TABLE tcons(
NO INT, -- primary key
NAME VARCHAR(20), -- not null
jumin VARCHAR(13),-- not null, unique
AREA INT, -- check 1,2,3,4
deptno VARCHAR(6) -- foreign key
);

// 변경
ALTER TABLE tcons ADD PRIMARY KEY (NO);
ALTER TABLE tcons MODIFY COLUMN name VARCHAR(20) NOT NULL;
ALTER TABLE tcons MODIFY COLUMN jumin VARCHAR(13) NOT NULL;
ALTER TABLE tcons ADD CONSTRAINT jumin_uk UNIQUE (jumin);
ALTER TABLE tcons ADD CONSTRAINT area_uk CHECK(AREA IN(1,2,3,4));
ALTER TABLE tcons ADD CONSTRAINT deptno_uk FOREIGN KEY(deptno) REFERENCES dept3(dcode);
//삭제
ALTER TABLE tcons DROP PRIMARY KEY;
ALTER TABLE tcons MODIFY COLUMN name VARCHAR(20);
ALTER TABLE tcons DROP CONSTRAINT jumin_uk;
ALTER TABLE tcons DROP CONSTRAINT area_uk;
ALTER TABLE tcons DROP CONSTRAINT deptno_fk;

DDL

1. CREATE

CREATE DATABASE mydb;

CREATE TABLE 복사테이블 AS SELECT col FROM 기존테이블 WHERE 조건;

복사

CREATE TABLE emp_cp AS SELECT empno FROM emp where empno=10;

SELECT col,... FROM table1 JOIN table2 USING(공통col);

CREATE TABLE emp_dept AS SELECT emp.*, d.dname FROM emp JOIN dept USING(deptno);

양쪽 테이블에 동일한 컬럼명이 있을 때, 그 기준으로 조인

CREATE TABLE emp_cp AS SELECT * FROM emp WHERE 1=2;

항상 거짓의 조건으로 데이터의 구조만 복사

2. ALTER

ADD 추가, MODIFY 변경, RENAME 이름변경

ALTER TABLE persons MODIFY COLUMN address VARCHAR(300);

변경

ALTER TABLE persons RENAME COLUMN city TO area;

이름변경

3. TRUNCATE TABLE 테이블;

TRUNCATE TABLE emp_dept;

테이블 비우기
자동 COMMMIT, ROLLBACK 불가능
WHERE 불가능

4. DROP TABLE 테이블;

테이블 자체를 완전히 삭제

5. RENAME COLUMN col1 TO col2

CRUD (DML)

1. INSERT INTO 테이블(col1, ...) VALUES (v1, ...);

INSERT INTO user (id, name) VALUES ('hong' '홍길동');
INSERT INTO user VALUES ('song', '송길동');
// 모든 컬럼 삽입 시 컬럼명 생략 가능

2. SELECT

SELECT @@AUTOCOMMIT;

SET autocommit=1;

autocommit=0; 수동커밋-> ROLLBACK 가능, COMMIT; 으로 확정
autocommit=1; 자동커밋

3. UPDATE 테이블명 SET col1=v1, ... WHERE 조건;

4. DELETE FROM 테이블명 WHERE 조건;

데이터만 삭제, 구조는 삭제x

DQL

SELECT

DCL

1. GRANT

2. REVOKE

TCL

START TRANSACTION;

: 여기서부터 COMMIT/ROLLBACK 전까지 하나의 트랜잭션
없으면 ROLLBACK -> 되돌릴 게 없음, COMMIT -> 확정할 게 없음

1. COMMIT

2. ROLLBACK

JOIN

1. JOIN ... USING

: 조인 조건 직접 작성 -> 공통 컬럼 한번만 표시

2. JOIN ... ON

: 양쪽 테이블에 같은 이름의 컬럼이 있을 때만 -> 여러 컬럼 출력

3. JOIN ... WHERE

합쳐진 후에 조건 필터링