SQL 문법 정리

프로그래머스 풀면서 나오는 SQL 문법 정리 ( ~ 2023.07.09)

& SQL 스터디 얄코 강의 정리

 

2023.07.09

 

- like 과 =

- CASE 와 IF문 이용

 

 

Lesson1. 

RDBMS : Relational Database Management System

Database : 한곳에 저장된 정보들을 원하는 어떤 곳에서든 사용할 수 있는것

특정 소프트웨어나 프로그램에 종속되지 않고 독립된 정보의 집합

-> 여기에 정보들을 집어넣고 원하는 걸 찾아 꺼내는 각종 기능들을 넣으면 DBMS

표 형태에 넣어줘서 데이터의 형식을 맞춰줌 -> SQL : 도메인 특화 언어

데이터 베이스라는 상자 안에 SQL이라는 버튼들을 제공해서 사용자들로 하여금

원하는대로 정보를 넣고 빼고 할 수 있게 해주는 것!

 

테이블을 분리! - 데이터들이 중복되지 않게 해주어 

'고유key'를 통해 테이블들을 JOIN 해줌

관계형 데이터 베이스 'RDBMS' - MySQL , Oracle...

 

Lesson2. SELECT 기초

* LIMIT : 원하는 만큼만 데이터 가져오기

# LIMIT {가져올 갯수}
SELECT * FROM Customers
LIMIT 10;

# LIMIT {건너뛸 갯수}, {가져올 갯수}
SELECT * FROM Customers
LIMIT 30, 10;

Lesson3. 각종 연산자들

- SQL에선 문자열과 숫자열을 더하면 숫자열을 0으로 인식해버림 but, 숫자로 구성된 문자열은 숫자로 자동인식

SELECT 'ABC' + 3; # 0 +3 = 3 
SELECT '1' + '002' * 3; # 1 + 2 + 3 = 6

-- 숫자로 구성된 문자열은 숫자로 자동인식

IS	양쪽이 모두 TRUE 또는 FALSE
IS NOT	한쪽은 TRUE, 한쪽은 FALSE

- %는 뒤에 글자수 상관없이 무언가가 온다, _(언더바)는 개수만큼 온다!

LIKE '... % ...'	0~N개 문자를 가진 패턴
LIKE '... _ ...'	_ 갯수만큼의 문자를 가진 패턴

Lesson4. 숫자와 문자열을 다루는 함수들
- 숫자

CEIL	올림
FLOOR	내림

GREATEST	(괄호 안에서) 가장 큰 값
LEAST	(괄호 안에서) 가장 작은 값

POW(A, B), POWER(A, B)	A를 B만큼 제곱
SQRT	제곱근

TRUNCATE(N, n)

N을 소숫점 n자리까지 선택

SELECT
  TRUNCATE(1234.5678, 1), # 1234.5
  TRUNCATE(1234.5678, 2), # 1234.56
  TRUNCATE(1234.5678, 3), # 1234.567
  TRUNCATE(1234.5678, -1), # 음수가 들어가면 숫자만큼 소수점 앞으로 0을 붙이게 됨 - 1230
  TRUNCATE(1234.5678, -2), # 1200
  TRUNCATE(1234.5678, -3); # 1000

- 문자열

UCASE, UPPER	모두 대문자로
LCASE, LOWER	모두 소문자로

CONCAT(...)	괄호 안의 내용 이어붙임
CONCAT_WS(S, ...)	괄호 안의 내용 S로 이어붙임

SELECT CONCAT_WS('-', 2021, 8, 15, 'AM') # 2021-8-15-AM
SELECT
  CONCAT_WS(' ', FirstName, LastName) AS FullName     # Davollo
FROM Employees;

SUBSTR, SUBSTRING	주어진 값에 따라 문자열 자름
LEFT	왼쪽부터 N글자
RIGHT	오른쪽부터 N글자

