快速学习MySQL基础知识

吾爱主题阅读：163 2024-04-05 16:22:00 评论：0

这篇文章主要梳理了 SQL 的基础用法，会涉及到以下方面内容：

SQL大小写的规范
数据库的类型以及适用场景
SELECT 的执行过程
WHERE 使用规范
MySQL 中常见函数
子查询分类
如何选择合适的 EXISTS 和 IN 子查询

了解 SQL

SQL 是我们用来最长和数据打交道的方式之一，如果按照功能划分可分为如下 4 个部分：

DDL，数据定义语言。定义数据库对象，数据表，数据列。也就是，对数据库和表结构进行增删改操作。
DML，数据操作语言。对数据表的增删改。
DCL，数据控制语言。定义访问权限和安全级别。
DQL，数据查询语言。用来查询数据。

平时在编写 SQL 时，可能发现许多 SQL 大小写不统一，虽然不会影响 SQL 的执行结果，但保持统一的书写规范，是提高效率的关键，通常遵循如下的原则：

表名，表别名，字段名，字段别名等用小写。
SQL 保留字，函数名，绑定变量等用大写。
数据表，字段名采用下划线命名。

目前排名较前的 DBMS：

关系型数据库：建立在关系模型上的数据库，在建表时，通常先设计 ER 图表示之间的关系。
键值型数据库：以 key-value 的形式存储数据，优点是查找速度快，缺点是无法向关系型数据库一样使用如 WHERE 等的过滤条件。常见场景是作为内容缓存。
文档型数据库，在保存时以文档作为处理信息的基本单位。
搜索引擎：针对全文检索而设计。核心原理是 “倒排索引”。
列式数据库：相对于如 MySQL 等行式存储的数据库，是以列将数据存在数据库中，由于列具有相同的数据类型，所以可以更好的压缩，从而减低系统的 I/O，适用于分布式文件系统，但功能相对有限。
图形数据库，利用图的数据结构存储实体之间的关系。比如社交网络中人与人的关系，数据模型为节点和边来实现。

认识 SELECT

SELECT 一般是在学习 SQL 接触的第一个关键字，基础的内容就是不提了，这里整理常用的规范：

起别名

1	`SELECT` `name` `AS` `n` `FROM` `student`

查询常数, 增加一列固定的常数列：

1	`SELECT` `'学生信息'` `as` `student_info,` `name` `FROM` `student`

去重重复行

1	`SELECT` `DISTINCT` `age` `FROM` `student`

需要注意的是 DISTINCT 是对后面的所有列进行去重, 下面这种情况就会对 age 和 name 的组合进行去重。

1	`SELECT` `DISTINCT` `age,` `name` `FROM` `student`

排序数据,ASC 代表升序，DESC 代表降序

如先按照 name 排序，name 相等的情况下按照 age 排序。

1	`SELECT` `DISTINCT` `age` `FROM` `student ORDERY` `BY` `name` `,age` `DESC`

限制返回的数量

1	`SELECT` `DISTINCT` `age` `FROM` `student ORDERY` `BY` `name` `DESC` `LIMIT 5`

SELECT 的执行顺序

了解了 SELECT 的执行顺序，才能更好地写出更有效率的 SQL。

对于 SELECT 顺序有两个原则：

关键字的顺序不能颠倒： ?

1 SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
SELECT 会按照如下顺序执行： ?

1 FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

1 2 3 4 5 6 7 SELECT DISTINCT student_id, name , count (*) as num #顺序5 FROM student JOIN class ON student.class_id = class.class_id #顺序1 WHERE age > 18 #顺序2 GROUP BY student.class_id #顺序3 HAVING num > 2 #顺序4 ORDER BY num DESC #顺序6 LIMIT 2 #顺序7

在逐一分析下这个过程前，我们需要知道在上面的每一个步骤中都会产生一个虚拟表，然后将这个虚拟表作为下一个步骤中作为输入，但这一过程对我们来说是不可见的：

从 FROM 语句开始，对 student 和 class 表进行 CROSS JOIN 笛卡尔积运算，得到虚拟表 vt 1-1；
通过 ON 筛选，在 vt1-1 的基础上进行过滤然后得到表 vt 1-2；
添加外部行。如使用左连接，右连接和全连接时，就会涉及到外部行，会在 vt1-2 的基础上增加外部行，得到 vt1-3。
如果超过两张表，就会重复上面的步骤。
在拿到最终的 vt1 的表数据后，会执行 WHERE 后面的过滤阶段，得到表 vt2.
接着到 GROUP 阶段，进行分组得到 vt3.
接着到 HAVING 阶段，对分组的数据进行过滤，得到 vt4.
后面进入 SELECT 阶段，提取需要的字段，得到 vt5-1，接着通过 DISTINCT 阶段，过滤到重复的行，得到 vt5-2.
然后对指定的字段进行排序，进入 ORDER BY 阶段，得到 vt6.
最后在 LIMIT 阶段，取出指定的行，对应 vt7，也就是最后的结果。

