1 数据库基础

1.1 基本概念

2 什么是 MySQL

• MySQL 是基于客户机-服务器的DBMS。

• 客户机-服务器应用分为来那个个不同的部分。
• 服务器部分是负责所有数据访问和处理的一个软件，这个软件运行在成为数据库服务器的计算机上。
• 客户机是与用户打交道的软件。
• 客户机软件通过网络提交该请求给服务器软件。

3 使用 MySQL

4 检索数据

• 检索使用 SELECT 语句

4.1 检索单个列

4.2 检索多列

4.3 检索所有列

4.4 检索不同的行

4.5 限制结果

4.6 使用完全限定的表名

5 排序检索数据

• 关系型数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义

• 排序使用 ORDER BY 子句

5.1 按单列排序

5.2 按多列排序

5.3 指定排序方向

• DESC 降序，ASC 升序（默认，一般没啥用）

• 使用 ORDER BY 和 LIMIT 组合，找出一列中最高或最低的值

6 过滤数据

6.1 使用 WHERE 子句

• 检查单个值

• 不匹配检查

• 范围值检索（BETWEEN A AND B，包括 A 和 B）

• 空值检查

• 注意事项：
在通过过滤选择出不具有特定值的行时，你可能希望返回具有 NULL 值的行。
但是，不行。因为未知具有特殊的含义，数据库不知道他们是否匹配，所以在匹配过滤和不匹配过滤中不返回NULL。
因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有 NULL 的行。

7 数据过滤

7.1 AND 或 OR 操作符连接多个 WHERE 子句

• AND 操作符

• OR 操作符

• 计算次序

7.2 IN 操作符

• IN 一般比 OR 执行更快

7.3 NOT 操作符

• Mysql支持not对in，between，exsits子句取反

8 用通配符进行过滤

8.1 LIKE 操作符

• LIKE 操作符：为在搜索子句中使用通配符，必须使用 LIKE 操作符。

8.1.1 百分号（%）通配符

• 表示任何字符（包括0个字符 ）出现任意次数

• 特殊：注意 % 不能匹配NULL空值！

8.1.2 下划线 （ _ ）通配符

• 匹配一个字符，不能多不能少

• 技巧：
• 把通配符至于搜索模式的开始处，搜索起来是最慢的
• 如果其他操作符能达到相同目的，应该使用其他操作符

9 用正则表达式进行搜索

9.1 基本字符匹配

• 查找产品名中含有'1000'的所有行

• .在正则表达式中，匹配任意 一个 字符

• like 和 正则表达式的区别 ，是否在列值中匹配

• regexp如何匹配整个列，同like效果呢，使用^和$定位符即可

• 正则表达式匹配默认不分大小写，需使用 BINARY 区分大小写

9.2 进行 OR 匹配

• 正则表达式的OR操作符： |

9.3 匹配几个字符之一

• 正则表达式匹配几个字符之一 [ ]

9.4 匹配范围

• 正则表达式匹配范围

9.5 匹配特殊字符

• 正则表达式匹配特殊字符，必须用\\前导，进行转义

• 多数正则使用单反斜杠转义，但mysql使用双反斜杠，mysql自己解释一个，正则表达式库解释一个

9.6 匹配字符类

• 字符类

9.7 匹配多个实例

• 重复元字符

• case

9.8 定位符

• 定位元字符

• case

• 注意事项：
• - ^的双重作用
• 在集合中（用[和]定义），用它来否定该集合
• 用来指串的开始处
• LIKE 匹配整个串，而 REGEXP 匹配字串
• 简单的正则表达式测试

10 创建计算字段

• 存储在表中的数据都不是应用程序所需要的，我们需要直接从数据库检索出转换、计算或格式化过的数据；而不是检索出数据，然后在客户机应用程序或报告程序中重新格式化

• 计算字段是运行时在 SELECT 语句内创建的

10.1 拼接字段（concat()）

• 多数 DBMS 使用 + 或 || 来实现拼接，MySQL 则使用 Concat（）函数来实现。

• 删除数据左侧多余空格 ltrim() 删除数据两侧多余空格 trim() 删除数据右侧多余空格 rtrim()

10.1.1 使用别名

• AS 赋予别名

10.2 执行算数计算

• 简单测试计算

11 利用数据处理函数

• 函数没有 SQL 的可移植性强，为了代码的可移植性，许多程序员不赞成使用特殊实现的功能

• 如果决定使用函数，应该保证做好代码注释

11.1 常用的文本处理函数

• case

11.2 日期和时间处理函数

• 首选的日期格式 yyyy-mm-dd，避免多义性

11.3 数值处理函数

12 汇总数据

12.1 聚集函数

• SQL 聚集函数

12.1.1 AVG（）

