MySQL必知必会——实践习题

大柠丶

已于 2022-10-08 00:11:01 修改

阅读量1.6k

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库 java

于 2022-10-08 00:05:20 首次发布

本文链接：https://blog.csdn.net/weixin_53364209/article/details/127201184

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、准备工作

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################

DROP DATABASE IF EXISTS crashcourse;

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

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################

USE crashcourse;

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

二、题目

1、显示一个数据库内的表的列表，例如：列出crashcourse数据库内所有的表。

mysql> show tables;
+-----------------------+
| Tables_in_crashcourse |
+-----------------------+
| customers             |
| orderitems            |
| orders                |
| productnotes          |
| products              |
| vendors               |
+-----------------------+

2、使用crashcourse数据库。

mysql> use crashcourse;
Database changed

3、显示MySQL当前可用的数据库的一个列表。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crashcourse        |
| jpa                |
| mvc                |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
| test               |
+--------------------+

4、显示一个表的所有列（属性），例如：列出customers表的所有列的名称、数据类型、主码等信息。

mysql> show columns from customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+

mysql> describe customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+

5、创建一个新的数据库crashcourse2。显示创建数据库crashcourse2的MySQL语句。

mysql> create database if not exists crashcourse2;
Query OK, 1 row affected (0.00 sec)

6、显示创建表customers的MySQL语句。

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

7、创建（克隆）一个和表customers同样的表customers2，并将表customers的全部数据填充到表customers2中。

mysql> create table customers2 like customers;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into customers2 select * from customers;
Query OK, 5 rows affected (0.01 sec)

8、从products表中查询所有产品的id（prod_id），产品名称（prod_name），产品价格（prod_price）。

mysql> select prod_id,prod_name,prod_price
-> from products;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| FC      | Carrots        |       2.50 |
| FU1     | Fuses          |       3.42 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
| OL1     | Oil can        |       8.99 |
| SAFE    | Safe           |      50.00 |
| SLING   | Sling          |       4.49 |
| TNT1    | TNT (1 stick)  |       2.50 |
| TNT2    | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+

9、检索products表中所有的列，即查询所有产品的信息。

mysql> select * from products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+

10、查询products表中产品的供应商id（vend_id），检索结果不重复。

mysql> select distinct vend_id
-> from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+

11、查询products表中前5项产品（第0-4项产品）的所有属性。

mysql> select prod_name
-> from products
-> limit 5;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| Bird seed    |
+--------------+

12、如果查询products表中下一个5项产品，即从第5项产品开始的5项产品的所有属性，又该如何进行？如果查询从第3项产品开始的5项产品的所有属性呢？

mysql> select prod_name
	-> from products
	-> limit 5,5;
+--------------+
| prod_name    |
+--------------+
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Oil can      |
+--------------+

mysql> select prod_name
-> from products
-> limit 3,5;
+--------------+
| prod_name    |
+--------------+
| Detonator    |
| Bird seed    |
| Carrots      |
| Fuses        |
| JetPack 1000 |
+--------------+

13、下列语句的每一个关键词的含义，包括dot小圆点。

mysql> select products.prod_name
-> from crashcourse.products;

从crashcourse数据库中的products表中检索出products表中prod_name列

14、从products表中查询所有产品的id（prod_id），产品名称（prod_name），产品价格（prod_price），对产品名称（prod_name）以英文字母的顺序排列。

mysql> select prod_id,prod_name,prod_price
-> from products
-> order by prod_name;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| FB      | Bird seed      |      10.00 |
| FC      | Carrots        |       2.50 |
| DTNTR   | Detonator      |      13.00 |
| FU1     | Fuses          |       3.42 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
| OL1     | Oil can        |       8.99 |
| SAFE    | Safe           |      50.00 |
| SLING   | Sling          |       4.49 |
| TNT1    | TNT (1 stick)  |       2.50 |
| TNT2    | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+