如果涉及到函数的计算比如 sum() 等，会在 GROUP BY分组后，HAVING 分组前，进行聚集函数的计算。

涉及到表达式计算，如 age * 10 等，会在 HAVING 阶段后，SELECT 阶段前进行计算。

通过这里，就可以总结出提高 SQL 效率的第一个方法：

使用 SELECT 时指定明确的列来代替 SELECT * . 从而减少网络的传输量。

使用 WHERE 进行过滤

使用 WHERE 筛选时，常有通过比较运算符，逻辑运算符，通配符三种方式。

对于比较运算符，常用的运算符如下表。

对于逻辑运算符来说，可以将多个比较运行符连接起来，进行多条件的筛选，常用的运算符如下：

需要注意的是，当 AND 和 OR 同时出现时，AND 的优先级更高会先被执行。当如果存在 () 的话，则括号的优先级最高。

使用通配符过滤：

like：（%）代表零个或多个字符，（_）只代表一个字符

函数

和编程语言中的定义的函数一样，SQL 同样定义了一些函数方便使用，比如求和，平均值，长度等。

常见的函数主要分为如下四类，分类的原则是根据定义列时的数据类型：

算术函数：

字符串函数

需要注意的是，在使用字符串比较日期时，要使用 DATE 函数比较。

日期函数

转换函数：

CAST 函数在转换数据类型时，不会四舍五入，如果原数值是小数，在转换到整数时会报错。

在转换时可以使用 DECIMAL(a,b) 函数来规定小数的精度，比如 DECIMAL(8,2) 表示精度为 8 位 - 小数加整数最多 8 位。小数后面最多为 2 位。

然后通过 SELECT CAST(123.123 AS DECIMAL(8,2)) 来转换。

聚集函数

通常情况下，我们会使用聚集函数来汇总表的数据，输入为一组数据，输出为单个值。

常用的聚集函数有 5 个：

其中 COUNT 函数需要额外注意，具体的内容可以参考这篇。

如何进行分组

在统计结果时，往往需要对数据按照一定条件进行分组，对应就是 GROUP BY 语句。

比如统计每个班级的学生人数：

