데이터를 읽고쓰는 공간

메인 쿼리(OUTER QUERY) + 서브 쿼리(INNER QUERY)

<쿼리의 핵심 키워드 6가지>

SELECT FROM WHERE GROUP BY HAVING ORDER BY

💡 GROUP BY 제외한 나머지 핵심 키워드에 서브 쿼리 적용 가능

• 서브 쿼리 : 하나의 쿼리 내에 포함된 또 하나의 쿼리
• 서브 쿼리는 괄호 안에 있어야 함
• INSERT, UPDATE, DELETE에도 적용 가능
• 서브 쿼리에는 ; (세미 콜론)을 붙이지 않아도 됨

SELECT절의 서브 쿼리 (스칼라 서브 쿼리)

• 반드시 결과값이 하나의 값이어야 함

SELECT [칼럼 이름]

(

SELECT

FROM

WHERE

)

FROM [테이블 이름] WHERE 조건식;

FROM절의 서브 쿼리 (인라인 뷰 서브쿼리)

• 반드시 결과값이 하나의 테이블이어야 함
• 반드시 서브쿼리로 만든 테이블은 별명을 가져야 함

SELECT [칼럼 이름]

FROM

(

SELECT

FROM

WHERE

) AS [테이블 별명]

WHERE 조건식;

FROM절의 서브 쿼리 (중첩 서브쿼리)

• 반드시 결과값이 하나의 칼럼이어야 함 (EXISTS 제외)
  • 하나의 칼럼에는 여러 개의 값이 존재할 수 O
• 연산자와 함께 사용합니다
  • WHERE [칼럼 이름] [연산자] [서브 쿼리] 형식

SELECT [칼럼 이름]

FROM [테이블 이름]

WHERE [칼럼 이름] [연산자]

(

SELECT

FROM

WHERE

);

ALL

< (작다) : 서브 쿼리의 최솟값보다 작은지

> (크다) : 서브 쿼리의 최댓값보다 큰지

ANY

< (작다) : 서브 쿼리의 최댓값보다 작은지

> (크다) : 서브 쿼리의 최솟값보다 큰지

합집합 (UNION, UNION ALL)

SELECT [칼럼 이름]
FROM [테이블 A 이름]
(UNION or UNION ALL)

SELECT [칼럼 이름]

FROM [테이블 B 이름];

• UNION : 동일한 값 제외
• UNION ALL : 동일한 값도 포함

• 쿼리 A와 쿼리 B의 결과 값의 개수가 같아야 함
• ORDER BY는 쿼리 A에서 가져온 칼럼으로만 가능

💡 교집합 (INTERSECT), 차집합 (MINUS) *mysql에는 두 표현이 존재하지 않음 → JOIN으로 표현

교집합

SELECT [칼럼 이름]

FROM [테이블A 이름] AS A INNER JOIN [테이블 B 이름] AS B

ON A.[칼럼1 이름] = B.[칼럼2 이름] AND … AND A.[칼럼n 이름] = B.[칼럼 n 이름];

차집합 (A - B)

SELECT [칼럼 이름]

FROM [테이블A 이름] AS A LEFT JOIN [테이블 B 이름] AS B

ON A.[칼럼1 이름] = B.[칼럼2 이름] AND … AND A.[칼럼n 이름] = B.[칼럼 n 이름]

WHERE B.[칼럼 이름] IS NULL;

조건 만들기

IF

• IF(조건식, 참일 때 값, 거짓일 때 값)
• SELECT절에 사용하는 함수
• 결과 값을 새로운 칼럼으로 반환함

IFNULL

• IFNULL([칼럼 이름], NULL일 때 값)
• SELECT절에 사용하는 함수
• 데이터가 NULL인지 아닌지 판단하고, 결과 값을 새로운 칼럼으로 반환함

여러 조건 한번에 만들기

CASE

CASE

WHEN 조건식1 THEN 결과값1

WHEN 조건식2 THEN 결과값2

ELSE 결과값3