15、从products表中查询所有产品的id（prod_id），产品名称（prod_name），产品价格（prod_price）。对检索结果先按价格，然后再按名称排序。

mysql> select prod_id,prod_name,prod_price
-> from products
-> order by prod_price,prod_name;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| FC      | Carrots        |       2.50 |
| TNT1    | TNT (1 stick)  |       2.50 |
| FU1     | Fuses          |       3.42 |
| SLING   | Sling          |       4.49 |
| ANV01   | .5 ton anvil   |       5.99 |
| OL1     | Oil can        |       8.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| FB      | Bird seed      |      10.00 |
| TNT2    | TNT (5 sticks) |      10.00 |
| DTNTR   | Detonator      |      13.00 |
| ANV03   | 2 ton anvil    |      14.99 |
| JP1000  | JetPack 1000   |      35.00 |
| SAFE    | Safe           |      50.00 |
| JP2000  | JetPack 2000   |      55.00 |
+---------+----------------+------------+

16、从products表中查询所有产品的id（prod_id），产品名称（prod_name），产品价格（prod_price）。对检索结果按价格降序排列（最贵的排在最前面）。

mysql> select prod_id,prod_name,prod_price
-> from products
-> order by prod_price desc;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| JP2000  | JetPack 2000   |      55.00 |
| SAFE    | Safe           |      50.00 |
| JP1000  | JetPack 1000   |      35.00 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| TNT2    | TNT (5 sticks) |      10.00 |
| ANV02   | 1 ton anvil    |       9.99 |
| OL1     | Oil can        |       8.99 |
| ANV01   | .5 ton anvil   |       5.99 |
| SLING   | Sling          |       4.49 |
| FU1     | Fuses          |       3.42 |
| FC      | Carrots        |       2.50 |
| TNT1    | TNT (1 stick)  |       2.50 |
+---------+----------------+------------+

17、从products表中查询所有产品的id（prod_id），产品名称（prod_name），产品价格（prod_price）。对检索结果先按价格（降序），然后再按名称（升序）排列。

mysql> select prod_id,prod_name,prod_price
-> from products
-> order by prod_price desc,prod_name;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| JP2000  | JetPack 2000   |      55.00 |
| SAFE    | Safe           |      50.00 |
| JP1000  | JetPack 1000   |      35.00 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| TNT2    | TNT (5 sticks) |      10.00 |
| ANV02   | 1 ton anvil    |       9.99 |
| OL1     | Oil can        |       8.99 |
| ANV01   | .5 ton anvil   |       5.99 |
| SLING   | Sling          |       4.49 |
| FU1     | Fuses          |       3.42 |
| FC      | Carrots        |       2.50 |
| TNT1    | TNT (1 stick)  |       2.50 |
+---------+----------------+------------+

18、找出products表中最高的产品价格。然后找出价格最高的那个产品，列出它的产品id（prod_id），产品名称（prod_name），产品价格（prod_price）。注：初学者可以分两步查询。

mysql> select prod_id,prod_name,prod_price
-> from products
-> order by prod_price desc
-> limit 1;
+---------+--------------+------------+
| prod_id | prod_name    | prod_price |
+---------+--------------+------------+
| JP2000  | JetPack 2000 |      55.00 |
+---------+--------------+------------+

19、从products表中查询产品价格等于2.50美元的产品的产品名称（prod_name）和产品价格（prod_price）。

mysql> select prod_name,prod_price
-> from products
-> where prod_price = 2.5;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+

20、从products表中查询产品名称为Fuses的产品的产品名称（prod_name）和产品价格（prod_price）。

mysql> select prod_name,prod_price
-> from products
-> where prod_name = 'Fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+

21、列出价格小于10美元的所有产品的产品名称（prod_name）和产品价格（prod_price）。

mysql> select prod_name,prod_price
	-> from products
	-> where prod_price < 10;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| .5 ton anvil  |       5.99 |
