SQL命令详解之数据的查询操作

目录

1 简介

2 基础查询

2.1 基础查询语法

2.2 基础查询练习

3 条件查询

3.1 条件查询语法

3.2 条件查询练习

4 排序查询

4.1 排序查询语法

4.2 排序查询练习

5 聚合函数

5.1 一般语法：

5.2 聚合函数练习

6 分组查询

6.1 分组查询语法

6.2 分组查询练习

7 分页查询

7.1 分页查询语法

7.2 分页查询练习

8 总结

---

1 简介

查询是数据操作至关重要的一部分比如说在所有商品中查找出价格在规定范围内的所有商品，要想把数据库中的数据在客户端中展示给用户，一般都进行了查询的操作。

在实际开发中，我们要根据不同的需求，并且考虑查询的效率来决定怎样进行查询，学习查询前，可以先看看查询的完整语法：

SELECT字段列表 
FROM表名列表 
WHERE条件列表 
GROUPBY分组字段 
HAVING分组后条件 
ORDERBY排序字段 
LIMIT分页限定

2 基础查询

2.1 基础查询语法

查询多个字段：

select 字段列表 from 表名;

查询全部字段：

select * from 表名;

去除重复记录：

select distinct 字段列表 from 表名;

起别名操作：

select 字段名 别名 from 表名;

2.2 基础查询练习

练习一：文章浏览 I

Views 表：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
此表可能会存在重复行。（换句话说，在 SQL 中这个表没有主键）
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意，同一人的 author_id 和 viewer_id 是相同的。

请查询出所有浏览过自己文章的作者，结果按照id 升序排列：

select DISTINCT author_id as id from Views where author_id = viewer_id;

3 条件查询

3.1 条件查询语法

一般语法：

select 字段列表 from 表名 where 条件列表;

条件查询一般配合运行符进行，下面是常见的几个运算符：

 

3.2 条件查询练习

练习一：可回收且低脂的产品

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+

• product_id 是该表的主键（具有唯一值的列）。

• low_fats 是枚举类型，取值为以下两种 （'Y'， 'N'），其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。

• recyclable 是枚举类型，取值为以下两种 （'Y'， 'N'），其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。

编写一个 SQL 找出既是低脂又是可回收的产品编号：

select product_id from Products where low_fats='Y' and recyclable='Y';

练习二：患某种疾病的患者

患者信息表：Patients

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
patient_id （患者 ID）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。

编写一个 SQL 查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions），I 类糖尿病的代码总是包含前缀DIAB1 。

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions like 'DIAB1%' OR conditions LIKE '% DIAB1%';

4 排序查询

4.1 排序查询语法

select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;

注：排序方式有两种：分别是升序 ASC 和降序 DESC，默认情况下是升序 ASC。

4.2 排序查询练习

# score 表
+------+------+-----------+---------+---------+
| s_name | 总分 | 语文成绩  | 数学成绩  | 英语成绩 |
+------+------+-----------+---------+---------+
| 赵雷   |    269 |     80 |      90 |     99 |
| 钱电   |    210 |     70 |      60 |     80 |
| 孙风   |    210 |     80 |      60 |     70 |
| 李云   |    100 |     50 |      30 |     20 |
| 周梅   |    170 |     83 |      0  |     87 |
| 吴兰   |     65 |     31 |      0  |     34 |
| 郑竹   |    187 |     83 |      87 |     0  |
+-----+------+------------+---------+---------+

编写一个 SQL 查询所有学生的成绩，并按 '总分' 倒序排序，如果 '总分' 相同再按 '语文成绩' 排序，以此类推。

select * form score order by '总分' desc, '语文成绩' desc, '数学成绩' desc, '英语成绩' desc;

5 聚合函数

5.1 一般语法：

注：NULL 值不参与聚合函数运算。

在进行查询操作时，往往需要对一整列进行运算，例如可以计算一整列成绩数据的平均值，我们就要使用聚合函数。下面是常见的聚合函数：

select 聚合函数 from 表名;

5.2 聚合函数练习

练习一：求所有学生的 '总成绩'、'平均分'、 '最高分' 、'最低分'

# score 表
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷   | 语文   |      80 |
| 赵雷   | 数学   |      90 |
| 赵雷   | 英语   |      99 |
| 钱电   | 语文   |      70 |
| 钱电   | 数学   |      60 |
| 钱电   | 英语   |      80 |
| 孙风   | 语文   |      80 |
| 孙风   | 数学   |      80 |
| 孙风   | 英语   |      80 |
| 李云   | 语文   |      50 |
| 李云   | 数学   |      30 |
| 李云   | 英语   |      20 |
| 周梅   | 语文   |      76 |
| 周梅   | 数学   |      87 |
| 吴兰   | 语文   |      31 |
| 吴兰   | 英语   |      34 |
| 郑竹   | 数学   |      89 |
| 郑竹   | 英语   |      98 |
+--------+--------+---------+

编写一个 SQL 查询所有学生的 '总成绩'、'平均分'、 '最高分' 、'最低分'

mysql> select s_name, sum(s_score) as'总分', round(avg(s_score),2) as'平均分', max(s_score) as'最高分', min(s_score) as'最低分'
fromtemp_student 
groupby s_name;+-------+-------+-----------+-------+--------+
| s_name | 总分   | 平均分   | 最高分  | 最低分  |
+-------+-------+----------+--------+--------+
| 赵雷   |    269 |     89.67 |        99 |        80 |
| 钱电   |    210 |     70.00 |        80 |        60 |
| 孙风   |    240 |     80.00 |        80 |        80 |
| 李云   |    100 |     33.33 |        50 |        20 |
| 周梅   |    163 |     81.50 |        87 |        76 |
| 吴兰   |     65 |     32.50 |        34 |        31 |
| 郑竹   |    187 |     93.50 |        98 |        89 |
+-------+-------+---------+--------+---------+
7 rows in set (0.00 sec)

6 分组查询

6.1 分组查询语法

select 字段列表 from 表名 [where 分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤]

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

6.2 分组查询练习

练习一：每月交易 I

表：Transactions
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键，该表包含有关传入事务的信息，state 列类型为 ["approved", "declined"] 之一。输入：
Transactions table:
+----+---------+--------+--------+--------+
| id  | country | state | amount | trans_date |
+----+---------+--------+--------+--------+
| 121 | US   | approved | 1000   | 2018-12-18 |
| 122 | US   | declined | 2000   | 2018-12-19 |
| 123 | US   | approved | 2000   | 2019-01-01 |
| 124 | DE   | approved | 2000   | 2019-01-07 |
+-----+-------+---------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

selectdate_format(trans_date, "%Y-%m") as 'month',country,count(*) as trans_count,sum(if(state = 'approved', 1, 0)) as approved_count,sum(amount) as trans_total_amount,sum(if(state = 'approved', amount, 0)) as approved_total_amount
fromTransactions
group bydate_format(trans_date, "%Y-%m"),country;

练习二：即时食物配送 II

配送表: Delivery
+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是该表中具有唯一值的列。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。如果顾客期望的配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。
结果示例如下所示：示例：
输入：
Delivery 表：
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+
输出：
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
解释：
1 号顾客的 1 号订单是首次订单，并且是计划订单。
2 号顾客的 2 号订单是首次订单，并且是即时订单。
3 号顾客的 5 号订单是首次订单，并且是计划订单。
4 号顾客的 7 号订单是首次订单，并且是即时订单。
因此，一半顾客的首次订单是即时的。

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

selectround((sum(if(order_date = customer_pref_delivery_date, 1, 0)) / count(*)) * 100,2) as immediate_percentage
fromDelivery
where(customer_id, order_date) in (selectcustomer_id,min(order_date) as order_datefromDeliverygroupbycustomer_id);

7 分页查询

7.1 分页查询语法

在我们的印象中，网页在展示大量的数据时，往往不是把数据一下全部展示出来，而是用分页展示的形式，其实就是对数据进行分页查询的操作，即每次只查询一页的数据展示到页面上。

select 字段列表 from 表名 limit 查询起始索引,查询条目数;

在limit 关键字中，查询起始索引这个参数是从 0 开始的。

7.2 分页查询练习

练习一：最后一个能进入巴士的人

表: Queue
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id 是这个表具有唯一值的列。
该表展示了所有候车乘客的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。
turn 决定了候车乘客上巴士的顺序，其中 turn=1 表示第一个上巴士，turn=n 表示最后一个上巴士。
weight 表示候车乘客的体重，以千克为单位。有一队乘客在等着上巴士。然而，巴士有1000千克 的重量限制，所以其中一部分乘客可能无法上巴士。
编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。
返回结果格式如下所示。示例 1：
输入：
Queue 表
+-----------+-------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------+--------+------+
| 5         | Alice       | 250    | 1    |
| 4         | Bob         | 175    | 5    |
| 3         | Alex        | 350    | 2    |
| 6         | John Cena   | 400    | 3    |
| 1         | Winston     | 500    | 6    |
| 2         | Marie       | 200    | 4    |
+-----------+-------------+--------+------+
输出：
+-------------+
| person_name |
+-------------+
| John Cena   |
+-------------+

编写解决方案找出最后一个 上巴士且不超过重量限制的乘客

selectq1.person_name
fromQueue q1,Queue q2
whereq1.turn >= q2.turn
groupbyq1.person_id
havingsum(q2.weight) <= 1000
orderbysum(q2.weight) desclimit1;

8 总结

本篇 SQL 学习笔记系统地总结了查询操作的各个方面，包括基础查询、条件查询、排序、聚合、分组及分页等内容。通过详细的语法解析和实践练习，涵盖了从简单的数据检索到复杂的数据处理方法，帮助理解 SQL 在实际应用中的关键用法。同时，笔记中提供了具体的 SQL 练习案例，使读者能够在实践中掌握高效查询的技巧。掌握这些 SQL 技巧，不仅能够提高数据库操作能力，还能为优化查询性能和处理复杂数据分析奠定坚实基础。