MySQL详解一

初识MySQL

之前文章中我们了解了 Redis 数据库，这篇文章主要是带大家了解一下 MySQL 数据库，Redis 数据库是一种内存型的数据库，而 MySQL 数据库对应的是一种结构化的存储数据库，他是存储在磁盘当中的。

SQL

在对 MySQL 进行系统的学习之前，我们需要来了解一些关键性的名词。

结构化查询语言(Structured Query Language) 简称 SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，SQL 是关系数据库系统的标准语言。

关系型数据库包括：MySQL, SQL Server, Oracle, Sybase, postgreSQL 以及 MS Access等。

SQL 命令包括：DQL、DML、DDL、DCL以及TCL；

DQL：Data Query Language - 数据查询语言

• select ：从一个或者多个表中检索特定的记录；

DML：Data Manipulate Language - 数据操作语言

• insert ：插入记录；
• update ：更新记录；
• delete ：删除记录；

DDL：Data Define Languge - 数据定义语言

• create ：创建一个新的表、表的视图、或者在数据库中的对象；
• alter ：修改现有的数据库对象，例如修改表的属性或者字段；
• drop ：删除表、数据库对象或者视图；

DCL：Data Control Language - 数据控制语言

• grant ：授予用户权限；
• revoke ：收回用户权限；

TCL：Transaction Control Language - 事务控制语言

• commit ：事务提交;
• rollback ：事务回滚;

数据库术语

• 数据库：数据库是一些关联表的集合；
• 数据表：表是数据的矩阵；
• 列：一列包含相同类型的数据；
• 行：或者称为记录是一组相关的数据；
• 主键：主键是唯一的；一个数据表只能包含一个主键；
• 外键：外键用来关联两个表，来保证参照完整性；MyISAM 存储引擎本身并不支持外键，只起到注释作用；而 innoDB 完整支持外键；
• 复合键：或称组合键；将多个列作为一个索引键；
• 索引：用于快速访问数据表的数据；索引是对表中的一列或者多列的值进行排序的一种结构。

MySQL体系结构

上图就是一个完整的 MySQL 体系结构 ，我们下面主要为大家介绍一下 MySQL 的连接池组件，至于其他组件，在后续 MySQL 文章的更新过程中也会依次为大家介绍。

对于我们 MySQL 来说，是会存在多个客户端来进行访问的，那么对应的访问形式是什么呢？在 Redis 中采用的 recator 来进行处理大量的并发操作，但是这对于 MySQL 来说并不适用，因为 MySQL 并不会向 Redis 那样去处理特别多的连接，没有必要使用 recator 来进行操作。

MySQL 内部连接池主要是管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求，他的一个网络处理流程就是主线程接收连接，接收连接交由连接池处理；主要处理方式：IO多路复用 select + 阻塞的 IO，就是像下图这样的一个处理方式。

主线程负责接收客户端连接，调用 select 函数为每个客户端 fd 分配一个线程，然后通过 listen 函数检测是否有客户端发起连接，当有客户端连接发起以后，就会为其分配一个线程，调用 while 循环，不同的进行读事件触发，读取数据，这就是 MySQL 内部连接的一个处理方式。

怎么执行一条select语句？

了解了 MySQL 体系结构以后，我们来看一下，一条 MySQL 语句是怎么去被执行的：

数据库设计三范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结，要想设计一个结构合理的关系型数据库，必须满足一定的范式。

MySQL 存在三范式，三范式在一定程度上解释了时间和空间的关系，他所设计的目的就是为了减少空间的占用以及数据冗余。

范式一

确保每列保持原子性，数据库表中的所有字段都是不可分解的原子值。

比如某一个表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将该字段拆分为多个字段，省份、城市、详细地址，这样我们在进行访问的时候，就可以提高我们的一个访问效率。

范式二

满足范式一的基础上，确保表中的每列都和主键完全依赖，而不能只与主键的某一部分依赖（组合索引）。

我们可以看见下面这张表，客户、所属单位和联系方式依赖于订单编号，商品名称、单位和价格依赖于商品编号，那么也就没有保证每一列和主键是完全依赖的，就会出现数据冗余的现象发生，那么此时我们就可以这个表进行拆分。

将这个表拆分为 3 个表以后，我们就可以看出来，每一列和主键就是一个完全依赖的关系了，此时数据冗余这种情况就得到了改善了。

