[데이터베이스] 관계형 DB 문법 정리

Structured Query Language : SQL
= 구조화된 질문을 날리는 언어

 

스키마  생성

CREATE DATABASE 스키마이름;

 

스키마 (표) 목록 보기

SHOW DATABASES;

 

표 선택하기 (USE)

USE opentutorials;

 

표의 생성 (CREATE)

CREATE TABLE 표 이름(
    -> id INT(11) NOT NULL AUTO_INCREMENT, 
    -- column 이름, 자료형(글자수), 필수 입력(NOT NULL), 자동으로 1씩 올라감(AUTO_INCREMENT)
    -> title VARCHAR(100) NOT NULL,
    -> description TEXT NULL,
    -> author VARCHAR(30) NULL,
    -> profile VARCHAR(100) NULL,
    -> PRIMARY KEY(id));

 

표의 정보 보기 (DESC)

DESC 표의 이름

 

SELECT (읽기)

★ 표의 정보 가져오기 (SELECT)

SELECT * FROM opentutorial; 
-- select 한다 전체를 from opentutorial 에서

SELECT addr "주소", name "이름" FROM opentutorial; 
-- addr열과 name 열을 출력하는데 각각 주소, 이름이라는 별칭으로 출력됨

1. * 에 표시하고 싶은 데이터의 column 넣어주면 그 column의 데이터만 출력한다
2. 여러개 열을 가져오고 싶으면 , 로 구분함
3. 열의 별칭을 지정하고 싶으면 큰따옴표로 묶어줌

★ SELECT + WHERE (표의 정보 가져오기) : WHERE로 조건 걸었음

SELECT * FROM opentutorial WHERE author="egoing"; 
--where문은 값에 조건을 건다(author = "egoing"인 사람만 가져옴)

SELECT * FROM opentutorial WHERE height>180; 
--where문은 값에 조건을 건다(height>180인 사람만 가져옴)

SELECT * FROM opentutorial WHERE height>180 AND height<200; 
--where문은 값에 조건을 건다(height>180이고 height<200인 사람만 가져옴)

SELECT * FROM opentutorial WHERE height BETWEEN 180 AND 200; 
--where문은 값에 조건을 건다(height가 180~200인 사람만 가져옴)

SELECT * FROM opentutorial WHERE addr IN('경기', '전남'); 
--where문은 값에 조건을 건다(addr가 경기나 전남인 사람만 가져옴 => or같은 효과)

SELECT * FROM opentutorial WHERE mem_name LIKE '우%'; 
--where문은 값에 조건을 건다(mem_name에 우로 시작하는 사람을 전부 가져옴)
--LIKE는 그 문장이 들어 있는 녀석은 다 가져온다

 

• 기본 where
• where문 뒤에 관계연산자(<, >, <=, >=)
• where문 뒤에 논리연산자(AND, OR)
• BETWEEN ~ AND
• IN()
• LIKE("우%", "__핑크")

★ 서브쿼리 (SELECT + WHERE + 조건 안 SELECT문) : WHERE 조건 안에 SELECT문

SELECT mem_name, height FROM member 
	WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');
    -- 에이핑크 키보다 더 키가 큰 member들을 출력함

★ SELECT + ORDER BY : ORDER BY로 출력순서 정함

SELECT * FROM member WHERE height >= 164 ORDER BY height DESC;
-- ORDER BY로 값을 정렬한다(height를 내림차순으로 정렬함)

SELECT * FROM member WHERE height >= 164 ORDER BY height DESC, debut_date ASC; 
-- ORDER BY로 값을 정렬한다(height를 내림차순으로 정렬하고 height가 같으면 debut_date로 정렬함)

• 기본 ORDER BY
• ORDER BY로 정렬했는데 값이 같은 경우 정렬 기준을 하나 더 줌

★ SELECT + LIMIT : LIMIT로 출력 갯수 정함

SELECT * FROM opentutorial WHERE author="egoing" ORDER BY id DESC LIMIT 2; 
-- ORDER BY로 정렬된 값중 상위 2개만 출력함

SELECT * FROM opentutorial WHERE author="egoing" ORDER BY id DESC LIMIT 2, 3; 
-- ORDER BY로 정렬된 값중 상위에서 2번째에서 3개만 출력함

 