END

• SELECT절에 사용하는 함수
• 결과 값을 새로운 칼럼으로 반환함
• ELSE 문장 생략시 NULL 값 반환

함수 만들기

################### mysql workbench 함수 생성시 주의할 점 #########################
SET GLOBAL log_bin_trust_function_creators = 1; # 사용자 계정에 function create 권한 생성
DELIMITER // # 함수의 시작 지정

######################################################
################### 함수 생성 #########################
######################################################
CREATE FUNCTION getScore(math INT, science INT) # 입력값, 데이터 타입 설정
								RETURNS INT # 결과값 데이터 타입 설정 (RETURN 뒤에 S 주의)
BEGIN 
			DECLARE a INT; # 임시값, 데이터 타입
			DECLARE b INT;
			DECLARE score INT;
			SET a = math; # 임시값에 입력값 세팅 
			SET b = science;
			SELECT a + b INTO score; # 쿼리
			RETURN score; # 반환할 결과값
END
######################################################
######################################################
######################################################

// DELIMITER ; # 함수의 끝 지정 (; 앞에 한 칸 띄우기 주의)

데이터 그룹화하기 (GROUP BY)

SELECT [GROUP BY 대상 칼럼 이름], …, [그룹 함수]

FROM [테이블 이름]

WHERE 조건식

GROUP BY [칼럼 이름];

💡 SELECT 절에는 GROUP BY 대상 칼럼과 그룹 함수만 사용 가능

• 여러 칼럼 정렬 가능
• 칼럼 번호로도 정렬 가능

그룹에 조건 추가 (HAVING)

SELECT [칼럼 이름], …, [그룹 함수]

FROM [테이블 이름]

WHERE 조건식

GROUP BY [칼럼 이름]

HAVING 조건식;

다양한 그룹 함수들

• COUNT, SUM, AVG, MIN, MAX : GROUP BY 대상 칼럼이랑 같아도 OK 달라도 OK

쿼리의 실행 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

💡 ORDER BY 대상 칼럼은 칼럼 이름 대신 SELECT 칼럼 순서로 명시할 수 있음!

데이터 줄 세우기 (ORDER BY)

SELECT [칼럼 이름] FROM [테이블 이름] WHERE 조건식 ORDER BY [칼럼 이름] ASC;

ORDER BY [칼럼 이름]

• 입력한 칼럼 기준으로 모든 row가 정렬됨
• 기본 정렬 규칙 = 오름차순(ASC)
• 내림차순(DESC)
• 여러 칼럼 정렬 가능
  • SELECT name, age, gender FROM mytable ORDER BY name DESC, age;
• 칼럼 번호로도 정렬 가능
  • SELECT name, age, gender FROM mytable ORDER BY 1, 2;

데이터 순위 만들기 (RANK)

SELECT [칼럼 이름], …, RANK() OVER (ORDER BY [칼럼 이름]) FROM [테이블 이름] WHERE 조건식;

RANK() OVER (ORDER BY [칼럼 이름])

• 항상 ORDER BY와 함께 사용
• 정렬된 순서에 순위를 붙인 새로운 칼럼 보여줌, 실제 데이터에 영향 미치지 X

SELECT name, age, RANK() OVER (ORDER BY age DESC) AS age_rank FROM mytable;

문자형 데이터 다루기

• LOCATE(’A’, ‘ABC) : ABC에서 A의 위치 반환
  • 찾는 문자열이 없으면 0 반환
• SUBSTRING(’ABC’, 2) : ABC에서 2번째 문자부터 반환
  • 입력한 숫자가 문자열 길이보다 크다면 아무것도 반환 X
