/*
* ν
μ΄λΈ
* user
* persistent_logins
* product
* product_io
* order
*/
-- μΈλν€ μ μ½μ‘°κ±΄ μμ
--ALTER TABLE product_io DROP FOREIGN KEY product_id_FK;
--ALTER TABLE product_io DROP FOREIGN KEY order_no_FK;
-- νμ ν
μ΄λΈ
DROP TABLE IF EXISTS `user` CASCADE;
create table `user` (
id varchar(10) not null COMMENT 'νμ μμ΄λ', -- νμ μμ΄λ
password varchar(10) not null COMMENT 'λΉλ°λ²νΈ', -- λΉλ°λ²νΈ
name varchar(10) not null COMMENT 'μ΄λ¦', -- μ΄λ¦
gender varchar(5) COMMENT 'μ±λ³', -- μ±λ³
birth varchar(10) COMMENT 'μμΌ', -- μμΌ
mail varchar(30) COMMENT 'λ©μΌ', -- λ©μΌ
phone varchar(20) COMMENT 'μ νλ²νΈ', -- μ νλ²νΈ
address varchar(90) COMMENT 'μ£Όμ', -- μ£Όμ
regist_day timestamp default now() COMMENT 'κ°μ
μΌμ', -- κ°μ
μΌμ
primary key(id)
) COMMENT 'νμ';
INSERT INTO `user` (id, password, name) VALUES ('joeun', '123456', 'κΉμ‘°μ');
-- μλ λ‘κ·ΈμΈ ν
μ΄λΈ
DROP TABLE IF EXISTS `persistent_logins` CASCADE;
CREATE TABLE `persistent_logins` (
`p_no` int NOT NULL AUTO_INCREMENT COMMENT 'μλ λ‘κ·ΈμΈ λ²νΈ',
`user_id` varchar(255) NOT NULL COMMENT 'νμ μμ΄λ',
`token` varchar(255) NOT NULL COMMENT 'ν ν°',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'λ±λ‘μΌμ',
PRIMARY KEY (`p_no`),
UNIQUE KEY `unique_token` (`token`)
) COMMENT 'μλ λ‘κ·ΈμΈ';
-- μν ν
μ΄λΈ
DROP TABLE IF EXISTS `product` CASCADE;
CREATE TABLE product (
product_id varchar(100) NOT NULL COMMENT 'μνID',
name varchar(100) NOT NULL COMMENT 'μνλͺ
',
unit_price INT NULL COMMENT 'κ°κ²©(λ¨κ°)',
description TEXT NULL COMMENT 'μ€λͺ
',
manufacturer varchar(100) NULL COMMENT 'μ μ‘°μ
체',
category varchar(100) NULL COMMENT 'μΉ΄ν
κ³ λ¦¬',
units_in_stock INT DEFAULT 0 NOT NULL COMMENT 'μ¬κ³ μ',
`condition` varchar(100) NULL COMMENT 'μν',
file TEXT NULL COMMENT 'νμΌκ²½λ‘',
quantity INT DEFAULT 0 NOT NULL COMMENT 'μ₯λ°κ΅¬λ κ°μ',
CONSTRAINT product_pk PRIMARY KEY (product_id)
) COMMENT 'μν';
INSERT INTO product
(product_id, name, unit_price, description, manufacturer, category, units_in_stock, `condition`, file)
VALUES('P100001', 'μλ° νλ‘κ·Έλλ°', 50000, 'μλ
νμΈμ μλ°νλ‘κ·Έλλ° κ°μμ
λλ€.', 'μλ‘νν΄λμ€', 'κ°μ', 100, 'NEW', '/static/img/JAVA.jpg');
INSERT INTO product
(product_id, name, unit_price, description, manufacturer, category, units_in_stock, `condition`, file)
VALUES('P100002', 'μ€λΌν΄ λ°μ΄ν°λ² μ΄μ€', 20000, 'μ€λΌν΄ λ°μ΄ν°λ² μ΄μ€ μ
λλ€.', 'μλ‘νν΄λμ€', 'κ°μ', 100, 'NEW', '/static/img/DB.jpg');
INSERT INTO product
(product_id, name, unit_price, description, manufacturer, category, units_in_stock, `condition`, file)
VALUES('P100003', 'HTML CSS JAVASCRIPT', 15000, 'μΉ κΈ°μ΄ κ°μμ
λλ€.', 'μλ‘νν΄λμ€', 'κ°μ', 100, 'NEW', '/static/img/WEB.jpg');
INSERT INTO product
(product_id, name, unit_price, description, manufacturer, category, units_in_stock, `condition`, file)
VALUES('P100004', 'JSP', 80000, 'JSP κ°μμ
λλ€.', 'μλ‘νν΄λμ€', 'κ°μ', 100, 'NEW', '/static/img/JSP.jpg');
-- μ£Όλ¬Έ ν
μ΄λΈ
DROP TABLE IF EXISTS `order` CASCADE;
CREATE TABLE `order` (
`order_no` int NOT NULL AUTO_INCREMENT COMMENT 'μ£Όλ¬Έλ²νΈ',
`ship_name` varchar(100) NOT NULL COMMENT 'λ°λμ¬λμ΄λ¦',
`zip_code` varchar(100) NOT NULL COMMENT 'μ°νΈλ²νΈ',
`country` varchar(100) NOT NULL COMMENT 'κ΅κ°',
`address` varchar(200) NOT NULL COMMENT 'μ£Όμ',
`date` varchar(200) NOT NULL COMMENT 'λ°°μ‘μΌμ',
`order_pw` varchar(200) NULL COMMENT 'μ£Όλ¬Έ λΉλ°λ²νΈ',
`user_id` varchar(10) DEFAULT NULL COMMENT 'νμμμ΄λ',
`total_price` int DEFAULT '0' COMMENT 'μ΄κ°κ²©',
`phone` varchar(20) DEFAULT NULL COMMENT 'λΉνμ μ νλ²νΈ',
PRIMARY KEY (`order_no`)
) COMMENT='μ£Όλ¬Έ';
-- μ
μΆκ³ ν
μ΄λΈ
DROP TABLE IF EXISTS `product_io` CASCADE;
CREATE TABLE `product_io` (
`io_no` int NOT NULL AUTO_INCREMENT COMMENT 'μ
μΆκ³ λ²νΈ',
`product_id` varchar(100) NOT NULL COMMENT 'μνμμ΄λ',
`order_no` int DEFAULT NULL COMMENT 'μ£Όλ¬Έλ²νΈ',
`amount` int DEFAULT NULL COMMENT 'μ
μΆκ³ λ',
`type` varchar(100) DEFAULT NULL COMMENT 'μ
κ³ (IN),μΆκ³ (OUT)',
`io_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'μ
μΆκ³ λ μ§',
`user_id` varchar(10) DEFAULT NULL COMMENT 'νμμμ΄λ',
PRIMARY KEY (`io_no`),
KEY `product_id_FK` (`product_id`),
KEY `product_id_FK_1` (`order_no`),
CONSTRAINT `product_id_FK` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`),
CONSTRAINT `order_no_FK` FOREIGN KEY (`order_no`) REFERENCES `order` (`order_no`)
) COMMENT='μν μ
μΆκ³ ';
SQL
볡μ¬