快速学习MySQL基础知识-巨人网络通讯

主页 > 知识库 > 快速学习MySQL基础知识

快速学习MySQL基础知识

了解 SQL

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

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

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

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

目前排名较前的 DBMS：

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

认识 SELECT

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

起别名

SELECT name AS n FROM student

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

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

去重重复行

SELECT DISTINCT age FROM student

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

SELECT DISTINCT age,name FROM student

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

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

SELECT DISTINCT age FROM student ORDERY BY name,age DESC

限制返回的数量

SELECT DISTINCT age FROM student ORDERY BY name DESC LIMIT 5

SELECT 的执行顺序

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

对于 SELECT 顺序有两个原则：

关键字的顺序不能颠倒：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

SELECT 会按照如下顺序执行：

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

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 语句。

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

SELECT class_id, COUNT(*) as student_count FROM student \

GROUP BY class_id;

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

SELECT class_id, sex, COUNT(*) as student_count FROM \

student GROUP BY class_id, sex;

HAVING 过滤和 WHERE 的区别

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

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

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

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

子查询

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

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

假设有如下的表结构：

-- ----------------------------
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);

非关联子查询

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

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

SELECT name FROM student WHERE class_id = \

(SELECT class_id FROM student WHERE name='胡一')

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

关联子查询

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

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

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。

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

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.

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

SELECT NAME FROM student as s where \

	NOT EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

集合比较子查询

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

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

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 条索引。

将其模式抽象为：

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 的区别：

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的执行效率会有明显的下降。