SQL 문법 쫌쫌따리

1. 중복 제거하기

SELECT

SELECT email as Email
FROM Person
GROUP BY email
HAVING COUNT(email) > 1;

GROUP BY로 묶어주고 HAVING으로 카운트해준다

DELETE

DELETE T1
FROM Person as T1, Person as T2
WHERE T1.email = T2.email and T1.Id > T2.Id

2. in

SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM Employee as e JOIN Department as d
ON e.departmentId = d.id
WHERE (e.salary, e.departmentId) IN (SELECT MAX(salary), departmentId FROM Employee GROUP BY departmentId);

하나가 아니라 여러개를 구할 때는 in을 사용 ~!

3. 날짜 차이 구하기

TO_DAYS(DATE) - TO_DAYS(DATE)

예시)

SELECT w1.id as id
FROM Weather as w1, Weather as w2
WHERE TO_DAYS(w1.recordDate) - TO_DAYS(w2.recordDate) = 1 and w1.temperature > w2.temperature

4. 홀수 짝수

MOD 함수가 있음

예시)

SELECT id, movie, description, rating
FROM Cinema
WHERE description != "boring" and MOD(id, 2) = 1
ORDER BY rating DESC

5. 바꾸기

UPDATE Salary
SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END

6. DATETIME 을 DATE로

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, "%Y-%m-%d") as 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;