• RIGHT(’ABC’, 1) : ‘ABC’에서 오른쪽부터 1번째 문자까지 반환
• LEFT(’ABC’, 1) : ‘ABC’에서 왼쪽부터 1번째 문자까지 반환
• UPPER(’abc’) : 문자열 모두 대문자로 반환
• LOWER(’ABC) : 문자열 모두 소문자로 반환
• LENGTH(’ABC’) : 문자열의 글자 수 반환
• CONCAT(’ABC’, ‘DEF’) : ‘ABC’문자열과 ‘DEF’ 문자열을 합쳐 반환
• REPLACE(’ABC’, ‘A’, ‘Z’) : ‘ABC’의 ‘A’를 ‘Z’로 바꿔 반환

숫자형 데이터 다루기

• ABS(숫자) : 절댓값 반환
• CEILING(숫자) : 올림
• FLOOR(숫자) : 내림
• ROUND(숫자, 자릿수) : 자릿수까지 반올림해서 반환
• TRUNCATE(숫자, 자릿수) : 자릿수까지 버림해서 반환
  *자릿수에 0 입력시 정수만 반환
• POWER(숫자1, 숫자2) : 숫자1의 숫자2 제곱 반환
• MOD(숫자1, 숫자2) : 숫자1를 숫자2로 나눈 나머지 반환
  *2로 나누면 홀수인지 짝수인지 분별 가능

날짜형 데이터 다루기

• NOW() : 현재 날짜와 시간 반환
• CURRENT_DATE() : 현재 날짜 반환
• CURRENT_TIME() : 현재 시간 반환
  *위 3개는 입력값 필요 X

• YEAR(날짜) : 날짜의 연도 반환
• MONTH(날짜) : 날짜의 월 반환
• MONTHNAME(날짜) : 날짜의 월을 영어로 반환

• DAYNAME(날짜) : 날짜의 요일을 영어로 반환
• DAYOFMONTH(날짜) : 날짜의 일 반환
• DAYOFWEEK(날짜) : 날짜의 요일을 숫자로 반환
  *일요일 : 1 ~ 토요일 7
• WEEK(날짜) : 날짜가 해당 연도에 몇 번째 주인지 반환

• HOUR(시간) : 시간의 시 반환
• MINUTE(시간) : 시간의 분 반환
• SECOND(시간) : 시간의 초 반환

• DATE_FORMAT(날짜/시간, 형식) : 날짜/시간의 형식을 형식으로 바꿔 반환
• DATEDIFF(날짜1, 날짜2) : 날짜1과 날짜2의 차이 반환 (날짜1 - 날짜2)
• TIMEDIFF(시간1, 시간2) : 시간1과 시간2의 차이 반환 (시간1 - 시간2)

SELECT [칼럼 이름] FROM [테이블 이름] WHERE 조건식;

EX) SELECT name FROM table WHERE age = 28;

비교 연산자

= 

!=

> 

>=

<

<=

논리 연산자

AND

OR

NOT

기타 주요 연산자

[칼럼 이름] BETWEEN A AND B 는 A <= [칼럼 이름] AND [칼럼 이름] <= B와 동일

[칼럼 이름] IN (A, B)는 [칼럼 이름] = A OR [칼럼 이름] = B와 동일

문자형 데이터 다루기

LIKE : 와일드카드(%, _)를 활용하여 특정 문자열이 포함된 데이터 선택

• % : 0개 이상의 문자
• _ : 1개의 문자

EX) ~ WHERE name LIKE ‘a%’ : a로 시작하는 문자열

~WHERE name LIKE ‘%a’ : a로 끝나는 문자열

~ WHERE name LIKE ‘%a%’ : a를 포함하는 문자열

NULL 데이터 가져오기

데이터 값이 존재하지 않는다는 표현 0이나 공백이 아니라 알 수 없는 값을 의미함

[칼럼 이름] IS NULL : NULL인 데이터

[칼럼 이름] IS NOT NULL : NULL이 아닌 데이터

💡 [칼럼 이름] = NULL이나 [칼럼 이름] != NULL은 불가

원하는 데이터 가져오기

SELECT [칼럼1 이름], [칼럼2 이름], [칼럼3 이름] FROM [데이터베이스 이름].[테이블 이름];

