《sql必知必会》案例部署脚本.txt

biubiubiu

420

6页

2次

2021-10-28

5墨值下载

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

# MySQL Crash Course

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

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

# MySQL Crash Course

# Example table population scripts

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

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

# Populate customers table

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

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state,

cust_zip, cust_country, cust_contact, cust_email)

VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA',

'Y Lee', 'ylee@coyote.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state,

cust_zip, cust_country, cust_contact)

VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333',

'USA', 'Jerry Mouse');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state,

cust_zip, cust_country, cust_contact, cust_email)

VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim

Jones', 'rabbit@wascally.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state,

cust_zip, cust_country, cust_contact, cust_email)

VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888',

'USA', 'Y Sam', 'sam@yosemite.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state,

cust_zip, cust_country, cust_contact)

VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E

Fudd');

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

# Populate vendors table

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

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state,

vend_zip, vend_country)

VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state,

vend_zip, vend_country)

VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state,

vend_zip, vend_country)

VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state,

vend_zip, vend_country)

VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state,

vend_zip, vend_country)

VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state,

vend_zip, vend_country)

VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

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

# Populate products table

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