잘 사용하지 않았던 것만 첨가하여 기록하였음.
SELECT 기초
1. Limit
SELECT * FROM TableName
LIMIT 30,10;다음은 TableName의 테이블에서 처음 30개를 건너뛰고, 10개를 가져온다.
2. 연산
1. 연산결과 저장
SELECT 10 - 3 AS DIFF;하게 된다면,
2. 연산결과 저장 및 어떻게 나오는지 살펴보기
SELECT 10 - 7, 'HI';
3. 문자가 포함된 문자열 연산에서 문자열은 0이 된다.
SELECT 'GOAT' + 7;
4. 숫자가 포함된 문자열 연산에서 문자열은 곧 숫자이다.
SELECT '001' * 3 + '040';
SELECT '04A' + 3;
5. 칼럼에 포함된 원소들의 값들을 연산하여 데이터 가져오기
SELECT OrderID, ProductID, OrderID + ProductID AS SUM
FROM OrderDetails;
6. 참/거짓 연산자
SELECT TRUE, FALSE;
이를 이용하여 WHERE 조건문과 함께하여 사용한다. (WHERE 참?) (WHERE 거짓?)
SELECT * FROM Customers WHERE TRUE;-> 모든 Customers 정보들을 가져옴.
7. MySQL은 대소문자 구분을 하지 않는다.
SELECT 'A' = 'a';
8. IN 연산자 (원소가 포함되어 있는가?)
IN ( ) 괄호 안에 값이 있을 때 True를, 없으면 False를 반환
1) 숫자
SELECT 100 IN (1,10,100);
2) 문자
- 안되는경우 !! => LIKE 연산자로 해결한다.
SELECT '천재' IN ("염소는 천재다");False를 반환한다.
- 되는 경우 !!
SELECT '천재' IN ('염소','천재','이다');
9. LIKE 연산자 (문자가 문자열에 포함되어 있는가?)
- "%는 0~n개 문자가 앞 또는 뒤에 포함될 수 있다." 를 의미한다.
- "는 '' 개수만큼 문자가 포함된다." 를 의미한다.
SELECT "염소는 천재다" LIKE "%천재%";
SELECT "cheonjae" LIKE "_____jae";
한글로 할 때는 안되는 경우가 있으니 조심
3. 숫자와 문자열을 다루는 함수들
숫자
1. GREATEST , LEAST
- GREATEST : 괄호 안에서 가장 큰 값
- LEAST : 괄호 안에서 가장 작은 값
MIN, MAX와는 다름 !!
2. MIN , MAX
열을 기준으로 MIN , MAX 값을 반환
3. TRUNCATE
소수점 이하는 버리고, 정수부분은 0으로 채워 넣는 함수 (마찬가지로 버리는 거긴 함)
SELECT
TRUNCATE(1234.5678,3),
TRUNCATE(1234.5678,2),
TRUNCATE(1234.5678,1),
TRUNCATE(1234.5678,0),
TRUNCATE(1234.5678,-1),
TRUNCATE(1234.5678,-2);
문자열
1. UPPER , LOWER
대문자와, 소문자로 바꾸는 함수
2. CONCAT / CONCAT-WS
문자열을 이어 붙이는 함수 / 이 때, 숫자 또한 이어 붙여짐
- CONCAT_WS
해당 함수는 이어 붙이는 사이사이에 문자열을 삽입함 / 이 때 숫자도 문자열처럼 사용 가능 (파이썬의 join과 유사)
SELECT CONCAT_WS('-',1997,06,06,'BIRTHDAY');
3. SUBSTR, LEFT ,RIGHT
SQL 문자들의 인덱스는 1부터 시작함에 유의 하자
- SUBSTR : 해당 인덱스(첫번째 인자)부터 몇개 (두번째 인자)까지 가져옴
- LEFT : 왼쪽 문자부터 몇개 가져옴
- RIGHT : 오른쪽 문자부터 몇개 가져옴
4. TRIM, LTRIM, RTRIM
문자열의 공백을 제거
5. LPAD, RPAD
- LPAD : 왼쪽에 몇자리(두번째인자)가 될 때 까지 문자(세번째인자)를 첫번째인자 문자열에 추가함
- RPAD : 오른쪽에 몇자리(두번째인자)가 될 때 까지 문자(세번째인자)를 첫번째인자 문자열에 추가함
SELECT LPAD('HI',15,'GOAT'),
RPAD('HI',15,'GOAT');
6. REPLACE
SELECT REPLACE('염소는 바보다. 바보맞지? 바보다.','바보','천재');
7. INSTR
문자열(첫번째인자)중 찾으려는 문자(두번째인자)가 포함된 인덱스를 반환
SELECT INSTR('ABCDE','B'),
INSTR('brave goat','goat');
8. 자료형 변환 함수
SELECT
'01' = '1',
CONVERT('01',DECIMAL) = CONVERT('1',DECIMAL);
4. 시간과 날짜를 다루는 함수
1. 현재 시간과 날짜를 다루는 함수
SELECT
CURDATE(), --현재 날짜
CURTIME(), --현재 시간
NOW(); --현재 시간과 날짜
2. 문자열에 따라 날짜로 바꿔주는 함수
- DATE : 날짜로 바꾸기
- TIME : 시간으로 바꾸기
SELECT
'2023-3-21' = '2023-03-21',
DATE('2023-3-21') = DATE('2023-03-21'),
'1:2:3' = '01:02:03',
TIME('1:2:3') = TIME('01:02:03');
주의해야할 사항
SELECT
DATE('2023-03-21 1:2:3') = TIME('2023-03-21 01:02:03');해당 쿼리는 다른 0을 반환한다.
이유는 DATE는 날짜정보로만, TIME은 시간정보로만 변환해주기 때문이다.
다시말해 '2022-3-21' = '1:2:3' 이 되는 것이다.
3. 파싱후 날짜 정보 반환 함수
=> 보면 앎.
SELECT
OrderDate,
YEAR(OrderDate) AS YEAR,
MONTHNAME(OrderDate) AS MONTHNAME,
MONTH(OrderDate) AS MONTH,
WEEKDAY(OrderDate) AS WEEKDAY,
DAYNAME(OrderDate) AS DAYNAME,
DAY(OrderDate) AS DAY
FROM Orders;- 여기서 WEEKDAY는 월요일은 0으로 되어 있으며, 요일별 숫자를 반환해주는 함수이다.
4. ADDDATE, SUBDATE 시간,날짜 연산함수
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);
5. 시간, 날짜 차이를 구하는 함수
- DATE_DIFF : 두 날짜/시간 일수 차
- TIME_DIFF : 두 날짜/시간 시간 차
SELECT
OrderDate,
NOW(),
DATEDIFF(OrderDate, NOW())
FROM Orders;
6. 월의 마지막 날짜를 구하는 함수
LAST_DAY
7. 시간/날짜를 지정한 형식으로 변환하는 함수
| 형식 | 설명 |
|---|---|
| %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초');
8. 문자열을 날짜 형식으로 해석후 날짜 생성
STR_TO_DATE
SELECT
DATEDIFF(
STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
),
TIMEDIFF(
STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
);
5. 기타 함수
1. IF 함수
IF(조건,참,거짓) => 조건이 참이라면 참 실행, 거짓이라면 거짓 실행
SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.');
2. CASE 함수
IF보다 복잡할 때 사용
SELECT
CASE
WHEN -1 > 0 THEN '-1은 양수다.'
WHEN -1 = 0 THEN '-1은 0이다.'
ELSE '-1은 음수다.'
END;3. IFNULL 함수
IFNUL(A,B) => A가 NULL시 B출력
SELECT
IFNULL('A','B'),
IFNULL(NULL,'NULL입니다');
6. GROUP BY
1. WITH ROLLUP
ROLLUP은 총 합계를 나타냄
ORDER BY와 절대 함께 사용할 수 없다.
SELECT
Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;다음 SQL문 생각해볼 것 (DISTINCT와 더불어)
SELECT
Country,
COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;SELECT 심화
1. 서브쿼리
1. ALL / ANY (비상관 서브쿼리)
- 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;3. 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
);P 테이블과 C테이블에서 같은 카테고리 ID를 가지면서, 가격이 80원이 넘어가는 row에서 ID와 NAME을 가져온다.
2. JOIN
1. JOIN ( = INNER JOIN )
2. SELF 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;
-- 1번의 전, 마지막 번호의 다음은? (INNER JOIN의 특징)3. LEFT (OUTER) JOIN
오른쪽 테이블이 비어있더라도 왼쪽이 존재하면 가져온다.
4. CROSS JOIN
모든 행 정보를 가져온다. (A 테이블 행의 수) * (B 테이블 행의 수)
3. UNION
0. JOIN과의 차이점
JOIN은 테이블의 좌 우로 붙여나간다면,
UNION은 테이블의 위 아래로 붙여나간다.
1. UNION
중복을 제거한 집합
2. UNION ALL
중복을 제거하지 않은 집합
기록하고, 공유합시다