Mysql:create Database

/*************************************** * 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, Sal Es;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 (Clie NT_ID) references Client (client_id) on delete cascade;--drop foreign key;alter table sales dropforeign key Fk_clie nt_id_wanmen;--Add update cascade;alter table sales dropforeign key Fk_client_id;alter table Sales dropforeign key Fk_pro Duct_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;--CA Scade Deletedelete from Productwhere product_id = 200;insert to Sales (tran_id, date, product_id, client_id, Price, Quan tity) 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;--the 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 Sal  ES (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_idfor Eign Key (tran_id) references sales (tran_id) on the DELETE cascade on UPDATE cascade);
SELECT * from shipping;

Mysql:create Database