• 기본 LIMIT
• LIMIT 시작과 갯수 지정

★ DISTINCT : 중복을 제거함

SELECT DISTINCT address FROM member;
-- address가 겹치는 값이 있으면 하나로 만들어줌

 

★ SELECT + GROUP BY : 표를 그룹화하여 데이터를 조회(HARD★)

표애 같은 값이 여러개 있어서 이를 묶어서 한번에 보고 싶을 때 사용

SELECT member_id, SUM(price * amount) FROM buy GROUP BY member_id;
-- member_id 별로 묶어서 price * amount를 sum한 값을 출력

SELECT member_id, AVG(amount) FROM buy GROUP BY member_id;
-- member_id 별로 묶어서 amount를 avg한 값을 출력

SELECT COUNT(phone) "연락처가 있는 회원" FROM members;
-- members에서 phone이 null이 아닌 회원의 갯수를 세서 반환

★ 보통 집계 함수랑 주로 사용됨

• GROUP BY + SUM(column)
• GROUP BY + AVG(column)
• GROUP BY + MIN/MAX(column)
• GROUP BY + COUNT(column)

★ SELECT + GROUP BY + HAVING

GROUP BY에서 조건 걸려면 WHERE문 대신 HAVING 사용
(HAVING은 조건을 제한하지만 집계 함수에 대해서 조건을 제한함)

SELECT member_id, SUM(price * amount) "총 구매 금액" 
	FROM buy 
    GROUP BY member_id;
    HAVING SUM(price*amount) > 1000;
-- member_id 별로 묶어서 price * amount를 sum한 값중 1000이상인 값을 출력

 

INSERT (추가)

★ 표의 정보 추가하기 (INSERT)

title = "MYSQL" / description = "MYSQL is ..." / author = "egoing" (순서 잘 맞춰야함)

INSERT INTO opentutorial (title, description, author) VALUES ("MYSQL", "MYSQL is ...", "egoing");

INSERT INTO opentutorial
	SELECT ~;
-- SELECT로 출력될 문장들이 opentutorial에 저장된다

 

• 기본
• SELECT문을 이용한 INSERT

 

UPDATE (수정)

★ 표의 정보 수정하기 (UPDATE)

UPDATE 표이름 SET description = "description column 이걸로 바뀜", title = "title column 이걸로 바뀜"
	WHERE id=2;
-- 주의할점 : WHERE로 바꿀 데이터를 지정해줘야 함 
-- 안그러면 모든 description하고 title에 해당하는 데이터가 다 바뀜

 

DELETE (삭제)

★ 표의 정보 삭제하기 (DELETE)

DELETE FROM opentutorial WHERE id=3;
-- id가 3인 친구를 모두 삭제

 

AUTO_INCREMENT (자동으로 증가)

CREATE TABLE table1(
	toy_id INT AUTO_INCREMENT PRIMARY KEY,
    ...
)
-- AUTO_INCREMENT 열은 PRIMARY KEY로 지정해 줘야한다

ALTER TABLE table1 AUTO_INCREMENT = 100;
-- 자동 증가를 100부터 시작

SET @@auto_increment increment = 3;
-- 한번 증가할때 3씩 증가

 

데이터 형식

정수형

• TINYINT
• SMALLINT
• INT
• BIGINT

문자형

• CHAR
• VARCHAR(가변 CHAR)

대량의 데이터형식

• TEXT
• LONGTEXT
• BLOB (이미지, 동영상)
• LONGBLOB (이미지, 동영상)

실수형

• FLOAT
• DOUBLE

날짜형

• DATE
• TIME
• DATETIME

변수의 사용

SET @변수이름 = 변수의 값; 
-- 프로시저 밖에서 변수 선언 및 값 대입

SELECT @변수이름;
-- 프로시저 밖에서 변수의 값 출력

LIMIT에서는 @count를 사용할수 없음 => PREPARE과 EXECUTE 사용