| 1 ton anvil   |       9.99 |
| Carrots       |       2.50 |
| Fuses         |       3.42 |
| Oil can       |       8.99 |
| Sling         |       4.49 |
| TNT (1 stick) |       2.50 |
+---------------+------------+

22、列出不是由供应商1003制造的所有产品的供应商id（vend_id）和产品名称（prod_name）。

mysql> select vend_id,prod_name
	-> from products
	-> where vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
|    1002 | Oil can      |
+---------+--------------+

23、检索价格在5美元和10美元之间的所有产品的产品名称（prod_name）和产品价格（prod_price）。

mysql> select prod_name,prod_price
-> from products
-> where prod_price between 5 and 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Oil can        |       8.99 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

24、检索customers表中电子邮件地址为空的用户的cust_id ，cust_name，cust_contact，cust_email。

mysql> select cust_id,cust_name,cust_contact,cust_email
	-> from customers
	-> where cust_email is null;
+---------+-------------+--------------+------------+
| cust_id | cust_name   | cust_contact | cust_email |
+---------+-------------+--------------+------------+
|   10002 | Mouse House | Jerry Mouse  | NULL       |
|   10005 | E Fudd      | E Fudd       | NULL       |
+---------+-------------+--------------+------------+

25、检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。

mysql> select prod_id,prod_price,prod_name
-> from products
-> where vend_id = 1003 and prod_price <= 10;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FB      |      10.00 | Bird seed      |
| FC      |       2.50 | Carrots        |
| SLING   |       4.49 | Sling          |
| TNT1    |       2.50 | TNT (1 stick)  |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+

26、用OR操作符检索由供应商1002或者1003制造的所有产品的名称和价格。

mysql> select prod_name,prod_price
-> from products
-> where vend_id = 1002 or vend_id = 1003;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

27、检索由供应商1002或者1003制造的且价格都在10美元以上（含10美元）的所有产品的名称和价格。

mysql> select prod_name,prod_price
-> from products
-> where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

28、用IN操作符检索供应商1002和1003制造的所有产品的名称和价格，并按产品名称排序。

mysql> select prod_name,prod_price
-> from products
-> where vend_id = 1002 or vend_id = 1003
-> order by prod_name;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+

29、检索除供应商1002或者1003之外的所有供应商制造的产品的名称和价格，并按产品名称排序。

mysql> select prod_name,prod_price
-> from products
-> where vend_id not in(1002,1003)
-> order by prod_name;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+

30、找出products表中所有以词jet开头的产品的产品id（prod_id）和产品名称（prod_name）。

mysql> select prod_id,prod_name
	-> from products
	-> where prod_name like 'jet%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+

31、在products表中找出产品名称中含有anvil的所有产品的产品id（prod_id）和产品名称（prod_name）。

mysql> select prod_id,prod_name
-> from products
-> where prod_name like '%anvil%';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+

32、找出以s起头、以e结尾的所有产品的产品id（prod_id）和产品名称（prod_name）。

mysql> select prod_id,prod_name
-> from products
-> where prod_name like 's%e';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| SAFE    | Safe      |
+---------+-----------+

33、使用LIKE操作符和下划线（_）通配符，按产品名称检索，获得以下结果。

mysql> select prod_id,prod_name
-> from products
-> where prod_name like '_ ton anvil';
+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+

34、使用LIKE操作符和百分号（%）通配符，按产品名称检索，获得以下结果。

mysql> select prod_id,prod_name
-> from products
-> where prod_name like '% ton anvil';
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| ANV01   | .5 ton anvil |
| ANV02   | 1 ton anvil  |
| ANV03   | 2 ton anvil  |
+---------+--------------+

35、使用正则表达式，在products表中，按产品名称进行检索，找出prod_name中包含文本1000的所有产品。

mysql> select prod_name
-> from products
-> where prod_name regexp '1000'
-> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+

36、使用正则表达式，如何匹配单个字符？请在products表中，按产品名称进行检索，找出prod_name中包含四位数字且后三位为000的所有产品。检索结果如下图所示。

