mysql 循环建库_【MySQL必知必会01】建库-建表-数据准备

在这里提供一下【MySQL必知必会】系列学习所需要的表、数据，以供大家学习参考。

创建数据库：

#######Create customers table#######

create database crashcourse；

创建表：

#######首先选择当前数据库#######

use crashcourse；

#######Create customers table#######

CREATE TABLE customers(

cust_id      int      NOT NULL AUTO_INCREMENT,

cust_name    char(50)  NOT NULL ,

cust_address char(50)  NULL ,

cust_city    char(50)  NULL ,

cust_state  char(5)  NULL ,

cust_zip    char(10)  NULL ,

cust_country char(50)  NULL ,

cust_contact char(50)  NULL ,

cust_email  char(255) NULL ,

PRIMARY KEY (cust_id)

) ENGINE=InnoDB;

#######Create orderitems table#######

CREATE TABLE orderitems(

order_num  int          NOT NULL ,

order_item int          NOT NULL ,

prod_id    char(10)    NOT NULL ,

quantity  int          NOT NULL ,

item_price decimal(8,2) NOT NULL ,

PRIMARY KEY (order_num, order_item)

) ENGINE=InnoDB;

#######Create orders table#######

CREATE TABLE orders(

order_num  int      NOT NULL AUTO_INCREMENT,

order_date datetime NOT NULL ,

cust_id    int      NOT NULL ,

PRIMARY KEY (order_num)

) ENGINE=InnoDB;

#######Create products  table#######

CREATE TABLE products(

prod_id    char(10)      NOT NULL,

vend_id    int          NOT NULL ,

prod_name  char(255)    NOT NULL ,

prod_price decimal(8,2)  NOT NULL ,

prod_desc  text          NULL ,

PRIMARY KEY(prod_id)

) ENGINE=InnoDB;

#######Create vendors table#######

CREATE TABLE vendors(

vend_id      int      NOT NULL AUTO_INCREMENT,

vend_name    char(50) NOT NULL ,

vend_address char(50) NULL ,

vend_city    char(50) NULL ,

vend_state  char(5)  NULL ,

vend_zip    char(10) NULL ,

vend_country char(50) NULL ,

PRIMARY KEY (vend_id)

) ENGINE=InnoDB;

#######Create productnotes table#######

CREATE TABLE productnotes(

note_id    int          NOT NULL AUTO_INCREMENT,

prod_id    char(10)      NOT NULL,

note_date datetime      NOT NULL,

note_text  text          NULL ,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=MyISAM;

#######Define foreign keys#######

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);