[프로그래머스] SQL 쿼리문 정리

[프로그래머스] SQL 쿼리문 정리

 

#SQL 쿼리

#INSERT
INSERT INTO member(ID, Name,Date, Num)
VALUES(1, '장호연', '2022-02-11', 99);

#UPDATE
UPDATE member
SET Num = 211
WHERE Name = '김철수';

#DELETE
DELETE FROM member
WHERE Name = '박해수';

#COLUMN 추가
ALTER TABLE member ADD mbti VARCHAR(20);

# MAX
SELECT MAX(DATETIME)
FROM ANIMAL_INS;

# NULL
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
WHERE NAME IS NOT NULL;

# STRING
WHERE INTAKE_CONDITION = 'Sick';
WHERE INTAKE_CONDITION != 'Aged';

# ASC DESC 중복
ORDER BY NAME ASC, DATETIME DESC;

# LIMIT (TOP N)
SELECT NAME
FROM ANIMAL_INS ORDER BY DATETIME
LIMIT 1;

# COUNT & GROUP BY
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) 
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE;

# HAVING
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL
GROUP BY NAME 
HAVING COUNT(NAME) > 1 
ORDER BY NAME;

# LIKE
SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE ANIMAL_TYPE = "Dog" AND NAME LIKE "%EL%" 
ORDER BY NAME ASC;

#IF
SELECT animal_type, if (name IS NULL, 'No name', name), sex_upon_intake
from animal_ins

#Datetime to date
SELECT animal_id, name, date_format(datetime, '%Y-%m-%d') as 날짜
from animal_ins
order by animal_id;

#SUBSTRING (문자열, 첫째자리, 얼마만큼)
SELECT substring(product_code, 1,2) as category, count(*) as products
from product
group by substring(product_code, 1,2)
order by category;

#CASE WHEN THEN END AS
SELECT board_id, writer_id, title, price, case status when 'SALE' then '판매중'
when 'DONE' then '거래완료' when 'RESERVED' then '예약중'END as status
from used_goods_board
where DATE(created_date) = '2022-10-05'
order by board_id desc;

#평균 AVG()

mysql 날짜 캐스팅 : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

 

* 입양시각 구하기 문제 HOUR(), GROUP BY HOUR

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR 
ORDER BY HOUR;