SELECT
  SUBSTR('ABCDEFG', 3),   # 3번째부터 읽는다 - CDEFG
  SUBSTR('ABCDEFG', 3, 2),   # 3번째부터 2개 읽는다 - CD
  SUBSTR('ABCDEFG', -4),     # 끝에서 4번째부터 읽는다 - DEFG
  SUBSTR('ABCDEFG', -4, 2);   # DE

LENGTH	문자열의 바이트 길이
CHAR_LENGTH, CHARACTER_LEGNTH	문자열의 문자 길이

* SQL에선 CHAR_LENGTH를 써야 내가 원하는 한글 글자수를 알 수 있음

TRIM	양쪽 공백 제거
LTRIM	왼쪽 공백 제거
RTRIM	오른쪽 공백 제거

 

SELECT * FROM Categories
WHERE CategoryName = TRIM(' Beverages ')

LPAD(S, N, P)	S가 N글자가 될 때까지 P를 이어붙임
RPAD(S, N, P)	S가 N글자가 될 때까지 P를 이어붙임

SELECT
  LPAD('ABC', 5, '-'), # 5글자가 될 때까지 왼쪽에 이어붙이기 # --ABC
  RPAD('ABC', 5, '-'); # 5글자가 될 때까지 오른쪽에 이어붙이기 # ABC--

REPLACE(S, A, B)

S중 A를 B로 변경

SELECT
  REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹');

INSTR(S, s)

S중 s의 첫 위치 반환, 없을 시 0

SELECT
  INSTR('ABCDE', 'ABC'),     #1
  INSTR('ABCDE', 'BCDE'),    #2
  INSTR('ABCDE', 'C'),       #3
  INSTR('ABCDE', 'DE'),      #4
  INSTR('ABCDE', 'F');       #0 - 없으므로 0 반환


SELECT * FROM Customers
WHERE INSTR(CustomerName, ' ') BETWEEN 1 AND 6;  # 1에서 6사이에 있는 space를 찾고 싶다
-- < 6으로 하면? #

CAST(A AS T)	A를 T 자료형으로 변환
CONVERT(A, T)	A를 T 자료형으로 변환

SELECT
  '01' = '1',
  CAST('01' AS DECIMAL) = CAST('1' AS DECIMAL);

SELECT
  '01' = '1',
  CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);

Lesson4-2. 시간/날짜 관련 및 기타 함수들

CURRENT_DATE, CURDATE	현재 날짜 반환
CURRENT_TIME, CURTIME	현재 시간 반환
CURRENT_TIMESTAMP, NOW	현재 시간과 날짜 반환

WEEKDAY

주어진 DATETIME값의 요일값 반환(월요일: 0)

ADDDATE, DATE_ADD	시간/날짜 더하기
SUBDATE, DATE_SUB	시간/날짜 빼기

SELECT 
  ADDDATE('2021-06-20', INTERVAL 1 YEAR),
  ADDDATE('2021-06-20', INTERVAL -2 MONTH),
  ADDDATE('2021-06-20', INTERVAL 3 WEEK),
  ADDDATE('2021-06-20', INTERVAL -4 DAY),
  ADDDATE('2021-06-20', INTERVAL -5 MINUTE),
  ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);

DATE_FORMAT⭐⭐⭐

시간/날짜를 지정한 형식으로 반환

%Y	년도 4자리
%y	년도 2자리
%M	월 영문
%m	월 숫자
%D	일 영문(1st, 2nd, 3rd...)
%d, %e	일 숫자 (01 ~ 31)
%T	hh:mm:ss
%r	hh:mm:ss AM/PM
%H, %k	시 (~23)
%h, %l	시 (~12)
%i	분
%S, %s	초
%p	AM/PM

SELECT
  DATE_FORMAT(NOW(), '%M %D, %Y %T'),
  DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'),
  DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');

 

STR _ TO _ DATE(S, F)

S를 F형식으로 해석하여 시간/날짜 생성

* 기타

IFNULL(A, B)

A가 NULL일 시 B 출력

Lesson5. 조건에 따라 그룹으로 묶기

- GROUP BY, GROUP BY에 집계함수 사용

