1.select from
*은 전체
2.정렬할때 order by
ORDER BY 컬럼명 ASC 하면 그 컬럼명 기준 오름차순
ORDER BY 컬럼명 DESC 하면 그 컬럼명 기준 내림차순
SELECT * FROM product ORDER BY 3 DESC
-ORDER BY 컬럼명 대신 ORDER BY 몇번째컬럼 인지를 적어도 정렬해준다.
3.where 로 데이터 필터링
SELECT 컬럼명 FROM 테이블명 WHERE 조건식
SELECT * FROM product
WHERE 가격 BETWEEN 5000 AND 8000
-가격이 5000이상 8000이하 이런걸 필터링하고 싶으면 컬럼명 BETWEEN X AND X 쓰면 됨.
4.where로 조건식 여러개하고싶으면
-and, or, not, in, 부등호 등등을 쓰면 됨.
SELECT * FROM product
WHERE 카테고리 = '가구' AND 가격 = 5000;
SELECT * FROM product
WHERE 카테고리 = '가구' OR 가격 = 5000;
SELECT * FROM product
WHERE NOT 카테고리 = '가구'
SELECT * FROM product
WHERE (카테고리 = '가구' OR 카테고리 = '옷') AND 가격 = 5000
SELECT * FROM product
WHERE 카테고리 IN ('신발', '가전, '식품')
(참고)
- OR 여러개를 IN ()으로 축약할 수 있으면 하는게 좋습니다. 그게 처리속도가 대부분 더 빠름
- IN () 쓰면 괄호 안에 SELECT 또 사용할 수 있는데 서브쿼리라고 합니다.
결론 :
1) SELECT FROM 뒤에 WHERE 조건식을 붙여서 필터링할 수 있고
2) 조건식란엔 > / < / = / != / >= / <= 전부 이용가능하고
3) 조건식 여러개가 필요하면 AND OR 이런걸로 이어붙일 수 있고
4) 괄호로 AND OR 사용한 부분을 묶을 수도 있고
5) OR 조건식 여러개 필요하면 IN () 사용해도 될 때가 있습니다.
5.검색할때 like, %, _로
‘%’는 wild card, 아무문자라는 뜻.
%소파 - 소파로 끝나는단어
%소파% - 소파가 들어가는 단어
소파% - 소파로 시작하는 단어
‘_’도 아무글자라는 뜻, 근데 한글자라는 뜻.
-%문자앞에쓰면 성능이슈가 있을수있다 index활용을 못할수있음
-문자자료가 담긴 칼럼에만 쓸수있음 char()칼럼에 쓰려면 주의해야함, char칼럼은 공백도 문자취급을 한다.
-
not 칼럼명 like, 칼럼명 not like 둘다 되는거같은데 전자가 나아서 전자를 쓰는거겠지?
-
6.MIN, MAX, AVG, SUM 집계함수로 통계, SQL의 집계함수 (aggregate function)
집계함수들은 한칼럼에 주로 사용, 여러개 쓰는건 상관없음
SELECT 사용금액 FROM card
SELECT MAX(사용금액) FROM card
SELECT MIN(사용금액) FROM card
SELECT AVG(연체횟수) FROM card
SELECT SUM(사용금액) FROM card
SELECT COUNT(사용금액) FROM card
응용1. 컬럼명을 바꾸고 싶으면 AS
SELECT MAX(사용금액) AS 최대사용금액 FROM card
컬럼명 뒤에 AS 원하는단어 = 컬럼명을 치환해줌 전문용어로 alias
주의할점 - 실제있는 컬럼명 말고 다른 단어를 작명해주는게 좋음.
응용2. 당연히 필터링 후에 통계낼 수도 있음
ex) 고객등급이 vip인 사람의 평균 사용금액
SELECT 사용금액 FROM card WHERE 고객등급 = 'vip'
-필터링한 결과에서 통계를 낼수있다
-전체를 가지고 통계내는 것 보다 특정 그룹의 통계를 내는게 대부분 더 의미찾기가 쉽다.
응용3. 참고로 DISTINCT도 사용가능합니다.
각 행에 있는 값들 중에 유니크한 값만 골라서 필터링 해줌.
SELECT DISTINCT 연체횟수 FROM card
SELECT AVG(DISTINCT 연체횟수) FROM card
-DISTINCT로 출력한 결과를 MIN MAX AVG SUM COUNT로 통계낼 수도 있음.
(참고) MAX, MIN 대신 LIMIT 쓰는 경우도 있음
데이터가 너무 많거나 해서 일부 환경에선 MAX()를 쓰는게 너무 오래걸릴 수 있음.
SELECT MAX(사용금액) FROM card;
SELECT * FROM card ORDER BY 사용금액 DESC;
둘다 최대금액은 결국 찾아낼 수 있음.
SELECT * FROM card ORDER BY 사용금액 DESC LIMIT 1;
-출력되는 행의 갯수를 제한을 두고 싶으면 맨 뒤에 LIMIT 행갯수 넣으면 됨.
Q1. 최대 결제횟수와 최소 결제횟수를 출력해보기.
SELECT MAX(결제횟수), MIN(결제횟수) FROM card
-select뒤에 칼럼 2개이상 사용가능
Q2. 고객등급이 vip인 사람들의 '평균 결제횟수'와 고객등급이 vip인 사람들의 '사용금액 총 합계'
SELECT AVG(결제횟수), SUM(사용금액) FROM card WHERE 고객등급 = 'vip'
Q3. 연체횟수가 1회 이하인 사람은 몇명일까요?
SELECT COUNT(연체횟수) FROM card WHERE 연체횟수 <= 1
7.컬럼 출력시 사칙연산 넣기 & 문자다루는 함수
-컬럼에 사칙연산이 가능하다. + - * /
select 사용금액 * 0.9 AS 부가세제외, 연체횟수 + 100 FROM card
동시에 여러칼럼도 가능하고 as도 쓰고싶으면 쓸수있음.
-컬럼 끼리의 연산도 가능함
결제 당 평균 사용금액을 출력하고싶으면? 사용금액을 결제횟수로 나누면됨
select 사용금액 / 결제횟수 FROM card
-문자 덧셈도 가능 CONCAT() 함수
SELECT CONCAT(고객명, 고객등급) FROM card
SELECT CONCAT(고객명, ' is ', 사용금액) FROM card
-여러 문자를 넣을 수도 있고, 직접문자를 입력해도 합쳐줌, 숫자입력도 문자처럼 합쳐줌.
*Oracle Postgres에서는 || 사용해서 합쳐줘야함.
SELECT 고객명 || ' is ' || 고객등급 FROM card
-문자 공백제거도 가능 TRIM()
문자 데이터에 쓸데없는 좌우 공백같은게 들어있을 수도 있는데 그걸 제거하고 싶을 때
SELECT TRIM(컬럼명) FROM 어쩌구
-단어를 대체도 가능하다 REPLACE()
SELECT REPLACE('서울에사는 서울맨', '서울', '경기')
참고로 SELECT 다음에 문자나 숫자만 달랑 넣어도 잘 출력해줌
-원하는 문자만 뽑아내고 싶을 때 SUBSTR()
SELECT SUBSTR('abcdef', 3, 2)
SUBSTR(문자, 몇번째부터, 몇자)를 채우면되는데 위의 코드는 3번째부터 2개임.
-문자의 일부를 다른 단어로 교체하고싶을때 INSERT()
SELECT INSERT('test@naver.com', 1, 4, 'hello')
INSERT(바꿀문자, 몇번째부터, 몇자를, 이걸로바꾸기)
위의 결과는 그래서 hello@naver.com
*Postgres는 OVERLAY() 를 쓴다.
OVERLAY('txxxxas' PLACING 'hom' FROM 2 FOR 4) → thomas 가 출력됨.
Oracle은 없어서 SUBSTR() 써서 직접 하면 된다.
Q1. 특정 문자에 있는 모든 공백을 제거해서 출력하려면 어떻게 코드를 짜야할까요?SELECT REPLACE(컬럼명, ' ', '') FROM 테이블명
Q2. 위 컬럼에서 휴대폰 뒷자리 4글자만 출력하려면 어떻게 코드를 짜야할까요?
SELECT RIGHT(번호, 4) from 테이블명
RIGHT() 이런거 쓰면 맨 뒤의 X자리 문자를 잘라서 출력해줌.
SELECT SUBSTR(번호, 10, 4) from 테이블명
번호가 항상 13자리라면 이렇게 써도 됨.
8.숫자 조작하는 SQL 함수들 (그때그때 찾아쓰면 됨)
GREATEST / LEAST
SELECT GREATEST(5, 3, 2, 1, 4);
SELECT LEAST(5, 3, 2, 1, 4);
여러 숫자들을 입력하면 최댓값, 최솟값 하나만 뽑아준다.
전에 했던 MAX(), MIN() 는 하나의 컬럼 안에서 최대, 최소를 1개 뽑아주는데
GREATEST(), LEAST() 는 하나의 행이나 숫자배열 안에서 최대, 최소를 뽑아준다.
FLOOR/CEIL
SELECT FLOOR(10.1);
SELECT FLOOR(10.9);
SELECT CEIL(10.1);
SELECT CEIL(10.9);
소수점이 들어있는 숫자들을 정수로 변환할 때 쓴다.
FLOOR는 소수부분을 내림해서 정수로 바꿔주고 CEIL은 소수부분을 올림해서 정수로 바꿔줌.
위 코드는 차례로 10, 10, 11, 11이 출력됨
ROUND/TRUNCATE
SELECT ROUND(10.777, 2);
SELECT TRUNCATE(10.777, 2);
소수점 부분을 반올림/내림할 때 쓴다.
소괄호에 (숫자, 자릿수)를 입력할 수 있는데
ROUND는 입력한 자릿수까지 반올림, TRUNCATE는 입력한 자릿수까지 내림.
위 코드는 차례로 10.78, 10.77 이 출력됨.
POWER
SELECT POWER(4, 2)
숫자를 거듭제곱하고 싶을 때 쓴다.
위의 예제는 4의 2승을 출력해줌.
ABS
SELECT ABS(-100)
숫자의 절댓값을 출력.
9.SELECT 안에 SELECT 또 쓸 수 있음 (서브쿼리)
사용금액의 평균보다 더 큰 사용금액을 가진 사람만 출력하고 싶으면 어떻게하죠?
-사용금액의 평균을 구해서, 사용금액 > 평균인 행만 출력해주면 되는데,
SELECT * FROM card WHERE 사용금액 >
(SELECT AVG(사용금액) FROM card)
SELECT 쿼리안에 다른 SELECT 쿼리를 자유롭게 넣을 수 있다.
저렇게 다른 쿼리 안에 들어가는 쿼리를 서브쿼리라고 한다.
(참고사항)
- 문자나 숫자 들어갈 곳에 서브쿼리를 대신 넣을 수 있습니다.
- 그래서 1개의 문자나 숫자를 뱉는 SELECT문만 서브쿼리로 넣을 수 있음.
(여러개의 행을 뱉는 SELECT는 서브쿼리 역할을 할 수 없다.)
- 서브쿼리 넣을 때 ( ) 소괄호 까먹으면 안됨.
SELECT 고객명, (SELECT AVG(사용금액) FROM card) FROM card;
이런식으로 서브쿼리는 아무데나 사용이 가능하다.
Q. card 테이블에서 블랙리스트 회원들의 사용금액을 출력하려면 어떻게 코드를 짜야하죠?
SELECT 사용금액 FROM card
WHERE 고객명 IN ('Pristine', 'George', 'Amy')
SELECT 사용금액 FROM card
WHERE 고객명 IN (SELECT 이름 FROM blacklist)
!)문자나 숫자를 1개만 뱉는 SELECT문만 서브쿼리역할을 할 수 있다 했는데?
- IN() 안에선 예외임!
결론 : 여러 SELECT 문장을 서브쿼리 형태로 하나로 합칠 수 있음.
심지어 위의 예제 잘 보면 다른 테이블을 SELECT한 결과도 서브쿼리로 넣을 수 있음을 알 수 있는데 테이블을 2개 이상 사용해서 결과를 뽑을 땐 대부분 서브쿼리말고 나중에 배울 JOIN 문법을 사용함.
대부분 동일한 결과를 얻을 수 있고 처리속도도 더 빠르고 문법도 약간 더 이해가 쉬움.
10.그룹지어 통계낼 땐 GROUP BY
SELECT 고객등급 FROM card
GROUP BY 고객등급
1. SELECT FROM 뒤에 GROUP BY 컬럼명을 붙일 수 있는데
2. 그럼 그 컬럼에 있는 카테고리끼리 그룹지어서 보여줌. (사실 distinct 동작방식이랑 유사)
3.GROUP BY는 전에 했던 MIN MAX COUNT SUM AVG 함수와 함께 이용하는 경우가 매우 많다.
그럼 각각의 카테고리마다 MIN MAX COUNT SUM AVG 값을 출력해볼 수 있음. 그룹만 지으면 쓸모가 없기에.
ex)
SELECT 고객등급, COUNT(고객명) FROM card
GROUP BY 고객등급
결론은 카테고리마다 통계를 내보고 싶으면 GROUP BY
GROUP BY는 category column에 주로 사용
GROUP BY 한 결과도 필터링 가능 - 뒤에 HAVING 조건문 쓰면 된다.
ex)
SELECT 고객등급, COUNT(고객명) FROM card
GROUP BY 고객등급
HAVING 고객등급 = 'vip'
HAVING vs WHERE
-용도가 서로 비슷하다. 둘 다 조건식을 입력하는 문법.
-HAVING은 GROUP BY 결과를 필터링하고 싶을 때 쓴다.
그래서 GROUP BY 뒤에만 붙일 수 있다.
- WHERE는 테이블 전체 데이터 출력시 필터링하고 싶을 때 쓰면 됨.
그래서 SELECT FROM 뒤에만 붙일 수 있다.
ex)
SELECT 고객등급, COUNT(고객명) FROM card
WHERE 연체횟수 = 0
GROUP BY 고객등급
HAVING 고객등급 = 'vip'
코드 많이 제시하면 어려워하는 사람들이 있는데
코드 읽을 땐 전체를 보려고 하면 안되고 위에서 부터 하나하나 읽어야함.
1. SELECT FROM으로 모든 데이터 출력하는데
2. 연체횟수 = 0인 행만 필터링하고
3. 그 결과를 고객등급으로 그룹화하고
4. 그 결과에서 고객등급 = 'vip'인 행만 필터링하라는 소리.
SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY