The SQL cheatsheet-Created

 

 

데이터 베이스 구조 생성

 

테이터 베이스, 테이블 구조

• 데이터베이스는 폴더로 비유할 수 있다.
• 그 폴더 안에 여러 파일이 존재하듯 테이블이 있다.
• 각 테이블은 여러 컬럼과 그 컬럼에 있는 값이 들어있는 Sheet가 생성된다.

 

데이터 베이스

QUERY	description
CREATE DATABASE [데이터베이스 이름];	데이터 베이스 만들기
SHOW DATABASES;	모든 데이터 베이스 조회하기
USE [데이터베이스 이름];	데이터베이스 사용 선언하기
DROP DATABASE [데이터베이스 이름];	데이터베이스 지우기

테이블 다루기 $( 테이블, 컬럼$)

QUERY	description
CREATE TABLE [테이블 이름] $([컬럼 이름], [데이터 타입],[컬럼 이름], [데이터 타입],…$);	테이블 생성하기
ALTER TABLE [테이블 이름] RENAME [새로운 테이블 이름];	테이블 이름 변경하기
ALTER TABLE [테이블 이름] ADD COLUMN [컬럼 이름] [데이터 타입];	새로운 컬럼 추가하기
ALTER TABLE [테이블 이름] MODIFY COLUMN [컬럼 이름] [새로운 데이터 타입];	기존 컬럼 타입 변경하기
ALTER TABLE [테이블 이름] CHANGE COLUMN [컬럼 이름] [새로운 컬럼 이름] [새로운 데이터 타입];	기존 컬럼 이름과 타입 변경하기
ALTER TABLE [테이블 이름] DROP COLUMN [컬럼 이름];	컬럼 지우기

 

테이블 다루기 $( 값$)

QUERY	Description
TRUNCATE TABLE [데이터베이스 이름];	테이블 값만 지우기
INSERT INTO [테이블 이름] $[컬럼 1 이름], [컬럼 2이름], [컬럼 3 이름]$ VALUE $[ 컬럼 1 값], [ 컬럼 2 값], [ 컬럼 3 값] …$;	데이터 여러 개 삽입하기
DELETE FROM [테이블 이름] WHERE 조건식;	데이터 삭제하기
UPDATE [테이블 이름] SET [컬럼 이름] = [새 값] WHERE 조건식;	데이터 수정하기

 

QUERY 작성하기

 

쿼리 원리

• 필수 쿼리 “작성” 순서 : SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
• 필수 쿼리 “실행” 순서 : FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

 

6가지 쿼리 키워드

KEYWORD	Syntax	Description
SELECT	SELECT [컬럼 이름]	가져올 데이터를 선택
FROM	FROM [테이블 이름]	데이터를 가져올 테이블을 지정
WHERE	WHERE 조건식	가져올 데이터의 조건을 지정
GROUP BY	GROUP BY [컬럼 이름]	컬럼에서 동일한 값을 가지는 행을 그룹화
HAVING	HAVING 조건식	가져올 데이터 그룹에 조건을 지정
ORDER BY	ORDER BY [컬럼 이름]	가져온 데이터를 정렬

 

 

쿼리 제약 키워드

KEYWORD	Syntax	Description
AS	AS [컬럼 이름]	가져온 데이터에 별명을 지정
LIMIT	LIMIT [로우 수]	가져올 데이터의 로우 개수를 지정
DISTINCT	DISTINCT [컬럼 이름]	중복된 데이터는 제외하여 같은 값은 한 번만 가져옴

 

연산자

비교 연산자

Operation	Syntax	Description
=	A = B	A와 B가 같다.
!=	A != B	A와 B가 같지 않다.
>	A > B	A가 B보다 크다
>=	A >= B	A가 B보다 크거나 작다
<	A < B	A가 B보다 작다
<=	A <= B	A가 B보다 작거나 같다.

 

논리 연산자

Operation	Syntax	Description
AND	A AND B	A와 B 모두 True이면 True
OR	A OR B	A와 B 둘 중 하나라도 True이면 True
NOT	NOT A	A가 아니면 True

 

사용 빈도 높은 연산자

Operation	Syntax	Description
BETWEEN	[컬럼 이름] BETWEEN A AND B	특정 범위 내의 데이터를 선택할 때 사용하는 연산자
IN	[컬럼 이름] IN $(A, B, C$)	목록 내 포함되는 데이터를 선택할 때 사용하는 연산자
LIKE	[컬럼 이름] LIKE [검색할 문자열]	특정 문자열이 포함된 데이터를 선택하는 연산자
IS NULL	[컬럼 이름] IS NULL	데이터가 NULL인지 아닌지를 확인하는 연산자

 