12.1.2 COUNT（）

12.1.3 MAX（）& MIN（）

12.1.4 SUM（）

12.2 聚集不同值（DISTINCT）

• 指定 ALL 包含所有值（默认）

• 指定 DISTINCT 只包含不同的值

• distinct 只能作用于count(),不能用于count(*)

• distinct 同max(),min()的结合使用，没有意义

12.3 组合聚集函数

• 4个聚集计算:物品的数目，产品价格的最高、最低以及平均值

13 分组计算（GROUP BY）

13.1 数据分组

• 按vend_id排序并分组数据

• 注意事项：
• GROUP BY 子句指示 MySQL 分组数据，然后对每个组而不是整个结果集进行聚集。
• 除聚集计算子句外，SELECT 语句中的每个列都必须在GROUP BY 子句中给出。
• 如果分组中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL值，它们将分为一组。

13.2 过滤分组

• 区别：
• WHERE 过滤行，HAVING 过滤分组
• WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤

• COUNT(*) >=2（两个以上的订单）的那些分组

• where和having组合使用

13.3 分组和排序

• 检索总计订单价格大于等于50的订单的订单号和总计订单价格

• 按总计订单价格排序输出

13.3 SELECT 子句顺序

14 使用子查询

14.1 利用子查询进行过滤

• 列出订购物品TNT2的所有客户

14.2 作为计算字段使用子查询

• 显示customers 表中每个客户的订单总数

15 联结表

• 联结是一种机制，用来在一条 SELECT 语句中关联表，因此称之为联结

15.1 创建联结

15.1.1 WHERE 子句联结

• 笛卡尔积 / 叉联结
• 由没有联结条件的表关系返回的结果为笛卡尔积
• 检索出的行的数目将是第一个表中的行数乘以第二个表的行数

15.1.2 内部联结（INNER JOIN … ON … ）

• 表间相等测试

15.1.3 联结多个表

• 编号为20005的订单中的物品及对应情况

• 注意事项：
• 不要联结不必要的表，联结的表越多，性能下降越厉害

16 创建高级联结

16.1 使用表别名

• 给表起别名

16.2 使用不同类型的联结

16.2.1 自联结

• 用自联结而不用子查询，通常联结比子查询快得多

• 方法：子查询

• 方法：使用联结

16.2.2 自然联结

• 自然联结使每个列只返回一次

• 方法：通过对表使用通配符*，对所有其他表的列使用明确的子集

16.2.3 外部联结（LEFT | RIGHT INNER JOIN … ON … ）

• 外部联结包含了那些在相关表中没有关联行的行

• 检索所有客户及其订单

• 检索所有客户及其订单,包括那些没有订单的客户

16.3 使用带聚集函数的联结

• 检索所有客户分别对应的订单数，inner join

• 检索所有客户分别对应的订单数，包括没有订单的客户，left outer join

17 组合查询

17.1 创建组合查询（UNION）

17.1.1 使用 UNION

• 价格小于等于5的所有物品的列表，而且包括供应商1001和1002生产的所有物品（不考虑价格）

17.1.2 包含或取消重复的行

• union默认自动去除重复的行

• union all，匹配所有行 ，不取消重复行

• 对union组合结果进行排序

18 全文本搜索

• 条件：并非所有引擎都支持全文本搜索，两个最常使用的引擎为 MyISAM 和 InnoDB，前者支持全文本搜索，而后者不支持

• 解决了通配符和正则表达式的一些匹配限制

18.1 使用全文本搜索

18.1.1 启用全文本支持（FULLTEXT（））

• 先导入所有数据，然后再修改表，定义 FULLTEXT

18.1.2 进行全文本搜索（Match（）& against（））

• 使用完整的 Match（）说明，该值必须与 FULLTEXT（）中定义的相同

• 较高等级的行先返回

• 演示排序如何工作

18.1.3 使用查询扩展

• 查询扩展或进行来两边扫描，发现相关的内容

18.1.4 布尔文本搜索（IN BOOLEAN MODE）

• 即使没有 FULLTEXT 索引也可以使用，但是一种缓慢的操作，其性能将随着数据量的增加而降低

• 排列而不排序，不安等级值降序排序返回的行

• 全文本布尔操作符

• 全文本搜索检索包含词heavy的所有行

• rope* 排除包含rope*（任何以rope开始的词，包括ropes）的行

• 匹配包含词rabbit和bait的行

• 不指定操作符，搜索匹配包含rabbit和bait中的至少一个词的行

• 搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait

• 匹配rabbit和carrot，增加前者的等级，降低后者的等级

• 必须匹配词safe和combination，降低后者的等级

19 插入数据（INSERT）

19.1 插入完整的行（INSERT INTO … VALUES …）

