1. SHOW : table 조회 시 사용
SHOW tables;
2. SELECT : 얻고자 하는 필드
SELECT * FROM orders;
3. FROM : 찾고자 하는 필드가 속한 테이블
SELECT order_no, created_at FROM orders;
4. WHERE : 조건(어디에서)
SELECT * FROM point_users WHERE point > 5000;
SELECT * FROM orders WHERE payment_method = 'CARD';
SELECT * FROM orders WHERE payment_method != 'kakaopay';
5. BETWEEN a AND b : 범위(a~b)
SELECT * FROM point_users WHERE point BETWEEN 20000 AND 30000;
SELECT * FROM orders WHERE created_at between ‘2020-07-13’ AND ‘2020-07-15’;
6. AND : 2개 이상의 조건
SELECT * FROM orders WHERE course_title = ‘웹개발 종합반’ AND payment_method = ‘CARD’;
7. IN : 조건 리스트 중 해당 하는 데이터가 있는지
SELECT * FROM checkins WHERE week IN (1, 3)
8. LIKE : 문자열 검색(a% : a로 시작하는, %b : b로 끝나는, a%b : a로 시작하고 b로 끝나는)
SELECT * FROM users WHERE name LIKE ‘황%’;
SELECT * FROM users WHERE email LIKE ‘%daum.net’;
SELECT * FROM users WHERE email LIKE ‘a%t’;
SELECT * FROM users WHERE email LIKE ’s%com’ AND name LIKE ‘이%’;
7. LIMIT : 개수 제한(n개만)
SELECT * FROM orders WHERE payment_method = ‘kakaopay’ LIMIT 5;
8. DISTINCT : 중복제거
SELECT DISTINCT(payment_method) FROM orders;
9. COUNT : 해당 하는 데이터 개수 세기
SELECT COUNT(*) FROM orders where payment_method = ‘kakaopay’;
SELECT COUNT(DISTINCT(name)) FROM users;
10. GROUP BY : 동일한 범주의 데이터 집합
SELECT name, COUNT(*) FROM users GROUP BY name
11. ORDER BY : 데이터 정렬 (숫자, 문자, date 모두 가능)
- 오름차순(ORDER BY 정렬기준 ASC : *ASC : ascending의 약자, 생략 가능)
SELECT * FROM checkins ORDER BY likes;
SELECT name, COUNT(*) FROM users GROUP BY name ORDER BY COUNT(*);
- 내림차순(ORDER BY 정렬기준 DESC : *DESC : descending의 약자)
SELECT * FROM checkins ORDER BY likes DESC;
SELECT name, COUNT(*) FROM users GROUP BY name ORDER BY COUNT(*) DESC;
12. GROUP BY + WHERE 함께 사용하기
SELECT payment_method, COUNT(*) WHERE course_title = ‘웹개발 종합반’ GROUP BY payment_method ORDER BY count(*) DESC;
13. MIN(a), MAX(a) : 최솟값, 최댓값(a : 해당 필드의 최솟값, 최댓값)
SELECT week, MIN(likes) FROM checkins GROUP BY week;
SELECT week, MAX(likes) FROM checkins GROUP BY week;
14. AVG(a) : 평균값(a : a 필드의 평균)
SELECT week, AVG(likes) FROM checkins GROUP BY week;
15. ROUND(a, n) : 반올림 (a : 대상 수, n : n번째 소숫점으로 반올림)
SELECT week, ROUND(AVG(likes), 2) FROM checkins GROUP BY week;
16. SUM(a) : 합계(a : a 필드의 합계)
SELECT week, SUM(likes) FROM checkins GROUP BY week;
17. Alias(AS) : 별칭 기능
SELECT * FROM orders o WHERE o.course_title = ‘앱개발 종합반’;
SELECT payment_method, COUNT(*) AS cnt FROM orders o WHERE o.course_title = ‘앱개발 종합반’ GROUP BY payment_method;
