Java后端笔记 MySQL：学习笔记

总结

• 在Ubuntu系统中启动mysql服务：sudo /etc/init.d/mysql start

基础

• MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。换句话说，DESCRIBE customers;是SHOW COLUMNS FROM customers;的一种快捷方式。
• SHOW STATUS，用于显示广泛的服务器状态信息；SHOW CREATE DATA BASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。
• 一般，除非你确实需要表中的每个列，否则最好别使用*通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。
• DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id,prod_price， 除非指定的两个列都不同，否则所有行都将被检索出来。
• LIMIT 5,5指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。
  检索出来的第一行为行0而不是行1。因此，LIMIT 1,1将检索出第二行而不是第一行。
  MySQL 5支持LIMIT的另一种替代语法。LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3,4一样。
• 其实，检索出的数据并不是以纯粹的随机顺序显示的。如果不排序，数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。因此，如果不明确控制的话，不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义。
• 重要的是理解在按多个列排序时，排序完全按所规定的顺序进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。
• DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。
• 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。
• 与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。
• 在字典（dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。这里，关键的问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句做不到。你必须请求数据库管理员的帮助。
• 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。
• MySQL的支持引擎以及默认引擎
• UNIQUE 唯一索引，PRIMARY KEY主键索引，AUTO_IMCREMENT自增索引
• 创建索引的语法如下：