- WITH ROLLUP : 전체 집계값을 추가해줌! 총계 추가해주는 기능

SELECT
  Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;

* ROLL UP은 ORDER BY와 함께 사용할 수 없음!

- HAVING : 그룹화된 데이터 걸러내기

SELECT
  Country, COUNT(*) AS Count
FROM Suppliers
GROUP BY Country
HAVING Count >= 3;

* WHERE는 그룹하기 전 데이터, HAVING은 그룹 후 집계에 사용!

 

- DISTINCT : 중복된 값 제거

* GROUP BY와 달리 집계함수 사용x, GROUP BY와 달리 정렬하지 않으므로 더 빠름!

SELECT
  Country,
  COUNT(DISTINCT CITY) # 겹치지 않는 city가 몇개인지
FROM Customers
GROUP BY Country;

CHAPTER2-1. 서브쿼리 ⭐⭐⭐

1. 비상관 서브쿼리 - 서브쿼리와 본 쿼리가 독자적으로 실행되고 있다는 것

SELECT
  CategoryID, CategoryName, Description,
  (SELECT ProductName FROM Products WHERE ProductID = 1)  # Product_id가 1인 ProductName도 가져옴
FROM Categories;

-> 의미 있는 서브쿼리는 아님

# 조건문 안에 들어간 서브쿼리

SELECT * FROM Products
WHERE Price < (
  SELECT AVG(Price) FROM Products
);
# 서브쿼리가 조건문 안에 들어감

# 서브쿼리 문이 하나의 행만을 반환할 경우 -> =을 사용해주기

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID =
  (SELECT CategoryID FROM Products
  WHERE ProductName = 'Chais');

# 서브쿼리가 여러개의 행을 반환할 경우 -> IN을 사용해주기

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID IN
  (SELECT CategoryID FROM Products
  WHERE Price > 50);

~ ALL	서브쿼리의 모든 결과에 대해 ~하다
~ ANY	서브쿼리의 하나 이상의 결과에 대해 ~하다

SELECT * FROM Products
WHERE Price > ALL (
  SELECT Price FROM Products
  WHERE CategoryID = 2
);

 

2. 상관 서브쿼리 - 서브쿼리가 본 쿼리와 맞물림

- 테이블들의 별칭이 들어감

SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName FROM Categories C
    WHERE C.CategoryID = P.CategoryID
  ) AS CategoryName
FROM Products P;

# product에서 id와 name을 가져오는데
# category 테이블에서 카테고리 id가 product 테이블의 카테고리 id와 같은 것들을 가져와라!
# 정규화로 분리되었던 테이블들을 연결 - 이제 제품명과 한 테이블을 같이 볼 수 있음
# JOIN을 이용해서도 가능

SELECT
  CategoryID, CategoryName,
  (
    SELECT MAX(Price) FROM Products P
    WHERE P.CategoryID = C.CategoryID
  ) AS MaximumPrice,
  (
    SELECT AVG(Price) FROM Products P
    WHERE P.CategoryID = C.CategoryID
  ) AS AveragePrice
FROM Categories C;

# 각 카테고리에 속한 최고값과 평균값 가져오기

SELECT
  ProductID, ProductName, CategoryID, Price
  -- ,(SELECT AVG(Price) FROM Products P2
  -- WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
  SELECT AVG(Price) FROM Products P2
  WHERE P2.CategoryID = P1.CategoryID
);

# 같은 테이블에서 각 카테고리별 평균값보다 가격이 낮은 것들만 가져오기
# 주석처리 빼면 평균값도 같이

EXISTS / NOT EXISTS 연산자

SELECT
  CategoryID, CategoryName
  -- ,(SELECT MAX(P.Price) FROM Products P
  -- WHERE P.CategoryID = C.CategoryID
  -- ) AS MaxPrice
FROM Categories C
WHERE EXISTS (
  SELECT * FROM Products P
  WHERE P.CategoryID = C.CategoryID
  AND P.Price > 80
);
# 가격이 80이 넘는 애들만 가져오기
# 주석부분, 카테고리별 max price도 가져오기

