1. SELECT (데이터 조회하기)
SELECT col_name1, col_name2 // 해당하는 열의 이름을 조회함
SELECT * // 애스터리스크(*)는 모든열을 의미함
FROM 테이블명 // 여러 테이블중 보고 싶은 테이블에 대한 이름을 조회
- 예약어의 데이터베이스 객체명은 대소문자를 구별하지 않는다.
- 표 형식의 데이터는 '행(레코드)'와 '열(컬럼/필드)'로 구성된다.
2. ORDER BY
ORDER BY col_name // 가격을 기준으로 오름차순(default) 정렬
ORDER BY col_name1, col_name2 , .. // col_name1부터 오름차순 정렬
ORDER BY col_name desc // 내림차순 정렬
ex1) 오름차순
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명; //기본이 오름차순
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 ASC;
ex2) 내림차순
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 DESC;
- 문자는 사전식 순서에 의해 대소관계가 결정된다.
- 참고로 숫자를 문자열 형식으로 저장하게 되면 '10'은 '2'보다 작은것으로 판단된다. (사전식 정렬에 의해)
3. GROUP BY / COUNT
1) COUNT
SELECT count(*) // COUNT는 개수를 셀때 사용. (행의 개수)
SELECT col_name, count(*) // col_name의 행 개수
SELECT COUNT(*) FROM 테이블명 [WHERE 열명=조건];
//테이블에 존재하는 [조건을 만족하는] 모든 행의 갯수
SELECT COUNT(열명1), COUNT(열명2) FROM 테이블명;
//열명1과 열명2의 갯수를 별도로 센다.
//NULL은 집계함수가 세지 않는다.
2) GROUP BY 로 그룹화하기 (중복제거나 특정항목 갯수를 카운트할때 사용)
GROUP BY col_name // 같은 col_name을 그룹으로 취급함.
SELECT * FROM 테이블명 GROUP BY 열명;
//명시한 열명에 대해서 같은 열명을 쓰는 경우 하나의 그룹으로 취급한다.
//마치 DISTINCT를 지정했을 때처럼
SELECT COUNT(열명1), SUM(열명2) FROM 테이블명 GROUP BY 열명3;
//GROUP BY는 다른 집계함수와 쓰여야 그 힘을 발휘한다.
//열명3이 같은 경우로 묶어서 COUNT(열명1), SUM을(열명2)를 계산한다.
4. WHERE / And / Or
1) WHERE
SELECT * FROM 테이블명 WHERE col_name=조건1
SELECT * FROM 테이블명 WHERE 조건1 AND 조건2;
SELECT * FROM 테이블명 WHERE 조건1 OR 조건2;
SELECT * FROM 테이블명 WHERE NOT 조건;
AND는 OR에 비해 우선순위가 높다. 그러므로 괄호를 통해서 우선수위를 바꿀 수 있다.
SELECT * FROM 테이블명 WHERE (a=1 OR a=2) AND (b=1 OR b=2);
5. CASW WHEN / ELSE
ex) grouping
SELECT price, YEAR , MONTH , DAY, case when price <=10000 then '1억 이하 '
when price <=30000 then '1억 ~ 3억 이하 '
ELSE '3억 초과 '
end
=> price가 1억 이하인 경우 , 1억~3억인경우 , 3억 초과인 경우를 그룹화 할 수 있음.
단 1억 이하인 경우는 price<=30000에 포함되지 않는다. (이미 1억이하인 경우는 그룹화되어 있기 때문)
- CASE문은 SELECT 구, WHERE 구, OREDER BY 구 어디에서나 사용할 수 있다.
- ELSE를 생략하게 되면 NULL이 된다. 그러므로 가능한 생략하지 않는 편이 낫다.
6. LIKE / NOT LIKE
LIKE 특정 단어가 들어간 데이터를 검색할 때 사용
NOT LIKE 특정 단어가 들어간 단어는 제외하고 나열
* 퍼센트(%)의 위치 중요
SELECT * FROM 테이블명 WHERE text LIKE 'SQL%';
//text라는 열에서 SQL로 시작하는 내용이 있다면 검색한다. (전방매치)
SELECT * FROM 테이블명 WHERE text LIKE '%SQL%';
//text라는 열에서 SQL을 포함하는 내용이 있다면 검색한다. (중간매치)
//예를들어 'SQL은 RDBMS를 조작하는 언어이다'
//'LIKE는 SQL에서 사용할 수있는 술어중 하나이다'
SELECT * FROM 테이블명 WHERE text LIKE '%\%%';
//이스케이프를 통해서 % 검색하기
// _를 검색할떄도 이스케이프 (\_) 시켜야한다.
7. 서브쿼리
서브쿼리란 SQL 명령문 안에서 SQL 문을 괄호로 묶어 지정하는 방식 . (쿼리를 재활용하여 사용하는 기능)
ex) 부동산 데이터에서 거래 지역과 거래건수를 활용한 서브쿼리
SELECT a.*
, case when cnt<200 then '200미만 거래지역 ' else '200이상 거래지역 ' end
FROM (
select dong,COUNT(*) cnt
from apt_list
group by dong
) a
DELETE FROM 테이블명 WHERE 열명 = (SELECT MIN(열명) FROM 테이블명));
//'열명'에 해당하는 값중 가장 작은 값을 삭제하는 쿼리
//참고로 MySQL에서는 실행되지 않는다.
DELETE FROM 테이블명 WHERE 열명 = (SELECT 열명 FROM (SELECT MIN(열명) AS 열명 FROM 테이블명) AS x); //MySQL에서는 인라인 뷰로 임시 테이블을 만들도록 해야 한다.
SELECT (SELECT COUNT(*) FROM 테이블명1) AS t1, (SELECT COUNT(*) FROM 테이블명2) AS t2;
//서브쿼리를 이용한 조회
//Oracle 등에서는 뒤에 'FROM 테이블명'을 생략할 수 없다.
UPDATE 테이블명 SET 열명 = (SELECT MAX(열명) FROM 테이블명);
//서브쿼리를 이용한 갱신
SELECT * FROM (SELECT * FROM 테이블명) [AS] sq;
//FROM 구에서 쓰인 서브쿼리
//sq는 테이블의 별명이다. (subquery)
INSERT INTO 테이블명 VALUES ( (SELECT COUNT(*) FROM 테이블명1), (SELECT COUNT(*) FROM 테이블명2)); //VALUES 구에서 서브쿼리 사용하기.
8. Join
여러 테이블의 쿼리를 결합해야 하는 경우에 사용
ex) 부동산 데이터에서 두개의 테이블에 공통된 데이터만 나오게 하는경우
SELECT a.dong , a.size , a.price , b.si , b.gu
from apt_list a , area_cd b
where a.area_cd = b.area_cd
limit 5
=> 둘중에 한쪽에만 있는 정보는 나오지 않음
9. Outer join
양쪽 테이블중 어느 한쪽에만 데이터가 존재하더라도 데이터를 보여주는 방법
ex) 부동산 데이터에서 두개의 테이블중 어느 한쪽에만 존재해도 다 표현해줌. (없는 데이터는 NULL로 표시)
SELECT a.si, a.gu , b.nm , b.size , b.price
from area_cd a
left outer join apt_list b
on a.area_cd=b.area_cd
AND b.con_year =2020
limit 50
left , right , pull outer join 3가지 종류가 있음.
left outer join - from 다음에 나오는 테이블 기준으로 데이터를 표시 (from 기준 테이블의 데이터를 모두 보여주고, 조건에 맞는 데이터를 탐색하여 있으면 표시해주고 없으면 NULL로 표시)
10. Sum / Min / Max / Avg
각각 합 , 최댓값, 최솟값, 평균값을 구함
ex) 가격(col_name)에 대한 합 , 최댓값, 최솟값, 평균값을 나타냄
SELECT SUM(price) , MIN(price) , MAX(price) , AVG(price)
FROM apt_list
+ GROUP BY와 함께 쓸경우 그룹별로 합 , 최댓값 , 최솟값, 평균값을 볼 수 있음.
11. 실제 함수 활용 예시
1) 중복 제거
프로그래머스 '중복 제거하기' - 중복된 NAME은 하나의 개체로 카운트,
ex) NULL , Sam , Sam , Lucy 가 있으면 count는 2를 반환
2) HAVING 구를 통한 조건 걸기
프로그래머스 '동명 동물 수 찾기' - 중복된 NAME이 있으면 count하여 출력하는 문제
*sol)
1. NAME과 , COUNT를 조회 (count 는 null값을 세지 않음)
2. GROUP BY를 통해 NAME을 그룹핑함
3. HAVING 구를 이용하여 중복된경우만 조회
GROUP BY 뒤에 그룹핑한 데이터들에서 HAVING 구를 이용해 원하는 데이터만 조회함.
3) 각 시간대별로 사건이 몇 번이나 발생 하는지 조회
프로그래머스 '입양 시각 구하기(1)' - DATETIME에서 HOUR 함수를 통해 시간대별로 count를 조회.
*sol1)
DATETIME에서 HOUR함수를 통해 시간을 추출하고 열의 이름을 HOUR로 설정함.
GROUP BY 로 시간대별로 그룹핑을 하고 HAVING구를 통해 시간조건을 설정함.
이후 ORDER BY로 정렬.
*sol2)
HAVING대신 WHERE활용
FROM 이후 WHERE HOUR(DATETIME) >=9 AND HOUR(DATETIME)<=19를 활용해도 된다.
3) NULL체크
sol1) WHERE col_name is null OR WHERE col_name is not null 둘중 하나 활용
NAME 에서 null 값이 있는 값을 제외하고 출력 ANIMAL_ID를 출력함
sol2) IFNULL 활용
SELECT문에서 IFNULL을 활용하여 NULL값인 NAME에 대해 'No name'으로 변경 후 출력.
4) OUTER JOIN 활용
프로그래머스 '없어진 기록 찾기' - 입양간 기록과, 보호소에 들어온 기록 두가지 데이터 중
보호소에 들어온기록의 일부가 유실되었음. 이 동물의 ID와 이름을 조회하는 코드.
sol) LEFT JOIN 활용 - LEFT JOIN은 두 데이터의 공통된 부분 + 한쪽에만 있는 데이터는 NULL로 표시해줌.
다음과 같이 A에는 있고 B에는 없는 데이터를 조회할 수 있음.
문제에서 보호소에 들어온 기록이 없는 동물의 ID와 NAME을 조회한다 했으므로 A는 데이터가 유실되지 않은 입양간 기록 ANIMAL_OUTS에 해당하고, B는 데이터가 유실되었던 ANIMAL_INS에 해당함. (B에는 없는 유실된 데이터를 A에서 찾아야 하므로)
SELECT A.ANIMAL_ID , A.NAME
FROM ANIMAL_OUTS A LEFT JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID is null
을 통해 B에는 없는 유실된 데이터가 NULL값으로 표현되기 때문에 기록이 없는 동물의 ID와 이름을 조회할 수 있음.
5) DATETIME의 활용
프로그래머스 'DATETIME에서 DATE로 형 변환' - DATETIME의 다양한 형변환
sol1) date_format(datetime, '%Y-%m-%d') 활용
SELECT animal_id, name, date_format(datetime, '%Y-%m-%d')
FROM animal_ins
ORDER BY animal_id;
sol2) substring(datetime, start_point , end_point)
SELECT animal_id, name, substring(datetime,1,10)
FROM animal_ins
ORDER BY animal_id;
sol3) left(string, length) , convert(datetime , 형변환함수1. SELECT (데이터 조회하기)
SELECT col_name1, col_name2 // 해당하는 열의 이름을 조회함
SELECT * // 애스터리스크(*)는 모든열을 의미함
FROM 테이블명 // 여러 테이블중 보고 싶은 테이블에 대한 이름을 조회
예약어의 데이터베이스 객체명은 대소문자를 구별하지 않는다.
표 형식의 데이터는 '행(레코드)'와 '열(컬럼/필드)'로 구성된다.
2. ORDER BY
ORDER BY col_name // 가격을 기준으로 오름차순(default) 정렬
ORDER BY col_name1, col_name2 , .. // col_name1부터 오름차순 정렬
ORDER BY col_name desc // 내림차순 정렬
ex1) 오름차순
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명; //기본이 오름차순
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 ASC;
ex2) 내림차순
SELECT 열명 FROM 테이블명 WHERE 조건식 ORDER BY 열명 DESC;
문자는 사전식 순서에 의해 대소관계가 결정된다.
참고로 숫자를 문자열 형식으로 저장하게 되면 '10'은 '2'보다 작은것으로 판단된다. (사전식 정렬에 의해)
3. GROUP BY / COUNT
1) COUNT
SELECT count(*) // COUNT는 개수를 셀때 사용. (행의 개수)
SELECT col_name, count(*) // col_name의 행 개수
SELECT COUNT(*) FROM 테이블명 [WHERE 열명=조건];
//테이블에 존재하는 [조건을 만족하는] 모든 행의 갯수
SELECT COUNT(열명1), COUNT(열명2) FROM 테이블명;
//열명1과 열명2의 갯수를 별도로 센다.
//NULL은 집계함수가 세지 않는다.
2) GROUP BY 로 그룹화하기 (중복제거나 특정항목 갯수를 카운트할때 사용)
GROUP BY col_name // 같은 col_name을 그룹으로 취급함.
SELECT * FROM 테이블명 GROUP BY 열명;
//명시한 열명에 대해서 같은 열명을 쓰는 경우 하나의 그룹으로 취급한다.
//마치 DISTINCT를 지정했을 때처럼
SELECT COUNT(열명1), SUM(열명2) FROM 테이블명 GROUP BY 열명3;
//GROUP BY는 다른 집계함수와 쓰여야 그 힘을 발휘한다.
//열명3이 같은 경우로 묶어서 COUNT(열명1), SUM을(열명2)를 계산한다.
4. WHERE / And / Or
1) WHERE
SELECT * FROM 테이블명 WHERE col_name=조건1
SELECT * FROM 테이블명 WHERE 조건1 AND 조건2;
SELECT * FROM 테이블명 WHERE 조건1 OR 조건2;
SELECT * FROM 테이블명 WHERE NOT 조건;
AND는 OR에 비해 우선순위가 높다. 그러므로 괄호를 통해서 우선수위를 바꿀 수 있다.
SELECT * FROM 테이블명 WHERE (a=1 OR a=2) AND (b=1 OR b=2);
5. CASW WHEN / ELSE
ex) grouping
SELECT price, YEAR , MONTH , DAY, case when price <=10000 then '1억 이하 '
when price <=30000 then '1억 ~ 3억 이하 '
ELSE '3억 초과 '
end
=> price가 1억 이하인 경우 , 1억~3억인경우 , 3억 초과인 경우를 그룹화 할 수 있음.
단 1억 이하인 경우는 price<=30000에 포함되지 않는다. (이미 1억이하인 경우는 그룹화되어 있기 때문)
CASE문은 SELECT 구, WHERE 구, OREDER BY 구 어디에서나 사용할 수 있다.
ELSE를 생략하게 되면 NULL이 된다. 그러므로 가능한 생략하지 않는 편이 낫다.
6. LIKE / NOT LIKE
LIKE 특정 단어가 들어간 데이터를 검색할 때 사용
NOT LIKE 특정 단어가 들어간 단어는 제외하고 나열
* 퍼센트(%)의 위치 중요
SELECT * FROM 테이블명 WHERE text LIKE 'SQL%';
//text라는 열에서 SQL로 시작하는 내용이 있다면 검색한다. (전방매치)
SELECT * FROM 테이블명 WHERE text LIKE '%SQL%';
//text라는 열에서 SQL을 포함하는 내용이 있다면 검색한다. (중간매치)
//예를들어 'SQL은 RDBMS를 조작하는 언어이다'
//'LIKE는 SQL에서 사용할 수있는 술어중 하나이다'
SELECT * FROM 테이블명 WHERE text LIKE '%\%%';
//이스케이프를 통해서 % 검색하기
// _를 검색할떄도 이스케이프 (\_) 시켜야한다.
7. 서브쿼리
서브쿼리란 SQL 명령문 안에서 SQL 문을 괄호로 묶어 지정하는 방식 . (쿼리를 재활용하여 사용하는 기능)
ex) 부동산 데이터에서 거래 지역과 거래건수를 활용한 서브쿼리
SELECT a.*
, case when cnt<200 then '200미만 거래지역 ' else '200이상 거래지역 ' end
FROM (
select dong,COUNT(*) cnt
from apt_list
group by dong
) a
DELETE FROM 테이블명 WHERE 열명 = (SELECT MIN(열명) FROM 테이블명));
//'열명'에 해당하는 값중 가장 작은 값을 삭제하는 쿼리
//참고로 MySQL에서는 실행되지 않는다.
DELETE FROM 테이블명 WHERE 열명 = (SELECT 열명 FROM (SELECT MIN(열명) AS 열명 FROM 테이블명) AS x); //MySQL에서는 인라인 뷰로 임시 테이블을 만들도록 해야 한다.
SELECT (SELECT COUNT(*) FROM 테이블명1) AS t1, (SELECT COUNT(*) FROM 테이블명2) AS t2;
//서브쿼리를 이용한 조회
//Oracle 등에서는 뒤에 'FROM 테이블명'을 생략할 수 없다.
UPDATE 테이블명 SET 열명 = (SELECT MAX(열명) FROM 테이블명);
//서브쿼리를 이용한 갱신
SELECT * FROM (SELECT * FROM 테이블명) [AS] sq;
//FROM 구에서 쓰인 서브쿼리
//sq는 테이블의 별명이다. (subquery)
INSERT INTO 테이블명 VALUES ( (SELECT COUNT(*) FROM 테이블명1), (SELECT COUNT(*) FROM 테이블명2)); //VALUES 구에서 서브쿼리 사용하기.
8. Join
여러 테이블의 쿼리를 결합해야 하는 경우에 사용
ex) 부동산 데이터에서 두개의 테이블에 공통된 데이터만 나오게 하는경우
SELECT a.dong , a.size , a.price , b.si , b.gu
from apt_list a , area_cd b
where a.area_cd = b.area_cd
limit 5
=> 둘중에 한쪽에만 있는 정보는 나오지 않음
9. Outer join
양쪽 테이블중 어느 한쪽에만 데이터가 존재하더라도 데이터를 보여주는 방법
ex) 부동산 데이터에서 두개의 테이블중 어느 한쪽에만 존재해도 다 표현해줌. (없는 데이터는 NULL로 표시)
SELECT a.si, a.gu , b.nm , b.size , b.price
from area_cd a
left outer join apt_list b
on a.area_cd=b.area_cd
AND b.con_year =2020
limit 50
left , right , pull outer join 3가지 종류가 있음.
left outer join - from 다음에 나오는 테이블 기준으로 데이터를 표시 (from 기준 테이블의 데이터를 모두 보여주고, 조건에 맞는 데이터를 탐색하여 있으면 표시해주고 없으면 NULL로 표시)
10. Sum / Min / Max / Avg
각각 합 , 최댓값, 최솟값, 평균값을 구함
ex) 가격(col_name)에 대한 합 , 최댓값, 최솟값, 평균값을 나타냄
SELECT SUM(price) , MIN(price) , MAX(price) , AVG(price)
FROM apt_list
+ GROUP BY와 함께 쓸경우 그룹별로 합 , 최댓값 , 최솟값, 평균값을 볼 수 있음.
11. 실제 함수 활용 예시
1) 중복 제거
프로그래머스 '중복 제거하기' - 중복된 NAME은 하나의 개체로 카운트,
ex) NULL , Sam , Sam , Lucy 가 있으면 count는 2를 반환

