MySQL 데이터베이스 한번에 끝내기 SQL Full Tutorial Course using MySQL Database (6회차 half)
use world;
show tables;
show table status;
describe city;
desc city;
select * from city where name in('seoul', 'new york');
select * from city where countrycode in('KOR', 'USA', 'JPN');
select from city where countrycode like 'KO_';
select from city where name like 'tel%';
select from city where countrycode = ( select countrycode from city where name = 'seoul');
select from city where population > any ( select countrycode from city where district = 'new york');
select * from city where population > all ( select countrycode from city where district = 'new york');
drop database pjk;
create database pjk;
use pjk;
create table pjk (
id int unsigned not null auto_increment primary key,
name varchar(40) not null,
number int not null);
desc pjk;
alter table pjk add column test
varchar(40) not null;
alter table pjk modify name varchar(50) not null;
alter table pjk change column name
names varchar(10) not null;
desc pjk;
alter table pjk drop column test;
-- DML : select delete update insert
-- DCL : grant revoke
-- TCL : commit rollback
-- DDL : rename drop alter create
insert into pjk values (1, 'pjk', 5);
select * from pjk;
delete from pjk where id = 1;
use gmarket;
select r.provider count() from ranking r inner join items i on
i.item_code = r.item_code where r.main_category = 'ALL' group by r.provider order by count()
desc;
select * from items;
select max(items.dis_price) from items
where item_code in (select item_code
from ranking where sub_category = '여성신발');
desc ranking;
desc items;
select main_category, items.provider, avg(items.dis_price), avg(discount_change), count() from items inner join ranking on items.item_code = ranking.item_code group by items.provider, ranking.main_category having count() > 20;