CREATE [UNION|FULLTEXT|SPATIAL

组合连词（WHERE，IN，NOT…）

• 在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。
• BETWEEN在指定的两个值之间，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。
• SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。
  
• SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。
• 在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。
• 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。
• 为什么要使用IN操作符？其优点具体如下： 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。IN操作符一般比OR操作符清单执行更快。IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
• MySQL支持使用NOT对IN、BETWEEN和EXISTS 子句取反，这与多数其他 DBMS允许使用NOT对各种条件取反有很大的差别。

通配符 & 正则表达式

• 根据MySQL的配置方式，搜索可以是区分大小写的。如果区分大小写，'jet%'与JetPack 1000将不匹配。
• 搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。
  %代表搜索模式中给定位置的0个、1个或多个字符。
  尾空格可能会干扰通配符匹配。例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil’将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数RTrim()去掉首尾空格。
• 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。
• _总是匹配一个字符，不能多也不能少。
• ==不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。==在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
• REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可。
• MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’。
• 字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^{即可。因此，尽管[123]匹配字符1、2或3，但[}123]却匹配除这些字符外的任何东西。
• \\也用来引用元字符:

表-空白元字符

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

• 为了匹配特殊字符，必须用\为前导。\-表示查找-，\.表示查找.。
• \ 为了匹配反斜杠（\）字符本身，需要使用\\。
  ^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。
• MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。

函数

• IF(expr1, expr2, expr3)：若expr1为TRUE，则返回expr2，否则返回expr3
• IFNULL(expr1,expr2)：若expr1为NULL，则返回expr2，否则返回expr1
  GROUP_CONCAT()：分组拼接函数

SELECT id,GROUP_CONCAT(name, SPEPARATOR ';') FROM aa GROUP BY id;

• FIND_IN_SET(str, strlist)
  
• COALESCE(value,…)：返回列表中第一个非NULL的值。
• GREATEST(value,…)：返回列表中最大的参数。

表-常用的文本处理函数

函 数	说明
Left(str, length)	返回串左边的字符
Length(str)	返回串的长度
Locate(delim, str)	找出串的一个子串的位置
Lower(str)	将串转换为小写
LTrim(str)	去掉串左边的空格
Right(str, length)	返回串右边的字符
RTrim(str)	去掉串右边的空格
Soundex(str)	返回串的SOUNDEX值
SubString(str, pos), substring(str, pos, length)	返回子串的字符
substring_index(str, delim, count)	按关键字进行读取，substring_index(被截取字符串，关键字，关键字出现的次数)
Upper(str)	将串转换为大写

• SOUNDEX()是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。

• MySQL使用内置函数来进行模糊搜索一共有4个方法：

1. 使用locate()方法
   1.1.普通用法： SELECT column from table where locate(‘keyword’, condition)>0
   类似于 java 的 indexOf();不过 locate() 只要找到返回的结果都大于0(即使是查询的内容就是最开始部分)，没有查找到才返回0；
   1.2. 指定其实位置：_SELECT LOCATE(‘bar’, ‘foobarbar’,5); _
   –> 7 (从foobarbar的第五个位置开始查找)

2. 使用instr()函数 (据说是locate()的别名函数)
   SELECT column from table where instr(condition, ‘keyword’ )>0
   唯一不同的是 查询内容的位置不同，见SQL语句中过的keyword

3. 使用position()方法，(据说也是locate()方法的别名函数，功能一样)
   SELECT column from table where position(‘keyword’ IN condition)
   不过它不再是通过返回值来判断，而是使用关键字 in

4. 使用find_in_set()函数
   如： find_in_set(str,strlist)，strlist必须要是以逗号分隔的字符串
   如果字符串str是在的strlist组成的N子串的字符串列表，返回值的范围为1到N
   SELECT FIND_IN_SET(‘b’,‘a,b,c,d’);

总结：
locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个起始位置的参数外，两者是一样的。
find_in_set()是个比较特殊的存在，但它们都是返回要查找的子字符串 在 指定字符串中的位置。
速度上前3个比用 like 稍快了一点。(不过这四个函数都不能使用索引，这是个遗憾)

表-常用日期和时间处理函数

函数	说 明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

mysql> SELECT curdate(),curTime();
+------------+-----------+
| curdate()  | curTime() |
+------------+-----------+
| 2019-10-02 | 19:42:28  |
+------------+-----------+

mysql> SELECT cust_id FROM orders WHERE Data(order_date) = '2019-10-01';

• DATEDIFF(date1， date2)：日期相减函数

• TIMEDIFF(time1，time2）：时间相减函数

• 首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。

表-常用数值处理函数

函数	说 明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

---

• 如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星号（*），则不忽略。

• SUM()函数忽略列值为NULL的行。

• 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

• 在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的SQL更易于理解和使用（以及将来容易排除故障）。

• HAVING和WHERE的差别 这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

• 在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。

• ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

• 正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

• 表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

连接

自联结
就是一个表自己和自己联结，一般用来替代子查询

比如班上有1个学生数学考了100分，你不知道他是谁，你想知道他的其他学科的成绩

新手的写法：

select student_id from score where type='mathematics' and score=100(假设结果为27)
select * from score where student_id=27

或者

select * from score where student_id=(
    select student_id from score where type='mathematics' and score=100)

自联结的写法

select t1.* from score as t1,score as t2 where t1.student_id=t2.student_id and t2.type='mathematics' and t2.score=100
select t1.* from score as t1 inner join score as t2 on t1.student_id=t2.student_id where t2.type='mathematics' and t2.score=100

自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)

标准的联结返回所有数据，甚至相同的列多次出现

自然联结排除多次出现，使每个列只返回一次

数据库通过自己的判断并使用表内所有相同的字段作为联结条件完成联结过程，不需要指定联结条件

一般的写法是第1个表用*指定字段，其他的表用明确的表字段指定

最好不要让数据库自动完成联结，不推荐使用

select * from t1 natural join t2(效果有点类似inner join)
select * from t1 natural left join t2(效果有点类似left join)
select * from t1 natural right join t2(效果有点类似right join)

内部联结(又叫等值联结)
联结的2个表必须联结条件匹配才会得到数据

内部联结一般都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结

select a.f1,b.f2 from a,b where a.f3=b.f4(不推荐这样的写法)
select a.f1,b.f2 from a inner join b on a.f3=b.f4(inner关键字可以省略)

如果两个表是根据字段名一样的字段联结的，可以这样写

select t1.id,t2.name from t1 inner join t2 using(f)

外部联结
外部联结根据情况来确定是否包含那些在相关表中没有匹配的行

1. 左外部联结(又叫左联结)
   左表的行一定会列出，右表如果没有匹配的行，那么列值就为null
   特别需要注意的是如果右表有多行和左表匹配，那么左表相同的行会出现多次

select a.f1,b.f2 from a left outer join b on a.f3=b.f4(outer关键字可以省略)

2. 右外部联结(又叫右联结)
   和左联结类似，只不过以右表为主表而已，左联结和右联结可以相互转化

select a.f1,b.f2 from a right outer join b on a.f3=b.f4(outer关键字可以省略)

3. 全外部联结
   返回左表和右表的所有行，不管有没有匹配，同时具有左联结和右联结的特性

select a.f1,b.f2 from a full outer join b on a.f3=b.f4(outer关键字可以省略)

交叉联结
生成笛卡尔积，它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

select * from a cross join b
UNION和UNION ALL
UNION不允许同一行(每个字段都一样)重复出现，而UNION ALL则没有这个限制

select A,B from u1
union all
select A,B from u2

关于联结的示意图

• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
• UNION从查询结果集中自动去除了重复的行，使用UNION ALL，MySQL不取消重复的行。
• 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

搜索（待…）

• 为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
• 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。
• 在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
• 除非使用BINARY方式，否则全文本搜索不区分大小写。
• 两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。这很重要。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回。
• 如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。
• 下面是相同的搜索，这次使用查询扩展：输入SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘anvils’ WITH QUERY EXPANSION);
• 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
• SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit bait’ IN BOOLEAN MODE); 分析没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。
• 输入SELECT note_text FROM productnotesWHERE Match(note_text) Against(’“rabbit bait”’ IN BOOLEAN MODE); 分析这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。
• 在布尔方式中，不按等级值降序排序返回的行。
• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
• 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
• 忽略词中的单引号。例如，don’t索引为dont。
• 如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级。其中单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。
  • 适用于Update和Delete。