mysql> select prod_name
-> from products
-> where prod_name regexp '.000'
-> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

37、在正则表达式中，一对方括号的作用是什么？解释下列语句的含义，并说明这条语句将检索到什么样的结果。

mysql> select prod_name
-> from products
-> where prod_name regexp '[123] ton'
-> order by prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

[]是另一种形式的or语句，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者

38、在正则表达式中，如何匹配某个范围的字符？解释下列语句的含义，并说明这条语句将检索到什么样的结果。

mysql> select prod_name
-> from products
-> where prod_name regexp '[1-5] ton'
-> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

[1-5]是[12345]的缩写

39、dot小圆点在正则表达式中是一个特殊字符，如果在匹配的字段中包含小圆点，该如何处理？请用正则表达式检索下面的结果。

mysql> select vend_name
-> from vendors
-> where vend_name regexp '\\.'
-> order by vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+

为了匹配特殊字符，必须用\\为前导

40、设计一个正则表达式，匹配括号、数字、空格、stick和sticks，获得以下的结果。

mysql> select prod_name
-> from products
-> where prod_name regexp '\\([0-9] sticks?\\)'
-> order by prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

41、使用正则表达式中的字符类，匹配连在一起的4位数字，获得以下的结果。

mysql> select prod_name
-> from products
-> where prod_name regexp '[[:digit:]]{4}'
-> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

42、使用正则表达式中的定位符，找出以一个数（包括以小数点开始的数）开始的所有产品。

mysql> select prod_name
-> from products
-> where prod_name regexp '^[0-9\\.]'
-> order by prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

43、如何拼接字段？请在零售商（vendors）表中，拼接以下4个元素：
（1）存储在vend_name列中的名字；
（2）包含一个空格和一个左括号的字符串；
（3）存储在vend_country中的国家；
（4）包含一个右括号的字符串。获得以下图示的结果。请为你检索出来的拼接字段使用别名vend_title。

mysql> select concat(vend_name,' (',vend_country,')') as vend_title
-> from vendors
-> order by vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+

44、检索订单号20005中的所有物品，列出它们的产品id（prod_id），数量（quantity），产品价格（item_price）。

mysql> select prod_id,quantity,item_price
	-> from orderitems
	-> where order_num = 20005;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+

45、检索订单号20005中的所有物品，列出它们的产品id（prod_id），数量（quantity），产品价格（item_price）。
并增加一个计算字段（expanded_price），显示每一种物品的总价。

mysql> select prod_id,quantity,item_price,quantity*item_price as expanded_price
-> from orderitems
-> where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

46、查询供应商的名称，并将供应商的名称转换为大写。

mysql> select vend_name,upper(vend_name) as vend_name_upcase
-> from vendors
-> order by vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+

47、在customers表中查询客户的名称（cust_name）和联系人（cust_contact），其中联系人的发音跟“Y.Lie”类似。

