数据库查询练习含参考答案.docx

1、查询练习题 第一部分： #创建 fruits 表 CREATE TABLE fruits ( f_id CHAR(10) NOT NULL, s_id INT NOT NULL, f_name CHAR(255) NOT NULL, f_price DECIMAL (8, 2) NOT NULL, PRIMARY KEY (f_id) ) ; #插入数据 INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES (a1, 101, apple, 5.2), (b1, 101, blackberry, 10.2),(bs1, 102, ora

2、nge, 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, 10

3、7, xxxx, 3.6) ; 【 1】从 fruits表中检索所有字段的数据 Select * from fruits; 【 2】查询 fruits表中 f_name 列所有水果名称 Select f_name from fruits; 【 3】从 fruits表中获取 f_name 和 f_price 两列 Select f_name,f_price from fruits; 【 4】查询价格为 10.2 元的水果的名称 Select f_name from fruits where f_price=10.2; 【 5】查找名称为“ apple”的水果的价格 Select f_price

4、from fruits where f_name=apple; 【 6】查询价格小于 10 的水果的名称 Select f_name from fruits where f_peice2.00 and f_price10.20; 【 11】查找所有以 b字母开头的水果 Select f_name from fruits where f_name like b%; 【 12】在 fruits表中，查询 f_name 中包含 字母 g的记录 Select * from fruits where f_name like “%g%”; 【 13】查询以 b开头，并以 y结尾的水果的名称 Select

5、* from fruits where f_name like “b%y”; 【 14】在 fruits表中，查询以字母 y结尾，且 y前面只有 4 个字母的记录 Select * from fruits where f_name like “_y”; 第二部分： #创建数据表 customers 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

6、, 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, LM), (10002, Stars, 333 Fromage Lane,Dalian, 116000, Zhangbo,J), (100

7、03, Netbhood, 1 Sunny Place, Qingdao, 266000,LuoCong, NULL), (10004, JOTO, 829 Riverside Drive, Haikou, 570000, YangShan, ); 查询表中的记录数目： SELECT COUNT(*) AS cust_num FROM customers; 【 15】查询 customers 表中 c_email 为空的记录的 c_id、 c_name 和 c_email 字段值 Select c_id,c_name,c_email from customers where c_email i

8、s null; 【 16】查询 customers 表中 c_email 不为空的记录的 c_id、 c_name 和 c_email 字段值 Select c_id,c_name,c_email from customers where c_email is not null; 【 17】在 fruits表中查询 s_id = 101，并且 f_price 大于等于 5 的水果价格和名称 Select f_name,f_price from fruits where s_id=101 and f_price=5; 【 18】在 fruits表中查询 s_id = 101 或者 102，且 f

9、_price 大于 5，并且 f_name= apple的水果价格和名称 Select f_name,f_price from fruits where s_id in (101,102) and f_price5 and f_name=apple; 【 19】查询 s_id=101 或者 s_id=102 的水果供应商的 f_price 和 f_name Select f_price,f_name from fruits where s_id=101 or s_id=102; 【 20】查询 s_id=101 或者 s_id=102 的水果供应商的 f_price 和 f_name（用不同的

10、方法） Select f_price,f_name from fruits where s_id in (101,102); 【 21】查询 fruits 表中 s_id 字段的值，返回 s_id 字段值且不得 重复 （使用 distinct去重复） SELECT DISTINCT s_id FROM fruits; 【 22】查询 fruits 表的 f_name 字段值，并对其进行排序 Select f_name from fruits order by f_name; 【 23】查询 fruits 表中的 f_name 和 f_price 字段，先按 f_name 排序，再按 f_pri

11、ce 排序 Select f_name,f_price from fruits order by f_name,f_price; 【 24】查询 fruits 表中的 f_name 和 f_price 字段，对结果按 f_price 降序方式排序 Select f_name,f_price from fruits order by f_price desc; 【 25】查询 fruits 表，先按 f_price 降序排序，再按 f_name 字段升序排序 Select * from fruits order by f_price desc,f_name; 【 26】根据 s_id 对 fru

12、its表中的数据进行分组，并显示每个分组的总记录数。 Select s_id,count(*) from fruits group by s_id; 【 27】根据 s_id 对 fruits 表中的数据进行分组，将每个供应商的水果名称显示出来 （使用GROUP_CONCAT 拼接字段内容 ） SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id; 【 28】根据 s_id 对 fruits表中的数据进行分组，并显示水果种类大于 1 的分组信息 Select s_id as nemes from fruits

