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

数据准备：

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

# 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

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

INSERT 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');

INSERT 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');

INSERT 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');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT 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');

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)

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

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

# Populate orders table

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

INSERT INTO orders(order_num, order_date, cust_id)

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

INSERT INTO orders(order_num, order_date, cust_id)

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

INSERT INTO orders(order_num, order_date, cust_id)

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

INSERT INTO orders(order_num, order_date, cust_id)

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

INSERT INTO orders(order_num, order_date, cust_id)

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

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

# Populate orderitems table

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

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20005, 1, 'ANV01', 10, 5.99);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20005, 2, 'ANV02', 3, 9.99);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20005, 3, 'TNT2', 5, 10);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20005, 4, 'FB', 1, 10);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20006, 1, 'JP2000', 1, 55);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20007, 1, 'TNT2', 100, 10);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20008, 1, 'FC', 50, 2.50);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20009, 1, 'FB', 1, 10);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20009, 2, 'OL1', 1, 8.99);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20009, 3, 'SLING', 1, 4.49);

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20009, 4, 'ANV03', 1, 14.99);

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

# Populate productnotes table

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

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(101, 'TNT2', '2005-08-17',

'Customer complaint:

Sticks not individually wrapped, too easy to mistakenly detonate all at once.

Recommend individual wrapping.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(102, 'OL1', '2005-08-18',

'Can shipped full, refills not available.

Need to order new can if refill needed.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(103, 'SAFE', '2005-08-18',

'Safe is combination locked, combination not provided with safe.

This is rarely a problem as safes are typically blown up or dropped by customers.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(104, 'FC', '2005-08-19',

'Quantity varies, sold by the sack load.

All guaranteed to be bright and orange, and suitable for use as rabbit bait.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(105, 'TNT2', '2005-08-20',

'Included fuses are short and have been known to detonate too quickly for some customers.

Longer fuses are available (item FU1) and should be recommended.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(106, 'TNT2', '2005-08-22',

'Matches not included, recommend purchase of matches or detonator (item DTNTR).'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(107, 'SAFE', '2005-08-23',

'Please note that no returns will be accepted if safe opened using explosives.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(108, 'ANV01', '2005-08-25',

'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(109, 'ANV03', '2005-09-01',

'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(110, 'FC', '2005-09-01',

'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(111, 'SLING', '2005-09-02',

'Shipped unassembled, requires common tools (including oversized hammer).'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(112, 'SAFE', '2005-09-02',

'Customer complaint:

Circular hole in safe floor can apparently be easily cut with handsaw.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(113, 'ANV01', '2005-09-05',

'Customer complaint:

Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'

);

INSERT INTO productnotes(note_id, prod_id, note_date, note_text)

VALUES(114, 'SAFE', '2005-09-07',

'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.

Comment forwarded to vendor.'

);

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,602评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,442评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,878评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,306评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,330评论 5赞 373
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,071评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,382评论 3赞 400
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,006评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,512评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,965评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,094评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,732评论 4赞 323
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,283评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,286评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,512评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,536评论 2赞 354
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,828评论 2赞 345

推荐阅读更多精彩内容