SET @count = 3;
SELECT height FROM member ORDER BY height LIMIT @count
-- 오류 남
-- ---이렇게 해야함---
SET @count = 3;
PREPARE mySQL FROM 'SELECT height FROM member ORDER BY height LIMIT ?';
-- SQL문만 준비
EXECUTE mySQL USING @count;
-- 실행

데이터 형 변환

• 명시적인 변환 (CAST(값 AS 데이터 형식), CONVERT(값, 데이터 형식) : 두개 똑같은데 형식만 다름)
• 묵시적인 변환

 

JOIN (두 테이블을 묶음)

내부 조인

일대다 관계 : 회원 테이블에서 회원은 한명(pk: 회원아이디)이지만 구매 테이블에선 한명의 회원(회원아이디 pk 안됨)이 여러 물건을 삼

SELECT * FROM buy
	INNER JOIN member
    ON buy.member_id = member.member_id;
    -- buy 테이블 요소 하나하나에 member가 옆에 붙음

=> ON 조건에 해당하면 buy 테이블 옆에 member가 하나씩 붙음

외부 조인

내부조인과 차이점 : 내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나옴 but 외부 조인은 한쪽에만 데이터가 있어도 결과가 나옴

SELECT * FROM LEFT
	LEFT OUTER JOIN RIGHT
    ON LEFT.member_id = RIGHT.member_id;
    -- LEFT 테이블 요소 하나하나에 RIGHT 옆에 붙음 (값이 없으면 null 붙음)

내부 조인 vs 외부 조인

내부조인은 양쪽 다 값의 교집합이 있을때만 조인이 되는 반면에 외부 조인은 교집합이 없어도 사용자가 지정한 표에 모든 행에 조인이 된다 (교집합이 없으면 null로 라도 조인이 된다)

내부 조인의 결과

 

외부 조인의 결과

상호 조인

무조건 많은 데이터가 필요할때 사용 => 양쪽으로 동시에 조인

SELECT * FROM buy CROSS JOIN member;

 

자체 조인 (HARD)

회사의 조직관계 같은 곳에서 사용

스토어드 프로시저 (프로그래밍 기능이 필요할때 사용하는 데이터베이스 객체)

기본 구조

DELIMITER $$ -- 구분자라는 뜻
CREATE PROCEDURE whileProc()
BEGIN
    -- SQL 코딩  
END $$ 
DELIMITER ;
CALL whileProc(); -- 스토어드 프로시저 실행

IF 문

IF <조건식> THEN
	-- SQL 문장들
END IF;

IF ~ ELSE문

IF <조건문> THEN
	-- 참일때 실행
ELSE
	-- 거짓일때 실행
END IF;

SELECT + INTO

SELECT debu_date INTO debudate
-- debu_date에 뽑아지는 값을 debudate에 넣음

CASE문

CASE
    WHEN <조건1> THEN
    -- 구현할 SQL
    WHEN <조건2> THEN
    -- 구현할 SQL
    ...
    ELSE
    -- 구현할 SQL
END CASE;

WHILE문

WHILE <조건식> DO
	-- SQL 문장
END WHILE;

WHILE + ITERATOR,LEAVE

• ITERATOR = continue 기능
• LEAVE = break 기능

 

기본 문법 외 스킬

 IF 사용 + (SELECT, WHERE)

SELECT IF(a>b, "참일경우 출력", "거짓일경우 출력");

 

CASE 사용 + (SELECT, WHERE, ORDER BY)

SELECT M.mem_id, M.mem_name, IFNULL(SUM(price*amount), "0원") "총 결제금액",
CASE
    WHEN(SUM(price*amount)>=1500) THEN '최우수고객' 
    WHEN(SUM(price*amount)>=1000) THEN '우수고객' 
    WHEN(SUM(price*amount)>=1) THEN '일반고객' 
    ELSE '유령고객'
    END "회원등급"
	FROM member M
	LEFT OUTER JOIN buy B ON M.mem_id=B.mem_id
    GROUP BY M.mem_id ORDER BY SUM(price*amount) desc;

 

IFNULL() 함수 = NULL 처리

SELECT ANIMAL_TYPE, IFNULL(NAME, "NULL대신 들어갈값") AS NAME FROM ANIMAl;

IFNULL(NULL이 아니면 반환할 값, NULL이면 반환할값)  AS 기준이 될 column

 

