SQL

😊 ST_Distance_Sphere

두 지점 간의 구 형태의 지구 표면상의 직선 거리를 계산

😊 현재 시간

SELECT CURDATE(); == SELECT CURRENT_DATE();
SELECT CURTIME();				-- 09:51:23
SELECT CURRENT_TIMESTAMP();		-- 2024-10-29 09:51:48.0

SELECT DAY(CURDATE());
SELECT MONTH(CURDATE());
SELECT YEAR(CURDATE());

😊 날짜 더하기

DATE_ADD(NOW(), INTERVAL 1 DAY)
DATE_ADD(NOW(), INTERVAL 1 MONTH)
DATE_ADD(NOW(), INTERVAL 1 YEAR)

😊 날짜 빼기

DATE_SUB(NOW(), INTERVAL 1 DAY)
DATE_SUB(NOW(), INTERVAL 1 MONTH)
DATE_SUB(NOW(), INTERVAL 1 YEAR)

😊 CASE 문

SELECT 이름,
    CASE 
        WHEN 나이 < 18 THEN '미성년자'
        WHEN 나이 >= 18 AND 나이 < 65 THEN '성인'
        ELSE '노년층'
    END AS 연령대
FROM 사용자;

😊 문자열 -> 날짜 변환

SELECT STR_TO_DATE("20241024","%Y%m%d"); -- 2024-10-24 
SELECT STR_TO_DATE("2024/10/24","%Y/%m/%d"); -- 2024-10-24  
SELECT STR_TO_DATE("2024.10.24","%Y.%m.%d"); -- 2024-10-24 
SELECT STR_TO_DATE("2024 10 24","%Y%m%d"); -- SELECT STR_TO_DATE("2024 10 24","%Y %m %d"); 도 가능 
SELECT STR_TO_DATE("2024a10a24","%Ya%ma%d"); -- 2024-10-24

😊 날짜 형식 변환

SELECT DATE_FORMAT("2024-10-24","%Y년"); -- 2024년
SELECT DATE_FORMAT("2024-10-24","%y년"); -- 24년
SELECT DATE_FORMAT("2024-10-24","%Y년%M"); -- 2024년October
SELECT DATE_FORMAT("2024-10-24","%Y년%m월"); -- 2024년10월
SELECT DATE_FORMAT("2024-10-24","%Y년%m월%D"); -- 2024년10월24th
SELECT DATE_FORMAT("2024-10-24","%Y년%m월%d일"); -- 2024년10월24일
SELECT DATE_FORMAT("2024-10-24 22:16:58","%Y년%m월%d일%H시"); -- 2024년10월24일22시
SELECT DATE_FORMAT("2024-10-24 22:16:58","%Y년%m월%d일%h %p"); -- 2024년10월24일10 PM
SELECT DATE_FORMAT("2024-10-24 22:16:58","%Y년%m월%d일%H시%i분"); -- 2024년10월24일22시16분
SELECT DATE_FORMAT("2024-10-24 22:16:58","%Y년%m월%d일%H시%i분%s초"); -- 2024년10월24일22시16분58초

😊 DML

ALTER TABLE wiz_loan_inquiry ADD COLUMN number_for_admin varchar(50);
ALTER TABLE wiz_loan_inquiry CHANGE number_for_admin number_for varchar(60);
ALTER TABLE wiz_loan_inquiry CHANGE number_for number_for_admin varchar(70);
ALTER TABLE wiz_loan_inquiry MODIFY number_for_admin varchar(50);
ALTER TABLE wiz_loan_inquiry DROP COLUMN number_for_admin;

😊 문자열 추출

SELECT SUBSTR('010-1234-5678',1,3) AS str; -- 010
SELECT SUBSTR('010-1234-5678',5,4) AS str; -- 1234
SELECT SUBSTR('010-1234-5678',-4) AS str; -- 5678

😊 문자열 비교

SELECT STRCMP("안녕","Hello"); -- 1
SELECT STRCMP("안녕",'안녕'); -- 0

😊 RANK , DESE_RANK

SELECT Name,RANK() OVER(ORDER BY population DESC) p
FROM city;

😊 EXISTS

where 과 having 에 사용

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

😊 집계함수

SELECT 
  CountryCode, 
  CEIL(AVG(population)) AS average_population,
  RANK() OVER(ORDER BY CEIL(AVG(population)) DESC)
FROM city
GROUP BY CountryCode
ORDER BY average_population DESC;