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