13、group by s_id having count(f_name)1; 【 29】根据 s_id 对 fruits表中的数据进行分组，并显示记录数量 SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP; WITH ROLLUP 对所有记录进行统计 【 30】根据 s_id 和 f_name 字段对 fruits表中的数据进行分组 Select * from fruits group by s_id,f_name; 第三部分 ： 创建数据表， SQL 语句如下： CREATE TABLE orderitems

14、( 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) ) ; 插入数据， SQL 语句如下： INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price) VALUES(30001, 1, a1, 10, 5.2),(30001, 2, b2, 3, 6),(30001, 3, b

15、s1, 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); 【 31】查询总订单价格大于 100 的订单号和总订单价格 【 32】显示 fruits 表查询结果的前 4 行 【 33】在 fruits表中，使用 LIMIT 子句，返回从第

16、 5 个记录开始的，行数长度为 3的记录 【 34】查询 customers 表中总的行数 【 35】查询 customers 表中有电子邮箱的顾客的总数 【 36】在 orderitems 表中，使用 COUNT()函数统计不同订单号中订购的水果种类 【 37】在 orderitems 表中查询 30005 号订单一共购买的水果总量 【 38】在 orderitems 表中，使用 SUM()函数统计不同订单号中订购的水果总量 【 39】在 fruits表中，查询 s_id=103 的供应商的水果价格的平均值 【 40】在 fruits表中，查询每一个供应商的水果价格的平均值 【 41】在 f

17、ruits表中查找市场上价格最高的水果 【 42】在 fruits表中查找不同供应商提供的价格最高的水果 【 43】在 fruits表中查找 f_name 的最大值 【 44】在 fruits表中查找市场上价格最低的水果 【 45】在 fruits表中查找不同供应商提供的价格最低的水果 第四部分： #创建数据表 suppliers， SQL 语句如下： 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) N

18、ULL, s_call CHAR(50) NOT NULL, PRIMARY KEY (s_id) ) ; 插入数据， SQL 语句如下： 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,Goo

19、d Set,Taiyuang,030000, 22222), (106,Just Eat Ours,Beijing,010, 45678), (107,DK Inc.,Zhengzhou,450000, 33332); 【 46】在 fruits表和 suppliers 表之间使用内连接查询。 查询之前，查看两个表的结构： DESC fruits; DESC suppliers; 由结果可以看到， fruits表和 suppliers 表中都有相同数据类型的字段 s_id，两个表通过 s_id字段建立联系。 接下来从 fruits表中查询 f_name、 f_price 字段，从 suppli

20、ers 表中查询 s_id、 s_name 【 47】在 fruits表和 suppliers 表之间，使用 INNER JOIN 语法进行内连接查询 【 48】查询供应 f_id= a1的水果供应商提供的其他水果种类【较难】 1 LEFT JOIN 左连接 左连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。 如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。 创建表 orders， SQL 语句如下： CREATE TABLE orders( o_num INT NOT NULL AUTO_INCREMENT,

21、o_date DATETIME NOT NULL, c_id INT NOT NULL, PRIMARY KEY (o_num) ) ; 插入数据， SQL 语句如下： INSERT INTO orders(o_num, o_date, c_id) VALUES(30001, 2008-09-01, 10001), (30002, 2008-09-12, 10003), (30003, 2008-09-30, 10004), (30004, 2008-10-03, 10005), (30005, 2008-10-08, 10001); 【 49】在 customers 表和 orders 表中

22、，查询所有客户，包括没有订单的客户 2 RIGHT JOIN 右连接 右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。 【 50】在 customers 表和 orders 表中，查询所有订单，包括没有客户的订单 【 51】在 customers 表和 orders 表中，使用 INNER JOIN 语法查询 customers 表中 ID 为 10001的客户的订单信息 【 52】在 fruits表和 suppliers 表之间，使用 INNER JOIN 语法进行内连接查询，并对查询结果排序 参考答案 【 1】从 fruits表中检索所有字段

23、的数据， SQL 语句如下： SELECT * FROM fruits; 【 2】查询 fruits表中 f_name 列所有水果名称， SQL 语句如下： SELECT f_name FROM fruits; 【 3】从 fruits表中获取 f_name 和 f_price 两列， SQL 语句如下： SELECT f_name, f_price FROM fruits; 【 4】查询价格为 10.2 元的水果的名称， SQL 语句如下： SELECT f_name, f_price FROM fruits WHERE f_price = 10.2; 【 5】查找名称为“ apple”的水

