MySQL 테이블 생성하기

테이블 만들기

• 테이블 생성, 삭제와 데이터 삽입

  -- INSERT, SELECT, UPDATE, DELETE : DML
  -- CREATE(테이블 생성), DROP(테이블 삭제), ALTER(테이블 수정) : DDL
  -- 상품 테이블 만들기
  -- 데이터 베이스는 카멜 표기법이 불가능
  -- 컬럼 이름 데이터타입(바이트 수)
  -- AUTO_INCREMENT 는 자동으로 PK값을 증가 시켜줌
  -- 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 '출하가',
  	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(5000, now(),3000, 4)
INSERT INTO products (product_price, create_at, shipment_price, brand_id)
values(9000, now(),7000, 5)

SELECT p.product_price, p.shipment_price, p.create_at, b.brand_name FROM products AS p INNER JOIN brand AS b ON p.brand_id = b.brand_id
ORDER BY p.create_at