MySQL

1. GROUP BY

#1. GROUP BY
SELECT name, COUNT(Country) as x1, MAX(City) as x2
FROM ANIMAL_INS
GROUP BY name
HAVING x1 > 50 and name like "A%"
ORDER BY x1 desc
-- GROUP BY when using aggregate functions (count,max,min,etc)
-- CAN ONLY GROUP BY ON 'name' column (x1, x2 are already aggregate)

2. 중복제거 and ignore NULL

SELECT count(distinct NAME)
FROM ANIMAL_INS
WHERE NAME is not NULL

3. 여러기준 정렬 (COL1 기준 정렬, THEN COL2 기준 정렬 (COL1 = COL1 일때) 

SELECT COL1, COL2
FROM TABLE
ORDER BY COL1 ASC, COL2 DESC

4. 변수 설정 

SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(HOUR(DATETIME))
    FROM ANIMAL_OUTS
    WHERE HOUR(DATETIME)=@HOUR) AS COUNT
    FROM ANIMAL_OUTS
WHERE @HOUR < 23;

5. JOIN 

JOIN 예제

SELECT B.animal_id, B.name
FROM ANIMAL_INS A
RIGHT JOIN ANIMAL_OUTS B
      On A.animal_id = B.animal_id
WHERE A.animal_id is NULL
ORDER BY B.animal_id

6. CASE 

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

7. 시간처리 

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

- HOUR(DATETIME), DATE(DATETIME), ...

8. NULL처리 

#8A. NULL 처리
SELECT ANIMAL_TYPE, IFNULL(NAME,"No name"), SEX_UPON_INTAKE
FROM ANIMAL_INS
-- CASE1: NAME != NULL --> return NAME
-- CASE2: NAME == NULL --> return "No name"
-- ex) equivalents --> 1. ORACLE = NVL      2. MSSQL ISNULL 

#8B.
SELECT COALESCE(Column명1, Column명2, Column명3, Column명4) FROM 테이블명
--EX) SELECT COALESCE(NAME, "No name") --> if NAME == NULL,
      then RETURN "No name" since "No name" is first NON-NULL

9. 상위 n 개 ROWS

SELECT NAME
FROM ANIMAL_INS 
ORDER BY DATETIME
LIMIT 1;

10. SUBQUERIES (오라클 첨부 이지만 SQL 해당) 

https://snowple.tistory.com/360

https://victorydntmd.tistory.com/139

11. 순위함수 [ROW_NUMBER, RANK, DENSE_RANK]

SELECT 성적,
       학생,
       ROW_NUMBER() OVER (ORDER BY 성적 desc) as 결과1, --1. SAME rank --> DIFFERENT rank
       RANK()       OVER (ORDER BY 성적 desc) as 결과2, --2. SAME rank --> SAME rank --> 다음 rank 건너뜀   (2,2,4)
       DENSE_RANK() OVER (ORDER BY 성적 desc) as 결과3  --3. SAME rank --> SAME rank --> 다음 rank 안건너뜀  (2,2,3)
FROM   GRADE_TABLE

 

12. STRING 함수

--------------------------------------------------------------------------

* WHERE NAME LIKE '%라면%'
* WHERE NAME LIKE '_라면_'
* WHERE NAME LIKE '__라면%'

1.  CONCAT('LearnSQL is good', ' and great', ' and fantastic!')
-- OUTPUT: LearnSQL is good and great and fantastic!

2. CONCAT  (name, ' was admitted to St. Ann's Hospital on ', date, ' with ', illness)
-- OUTPUT: John Doe was admitted to St. Ann's Hospital on 2021-01-14 with flu.

3.  REPLACE( 'LearnSQL is good!', 'good', 'great') -- REPLACE 'GOOD' WITH 'GREAT'
-- OUTPUT: LearnSQL is great!

4. SUBSTR('LearnSQL', 6, 3) -- START INDEX 6 FOR 3 LETTERS
-- OUTPUT: SQL

5. LEFT('Hello World', 5)  -- TAKE 5 char from LEFT
-- OUTPUT: Hello
6. RIGHT('Hello World', 5) -- TAKE 5 char from RIGHT
-- OUTPUT: World
7. LOWER('Hello World') -- HELLO
   UPPER('Hello World') -- hello

8. TRIM -- 공백제거
TRIM ('   Sample   ') -- OUTPUT: 'Sample'      --> (좌우 제거)
LTRIM('   Sample   ') -- OUTPUT: 'Sample   '   --> (좌 제거)
RTRIM('   Sample   ') -- OUTPUT: '   Sample'   --> (우 제거)
TRIM('#' FROM '#SQL Tutorial!')  -- OUTPUT: SQL Tutorial!
TRIM('#!' FROM '#SQL Tutorial!') -- OUTPUT: SQL Tutorial

 

13. DATETIME 날짜 처리 함수

DATE_FORMAT(NOW(), '%Y-%m-%d %T')  -- OUTPUT: 2021-03-30 12:00:00
DATE_FORMAT(NOW(), '%Y%m%d')       -- OUTPUT: 20210330
DATE_FORMAT(NOW(), '%Y%m%d%H%i%S') -- OUTPUT: 20210330100000
-- %Y(4자리 연도), %y(2자리 연도), %M(Jan~Dec), %m(월), %d(일), %H(24시간), %h(12시간), %i(분), %s(초) %p(AM/PM) %T (hh:mm:ss)


SELECT YEAR('1998-02-03'); --OUTPUT: 98 --> MONTH(TIME), WEEK(TIME), DAY(TIME), HOUR(TIME)...
months_between ('20/05/06' , '20/03/06')