CHAPTER2-2. JOIN ⭐⭐⭐⭐⭐

1. JOIN(INNER JOIN) - 양쪽 모두에 값이 있는 행 반환

SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 

-- ambiguous 주의! => 테이블 별칭 주의하기

# 여러개의 테이블도 JOIN이 가능하다!

SELECT 
  C.CategoryID, C.CategoryName, 
  P.ProductName, 
  O.OrderDate,
  D.Quantity
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
JOIN Orders O
  ON O.OrderID = D.OrderID;

SELECT 
  C.CategoryName,
  MIN(O.OrderDate) AS FirstOrder,
  MAX(O.OrderDate) AS LastOrder,
  SUM(D.Quantity) AS TotalQuantity
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
JOIN Orders O
  ON O.OrderID = D.OrderID
GROUP BY C.CategoryID;
# 통계량 사용

- 서브쿼리를 쓸 때보다 더 빠르게 계산이 가능하다

# SELF- JOIN (같은 테이블끼리 JOIN)

SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1 JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID;
# 각각의 employee 마다 그 다음 employee들이 누구인지 한행에서 알고 싶을 때!

-- 1번의 전, 마지막 번호의 다음은? : 1번은 이전 id가 없고, 마지막 번호는 그 다음 id가 없다
- 이 경우, INNER JOIN 사용하므로, 테이블에 없는 값은 가져오지 않ㄴ음!

2. LEFT/RIGHT OUTER JOIN - 외부조인

- 반대쪽에 데이터가 있든 없든, 선택된 방향에 있으면 출력!

 

3. CROSS JOIN - 조건 없이 모든 결과 반환

 

CHAPTER2-3. UNION 
- JOIN은 좌우로 합치는 거라면, UNION은 위 아래로 합침

UNION	중복을 제거한 집합
UNION ALL	중복을 제거하지 않은 집합

- 중복된 것까지 가져오려면 UNION ALL을 이용해야 한다!

교집합

SELECT CategoryID AS ID
FROM Categories C, Employees E
WHERE 
  C.CategoryID > 4
  AND E.EmployeeID % 2 = 0
  AND C.CategoryID = E.EmployeeID;

차집합

SELECT CategoryID AS ID
FROM Categories
WHERE 
  CategoryID > 4
  AND CategoryID NOT IN (
    SELECT EmployeeID
    FROM Employees
    WHERE EmployeeID % 2 = 0
  );
  # NOT IN -

대칭차집합

SELECT ID FROM (
  SELECT CategoryID AS ID FROM Categories
  WHERE CategoryID > 4
  UNION ALL
  SELECT EmployeeID AS ID FROM Employees
  WHERE EmployeeID % 2 = 0
) AS Temp 
GROUP BY ID HAVING COUNT(*) = 1;
# UNION ALL로 중복포함 합쳐줌 - 중복부분은 2개가 나오게 됨! 
# HABING 이용해서 1개 있는 것들만 걸러주기 - 대칭차집합

LESSON3. 데이터 조작하기

 

CREATE TABLE : 테이블 만들기

CREATE TABLE people (
  person_id INT,
  person_name VARCHAR(10),
  age TINYINT,
  birthday DATE
);

ALTER TABLE : 테이블 변경

-- 테이블명 변경 - friends로 이름 바꾸기
ALTER TABLE people RENAME TO  friends,
-- 컬럼 자료형 변경 - 이름은 똑같고, 자료형 바꾸기
CHANGE COLUMN person_id person_id TINYINT,
-- 컬럼명 변경 - 자료형 똑같고, 이름 바꾸기
CHANGE COLUMN person_name person_nickname VARCHAR(10), 
-- 컬럼 삭제 -DROP
DROP COLUMN birthday, 
-- 컬럼 추가 - ADD
ADD COLUMN is_married TINYINT AFTER age;

DROP TABLE : 테이블 삭제