24、果的价格， SQL 语句如下： SELECT f_name, f_price FROM fruits WHERE f_name = apple; 【 6】查询价格小于 10 的水果的名称， SQL 语句如下： SELECT f_name, f_price FROM fruits WHERE f_price =5; 【 18】在 fruits表中查询 s_id = 101 或者 102，且 f_price 大于 5，并且 f_name= apple的水果价格和名称， SQL 语句如下： SELECT f_id, f_price, f_name FROM fruits WHERE s_id IN(

25、101, 102) AND f_price = 5 AND f_name = apple; 【 19】查 询 s_id=101 或者 s_id=102 的水果供应商的 f_price 和 f_name， SQL 语句如下： SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102; 【 20】查询 s_id=101 或者 s_id=102 的水果供应商的 f_price 和 f_name， SQL 语句如下： SELECT s_id,f_name, f_price FROM fruits WHERE s_id

26、IN(101,102); 【 21】查询 fruits 表中 s_id 字段的值，返回 s_id 字段值且不得重复， SQL 语句如下： SELECT DISTINCT s_id FROM fruits; 【 22】查询 fruits 表的 f_name 字段值，并对其进行排序， SQL 语句如下： SELECT f_name FROM fruits ORDER BY f_name; 【 23】查询 fruits 表中的 f_name 和 f_price 字段，先按 f_name 排序，再按 f_price 排序， SQL语句如下： SELECT f_name, f_price FROM fr

27、uits ORDER BY f_name, f_price; 【 24】查询 fruits 表中的 f_name 和 f_price 字段，对结果按 f_price 降序方式排序， SQL 语句如下： SELECT f_name, f_price FROM fruits ORDER BY f_price DESC; 【 25】查询 fruits 表，先按 f_price 降序排序，再按 f_name 字段升序排序， SQL 语句如下： SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name; 【 26】根据 s_id 对

28、 fruits表中的数据进行分组，并显示每个分组的总记录数， SQL 语句如下： SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id; 【 27】根据 s_id 对 fruits表中的数据进行分组，将每个供应商的水果名称显示出来， SQL 语句如下： SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id; 【 28】根据 s_id 对 fruits表中的数据进行分组，并显示水果种类大于 1 的分组信息， SQL 语句如下： SELECT s_id,

29、GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name) 1; 【 29】根据 s_id 对 fruits表中的数据进行分组，并显示记录数量， SQL 语句如下： SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP; 【 30】根据 s_id 和 f_name 字段对 fruits表中的数据进行分组， SQL 语句如下， SELECT * FROM fruits GROUP BY s_id,f_name; 【 31

30、】查询总订单价格大于 100 的订单号和总订单价格， SQL 语句如下： SELECT o_num, SUM(quantity * item_price) AS orderTotal FROM orderitems GROUP BY o_num HAVING SUM(quantity*item_price) = 100; 可以看 到，返回的结果中 orderTotal 列的总订单价格并没有按照一定顺序显示，使用 ORDER BY 关键字按总订单价格排序显示结果， SQL 语句如下： SELECT o_num, SUM(quantity * item_price) AS orderTotal F

31、ROM orderitems GROUP BY o_num HAVING SUM(quantity*item_price) = 100 ORDER BY orderTotal; 【 32】显示 fruits 表查询结果的前 4 行， SQL 语句如下： SELECT * FROM fruits LIMIT 4; 【 33】在 fruits表中，使用 LIMIT 子句，返回从第 5 个记录开始的，行数长度为 3 的记录，SQL 语句如下： SELECT * FROM fruits LIMIT 4, 3; 【 34】查询 customers 表中总的行数， SQL 语句如下： SELECT COU

32、NT(*) AS cust_num FROM customers; 【 35】查询 customers 表中有电子邮箱的顾客的总数， SQL 语句如下： SELECT COUNT(c_email) AS email_num FROM customers; 【 36】在 orderitems 表中，使用 COUNT()函数统计不同订单号中订购的水果种类， SQL 语句如下： SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num; 【 37】在 orderitems 表中查询 30005 号订单一共购买的水果总量， SQL 语句如下： S

33、ELECT SUM(quantity) AS items_total FROM orderitems WHERE o_num = 30005; 【 38】在 orderitems 表中，使用 SUM()函数统计不同订单号中订购的水果总量， SQL 语句如下： SELECT o_num, SUM(quantity) AS items_total FROM orderitems GROUP BY o_num; 【 39】在 fruits表中，查询 s_id=103 的供应商的水果价格的平均值， SQL 语句如下： SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id = 103; 【 40】在 fruits表中，查询每 一个供应商的水果价格的平均值， SQL 语句如下： SELECT s_id,AVG(f_price) AS avg_price FROM fruits GROUP BY s_id;