增删改查

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'),
('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

使用多条插入语句会降低性能，可以用一条insert语句插入多条数据。

INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country 
FROM custnew;

这个例子把一个名为custnew的表中的数据导入customers表中。为了试验这个例子，应该首先创建和填充custnew表。custnew表的结构与附录B中描述的customers表的相同。在填充custnew时，不应该使用已经在customers中使用过的cust_id值（如果主键值重复，后续的INSERT操作将会失败）或仅省略这列值让MySQL在导入数据的过程中产生新值。

• INSERT SELECT中的列名：在组合使用INSERT和SELECT时不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的。
• INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。
• 在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行（如果没有WHERE限定条件的话）。
• 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字：
  
• 为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。
• 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
• 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
• 不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。如果指定’’（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。
• 每个表只允许一个AUTO_INCREMENT列，只有整型类型才能设置此属性，而且它必须被索引。
• 如果一个列被指定为AUTO_INCRE-MENT，则它需要使用特殊的值吗？你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。
• 如何在使用AUTO_INCREMENT列时获得这个值呢？可使用last_insert_id()函数获得这个值。

SELECT last_insert_id();

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

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);

• ALTER TABLE 使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

视图

创建：
CREATE VIEW productcustomers_view AS SELECT ...;
使用：
SELECT cust_id,cust_contact FROM productcustomers_view WHERE prod_id = 'TNT2';

• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
• ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
• 视图不能索引，也不能有关联的触发器或默认值。
• 一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。

游标（待…）

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

• 使用游标涉及几个明确的步骤。

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

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

• 用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。

触发器（待…）

• 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
• 比如：下面是一个当插入新用户时，在用户创建时间表中插入一条新数据，是当前时间。

create trigger trigger_addUserTime 
before
 insert 
on user_info 
for each row 
insert into usercreatetime(create_time) values(now());

• 如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。
• BEFORE或AFTER？ 通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器。

事务处理

BEGIN/START TRANSACTION;
...
COMMIT/ROLLBACK;

• 下面是关于事务处理需要知道的几个术语：事务（transaction）指一组SQL语句；回退（rollback）指撤销指定SQL语句的过程，即恢复到开启事务管理之前；提交（commit）指将未存储的SQL语句结果写入数据库表；保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。
• 事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。
  
• 可以在MySQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能按自己的意愿灵活地进行回退。
• 保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。
• autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。
• autocommit标志是针对每个连接而不是服务器的。

set autocommit = off;   # 关闭自动提交事务
set autocommit = on;   # 开启自动提交事务

事务四个隔离级别

四个事务的示例

1. read uncommitted 读未提交
2. read committed 读以提交
3. repeatable read 可重复读
4. serializable 串行化
   这四个隔离级别逐渐增高。

① read uncommitted：事务A未提交的数据，事务B也可以读取到，这种隔离级别最低。这种事务会导致"dirty read(脏读)"。因为事务A的数据还没有提交，事务B就可以读取到，那如果事务A在事务B读取后回滚了呢，就导致了事务B读取到的数据是"脏数据"。

② read committed：事务A未提交的数据，事务B读取不到，事务A提交后的数据事务B才能读取到。这个事务级别不会导致"dirty read"，但会导致"不可重复读"。假设事务A需要半天，在这期间有很多的其它事务都在修改数据，那么就导致了一个问题，事务A在开启时读到的数据与半天后读到的数据差别很大，那么事务A需要在这半天内读到的数据都是一样的该怎么办，比如每个月底网络运营商系统出账的时候，那肯定得在出账期间读到的数据都必须一样才行。

