230303

<JOIN>

1. table 만들기

mysql> CREATE TABLE girlgroup (
id VARCHAR(20) NOT NULL PRIMARY KEY,
name VARCHAR(20),
attach VARCHAR(20),
signdate VARCHAR(10)
   );


mysql> CREATE TABLE song (
id VARCHAR(20) NOT NULL PRIMARY KEY,
title VARCHAR(20)
   );

 

2. 내용 insert

INSERT INTO girlgroup VALUES ('11', '블랙핑크','YG','2016-08-08');
INSERT INTO girlgroup VALUES ('22', '트와이스','JYP','2015-10-20');
INSERT INTO girlgroup VALUES ('33', '오마이걸','WM','2015-04-21');
INSERT INTO girlgroup VALUES ('44', '레드벨뱃','SM','2014-08-01');
INSERT INTO girlgroup VALUES ('55', '스테이씨','하이업','2020-01-23');
INSERT INTO girlgroup VALUES ('66', '에스파','SM','2020-11-17');
INSERT INTO girlgroup VALUES ('77', '위클리','플레이엠','2020-06-30');

INSERT INTO song VALUES ('11', '휘파람');
INSERT INTO song VALUES ('22', '우아하게');
INSERT INTO song VALUES ('33', 'CUPID');
INSERT INTO song VALUES ('44', '행복');
INSERT INTO song VALUES ('77', 'GEE');
INSERT INTO song VALUES ('88', '응응');
INSERT INTO song VALUES ('99', '라비앙로즈');

 

3. 조회

<Inner Join>

mysql> select * from girlgroup join song where girlgroup.id = song.id;

 

mysql> select girlgroup.id, girlgroup.name,song.title from girlgroup join song where girlgroup.id = song.id;

 

mysql> select g.id, g.name,g.attach,g.signdate,s.title from girlgroup as g join song as s where g.id = s.id;

 

<Left Join>  --> where 대신에 on 쓰임

mysql> select * from girlgroup as g left join song as s on g.id = s.id;

 

<Right Join> 

mysql> select * from girlgroup as g right join song as s on g.id = s.id;

 

<기타 Join>

mysql> select id, name, (select title from song where id = girlgroup.id) as title from girlgroup;

mysql> select id, name, (select title from song where song.id = girlgroup.id) as title from girlgroup;  // 가독성 높이기

 

 

---

<GROUP BY>  --> 회원별, 날짜별, 월매출, 일매출, point 등등 구할 때 사용

1. table 만들기

mysql> CREATE TABLE member3(
id varchar(20) not null,
pass varchar(20) not null,
name varchar(12) default "",
primary key(id)
   );


mysql> create table points (
memberid varchar(20) NOT NULL,
title varchar(40) NOT NULL,
price int(11),
signdate VARCHAR(10)
   );

 

2. 내용 insert 

insert into member3 values ('1111','1111','유관순');
insert into member3 values ('2222','2222','이순신');
insert into member3 values ('3333','3333','안중근');

insert into points values ('1111','회원가입',1000,'2021-08-13');
insert into points values ('2222','회원가입',1000,'2021-08-13');
insert into points values ('3333','회원가입',1000,'2021-08-14');
insert into points values ('1111','주문',500,'2021-08-15');
insert into points values ('3333','주문',300,'2021-08-15');
insert into points values ('3333','주문',400,'2021-08-16'); 

 

3. 조회

mysql> select memberid, count(*) as count from points group by memberid;  --> 아이디별 point 받은 횟수

 

mysql> select signdate, sum(price) as sum from points group by signdate;  --> 날짜별 가격 총합

 

mysql> select signdate, sum(price) as sum from points group by signdate having signdate >= '2021-08-15';

 

 

mysql> select sum(price),signdate from points group by signdate;  --> 일매출