范式三

满足范式二的基础上，确保每列都和主键直接相关，而不是间接相关，减少数据冗余。

我们可以看见下面这张表就做到了范式三的要求。

反范式

范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低，因此基于性能考虑，可能需要进行反范式设计，反范式所指的就是允许一定的数据冗余。

反范式意义就在于提高查询效率，比如本来我们本来只需要一个 select 语句就可以完成的查询，在满足三范式要求以后，可能就会需要好几次查询才可以，查询效率不够。

CRUD

约束

约束对于数据来说是一个很重要的东西，他是数据库为我们提供的一个保证，当约束设置以后，数据库就为我们保证了对应数据的一种特性，约束主要有以下几种：

在后续的学习当中我们就会逐渐的知道这些约束所存在的意义。

接下来就是一些表对应的操作：

创建数据库

CREATE DATABASE `数据库名` DEFAULT CHARACTER SET utf8; # 字符集设置为 utf8

删除数据库

DROP DATABASE `数据库名`;

选择数据库

USE `数据库名`;

创建表

CREATE TABLE `table_name` (column_name column_type);

删除表

DROP TABLE `table_name`; # 把数据和表都删除

清空数据表

TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至少有两行数据），有自增索引的话，从初始
值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的话，从之前值继续累加

添加数据

INSERT INTO `table_name`(`field1`, `field2`, ..., `fieldn`) VALUES (value1,
value2, ..., valuen);

删除数据

DELETE FROM `table_name` [WHERE Clause];

改数据

UPDATE table_name SET field1=new_value1, field2=new_value2 [, fieldn=new_valuen]

这儿主要是是删除数据，我们需要理解三个操作有什么不同：

高级查询

接下来的查询我们以下面这张表为例，我们已经提前插入好数据：

基础查询

全部查询：SELECT * FROM student;

只查询部分字段：SELECT sname, class_id FROM student;

别名、列明、不要用关键字：SELECT sname, class_id FROM student;

把查询出来的结果的重复记录去掉：SELECT distinct class_id FROM student;

条件查询

查询姓名为 孙燕 的学生信息：SELECT * FROM student WHERE sname = ‘孙燕’;

查询性别为 男，并且班级为 2 的学生信息：SELECT * FROM student WHERE gender=“男” AND class_id=2;

范围查询

查询班级id 1 到 3 的学生的信息：SELECT * FROM student WHERE class_id BETWEEN 1 AND 3;

判空查询

判断不为空：SELECT * FROM student WHERE class_id IS NOT NULL;

判断为空：SELECT * FROM student WHERE class_id IS NULL;

判断不为空字符串：SELECT * FROM student WHERE gender <> ‘’;

判断为空字符串：SELECT * FROM student WHERE gender = ‘’;

模糊查询

模糊查询通常使用 like关键字，"%"代表任意数量的字符，”_”代表占位符

查询名字为 孙 开头的学生的信息：SELECT * FROM student WHERE sname LIKE ‘孙%’;

查询姓名里第二个字为 小 的学生的信息：SELECT * FROM student WHERE sname LIKE ‘_洋%’;

分页查询

分页查询主要用于查看第 N 条到第 M 条的信息，通常和排序查询一起使用，使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。

表中默认第一条记录的参数为0。

查询第二条到第三条内容：SELECT * FROM student LIMIT 1,2;

查询后排序

关键字：order by field，asc：升序, desc：降序。

分数以从低到高的顺序进行查询

我们也可以按照多个字段进行排序：

聚合查询

通常聚合查询需要使用聚合函数：

SELECT sum(num) FROM score;

SELECT avg(num) FROM score;

SELECT max(num) FROM score;

SELECT min(num) FROM score;

SELECT count(num) FROM score;

分组查询

将性别进行分组

以性别为基准，对班级进行分组

分别统计男女的数量

根据性别统计数量 > 6 的数据

联表查询

我们通常需要多张表进行比对，此时就会使用到我们的联表查询：

INNER JOIN：只取两张表有对应关系的记录

LEFT JOIN：在内连接的基础上保留左表没有对应关系的记录

RIGHT JOIN：在内连接的基础上保留右表没有对应关系的记录

只保留左表对应的记录

只保留右表对应的记录

子查询/合并查询

单行子查询

多行子查询

• IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
• EXISTS 关键字：内层查询语句不返回查询的记录，而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（ true ），否则，将返回一个假值（ false ）。当返回的值为 true 时，外层查询语句将进行查询；当返回的为 false 时，外层查询语句不进行查询或者查询不出任何记录。
• ALL 关键字：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
• ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。

在 FROM 子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用。

查看老师id为2的教过的学生的信息

接下来我们来做一些练习，首先我们要理清楚对应的几张表的一个关系：

查询平均成绩大于60分的同学的学号和平均成绩；

当前题目中，首先我们要明确平均成绩大于60分针对的是某一个同学，因为一个同学会报多门课，我们所求的是他的所有课程的平均成绩，求平均成绩就需要使用到聚合函数 avg ，而且我们还需要理解当前是需要进行一个去重操作的，所以会用到 group by 语句。

查询 ‘c++高级架构’ 课程比 ‘音视频’ 课程成绩高的所有学生的学号；

该题目首先理解查询的是’c++高级架构’ 课程比 ‘音视频’ 课程成绩高的所有学生，那么针对的每一个学生，此时我们就可以考虑去用两个集合解决问题，c++高级架构作为一个集合，音视频作为一个集合，求两个集合的交集就会两个课程都报了的人；然后再求c++高级架构’ 课程比 ‘音视频’ 课程成绩高的学生。

查询 ‘c++高级架构’ 课程比 ‘音视频’ 课程成绩高的所有学生的学号，如果只报了c++高级，也满足条件；

这个题目就是在上个题目的场景下在修改一下就可以了，默认只报了 ‘c++高级架构’的成绩就比’音视频’ 课程成绩高，可以只用 left join 语句。

查询所有同学的学号、姓名、选课数、总成绩；

当前题目考察的就是 student 和 score 这两张表：
他针对的也是每一个学生，所以就需要用到 group by 语句去重，我们其实依然可以采用集合的思想，将 student_id，course_id，num 先统计称成一张临时表，然后在求整个的数据。

查询没学过 ‘谢小二’ 老师课的同学的学号、姓名；

该题目考察的就是 student 、 teacher 、 course 和 score 四张表：

首先我们需要找到 ‘谢小二老师’ 的课的 tid 与对应的 course 表形成一张新的表B，表示对应的 cid，形成一张新的表B以后，用新的表B与 course 表A使用联表查询找到没有报这个课程 id 的那一部分然后又形成一张表 C，此时的表 C 代表的就是没有报该门课的 student_id，在与 student 表进行比对，最终返回对应的学号和姓名。

查询学过课程编号为 ‘1’ 并且也学过课程编号为 ‘2’ 的同学的学号、姓名；

该题目考察的就是 student 和 score 两张表：

当前就可以使用联表查询的策略：

查询学过 ‘谢小二’ 老师所教的所有课的同学的学号、姓名；

该题目考察的就是 student 、 teacher 、 course 和 score 四张表的联表查询：

查询有课程成绩小于 60 分的同学的学号、姓名；

该题目考察的就是 student 和 score 两张表的联表查询：

查询没有学全所有课的同学的学号、姓名；

该题目考察的就是 student 和 score 两张表的联表查询：

查询至少有一门课与学号为 ‘1’ 的同学所学相同的同学的学号和姓名；

该题目考察的就是 student 和 score 两张表的联表查询：

查询至少学过学号为 ‘1’ 同学所有课的其他同学学号和姓名；

该题目考察的就是 student 和 score 两张表的联表查询：

MySQL视图

定义

视图（ view ）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据，其内容由查询定义。

基表：用来创建视图的表叫做基表，通过视图，可以展现基表的部分数据。

视图数据来自定义视图的查询中使用的表，使用视图动态生成；

优点

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

使用

CREATE VIEW <视图名> AS <SELECT语句>

就像下面这样，视图创建完毕以后，就可以当成一张表去进行使用。

作用

• 可复用，减少重复语句书写；类似程序中函数的作用；
• 重构利器：假如因为某种需求，需要将 user 拆成表 usera 和表 userb；如果应用程序使用 sql 语句：select * from user 那就会提示该表不存在；若此时创建视图 create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name; ，则只需要更改数据库结构，而不需要更改应用程序；
• 逻辑更清晰，屏蔽查询细节，关注数据返回；
• 权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作；