본문 바로가기
[Tools]/[SQL]

The SQL cheatsheet-Created

by 보끔밥0130 2024. 1. 29.
728x90

 

 

데이터 베이스 구조 생성

 

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

  • 데이터베이스는 폴더로 비유할 수 있다.
  • 그 폴더 안에 여러 파일이 존재하듯 테이블이 있다.
  • 각 테이블은 여러 컬럼과 그 컬럼에 있는 값이 들어있는 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;
728x90

'[Tools] > [SQL]' 카테고리의 다른 글

[Cheat Sheet] SQL QUERY  (0) 2024.01.27

댓글