1 2	`SELECT` `class_id,` `COUNT` `(*)` `as` `student_count` `FROM` `student \` `GROUP` `BY` `class_id;`

GROUP BY 后也可接多个列名，进行分组，比如按照班级和性别分组：

1 2	`SELECT` `class_id, sex,` `COUNT` `(*)` `as` `student_count` `FROM` `\` `student` `GROUP` `BY` `class_id, sex;`

HAVING 过滤和 WHERE 的区别

和 WHERE 一样，可以对分组后的数据进行筛选。区别在于 WHERE 适用于数据行，HAVING 用于分组。

而且 WHERE 支持的操作，HAVING 也同样支持。

比如可以筛选大于2人的班级：

1 2 3 SELECT class_id, COUNT (*) as student_count FROM student \ GROUP BY class_id \ HAVING student_count > 20;

子查询

在一些更为复杂的情况中，往往会进行嵌套的查询，比如在获取结果后，该结果作为输入，去获取另外一组结果。

在 SQL 中，查询可以分为关联子查询和非关联子查询。

假设有如下的表结构：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int (11) NOT NULL AUTO_INCREMENT, ` name ` varchar (20) NOT NULL DEFAULT '' , `age` int (3) NOT NULL , `sex` varchar (10) NOT NULL DEFAULT '' , `class_id` int (11) NOT NULL COMMENT '班级ID' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of Student -- ---------------------------- INSERT INTO `student` VALUES ( '1' , '胡一' , 13, '男' , '1' ); INSERT INTO `student` VALUES ( '3' , '王阿' , 11, '女' , '1' ); INSERT INTO `student` VALUES ( '5' , '王琦' , 12, '男' , '1' ); INSERT INTO `student` VALUES ( '7' , '刘伟' , 11, '女' , '1' ); INSERT INTO `student` VALUES ( '7' , '王意识' , 11, '女' , '2' ); -- ---------------------------- DROP TABLE IF EXISTS `student_activities`; CREATE TABLE `student_activities` ( `id` int (11) NOT NULL AUTO_INCREMENT, ` name ` varchar (20) NOT NULL DEFAULT '' , `stu_id` int (11) NOT NULL COMMENT '班级ID' , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; INSERT INTO `student_activities` VALUES ( '1' , '博物馆' , 1); INSERT INTO `student_activities` VALUES ( '3, ' 春游', 3);

非关联子查询

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件接着执行。

这里想要查询和胡一相同班级的同学名称：

1 2	`SELECT` `name` `FROM` `student` `WHERE` `class_id = \` `(` `SELECT` `class_id` `FROM` `student` `WHERE` `name` `=` `'胡一'` `)`

这里先查到胡一的班级，只有一次查询，再根据该班级查找学生就是非关联子查询。

关联子查询

如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部

再举个例子, 比如查询比每个班级中比平均年龄大的学生姓名信息：

1 2	`SELECT` `name` `FROM` `student` `as` `s1` `WHERE` `age >` `(` `SELECT` `AVG` `(age)` `FROM` `student` `as` `s2` `where` `s1.class_id = s2.class_id)`

这里根据每名同学的班级信息，查找出对应班级的平均年龄，然后做判断。子查询每次执行时，都需要根据外部的查询然后进行计算。这样的子查询就是关联子查询。

EXISTS 子查询

在关联子查询中，常会和 EXISTS 一起使用。用来判断条件是否满足，满足的话为 True，不满足为 False。

比如查询参加过学校活动的学生名称：

1 2	`SELECT` `NAME` `FROM` `student` `as` `s` `where` `\` `EXISTS(` `SELECT` `stu_id` `FROM` `student_activities` `as` `sa` `where` `sa.stu_id=s.id)`

同样 NOT EXISTS 就是不存在的意思，满足为 FALSE , 不满足为 True.

比如查询没有参加过学校活动的学生名称：

1 2	`SELECT` `NAME` `FROM` `student` `as` `s` `where` `\` `NOT` `EXISTS(` `SELECT` `stu_id` `FROM` `student_activities` `as` `sa` `where` `sa.stu_id=s.id)`

集合比较子查询

可以在子查询中，使用集合操作符，来比较结果。

还是上面查询参加学校活动的学生名字的子查询, 同样可以使用 IN：

1	`SELECT` `name` `FROM` `student` `WHERE` `id` `IN` `(` `SELECT` `stu_id` `FROM` `student_activities)`

EXISTS 和 IN 的区别

既然 EXISTS 和 IN 都能实现相同的功能，那么他们之间的区别是什么？

现在假设我们有表 A 和表 B，其中 A，B 都有字段 cc，并对 cc 建立了 b+ 索引，其中 A 表 n 条记录，B 表 m 条索引。

将其模式抽象为：

1 2 3 SELECT * FROM A WHERE cc IN ( SELECT cc FROM B) SELECT * FROM A WHERE EXIST ( SELECT cc FROM B WHERE B.cc=A.cc)

对于 EXISTS 来说，会先对外表进行逐条循环，每次拿到外表的结果后，带入子查询的内表中，去判断该值是否存在。

伪代码类似于下面：

for i in A
    for j in B
        if j.cc == i.cc:
         return result

首先先看外表 A，每一条都需要遍历到，所以需要 n 次。内表 B，在查询时由于使用索引进而查询效率变成 log(m) B+ 的树高，而不是 m。

进而总效率：n * log(m)

所以对于 A 表的数量明显小于 B 时，推荐使用 EXISTS 查询。

再看 IN ，会先对内表 B 进行查询，然后用外表 A 进行判断，伪代码如下：

for i in B
    for j in A
        if j.cc == i.cc:
         return result

由于需要首先将内表所有数据查出，所以需要的次数就是 m. 再看外表 A ，由于使用了 cc 索引，可将 n 简化至 log(n), 也就是 m * log(n).

所以对于 A 表的数据明显大于 B 表时，推荐使用 IN 查询。

总结一下对于 IN 和 EXISTS时，采用小表驱动大表的原则。

这里再扩展下 NOT EXISTS 和 NOT IN 的区别：

1 2 3 SELECT * FROM A WHERE cc NOT IN ( SELECT cc FROM B) SELECT * FROM A WHERE NOT EXIST ( SELECT cc FROM B WHERE B.cc=A.cc)

对于 NOT EXITS 来说，和 EXISTS 一样，对于内表可以使用 cc 的索引。适用于 A 表小于 B 表的情况。

但对于 NOT IN 来说，和 IN 就有区别了，由于 cc 设置了索引 cc IN (1, 2, 3) 可以转换成 WHERE cc=1 OR cc=2 OR cc=3 , 是可以正常走 cc 索引的。但对于 NOT IN 也就是转化为 cc!=1 OR cc!=2 OR cc!=3 这时由于是不等号查询，是无法走索引的，进而全表扫描。

也就是说，在设置索引的情况下 NOT EXISTS 比 NOT IN 的效率高。

但对于没有索引的情况，IN 和 OR 是不同的：

一、操作不同
1、in：in是把父查询表和子查询表作hash连接。
2、or：or是对父查询表作loop循环，每次loop循环再对子查询表进行查询。

二、适用场景不同
1、in：in适合用于子查询表数据比父查询表数据多的情况。
2、or：or适合用于子查询表数据比父查询表数据少的情况。

三、效率不同
1、in：在没有索引的情况下，随着in后面的数据量越多，in的执行效率不会有太大的下降。
2、or：在没有索引的情况下，随着or后面的数据量越多，or的执行效率会有明显的下降。