---

一、数据库结构与数据源插入

本节将介绍如何构建一个水果销售数据库，包括创建数据库、数据表，并插入初始数据。

1.1 创建数据库与表

CREATE DATABASE fruitsales;USE fruitsales;CREATE TABLE fruits(f_id    char(10)    NOT NULL  PRIMARY KEY,s_id    INT        	NOT NULL,f_name  char(255)  	NOT NULL,f_price decimal(8,2)
);

1.2 插入数据

INSERT INTO fruits (f_id, s_id, f_name, f_price)VALUES('a1', 101,'apple',5.2),('b1',101,'blackberry', 10.2),('bs1',102,'orange', 11.2),('bs2',105,'melon',8.2),('t1',102,'banana', 10.3),('t2',102,'grape', 5.3),('o2',103,'coconut', 9.2),('c0',101,'cherry', 3.2),('a2',103, 'apricot',2.2),('l2',104,'lemon', 6.4),('b2',104,'berry', 7.6),('m1',106,'mango', 15.6),('m2',105,'xbabay', 2.6),('t4',107,'xbababa', 3.6),('m3',105,'xxtt', 11.6),('b5',107,'xxxx', 3.6);CREATE TABLE customers(c_id      int       NOT NULL AUTO_INCREMENT,c_name    char(50)  NOT NULL,c_address char(50)  NULL,c_city    char(50)  NULL,c_zip     char(10)  NULL,c_contact char(50)  NULL,c_email   char(255) NULL,PRIMARY KEY (c_id)
);
INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip,  c_contact, c_email) 
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000',  'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane','Dalian', '116000',  'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao',  '266000','LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou','570000',  'YangShan', 'sam@hotmail.com');CREATE TABLE orderitems(o_num      int          NOT NULL,o_item     int          NOT NULL,f_id       char(10)     NOT NULL,quantity   int          NOT NULL,item_price decimal(8,2) NOT NULL,PRIMARY KEY (o_num,o_item)
) ;
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);CREATE TABLE suppliers(s_id      int      NOT NULL AUTO_INCREMENT,s_name    char(50) NOT NULL,s_city    char(50) NULL,s_zip     char(10) NULL,s_call    CHAR(50) NOT NULL,PRIMARY KEY (s_id)
) ;
INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');CREATE TABLE orders(o_num  int      NOT NULL AUTO_INCREMENT,o_date datetime NOT NULL,c_id   int      NOT NULL,PRIMARY KEY (o_num)
) ;
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2021-09-01', 10001),
(30002, '2021-09-12', 10003),
(30003, '2021-09-30', 10004),
(30004, '2021-10-03', 10005),
(30005, '2021-10-08', 10001);

二、基础数据查询

2.1 查询客户信息

• 查询客户表customers中的客户编号c_id、客户名c_name和地址c_address信息。

SELECT c_id,c_name,c_address FROM customers; 

• 在customers表中查询每个客户的c_id、c_name、c_email，输出的列名为客户编号、客户姓名、邮箱。

SELECT c_id AS '客户编号',c_name '客户姓名',c_email '邮箱' 
FROM customers;

2.2 查询供应商信息

• 查询供应商表suppliers中供应商编号s_id为101的供应商名称s_name和供应商电话s_call。

SELECT s_name,s_call FROM suppliersWHERE s_id=101;

• 查询fruits表中s_id为102的供应商所供应的orange和banana水果的名称及价格信息。

SELECT f_name,f_price FROM  fruitsWHERE s_id=102 AND (f_name='orange' OR f_name='banana');

• 查询orders表中订购日期o_date为2021年9月份的订单编号o_num信息。

SELECT o_num FROM ordersWHERE o_date BETWEEN '2021-09-01' AND '2021-9-30';

• 查询suppliers表中供应商名以Inc.结尾或供应商名第3个字母为M的供应商名称及所在城市的信息。

SELECT s_name,s_city FROM suppliersWHERE s_name LIKE '%Inc.' OR s_name LIKE '__M%';

