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