21.3.12 / SQL / SQL 실습

pjk·2021년 3월 12일

mysql sql 매일코딩

[매일코딩 스터디]

목록 보기

39/62

Today

강의

MySQL 데이터베이스 한번에 끝내기 SQL Full Tutorial Course using MySQL Database (6회차 half)

스터디 내용

sql 실습
인프런 sql gmarket data 분석

결과

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;