美文网首页
【MySQL必知必会01】建库-建表-数据准备

【MySQL必知必会01】建库-建表-数据准备

作者: 菩提狗 | 来源:发表于2018-11-04 10:56 被阅读0次

    在这里提供一下【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.'

    );

    相关文章

      网友评论

          本文标题:【MySQL必知必会01】建库-建表-数据准备

          本文链接:https://www.haomeiwen.com/subject/ohjrxqtx.html