1017 데이터베이스 정리

💜 오늘 데이터베이스 정리 💜

* INSERT, SELECT, UPDATE, DELETE : DML

* CREATE(테이블 생성), DROP(테이블 삭제), ALTER(테이블 수정) : DDL

* 상품 테이블 만들기

create table products(
product_id int(4) auto_increment primary key,
product_price int(4) comment '물품가',
create_at datetime comment '입고날짜',
shipment_price int(4) comment '출하가',
a_brand_name varchar(30) comment 'A브랜드 이름',
b_brand_name varchar(30) comment 'B브랜드 이름',
c_brand_name varchar(30) comment 'C브랜드 이름'); 

* 상품 테이블에 데이터 넣기

insert into products(product_price,create_at,shipment_price,a_brand_name)
values(3000,now(),5000,'나이키 에어포스')
insert into products(product_price,create_at,shipment_price,b_brand_name)
values(3000,now(),5000,'아디다스')
insert into products(product_price,create_at,shipment_price,a_brand_name,b_brand_name,c_brand_name)
values(3000,now(),5000,'나이키','아디다스','코닥')

 * 상품 테이블 삭제(안에있는 데이터도 모두 사라짐!)

drop table products * 브랜드 테이블 만들기

create table brand(
brand_id int(4) auto_increment primary key,
brand_name varchar(30) comment '브랜드 이름'
) * 상품 테이블 다시 만들기

create table products(
product_id int(4) auto_increment primary key,
product_price int(4) comment '물품가',
create_at datetime comment '입고날짜',
shipment_price int(4) comment '출하가',
brand_id int(4) comment '브랜드번호',
foreign key (brand_id) references brand(brand_id) on delete cascade
); 

* 브랜드 테이블에 값 넣어주기

insert into brand (brand_name) values('나이키')
insert into brand (brand_name) values('아디다스')
insert into brand (brand_name) values('코닥')
insert into brand (brand_name) values('톰브라운')
insert into brand (brand_name) values('버버리') 

* 상품 테이블에 값 넣어주기

insert into products(product_price,create_at,shipment_price,brand_id)
values(5000,now(),3000,1)
insert into products(product_price,create_at,shipment_price,brand_id)
values(6000,now(),4000,2)
insert into products(product_price,create_at,shipment_price,brand_id)
values(7000,now(),5000,3)
insert into products(product_price,create_at,shipment_price,brand_id)
values(8000,now(),6000,4)
insert into products(product_price,create_at,shipment_price,brand_id)
values(9000,now(),7000,5)

 * 상품 물품가, 출하가, 입고날짜, 브랜드명을 조회하고 입고날짜를 내림차순 하시오.

select p.product_price as '상품물품가',p.create_at as '입고날짜',p.shipment_price as '출하가',b.brand_name as '브랜드명'
from products as p
inner join brand as b
on p.brand_id = b.brand_id
order by create_at desc