• 插入一个新客户到customers表

• 注意事项：
• 总是使用明确给出列的列表的 INSERT 语句，这样即使表结构发生变化，也能继续发挥作用
• 可使用 INSERT LOW_PRIORITY INTO 降低 INSERT 语句优先级，也适用于 UPDATE 和 DELETE

19.2 插入多个行

• 方法1： 提交多个insert 语句

• 方法2： 只要每条INSERT语句中的列名（和次序）相同，可以如下组合各语句

19.3 插入检索出来的值

• 将custnew中内容插入到customers表中

20 更新和删除数据

20.1 更新数据（UPDATE）

• UPDATE 语句组成部分
• 要更新的表
• 列名和它们的新值
• 确定要更新行的过滤条件

• IGNORE 关键字：即使是发生错误，也继续更新
使用方法：UPDATE IGNORE …

• 更新： 客户10005现在有了电子邮件地址

• 更新： 多个列

20.2 删除数据（DELETE & TRUNCATE TABLE）

• 删除： 更新某个列的值，可设置它为NULL（假如表定义允许NULL值）

• delete 语句： 删除整行而不是某列

• truncate table 语句
• 如果想从表中删除 所有行，不要使用DELETE，可使用TRUNCATE TABLE语句
• TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据

21 创建和操纵表

21.1 创建表（CREATE TABLE）

21.1.1 表创建基础

• 处理现有的表：不能覆盖已有的表，必须先删除该表，再创建相同的表。

• 仅想在表不存在时创建：在表名后加 IF NOT EXISTS

21.1.2 使用 NULL 值

• 每个表列必须指定为 NULL 或者 NOT NULL

• NULL 不是空串，不同于‘’

21.1.3 主键

• 指定主键：PRIMARY KEY（）

21.1.4 自动增量

• 指定自动增量： AUTO_INCRENMENT

• 覆盖 AUTO_INCRENMENT：只要值是唯一的，即可 INSERT

• 确定 AUTO_INCRENMENT 值：SELECT last_insert_id()

21.1.5 指定默认值

• 指定默认值：DEFAULT 默认值

21.1.6 引擎类型

• 创建表时，使用 ENGINE = 引擎类型，指定引擎

• 常见引擎种类：
• InnoDB：是一个可靠的事物处理引擎，它不支持全文本搜索
• MEMORY：在功能等同于 MYISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）
• MyISAM：是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

• 外键不能跨引擎

21.2 更新表 & 定义外键（ALTER TABLE）

• 给vendors表增加一个名为vend_phone的列

• 删除刚刚添加的列

• ALTER TABLE的一种常见用途是定义外键

21.3 删除表（DROP TABLE）

• 删除customers2表（假设它存在）

21.4 重命名表（RENAME TABLE）

• 重命名一个表 (假设存在下述表)

• 对多个表重命名(假设存在下述表)

22 使用视图

22.1 视图

• 视图提供了一种 MySQL 的 SELECT 语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

• 性能问题：视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。

22.2 使用视图

• 创建视图 create view

• 查看创建视图的语句 show create view viewname

• 删除视图 drop view viewname

• 更新视图
• 先 drop 后 create
• 直接用 create or repalce view

22.2.1 利用视图简化复杂的联结

• 创建一个名为productcustomers的视图

• 检索订购了产品TNT2的客户

22.2.2 用视图重新格式化检索出的数据

• 创建所有邮件标签的数据的视图

• 检索出以创建所有邮件标签的数据

22.2.3 用视图过滤不想要的数据

• 定义customeremaillist视图，它过滤没有电子邮件地址的客户

22.2.4 使用视图与计算字段

• 检索某个特定订单中的物品，计算每种物品的总价格的视图

• 创建视图的时候select添加了列名order_num,否则无法按照order_num进行过滤查找

22.2.5 更新视图

• 视图中虽然可以更新数据，但是有很多的限制。

• 一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。

23 使用存储过程

23.1 存储过程

• 存储过程简单来说，就是为以后的使用而保存的一条或多条 MySQL 语句的集合。可将其视为批文件，虽然它们的作用不仅限与批处理。

23.2 为什么使用存储过程

• 原因很多，简单来说就是更加简单、安全、高性能

• MySQL 将编写存储过程的安全和访问与执行存储过程的安全与访问区分开了

23.3 使用存储过程

23.3.1 执行存储过程

• MySQL 称存储过程的执行为调用，因此执行存储过程的语句为 CALL

23.3.2 创建存储过程

• 返回产品平均价格的存储过程（其中 delimiter // 是为了解决mysql 命令行客户机的分隔符解析问题）

• 调用上述存储过程

23.3.3 删除存储过程

