mysql: create database

標籤：sql   enum   upd   idf   select   ble   sel   values   ase   

/***************************************  *        DDL: Create Database         *  ***************************************/-- drop existing database if exists;DROP DATABASE IF EXISTS fitbit_new;-- create database;CREATE DATABASE fitbit_new;-- use database;USE fitbit_new;show tables;/***************************************  *           DDL: Create Table         *  ***************************************//*        product          */-- create table product;DROP TABLE IF EXISTS product, client, sales;describe sales;-- alter table;/*       client      */-- create table client;describe client;/*        sales         */-- create table sales;-- create foreign key seperately;alter table sales addconstraint fk_client_id_wanmenforeign key (client_id)    references client (client_id)    on delete cascade;-- drop foreign key;alter table sales dropforeign key fk_client_id_wanmen;-- add update cascade;alter table sales dropforeign key fk_client_id;alter table sales dropforeign key fk_product_id;alter table sales addconstraint fk_product_idforeign key (product_id)    references product (product_id)    on delete cascade    on update cascade;alter table sales addconstraint fk_client_idforeign key (client_id)    references client (client_id)    on delete cascade    on update cascade;-- create view;  /**********************************************  *        DML: Insert, Update, Delete         *  **********************************************/INSERT INTO product VALUES(1,‘E-ZIP‘,‘Zip‘,‘EVERYDAY‘,‘GREEN‘,‘Y‘,59.95),(2,‘E-FLX‘,‘Flex‘,‘EVERYDAY‘,‘BLACK‘,‘N‘,99.95),(3,‘A-BLZ‘,‘Blaze‘,‘ACTIVE‘,‘PURPLE‘,‘Y‘,199.95),(4,‘P-SUG‘,‘Surge‘,‘PERFORMANCE‘,‘BLACK‘,‘Y‘,249.95);select * from product;-- update product;update productset product_id = 200where product_id = 2;-- cascade deletedelete from productwhere product_id = 200;INSERT INTO sales (tran_id, date, product_id, client_id, price, quantity)VALUES(1,‘2016-6-1‘, 1, 1, 40, 10),(2,‘2016-6-5‘, 1, 2, 30, 5),(3,‘2016-6-8‘, 2, 1, 80, 8),(4,‘2016-6-8‘, 2, 2, 70, 7),(5,‘2016-6-13‘, 3, 2, 150, 5),(6,‘2016-6-18‘, 3, 4, 150, 10),(7,‘2016-6-20‘, 2, 4, 40, 15); select * from sales;-- update sales;update salesset date = ‘2016-06-21‘,price = 200where date = ‘2016-06-20‘ and tran_id = 7;INSERT INTO client VALUES(1,‘FITBIT‘,‘ONLINE‘),(2,‘AMAZON‘,‘ONLINE‘),(3,‘BESTBUY‘,‘OFFLINE‘),(4,‘WALMART‘,‘OFFLINE‘);select * from client;-- create table SHIPPING;DROP TABLE IF EXISTS shipping;create table if not exists shipping(shipping_id int not null,    tran_id int not null,    tracking_no int,    status enum(‘preparing‘, ‘shipped‘, ‘arrived‘),    arrive_date date,    etadate,    primary key (tran_id),    constraint fk_tran_idforeign key (tran_id)    references sales (tran_id)    on delete cascade    on update cascade);select * from shipping; 

　　

/***************************************  *        DDL: Create Database         *  ***************************************/
-- drop existing database if exists;DROP DATABASE IF EXISTS fitbit_new;
-- create database;CREATE DATABASE fitbit_new;
-- use database;USE fitbit_new;show tables;
/***************************************  *           DDL: Create Table         *  ***************************************/
/*        product          */-- create table product;DROP TABLE IF EXISTS product, client, sales;



describe sales;-- alter table;


/*       client      */-- create table client;


describe client;

/*        sales         */-- create table sales;

-- create foreign key seperately;alter table sales addconstraint fk_client_id_wanmenforeign key (client_id)    references client (client_id)    on delete cascade;
-- drop foreign key;alter table sales dropforeign key fk_client_id_wanmen;
-- add update cascade;alter table sales dropforeign key fk_client_id;alter table sales dropforeign key fk_product_id;alter table sales addconstraint fk_product_idforeign key (product_id)    references product (product_id)    on delete cascade    on update cascade;alter table sales addconstraint fk_client_idforeign key (client_id)    references client (client_id)    on delete cascade    on update cascade;
-- create view;

  /**********************************************  *        DML: Insert, Update, Delete         *  **********************************************/
INSERT INTO product VALUES(1,‘E-ZIP‘,‘Zip‘,‘EVERYDAY‘,‘GREEN‘,‘Y‘,59.95),(2,‘E-FLX‘,‘Flex‘,‘EVERYDAY‘,‘BLACK‘,‘N‘,99.95),(3,‘A-BLZ‘,‘Blaze‘,‘ACTIVE‘,‘PURPLE‘,‘Y‘,199.95),(4,‘P-SUG‘,‘Surge‘,‘PERFORMANCE‘,‘BLACK‘,‘Y‘,249.95);select * from product;
-- update product;update productset product_id = 200where product_id = 2;
-- cascade deletedelete from productwhere product_id = 200;


INSERT INTO sales (tran_id, date, product_id, client_id, price, quantity)VALUES(1,‘2016-6-1‘, 1, 1, 40, 10),(2,‘2016-6-5‘, 1, 2, 30, 5),(3,‘2016-6-8‘, 2, 1, 80, 8),(4,‘2016-6-8‘, 2, 2, 70, 7),(5,‘2016-6-13‘, 3, 2, 150, 5),(6,‘2016-6-18‘, 3, 4, 150, 10),(7,‘2016-6-20‘, 2, 4, 40, 15); select * from sales;-- update sales;update salesset date = ‘2016-06-21‘,price = 200where date = ‘2016-06-20‘ and tran_id = 7;


INSERT INTO client VALUES(1,‘FITBIT‘,‘ONLINE‘),(2,‘AMAZON‘,‘ONLINE‘),(3,‘BESTBUY‘,‘OFFLINE‘),(4,‘WALMART‘,‘OFFLINE‘);select * from client;-- create table SHIPPING;DROP TABLE IF EXISTS shipping;
create table if not exists shipping(shipping_id int not null,    tran_id int not null,    tracking_no int,    status enum(‘preparing‘, ‘shipped‘, ‘arrived‘),    arrive_date date,    etadate,    primary key (tran_id),    constraint fk_tran_idforeign key (tran_id)    references sales (tran_id)    on delete cascade    on update cascade);
select * from shipping;
 

mysql: create database