함수

순위 함수

Function	Description
RANK	공동 순위가 있으면 다음 순위는 순서를 건너 뜀
DENSE_RANK	공동 순위가 있어도 다음 순위는 순서를 뛰어 넘지 않음
ROW_NUMBER	공동 순위를 무시함

집계 함수

• 집계 함수는 주로 Group에서 사용하나 컬럼 값 전체에도 사용 가능

Function	Description
COUNT	그룹의 값 수를 세는 함수
SUM	그룹의 합을 계산하는 함수
AVG	그룹의 평균을 계산하는 함수
MIN	그룹의 최솟값을 반환하는 함수
MAX	그룹의 최댓값을 반환하는 함수

데이터 함수

Function	Syntax	Description
LOCATE	LOCATE$(”A”, “ABC”$)	“ABC”에서 “A”는 몇 번째에 위치해 있는지 검색해 위치 반환
SBUSTRING	SUBSTRING$(”ABC”,2$)	“ABC”에서 2번째 문자부터 반환
RIGHT	RIGHT$(”ABC”,1$)	“ABC”에서 오른쪽으로 1번째 문자까지 반환
LEFT	LEFT$(”ABC”,1$)	“ABC”에서 왼쪽으로 1번째 문자까지 반환
UPPER	UPPER$(”abc”$)	“abc”를 대문자로 바꿔 반환
LOWER	LOWER$(’ABC”$)	“ABC”를 소문자로 바꿔 반환
LENGTH	LENGTH$(”ABC”$)	“ABC”의 글자 수를 반환
CONCAT	CONCAT$(”ABC”,”DEF”$)	“ABC” 문자열과 “CDF”문자열을 합쳐 반환
REPLACE	REPLACE$(”ABC”,”A”,”Z”$)	“ABC”의 “A”를 “Z”로 바꿔 반환

숫자형 함수

Function	Syntax	Description
ABS	ABS$(숫자$)	숫자의 절댓값 반환
CEILING	CEILING$(숫자$)	숫자를 정수로 올림해서 반환
FLOOR	FLOOR$(숫자$)	숫자를 정수로 내림해서 반환
ROUND	ROUND$(숫자, 자릿수$)	숫자를 소수점 자릿수까지 반올림해서 반환
TRUNCATE	TRUNCATE$(숫자, 자릿수$)	숫자를 소수점 자릿수까지 버림해서 반환
POWER	POWER$(숫자A, 숫자B$)	숫자A의 숫자B 제곱 반환
MOD	MOD$(숫자A, 숫자B$)	숫자A를 숫자B로 나눈 나머지 반환

날짜형 함수

Function	Syntax	Description
NOW	NOW$($)	현재 날짜와 시간 반환
CURRENT_DAT	CURRENT_DATE$($)	현재 날짜 반환
CURRENT_TIME	CURRENT_TIME$($)	현재 시간 반환
YEAR	YEAR$(날짜$)	날짜의 연도 반환
MONTH	MONTH$(날짜$)	날짜의 월 반환
MONTHNAME	MONTHNAME $(날짜$)	날짜의 월을 영어로 반환
DAYNAME	DAYNAME $(날짜$)	날짜의 요일을 영어로 반환
DAYOFMONTH	DAYOFMONTH $(날짜$)	날짜의 일 반환
DAYOFWEEK	DAYOFWEEK $(날짜$)	날짜의 요일을 숫자로 반환 $일요일 1$
WEEK	WEEK$(날짜$)	날짜가 해당 연도에 몇 번째 주인지 반환
HOUR	HOUR$(시간$)	시간의 시 반환
MINUTE	MINUTE$(시간$)	시간의 분 반환
SECOND	SECOND$(시간$)	시간의 초 반환
DATE_FORMAT	DATE_FORMAT$(날짜/시간, 형식$)	날짜/시간의 형식을 형식으로 바꿔 반환
DATEDIFF	DATEDIFF$날짜1, 날짜2$	날짜1과 날짜2의 차이 반환 $날짜1 ‒ 날짜2$
TIMEDIFF	TIMEDIFF$시간1, 시간2$	시간1과 시간2의 차이 반환 $시간1 ‒ 시간2$

 

DATE_FORMAT

Expression	Description	Expression	Description
%Y	연도를 네 글자로 표현 $(0000-9999$)	%H	시를 24시간으로 표현 $(00-23$)
%m	월을 표현 $(00-12$)	%i	분을 표현 $(00-59$)
%d	일자를 항상 숫자 두 글자로 표현 $(00-31$)	%s	초를 표현 $(00-59$)

조건 만들기

