수업정리(2022.07.05)-MySQL order by, join, sum, count, group by 및 응용

개발/Database

수업정리(2022.07.05)-MySQL order by, join, sum, count, group by 및 응용

eeee 2022. 7. 5. 20:10

자료 보고 직접 테이블 만들기

create table pay(

pay_no int(8) not null auto_increment,

pay_place varchar(100) not null,

pay_code varchar(250) not null,

pay_time datetime not null default '0001-01-01 01:00:00',

pay_card varchar(50) not null,

pay_monthly char(30) not null,

pay_cost bigint not null,

pay_recipt char(5) not null,

primary key(pay_no)

);

create table pay(

pidx int(8) not null auto_increment,

parea char(100) not null,

pno varchar(13) not null,

pdate datetime not null default '0001-01-01 01:00:00',

pcard char(100) not null,

pm char(40) not null,

money int(7) not null,

presult enum('Y', 'N') not null default 'Y',

primary key(pidx)

);

※테이블 생성 시 상품코드 같은경우는 숫자로 구성되어있더라도 int같이 숫자형으로 설정하면 안됨( 맨앞에 0 이들어갈수있어서), char, varchar같이 문자형 사용할것.

테이블 정렬

오름차순 : select * from tablename order by fieldname asc;

내림차순 : select * from tablename order by fieldname desc;

order by - 정렬기준

group by 필드명 : 해당 필드값이 같은 것 끼리 모아서 추출- sum, count 등의 명령어 사용 가능

해당 같은 이름을 가진 그룹별로 각각 값을 합친 데이터를 출력하는 sql 명령어

select 필드명, sum(필드명) as 가상의 필드명 from 테이블명 group by 필드명;

같은이름가진 그룹의 갯수 세는 명령어

select 필드명, count(*) as 가상의 필드명 from 테이블명 group by 필드명;

mysql> select pcard, sum(money) as totalmoney, count(*) as number from pay group by parea;

카드사별 결제 건수

select pcard, count(*)as cno from pay group by pcard;

새로운 테이블 생성-join에사용

create table product(

pd_no int(8) not null auto_increment,

pd_code char(13) not null,

pd_name varchar(250) not null,

pd_cost int(7) not null,

pd_result enum('Y' , 'N') not null default 'Y',

primary key(pd_no)

);

insert into product values ('0','9203101937988','토니모리 원더 세라미이드 500ml','37000','Y');

insert into product values('0','0612329255417','토니모리 토니 랩 에이씨 컨트롤 500ml','19000','Y');

insert into product values('0','9210233222339','토니모리 원더 퀵 카밍 토너 4종 세트','118000','Y');

insert into product values('0','3611264692414','토니모리 토니 랩 에이씨 컨트롤 5종세트','248000','Y');

insert into product values('0','2456781123245','토니모리 토니 랩 에이씨 컨트롤 토너 180ml','154800','Y');

insert into product values('0','5678818234496','토니모리 알로에 99% 수딩젤 250ml','24000','Y');

insert into product values('0','7656462265410','토니모리 어성초시카 퀵 카밍 패드 200ml 4개','61000','Y');

insert into product values('0','1263656514459','토니모리 어성초시카 퀵 카밍 수딩 젤 300ml','76000','Y');

insert into product values('0','8766545233212','토니모리 슈퍼 인텐스 골드 24K 140ml','58900','Y');

insert into product values('0','7612323337489','토니모리 바이오이엑스 골드 솔루션 토너 130ml','32500','Y');

insert into product values('0','9331312245524','토니모리 바이오이엑스 골드 기초화장품 2종 세트','228700','Y');

테이블 join

select a.필드명,b.필드명 from 본테이블 as a별명명칭 join 비교테이블 as b별명명칭 on a별명명칭.필드명 = b별명명칭.필드명 -> from뒤에 쓴 테이블 기준으로 데이터 가져옴 , on 비교

select p.pno,z.pd_name,p.parea,p.pdate,p.money from pay as p join product as z on p.pno=z.pd_code;

응용문제) 상품 코드명 9203101937988 해당사항에 대한 총 매출 금액을 출력. 출력방식은 상품코드, 상품명, 총판매금액

mysql> select p.pno,z.pd_name,sum(p.money) from pay as p join product as z on p.pno=z.pd_code where p.pno='9203101937988';

응용문제)각 상품별 판매 갯수를 출력하시오.

상품 코드, 상품명, 판매갯수

select p.pno,z.pd_name,count(*) from pay as p join product as z on p.pno=z.pd_code group by z.pd_name;

수량으로 정렬하려면 count에도 이름을 붙여주면 가능.

응용 문제) 5월 22일 날짜에 대한 판매 상품을 확인하고 싶어합니다.

상품코드, 상품명, 판매점,판매일자, 판매금액

select p.pno, z.pd_name, p.parea, p.money from pay as p join product as z on p.pno=z.pd_code where p.pdate>="2022-05-22 00:00:00" and p.pdate<="2022-05-22 23:59;59";

테이블 3개 만들기

members payok points

create table members(

m_no int(8) not null auto_increment,

m_id varchar(100) not null,

m_name varchar(100) not null,

m_phone varchar(13) not null,

m_email varchar(200) not null,

m_date datetime not null default '0001-01-01 01:00:00',

m_area varchar(50) not null,

primary key(m_no, m_id),

unique key(m_id)

);

create table payok(

pa_no int(8) not null auto_increment,

pa_code varchar(8) not null,

pa_product tinytext not null,

pa_id varchar(200) not null,

pa_cost int(7) not null,

pa_card varchar(200) not null,

pa_result enum('Y','N') not null default 'Y',

primary key(pa_no)

);

create table points(

po_no int(8) not null auto_increment,

po_id varchar(100) not null,

po_point int(6) not null,

po_use enum('Y','N') not null default 'Y',

po_date datetime not null default '0001-01-01 01:00:00',

primary key(po_no)

);

point테이블에서 적립포인트와 사용포인트를 한번에 보도록 추출

select sum(po_point),(select sum(po_point) from points where po_id='kmin_lee' and po_use='N')as usepoint from points where po_id='kmin_lee' and po_use='Y';

응용문제)조기현님 포인트 적립 및 사용금액을 출력하세요. 아이디, 적립금액, 사용금액

1.아이디 먼저 검색

select m_id from members where m_name = '조기현';

2.테이블 조인 (members +points)

select * from members as m join points as p where m.m_name='조기현';

-> 비교를안해서 데이터가 많이 나옴

3. 멤버테이블의 id필드 와 포인트 테이블의 id필드 비교해서 같은값만 출력

select * from members as m join points as p on m.m_id =p.po_id where m.m_name='조기현';

4. 사용한 포인트 확인

select p.po_id, sum(p.po_point) from members as m join points as p on m.m_id = p.po_id where m.m_name='조기현' and po_use='Y';

5. 사용한 포인트와 적립된 포인트 확인

select p.po_id, sum(p.po_point) as plus_point, (select sum(p.po_point) from members as m join points as p on m.m_id = p.po_id where m.m_name ='조기현' and p.po_use ='N') as loss_point from members as m join points as p on m.m_id = p.po_id where m.m_name ='조기현' and p.po_use ='Y';

>내가 풀어본 방법

select (select m_id from members where m_name='조기현') as id, sum(po_point) as savepoint, (select sum(po_point) from points where po_id='kihyun_top' and po_use='N')as usepoint from points where po_id='kihyun_top' and po_use='Y';