mysql> select cust_name,cust_contact
-> from customers
-> where soundex(cust_contact) = soundex('Y Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

48、检索日期为2005-09-01的订单记录。使用日期进行匹配，不考虑时间。

mysql> select cust_id,order_num
-> from orders
-> where date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

49、检索2005年9月下的所有订单。

mysql> select cust_id,order_num
-> from orders
-> where date(order_date) between '2005-09-01' and '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+

50、查询products表中所有产品的平均价格。

mysql> select avg(prod_price) as avg_price
	-> from products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

51、查询vend_id为1003的供应商的所有产品的平均价格。

mysql> select avg(prod_price) as avg_price
-> from products
-> where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+

52、查询customers表中客户的总数。

mysql> select count(*) as num_cust
-> from customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+

53、查询有邮件地址的客户总数，不包含那些没有邮件地址的客户。

mysql> select count(cust_email) as num_cust
-> from customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+

54、查询products表中最贵的物品的价格。

mysql> select max(prod_price) as max_price
-> from products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+

mysql> select prod_price as max_price
-> from products
-> order by prod_price desc
-> limit 1;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+

55、查询订单号为20005的订单里面的所订购物品的总数。

mysql> select sum(quantity) as items_ordered
-> from orderitems
-> where order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+

56、查询订单号为20005的订单的总金额。

mysql> select sum(item_price*quantity) as total_price
-> from orderitems
-> where order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+

57、查询特定供应商（vend_id=1003）提供的产品的平均价格，相同的价格只算一次。

mysql> select avg(distinct prod_price) as avg_price
-> from products
-> where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+

58、查询products表中物品的数目、最低价格、最高价格以及平均价格，为查询的每一个条目取别名。

mysql> select count(*) as num_items,
-> min(prod_price) as price_min,
-> max(prod_price) as price_max,
-> avg(prod_price) as price_avg
-> from products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+

59、查询vend_id为1003的供应商提供的产品数目。

mysql> select count(*) as num_prods
	-> from products
	-> where vend_id = 1003;
+-----------+
| num_prods |
+-----------+
|         7 |
+-----------+

60、查询每一个供应商提供的产品数目，列出供应商id（vend_id）和产品数目（num_prods），得到下面的结果。

mysql> select vend_id,count(*) as num_prods
-> from products
-> group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

61、查询有两个以上（含两个）订单的客户id（cust_id）和订单数（num_orders）。

mysql> select cust_id,count(*) as orders
-> from orders
-> group by cust_id
-> having count(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

62、查询2005年1月至12月具有两个以上（含两个）订单的客户id（cust_id）和订单数（num_orders）。
提示：同时使用where和having进行过滤，先用where针对时间过滤2005年1月至12月的订单，再用having筛选出具有两个以上（含两个）订单的客户。

mysql> select cust_id,count(*) as orders
-> from orders
-> where date(order_date) between '2005-01-01' and '2005-12-30'
-> group by cust_id
-> having count(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

63、查询具有两个以上（含两个）产品价格大于等于10美元的产品的供应商，列出他们的供应商id（vend_id）和产品数目（num_prods），结果如下图所示。

mysql> select vend_id,count(*) as num_prods
-> from products
-> where prod_price >= 10
-> group by vend_id
-> having count(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

64、检索订单总价大于等于50美元的订单的订单号和订单总价。

mysql> select order_num,sum(quantity*item_price) as ordertotal
-> from orderitems
-> group by order_num
-> having sum(quantity*item_price) >= 50
-> order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

65、使用子查询，检索订购物品TNT2的所有客户的信息，列出他们的客户名称（cust_name）和联系人（cust_contact）。

mysql> select cust_name,cust_contact
-> from customers
-> where cust_id in (select cust_id
-> from orders
-> where order_num in (select order_num
-> from orderitems
-> where prod_id = 'TNT2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

66、检索customers表中每个客户的订单总数。

mysql> select cust_name,
-> cust_state,
-> (select count(*)
-> from orders
-> where orders.cust_id = customers.cust_id) as orders
-> from customers
-> order by cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+

67、查询编号为20005的订单中的所有物品，列出它们的产品名称（prod_name），供应商名称（vend_name），
产品价格（prod_price）和数量（quantity）。结果如下图所示。

mysql> select prod_name,vend_name,prod_price,quantity
-> from orderitems,products,vendors
-> where products.vend_id = vendors.vend_id
-> and orderitems.prod_id = products.prod_id
-> and order_num = 20005;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+

68、使用子查询，首先找到产品id（prod_id）为DTNTR的物品的供应商，
然后找出这个供应商生产的其它物品，列出它们的产品id（prod_id）和产品名称（prod_name）。结果如下图所示。

mysql> select prod_id,prod_names
-> from products
-> where vend_id = (select vend_id
-> from products
-> where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

69、检索products表中价格最贵的物品，列出它的产品id（prod_id），产品名称（prod_name），产品价格（prod_price）。

mysql> select prod_id,prod_name,prod_price
-> from products
-> order by prod_price desc
-> limit 1;
+---------+--------------+------------+
| prod_id | prod_name    | prod_price |
+---------+--------------+------------+
| JP2000  | JetPack 2000 |      55.00 |
+---------+--------------+------------+

70、使用联结（有的书中称作连接），查询供应商名称（vend_name），供应商提供的产品名称（prod_name）和产品价格（prod_price）。
根据供应商名称（vend_name）和产品名称（prod_name）对检索结果进行排序。

mysql> select vend_name,prod_name,prod_price
	-> from vendors,products
	-> where vendors.vend_id = products.vend_id
	-> order by vend_name,prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+

71、使用联结（连接），检索订购物品TNT2的所有客户的信息，列出他们的客户名称（cust_name）和联系人（cust_contact）。

mysql> select cust_name,cust_contact
-> from customers,orders,orderitems
-> where customers.cust_id = orders.cust_id
-> and orderitems.order_num = orders.order_num
-> and prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

72、使用联结（连接），找到产品id（prod_id）为DTNTR的物品的供应商生产的所有物品，
列出它们的产品id（prod_id）和产品名称（prod_name）。结果如下图所示。

mysql> select prod_id,prod_name
-> from products
-> where vend_id = (select vend_id
-> from products
-> where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

73、使用内部联结（又叫内连接），检索所有客户及其订单，列出他们的客户id（cust_id），客户姓名（cust_name）和订单号（order_num）。

mysql> select customers.cust_name,
-> customers.cust_id,
-> count(orders.order_num) as num_ord
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id
-> group by customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

74、使用外部联结，检索所有客户及其订单，列出他们的客户id（cust_id），客户姓名（cust_name）和订单号（order_num）。
包括那些没有订单的客户。结果如下图所示。

mysql> select customers.cust_id,orders.order_num
-> from customers left outer join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

75、检索所有客户及其所下的订单数，列出客户名称（cust_name），客户id（cust_id），订单数（num_ord）。结果如下图所示。

mysql> select customers.cust_name,
-> customers.cust_id,
-> count(orders.order_num) as num_ord
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id
-> group by customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

76、检索所有客户及其所下的订单数，列出客户名称（cust_name），客户id（cust_id），订单数（num_ord）。
包括那些没有下任何订单的客户。结果如下图所示。

mysql> select customers.cust_name,
	-> customers.cust_id,
	-> count(orders.order_num) as num_ord
	-> from customers left outer join orders
	-> on customers.cust_id = orders.cust_id
	-> group by customers.cust_id;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

77、首先，检索价格小于等于5的所有物品，列出它们的供应商id（vend_id），产品id（prod_id），产品价格（prod_price）。
其次，检索供应商1001和1002 生产的所有物品，，列出它们的供应商id（vend_id），产品id（prod_id），产品价格（prod_price）。
然后，使用UNION将上面的两个结果集组合在一起，形成一个结果集。结果如下图所示。

mysql> select vend_id,prod_id,prod_price
-> from products
-> where prod_price <= 5
-> union
-> select vend_id,prod_id,prod_price
-> from products
-> where vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+

78、同第77题，对查询结果进行排序，结果如下图所示。

mysql> select vend_id,prod_id,prod_price
-> from products
-> where prod_price <= 5
-> union
-> select vend_id,prod_id,prod_price
-> from products
-> where vend_id in (1001,1002)
-> order by vend_id;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+

79、同第77题，保留重复的数据。

80、在productnotes表中对note_text列进行全文本检索，检索包含rabbit的文本，结果如下图所示。

mysql> select note_text
-> from productnotes
-> where match(note_text) against('rabbit');
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load.                                                                              |
| All guaranteed to be bright and orange, and suitable for use as rabbit bait.                                         |
+----------------------------------------------------------------------------------------------------------------------+

81、在productnotes表中对note_text列进行全文本检索，使用关键词rabbit进行匹配，给出排序的等级（rank），演示全文本搜索中排序如何工作。

mysql> select note_text,
-> match(note_text) against('rabbit') as ranks
-> from productnotes;

82、在productnotes表中对note_text列进行全文本检索，检索包含anvils的文本，观察使用查询扩展与不使用查询扩展的差别。

mysql> select note_text
-> from productnotes
-> where match(note_text) against('anvils');

mysql> select note_text
-> from productnotes
-> where match(note_text) against('anvils' with query expansion);

83、使用全文本布尔搜索，匹配包含词rabbit和bait的行。

mysql> select note_text
-> from productnotes
-> where match(note_text) against('+rabbit +bait' in boolean mode);

84、使用全文本布尔搜索，匹配包含词rabbit和bait中的至少一个词的行。

mysql> select note_text
-> from productnotes
-> where match(note_text) against('rabbit bait' in boolean mode);

85、使用全文本布尔搜索，匹配短语rabbit bait而不是匹配两个词rabbit和bait。

mysql> select note_text
-> from productnotes
-> where match(note_text) against('"rabbit bait"' in boolean mode);

86、使用全文本布尔搜索，匹配rabbit和carrot，增加前者的等级，降低后者的等级。

mysql> select note_text
-> from productnotes
-> where match(note_text) against('>rabbit <carrot' in boolean mode);

87、使用全文本布尔搜索，匹配包含词safe和combination的行，降低后者的等级。

mysql> select note_text
-> from productnotes
-> where match(note_text) against ('+safe +(<combination)' in boolean mode);

88、首先克隆一个和customers一样的表customers2（如果已经存在的话，请先用drop table customers2将其删除），
然后向customers2表中插入一行数据，插入的数据可以不完整，但是要与customers2表的属性相对应。

mysql> drop table if exists customers2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table customers2 like customers;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into customers2(cust_name,cust_address,cust_city,cust_state,cust_zip)
-> values('sjn','jit','nanjing','MI','16323');
Query OK, 1 row affected (0.00 sec)

mysql> select * from customers2;
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+
|       1 | sjn       | jit          | nanjing   | MI         | 16323    | NULL         | NULL         | NULL       |
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+

89、一次向customers2表中插入两行数据

mysql> delete from customers2;
Query OK, 1 row affected (0.01 sec)

mysql> insert into customers2(cust_name,cust_address,cust_city,cust_state,cust_zip)
-> values('sjn','jit','nanjing','MI','16323'),('daning','jit','nanjing','MI','16323');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from customers2;
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+
|       2 | sjn       | jit          | nanjing   | MI         | 16323    | NULL         | NULL         | NULL       |
|       3 | daning    | jit          | nanjing   | MI         | 16323    | NULL         | NULL         | NULL       |
+---------+-----------+--------------+-----------+------------+----------+--------------+--------------+------------+

90、客户10005现在有了电子邮件地址“elmer@fudd.com”，请更新他的记录。

mysql> update customers
-> set cust_email = 'elmer@fudd.com'
-> where cust_id = 10005;
Query OK, 1 row affected (0.00 sec)

mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | elmer@fudd.com      |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+

91、首先克隆一个和customers一样的表customers3，使用表customers的数据填充表customers3。
然后删除表customers3中的顾客id为10001的那一行数据。最后，查看一下是否真的删除了。

mysql> create table customers3 like customers;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into customers3 select * from customers;
Query OK, 5 rows affected (0.01 sec)
	
mysql> delete from customers3
-> where cust_id = 10001;
Query OK, 1 row affected (0.00 sec)

mysql> select * from customers3;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | elmer@fudd.com      |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+

92、使用truncate命令，将中customers3表中的数据全部删除。
注：如果不能删除，且出现以下错误信息：
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.
这是因为MySQL默认使用了安全模式，请执行以下指令：
SET SQL_SAFE_UPDATES = 0;
然后再尝试一下。

mysql> truncate customers3;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from customers3;
Empty set (0.00 sec)

93、创建一个orders2表（如果已经存在的话，请先删除它），它有以下属性：
order_num，数据类型为int，不允许为空值，自动增长；
order_date，数据类型为datetime，不允许为空值；
cust_id，数据类型为int，不允许为空值；
定义order_num为主码，cust_id为外码，参照customers表的cust_id属性；
使用InnoDB引擎。

mysql> drop table if exists orders2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table orders2
-> (
->  order_num int NOT NULL AUTO_INCREMENT,
->  order_date datetime NOT NULL,
->  cuts_id int NOT NULL,
->  PRIMARY KEY(order_num)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

94、说明MySQL有哪几个引擎，各个引擎的主要功能。

1. MyISAM：默认的MySQL 插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
2. InnoDB：用于事务处理应用程序，具有众多特性，包括ACID 事务支持。
3. Memory：将所有数据保存在RAM 中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。
4. Merge：允许MySQL DBA 或开发人员将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为1 个对象引用它们。
   对于诸如数据仓储等VLDB环境十分适合。

95、给vendors表增加一个名为vend_phone的列，数据类型为char(20)。

mysql> ALTER TABLE vendors ADD vend_phone CHAR(20);
Query OK, 0 rows affected (0.07 sec)

96、删除vendors表中名为vend_phone的列。

mysql> ALTER TABLE vendors DROP COLUMN vend_phone;
Query OK, 0 rows affected (0.06 sec)

97、创建一个名为productcustomers的视图，它联结三个表，以返回已定购了任意产品的所有客户的列表，
列出他们的客户名称（cust_name），联系人（cust_contact），产品id（prod_id）。然后，使用该视图检索订购了产品TNT2的客户。

mysql> create view productcustomers as
-> select cust_name,cust_contact,prod_id
-> from customers,orders,orderitems
-> where customers.cust_id = orders.cust_id
-> and orderitems.order_num = orders.order_num;
Query OK, 0 rows affected (0.01 sec)

mysql> select cust_name,cust_contact
	-> from productcustomers
	-> where prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

98、建立一个智能存储过程ordertotal，获得一个订单的合计价格。
输入参数为订单号（onumber，数据类型为int），是否加税的标志（taxable，数据类型为boolean）；
输出参数为（ototal，数据类型为decimal(8,2)）。然后，使用这个存储过程，分别查询订单号为20005的订单含税与不含税的总价。

99、建立一个存储过程processorders，在其中使用游标，获得orders表中的所有订单号。
然后，根据每一个订单号，调用第98题的智能存储过程ordertotal，计算该订单的合计价格。
建立一个新的表ordertotals，保存订单号和合计价格。最后，调用存储过程processorders，检索出所有订单的合计价格。

100、创建一个触发器newproduct，每当向products表中插入一条新的记录之后，就返回“Product added”的提示。
实际测试一下这个触发器是否工作正常。然后删除这个触发器。

101、创建一个触发器neworder，每当向orders表中插入一条新的记录之后，就自动返回新的订单号。
实际测试一下这个触发器是否工作正常。

102、创建一个触发器deleteorder，每当从orders表中删除一条记录之前，
   就自动把即将删除的行（表中的一行数据又称为一个元组，英文为tuple）保存到一个名为archive_orders的表中。
   创建（或克隆）一个和orders表一样的空表archive_orders，删除orders表中的两条记录，然后查询archive_orders表，
   实际测试一下这个触发器是否工作正常。在这个触发器中，会用到OLD虚拟表，请问OLD虚拟表中保存的是什么？

103、创建一个触发器updatevendor，每当更新vendors表中的一行记录之前，就自动将供应商所在的州的名称（vend_state）替换为大写字母。
实际测试一下这个触发器是否工作正常。在这个触发器中，会用到NEW虚拟表，请问NEW虚拟表中保存的是什么？

注：如果向一个表中插入新的数据，请在实验结束的时候，删除刚才插入的数据。如果需要更新、删除一个表中的数据，请使用克隆表进行操作。避免破坏原数据，给后面实验造成不便。