Mysql 基础重点

1. 基本的查询

当数据库中你的表名或字段名和数据库的关键字重名时，你写的sql语句就会语法分析出错，这时你可以将他们用 ``(着重号) 包括起来

-- 例如我们有一张表的名称叫做 insert 可以使用如下的方法进行查询
select * from `insert`

2. 运算符

1. sql 语法中如果进行字符串和数字类型之间的运算或比较，会默认对字符串进行隐式转换，例如 1 + ‘2’ 中 ‘2’ 会被转换为数字 2，最终得到运算结果为 3 。

   但是某些字符可能没有办法被转换为数字，例如 1+ ‘a’ 中由于 ‘a’ 无法成功的转换为数字，不过要注意的是这里并不会报错，而是会把 ‘a’ 当作 0，所以最终的运算结果为 1

2. 运算或比较中如果有一个参数为 NULL 那么结果就是 NULL。例如 NULL = NULL的结果为NULL，并不是相等。100 * NULL的结果也是 NULL

3. sql 中相等为 1，不相等为 0

4. <=> : 安全等于运算符，他和 = 的区别就是，NULL <=> NULL 的结果为 1，也就是说它可以对 NULL进行比较。

5. and 的优先级大于 or 的优先级

6. where 语句中不能使用别名

-- 执行顺序是先 from 查出 test 中的数据再执行 where 进行过滤这个时候还没有 select 所以 where 中不能使用别名
select *
from test
where ...

3. 多表查询

1. 从 sql 优化的角度来说，在多表关联查询的 sql 语句中，select 时最好指定每一个字段的表名（可以这样理解，因为时多表查询，所以不知道每一个字段是来自于哪张表中，如果不指定表名，还得去判断每一个字段来源于哪一张表，还要判断这个字段是不是存在于多张表中，就会浪费一定的时间）
2. 开发中能使用 union all 就不要使用 union，因为 union 会进行去重操作，所以效率会低一些。如果明确知道合并后的结果集中不存在重复的数据，那么就要尽可能的使用 union all，这样可以提高查询效率。
3. Mysql 不支持满外连接 FULL OUTER JOIN ，在 oracle 中是支持的。所以在 Mysql 中如果想要实现满外连接的效果，就要使用 union 或 union all
4. sql 中的连接，Mysql不支持满外连接（下图中的6号），所以我们需要另辟蹊径，使用 union的方法，比如可以通过 1号 union all 4号 得到 6 号，来实现满外连接
   
5. 阿里巴巴开发手册中规定，超过三个表禁止 join，关联的字段要有索引。多表 join 就相当于是嵌套 for 循环，对性能消耗比较大

4. 常用函数

4.1 字符函数

1. 连接多个字符串 concat(str1,str2,str3…) ⇒ str1str2str3
2. 以指定符号连接多个字符串 concat_ws(–,str1,str2,str3) ⇒ str1–str2–str3
3. 将第2个索引位置开始向后3个字符替换为指定字符 INSERT(‘123456789’, 2, 3, ‘xxxxxxxx’) ⇒ 1xxxxxxxx56789 （注意这里的索引是从1开始）
4. 替换指定字符为另一个指定字符 REPLACE(‘hello’, ‘h’, ‘xxx’) ⇒ xxxello
5. 转大写 UPPER(‘hello’) ==> HELLO
6. 转小写 LOWER(‘HELLO’) ==> hello
7. 返回左边x个字符 LEFT(‘hello’, 1), ==> h
8. 返回右边x个字符 RIGHT(‘hello’, 1) ==> o

4.2 流程控制函数

1. IF(money > 10000, ‘有钱’，‘没钱’) 如果money > 10000 为 true 则返回有钱否则返回没钱
2. IFNULL(a, b) 如果 a 不为 null 返回 a，否则返回 b
3. case when … than … when … than … else … end 类似于 if … else if … else if … else …
4. case … when … than … when … than … else … end 类似于 switch case

4.3 聚合函数

1. AVG、SUM AVG() = SUM() / COUNT()
2. MIN、MAX （可以order by 排序的字段就可以比较大小）
3. WHERE 后不能使用聚合函数，想要使用要写在 HAVING 后
4. HAVING 一般是和 GROUP BY 一起使用，当然单独使用也不会报错，但是没有任何意义，因为HAVING 后使用的一般都是聚合函数，这样不管查询什么最后都是一条记录
5. 聚合函数不能嵌套使用，比如 MIN(AVG(money)) 是错误的
6. count(字段) 如果字段可以为 null ，那么count 时会忽略为 null 的记录，造成计数不准确
7. avg(字段)如果字段可以为 null，那么avg时会忽略为 null 的记录

5. SQL 执行流程

from => join => where => group by => having => select distinct => order by => limit

where 中的条件虽然也可以写在 having 中但是这样效率会降低，因为 having 前还有 group by 流程，未经过 where 筛选的数据直接进入到 group by 可能会对性能造成影响

6. 子查询

6.1 分类角度一：单行子查询、多行子查询

单行子查询：子查询的结果只有一条记录
多行子查询：子查询的结果是多条记录

1. 单行子查询

单行子查询只能使用单行操作符，例如下面这些
单行操作符

操作符	含义
=	等于
<>	不等于
<	小于
>	大于

空值场景
子查询结果如果为空，整体查询结果也为空

2. 多行子查询

多行子查询操作符

操作符	含义	使用
IN	等于任意一个
ANY	配合单行操作符一起使用，和子查询的某一个值比较	where xx < ANY (子查询)
ALL	配合单行操作符一起使用，和子查询的所有值比较	where xx > ALL (子查询)
SOME	和ANY相同，一般使用ANY

