Mysql的一些笔记

前言

随便写写，吧啦吧啦，基础还是很重要滴。

数据库事务的特性含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

连接查询

之前网上偶然间看了一道面试题“谈谈Mysql各种连接查询的区别”，而我平时工作中一般都是用的left join然后把查询主表放前面，也就是左边。但是这个问题一下子就暴露了我平时写的sql的局限性，然后下面就总结下Mysql的连接查询吧。

现以下面两张表威为栗子，主表user存储用户基本信息，user_class存储用户班级信息，关联字段为user表的id（对应user_class的user_id字段）

images

inner join

即内连接，两张表关联查询的时候保留两张表中完全匹配的结果集

sql：

SELECT
	u.username,
	u.pwd,
	u.type,
	u.create_time,
	c.class_id,
	c.class_name 
FROM
	USER u
	INNER JOIN user_class c ON u.id = c.user_id

结果集：

images

解释：

如上图所示，主表user通过id关联查询user_class表，会列出其完全匹配的结果集，如若user表中的id字段在user_class中找不到匹配，就不会列出来。

left join

左连接，在两张表连接查询的时候，会返回左边表所有行，即使右表中没有匹配记录

sql：

SELECT
	u.username,
	u.pwd,
	u.type,
	u.create_time,
	c.class_id,
	c.class_name 
FROM
	USER u
	LEFT JOIN user_class c ON u.id = c.user_id

结果集：

images

解释：

可以看到其实左右连接不想内连接，它是有一个主次关系的。上面的左连接，会以左表为主表，关联查询它与另一张表的所有匹配结果。即使‘三毛’这条数据在另一张表里面没有记录与之匹配

right join

右连接，与上面的左连接相反，会返回右边表的所有行，即使左边表中没有记录

sql：

SELECT
	u.username,
	u.pwd,
	u.type,
	u.create_time,
	c.class_id,
	c.class_name 
FROM
	USER u
	RIGHT JOIN user_class c ON u.id = c.user_id

结果集：

images

解释：

这里可以对比左连接，主次关系变了，现在是以右边的表为主表，关联查询它与另一张表的所有匹配结果。即使user_class表里面id为‘1004’这条数据在另一张表里面没有记录与之匹配

full join

在两张表连接查询时，会返回左右表所有没有匹配的行

SELECT
	u.username,
	u.pwd,
	u.type,
	u.create_time,
	c.class_id,
	c.class_name 
FROM
	USER u
	FULL JOIN user_class c ON u.id = c.user_id

结果集：

这里说一下，mysql是不支持全连接的。但是下一节会有相应的解决方案。结果集就是上面左连接右连接的并集

union和union all

实现full join

union和union all用于把来自多个查询语句的结果组合到一个结果集合中。在多个 SELECT 语句中，对应的列应该具有相同的字段属性，且第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称。

ok，有了它我们就可以来实现mysql的full join了

sql：

( SELECT
	u.username,
	u.pwd,
	u.type,
	u.create_time,
	c.class_id,
	c.class_name 
	FROM
		USER u
		LEFT JOIN user_class c ON u.id = c.user_id 
	) UNION
	(
	SELECT
		u.username,
		u.pwd,
		u.type,
		u.create_time,
		c.class_id,
		c.class_name 
	FROM
		USER u
	RIGHT JOIN user_class c ON u.id = c.user_id 
	)

images

注意，要保证两个查询的字段匹配。否则会报错。

区别

上面为什么不用union all 而用union，是因为union会根据结果集智能地去去处重复值，因为正常来说两个select语句，会返回4+4条记录。但是union帮我们去除了重复的记录，就剩下了5条，而union all不会给我们去重，它很任性，它说它只负责查询，如下图，如果我们用union all 结果集就是这样。

images

归纳一下就是

效率： union all 比union要快很多，因为union all只是简单地将两个结果集合并后就之就直接返回了。union就很乖巧，在我们进行连接查询的时候会帮我筛选过滤掉重复记录。
对重复结果的处理：union在进行表链接后会筛选掉重复的记录，union all不会去除重复记录。
对排序的处理：union将会按照字段的顺序进行排序；union all只是简单的将两个结果合并后就返回

所以要是我们能确定合并的两个结果集中不会有重复的记录就使用union all吧，会快很多。

数据库范式

1 第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

2 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

3 第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

SQL优化

以下为转载内容

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
很多时候用 exists 代替 in 是一个好的选择
用Where子句替换HAVING 子句因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

总结

学然后知不足，教然后知困。知不足，然后能自反也；知困，然后能自强也。