mysqllong类型建表语句

供应商供货目录表

CREATE TABLE products

(

prod_id ｃｈａｒ(10) NOT NULL,

vend_id int NOT NULL ,

prod_name ｃｈａｒ(255) NOT NULL ,

prod_price decimal(8,2) NOT NULL ,

prod_desc text NULL ,

PRIMARY KEY(prod_id)

)

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘ANV01‘, 1001, ‘.5 ton anvil‘, 5.99, ‘.5 ton anvil, black, complete with handy hook‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘ANV02‘, 1001, ‘1 ton anvil‘, 9.99, ‘1 ton anvil, black, complete with handy hook and carrying case‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘ANV03‘, 1001, ‘2 ton anvil‘, 14.99, ‘2 ton anvil, black, complete with handy hook and carrying case‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘OL1‘, 1002, ‘Oil can‘, 8.99, ‘Oil can, red‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘FU1‘, 1002, ‘Fuses‘, 3.42, ‘1 dozen, extra long‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘SLING‘, 1003, ‘Sling‘, 4.49, ‘Sling, one size fits all‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘TNT1‘, 1003, ‘TNT (1 stick)‘, 2.50, ‘TNT, red, single stick‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘TNT2‘, 1003, ‘TNT (5 sticks)‘, 10, ‘TNT, red, pack of 10 sticks‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘FB‘, 1003, ‘Bird seed‘, 10, ‘Large bag (suitable for road runners)‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘FC‘, 1003, ‘Carrots‘, 2.50, ‘Carrots (rabbit hunting season only)‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘SAFE‘, 1003, ‘Safe‘, 50, ‘Safe with combination lock‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘DTNTR‘, 1003, ‘Detonator‘, 13, ‘Detonator (plunger powered), fuses not included‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘JP1000‘, 1005, ‘JetPack 1000‘, 35, ‘JetPack 1000, intended for single use‘);

ｉｎｓｅｒｔ INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

VALUES(‘JP2000‘, 1005, ‘JetPack 2000‘, 55, ‘JetPack 2000, multi-use‘);

客户表

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name ｃｈａｒ(50) NOT NULL ,

cust_address ｃｈａｒ(50) NULL ,

cust_city ｃｈａｒ(50) NULL ,

cust_state ｃｈａｒ(5) NULL ,

cust_zip ｃｈａｒ(10) NULL ,

cust_country ｃｈａｒ(50) NULL ,

cust_contact ｃｈａｒ(50) NULL ,

cust_email ｃｈａｒ(255) NULL ,

PRIMARY KEY (cust_id)

)

ｉｎｓｅｒｔ 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‘);

ｉｎｓｅｒｔ 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‘);

ｉｎｓｅｒｔ 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‘);

ｉｎｓｅｒｔ 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‘);

ｉｎｓｅｒｔ 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‘);

订单表

CREATE TABLE orders

(

order_num int NOT NULL AUTO_INCREMENT,

order_date datetime NOT NULL ,

cust_id int NOT NULL ,

PRIMARY KEY (order_num)

)

ｉｎｓｅｒｔ INTO orders(order_num, order_date, cust_id)

VALUES(20005, ‘2005-09-01‘, 10001);

ｉｎｓｅｒｔ INTO orders(order_num, order_date, cust_id)

VALUES(20006, ‘2005-09-12‘, 10003);

ｉｎｓｅｒｔ INTO orders(order_num, order_date, cust_id)

VALUES(20007, ‘2005-09-30‘, 10004);

ｉｎｓｅｒｔ INTO orders(order_num, order_date, cust_id)

VALUES(20008, ‘2005-10-03‘, 10005);

ｉｎｓｅｒｔ INTO orders(order_num, order_date, cust_id)

VALUES(20009, ‘2005-10-08‘, 10001);

另外，往期相关文章链接如下：