DATE_FORMAT() 함수

SELECT DATE_FORMAT(debut_date, "%m-%d-%y") AS debut_date FROM member

DATE_FORMAT(날짜, "바꿀날짜형식") AS "표상단에 표시할 이름"

 

MIN(), MAX() + (SELECT에서 사용)

SELECT MIN(price), MAX(mem_id) FROM buy;

MAX/MIN(기준 열) = 기준열에서 최대/최소를 구해서 출력 = GROUP BY로 묶이면 GROUP에서 최대/최소 출력

 

GROUP BY하면 그룹에서 최대/최소값 구해서 출력한다

 

GROUP BY 안하면 전체에서 min/max값 찾아준다

 

 

COUNT() + (SELECT에서 사용)

SELECT COUNT(*) FROM buy;
-- buy 테이블의 행 갯수를 구한다

SELECT COUNT(DISTINCT mem_id) FROM buy;
-- 중복되는 mem_id를 제외한 행 갯수를 구한다

SELECT COUNT(*) FROM (SELECT * FROM buy GROUP BY group_name)sub;
-- 중복되는 group_name을 제거하고 세는데 null값도 count로 셀때 사용
-- 그냥 DISTINCT COUNT보다 + 1 증가함

 

AVG(), SUM() + (SELECT에서 사용)

SELECT first_name, AVG(sales)
	FROM cookie_sales
	GROUP BY first_name;
-- GROUP 된 항목의 sales의 평균을 구함

SELECT mem_id, SUM(price*amount) "총 결제금액"
	FROM buy 
    GROUP BY mem_id ORDER BY SUM(price*amount) desc;
-- GROUP 된 항목의 price*amount의 합을 구함

 

CEIL, FLOOR, ROUND, POW(올림, 버림, 반올림, 제곱)

SELECT CEIL(10, 1) -- 소수점 첫째자리에서 올림
SELECT FLOOR(10, 1) -- 소수점 첫째자리에서 버림
SELECT ROUND(10, 1) -- 소수점 첫째자리에서 반올림
SELECT POW(10, 2) -- 10의 2승

 

NULL은 = NULL이 아니라 IS NULL로 찾아야한다

SELECT * FROM buy WHERE group_name IS NULL;
-- 결과 출력

SELECT * FROM buy WHERE group_name IS NOT NULL;
-- 결과 출력

SELECT * FROM buy WHERE group_name = NULL;
-- 결과 안나옴

 

SUBSTRING()

SUBSTRING(자를문자,시작,끝);
--문자를 시작~끝까지 잘라서 반환

 

쌍으로 비교를 해야하는 경우

 SELECT FOOD_TYPE,REST_ID,REST_NAME,FAVORITES FROM rest_info 
 	WHERE (FOOD_TYPE,FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES) FROM rest_info GROUP BY FOOD_TYPE)
    -- 이런식으로 쌍으로 사용 가능

 

https://velog.io/@nn1co1/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4%EC%A6%90%EA%B2%A8%EC%B0%BE%EA%B8%B0%EA%B0%80-%EA%B0%80%EC%9E%A5-%EB%A7%8E%EC%9D%80-%EC%8B%9D%EB%8B%B9-%EC%A0%95%EB%B3%B4-%EC%B6%9C%EB%A0%A5%ED%95%98%EA%B8%B0

[프로그래머스]즐겨찾기가 가장 많은 식당 정보 출력하기

이 문제 형식 참고 

 

Hour 0부터 23 까지 출력

https://jaaamj.tistory.com/155 (hour 0부터 23까지 출력)

[프로그래머스 SQL] 입양 시각 구하기(2)

 

 

 

한번 더 볼만한 문제

 

SUM, MAX, MIN

가격이 제일 비싼 식품의 정보 출력하기

중복 제거하기

 

GROUP BY

다 풀어봐

 

<TIP>
표에 정보 추가(C), 수정(U), 삭제(D)는 굉장히 쉽지만 읽기(R)는 어렵다
윗쪽 화살표 누르면 전에 쳤던 코드 나옴
SQL에서 하이픈 2개(--) 하면 주석 취급이 된다