2) HAVING 구를 통한 조건 걸기
프로그래머스 '동명 동물 수 찾기' - 중복된 NAME이 있으면 count하여 출력하는 문제

*sol)
1. NAME과 , COUNT를 조회 (count 는 null값을 세지 않음)
2. GROUP BY를 통해 NAME을 그룹핑함
3. HAVING 구를 이용하여 중복된경우만 조회

GROUP BY 뒤에 그룹핑한 데이터들에서 HAVING 구를 이용해 원하는 데이터만 조회함.
3) 각 시간대별로 사건이 몇 번이나 발생 하는지 조회
프로그래머스 '입양 시각 구하기(1)' - DATETIME에서 HOUR 함수를 통해 시간대별로 count를 조회.

*sol1)
DATETIME에서 HOUR함수를 통해 시간을 추출하고 열의 이름을 HOUR로 설정함.
GROUP BY 로 시간대별로 그룹핑을 하고 HAVING구를 통해 시간조건을 설정함.
이후 ORDER BY로 정렬.
*sol2)
HAVING대신 WHERE활용
FROM 이후 WHERE HOUR(DATETIME) >=9 AND HOUR(DATETIME)<=19를 활용해도 된다.
3) NULL체크

sol1) WHERE col_name is null OR WHERE col_name is not null 둘중 하나 활용
NAME 에서 null 값이 있는 값을 제외하고 출력 ANIMAL_ID를 출력함