③ repeatable read：事务A提交后的数据，事务B读取不到，事务B读取的数据依旧是事务B刚开始时的数据。MySQL的事务默认是这个级别。

④ serializable：事务A在执行的时候，事务B只能等待，就是说当多个事务需要执行时，只能排队一个个的来，就是串行化的字面意思了。这种隔离级别最高，但会导致数据库的吞吐量很低一般不用。

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

设置事务隔离级别：
SET SESSION TRANSACTION ISOLATION LEVEL 事务级别名称;

• DECLAARE： 定义的类型是局部变量，定义的变量会初始化为NULL，只会在BEGIN和END之间生效。

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

• SET：定义的变量类似全局变量，从一个session或链接开始到结束

SET @p = 'b1';

字符集

• MySQL提供了不同级别的字符集设置，包括server级、database级、table级、column级，可以提供非常精准的设置。
• 字符集（character set）：定义了字符以及字符的编码。
• 字符序（collation）：定义了字符的比较规则。
• 创建表、修改表的语法如下，可通过CHARACTER SET、COLLATE设置字符集、字符序。

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

• 防止修改字符集后乱码等问题的方法：

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

• mysql支持的字符集
  
• ci(大小写不敏感），_cs（大小写敏感），_bin（二元，区分大小写）

---

附录

表-串数据类型

数据类型	说 明
CHAR	1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM	接受最多64 K个串组成的一个预定义集合的某个串
LONGTEXT	与TEXT相同，但最大长度为4 GBMEDIUMTEXT与TEXT相同，但最大长度为16 KSET接受最多64个串组成的一个预定义集合的零个或多个串
TEXT	最大长度为64 K的变长文本TINYTEXT与TEXT相同，但最大长度为255字节VARCHAR长度可变， 最多不超过255 字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）

• 数据类型允许变换排序顺序。如果所有数据都作为串处理，则1位于10之前，而10又位于2之前（串以字典顺序排序，从左边开始比较，一次一个字符）。作为数值数据类型，数值才能正确排序。
• 不管使用何种形式的串数据类型，串值都必须括在引号内（通常单引号更好）。
• 你可能会认为电话号码和邮政编码应该存储在数值字段中（数值字段只存储数值数据），但是，这样做却是不可取的。如果在数值字段中存储邮政编码01234，则保存的将是数值1234，实际上丢失了一位数字。需要遵守的基本规则是：如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。

表-数值数据类型

数据类型	说 明
BIT	位字段，1～64位。（在MySQL 5之前，BIT在功能上等价于TINYINT）
BIGINT	整数值，支持−9223372036854775808～9223372036854775807（如果是UNSIGNED，为0～18446744073709551615）的数。
BOOLEAN（或BOOL）	布尔标志，或者为0或者为1，主要用于开/关（on/off）标志。
DECIMAL（或DEC）	精度可变的浮点值DOUBLE双精度浮点值。
FLOAT	单精度浮点值。
INT（或INTEGER）	整数值，支持−2147483648～2147483647（如果是UNSIGNED，为0～4294967295）的数。
MEDIUMINT	整数值，支持−8388608～8388607（如果是UNSIGNED，为0～16777215）的数。
REAL	4字节的浮点值
SMALLINT	整数值，支持−32768～32767（如果是UNSIGNED，为0～65535）的数。
TINYINT	整数值，支持−128～127（如果为UNSIGNED，为0～255）的数。

• 所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字，这样做将允许你存储两倍大小的值。
• MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8,2)

表-日期和时间数据类型

数据类型	说 明
DATE	表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
TIME	格式为HH:MM:SS
DATETIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同（但范围较小）
YEAR	用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年

表-二进制数据类型

数据类型	说 明
BLOB	Blob最大长度为64 KB
MEDIUMBLOB	Blob最大长度为16 MB
LONGBLOB	Blob最大长度为4 GB
TINYBLOB	Blob最大长度为255字节

参考列表：
《Mysql必知必会》
https://www.cnblogs.com/heyonggang/p/8117754.html
https://www.cnblogs.com/tommy-huang/p/4483583.html
https://segmentfault.com/a/1190000017872232
https://www.cnblogs.com/yhongji/p/9824279.html
https://www.cnblogs.com/progor/p/8877821.html
https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html