SELECT * FROM [데이터베이스 이름].[테이블 이름];

별명 붙이기

테이블 내 실제 칼럼 이름은 변하지 X 별명은 쿼리 내에서만 유효함

SELECT [칼럼 이름] AS [칼럼 별명] FROM [테이블 이름];

EX) SELECT name AS 네임 FROM table;

*workbench에서 정확하게 인식할 수 있도록 별명 이름에 가급적이면 따옴표로 감싸주는게 좋음!

데이터 일부만 가져오기

LIMIT [로우 수]

쿼리의 가장 마지막에 위치함

EX) SELECT * FROM tables.first_table LIMIT 2;

중복 제거하기

DISTINCT [칼럼 이름]

EX) SELECT DISTINCT name FROM tables.first_table;

mysql 데이터 타입 : 숫자형(정수형, 실수형), 문자형, 날짜형

데이터를 저장하기 전에 저장 데이터 타입을 정해두어야 한다

• 정수형 : INT
• 실수형 : FLOAT
• 문자형 :
  • 고정형 길이 문자형 : CHAR(n)
  • 변동 길이 문자형 : VARCHAR(n)

mysql에서 데이터 다루는 법은 엑셀과 굉장히 유사함

숫자형 데이터 간 연산(사칙연산) 가능

문자형 데이터는 “”, ‘’ 로 명시

데이터 타입 간 변환 가능

테이블 : 데이터베이스에서 데이터를 형태를 정해 모아 놓은 저장 공간 = 행과 열로 이루어진 데이터 표

<데이터베이스, 테이블, 칼럼 이름 정하기 규칙>

1. 문자, 숫자, _ 사용
2. 이름에 쓰이는 문자는 주로 영문 소문자
3. 예약어 사용 불가
4. 단어와 단어 사이는 빈칸 대신 _
5. 숫자, _로 시작 불가 + 문자로 시작
6. 데이터베이스 이름 중복 불가

CREATE DATABASE [데이터베이스 이름];

SHOW DATABASES;

USE [데이터베이스 이름];

CREATE TABLE [테이블 이름](

[칼럼1 이름] [데이터1 타입],

[칼럼2 이름] [데이터2 타입],

[칼럼3 이름] [데이터3 타입]

);

*데이터베이스 내 테이블을 만드려면

1) USE [데이터베이스 이름]; 쓰고 CREATE TABLE [테이블 이름](~); 하거나

2) CREATE TABLE [데이터베이스 이름].[테이블 이름](~); 써야함

ALTER TABLE [테이블 이름] RENAME [새로운 테이블 이름];

ALTER TABLE [테이블 이름] ADD COLUMNS [칼럼 이름] [데이터 타입];

ALTER TABLE [테이블 이름] MODIFY COLUMN [칼럼 이름] [새로운 데이터 타입];

ALTER TABLE [테이블 이름] CHANGE COLUMN [칼럼 이름] [새로운 칼럼 이름] [새로운 데이터 타입];

ALTER [테이블 이름] DROP COLUMN [칼럼 이름];

DROP DATABASE [데이터베이스 이름];

• 테이블 아예 지우기 DROP TABLE [테이블 이름];
• 테이블 값만 지우기 TRUNCATE TABLE [테이블 이름];

INSERT INTO [테이블 이름] ([칼럼1 이름] [칼럼2 이름] [칼럼3 이름]) VALUES ([칼럼1 값] [칼럼2 값] [칼럼3 값]);

INSERT INTO [테이블 이름] ([칼럼1 이름] [칼럼2 이름] [칼럼3 이름])

VALUES ([칼럼1-1값], [칼럼2-1값], [칼럼3-1값]),

([칼럼1-2값], [칼럼2-2값], [칼럼3-2값]),

([칼럼1-3값], [칼럼2-3값], [칼럼3-3값]);

DELETE FROM [테이블 이름] WHERE [조건 값];

UPDATE [테이블 이름] SET [칼럼 이름] = [새 값] WHERE [조건 값];