数据库应用（mysql）PetStore数据库中的数据查询

第1关：简单的单表查询

任务描述

本关任务：查询PetStore数据库中用户表account中的用户编号是u0001的用户姓名fullname和电话号码phone。
其中account表结构如下：

USE petstore;
select fullname,phone from account
where userid=’u0001’;#查询的条件
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：select。

语法格式：
SELECT [ALL | DISTINCT] 输出列表达式, ...
[FROM 表名1 [ , 表名2] …] /FROM子句/ [WHERE 条件] /WHERE子句/ [GROUP BY {列名 | 表达式 | 列编号}
[ASC | DESC], ... /* GROUP BY 子句*/
[HAVING 条件] /* HAVING 子句*/
[ORDER BY {列名 | 表达式 | 列编号}
[ASC | DESC] , ...] /ORDER BY子句/ [LIMIT {[偏移量,] 行数|行数OFFSET偏移量}] /LIMIT子句/

1.     顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

第2关：定义列别名

任务描述

本关任务：查询PetStore数据库account表中所有女性用户的姓名和email，结果中列的标题定义为“姓名”、“电子邮箱”。
其中account表结构如下：

USE petstore;
select fullname as 姓名,email as 电子邮箱#列的标题定义别名
from account
where sex=’女’;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select语句。

语法格式：
SELECT column_name [AS] column_alias

第3关：case

任务描述

本关任务：查询PetStore数据库product商品表中商品名称和数量，对其库存数量按以下规则进行替换：若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若数量大于50，替换为“库存积压”。列标题更改为“库存”。
其中product表结构如下：

USE petstore;
select name,qty,
case when qty<5 then ‘需进货’ 
when qty>=5 and qty<=50 then ‘库存正常’
when qty>50 then ‘库存积压’
end as 库存        
form  product;
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：select语句。

语法格式：
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
……
ELSE 表达式
END

第4关：计算列值

任务描述

本关任务：查询PetStore数据库lineitem订单详情表中每种商品在每个订单中的总价。

其中lineitem表结构如下：

USE petstore;
select orderid,itemid,
quantity*untiprice as 单种商品总价
from lineitem;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select语句。

语法格式：
SELECT 计算列值的表达式1 [ , 计算列值的表达式2 ]

第5关：消除结果集重复行

任务描述

本关任务：查询PetStore数据库account用户表中的用户地址address，注意重复的不显示。
其中account表结构如下：

 

USE petstore;
select distinct address from account;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select语句。

语法格式：
SELECT [ALL | DISTINCT] 输出列表达式, ...
[FROM 表名1 [ , 表名2] …] /FROM子句/ [WHERE 条件] /WHERE子句/ [GROUP BY {列名 | 表达式 | 列编号}
[ASC | DESC], ... /* GROUP BY 子句*/
[HAVING 条件] /* HAVING 子句*/
[ORDER BY {列名 | 表达式 | 列编号}
[ASC | DESC] , ...] /ORDER BY子句/ [LIMIT {[偏移量,] 行数|行数OFFSET偏移量}] /LIMIT子句/

1.     顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

第6关：聚合函数

任务描述

本关任务：
1.查询PetStore数据库中所有用户数（通过查询account表的行数可以确定用户数）。
2.查询商品表product中每种商品的平均标价和平均进价。
3.查询所有商品总数量（通过查询product表的qty列数值的总和可以确定商品总数量）。
其中account表结构如下：

 product表结构如下：

 

USE petstore;
select count(*)
from account;
select avg(listprice),avg(unitcost)
from product;
select sum(qty)
form product;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select语句。

语法格式：

1.COUNT ( { [ ALL | DISTINCT ] expression } | * )

其中，expression是一个表达式，其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含 NULL值。
2.MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：
MAX / MIN ( [ ALL | DISTINCT ] expression )
其中，expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期 类型。
3.SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：
SUM / AVG ( [ ALL | DISTINCT ] expression )
其中，expression是常量、列、函数或表达式，其数据类型只能是数值型。

第7关：where子句

任务描述

本关任务：
1.查找订单表orders中订单金额(totalprice)大于300的所有订单的用户号(userid)、订单金额(totalprice)和订单状态(status)。
2.在订单表orders中查找用户号(userid)为u0003号顾客的所有订单情况。
3.在用户表account中查找所有地址address是’广东深圳市’的女性用户信息。
4.在用户表account中查找所有姓刘的用户的电话号码和地址。
5.在商品表product中查找标价listprice在1000-2000之间的商品信息。
6.在用户表account中查找地址address不是’广东广州市’的用户信息。

其中orders表结构如下：

 account表结构如下：

 product表结构如下：

后，表中的数据如下：

USE petstore;
select userid,totalprice,status
from orders
where totalprice>300;
select * from orders
where userid=’u0003’;
select * from account
where address=’广东深圳市’ and sex=’女’;
select phone,address
from account
where fullname like ‘刘%’;
select * from product
where listprice between 1000 and 2000;
select * from account
where address!=’广东深圳市’; 
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：where

语法

WHERE子句必须紧跟FROM子句之后，在WHERE子句中，使用一个条件从FROM子句的中间结果中选取行。其基本格式为：
WHERE 列 运算符 值

2.逻辑运算:
通过逻辑运算符（AND、OR、XOR和NOT）组成更为复杂的查询条件。逻辑运算操作的结果是“1”或“0”，分别表示“true”或“false”。

 

第8关：多表查询1

任务描述

本关任务：查找用户号是’u0003’号顾客的姓名和所有订单的订单号、订单总价。
其中orders表结构如下：

 account表结构如下：

 

USE petstore;
select fullname,orderid,totalprice
from account,orders
where account.userid=orders.userid and 
orders.userid=’u0003’;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select多表查询。

语法

SELECT的查询对象由FROM子句指定，其格式为：
FROM 表名1 [ [AS] 别名1 ] [ , 表名2[ [AS] 别名2]] … /查询表/

1. | JOIN子句 /连接表/ ● 引用单个表的两种方式：
   可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：
   SELECT * FROM db2.tb;
   当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。

第9关：多表查询2

任务描述

本关任务：显示订单详情表lineitem中所有订单的订单编号orderid，商品名称name和购买数量quantity。
其中lineitem表结构如下：

 product表结构如下：

 

USE petstore;
select lineitem.orderid,product.name,lineitem.quantity
from lineitem,product
where lineitem.itemid=product.productid;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select多表查询。

语法

SELECT的查询对象由FROM子句指定，其格式为：
FROM 表名1 [ [AS] 别名1 ] [ , 表名2[ [AS] 别名2]] … /查询表/

1. | JOIN子句 /连接表/ ● 引用单个表的两种方式：
   可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：
   SELECT * FROM db2.tb;
   当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。

第10关：多表查询进阶版1

任务描述

本关任务：查找单个订单中订购数量大于5的用户名，订单id和商品名。

其中orders表结构如下：

 account表结构如下：

product表结构如下：

 lineitem表结构如下：

 

USE petstore;
select account.fullname,orders.orderid,product.name
from account,orders,product,lineitem
where orders.userid=account.userid and orders.orderid=lineitem.orderid and product.productid=lineitem.itemid and lineitem.quantity>5;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select多表查询。

语法

SELECT的查询对象由FROM子句指定，其格式为：
FROM 表名1 [ [AS] 别名1 ] [ , 表名2[ [AS] 别名2]] … /查询表/

1. | JOIN子句 /连接表/ ● 引用单个表的两种方式：
   可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：
   SELECT * FROM db2.tb;
   当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。

第11关：多表查询进阶版2

任务描述

查询每位用户的每个订单购买的商品名称和数量。

本关任务要用到用户表account、订单表orders、订单详情表lineitem，商品表product。
其中orders表结构如下：

 account表结构如下：

 product表结构如下：

 Lineitem表结构如下：

 

USE petstore;
select account.fullname,orders.orderid,product.name,lineitem.quantity
from account,orders,product,lineitem
where orders.userid=account.userid and orders.orderid=lineitem.orderid and product.productid=lineitem.itemid;
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：多表查询

语法

SELECT的查询对象由FROM子句指定，其格式为：
FROM 表名1 [ [AS] 别名1 ] [ , 表名2[ [AS] 别名2]] … /查询表/ | JOIN子句 /连接表/ ● 引用单个表的两种方式：
可以用两种方式引用一个表，第一种方式是使用USE语句让一个数据库成为当前数据库，在这种情况下，如果在FROM子句中指定表名，则该表应该属于当前数据库。第二种方式是指定的时候在表名前带上表所属数据库的名字。例如，假设当前数据库是db1，现在要显示数据库db2里的表tb的内容，使用如下语句：
SELECT * FROM db2.tb;
当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字，但是一般来说，如果选择的字段在各表中是唯一的，就没有必要去特别指定。

第12关：in子查询

任务描述

本关任务：在account表中查找2013-04-11 15:07:34购买了商品的户信息。
其中orders表结构如下：

 account表结构如下：

 

USE petstore;
select account.*
from account where userid in 
(select userid from orders where orderdate=’2013-04-11 15:07:34’);
######Begin####
######end#####

####相关知识
IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：
expression [ NOT ] IN ( subquery )

其中，subquery是子查询。当表达式expression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。

第13关：比较子查询

任务描述

本关任务：在account表中查找所有订单总价totalprice>200的用户信息。
其中orders表结构如下：

 account表结构如下：

 

USE petstore;
select account.*
from account where userid in 
(select userid from orders where totalprice>200);
######Begin####
######end#####

####相关知识

为了完成本关任务，你需要掌握：select比较子查询

语法

表达式{比较符号}{all|some|any}（子查询）

第14关：Exists子查询

任务描述

本关任务：在account表中查找订单状态status是‘1’的用户信息。
其中orders表结构如下：

 account表结构如下：

 

USE petstore;
select account.*
from account where userid in 
(select userid from orders where status=’1’);
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：Exists子查询。

语法

[not] exists (子查询)

第15关：Group by

任务描述

本关任务：在用户表account中按性别sex统计客户人数。

USE petstore;
select sex,count(*)
from account
group by sex;
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：group by。

语法

GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XS表中的所有行分组，结果是每个专业的学生成为一组。GROUP BY子句的语法格式如下：

GROUP BY {列名 |表达式 | 列编号} [ASC | DESC], ...
[WITH ROLLUP]

GROUP BY子句后通常包含列名或表达式。MySQL对GROUP BY子句进行了扩展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUP BY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。

第16关：order by与limit

任务描述

本关任务：在orders表中查询最早在宠物商店购买过商品的3位用户的用户号userid。

USE petstore;
select userid from orders
order by orderdate
limit 3;
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：

语法

1.order by
在一条SELECT语句中，如果不使用ORDER BY子句，结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。

语法格式：
ORDER BY {列名 | 表达式 | 列编号} [ASC | DESC] , ...

说明：ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。例如，使用ORDER BY 3表示对SELECT的列清单上的第3列进行排序。

关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。
2.limit
LIMIT子句是SELECT语句的最后一个子句，主要用于限制被SELECT语句返回的行数。
语法格式：
LIMIT {[offset,] row_count
| row_count OFFSET offset}
语法格式中的offset和row_count都必须是非负的整数常数，offset指定返回的第一行的偏移量，row_count是返回的行数。例如，“LIMIT 5”表示返回SELECT语句的结果集中最前面5行，而“LIMIT 3，5”则表示从第4行开始返回5行。值得注意的是初始行的偏移量为0而不是1。

第17关：having

任务描述

本关任务：查询订单详情表lineitem中商品购买总数量超过6件的订单编号orderid、商品编号itemid和购买总数sum(quantity) as total。按购买总数total从大到小排列。

USE petstore;
select orderid,itemid,sum(quantity) as total
from lineitem
group by quantity desc 
having (quantity)>5;
######Begin####
######end#####

相关知识

为了完成本关任务，你需要掌握：group by,order by,having。

语法

1.group by
GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XS表中的所有行分组，结果是每个专业的学生成为一组。GROUP BY子句的语法格式如下：

GROUP BY {列名 |表达式 | 列编号} [ASC | DESC], ...
[WITH ROLLUP]

GROUP BY子句后通常包含列名或表达式。MySQL对GROUP BY子句进行了扩展，可以在列的后面指定ASC（升序）或DESC（降序）。GROUP BY可以根据一个或多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。

2.order by
在一条SELECT语句中，如果不使用ORDER BY子句，结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。

语法格式：
ORDER BY {列名 | 表达式 | 列编号} [ASC | DESC] , ...

说明：ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。例如，使用ORDER BY 3表示对SELECT的列清单上的第3列进行排序。

关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。

3.having
使用HAVING子句的目的与WHERE子句类似，不同的是WHERE子句是用来在FROM子句之后选择行，而HAVING子句用来在GROUP BY子句后选择行。例如，查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC表上按学号分组后筛选出符合平均成绩大于等于85的学生。

语法格式：
HAVING 条件

其中，where_definition是选择条件，条件的定义和WHERE子句中的条件类似，不过HAVING子句中的条件可以包含聚合函数，而WHERE子句中则不可以。
SQL标准要求HAVING必须引用GROUP BY子句中的列或用于聚合函数中的列。不过，MySQL支持对此工作性质的扩展，并允许HAVING引用SELECT清单中的列和外部子查询中的列。