• 查询customers表中e_mail值为空的客户编号和客户姓名信息。

SELECT c_id,c_name FROM customersWHERE c_email = NULL;SELECT c_id,c_name FROM customersWHERE c_email IS NULL;

• 查询fruits表中s_id为101、102和103，且f_price不小于10元的供应商编号、水果名称和价格的信息。

SELECT s_id,f_name,f_price FROM fruitsWHERE s_id IN(101,102,103) AND f_price>=10;

• 以s_id的降序、s_id相同以f_price升序，显示fruits表中s_id为101和102的s_id、f_id和f_price的信息。

SELECT s_id,f_id,f_price FROM fruitsWHERE s_id IN(101,102)ORDER BY s_id DESC,f_price;

• 显示fruits表中水果价格最高的3种水果的信息。

SELECT * FROM fruitsORDER BY f_price DESCLIMIT 3;

三、查询优化与技巧

探讨如何优化查询语句，以及一些实用的查询技巧。

3.1 使用LIMIT子句

• 使用LIMIT子句，显示customers表中第2-3条记录。

SELECT * FROM customersLIMIT 1,2;

• 统计customers表中客户的总人数和有电子邮箱的客户人数。

SELECT COUNT(*) '总人数',COUNT(c_email) '邮箱人数' FROM customers;

四、高级查询技巧

展示如何使用更复杂的SQL查询语句，包括使用聚合函数、连接查询等。

4.1 使用聚合函数

• 在fruits表中，查询每个供应商水果价格的平均值、最高值和最低值。

SELECT s_id,AVG(f_price) '平均价格',MAX(f_price) '最高价格',MIN(f_price) '最低价格' FROM fruitsGROUP BY s_id;

• 查询orderitems表中每份订单总金额，并按总金额的降序排列。

SELECT o_num,SUM(quantity*item_price) '总金额'  FROM orderitemsGROUP BY o_numORDER BY '总金额' DESC;

• 查询orderitems中每个订单中订购数量相同的订单数

SELECT o_num,quantity,count(*) 订单数 FROM orderitemsGROUP BY o_num,quantity;

• 显示水果种类大于2种的供应商编号和提供的水果种类数。

SELECT s_id,COUNT(*) 种类数 FROM fruitsGROUP BY s_idHAVING COUNT(*)>2;

• 查询s_id为101的供应商名称s_name及所供应水果的f_id、f_name和f_price，查询结果按f_price的降序排列。

SELECT s_name,f_id,f_name,f_price FROM fruits f,suppliers AS sWHERE f.s_id = s.s_id AND s.s_id = 101ORDER BY f_price DESC;

4.2 连接查询

• 通过customers和orders表，查询所有客户的订单信息，包括没有下订单的的客户。

SELECT c.c_id,c_name,o_num,o_date FROM customers c LEFT JOIN orders oON c.c_id = o.c_id;

4.3 使用子查询

• 查询供应商’ACME’供应的水果编号、名称及价格。

 SELECT f_id,f_name,f_price FROM fruitsWHERE s_id = (SELECT s_id FROM suppliersWHERE s_name='ACME')

• 查询供应商’ACME’供应且大于水果平均价格的水果编号、名称及价格。

SELECT f_id,f_name,f_price FROM fruitsWHERE s_id = (SELECT s_id FROM suppliers WHERE s_name='ACME')AND f_price > (SELECT AVG(f_price) FROM fruits);

五、案例分析

• 通过具体案例，展示如何综合运用SQL知识解决实际问题。

5.1 客户订单详情查询

• 查询客户10001所下订单的详细信息。

SELECT * FROM orderitemsWHERE o_num IN (SELECT o_num FROM ordersWHERE c_id=10001);

• 查询fruits表中f_price高于供应商101供应的全部水果价格的f_id和f_price信息。

 SELECT f_id,f_price FROM fruitsWHERE f_price >ALL (SELECT f_price FROM fruits WHERE s_id = 101);