空值场景
子查询结果中只要有一列为空，整体结果就为空

6.2 分类角度二：相关子查询、不相关子查询

相关和不相关主要是看子查询的结果是否会受到主查询条件的影响

1. 相关子查询

查询工资大于本部门平均工资的员工信息（子查询中需要查询部门的平均工资，每次查询都要根据员工的部门id重写计算部门平均工资，所以是相关联的）

2. 不相关查询

查询工资大于所有员工平均工资的员工信息（所有员工的平均工资是确定的，不会受到其他因素的影响，和员工是没有关系的，所以是不相关联的）

7. 创建表

1. 根据现有表创建一个新表并复制数据

-- t1_copy 中的字段和 t1 中一致并且会把数据也复制过来
CREATE TABLE t1_copy
AS 
------------------------------------------------------------
-- 被复制的数据受由部分的查询语句决定
SELECT *
FROM t1;

---

2. 根据现有表创建一个新表不复制数据

CREATE TABLE t2_copy
AS 
------------------------------------------------------------
-- 只要保证这一部分的查询语句查询不到任何记录即可
-- 比如表中不存在id=-1,那我们就偏要查id=-1的记录,这样就可以了
SELECT *
FROM t2
WHERE id = -1;
------------------------------------------------------------
-- 更粗暴一点的做法
where 1 = 2

8. 管理表

8.1 增、删、改、重命名表字段

8.2 重命名表

8.3 删除表

MySQL 8.0 新特性：DDL 原子化，支持事务完整性

8.3.1 5.7 版本

-- 前提：t1表存在,t2表不存在
-- 执行结果: 执行报错但是t1表删除成功
DROP TABLE t1,t2;

8.3.2 8.0 版本

-- 前提：t1表存在,t2表不存在
-- 执行结果: 执行报错,t1表仍然存在
DROP TABLE t1,t2;

8.4 清空表

8.4.1 TRUNCATE TABLE

删除表中所有数据
释放表的存储空间
开启事务也不可回滚（DDL 执行完会自动commit）

TRUNCATE TABLE t1;

8.4.2 DELETE FROM

可以添加where条件只删除部分数据
开启事务可以回滚

DELETE FROM t1;

总结
TRUNCATE TABLE 的速度比 DELTE 要快，占用的系统和事务日志资源较少，但在生产环境下不建议使用，因为无法回滚可能会造成事故

9. COMMIT 和 ROLLBACK

9.1 COMMIT 提交

提交数据，一旦提交数据就永久保存在数据库中。不可回滚

9.2 ROLLBACK 回滚

回滚数据，回滚到最近一次 COMMIT 之后的状态

10. 数据类型

10.1 日期时间类型

DATETIME 和 TIMESTAMP 的区别

• DATETIME 存储的范围更大
• DATETIME 占用8字节、TIMESTAMP 占用4字节
• TIMESTAMP 的时间计算是和时区挂钩的
• TIMESTAMP存储的是毫秒数，比较或计算时间时速度更快

开发中使用最多的是 DATETIME

11. 文本类型

CHAR 和 VARCHAR 对比

	长度	空间	效率
CHAR	固定长度	浪费空间(没有达到长度会补空格)	效率高
VARCHAR	可变长度	节省空间	效率低(还要计算可变的位数是多少)

• CHAR 适用于长度很小的数据，比如 1 代表男、0 代表女。长度只可能为 1，这个时候就没必要使用 VARCHAR 了，因为 VARCHAR 还需要一个字节存储可变位数的长度，反而没有实现节省空间的目标。
• CHAR 适用于长度固定的数据，比如电话号码、身份证号码等长度是不会改变的，也就没必要使用 VARCHAR 了。
• CHAR 适用于需要频繁修改的列，因为频繁修改使用 VARCHAR 每次还需要额外的计算可变位数的长度

12. 数据完整性和约束

12.1 唯一性约束 UNIQUE

对于唯一性约束的列，可以插入多个NULL值

12.2 非空约束 NOT NULL

12.3 主键约束 PRIMARY KEY

一般和自增列 (auto_increment) 一起使用

对于自增列插入 0 或 null 都会在当前基础上加 1

特殊场景，插入了 5 条数据(id增长到5)，然后删除了最后两条数据。此时重启数据库服务

5.7 版本中再次插入一条数据，id 将会是 4
8.0 版本中再次插入一条数据，id 将会是 6

12.4 外键约束 FOREIGN KEY

每一个表创建时都可以单独指定存储引擎

使用外键时，主表和从表需要使用一致的存储引擎

注意： 高并发场景禁止使用外键和级联，单机低并发场景可以使用（阿里巴巴开发规范）

12.5 检查约束 CHECK

12.6 默认约束 DEFAULT

13. 视图

13.1 视图的本质

可以看作是提前存储好的一段 select 查询语句（一个虚拟表）

13.2 视图的优点

可以很方便的进行权限控制，比如对一张表中可能有一些字段比较敏感不想让一些人员看到，比如工资。那么可以根据这个表创建一个视图，然后对这些人员开放这个视图的权限。

14. 存储过程

阿里巴巴开发手册中规定禁止使用存储过程

• 存储过程不可跨数据库使用，系统同时对接多个数据库时，需要针对每个数据库单独编写存储过程
• 存储过程调试困难，只要少数DBMS支持debug
• 存储过程的版本控制困难，不像代码可以用git管理