MySql 맛보기

SELECT * FROM shop_db.product;
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('바나나', '1500', '2021-07-01', '델몬트', '17');
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('카스', '2500', '2022-03-01', 'OB', '3');
INSERT INTO `shop_db`.`product` (`product_name`, `cost`, `make_date`, `company`, `amount`) VALUES ('삼각김밥', '800', '2023-09-01', 'CJ', '22');
DELETE FROM `shop_db`.`product` WHERE (`product_name` = '삼각김밥');
UPDATE `shop_db`.`product` SET `amount` = '5' WHERE (`product_name` = '카스');

select product_name, cost
from product
where product_name = '삼각김밥';

create database marketdb;	--	create marketdb
use marketdb;				--	marketdb open
create table member (		-- craete member table
	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
	);
select * from marketdb.member;	-- show marketdb.member

집가서 정리해야하는 부분

create database marketdb;	--	create marketdb
use marketdb;				--	marketdb open
create table member (		-- craete member table
	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
	);
select * from marketdb.member;	-- show marketdb.member

insert into member values ('TWC', '트와이스', 9, '서울', '02', '11111111', '165', '2015.10.19');
INSERT INTO `marketdb`.`member` (`mem_id`, `mem_name`, `mem_number`, `addr`, `phone1`, `phone2`, `height`, `debut_date`) VALUES ('BTS', '방탄소년단', '6', '서울', '02', '22222222', '180', '2021-07-01');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');

create table buy (
	num		int not null primary key auto_increment,
    mem_id	char(8) not null,
    prod_name	char(6) not null,
    group_name	char(4),
    price	int not null,
    amount	smallint not null,
    foreign key (mem_id) references member(mem_id)
    );

select * from buy;
select * from member;

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);

select mem_name, mem_id, addr from member;
select addr 주소, debut_date "데뷔 일자", mem_name 이름 from member;
select * from member where mem_name = '블랙핑크' or mem_name = '소녀시대';
select * from member where mem_number = 4;
select * from member where height <= 162;
select * from member where height >= 165 or mem_number > 6;
select * from member where height >= 163 and height <= 165;
select * from member where height between 163 and 165;
select * from member where addr = '경기' or addr = '전남' or addr = '경남';
select * from member where addr in ('경기', '전남', '경남');
select * from member where mem_name like '%크%';
select * from member where mem_name like '__핑크';

select height 키 from member where mem_name = '에이핑크';
select mem_name 이름, height 키 from member where height > 167;
select mem_name 이름, height 키 from member where height > (
	select * from member where mem_number = 4
    );-- 두개 이상의 리턴으로 에러
    
select mem_name, height from member where height = any (
	select height from member where addr = '경남'
    );
    
select mem_name, height from member where height >= (
	select height from member where addr = '경남'
    );
    
select mem_name, height from member where height 
	in (select height from member where addr = '경남');
    
select mem_name, height from member where height > all (
select height from member where addr = '경남'
);

select mem_name from member order by mem_name;
select mem_name, debut_date from member order by debut_date;
select * from member where addr = '경남' order by mem_name desc;
select * from member order by debut_date desc;
select mem_id '아이디', mem_name '이름', debut_date '데뷔 일', height 키 from member where height >= 162 order by debut_date desc ;

select * from member where height >= 164 order by height desc, debut_date asc;

select * from member height limit 3, 2;


select distinct addr from member order by addr;

create table memTble (
	select mem_id, mem_name, addr from member
	);

create table memmemtblememtbleemem_idTblee (
	select mem_name, addr from member
	);
    
select * from buy;

select mem_id, amount from buy order by mem_id;
select mem_id, sum(price) from buy group by mem_id;
select prod_name '회원 아이디', sum(price*amount) '총 구매 금액' from buy group by mem_id;
select mem_id '회원 아이디', avg(price * amount) '총 구매 금액' from buy group by mem_id;
select avg(amount) 전체평균구매횟수 from buy;

select mem_id 회원아이디, avg(amount) 평균구매갯수 from buy group by mem_id;
select count(*) from member;
select count(phone1) from member;
select * from buy;
select mem_name, max(height), min(height) from member group by mem_name;

select mem_name, height 
from member 
where height = (select max(height) from member) or height = (select min(height) from member);
select mem_name, max(height), min(height) from member;

-- 회원별 총 구매 금액
select mem_id '회원 아이디', sum(price * amount) '총 구매 금액' from buy 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;

select num, group_name, sum(price * amount) '총 구매 금액' 
from buy 
group by num, group_name
with rollup;

create table hongong1(
	toy_id int, 
    toy_name char(4),
    age int
    );
    
insert into hongong1(toy_id, toy_name) values (2, '버즈');
select * from hongong1;

insert into hongong1(age, toy_name, toy_id) values (25, '제시', 3);
insert into hongong1 values (4, '우디', 20);
insert into hongong1 values (5, '시드', null);

-- auto increment에서 number조회
select last_insert_id();

alter table buy auto_increment = 100;
insert into buy values (NULL, 'BLK', '맥북프로s', '디지털', 10000, 1);
select * from buy;
select last_insert_id();
set @@auto_increment_increment = 3;

insert into hongong1 values (6, '버즈2', 24),(7, '우디2', 20);

select count(*) from world.city;
select count(*) from world.countrylanguage;

select * from world.city limit 5;

create table city_popul(
	city_name	char(35),
    popul		int
    );

insert into city_popul
select name, population 
from world.city;

select * from city	_popul;

use marketdb;

update city_popul
set city_name = '서울'
where city_name = 'Seoul';

select * from city_popul where city_name  = '서울';

update city_popul
set popul = 0
where city_name = '뉴요크';

select * from city_popul where city_name = '뉴요크';

update city_popul
set popul = popul / 10000;

delete from city_popul where city_name = '뉴요크';

select * from city_popul;

delete from city_popul where city_name like  'New%';

delete from city_popul where city_name = 'Qandahar' limit 5;

delete from city_popul where popul = 15 limit 3;