SQl 기본 문법 정리
01. 데이터베이스와 SQL의 필요성
: 데이터 양이 많아지면 엑셀이 느려지고 내용이 충돌되는 문제 발생
데이터베이스에 내가 원하는 형태로 가지고 올 수 있게 SQL 사용
02. DB에 명령을 내리려면 사용자와 DB 사이 약속된 규칙 혹은 언어가 필요
=> SQL
; Structured Query Language의 약자로 데이터베이스에 요청(Query)를 보내 원하는
데이터를 가져오는 것을 도와주는 언어
📚 데이터베이스를 이용해서 사용자들의 데이터를 저장할 경우의 장점
1) 방대한 양의 데이터 저장
2) 방대한 양의 데이터에서 내가 원하는 정보를 신속하게 가져오기
<데이터베이스 기초개념>
여러 사람들이 같이 사용할 목적으로 데이터를 담는 통(보관함)
-> 데이터를 쌓아두는 것이아닌, 데이터를 잘 꺼내오기 위함!
C (Create): 데이터의 생성
**R (Read): 저장된 데이터를 읽어오는 것**
U (Update): 저장된 데이터 변경
D (Delete): 저장된 데이터 삭제
## **<Select 쿼리문>**
쿼리문이란?
쿼리(:질의) , 데이터베이스에 명령을 내리는 것
### Select 쿼리문
:데이터베이스에서 데이터를 선택해서 가져옴
_point!_
1) 어떤 테이블에서 2) 어떤 필드의 데이터를 가져올지
* 테이블 : 데이터가 담긴 엑셀시트
* 필드(칼럼) : 데이터베이스(엑셀시트)에서 세로방향으로 표시된 column
1. 쿼리문 작성 _꿀팁!_
1) show table을 이용해 테이블 살펴보기
2) 원하는 정보가 들어있을 것 같은 테이블에 select * from 테이블명 쿼리 작성
3) 원하는 테이블에 조건을 걸 필드를 찾기
4) 조건에 맞는 where문 작성
> 💡 where문과 자주 쓰이는 문법
!= 같지 않음을 나타냄, 제외할 때 사용
between 범위 조건
in (포함할 조건)
like'%내용' 패턴 조건(문자열 규칙을 설정할 때 사용)
=> %가 지칭된 내용은 제외하고 찾을 수 있도록 함
_ex) where email like 'b%com' :email 필드값이 b로 시작,com으로 끝나는 데이터_
Limit 일부 데이터만 가져옴 => 방대한 데이터를 잠깐만 보려고 할 때 유용
Distinct 중복 제외
Count 카운트 기능
* Distinct와 Count 같이 쓰기 (응용)
<사이트 회원의 성씨 개수 세기>
select distinct(name) from users; // 결과 : 성씨 나열
select count(distinct(name)) from users // 성씨 개수 count
## <통계, 통계 구하기, Group by, Order by>
=> 데이터베이스 테이블에 저장된 날것의 쌓여있는 데이터를 의미있는 **_'정보'_**로 변환
#### 01.Group by
:동일한 범주의 데이터를 묶어 줌 (**범주의 통계**를 내준다)
> count, min, max, avg, round(,반올림 자리 수), sum 문법과 사용
예시)
select name, count(*) from users
group by name;
_*실행순서
from -> group by -> select
(1) users 테이블 전체 가져옴
(2) name 필드 데이터를 묶어줌
(3) name에 따라 묶어진 데이터가 몇 개가 묶어졌는지 세어줌_
02.Order by
:데이터 정렬
맨 마지막에 실행, 정렬할 필드명을 적어줌 (디폴트: 오름차순/ desc : 내림차순)
> select * from 테이블명
order by 정렬의 기준이 될 필드명;
> ex) like를 많이 받은 순서대로 '오늘의 다짐'을 출력
select * from checkins
order by likes desc;
3.쿼리를 작성할 때의 팁💡
-테이블을 먼저 살핀 후 원하는 정보가 있을 것 같은 테이블에 select * from table이름 limit 10으로 쿼리를 날려서 정보가 있는지 확인
-테이블을 찾았다면 범주를 나눠서 보고싶은 필드를 찾고, 범주별로 통계를 보고싶은 필드를 찾는다.
-마지막으로 SQL 쿼리를 작성한다.
=> SQL은 **실행순서**를 잘 알아두고 중요도가 높다는 것을 인지하면서 작성하자
<별칭기능 : Alias>
테이블이 길어졌을 때 혼동을 방지하도록 하는 **별칭** 기능
ex 1) select * from orders o
where o.course title = '앱개발 종합반'
ex 2) count(*) as cnt : as뒤에 따라오는 cnt가 별칭
< Join >
실무에서 많이 쓰임. 테이블과 테이블을 매칭시킬 때 사용
> Join : 테이블과 테이블을 두 테이블의 공통된 정보인 'key값'을 사용해서 key값 기준으로 연결해 붙이는 것
한 테이블에 모든 정보를 담을 순 있지만, 불필요한 필드가 많다면 테이블의 크기가 커져서 사용이 불편
-> 데이터를 종류별로 쪼개서 다른 테이블에 담아놓고 연결이 필요한 경우에 연결 할 수 있도록 만들어 놓는다.
_두 테이블을 연결시켜주는 필드를 **'key'**라고 부름_
01. Left Join
왼쪽 테이블을 기준으로 오른쪽 테이블을 붙인다고 해서 _Left Join_
select * from users u
left join point_users p
on u.user_id = p.user_id;
(NULL 값이 뜨는 경우는 그 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않음)
02. Inner Join
Inner Join을 쓰면 교집합 결과만 나옴
📢 Left Join과 Inner Join의 차이점
Left Join은 A에 B를 붙였을 때 없으면 NULL을 표시함 => 순서가 중요
Inner Join은 교집합이기 때문에 상관이 없음 => 조금 더 쉬움
> Join 쿼리가 실행되는 순서: from → join → select
Union
: Select를 2번 하지 않고 한 번에 모아서 보고 싶은 경우 사용
묶고 싶은 쿼리를 적고,
괄호로 각각 묶은 다음 중간에 union all 하면 결과가 합쳐진다!
**union은 ordery by(내부정렬)이 적용되지 않음을 주의
예시
(
select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at < '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
)
union all
(
select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
)
## < Subquery >
: 하나의 SQL 쿼리 안에 또다른 SQL쿼리가 있는 것
where, select, from 절에서 유용하게 사용
**1) Where 필드명 in (subquery) ****
Subquery의 결과를 조건에 활용하는 방식으로 유용
포인트가 평균보다 많은 사람들의 데이터를 추출
select * from point_users pu
where point > (
SELECT avg(point) from point_users pu
)
-> 굳이 평균값을 적지 않고 Subquery를 넣는 이유?
평균값이 변할 수 있기 때문
**1-2) Select 필드명, 필드명, (subquery) from .. . **
=> Select는 결과를 출력해줌, 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
Ex)
select c.checkin_id, c.user_id, c.likes,
(select avg(likes) from checkins c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c;
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력
// 직접 해보는 게 이해에 빠름
** Select Subquery 복습 필수 문제!
문제)
checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
오답)
select checkin_id, course_id, user_id, likes,
(select avg(c.likes) from checkins c
where c.course_id = course_id)
from checkins
해답)
select checkin_id, course_id, user_id, likes,
(select avg(c.likes) from checkins c
where c.course_id = c2.course_id)
from checkins c2
// subquery안의 course_id와 main query의 course_id를 join시켜야 하므로 alias를 꼭 붙여줘야 한다 ! alias를 쓰는 이유,, 일종의 변수 개념이기 때문에 ! + 구분 짓기 위해서
**1-3) From에 들어가는 Subquery (가장 많이 사용)
보통 내가 만든 select와 이미 있는 테이블을 join하고 싶을 때 사용
**
```
Ex)
select c.title, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio
from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b on a.course_id = b.course_id
inner join courses c
on a.course_id = c.course_id
```
**( with 구문 )**
* from안에 들어가는 subquery를 쓸 때 훨씬 간결하게 표현할 수 있다.**
```
Ex)
with table1 as (
select course_id, count(*) as cnt_total from orders
group by course_id
), table2 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
)
select c.title,
b.cnt_checkins,
a.cnt_total,
(b.cnt_checkins/a.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
```
-> 이처럼 from과 inner join에 들어가는 subquery를 with구문으로 빼서 각각의 임시 테이블을 만들어주었다. **with구문을 사용할 때는 항상 윗단에다가 적도록 해야한다.
**( 문자열 데이터 가공 )**
> 실무에서는 문자열을 원하는 형태로 가공해야하는 경우가 많다! 그렇다면?
1-1) SUBSTRING_INDEX ; 문자열 쪼개기
문자열을 한 기준에서 기준의 앞 데이터나 뒤의 데이터만 쪼개는 것
예시 1) SUBSTRING_INDEX(email,'@',1)
@기준으로 문자열을 쪼개고, 첫 번째 조각을 가져온다 (아이디)
2) SUBSTRING_INDEX(email,'@',-1)
@기준으로 문자열을 쪼개고, 마지막 조각을 가져온다 (도메인)
1-2) SUBSTRING ; 문자열 일부 출력
SUBSTRING(칼럼명, 출력시작위치, 마지막위치)
ex) 칼럼(created_at) [2023-02-16 20:29:04] 기준
SELECT order_no, SUBSTRING(created_at,1,10) as date from orders
=> 일자 출력
SELECT order_no, SUBSTRING(created_at,12,8) as date from orders
=> 시간 출력
결과를 group by로 통계를 내도록 활용 가능
04. case when 조건문
; 조건에 따라 원하는 값을 새로운 칼럼에 출력
SELECT pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
else '1만 미만' end) as msg
from point_users pu
//포인트 조건에 따라 메세지를 다르게 칼럼에 출력하기
또, 때에 따라 subquery를 이용해서, case when 조건문 결과를 통계 낼 수 있다
+) subquery와 case when
SELECT a.lv, count(*) FROM (
SELECT pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
) a
group by a.lv
=> subquery로 조건문을 통계 낼 수 있다
++) with절로 표현
with table1 as (
SELECT pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
)
SELECT a.lv, count(*) FROM table1 a
group by a.lv
