SQL速查《SQL基础教程》学习笔记（上）单表查询

最新推荐文章于 2024-08-10 22:43:45 发布

是阿福呀

最新推荐文章于 2024-08-10 22:43:45 发布

阅读量729

点赞数 4

分类专栏： MySQL

本文链接：https://blog.csdn.net/The_Rising_Sun_/article/details/90145633

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL速查《SQL基础教程》学习笔记不定期更新，记录一些必会的常见SQL语法和易错点。
本文是上篇，主要记录单表查询。

聚合函数与分组（GROUP BY子句）

聚合函数（将多行汇总为一行）

COUNT ：计算表中的记录数（行数）
SUM ：计算表中数值列中数据的合计值
AVG ：计算表中数值列中数据的平均值
MAX ：求出表中任意列中数据的最大值
MIN ：求出表中任意列中数据的最小值

聚合函数的参数是列名（字段名，属性名），
对于COUNT函数来说，可以用 * 来表示所有字段（星号仅限COUNT函数使用）
COUNT () 会得到包含 NULL 的数据行数，而 COUNT (< 列名 >) 会得到 NULL 之外的数据行数（即不包含NULL）。

对于只包含NULL的表，SELECT COUNT(), COUNT(col_1) FROM NullTbl;
结果为：

在这里插入图片描述

SUM和AVG在计算时会把NULL数据过滤掉
MAX/MIN 函数和 SUM/AVG 函数有一点不同，那就是 SUM/AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

使用COUNT函数时，加入DISTINCT关键字作为参数可以去重，例如：
在这里插入图片描述
SUM函数也可以使用DISTINCT

分组（GROUP BY子句）

如果聚合键中有NULL取值，也会被分成1组。
在这里插入图片描述

各种子句的书写顺序：

SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

而实际的执行顺序为：
FROM → WHERE → GROUP BY → SELECT

使用 GROUP BY 子句和聚合函数时的注意事项：
1、使用 GROUP BY 子句时， SELECT 子句中不能出现聚合键之外的列名。一般此时SELECT子句中只能出现：常数、聚合函数、 GROUP BY 子句中指定的列名（也就是聚合键）三种
2、GROUP BY 子句的结果没有固定的排列顺序，如需排序可以在SELECT子句中定义
3、WHERE子句中不能使用聚合函数，因为WHERE是筛选数据时使用的，实际上，只有 SELECT 子句、 HAVING 子句和ORDER BY 子句中能够使用 COUNT 等聚合函数。并且， HAVING 子句可以非常方便地实现上述要求。

GROUP BY 也可以实现按多列分组，也就是说先按照col1分组，每一个组内按照col2再进行细分。写法是在 GROUP BY 子句中将多个col使用逗号分隔开。
eg: SELECT …… GROUP BY class_id, gender;

WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件。对集合指定条件就需要使用HAVING子句。
HAVING 子句起始点是一次汇总后的结果（HAVING伴随GROUP BY），作用是对GROUP BY分组之后的结果做筛选，因此HAVING 子句的组成要素包括：常数、聚合函数、 GROUP BY 子句中指定的列名（即聚合键）。
在这里插入图片描述
还有一些条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中，从逻辑意义及效率的角度来看，此时更应该写在WHERE 子句中。

子句的书写顺序

SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →5. HAVING 子句 → 6. ORDER BY 子句

使用 HAVING 子句时 SELECT 语句的执行顺序
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

LIMIT 子句：用于限制获取的记录数量

限制记录数，限制获取从首条到指定数量的记录
写法： LIMIT 数量;
eg: SELECT * FROM my_student LIMIT 2;

分页，限制获取指定区间的数据
写法1：LIMIT offset, length; // offset：偏移量，从第几条开始（首条从0开始计数）; length：具体获取多少条记录
eg: LIMIT 0, 2 指从第一条开始取两条； LIMIT 2, 2 指从第三条开始取两条

写法2： LIMIT length OFFSET offset;
eg: LIMIT 2 OFFSET 3 等价于 LIMIT 3, 2 是指查询4-5两条记录

事务（transaction）

● 事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理。
● 事务处理的终止指令包括 COMMIT （提交处理）和 ROLLBACK （取消处理）两种。
● DBMS的事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）四种特性。通常将这四种特性的首字母结合起来，统称为ACID特性。

MySQL中事务的开始和结束语句：
START TRANSACTION; / BEGIN;
COMMIT;

几乎所有的数据库产品的事务都无需开始指令。这是因为大部分情况下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户再明确发出开始指令。
像这样不使用指令而悄悄开始事务的情况下，应该如何区分各个事务呢？通常会有如下两种情况。
A 每条SQL语句就是一个事务（自动提交模式，自动提交的情况需要特别注意的是 DELETE 语句）
B 直到用户执行 COMMIT 或者 ROLLBACK 为止算作一个事务

ACID
一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。对事务来说，这些不合法的 SQL 会被回滚。也就是说，这些 SQL 处理会被取消，不会执行。
隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。
持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。
保证持久性的方法中最常见的就是将事务的执行记录保存到硬盘等存储介质中（该执行记录称为日志）。当发生故障时，可以通过日志恢复到故障发生前的状态。

子查询

视图是将常用的SELECT语句保存下来（一般包含常用的GROUP BY分组等操作），为复杂查询提供便利。子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中，是一张一次性视图。

视图
在这里插入图片描述
子查询，注意执行步骤是先执行括号里子查询的部分

原则上子查询必须设定名称，因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称。在MySQL上测试如果子查询没有设定别名，会报ERROR 1248的错误。

标量子查询

标量子查询就是返回单一值的子查询。必须而且只能返回 1 行 1 列的结果，也就是返回表中某一行的某一列的值，例如“10”或者“东京都” 这样的值。标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中。
在这里插入图片描述
使用标量子查询就可以解决WHERE子句中不能使用聚合函数的问题。上图中错误示范的正确打开方式为下图：

能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用标量子查询。

在这里插入图片描述

标量子查询的使用注意事项：该子查询绝对不能返回多行结果。
如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中。

关联子查询（重要）

如果想在每个商品类别组内，查找出组内价格高于组内平均价格的商品，很容易写出下面的语句：
在这里插入图片描述
但是由于 > 运算符右边的应该是一个标量子查询的结果，而这里右边子查询的结果不是单一的标量。正确的写法为：

这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件。该条件的意思就是，在同一商品种类中对各商品的销售单价和平均单价进行比较。这次由于作为比较对象的都是同一张 Product 表，因此为了进行区别，分别使用了 P1 和 P2 两个别名。

在细分的组内进行比较时，需要使用关联子查询。关联子查询也是用来对集合进行切分的。关联子查询实际只能返回 1
行结果。这也是关联子查询不出错的关键。
在这里插入图片描述
关联子查询的注意事项：结合条件一定要写在子查询中。 SQL是按照先内层子查询后外层查询的顺序来执行的。这样，子查询执行结束时只会留下执行结果，作为抽出源的 P2 表其实已经不存在了。因此，在执行外层查询时，由于 P2 表已经不存在了，因此就会返回“不存在使用该名称的表”这样的错误。