Schema
생성
CREATE DATABASE db명;
ex) CREATE DATABASE market_db;
삭제
DROP DATABASE IF EXISTS db명;
ex) DROP DATABASE IF EXISTS market_db;
조회
SHOW DATABASES;
사용
USE db명;
ex) USE market_db;
Table
column 타입
정수형
TINYINT, SMALLINT, INT, BIGINT
실수형
FLOAT, DOUBLE
문자형
CHAR, VARCHAR
날짜형
DATE(날짜), TIME(시간), DATETIME(날짜와 시간)
대량 데이터
TEXT, LONGTEXT, BLOB, LONGBLOB
생성
CREATE TABLE 테이블명 (
column명 타입(글자 수 제한) NULL 허용 여부,
⋯
)
ex) CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
mem_number TINYINT NOT NULL,
addr CHAR(2) NOT NULL,
phone1 CHAR(3),
phone2 CHAR(8),
height TINYINT UNSIGNED, -- 부호 없음 (범위 0부터 시작)
birth_date DATE
)
삭제
DROP TABLE 테이블명; (테이블 삭제)
ex) DROP TABLE member;
TRUNCATE TABLE 테이블명; (빈 테이블 남김, 속도 빠름)
ex) TRUNCATE TABLE member;
DELETE FROM 테이블명; (빈 테이블 남김, 속도 느림)
ex) DELETE FROM member;
AUTO_INCREMENT(PRIMARY KEY 자동 index)
Table 생성 시
ex) CREATE TABLE member (
mem_id INT AUTO_INCREMENT PRIMARY KEY,
mem_name CHAR(4),
age INT);
INSERT 시
ex) INSERT INTO member VALUES (NULL, '홍길동', 20); -- id : 1
INSERT INTO member VALUES (NULL, '김길동', 30); -- id : 2
index 변경 시
ex) ALTER TABLE member AUTO INCREMENT = 1000; -- 시작값 1000
SET @@auto_increment_increment = 3; -- 증가값 3씩
마지막 index 확인
SELECT LAST_INSERT_ID();
Table 구조 확인(DESCribe)
DESC 테이블명
ex) DESC member;
CRUD
INSERT
전체 column (Table 정의와 입력 순서 동일해야 함)
INSERT INTO 테이블명 VALUES (int타입, 'CHAR타입', 'DATE타입', ⋯)
ex) INSERT INTO member VALUES (3, '홍길동', '2023.04.09')
일부 column (입력 순서 상관없음)
INSERT INTO 테이블명 (column1, column2, column3, ⋯) VALUES ('CHAR타입', 'DATE타입', int타입, ⋯)
ex) INSERT INTO member (mem_name, mem_id) VALUES ('홍길동', 3)
다른 테이블 데이터 삽입
INSERT INTO 테이블명
SELECT column명, ⋯ FROM 다른 테이블명
ex) INSERT INTO member
SELECT mem_id, mem_name FROM member2
UPDATE
UPDATE 테이블명
SET column명 = 값, ⋯
(WHERE 조건식);
ex) UPDATE member
SET mem_name = '홍길동', height = 180
WHERE mem_id = 1;
DELETE
DELETE FROM 테이블명
WHERE 조건식
(LIMIT 값);
ex) DELETE FROM member
WHERE mem_name LIKE '홍%'
LIMIT 5;
SELECT
모든 column
SELECT * FROM db명.테이블명; (스키마 USE 중인 경우 : SELECT * FROM 테이블명;)
ex) SELECT * FROM market_db.member; (SELECT * FROM member;)
특정 column
SELECT column명, ⋯ FROM 테이블명;
ex) SELECT mem_name, height FROM member;
특정 row
SELECT column명, ⋯ FROM 테이블명 WHERE 조건식;
ex) SELECT * FROM member WHERE height >= 180 AND mem_number < 5;
서브 쿼리
조회 결과를 이용한 조건식으로 조회
ex) SELECT mem_name, height FROM member
WHERE height > (SELECT height FROM member WHERE mem_name = '홍길동');
ORDER BY(정렬, WHERE 절보다 뒤에 나와야 함)
오름차순(ASCending)
ex) SELECT mem_id, mem_name
FROM member
WHERE mem_number > 5
ORDER BY height, weight; -- 키가 동일할 경우 몸무게 순 정렬
내림차순(DESCending)
ex) SELECT mem_id, mem_name
FROM member
WHERE mem_number > 5
ORDER BY height DESC, weight; -- 키는 내림차순, 몸무게는 오름차순
LIMIT
출력 개수 제한(주로 ORDER BY와 함께 사용)
ex) SELECT *
FROM member
ORDER BY height
LIMIT 3; -- 0번째부터 3건 조회
ex) SELECT *
FROM member
ORDER BY height DESC
LIMIT 5, 2; -- 5번째부터 2건 조회
DISTINCT
중복 결과 제거(column명 앞에 사용)
SELECT DISTINCT addr FROM member;
집계 함수 (주로 GROUP BY와 함께 쓰여 같은 column값 별 집계)
합계(SUM)
ex) SELECT mem_id "회원 아이디", SUM(amount) "총 구매 개수", SUM(price * amount) "총 구매 금액"
FROM buy
GROUP BY mem_id
HAVING SUM(price * amount) > 1000 -- GROUP BY는 WHERE 절과 사용 불가, HAVING 절 사용
ORDER BY SUM(price * amount) DESC;
평균(AVG)
ex) SELECT mem_id "회원 아이디", AVG(amount) "평균 구매 개수"
FROM buy
GROUP BY mem_id;
개수(COUNT)
ex) SELECT COUNT(*) "회원 수" FROM member
ex) SELECT COUNT(phone1) "연락처가 있는 회원 수" FROM member;
최대, 최소(MIN, MAX)
ex) SELECT MIN(height) FROM member;
ex) SELECT MAX(height) FROM member;
값 변경해서 조회
SELECT IF(조건, 참일 때, 거짓일 때) 'column명'
ex) SELECT mem_id, IF(mem_name IS NULL, group_name, mem_name) 'mem_or_group_name'
-- mem_name값이 NULL일 경우 group_name으로 출력, NULL이 아닐 경우 mem_name으로 출력, column명은 mem_or_group_name (지정 안 하면 column명이 IF(mem_name IS NULL, group_name, mem_name)이 됨)
FROM member;
값 설정
ex) SELECT null AS mem_id, 'abcd' AS mem_name;
DATE FORMAT 변경
SELECT DATE_FORMAT(column명, 형식) 'column명'
ex) SELECT mem_id, DATE_FORMAT(birth_date, '%Y-%m-%d') 'birth_date' -- 2023-04-09 형식으로 변경
FROM member;
특정 기간 조회
ex) SELECT mem_id, DATE_FORMAT(birth_date, '%Y-%m-%d') 'birth_date' -- birth_date의 형식을 변경했으므로, column명 지정 안 하면 날짜 설정 안 됨
FROM member;
WHERE birth_date BETWEEN '2023-01-01' AND '2023-12-31';
조건식
관계 연산자
column명 =, <, <=, >, >= 값
ex) height = 180
논리 연산자
관계 연산자 AND, OR 관계 연산자
ex) height >= 180 AND mem_number < 5;
ex) height >= 180 OR mem_number < 5;
BETWEEN ~ AND
column명 BETWEEN 값 AND 값;
ex) height BETWEEN 170 AND 180;
IN
문자형인 경우 BETWEEN 대신 사용
ex) addr IN('경기', '전남', '경남')
LIKE
일부 글자 검색할 때
ex) mem_name LIKE '김%'
정확한 글자 수로 검색할 때
ex) country_name LIKE '__민국'
기타
변수 선언, 출력
SET @변수명 = 값;
SELECT @변수명;
ex) SET @txt = '이름 : ';
SET @height = 160;
SELECT @txt, mem_name FROM member WHERE height > @height;
준비, 실행
LIMIT 문에 변수를 사용할 때 이용
ex) SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?'; -- 준비
EXECUTE mySQL USING @count; -- 실행
문자 덧셈
CONCAT(값1, 값2)
ex) SELECT CONCAT('홍', '길동');
형 변환
강제 형 변환
CAST(값 AS 데이터 형식)
ex) SELECT CAST(AVG(price) AS SIGNED INTEGER) '평균 가격' FROM buy; -- 부호 있는 정수
ex) SELECT CAST(AVG(price) AS UNSIGNED INTEGER) '평균 가격' FROM buy; -- 부호 없는 정수
ex) SELECT CAST('2023$04$09' AS DATE);
ex) SELECT num, CONCAT( CAST(price AS CHAR), ' X ', CAST(amount AS CHAR, '=') ) '가격 X 수량',
price * amount '구매액'
FROM buy;
CONVERT(값, 데이터 형식)
SELECT CONVERT(AVG(price), SIGNED INTEGER) '평균 가격' FROM buy;
자동 형 변환
숫자로 변환
ex) SELECT 100 + '200' -- 자동 형 변환 => 300
문자로 변환
ex) SELECT CONCAT(100, '200') -- 자동 형 변환 => 100200
JOIN
내부 조인(양쪽 다 있는 행 데이터만 연결)
ex) SELECT DISTINCT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT (M.phone1, M.phone2) '연락처'
FROM buy B -- buy 테이블의 별칭 : B
JOIN member M -- member 테이블의 별칭 : M
ON B.mem_id = M.mem_id; -- 구매 기록이 있는 회원 목록
외부 조인(왼쪽, 오른쪽 또는 어느 한쪽이 가진 행 데이터 전부 연결)
ex) SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member B
LEFT JOIN member M -- RIGHT JOIN, FULL JOIN
ON M.mem_id = B.mem_i
WHERE B.prod_name IS NULL -- 구매 기록이 없는 회원 목록
ORDER BY M.mem_id;
상호 조인(모든 행 X 모든 행) 연결
ex) SELECT * FROM buy CROSS JOIN member;
자체 조인(자기 자신과 조인)
ex) SELECT A.emp "직원", B.emp "직속상관", B.phone "직속상관연락처"
FROM emp_table A
JOIN emp_table B
ON A.manager = B.emp
WHERE A.emp = '경리부장'; -- 경리부장과 직속상관의 연락처를 연결
UNION(병합해서 조회)
전체 병합
ex) SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
UNION ALL
SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE BETWEEN '2022-03-01' AND '2022-03-31'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID