学习笔记之《MySQL必知必会》

• 唯一标识表中每行的这个列（或这组列）称为主键 注:主键可以为多个字段的组合！！！
• 主键列不允许NULL值 
• 语句以“；”或者“\g”结束
• SELECT DISTINCT vend_id,prod_price，除非指定的两个列都相同，否则所有行都将被检索出来（即只有两个列对应值都一样才视为一样）。如下：

• limit：限制展示出来的记录数。可提供1或两个参数。若只提供一个参数a，表示从原结果集中挑选从第0条（从0开始）开始的a条记录；若提供两个参数b,a，则表示从原结果集中的第b条开始的a条记录。如下：

• 若需要返回的记录数超过了原结果集中记录数，则返回原结果集能返回的最大记录数。如下：

• 另一种limit语法：limit a offset b;表示从第b条（b从0开始）开始选取a条记录。如下：

• 可以用非检索的列对数据排序

• order by可用于按多个列依次排序，每个列用逗号隔开。desc为按降序排，asc为按升序排（默认）
• 在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL的默认行为
• MySQL在执行匹配时默认不区分大小写
• BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。 注:between and 包含端点值
• 判断列中某个元素是否为空使用IS NULL而不是 = NULL
• 未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤（等于、大于等于、小于等于）或不匹配过滤（不等于）时不返回它们 注:若想返回一个不等于条件的结果，且某一行在该字段值为空，那么该行不会被返回。因为为空只是表示该行该字段值还未被设置，并不能保证它一定是满足不匹配过滤条件(不等于)的
• SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符  注:逻辑运算符顺序:非 与 或
• in操作符：

• IN操作符一般比OR操作符清单执行更快，因为IN操作符使用的操作符更少，计算的次序更容易管理
• IN操作符后面的集合可以为SELECT字句，使得能够更动态地建立WHERE子句。 注:SELECT字句返回的其实也就可以看成是个集合
• NOT操作符：否定它之后所跟的任何条件
• EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。EXISTS 指定一个子查询，检测 行 的存在。如下：

在WHERE字句中使用EXISTS字句返回的结果与不使用WHERE字句返回的结果一样，说明了EXISTS字句不返回任何数据

• 使用通配符关键字：LIKE。两个通配符：% 匹配任意多个字符（包括0个）  _：只匹配一个字符
• LIKE '%'也不能匹配值为NULL的行。 注:like "%" 当%匹配0个字符时，即like "" 是一个空串，并不是NULL
• 不要过度使用通配符，把通配符置于搜索模式的开始处，搜索起来是最慢的。
• 正则表达式关键字：REGEXP（Regular Expression）
• LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）； 而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。如下：

• 为搜索两个串之一（或者为这个串，或者为另一个串），使用“ | ”，“ | ”为正则表达式的OR操作符。如下：

• [0-9]：匹配0-9任意一个字符   [^0-9]：匹配除0到9之外任意一个字符 
• 若需要匹配特殊字符，则使用“\\”进行转义（双斜线）
• 字符类：(每个类前后都有“：”)

类	说明
[:alnum:]	匹配任意字母和数字
[:alpha:]	匹配任意字符（同[a-zA-Z]）
[:digit:]	匹配任意数字
[:lower:]	匹配任意小写字母
[:upper:]	匹配任意大写字母
[:xdigit:]	匹配任意十六进制字符[a-fA-F0-9]

• 重复元字符：（都是对元字符的前一个字符进行限定）

元字符	说明
*	0个或多个匹配，等于｛0，｝
+	1个或多个匹配，等于｛1，｝
？	0个或1个匹配，等于｛0，1｝
｛n｝	指定数目的匹配
｛n，｝	不少于n个的匹配
｛n，m｝	匹配数目的范围，m不超过255

•  定位元字符：

元字符	说明
^	文本的开始
$	文本的结尾

• ^的双重用途：在集合中（用"[ ]"定义），用它来否定该集合；否则，用来指串的开始处
• mysql的拼接函数concat：

• 字段（列）别名用 as 关键字赋予
• SELECT Now()利用Now()函数返回当前日期和时间
• 常用的文本处理函数：

函数	说明
left	返回串左边的字符。有两个参数，str以及len
length	返回串的长度
locate	找出一个串在另一个串中的位置。有两个参数，第一个参数为子串，第二个参数为父串。返回的位置从1开始（不是0）
lower	将串转换为小写
ltrim	将串左边多余的空格去掉
right	返回串右边的字符。有两个参数，str以及len，len从最右边往左走
rtrim	将串右边多余的空格去掉
soundex	返回串的soundex值
substring	返回串的子串。有三个参数，ste，start（从1开始）以及len
upper	将串转换为大写

•  soundex是一个将任何文本串转换成描述其语音表示的字母数字模式的算法，soundex考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字符比较
• 常用日期和时间处理函数：

函数	说明
curdate	返回当前日期
curtime	返回当前时间
date	返回一个日期时间的日期部分
time	返回一个日期时间的时间部分
now	返回当前日期和时间
year	返回当前日期的年份（同理，有month、day、hour、minute、second）

• 常用数值处理函数：

函数	说明
abs	返回一个数的绝对值
sqrt	返回一个数的平方根。如果数字为复数，则返回NULL
exp	返回一个数的指数值。即e^x
mod	返回除操作的余数，即mod(a, b) = a % b
pi	返回圆周率
rand	返回一个随机数，位于 [ 0，1）
sin	返回正弦，同理有cos，tan

• 常用聚集函数：

函数	说明
avg	返回某列的平均值
count	返回某列的行数
max	返回某列的最大值
min	返回某列的最小值
sum	返回某列值之和

• 聚集函数中，除了count（*）把NULL当回事，其余函数都不把NULL计算在内（包括count（column_name））
•  在用于文本数据时，如果数据按相应的列排序(字典顺序)，则MAX()返回最后一行
• sum函数也可以用来合计计算值。如下：

• 对于count，distinct只能用于指定列的count，而不能用于count（*）。如下：

• group by字句指示mysql对数据分组，然后对每个组而不是整个结果集进行聚集。所以是先group，再select
• group by分组可以包含任意数目的列，能为数据分组提供更细致的控制。对于任意两个分组，对应的列至少有一个列中元素不同
• 如果在group by字句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列中取回数据）？？？
• group by字句中列出的每个列都必须是检索列（在select中）或有效的表达式，但不能是聚集函数。如果在select中使用表达式，则必须在group by中使用相同的表达式，不能使用别名
• 除聚集计算语句外，SELECT语句中的每个列都必须在group by给出
• 如果分组列中具有NULL值，则NULL 将作为一个分组返回。如果列中有多行NULL值，则它们将被分为一组
• group by字句必须出现在where字句之后，order by字句之前
• 在 group by 后使用 with rollup，可以得到每个分组汇总后的信息。如下：

• WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。
• WHERE过滤行，而HAVING过滤分组
• where与having同存的例子：列出具有2个以上且价格为10以上的产品的供应商。如下：

• group by的输出可能不是分组的顺序
• SELECT字句及其顺序：

字句	说明	是否必须使用
select	要返回的列或表达式	是
from	从其中检索数据的表	否，仅在从表中选择数据时使用
where	行级过滤	否
group by	分组说明	否，仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否
limit	限定返回的行数	否

• 嵌套子查询：列出订购物品TNT2的所有客户姓名和联系名（使用了IN关键字）

• 通常，子查询将返回当个列并且与单个列匹配，但如果需要也可以使用多个列
• 相关子查询：涉及外部的子查询
• 相关子查询例子：检索出customers表中每个客户的订单总数（包括未下过订单的客户，稍后有使用联结解决该题的方法）

• 相关子查询的理解：针对上例，每次从父查询的表customers中取出一条数据，对于这条数据，每次都从orders表中遍历数据，找到orders.cust_id = customers.cust_id的记录并计算count（*）然后返回。
• 外键：外键为某个表的一列，它包含另一个表的主键值
• 关系数据库的三大完整性：

1. 实体完整性。主键非空且唯一
2. 参照完整性。外键为null或者在为其对应表主键的值
3. 用户自定义完整性。具体应用的数据符合用户定义的要求

• 在联结两个表时，实际是将第一个表中的每一行与第二个表中每一行配对
• 笛卡尔积：由没有联结条件的表关系返回的结果。检索出的行的数目将是第一个表的行数目乘以第二个表的行数目
• 等值联结：基于两个表之间的相等测试，也称内部联结。
• 使用内联结（INNER JOIN  + ON）例子：

• 表别名与列别名创建方式一样，使用AS关键字。但表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机
• 使用自联结（对表取别名）：查询生产ID为‘DTNTR’的物品的供应商生产的所有物品

• 自然联结：排除多次出现的列，只选择那些唯一的列。一般通过对一个表使用通配符（select *），对另一个表使用明确的子集完成
• 外部联结（OUTER JOIN）：包含了那些在相关表中没有关联行的行。例子：检索所有客户，包括没有订单的客户

• 在使用OUTER JOIN时，必须指定关键字LEFT(或者RIGHT)指定包括其所有行的表
• 使用带聚集函数的联结：检索出customers表中每个客户的订单总数（只包含下过订单的客户）：

• 使用带聚集函数的联结：检索出customers表中每个客户的订单总数（包含所有用户，只需要使用外联结就行）：

• MySQL允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询
• 以下两种情况，可以使用组合查询：

1. 在单个查询中从不同的表返回类似结构的数据
2. 对单个表执行多个查询，按单个查询返回数据

• 使用UNION：检索出所有价格小于等于5或者供应商为‘1001’或者供应商为‘1002’的所有商品信息（将where条件拆分）

• 在使用UNION时，重复的行会被取消。如果想返回所有匹配行，可以使用UNION ALL
• 对组合结果查询排序：在用UNION组合查询时，只能使用一条ORDER BY 字句，它必须出现在最后一条SELECT语句之后

• 在对列进行了FULLTEXT索引之后，使用两个函数match（）和against（）执行全文本搜索。其中，match（）指定被搜索的列，against指定要使用的搜索表达式

• 使用LIKE和REGEXP通常是要求MySQL匹配表中所有行，然后在做投影操作。而全文本搜索是只对对应列搜索。另外，全文本搜索还可以对结果排序，按关键字在该行出现的位置来排序
• 使用查询扩展（使用语句 WITH QUERY EXPANSION），不仅能找出完全匹配的结果，而且还能找出与你的搜索有关的所有其他行

• 布尔文本搜索（IN BOOLEAN MODE）。布尔文本搜索可以提供一下一些功能：

1. 要匹配的词
2. 要排斥的词（如果某行包含这个词，则不包含该文本，即使它包含指定的匹配的词也是如此）
3. 排列提示（指定某些词更重要，更重要的词等级更高）
4. 表达式分组

• 即使没有定义FULLTEXT索引，也可以使用布尔文本搜索。但这样速度很慢
• 布尔搜索排除单词举例：首先是补排斥的情况返回，然后是排除单词后的返回结果（排除含“rope”的文本，使用“-rope*”）

•  全文本布尔操作符：

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
>	包含，而且增加等级值
<	包含，且减少等级值
( )	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
'" "'	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语） 不是三个单引号，最外面一个单引号，里面一个双引号

• 匹配同时包含词‘rabbit’和词‘bait’的文本

• 匹配包含rabbit和bait中的至少一个词

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

• 可针对每个表或每个用户，利用MySQL的安全机制限制使用INSERT、UPDATE和DELETE语句
• 简单的插入语句：

• 针对上例，需要注意：

1. 插入语句关键字有values而不是value，意味着你可以插入多条，用逗号分隔插入的数据即可（插入的数据都在‘（）’内）
2. 尽管主键为自增类型，但是也得插入值。它实际的值是由MySQL指定下一个可用的cust_id
3. 按序插入

• 以上方法太过于依赖表中定义列的顺序，可以使用以下指定插入到某些列的方法（不需要提供自增量的值，且可以以不同于列定义的顺序插入）：

• 如果表的定义允许，可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件：

1. 该列定义为允许NULL值
2. 在表定义中给出默认值。这表示如果不给出值，则使用默认值

• 可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级

• 插入检索出的数据：

• 针对上例，需注意：

1.  插入检索出的数据时，没有关键字values了
2. 不要插入与原表中主键值重复的数据。上例未插入cust_id，因为cust_id值为自增
3. 基本语法为 INSERT INTO table_name(COLUMNS...) SELECT COLUMNS...
4. INSERT和SELECT中的列名不一定要一致，只需要对应的数据可以转换就行

• 更新数据（UPDATE + SET + WHERE，若没WHERE，则更新所有的行）：

• 更新多个列，只需使用一个SET命令，每个“列-值”对用逗号隔开
• IGNORE关键字：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作将被取消（错误发生前更新的所有行恢复其原来的值）。使用IGNORE关键字，可以使即使发生错误，也继续更新。语法为：UPDATE IGNORE customers....
• 删除数据：DELETE

• DELETE不需要列名或通配符，DELETE删除整行而不是删除列。为了删除指定的列，可以使用UPDATE将该列值置空
• DELETE语句删除的是表的行的内容，并不删除表本身。DROP table_name可删除表本身
• 如果想从表中删除所有行，不要使用DELETE，可以使用TRUNCATE，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除数据）
• 使用强制实施引用完整性的数据库时，MySQL将不允许删除具有与其他表相关联的数据的行。（可以先删除以其外键为主键的表，在删除该表）
• 创建表：

• 针对上表的创建，细节解释：

1. 第一行table后指定表名
2. 2~10行指定列名，以及对本列所能取值的一些限制。int表示只接受整数值，not null表示不能为空，auto_increment表示为自增型，char(50)表示长度为50的字符串（定长，空间已分配），null表示可以为null
3. 第11行指定主键为cust_id。若主键为多个列的组合时，每个列以逗号分隔
4. 第12行指定数据库引擎

• 如果想在一个表不存在时创建它，应该再表名后给出 IF NOT EXISTS
• 如果主键使用单个列，那么它的值必须唯一。如果主键使用多个列，那么这些列的组合必须唯一。主键不能为NULL
• 每个表只运行一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）
• 使用last_insert_id（）可以得到上一个插入的数据的id值。
• 默认值用CREABLE TABLE语句的列定义中的DEFAULT关键字指定

• MySQL不允许使用函数作为默认值，只允许常量
• InnoDB：是一个可靠的事务处理引擎，它不支持全文本搜索
• MEMORY：在功能等同于MyISAM，但由于数据存储在内存（不是磁盘中），速度很快
• MyISAM：是一个性能极高的搜索引擎，它支持全文本搜索，但不支持事务处理
• 外键不能跨引擎，即使用一个引擎的表不能引用使用不同引擎的表的外键
• 更新表（ALTER TABLE table_name + ADD），添加一列

• 删除表的某一列（ALTER + TABLE + table_name + DROP COLUMN column_name）

• 用alter table 定义外键

alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders(order_num);

• 针对上面的定义外键代码解释：

1. 第一行表明修改的表为orderitems
2. 第二行表明增加一个限制，限制名（自定义）为fk_orderitems_orders
3. 第三行表明该限制为一个外键，其中本表的order_num字段为外键，其引用自orders表中的order_num字段

• 删除表：DROP TABLE table_name
• 重命名表：使用 RENAME 关键字（rename table old_name to new_name）

• 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
• 理解视图：

select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
and prod_id = 'TNT2';

• 接上一条。此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的where字句（prod_id = 'TNT2'）。现在，假如可以把整个查询（不包括最后的prod_id = 'TNT2'这个条件）包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据：

select cust_name,cust_contact
from productcustomers
where prod_id = 'TNT2';

• 接上一条。productcustomers是一个视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询
• 视图的常见应用：

1. 重用SQL语句
2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节
3. 使用表的组成部分而不是整个表
4. 保护数据。可以给用户授权表的特定部分的访问权限而不是整个表的访问权限
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

• 视图的一些常见规则和限制：

1. 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
2. 对于可以创建的视图数目没有限制
3. 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予
4. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
5. order by 可以用在视图中，但如果从该视图检索数据select也含有order by，那么该视图中的order by 将被覆盖
6. 视图不能索引，也不能有关联的触发器或默认值
7. 视图可以和表一起使用。例如，编写一条联结表和视图的select语句

• 创建视图：CREATE VIEW viewname AS
• 查看视图：SHOW CREATE VIEW viewname
• 删除视图：DROP VIEW viewname
• 更新视图：CREATE OR REPLACE VIEW viewname AS 
• 视图最常见的应用是隐藏复杂的SQL。创建视图如下：

• 在上例中，为检索订购了‘TNT2’的客户，可如下进行：

• 通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表（因为视图没有数据）。如果对视图增加或删除行，实际是对其基表增加或删除行
• 如果视图中有以下操作，则不能进行视图的更新：

1. 分组（使用 group by 和 having）
2. 联结
3. 子查询
4. 并
5. 聚集函数（min（）、count（）、sum（）等）
6. 含distinct
7. 导出（计算）列

• 一般来说，应该将视图用于检索（select字句）而不用于更新（insert、update和delete）
• 更新视图   》》》在基表中的视图数据将会更新；更新基表 》》》 若基表更新的行在视图中存在，则视图会被更新
• 存储过程：为以后的使用而保存的一条或多条MySQL语句的集合。存储过程实际上是一种函数
• 创建存储过程：

• 针对上例创建存储过程。此存储过程名为productpricing，用create procedure productpricing( )语句定义。如果存储过程接受参数，它们将在（）列举出来。此存储过程没有参数，但后面的（）仍然需要。begin和end语句用来限定存储过程体，过程体本身仅是一个简单的select语句
• 由于是在命令行输入的命令，因此需要临时更改命令行程序的语句分隔符。如 delimiter //  指示程序以“//”结尾
• 除“/”外，任何字符都能用作语句分隔符
• 调用存储过程：（使用 call + 存储过程名 + （））

• 删除存储过程：（drop + procedure + 存储过程名，注意没有“（）”）

• 如果指定的过程不存在，那么 drop procedure 将产生一个错误。如果想删除某过程，而它不存在也不产生错误，可以使用 drop procedure if exists
• 创建带参数的存储过程：

• 针对上例。此存储过程接受3个参数，每个参数必须具有指定的类型，这里使用十进制值。关键字out指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持 IN（传递给存储过程）、OUT（从存储过程传出）和 INOUT（对存储过程传入和传出）类型的参数。使用关键字 INTO 把 SELECT 的结果存储到相应的变量
• 不能通过一个参数返回多个行和列
• 调用带参数的存储过程：

• 所有MySQL变量都必须以@开头
• 为了获得上面得到的三个变量的值，可使用以下语句：

• 使用 IN 和 OUT 参数的存储过程：（输入一个订单号，输出该订单的总价）

• 计算某个订单的总价（可能需要再加上营业税）：

• 针对上例。注意：

1. “-- ”（两横线后面还有个空格） 为注释标记，
2. declare 为声明变量的关键字，变量可以有默认值，使用 default 关键字即可
3. if 常和 then一起搭配，以 end if 结束if语句
4. comment值不是必须的，但如果给出，将在 show procedure  status 的结果中显示
5.  boolean值指定所有非0的值都为真，只有0为假
6. if 语句还支持 elseif 和 else字句，前者还使用 then 字句，后者不使用
7. 倒数第五行，变量 texable 应为 taxable，写错了

• 游标：是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT 语句， 而是被该语句检索出来的结果集。 使用游标可以在检索出来的行中前进或后退一行或多行
• MySQL游标只能用于存储过程和函数
•  使用游标的步骤：

1. 在能过使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句
2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来
3. 对于填有数据的游标，根据需要取出（检索）各行
4. 在结束游标使用时，必须关闭游标

• 创建游标：

• 针对上例。游标只能在存储过程内创建，使用 declare 游标名 cursor for select语句 创建。
• 定义了游标之后，使用 open 游标名； 打开游标（也是在存储过程内使用）
• 游标使用完后，使用 close 游标名； 关闭游标（也是在存储过程内使用）
• 如果不明确关闭游标，那么MySQL将会在到达 end 语句时自动关闭它
• 从游标中检索单个行，使用 fetch  游标名 

• 循环检索数据，从第一行到最后一行：

• 针对上例，解释如下：

1. 第三行定义了个布尔变量 done 默认为0，用于后面判断是否结束循环
2. 第八行定义了个句柄变量。for sqlstate '02000' set done = 1 意为当检索到 ‘02000’ 时将布尔变量 done 置为1，并且该语句还有另外一个功能：当 repeat 由于没有更多的行供循环时自动出现此条件
3. 第十到十二行为SQL中的循环语句，以关键字 repeat 开启循环，until 布尔变量 end repeat； 结束循环

• 用 declare 定义的局部变量必须在定义任何游标或句柄之前定义，而句柄必须在游标之后定义
• 对取出的数据进行某种实际的处理，比如说存储到一个表中

• 针对上例，倒数第六行的存储过程 ordertotal 在之前的实验已经创建。第十行创建表的语句为 create table if not exists ordertotals  而不是 create table  ordertotals if not exists
• 使用 call processorders(). 即可调用刚才创建的存储过程，并且发现数据库已经新增了一个表为 ordertotals

• 吐槽：MySQL竟然没有直接修改存储过程源码的功能！！！！要想改一个存储过程的功能，必须先drop，在create。或者直接 create 另外一个不同名的存储过程
• 触发器：是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于 begin 和 end 语句之间的一组语句）：

1. DELETE
2. INSERT
3. UPDATE

• 在创建触发器时，需要给出4条信息：

1. 唯一的触发器名
2. 触发器关联的表
3. 触发器应该响应的活动（DELETE、INSERT或UPDATE）
4. 触发器何时执行（处理之前或之后）

• 触发器使用 create trigger 语句创建
• 针对 P182 的创建触发器的代码：

create trigger newproduct after insert on products
for each row select 'product added';

• 以上代码在MySQL5的早期版本可行，但是在最新的版本是会报错的。提示不能从 trigger 中返回结果集

• 只有表才支持触发器， 视图或者临时表都不支持
• 触发器按每个表每个事件（BEFORE或者AFTER）每次地定义，每个表每个事件每次只允许一个触发器。因此，一个表最多只支持6个触发器（每条 INSERT、UPDATE、DELETE的之前或之后）
• 单一触发器不能与多个事件或多个表关联，如果需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器
• 如果 BEFORE 触发器失败，则 MySQL将不执行请求的操作（INSERT、UPDATE或者DELETE）。如果 BEFORE触发器或语句本身（INSERT、UPDATE或者DELETE 语句）失败，MySQL将不执行 AFTER 触发器
• 删除触发器：DROP TRIGGER 触发器名
• INSERT 触发器：

1. 在 INSERT 触发器代码内，可引用一个名为 new 的虚拟表，访问被插入的行
2. 在 BEFORE INSERT 触发器中，new 中的值也可以被更新（运行更改被插入的值）
3. 对于 AUTO_INCREMENT 列，new 在执行之前包含0，在 INSERT 执行之后包含新的自动生成值

• INSERT 触发器实例：

• 针对上例，定义的是 AFTER 触发器，功能是在每次插入数据后，将插入数据行的 order_num 字段的值赋给变量 res
• 通常，将 BEFORE 用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）
• 复制表：（仅复制表结构）。使用语句 create table 新表名 like 旧表名

• DELETE 触发器：

1. 在 DELETE 触发器代码内，可以引用一个名为 OLD的虚拟表，访问被删除的行
2. OLD 中的值全部都是只读的，不能更新

• 创建 DELETE 触发器实例：

• 使用 BEFORE DELETE 相对于 AFTER DELETE 来说的优点：如果由于某种原因，被删除的订单不能存档，则 DELETE 本身将被放弃
• 使用 BEGIN END 块可以使触发器能容纳多条SQL语句
• UPDATE 触发器：

1. 在 UPDATE 触发器代码中，可以引用一个名为 OLD 的虚拟表访问以前的（UPDATE 语句之前）的值，引用一个名为 NEW 的虚拟表访问新更新的值
2. 在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值，改的是用于UPDATE 的值，而不是原表的值）
3. OLD 的值全都是只读的，不能更新

• UPDATE 触发器实例：

• 针对上例，修改的是用于 UPDATE 的vend_city 值，将它们全转换成大写模式。注意： 创建触发器最后是 new.vend_city = upper(new.vend_city)
• 事务（transaction）处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行
• 了解 start transaction 与 rollback

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

• rollback 只能在一个事务处理内使用（在执行一条 start transaction 命令之后）。
• 事务处理用来管理 INSERT、UPDATE 和 DELETE语句。不能回退 SELECT语句（这样做也没意义），无法回退 CREATE 和 DROP 操作
• 在事务处理块中，提交不会隐含地进行。为进行明确的提交，需使用 commit 语句。如以下代码：

start transaction;
delete from orderitems where order_num = 20009;
delete from orders where order_num = 20009;
commit;

• 针对上例。最后若没有 commit ，则这个事务并不算完成。虽然此时订单号为 20009 的相关信息已经从 orderitems 表和 orders 表中删除，但最新的信息并未写入内存，因此若将 commit 改为 rollback 则可以使数据库状态回到事务开始之前
• 即一个事务总是以 rollback 或者 commit 结束
• 使用保留点（savepoint，通过 savepoint 保留点名 创建保留点），可使用 rollback to 保留点名 使得数据库操作回滚到固定的保留点所在的地方
• 保留点在事务处理完成（执行一条 rollback 或者 commit ）后自动释放，也可以用 release savepoint 保留点名 明确地释放保留点
• 关于字符集和校对顺序的一些代码例子：

-- 显示所有可用的字符集以及每个字符集的描述和校对
show character set;
-- 显示所有可用的校对，以及它们适用的字符集
show collation;
-- 确定数据库系统使用的默认字符集
show variables like 'character%';
-- 确定数据库系统使用的默认校对
show variables like 'collation%';

• 不同的表甚至是不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定
• 给表指定字符集和校对：

 

• 给表的特定列设置不同的字符集和校对：

• MySQL用户账户和信息存储在名为 mysql 的数据库中
• 创建一个新用户帐号：（此时用户无任何权限）

• 重命名用户帐号：

• 删除一个用户账户及相关的权限：

• 查询某用户当前拥有的权限：（USAGE 表示根本没有权限）

• 为用户赋予权限（此时用户 ccc 具有对数据库 test 任意表执行查询的权限 ）

• 撤销用户权限：（使用关键字 revoke）

• 可以授予或撤销的每个权限：

权限	说明
ALL	除 GRANT OPTION 外的所有权限
ALTER	使用 ALTER TABLE
ALTER ROUTINE	使用 ALTER PROCEDIRE 或 DROP PROCEDURE
CREATE	使用 CREATE TABLE
CREATE ROUTINE	使用 CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用 CREATE TEMPORARY TABLES
CREATE USER	使用 CREATE USER、DROP USER、RENAME USER 和 REVOKE ALL PRIVILEGES
CREATE VIEW	使用 CREATE VIEW
DELETE	使用 DELETE
DROP	使用 DROP TABLE
EXECUTE	使用 CALL 或 存储过程
FILE	使用 SELECT INTO OUTFILE 和 LOAD DATA INFILE
GRANT OPTION	使用 GRANT 和 REVOKE
INDEX	使用 CREATE INDEX 和 DROP INDEX
INSERT	使用 INSERT
LOCK TABLES	使用 LOCK TABLES
PROCESS	使用 SHOW FULL PROCESSLIST
RELOAD	使用 FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用 SELECT
SHOW DATABASES	使用 SHOW DATABASES
SHOW VIEW	使用 SHOW CREATE VIEW
SHUTDOWN	使用 mysqladmin shutdown（用来关闭mysql）
SUPER	使用 CHANGE、MASTER、KILL、LOGS、PURGE、MASTER 和 SET GLOBAL. 还允许mysqladmin调试登录
UPDATE	使用 UPDATE
USAGE	无访问权限

• 在使用 GRANT 和 REVOKE 时，用户账户必须存在，但对所涉及的对象（表或数据库）没有这个要求。
• 多个授权合并：

grant select，insert on test.* to ccc;

•  更改用户登录口令：

set password for ccc = password('sc');

•  设置当前用户自己的口令：

set password = password('rrr');

• 备份数据库：（以下代码不是在mysql命令行窗口执行，而是在服务器的命令行执行）

mysqldump -u root -p test <具体表名> > test.sql

• 针对上例。 具体表名如果没有指明，那么就是备份整个数据库
• 利用上例创建的数据库的备份文件再创建一个一样的数据库（数据库名不一样）。以下代码在mysql命令行中运行：

create database test1;
use test1;
source ~/test.sql;

• 针对上例。首先创建一个数据库，名为 test1 ，再用 use 命令使用 test1 数据库。然后使用 source 命令，将刚才的 test.sql 文件数据填充到 test1
• 总结：《MySQL必知必会》使用的 MySQL版本并非最新的，书上有些功能在新版本中已经不支持。另外，该书讲的都是一些关于MySQL的基本操作，用来初步学习MySQL基本操作或者是复习以前学过的MySQL操作相关的知识都是很有帮助的。但是，该书对于数据库相关的理论知识并未涉及，比如 E-R图、范数、锁机制等等。