mysql必知必会 create_mysql必知必会样例表create.sql

########################################

# MySQL Crash Course

# http://www.forta.com/books/0672327120/

# Example table creation scripts

########################################

########################

# 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);