01.24(6일차)
create table test(
id int);
select * from test;
insert into test values(1);
# 트랜잭션 시작
start transaction;
insert into test values(2);
# 세이브포인트 생성
savepoint a;
insert into test values(3);
# 롤백 연산
rollback to a;
# 트랜잭션 종료
commit;
start transaction;
insert into test values(3), (4);
savepoint b;
commit; # commit을 진행한 순간 트랜잭션이 완료됐음 --> 세이브포인트 소멸
rollback to b; # savepoint 소멸 -> 오류 발생
select * from test; # 1,2,3,4
truncate table test;
select * from test;
start transaction;
insert into test values(1);
savepoint p1; # 1
insert into test values(2), (3);
savepoint p2; # 1,2,3
insert into test values(4), (5);
rollback to p1;
select * from test;
# 첫 번째 실습
truncate table test;
start transaction;
insert into test values(1), (2), (3);
savepoint A;
insert into test values(4), (5), (6);
savepoint B;
commit;
rollback to A;
select * from test;
# LIKE 조건
# - 패턴 포함, 특정 문자열 포함, 글자수 맞춤 등..
# _ --> 1 글자
# % --> 글자 수 제한 X
# EX) _AB_ --> 4글자 + 중간 글자가 AB인 것을 찾는거
# EX) %AB% --> 글자 수 제한 X + 중간에 AB만 들어있으면 다 찾을 수 있음
use sale;
select * from cus;
select * from cus where email like ('__Email');
select * from cus where email like ('%Email');
select * from cus where email like ('%gmail%');
select * from cus where tel like ('%1654');
select * from emp where name like('A%');
use world;
select * from country where name like ('%A');
# 두 번째 실습
select * from city where name like('A%');
select * from city where name like('s____');
select * from country where name like('C%') and population >= 10000000;
select * from country where name like('k%') or population between 10000000 and 100000000;
# Day07
use sale;
select * from cus;
# order by -> 정렬 구문
select * from cus order by cus_id desc; # cus_id를 기준으로 내림차순 정렬
# ASC -> 오름차순(생략 가능)
select * from cus order by name; # asc가 숨어있음
select * from cus order by name asc;
select * from cus order by 1 desc; # 1번째 속성을 기준으로 내림차순 정렬
insert into cus(name, tel, email, emp_id) values('Gavin', '635154616351', default, 1);
# 1번째 기준 속성으로 정렬을 하는데 -> 중복된 값이 있다면 2번째 기준 속성으로 정렬
select * from cus order by 2, 1 desc;
select * from cus order by 2, 1 asc;
use world;
# 2가지 속성(숫자)을 더한 결과에 새로운 이름을 부여하고 해당 이름으로 정렬
select name, population + gnp as total from country order by total;
select name, population + gnp as total from country order by total desc;
# limit -> 결과 개수 정하기
select * from city;
select * from city limit 10;
select * from city limit 5;
select * from city limit 100;
select * from city limit 200;
# distinct -> 중복 제거
select continent, name from country order by continent;
select continent from country;
select distinct continent from country;
# 주의점 --> 레코드 전체 검색할 때는 레코드 전체가 중복이 되어야지만 제외 대상
# group by --> 특정 속성을 기준으로 묶어서 집계 함수를 사용할 수 있다.
select countrycode, sum(population) from city
group by countrycode;
select continent, sum(population) from country # 총합
group by continent;
select continent, count(population) from country # 개수
group by continent;
select continent, avg(population) from country # 평균
group by continent;
# 월별 총액, 연도별 평균
drop database if exists Day07;
create database Day07;
use Day07;
create table T1(
month_value varchar(10) not null,
price int);
insert into T1 values('1', 782785),
('1', 786786),
('1', 545342),
('1', 452452),
('1', 478788),
('2', 123145),
('2', 23452345),
('2', 12345143),
('2', 12341267),
('2', 4524152),
('3', 524425),
('3', 45237),
('3', 75378),
('3', 48615),
('3', 7863712),
('4', 23453456),
('4', 234562),
('4', 786967),
('4', 4125867),
('4', 8973543),
('5', 414152),
('5', 4421012),
('5', 452452),
('5', 203122),
('5', 7485645);
select * from T1;
select month_value, sum(price) from T1 # 합계
group by month_value;
select month_value, count(price) as count from T1 # 개수
group by month_value;
select month_value, avg(price) from T1 # 평균
group by month_value;
select month_value, max(price) from T1 # 최대값
group by month_value;
select month_value, min(price) from T1 # 최소값
group by month_value;
# 세 번째 실습
use world;
# 1
select continent, min(population), max(population) from country
group by continent;
# 2
select countrycode, sum(population) from city
group by countrycode;
# 3
select language, avg(percentage) from countrylanguage
group by language;
# 4
select region, sum(population), avg(population), count(population),
min(population), max(population) from country
group by region;
# 5
select continent, sum(population) from country
where name like('%A%')
group by continent;
# having -> 집계 함수를 적용한 속성에 조건을 작성할 때 사용
select continent, sum(population) from country
group by continent
having sum(population) >= 1000000000;
select continent, sum(population) as total from country
group by continent
having total >= 1000000000;
select continent, sum(population) as total from country
where name like('%A%') # 처음 조건(검색할 때)
group by continent having total >= 50000000; # 결과 조건(가져올 때)
use Day07;
desc t1;
insert into t1 values('6', 123123), ('6', 123542), ('7', 321654), ('7', 652316);
select * from t1;
select month_value, count(price) as count from t1
group by month_value having count = 5;
select month_value, avg(price) as price from t1
group by month_value having price >= 1000000;
# where -> 검색할 때 조건을 이용
# having -> 집계함수를 적용한 결과에서 조건을 이용(가져올 때)
use world;
select * from country;
# 아시아 -> 인구수의 총합과 평균을 계산합니다
select continent, sum(population) as sum_rs, avg(population) as avg_rs
from country where continent like ('Asia') group by continent;
# 대륙별로 나라 개수 확인 -> 나라 개수가 40개 이상인 대륙만 출력
select continent, count(code) as count from country
group by continent having count >= 40;