sol2) IFNULL 활용
SELECT문에서 IFNULL을 활용하여 NULL값인 NAME에 대해 'No name'으로 변경 후 출력.
4) OUTER JOIN 활용

프로그래머스 '없어진 기록 찾기' - 입양간 기록과, 보호소에 들어온 기록 두가지 데이터 중
보호소에 들어온기록의 일부가 유실되었음. 이 동물의 ID와 이름을 조회하는 코드.
sol) LEFT JOIN 활용 - LEFT JOIN은 두 데이터의 공통된 부분 + 한쪽에만 있는 데이터는 NULL로 표시해줌.

다음과 같이 A에는 있고 B에는 없는 데이터를 조회할 수 있음.
문제에서 보호소에 들어온 기록이 없는 동물의 ID와 NAME을 조회한다 했으므로 A는 데이터가 유실되지 않은 입양간 기록 ANIMAL_OUTS에 해당하고, B는 데이터가 유실되었던 ANIMAL_INS에 해당함. (B에는 없는 유실된 데이터를 A에서 찾아야 하므로)
SELECT A.ANIMAL_ID , A.NAME
FROM ANIMAL_OUTS A LEFT JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID is null
을 통해 B에는 없는 유실된 데이터가 NULL값으로 표현되기 때문에 기록이 없는 동물의 ID와 이름을 조회할 수 있음.
5) DATETIME의 활용
프로그래머스 'DATETIME에서 DATE로 형 변환' - DATETIME의 다양한 형변환
sol1) date_format(datetime, '%Y-%m-%d') 활용
SELECT animal_id, name, date_format(datetime, '%Y-%m-%d')
FROM animal_ins
ORDER BY animal_id;
※주의 : date_format(datetime, ' 형식' ); 형식에서 y, m ,d의 대소문자 구분 중요..
2015년 12월 10일
ex1) date_format(datetime, '%Y-%M-%D') -> 2015-December-10th
ex2) date_format(datetime, '%y-%m-%d') -> 15-12-10
sol2) substring(datetime, start_point , end_point)
SELECT animal_id, name, substring(datetime,1,10)
FROM animal_ins
ORDER BY animal_id;
sol3) left(string, length) , convert(datetime , 형변환함수)
SELECT ANIMAL_ID,NAME,left(CONVERT(DATETIME,DATE),10) AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID