疑难杂症_SQL语言---DQL

前言

SQL，Structured Query Language，结构化查询语言
SQL语言包括：DDL、DML、DQL、DCL、TCL
DQL，Data Query Language，是一种用于查询数据库中的数据语言。与其他数据库语言(如：DML、DDL等不同)，DQL主要关注的是查询过程中所设计到的数据内容，而不涉及数据的增加、删除或修改

---

语法

							执行顺序
SELECT 			子句			6
FROM			子句			1
JOIN...ON...	子句			2
WHERE			子句			3
GROUP BY		子句			4
HAVING			子句			5
ORDER BY		子句			7
LIMIT			子句			8

基础查询_SELECT…FROM…

语法：

SELECT 字段1,字段2,... 
FROM 表名;
-- SELECT子句 用于指定查询表中的字段
-- FROM子句 用于指定数据来自哪张表

示例：
查询表person中所有字段的所有记录

SELECT *
FROM person;

查询表person中的某些指定字段

SELECT id,name,gender,age
FROM person

注意：

• 在实际开发中，java代码若执行DQL语句，不要用*
  因为当使用SELECT *时数据库首先要查询数据字典了解带查询表的表结构，了解全字段后才能进行查询，由此给数据库带来额外的开销，这并不划算。因为java代码仅需要编写一次，因此我们应当在SELECT后将所有字段列出

过滤_WHERE

在DQL语句中，WHERE同样用于指定过滤条件，此时仅将满足该过滤条件的记录查询出来

示例：
查看6岁人员的信息，列出：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age=6;

查看除6岁人员外所有人员的信息，列出：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age<>6;

连接多个条件_AND、OR

AND，与，都为真时才为真
OR，或，都为假时才为假

示例：
查看表person中6岁的男性都有谁，列出：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age=6 AND gender='男';

查看表person中6岁的女性都有谁，列出：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age=6 AND gender='女';

注意：

• AND的优先级大于OR，若出现两者紧挨着的情况，会先进行AND判断再进行OR判断，为了提高OR的优先级，需要使用()

列表_IN、NOT IN

示例：
查看表person中所有6岁、12岁、24岁人员的信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age IN (6,12,24);

查看表person中所有除了6岁、12岁、24岁人员的信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age NOT IN (6,12,24);

闭区间_(NOT) BETWEEN…AND…

BETWEEN...AND...等效于闭区间[]

示例：
查看表person中6~24岁人员的信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age BETWEEN 6 AND 24;

查看表person中除了6~24岁人员外所有人员的信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE age NOT BETWEEN 6 AND 24;

去除重复行_DISTINCT

DISTINCT可以将结果集按照指定字段去除重复行，且其必须紧跟在SELECT关键字之后

示例：
查看表person中的人员年龄组成

SELECT DISTINCT age
FROM person;

模糊查询_LIKE

LIKE有两个可用的通配符：

• _：表示任意一个字符。(确切数量，表示1个字符)
• %：表示任意个字符。(不确切的数量，表示0~任意个字符)

示例：
查看表person中名字中含有玲的人员信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE name LIKE '%玲%';

查看表person中姓王的人员信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE name LIKE '王%';

查看表person中名字以玲结尾的人员信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE name LIKE '%玲';

查看表person中名字中第二个字为莹的人员信息：id，名字，性别，年龄

SELECT id,name,gender,age
FROM person
WHERE name LIKE '_莹%';

空值判断_IS NULL、IS NOT NULL

用于判断一个字段是否为NULL

• IS NULL，判断字段的值是否为NULL
• IS NOT NULL，判断字段的值是否不为NULL

注意！不可以用=和<>判断NULL值

示例：
查看表person中年龄为空人员的名字

SELECT name
FROM person
WHERE age IS NULL;

查看表person中年龄不为空人员的名字

SELECT name
FROM person
WHERE age IS NOT NULL;

排序_ORDER BY

ORDER BY子句用于对查询结果集进行排序，可以按照ORDER BY后指定的字段值进行升序或降序排序

• 升序
  ORDER BY 字段 [ASC]，ASC可省，此为将当前结果集按照指定的字段值从小到大排序
• 降序
  ORDER BY 字段 DESC，此为将当前结果集按照指定的字段值从大到小排序

ORDER BY可以按照多个字段排序，此时排序存在优先级：

• 首先按照ORDER BY后指定的第一个字段的值将结果集按照该字段排序方式(升序或降序)排序
• 排序后再按照第二个字段指定的值排序，仅会对第一个字段值相同的记录按照第二个字段排序
• 若有第三个字段以此类推

示例：
查看表person中人员年龄与名字，按年龄从大到小排列

SELECT name,age
FROM person
ORDER BY age DESC;

查看表person中人员生日与名字，按生日从大到小排列

SELECT name,birth
FROM  person
ORDER BY birth;
-- 因为生日的锚定物为1970年元旦，据这个时间点越远则越大，但是相对来说年龄越小，生日越小，所以birth按照从小到大进行排列得到的是年龄从大到小的排列

分页查询_LIMIT m,n

分页查询的作用是将一个DQL语句的查询结果分段查询出来
当一条DQL语句可以查询出非常大量的数据时，我们可以分批将数据查询，避免因为数据庞大导致系统过度的资源开销

注意！分页查询是方言，并没有在SQL92标准中有语法定义，这意味着不同的数据库分页方式的SQL可能完全不同

• ORACLE中使用伪列ROWNUM完成
• MySQL和MariaDB则使用LIMIT子句完成

语法：

SELECT ...
FROM ...
...
ORDER BY ...
LIMIT m,n;
-- m：表示跳过结果集的m条记录
-- n：表示从跳过的m条记录后连续检索n条记录

在分页查询中有两个常见的参数：

• 页数：当前显示第几页的内容
• 条目数：每页显示的条目数

由此得到分页公式：

• m：(页数-1)*条目数
• n：条目数

示例：
查看表person中年龄最小的前5名的名字、年龄、性别

SELECT name,age,gender
FROM person
ORDER BY age
LIMIT 0,5;

---

在DQL中使用函数或表达式

在SELECT子句中使用表达式

示例：

SELECT name,salary,salary*12
FROM person;

在SELECT子句中使用函数

IFNULL函数

语法：

IFNULL(arg1, arg2)

作用：

• 当arg1的值不为NULL时，函数返回arg1的值
• 当arg2的值为NULL时，函数返回arg2的值

示例：

SELECT IFNULL(salary,0)
FROM person;

在WHERE中使用表达式

示例：
查看表person中年薪高于60000的成员信息：id，名字，月薪

SELECT id,name,salary
FROM person
WHERE IFNULL(salary,0)*12 > 60000;

在WHERE中使用函数

示例：
查看表person中哪些成员的奖金少于3000，展示其：id，名字，奖金

SELECT id,name,comm
FROM person
WHERE IFNULL(comm,0)<3000;

别名

• 别名通常使用在SELECT子句和FROM子句中
• 在SELECT子句中我们可以为字段取别名
  • 当字段为函数或表达式时，我们通常给字段添加别名，为了增加可读性
  • 为SELECT中的子查询取别名
• 在FROM子句中可以为表添加别名

语法：

字段名<空格>别名

示例：

SELECT name,salary sal,salary*12 'annusal'
-- 无论是直接空格跟还是 单引号/双引号 括都可以，只不过 单引号/双引号 中允许名称中额外含有空格
-- 两种语法中的<空格>部分也可以替换为：<空格>AS<空格>，不过不推荐这么写
FROM person;

---

聚合函数

概念

聚合函数又称为多行函数、分组函数。对记录按照指定字段统计的，可以将多行记录统计出一行记录

聚合函数分类

• MIN：统计指定字段的最小值
• MAX：统计指定字段的最大值
• SUM：对指定字段求和
• AVG：对指定字段求平均值
• COUNT：统计记录数的

注意事项

MIN、MAX、SUM、AVG是对值的统计，而COUNT是对记录数的统计
聚合函数忽略NULL值，这个在AVG、COUNT上尤为明显

示例

查看表person中年龄为12~24岁成员的总人数

SELECT COUNT(*)
FROM person
WHERE age BETWEEN 12 AND 24;

查看表person中成员的最大年龄

SELECT MAX(age)
FROM person

查看表person中成员年龄的平均值

SELECT AVG(age)
FROM person;

分组_GROUP BY

GROUP BY子句可以将结果集按照指定字段值相同的记录进行分组，配合聚合函数可以实现组内统计

• 在SELECT子句中出现聚合函数时，那么不在聚合函数中的字段都要出现在GROUP BY子句中
• GROUP BY子句是配合聚合函数使用的，如果SELECT子句中没有聚合函数，通常不写GROUP BY

按单字段分组

示例：
统计表person中每种职业的人数

SELECT COUNT(*),job
FROM person
GROUP BY job;

按多字段分组

分组规则为：各字段的值都一样的记录视作一组

示例：
统计表person中同性别同职业的人数

SELECT COUNT(*),job,gender
FROM person
GROUP BY job,gender;

按照聚合函数的结果排序

示例：
统计表person中各职业的平均工资

SELECT AVG(IFNULL(salary,0)),job
FROM person
GROUP BY job
ORDER BY AVG(IFNULL(salary,0));

过滤(通过聚合函数)_HAVING

HAVING子句用于分组中的过滤条件

注意：

• 聚合函数不能再WHERE子句中使用
  • WHERE子句中使用聚合函数错误的原因：过滤时机不对
    • WHERE的过滤时机是在第一次检索表中每一条记录时进行过滤的，用于确定结果集的记录
    • 聚合函数的过滤是在WHERE之后进行的：
      聚合函数进行过滤的前提是先从表中获得初始结果集，然后通过限制条件对初始结果集进行过滤，得到聚合函数过滤后的结果集
• HAVING子句是紧跟在GROUP BY子句之后，用于对分组进行过滤的子句

HAVING和WHERE的区别

• WHERE是在第一次检索表数据时用于添加过滤条件，确定结果集
• HAVING是在GROUP BY之后(将结果集分组之后)添加过滤条件的，用于确定分组
• 过滤时机不同，作用不同

示例：
查看表person中平均工资高于6000的职业

SELECT job,salary,( SELECT AVG(IFNULL(salary,0) FROM person) ) 'avg_sal'
FROM person
GROUP BY job
HAVING AVG(salary)>6000;

子查询_SubQuery

概念

嵌套在一个SQL语句中的DQL语句，该DQL被称为子查询

应用场景

• DQL中使用子查询
  • 在SELECT子句中，将当前子查询结果作为一个字段展示
  • 在WHERE子句中，将当前子查询结果作为过滤条件使用(最常用的场景)
• DML中使用子查询
  • 将一个查询结果集用于增删改操作
• DDL中使用子查询
  • 在创建表时可以将一个子查询结果集当作表创建
  • 在创建视图时使用一个子查询结果集

子查询分类

• 单行单列子查询，该子查询的结果集只有一个值
• 多行单列子查询，该子查询结果集是多个值
• 多列子查询(单行多列子查询 | 多行多列子查询)，将该子查询当作一张表使用

在DQL中使用子查询

单行单列子查询
示例：
查看表person中平均工资大于所有成员平均工资的工作

SELECT AVG(salary)
FROM person
WHERE salary>(
	SELECT AVG(salary) FROM person
)
GROUP BY job;

多行单列子查询
多行单列子查询可以检索出若干个值，因此作为过滤条件使用时需要：

• 判断等值，要配合：IN、NOT IN使用，等于是不能同时等于好几个值的，只能等于其中之一
• 判断>、<、>=、<=时，要搭配ANY和ALL使用
  • >ANY(列表)：大于列表其中之一，判断标准：大于列表最小值即可
  • >ALL(列表)：大于列表所有，判断标准：大于列表最大值
  • <ANY(列表)：小于最大的即可
  • <ALL(列表)：小于最小的即可
• 示例：
  • 查看表person中与赵莹、周玲同职业的成员都有谁

    SELECT name,age
    FROM person
    WHERE job=(
      SELECT job FROM person WHERE name IN ('赵莹','周玲')
    );
    

在DML语句中使用子查询

示例：
给表person中老师职业的成员上涨500元工资

UPDATE person
SET salary=salary+500
WHERE job_id=(
	SELECT job_id
	FROM person
	WHERE job='老师'
);

在DDL语句中使用子查询

可以将一个查询结果集当作一张表创建出来

示例：
创建一张新表teacher，该表中记录了每个老师的工资情况，要求：按工资从高到低排列，展示其名字和工资

CREATE TABLE teacher
AS
SELECT name,salary
FROM person
WHERE job='老师'
ORDER BY salary DESC;