Function	Syntax	Description
IF	IF$(조건식, 참일 때 값, 거짓일 때 값$)	조건을 만들기
CASE 단일 값	CASE [컬럼 이름] WHEN 조건값1 THEN 결과값1 WHEN 조건값2 THEN 결과값2 ELSE 결과값3 END	여러 조건 한번에 만들기
CASE 조건 식	CASE WHEN 조건식1 THEN 결과값1 WHEN 조건식2 THEN 결과값2 ELSE 결과값3 END	여러 조건 한번에 만들기 $주로 SELECT 절에 사용하는 함수로, 결과 값을 새로운 컬럼으로 반환$

테이블 합치기

Function	Set	Syntax	Description
INNER JOIN	$A \cap B$	SELECT [컬럼 이름] FROM [테이블 A 이름] INNER JOIN [테이블 B 이름] ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름];	두 테이블에 모두 지정한 열의 데이터만 합치기
LEFT JOIN	$A \cap B^c$	SELECT [컬럼 이름] FROM [테이블 A 이름] LEFT JOIN [테이블 B 이름] ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름];	왼쪽 테이블에 있는 값만 합치기
RIGHT JOIN	$A^c\cap B$	SELECT [컬럼 이름] FROM [테이블 A 이름] RIGHT JOIN [테이블 B 이름] ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름];	오른쪽 테이블에 있는 값만 합치기
OUTER JOIN	$A \cup B$	SELECT [컬럼 이름] FROM [테이블 A 이름] LEFT JOIN [테이블 B 이름] ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] UNION SELECT [컬럼 이름] FROM [테이블 A 이름] RIGHT JOIN [테이블 B 이름] ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] ;	두 테이블에 있는 모든 값 합치기 $1개의 테이블에만 데이터가 있어도 결과나옴$
FULL OUTER JOIN	$A \cup B$	SELECT column_name$s$ FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;	왼쪽$table1$ 또는 오른쪽$table2$ 테이블 레코드에 일치하는 항목이 있을 때 모든 레코드를 반환
CROSS JOIN $Cartesian Product$	$A \times B$	SELECT [컬럼 이름] FROM [테이블 A 이름] CROSS JOIN [테이블 B 이름]	두 테이블에 있는 모든 가능한 조합
SELF JOIN	$A \cap A$	SELECT [컬럼 이름] FROM [테이블 A 이름] AS t1 INNER JOIN [테이블 A 이름 ] AS t2 ON t1.[컬럼 A 이름] = t2.[컬럼 A 이름];	같은 테이블에 있는 값 합치기

함수 정의하기

 

함수 생성

CREATE FUNCTION [함수 이름] (
                [입력값 이름] [데이터 타입], …)
                RETURNS [결과값 데이터 타입]
BEGIN
                DECLARE [임시값 이름] [데이터 타입]; -- 타입 선언
                SET [임시값 이름] = [입력값 이름]; -- 변수 선언
                쿼리; -- SELECT 식 INTO (데이터를 넣어준다) 입력값 이
                RETURN 결과값
END

함수 지우기

DROP FUNCTION [함수 이름];

 

 

MySQL Workbench에서 함수 생성 시 주의점

• DELIMITER : 프로시저 자체를 하나의 명령문으로 묶어주기 위한 구분자 재정의

SET GLOBAL log_bin_trust_function_creators = 1;

-- ※ 사용자 계정에 function create 권한 생성
DELIMITER // -- ※ 함수의 시작 지정

-- function 생성
CREATE FUNCTION
END
//
DELIMITER ; -- 함수의 끝 지정, 꼭 1칸 띄어 주세요!!!

여러 테이블 한번에 다루기

Function	Set	Syntax
합집합	$A \cup B$	SELECT [컬럼 이름] FROM [테이블 A 이름] UNION SELECT [컬럼 이름] FROM [테이블 B 이름];
중복 포함 합집합	$A \cup B$	SELECT [컬럼 이름] FROM [테이블 A 이름] UNION ALL SELECT [컬럼 이름] FROM [테이블 B 이름];
교집합	$A \cap B$	SELECT [컬럼 이름] FROM [테이블 A 이름] AS A INNER JOIN [테이블 B 이름] AS B ON A.[컬럼1 이름] = B.[컬럼1 이름] AND ... AND A.[컬럼n 이름] = B.[컬럼n 이름];
차집합	$A \cap B^c$	SELECT [컬럼 이름] FROM [테이블 A 이름] AS A LEFT JOIN [테이블 B 이름] AS B ON A.[컬럼1 이름] = B.[컬럼1 이름] AND ... AND A.[컬럼n 이름] = B.[컬럼n 이름] WHERE B.[컬럼 이름] IS NULL;