DROP TABLE friends;

INSERT INTO : 데이터 삽입

-- 여러 행을 한 번에 입력 가능
INSERT INTO people
  (person_id, person_name, age, birthday)
  VALUES 
    (4, '존 스미스', 30, '1991-03-01'),
    (5, '루피 D. 몽키', 15, '2006-12-07'),
    (6, '황비홍', 24, '1997-10-30');

* 테이블 생성 시 제약 조건

AUTO_INCREMENT	새 행 생성시마다 자동으로 1씩 증가
PRIMARY KEY	중복 입력 불가, NULL(빈 값) 불가
UNIQUE	중복 입력 불가
NOT NULL	NULL(빈 값) 입력 불가
UNSIGNED	(숫자일시) 양수만 가능
DEFAULT	값 입력이 없을 시 기본값

PRIMARY KEY : 각 행들이 구분 될 수 있는 고유값, 인덱스 생성, 보통 AUTO INCREMENT와 함께 생성

 

* 자료형

1. 숫자 자료형

- 정수 

TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535
MEDIUMINT	3	-8,388,608 ~ 8,388,607	0 ~ 16,777,215
INT	4	-2,147,483,648 ~ 2,147,483,647	0 ~ 4,294,967,295
BIGINT	8	-2^63 ~ 2^63 - 1	0 ~ 2^64 - 1

- 고정 소수점 : 좁은 범위의 수 표현 가능, 정확한 값

DECIMAL( s, d )

실수 부분 총 자릿수( s ) & 소수 부분 자릿수 ( d )

s 최대 65

- 부동 소수점 : 넓은 범위의 수 표현 가능, 정확하지 않은 값

FLOAT	-3.402...E+38 ~ -1.175...E-38 , 0 , 1.175...E-38 ~ 3.402...E+38
DOUBLE	-1.797...E+308 ~ -2.225E-308 , 0 , 2.225...E-308 ~ 1.797...E+308

2. 문자 자료형

CHAR( s )	고정 사이즈 (남는 글자 스페이스로 채움)	s (고정값)	255
VARCHAR ( s )	가변 사이즈	실제 글자 수[최대 s] + 1 [글자수 정보]	65,535

- 검색시 CHAR가 더 빠름

- VARCHAR 길이값이 4보다 적으면 CHAR로 자동변환

TINYTEXT	255
TEXT	65,535
MEDIUMTEXT	16,777,215
LONGTEXT	4,294,967,295

3. 시간자료형

DATE	YYYY-MM-DD
TIME	HHH:MI:SS	HHH: -838 ~ 838까지의 시간
DATETIME	YYYY-MM-DD HH:MI:SS	입력된 시간을 그 값 자체로 저장
TIMESTAMP	YYYY-MM-DD HH:MI:SS	MySQL이 설치된 컴퓨터의 시간대를 기준으로 저장

1. DELETE : 주어진 조건의 행 삭제하기

DELETE FROM businesses
WHERE status = 'CLS';

DELETE FROM businesses; # WHERE문 안 쓰면 행 전체 삭제

- DELETE로 지웠을 때는 AUTO_INCREMENT값이 그 전 삭제한 거에서 이어짐...

- 마지막에 어떤 행이 있었는지 기억 

 

2. TRUNCATE : 삭제하면서 테이블 초기화

- TRUNCATE는 AUTO_INCREMENT값 안 이어짐

 

3. UPDATE : 주어진 조건의 행 수정하기

UPDATE menus
SET menu_name = '삼선짜장'
WHERE menu_id = 12;

UPDATE menus
SET menu_name = CONCAT('전통 ', menu_name)
WHERE fk_business_id IN (
  SELECT business_id 
  FROM sections S
  LEFT JOIN businesses B
    ON S.section_id = B.fk_section_id 
  WHERE section_name = '한식'
);

- UPDATE문도 WHERE문 안 넣으면 전부 다 바꿔버리니까 조심하기!!!

 

 

 

참고자료 : MySQL | 얄코 (yalco.kr)