create database market_db default character set utf8 collate utf8_bin;
use market_db;
create table member
(
mem_id char(8) not null primary key,
mem_name varchar(10) not null,
mem_number int not null,
addr char(2) not null,
phone1 char(3),
phone2 char(8),
height smallint,
debut_date date
);
drop table member;
create table buy
(
num int auto_increment not null primary key,
mem_id char(8) not null,
prod_name char(6) not null,
group_name char(4),
price int not null,
amount smallint not null,
-- member(mem_id)에서 mem_id는 아무거나 들어와도 상관없지만,
-- mem_id는 PK,유니크 아니면 되지않는다.
foreign key (mem_id) references member(mem_id)
);
set foreign_key_checks=1;
select * from member;
select * from buy;
insert into member values('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
insert into member values('APN', '에이핑크', 6, '경기', '031', '32133311', 111, '2014.10.19');
insert into member values('BLK', '블랙핑크', 4, '경님', '055', '22222222', 168, '2013.01.19');
insert into member values('GRL', '소녀시대', 8, '서울', '02', '32132111', 166, '2012.03.19');
insert into member values('ITZ', '잇지', 5, '경남', null, '53212346', 164, '2011.10.19');
insert into member values('MMU', '마마무', 4, '전남', '033', '55555555', 162, '2018.12.19');
insert into member values('OMY', '오마이걸', 7, '서울', '022', '77777777', 161, '2017.11.19');
insert into member values('RED', '레드벨벳', 4, '경북', '032', '55523366', 160, '2016.01.19');
insert into member values('SPC', '우주소녀', 13, '서울', null, '12312311', 169, '2003.03.19');
insert into member values('WMN', '여자친구', 6, '경기', '053', '66666666', 150, '2016.05.19');
insert into buy values(null, 'BLK', '지갑', null, 30, 2);
insert into buy values(null, 'BLK', '맥북프로', '디지털', 1000, 1);
insert into buy values(null, 'APN', '아이폰', '디지털', 200, 1);
insert into buy values(null, 'MMU', '아이폰', '디지털', 200, 5);
insert into buy values(null, 'BLK', '청바지', '패션', 50, 3);
insert into buy values(null, 'MMU', '에어팟', '디지털', 80, 10);
insert into buy values(null, 'GRL', '홍콩SQL', '서적', 15, 5);
insert into buy values(null, 'APN', '홍콩SQL', '서적', 15, 2);
insert into buy values(null, 'APN', '청바지', '패션', 50, 1);
insert into buy values(null, 'MMU', '지갑', null, 30, 1);
insert into buy values(null, 'APN', '홍콩SQL', '서적', 15, 1);
insert into buy values(null, 'MMU', '지갑', null, 30, 4);
-- member table의 모든 것을 조회
select * from member;
-- member table에서 mem_name column만 조회
select mem_name from member;
-- column명 바꾸기
select mem_name as 그룹이름 from member;
-- as 생략가능
select addr 주소, debut_date "데뷔 날짜",mem_id from member;
-- mem_name이 블랙핑크인 column의 행을 조회
select * from member where mem_name = '블랙핑크';
-- 멤버넘버가 5보다 큰것을 조회
select * from member where mem_number>5;
-- 키가 162보다 작거나 같은 행들에서 mem_id, mem_name column만 출력.
select mem_id, mem_name from member where height <= 162;
-- and조건 키가 165이상이고, 멤버넘버가 6초과인사람의 mem_name, height, mem_number column만 조회.
select mem_name, height, mem_number from member where height >= 165 and mem_number > 6;
-- or조건 키가 165이상이고, 멤버넘버가 6초과인 사람의 mem_name, height, mem_number의 column만 조회
select mem_name, height, mem_number from member where height >= 165 or mem_number > 6;
-- between~and 평균키 163~165인 회원 조회
select mem_name, height from member where height >=163 and height <=165;
-- 위와 동일
select mem_name, height from member where height between 163 and 165;
-- in()
select mem_name, addr from member where addr = '경기' or addr = '전남' or addr = '경남';
select mem_name, addr from member where addr in('경기', '전남', '경남');
-- like
select * from member where mem_name like '우%';
select * from member where mem_name like '__핑크';
-- 서브 쿼리 select을 한번했을 때 select를 한번 더 사용해 조건을 두는 것.
select mem_name, height from member where height > (select height from member where mem_name = '에이핑크');
-- ---------------------------------------------------------------------------------------------------------
-- 3-2
-- order by절 select문의 정렬을 위한 order by문
-- 기본적으로 오름차순으로 되고, 뒤에 desc를 붙여주면 내림차순으로 변경이 된다.
select mem_id, mem_name, debut_date from member order by debut_date;
select mem_id, mem_name, debut_date from member order by debut_date desc;
-- order by절은 where 뒤에 온 뒤에 정렬을 해주는 것이다.
-- select mem_id, mem_name, debut_date from member order by debut_date where height >= 164;
select mem_id, mem_name, debut_date, height from member where height >= 164 order by debut_date, height ;
-- 키가 164이상인 column의 mem_id,mem_name,debut_date,height를 보여주는데, 키는 내림차순, debut_date는 오름차순으로 정렬
select mem_id, mem_name, debut_date, height from member where height >= 164 order by height desc, debut_date asc;
-- member table의 위쪽 3개부터 보여주기
select * from member limit 3;
-- 3번쨰부터 2개 (3이 스타트순서가 된다)
select mem_name, height from member order by height desc limit 3,2;
-- distinct 요약하고 싶은, 중복을 제거하고 싶은 column의 앞에 붙인다.
select addr from member;
select distinct addr from member;
-- group by 절
-- mem_id를 오름차순으로 정렬하고, mem_id,amount column만 보여주기
select mem_id, amount from buy order by mem_id;
-- group by절로 mem_id열을 그룹화 시키고, amount를 집계함수 sum을 사용해 result 를 나타내준다.
select mem_id, sum(amount) from buy group by mem_id;
-- 앞에서 배운 별칭을 통해 결과를 보기 좋게 수정
select mem_id "회원 아이디", sum(amount) "총 구매 개수" from buy group by mem_id;
-- 회원에 구매한 금액의 총합을 출력, 구매한 금액은 가격(price)*수량(amount)입니다. 합계는 sum()을 사용
select mem_id "회원 아이디", sum(price*amount) "총 구매 금액" from buy group by mem_id;
-- 전체 회원이 구매한 개수(amount)의 평균 구하기.
select avg(amount) "평균 구매 개수" from buy;
-- 각 회원이 한번 구매 시 평균 몇개를 구매했는지 구하기. 회원(mem_id)별로 구해야 하기 때문에 group by를 사용.
select mem_id, avg(amount) "평균 구매 개수" from buy group by mem_id;
-- 이번에는 회원 테이블(member)에서 연락처가 있는 회원의 수를 카운트
select count(*) from member;
-- having절
select mem_id "회원 아이디" ,sum(price*amount) "총 구매 금액" from buy group by mem_id;
-- 결과 중에 총 구매 금액 1000이상의 회원에게만 사은품 증정시 조건식
-- select mem_id "회원 아이디" ,sum(price*amount) "총 구매 금액" from buy where sum(price*amount) > 1000; group by mem_id;
SELECT
mem_id '회원 아이디',
SUM(price * amount) '총 구매 금액'
FROM
buy
GROUP BY mem_id
HAVING SUM(price * amount) > 1000;
-- 총 구매액이 큰 것부터 나타내려면 내림차순을 사용하면 됩니다.
SELECT
mem_id '회원 아이디',
SUM(price * amount) '총 구매 금액'
FROM
buy
GROUP BY mem_id
HAVING SUM(price * amount) > 1000
ORDER BY SUM(price * amount) DESC;