• 删除存储过程,请注意:没有使用后面的()，只给出存储过程名

23.3.4 使用参数

• 使用参数 out（从存储过程传出）

• 为调用上述存储过程，必须指定3个变量名（所有 MySQL 变量都必须以 @ 开头）

• 获得3个值

• 使用参数 in 和 out

• 给ordertotal传递两个参数

• 显示此合计

• 得到另一个订单的合计显示

23.3.5 建立智能存储过程

• 获得与以前一样的订单合计，但只针对某些顾客对合计增加营业税

• 调用上述存储过程，不加税

• 调用上述存储过程，加税

23.3.6 检查存储过程

• 显示用来创建一个存储过程的CREATE语句

• 该语句列出所有存储过程

• 过滤模式

24 使用游标

24.1 游标

• 游标是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

• 游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

• MySQL 游标只能用于存储过程（和函数）。

24.2 使用游标

24.2.1 创建游标

• 声明游标：DECLARE 游标名 CURSOR FOR 查询语句;

• 定义名为ordernumbers的游标，检索所有订单

24.2.2 打开和关闭游标

• 打开游标

• 关闭游标

• 隐含关闭：如果你不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭它

24.2.3 使用游标数据

• 检索 当前行 的order_num列，对数据不做实际处理

• 循环检索数据，从第一行到最后一行，对数据不做实际处理

• 循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理

• # 调用存储过程 precessorders() 并输出结果

25 使用触发器

25.1 触发器

• 触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语句）：
• DELETE
• INSERT
• UPDATE

• 只有表才支持触发器，视图不支持（临时表也不支持）

• 每个表最多支持6个触发器（每条 DELETE、INSERT、UPDATE 之前和之后）

25.2 创建触发器

25.3 删除触发器

25.4 使用触发器

25.4.1 INSERT 触发器

• 可以引用一个名为 NEW 的虚拟表，访问被插入的行

25.4.2 DELETE 触发器

• 使用 OLD 保存将要被删除的行到一个存档表中

25.4.3 UPDATE 触发器

26 管理事务处理

26.1 事务处理

• 事物处理：可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。

• 事务：指一组sql语句

• 回退：指撤销指定sql语句的过程

• 提交：指将未存储的sql语句结果写入数据库表

• 保留点：指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）

26.2 控制事务处理

26.2.1 使用 ROLLBACK

• 开始事务及回退

26.2.2 使用 COMMIT

• commit 提交

26.2.3 使用保留点

• savepoint 保留点

26.2.4 更改默认的提交行为

27 全球化和本地化

27.1 字符集和校对顺序

• 字符集：字母和符号的集合

• 编码：某个字符集成员的内部表示

• 校对：规定字符如何比较的指令

27.2 使用字符集和校对顺序

• 查看所支持的字符集完整列表

• 查看所支持校对的完整列表,以及它们适用的字符集

• 确定所用系统的字符集和校对

• 使用带子句的 CREATE TABLE，给表指定字符集和校对

• 除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们

• 校对 collate 在对用 ORDER BY 子句排序时起重要的作用

• 如果要用与创建表时不同的校对顺序排序,可在SELECT语句中说明

28 安全管理

28.1 访问控制

• 访问控制：需要给用户提供他们所需要的访问权，且仅提供他们所需的访问权

• 管理控制访问需要创建和管理用户帐号

28.2 管理用户

• 需要获得所有用户账号列表时

28.2.1 创建用户账号

• 使用 create user

• 重命名一个用户账号

28.2.2 删除用户帐号

28.2.3 设置访问权限

• 查看赋予用户账号的权限

• 允许用户在（crashcourse数据库的所有表）上使用SELECT，只读

• 撤销特定的权限

• 简化多次授权

28.2.4 更改口令

• 原来课本中使用的password()加密函数，在8.0版本中已经移除

• 如果不指定用户名，直接修改当前登录用户的口令

29 数据库维护

29.1 备份数据

• MySQL 数据是基于磁盘的文件，普通的备份系统和例程就能备份，但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总有效

• 备份前需要使用 FLUSH TABLES 刷新未写数据，保证所有数据被写到磁盘

29.2 进行数据库维护

• 分析表键状态是否正确

• 检查表是否存在错误

• #优化表 OPTIMIZE TABLE，消除删除和更新造成的磁盘碎片，从而减少空间的浪费

29.3 诊断启动问题

29.4 查看日志文件

• 错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)

• 二进制日志：-log-bin （记录所有更改数据的语句，还用于复制，恢复数据库用）

• 查询日志：-log （记录建立的客户端连接和执行的语句）

• 慢查询日志: -log-slow-queries （记录所有执行超过long_query_time秒的所有查询）

• 更新日志: -log-update （二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用）

30 改善性能