-
강의 들으면서 배운 문법 정리
한줄 주석달기 '--'
--Select all:
SELECT * FROM customer블록 주석 /* 에서 */까지
/*Select all the columns
of all the records
in the customers table:*/
SELECT * FROM customer;쿼리 작성요령
1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 조건을 걸 필드를 찾기
5) select * from 테이블명 where 조건 이렇게 쿼리 완성!DB 테이블 보기
Show tables;
orders 테이블의 데이터 가져와보기
select * from orders;
orders 테이블에서 payment_method가 kakaopay인 것만 가져와줘
select * from orders
where payment_method = "kakaopay";여러 조건을 같이 걸 경우 and를 붙인다.
select * from orders
where course_title = "앱개발 종합반" and payment_method = "kakaopay";포인트가 20000점보다 많은 유저만 뽑아보기!(부등호도 조건으로 가능)
select * from point_users
where point > 20000;같지 않음' 조건은 != 로 걸 수 있음
select * from orders
where course_title != "웹개발 종합반";'범위' 조건은 between으로
select * from orders
where created_at between "2020-07-13" and "2020-07-15";'포함' 조건은 in 으로
select * from checkins
where week in (1, 3);'패턴' 조건은 like으로, %을 문자열 앞뒤로 붙이면 와일드카드처럼 쓸 수 있음
select * from users
where email like '%daum.net';일부 데이터만 가져오기: Limit
select * from orders
where payment_method = "kakaopay"
limit 5;<5줄만 데이터를 가지고 오기>
중복 데이터는 제외하고 가져오기: Distinct
select distinct(payment_method) from orders;
몇 개인지 숫자 세보기: Count
select count(*) from orders
모든 값 더하기 sum
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id응용) 중복 안된 데이터를 카운트하기
SELECT count(distinct(name)) from users;
Group by 제대로 알아보기: SQL 쿼리가 실행되는 순서
select name, count(*) from users
group by name;위 쿼리가 실행되는 순서: from → group by → select
결과의 개수 오름차순으로 정렬
select name, count(*) from users
group by name
order by count(*);DESC를 order by문 뒤에 붙여주면 내림차순으로 정렬
쿼리가 실행되는 순서: from → group by → select → order by
별칭 붙여주기(as 사용)
select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_methodJoin : 테이블 여러개를 묶어서 한테이블처럼 표시
Left Join
A라는 테이블에 B의 공통부분을 붙이기 select * from users u
left join point_users p
on u.user_id = p.user_id;Inner Join
A,B테이블 공통된 부분을 서로 묶기 select * from users u
inner join point_users p
on u.user_id = p.user_id;쿼리가 실행되는 순서: from → join → where → group by → select
한마디로 말하면 inner join 은 교집합, left join 은 첫번째 원에 붙이는 것!
Union 사용법
A, B 테이블의 필드명이 전부 같아야함.
( select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c
on c2.course_id = c.course_id
inner join orders o
on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week )
union all
( select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c
on c2.course_id = c.course_id
inner join orders o
on o.user_id = c2.user_id
where o.created_at >= '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week )union 사용시 order by문이 먹히지가 않음.
Where 에 들어가는 Subquery
select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay');(1) from 실행: users 데이터를 가져와줌
(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력
Select 에 들어가는 Subquery
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) 함께 출력해준다!
From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
) a on pu.user_id = a.user_id(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!
with절
with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
), table2 as (
select course_id, count(*) as cnt_total from orders
group by course_id )
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.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각각 subquery를 table 1,2로 지정하고 조건식
문자열 쪼개기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!
문자열 일부만 출력하기
select order_no, created_at, substring(created_at,1,10) as date from orders
SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기
with table1 as (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
)
select level, count(*) as cnt from table1
group by level추가로
테이블 생성하기
CREATE TABLE member_table (
seq INT NOT NULL AUTO_INCREMENT,
mb_id VARCHAR(20),
mb_pw VARCHAR(20),
address VARCHAR(50),
mb_tell VARCHAR(50),
PRIMARY KEY(seq)
) ENGINE=MYISAM CHARSET=utf8;seq INT NOT NULL AUTO_INCREMENT,
seq로 컬럼의 이름을 입력하고 데이터 타입은 INT NOT NULL 로 중복이 불가능한 숫자형하고 AUTO_INCREMENT 로 자동 증가를 설정
PRIMARY KEY(seq)
7번째줄은 seq 를 기본키로 지정하여 중복이 불가능하게 지정 합니다.
1) 문자형 타입
CHAR() - 고정 문자일때 사용됩니다
- char(10)을 지정하면 10개(10Byte)의 데이터를 입력할 수 있습니다.
- (char(10) 지정후 데이터를 5Byte를 입력해도 데이터 공간은 10Byte로 자료형 크기만큼 데이터 공간을 차지하게 됩니다)
VARCHAR() - 가변 문자일대 사용됩니다
- VARCHAR(10)을 지정하면 10개(10Byte)의 데이터를 입력할 수 있습니다.
- (varchar(10) 지정후 데이터를 5Byte를 입력시 데이터 공간은 5Byte로 데이터 크기만큼 차지하게 됩니다)
CHAR(), VARCHAR() 차이점
- char은 검색이나 성능이 뛰어나 정형화 일때 사용되며, varchar는 가변적일때 사용됩니다.
TEXT
- text는 자연어 검색이 가능하게 하는것으로 용량이 많이 사용하게 될때 사용됩니다.
CHAR - 전화번호 같이 정형화 되어 있는것으로 사용됩니다
VARCHAR - 게시판 제목 같이 가변적일때 사용됩니다
TEXT - 게시판의 내용 같이 내용이 많을때 사용됩니다2) 숫자형 타입
주로 INT를 사용 숫자 크기에 따라 FLOAT, DOUBLE 사용
3) 날짜형 타입
DATE : YYYY-MM-DD
DATETIME : YYYY-MM-DD HH:MM:SS
TIMESTAMP : YYYYMMDDHHMMSS
TIME : HH:MM:SS테이블에 데이터 삽입하기
INSERT INTO member_table (mb_id, mb_pw, address, mb_tell)
VALUE('build', 'build1', '서울특별시 강남구 도곡동', '010-1234-1234');INSERT INTO [테이블 명] ([컬럼명], [컬럼명], [컬럼명], [컬럼명] )
테이블 데이터 수정하기
UPDATE Temp_Table SET field3='변경된 값'
WHERE field1 = 'data2';Temp_Table 의 field1의 값이 'data2' 인 행의 field3의 값을 '변경된 값'으로 수정
테이블 데이터 삭제
DELETE FROM Temp_Table
WHERE field1='data2';Temp_Table 의 field1의 값이 'data2' 인 행을 삭제
테이블 삭제
DROP TABLE