MySql必知必会

MySql必知必会

DBMS术语

• 数据库：一种以某种有组织的方式存储的数据集合。
• 表：某种特定类型数据的结构化清单。(数据库中的表拥有唯一标识名)
• 列：表中的一个字段。包含了相同类型的数据。
• 行：表中的数据是按行存储的，所保存的每个记录存储在自己的行内。
• 冗余：数据之间的重复。(同一数据存储在不同数据文件中的现象，通过拆分表解决)
• 主键：一列(或一组列)，其值能够唯一区分表中每一行。(唯一标识表中的这个列(或这个组列))
  • 表中的任何列都可以作为主键，只要拓满足一下条件：
    • 任意两行都不具有相同的主键值
    • 每个行都必须具有一个主键值(主键列不允许NULL值)
• 外键：用于关联两个表。
• 复合键：复合键(组合键)将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列值进行排序的一种结构。

SQL

• 子句：SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。
• 操作符：用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符。

数据库操作相关语句

• 用于显示广泛的服务器状态信息

  • SHOW STATUS;
    

• 显示创建特定数据库或表的MySql语句

  • SHOW CREATE DATABASE databaseName;
    SHOW CREATE Table tableName;
    

• 显示授予用户(所有用户或特定用户)的安全权限

  • SHOW GRANTS；
    

• 显示服务器错误或警告消息

  • SHOW ERRORS;
    SHOW WARNINGS;
    

数据库相关操作

• 创建数据库

#创建数据库
CREATE DATABASE databaseName;
#创建数据库指定字符集
CREATE DATABASE databaseName SET GBK;

• 删除数据库

DROP DATABASE databaseName;

• 查看所有数据库SHOW DATABASES

• 查看数据库详情

SHOW CREATE DATABASE databaseName;

• 使用数据库

USE databaseName；
--必须先使用USE打开数据库才能对该数据库中的数据进行操作
--不然会报错误ERROR 1046 (3D000): No database selected

表相关操作

• 创建表

-- CREATE TABLE 表名(字段1名 字段1类型,字段2名 字段2类型,.....);
CREATE TABLE tableName(colName1 colType1, colName2 colType2,...);
--创建表时指定引擎和字符集
CREATE TABLE tableName(colName1 colType1, colName2 colType2,...) ENGINE=INNODB/MYISAM CHARSET=UTF8/GBK;

表引擎：

1. Innodb：支持数据库的高级操作，如事务、外键等
2. myisam：不支持数据库的高级操作，仅支持数据基础的增删改查操作

数据查询

检索数据

检索所有列(SELECT)

SELECT * FROM tableName;--使用通配符*表名查询表中的所有列。

检索指定列(SELECT)

SELECT colName1, colName2,...,colNameN FROM tableName;

检索不同的行(去重,DISTICNT)

SELECT返回所有匹配的行，当我们希望每行的内容有且唯一不重复时，可以使用DISTINCT关键字。

下面通过举例解释：

创建如下表：

输入：SELECT id FROM products;

输出：

输入：SELECT DISTINCT id FROM products;

输出：

输入：SELECT DISTINCT id, name FROM products;

输出：

问题：为什么明明在id字段前添加了DISTINCT 但是第三种查询语句依然出现了重复的id行？

答：**不能部分使用DISTINCT**DISTINCT关键字应用于所有列而不仅仅是前置它的列，除非指定的所有列都不相同，否则所有行都将被检索出来(简单的理解就是DISTINCT关键字作用在一个包含所有指定字段的整体上)，并且DISTINCT关键字在语法上规定放置在所有查询字段的前面。

限制结果(分页,LIMIT)

​ LIMIT子句用来限定返回行的范围。

SELECT * FROM tableName LIMIT number;
--LIMIT number表示返回不多于number行 ,等价于以下代码
SELECT * FROM tableName LIMIT 0,number;
SELECT * FROM tableName LIMIT 0 OFFSET number;
--LIMIT 5,5 表示MySql返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。

注：在行数不够时，LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行(例如，给出LIMIT 10, 5 但实际之后13行)，MySql将只返回它能够返回的最多行。

排序检索数据(ORDER BY)

• 排序语法

--单列排序
SELECT colName1 FROM tableName ORDER BY colName1;
--多列排序
SELECT colName1， colName2 FROM tableName ORDER BY colName1, colName2;
--多列排序中，依据字段顺序进行依
 次有限排序，即在满足colName1排序的情况下，再根据colName2排序

• 指定排序方向

1. DESC关键字表示降序
2. ASC关键字表示升序，默认情况下不添加排序关键字，默认为升序排序。

SELECT colName1, colName2 FROM tableName ORDER BY colName1 DESE, colName2;

ORDER BY 和 LIMIT相结合能够找出一个列中最高或最低的值。

SELECT * FROM tableName ORDER BY colName DESC LIMIT 1;

ORDER BY子句的位置：在给出ORDER BY 子句时，应该保证它位于FROM子句之后。如果使用了LIMIT，它必须位于ORDER BY 之后。使用子句的次序不对将产生错误信息。

**区分大小写和排序顺序：**在对文本性的数据进行排序时，A与a相同吗？a位于B之前还是位于Z之后？这些问题不是理论问题，其答案取决于数据库如何设置。

在字典排序顺序中，A被视为与a相同，这是MySql(和大多数数据库)的默认行为。但是，许多数据库管理员能够在需要时改变这种行为(如果你的数据库包含大量外语字符，可能必须这样做)。

这里关键的问题是，如果确实需要改变这种排序顺序，用简单的ORTDER BY 子句做不到。你必须请求数据库管理员的帮助。

过滤数据

WHERE子句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY 位于WHERE之后，否则将会产生错误。

WHERE子句操作符

操作符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

WHERE子句组合

AND操作符

AND操作符用来指示满足所有给定条件的行。

SELECT * FROM tableName WHERE colName1='X' AND colName2<2;

---

OR操作符

OR操作符指示检索匹配任一条件的行。

SELECT * FROM tableName WHERE colName1='X' OR colName2<2;

WHERE和OR的计算次序：

OR和AND两个操作符可以结合进行复杂的高级过滤，例如：

SELECT * FORM tableName WHERE colName1=‘x’ OR colName='c' AND colName3<2;

这里思考一个问题：WHERE后面的过滤条件是如何进行分组的，是colName1=‘x’ OR colName='c'和colName3<2进行AND操作，还是colName1=‘x’和colName='c' AND colName3<2进行OR操作。

答：实际上处理方式是后者，SQL在处理OR操作符前，优先处理AND操作符，因此在AND和
OR结合进行高级过滤时容易产生实际结果和预期不一致问题

此问题的解决方法是使用圆括号进行明确的分组或者将OR写在AND的后面

SELECT * FORM tableName WHERE (colName1=‘x’ OR colName='c') AND colName3<2;

在WHERE子句中使用圆括号：任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分的依赖默认计算次序，即使它确实符合实际设计需求。使用圆括号能消除歧义。

---

IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配(相当于多个OR子句的简化写法。

SELECT * FROM tableName WHERE colName IN(1,2);
SELECT * FROM tableName WHERE colName=1 OR colName=2;

使用IN的优点：

• 在使用长的合法选项清单时，In操作符的语法更清楚直观。
• 在使用IN时，计算的次序更容易管理
• IN操作符一般比OR操作符清单执行更快
• IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

---

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定他之后所跟的任何条件。

SELEC * FROM tableName WHERE colName NOT LIKE '%x%';
SELEC * FROM tableName WHERE colName NOT IN(1,2,3);
SELEC * FROM tableName WHERE colName NOT BETWEEN 1 AND 10;

LIKE操作符(模糊查询)

​ 在搜索子句中使用通配符必须使用LIKE操作符。LIKE指示MySql，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

• 通配符(wildcard)：用来匹配值的一部分的特殊字符。
• 搜索模式(search pattern)：有字面值、通配符或两者组合构成的搜索条件。

通配符：

1. 百分号%：表示任何字符出现任意次数，注意%不能匹配NULL。
2. 下划线_：表示值匹配单个字符。

使用通配符的技巧：

1. 不要过度使用通配符。如果其他操作符能够达到相同的目的，应该使用其他操作符。
2. 在确实需要使用通配符时，除非绝对有 必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
3. 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

---

REFEXP操作符(正则表达式)

LIKE和REGEXP的区别：

有一名为prod_name的列其中包含数据‘JetPack 1000’ 和 ‘JetPack2000’

分别利用LIKE和REGEXP进行查询：

SELECT prod_name FROM products WHERE prod_name LIKE '1000';
SELECT prod_name FROM products WHERE prod_name REGEXP '1000';

如果执行上述两句语句，会发现第一条语句不返回数据，而第二条语句返回数据。

这是因为LIKE匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回(除非使用通配符)。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将会被返回。

匹配不区分大小写：

MySql中正则表达式匹配不区分大小写。为区分大小写，可以使用BINARY关键字，如WHERE prod_name BINARY ‘JetPack .000’。

• 元字符

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

• 重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配(等于{1,})
？	0个或1个匹配(等于{0,1})
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围(m不超过255)

• 定位元字符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

例子：

查找一个数(包含以小数点开始的数)开始的字符串

SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9]\\.’；

简单的正则表达式测试：

可以在不使用数据库的情况下用SELECT来测试正则表达式。REGEXP检测总是返回0(没有匹配)或(匹配)。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：

SELECT ‘hello’ REGEXP ‘[0-9]’;

这个例子显然将返回0。

• 匹配字符类

类	说明
[:alnum:]	任意字母行和数字(同[a-zA-Z0-9])
[:alpha:]	任意字符(同[a-zA-Z])
[:blank:]	空格和制表(同[\\t])
[:cntrl:]	ASCII控制字符(ASCII 0到31和127)
[:digit:]	任意数字(同[0-9])
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母(同[a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同[\\f\\n\\r\\\t\\v])
[:upper:]	任意大写字母(同[A-Z])
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

例子：

匹配连在一起的4位数字：

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9]{4}' ORDER BY prod_name;

---

数据处理函数

大多数SQL实现支持一下类型的函数：

• 用于处理文本串(如删除或填充值，转换为大写或小写)的文本函数。
• 用于在数值数据上进行算数操作(如返回绝对值，进行代数运算)的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分(例如，返回两个日期之差，检查日期有等效性等)的日期和时间函数。
• 返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数。

文本处理函数(字符串处理函数)

函数	说明
Concat()	返回拼接后的字符串
Char_Length(str)	返回字符串的长度
Instr(str,substr)	返回substr在str中的位置
Locate(substr,sub)	返回substr在str中的位置
Insert(str,start,length,newstr)	返回插入后的字符串，start为起始位子(从1开始)，length为替换的字符数量(从start后开始计算，从0开始取值)
Upper(str)	返回全部转换为大写字母的字符串，字符串中不属于字母的字符保持不变
Lower(str)	返回全部转换为小写字母的字符串
Trim(str)	返回除去两端所有空格的字符串
Ltrim(str)	返回除去左端所有空格的字符串
Rtrim(str)	返回除去右端所有空格的字符串
Left(str,length)	返回从左边截取的字符串，length表示截取字符串的长度
Right(str,length)	返回从右边截取的字符串
SubString(str,start)	返回任意截取的字符串，start表示开始截取的位置，从1开始取值，默认截取到字符串末尾
SubString(str,start,length)	返回任意截取的字符串，length表示截取字符串的长度，如果length长度超过最大能截取的字符长度则返回最大能截取的字符串，如果lenght<=0返回空串("")
Repeat(str,count)	返回重复count此拼接str的字符串
Replace(str,old,new)	返回替换后的字符串，如果old字符串不存在与str中，则不进行替换
Reverse(str)	返回将str中的字符进行顺序反转进行返回

数值处理函数

函数	说明
Abs(num)	返回一个函数的绝对值
Cos(alpha)	返回一个角度的余弦
Exp(num)	返回一个数的以e为底数的指数值
Mod(n1,n2)	返回除操作的余数，n1为被除数，n2为除数
Pi()	返回圆周率
Rand()	返回一个随机数(数的范围为0-1)
Sin(alpha)	返回一个角度的正弦
Sqrt(num)	返回一个数的平方根
Tan(alpha)	返回一个角度的正切
Floor(num)	返回一个向下取整的数
ROUND(num)	范围对num进行四舍五入后的数，是对数值部分进行四舍五入，正数和负数的舍入方向相反，例如-1.5计算后为-2，而1.5为2
Truncate(num,n)	根据n值来进行取舍

• Truncate(num,n)

  • select truncate(23.879,5);
    --输出：23.87900
    select truncate(23.879,2);
    --输出：23.87
    select truncate(23.879,0);
    --输出：23
    select truncate(23.879,-1);
    --输出：20
    select truncate(23.879,-3);
    --输出：0
    

日期和时间处理函数

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

聚集(合)函数

**聚集函数(aggreate function)：**运行在行组上，计算和返回单个值的函数。

函数	说明
avg(colName)	对多行数据进行统计平均值，忽略列值为NULL的行
Max(colName)	对多行数据进行统计最大值，忽略列值为NULL的行
Min(colName)	对多行数据进行统计最小值，忽略列值为NULL的行
Sum(colName)	对多行数据统计总和，忽略列值为NULL的行
Count(colName)	统计指定列的行数(数量)

• Count(colName)对NULL值的处理

  当Count函数指定列名时，则指定列的值为NULL的行被忽略；

  当Count函数不进行指定时(Count(*))，则不忽略。

• Min(colName)和Max(colName)对非数值数据处理

  MySql允许 它们用来返回任意列中的最值，包括返回文办列中的最小值。在用户文本数据时，如果数据按照相应的列排序，则返回最后和最前的行。

• Sum()函数也可以用来合计计算

  SELECT Sum(colName1*colName2) FROM tableName
  

  在多个列上进行计算：如本例所示，利用标准的算术操作符，所有聚集函数都可以用来执行多个列上的计算。

• 聚集不同值：

  对于以上几个聚集函数，可以有以下两种使用：

  • 对所有的执行计算，指定ALL参数或不给参数(因为ALL是默认行为)
  • 只包含不同的值，指定DISTINCT参数

  SELECT Avg(DISTINCT prod_price) AS avg_price
  FROM products
  WHERE ven_id = 1003;
  

  注意：如果指定列名，则DISTINCT只能用于COUNT()。不能用于COUNT(*)，因此不允许使用COUNT(DISTINCT)。

**注：**MySql中还支持一系列的标准偏差聚集函数，目前不做涉及。

---

分组数据

创建分组(GROUP BY)

关于GROUP BY的重要规定：

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算(所以不能从个别的列取回数据)。//TODO???
• GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式，则必须在GRPUP BY子句中指定相同的表达式，并且不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。//TODO???
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

GROUP BY使用实例：

--1.查询每个部门的平均工资
SELECT deptno, Avg(sal) FROM emp GROUP BY deptno;
--2.查询每个领导下的人数
SELECT mgr,Count(*) FROM BY emp WHERE mgr IS NOT NULL GROUP BY mgr;
--3.查询每个部门工资大于1000的员工数量
SELECT deptno,Count(*) FROM emp WHERE sal>1000 GROUP BY deptno;

ORDER BY 和 GROUP BY的区别：

ORDER BY	GROUP BY
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用(甚至非选择的列也可以使用)	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列(或表达式)，则必须使用

过滤分组(HAVING)

HAVING和WHERE的异同：

​ HAVING和WHERE的句法是完全相同的，最大的区别在于WHERE后面只能写普通字段的条件，不能写聚集函数的条件；而HAVING后面都可以写，但是建议WHERE后面写普通字段条件HAVING后面只写聚集函数的条件，这样有利于代码逻辑的辨识。

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

HAVING使用实例：

--1.查询每个部门的平均工资，要求平均工资大于2000
--错误写法 where后面不能写聚合函数
SELECT deptno,Avg(sal) FROM emp WHERE Avg(sql)>2000 GROUP BY deptno;
--正确写法
SELECT deptno,Avg(sal) a FROM emp GROUP BY deptno HAVING a>2000;--HAVING中可以使用别名

总结(SELECT子句顺序)

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数(分页)	否

子查询(嵌套查询)

• 可以在查询语句中嵌套另一条SQL语句
• 可以嵌套n层

运用实例：

--查询emp表中工资最高的员工信息
SELECT Max(sal) FROM emp;
SELECT * FROM emp WHERE sal=(SLEECT Max(sal) FROM emp);
--查询emp表中工资大于平均工资的员工信息
SELECT Avg(sal) FROKM emp;
SELECT * FROM emp WHERE sal>(SELECT Avg(sal) FROKM emp);
--查询工资高于20号部门最高工资的员工信息
SELECT Max(sal) FROM emp WHERE deptno=20;
SELECT * FROM emp WHERE sal>(SELECT Max(sal) FROM emp WHERE deptno=20);
--查询和Jones工作相同的其他员工信息
SELECT jod FROM emp WHERE ename='jones';
SELECT * FROM emp WHERE job=(SELECT jod FROM emp WHERE ename='jones');
--查询工资最低员工的同事们的信息(同事=相同job)
    --得到最低工资
    SELECT Min(sal) FROM emp;
    --得到那最低工资员工的职位
    SELECT job FROM emp WHERE sal=(SELECT Min(sal) FROM emp);
    --通过职位得到此职位的员工信息排除最低工资那个哥们儿
    SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE sal=(SELECT Min(sal) FROM emp));
--查询最后入职的员工信息
SELECT Max(hiredate) FROM emp;
SELECT * FROM emp WHERE hiredate=(SELECT Max(hiredate) FROM emp);
--查询员工King所属的部门编号和部门名称(需要用到部门表dept)
SELECT deptno FROM emp WHERE ename='king';
SELECT deptno,dname FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE ename='king');
--查询有员工的部门信息(查询在员工表中年出现的部门信息)
    --得到员工表中的部门编号
    SELECT distinct deptno FROM emp;
    --查询上面结果对应的部门详情
    SELECT * FROM dept WHERE deptno IN (SELECT distinct deptno FROM emp);
--查询平均工资最高的部门信息(难度最高，需要考虑并列第一问题)
--查询最高的平均工资
SELECT Avg(sal) a FROM emp GROUP BY deptno ORDER BY a DESC LIMIT 0,1;
--通过最高的平均工资找到对应的部门编号
SELECT deptno FROM emp GROUP BY deptno HAVING Avg(sal)=(上面一坨);
--通过部门编号查询部门信息
SELECT * FROM dept WHERE deptno IN (上面一坨);

子查询总结：

• 嵌套在SQL语句中的查询语句称为子查询

• 子查询可以嵌套n层

• 子查询可以写在什么位置

  • 写在WHERE和HAVING的后面，当做查询条件的值

  • 写在创建表的时候

    CREATE tableName newemp AS (SELECT * FROM emp WHERE deptno=10);
    

  • 写在FROM后面当成一个虚拟表 (必须有别名)

    SELECT ename FROM (SELECT * FROM emp WHERE deptno=10) newtable;
    

联结表

外键(foregin key)：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性(scale)：能够适应不断增加的工作量而不失败。设计良好的数据库应用程序称之为可伸缩性好(scale well)。

笛卡尔积(cartesion product)：由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘第二个表中的行数。工作中不允许出现，因为其极其耗内存，有可能直接奔溃。

叉联结：笛卡尔积的联结类型。

维护引用的完整性：重要的是，要理解联结不是物理实体。换句话说，它在实际的数据表中不存在。联结由MySql根据需要建立，它存在于查询的执行当中。

​ 在使用关系表时，仅在关系列表中插入合法和数据非常重要。回到这里的例子，如果在products表中插入拥有非法供应商ID(即没有在vendors表中出现)的供应商生产的产品，则这些产品是不可访问的，因为他们没有关联到某个供应商。

​ 为防止这种情况发生，可指示MySql只允许在products表的供应商ID列中出现合法值(即出现在vendors表中的供应商)，这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。

内部联结和等值联结

内部联结也称为等值联结(equijion)，它基于两个表之间的相等测试

• 等值联结和内联结查询到的结果一样，都为两张表的交集数据。

• 等值联结：

  SELECT * FROM tableName1,tableName2 WHERE tableName1.x=tableName2.x AND tableName1.colName=18;
  

• 内联结：

  SELECT * FROM tableName1 [INNER] JOIN tableName2 WHERE tableName1.colName=18;
  

联结多个表：

​ SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。例如：

SELECT prod_name,vend_name,prod_price,quantity
FROM prderitems,products,vendors
WHERE products.vend_id=vendors.vend_id
AND orderitems.prod_id=products.prod_id
AND order_num=20005;

注意：应该保证所有联结都有WHERE子句，否则MySql将会返回比想象要的多得多的数据，即发生笛卡尔积现象。

自联结

​ 使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。下面举一个例子：

​ 假如你发现某物品(其ID为DTNTR)存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法：

SELECT prod_id,prod_name
FROM products
WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='DTBTR');

分析：

​ 这是第一种解决方案，它使用了子查询。内部的SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品。

使用自联结的相同查询：

SELECT p1.prod_id,p1.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id=p2.vend_id
AND p2.prod_id='DTNTR';

分析：

​ 此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySql不知道你引用的是products表中的哪个实例。解决此问题的方法就是使用表别名。

用自联结而不用子查询：

​ 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果时相同的，但有时候处理了，联结远比子查询快得多。因此应该试一下两种方法，以确定哪一种的性能更好。

自然联结

​ 无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

​ 怎样完成这项共工作呢？答案是，系统不完成这项工作，由你自己完成。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符(SELECT *)，对所有其他表的列使用明确的子集来完成的。

​ 实例：

SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';

外部联结

没有=操作符*：

​ MySql不支持简化字符*=环绕=*的使用，这两种操作符在其他DBMS中是很流行的。

外部连接的类型：

​ 存在两种基本的外部联结形式；左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中的表的顺序转换为右外部联结。因此，两种类型的外部联结可互接使用，而究竟使用哪一种纯粹是根据方便程度而定。

总结：

LEFT JOIN 、RIGHT JOIN、INNER JOIN的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 (左表的全部加上右表和左表重合的部分)

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 (右表的全部加上右表和左表重合的部分)

inner join(等值连接) 只返回两个表中联结字段相等的行(左表和右表重合的部分)

inner join 就是join

full [outer] join 全外连接

from 表1 full [outer] join 表2 on 表1.字段1 = 表2.字段2；

注意MySql不支持全连接(full join) ，可以使用以下方式代替：

--left join + union + right join;
(SELECT * FROM a LEFT JOIN b ON a.name = b.id) UNION 
(SELECT * from a RIGHT JOIN b on a.name=b.id );

使用带聚集函数的联结：

实例：

SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
OM customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

组合查询

​ MySql允许执行多个查询(多条SELEC语句)，并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)

​ 有两种情况，其中需要使用组合查询：

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

组合查询和WHERE条件：

多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。即任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。

使用UNION

实例：

假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的物品(不考虑价格)。

正如所述，创建UNION涉及编写多条SELECT语句。

首先来看单条语句：

--检索价格不高于5的所有物品
SELECT vend_id,prod_id,prod_price
FROM productssql
WHERE prod_perice <= 5;

--使用IN找出供应商1001和1002生产的所有物品
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1002,1003);

使用union：

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);
--使用多条WHERE进行查询，返回结果集一致
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5 OR vend_id IN (1001,1002);

UNION规则

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)
• 列数据类型 必须兼容：类型不必完全相同，但是必须是DBMS可以隐含地转换的类型(例如不同的数值类型或不同的日期类型)

包含或取消重复的行

UNION从查询结果集中自动去除重复的行(它的行为和单条SELECT语句中使用多个WHERE子句条件一样)。

如果需要返回所有匹配行，可以使用UNION ALL。

实例：

输入

SELECT vend_id，prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

输出

UNION和WHERE：

UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL 为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现(包括重复行)，则必须使用UNION ALL而不是WHERE。

对组合查询的结果排序

​ 在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

组合不同的表：以上举例中使用的表都是相同的，实际上UNION的组合查询可以应用在不同的表。

自然语言全文本搜索

理解全文本搜索

并非所有引擎都支持全文本搜索：

MySql两种最常用的引擎为MyISAM 和 InnoDB，前者支持全文本搜索，而后者不支持。

前面我们讲到了通匹配符查询、正则查询，这些查询方式存在几个限制：

• 性能：通配符和正则表达式匹配通常要求MySql尝试匹配表中的所有行(而且这些搜索极少使用表索引)。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
• 明确控制：使用通配符和正则在表达式匹配，很难(而且并不总是能)明确地控制匹配什么和 不匹配什么。例如：指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
• 智能化的结果：虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但是它们都不能提供一种智能化的选择结果的方法。例如：一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配来排列它们)。

​ 以上几个限制以及更多的限制都可以使用全文本搜索来解决。在使用全文本搜索时，MySql不需要分别查看每个行，不需要分别分析和处理每个词。MySql创建指定列中各词的一个索引，搜索可以针对这些词进行。

启用全本文搜索

​ 一般在创建表时启用全文本搜索。CREATE TABLE 语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。

创建全文本索引的方法

• 创建表的同时创建全文本索引

  • CREATE TABLE productnotes(
    	note_id int NOT NULL AUTO_INCREMENT,
        prod_id char(10) NOT NULL,
        note_date datetime NOT NULL,
        note_text text NULL,
        PRIMARY KEY(note_id),
        FULLTEXT(note_text)
    )ENGINE=MyISAM;
    

• 通过ALTER TABLE的方式添加索引

  • ALTER TABLE tableName ADD FULLTEXT INDEX indexName (colName);
    --indexName为索引名称，可随意命名
    ALTER TABLE `tableName` ADD FULLTEXT indexName (`colName`);
    

• 直接通过CREATE INDEX的方式创建索引

  • CREATE FULLTEXT INDEX indexName ON tableName (colName);
    --也可以在创建索引的时候指定索引的长度
    CREATE FULLTEXT INDEX indexName ON `tableName` (`colName`(length));
    --注意这里INDEX不能省略，只能使用FULLTEXT INDEX
    

​ 在定义之后，MySql自动维护该索引。在增加、更新或删除行时，索引随之自动更新。可以在创建表时指定FULLTEXT，或者在稍后指定(在这种情况下所有已有数据必须立即索引)。

不要在导入数据时使用FULLTEXT：更新索引要花费时间，虽然不是很多，但毕竟要花费时间。如果正在导入数据到一个新表。此时不应该启用FULLTEXT索引。应该先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快的导入数据(而且索引数据的总时间小于在导入每行时分别进行索引所需的总时间)。

删除全文本索引

• 直接使用DROP INDEX(注意：没有DROP FULLTEXT INDEX这种用法)

  • DROP INDEX indexName ON [databaseName.]tableName;
    

• 使用ALTER TABLE的方式

  • ALTER TABLE [databaseName.]tableName DROP INDEX indexName;
    

进行全文本搜索

​ 在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against指定要使用的搜索表达式。

搜索实例

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

输出：

使用完整的Match()说明：

传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们(而且次序正确)。

搜索不区分大小写：

除非使用BINARY方式，否则全文本搜索不区分大小写。

事实上上述搜索可以简单地用LIKE子句完成，如下所示：

输入：

SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

输出：

分析：

上述两条SELECT语句都不包含ORDER BY子句，但次序不同(虽然并不总是出现这种情况)。

全文本搜索是默认排序的：

​ 上述两条SELECT语句都不包含ORDER BY子句。后者(使用LIKE)以不特别有用的顺序返回数据。前者(使用全文本搜索)返回以文本匹配的良好程度排序的数据(具有较高等级的行先返回)。

​ 例如，两个行都包含rabbit，但包含rabbit作第三个词的行等级比作为第二十个词的行高。

排序演示

输入：

SELECT note_text
	   Match(note_text) Against('rabbit') AS rank
FROM productnotes;

输出：

分析：

​ 这里，在SELECT而不是WHERE子句中使用Match()和Against()。这是所有行都被返回(因为没有WHERE子句)。Match()和Against()用来建立一个计算列(别名rank)，此列包含全文本搜索计算出的等级值。等级由MySql根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算得出。正如所见，不包含此rabbit的行等级为0(因此不被前一例子中的WHERE子句选择)。

排序多个搜索项：

​ 如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。

使用查询扩展(WITH QUERY EXPANSION)

​ 查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。

​ 这也是查询扩展的一项任务。在使用查询扩展时，MySql对数据和索引进行两遍扫描来完成搜索：

• 首先，进行一个基本的全文本搜索，找出与搜索条件匹配到的所有行。

• 其次，MySql检查这些匹配行并选择所有有用的词(我们将会简要地解释MySql如何断定什么有用，什么无用)。

• 再其次，MySql再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

  利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。

  **只用于MySql版本4.1.1或更高级的版本：**查询扩展功能是在MySql4.11中 引入的，因此不能用于之前的版本。

下面举一个例子，首先进行简单的全本搜索，没有查询扩展：

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils');

输出：

分析： 只有一行包含词anvils，因此只返回一行。

下面是相同的搜索，这次试用查询扩展：

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

输出：

分析：这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词(customer和recommend)，所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils(按它们的产品名)。

​ 正如所见，查询扩展极大地增加了返回的行数，但这样做也增加了实际上并不想要的行的数目。

布尔文本搜索(IN BOOLEAN MODE)

布尔文本搜索是全文本搜索的附加形式。

理解布尔文本搜索

​ MySql支持全文本搜索的另一种形式，称为布尔方式(boolean mode)。以布尔方式，可以提供关于如下内容的细节：

• 要匹配的词
• 要排斥的词(如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此)
• 排列提示(指定某些词比其他词更重要，更重要的词等级更高级)
• 表达式分组
• 其余内容…

即使没有FULL TEXT索引也可以使用：布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULL TEXT索引，也可以使用它。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。

错误实例：

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy'IN BOOLEAN MODE);

输出：

分析：此全文本搜索检索包含词heavy的所有行(有两行)。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。

正确实例：

为了匹配包含heavy但不包含任意以rope开始的词的行，可使用以下查询：

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rpoe*' IN BOOLEAN MODE);

输出：

分析：这次只返回一行。这一次任然匹配词heavy，但-rope*明确地指示MySql排除包含rope*()(任何以rope开始的词，包括rope)的行，这就是为什么上一个例子中的第一行被排除的原因(包含单词dropping)。

在MySql 4.x中所需的代码更改：如果你使用的是MySql4.x，则上面的例子可能不返回任何行。这是*操作符处理中的一个错误。为在MySql 4.x中使用这个例子，使用-ropes而不是-rope*(排除ropes而不是排除任何以rope开始的词)。

全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~	取消一个词的排序值，这对于标记“noise（无用信息）”的单词很有用。包含这类单词的行较其它行等级低
*	词尾的通配符
""	定义一个短语(与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语)

下面举几个例子，说明某些操作符如何使用：

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

分析：该搜索匹配包含rabbit和bait的行。

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

分析：没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit bait' IN BOOLEAN MODE);

分析：搜索包含rabbit的行，但包含bait的行等级较高

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

分析：这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);

分析：匹配rabbit和carrot，增加前者的等级，降低后者的等级。

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

分析：这个搜索匹配词safe和combination，降低后者的等级。

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(>combination <train)' IN BOOLEAN MODE);

分析：搜索包含词safe和combination或safe和train的行以任何顺序排列，但包含safe combination的行等级高于包含safe train的行

输入：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('s*' IN BOOLEAN MODE);

分析：搜索以s开头的词

全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短语定义为那些具有3个或3个以下字符的词(如果需要这个数目可以更改)。
• MySql带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表
• 许多词出现的频率很高，搜索它们没有用处(返回太多结果)。因此MySql规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
• 如果表中的行数少于3行，则全本文搜索不返回结果(因为每个词或者不出现，或者至少出现在50%的行中)。
• 忽略词中的单引号。例如，don't索引为dont。
• 不具有分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
• 仅在MySIAM数据库引擎中支持全文本搜索。

总结

自然语言全文本搜索：

• 搜索方式：通配符和正则匹配匹配表中的所有行(分别查看每个行，分析和处理每个词)，极少使用表索引；全文本搜索利用表索引，创建指定列中各词的一个索引，面对大量数据拥有较好的搜索效率。
• 排序方式：通配符和正则匹配以不特别有用的顺序返回数据(不进行排序)，全文本搜索返回是默认根据优先级降序排序。
  • 排序优先级：根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算得出。
• 创建方式：
  • 在创建表时启用全文本搜索。
  • 在导入大量数后启用全文本搜索(因为开启全文本搜索后，每一次插入数据都会自动更新表索引，消耗运行时间)。
  • 启用全文本搜索的表引擎必须支持索引，例如MyISAM。

布尔全文本搜索：

• 不一定需要使用FULLTEXT定义索引，但是，没有定义索引的情况下，匹配速度极慢。
• 了解全文本布尔操作符

插入数据(INSERT)

插入数据的几种形式：

• 插入完整的行

  • INSERT INTO Customers
    VALUES(
    	NULL,
    	'Pep E. LaPew',
    	'100 Main Street',
    	'Los Angeles',
    	'CA',
    	'90046',
    	'USA',
    	NULL,
    	NULL
    )
    

    注：

    以上这种语法很简单，但是不安全，应该尽量避免使用，上述SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。即使可得到次序信息，也不能保证是否下一次表变动后各个列保持完全相同的次序。

  • INSERT INTO Customers(
        cust_name,
        cust_address,
        cust_city,
        cust_state,
        cust_zip,
        cust_country,
        cust_contact,
        cust_email
    )VALUES(
    	NULL,
    	'Pep E. LaPew',
    	'100 Main Street',
    	'Los Angeles',
    	'CA',
    	'90046',
    	'USA',
    	NULL,
    	NULL
    )
    

• 插入多个行：

  • 方式一

    • 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'
      );
      INSERT INTO customers(
      	cust_name,
      	cust_address,
      	cust_city,
      	cust_state,
      	cust_zip,
      	cust_country
      )VALUES(
      	'M. Martian',
      	'42 Galaxy Way',
      	'New York',
      	'NY',
      	'11213'
      	'USA'
      );
      --使用多条INSERT语句，每条语句用一个分号结束，最后一次性提交
      

  • 方式二

    • 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 SELECT)

    • 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;
      

      INSERT SELECT中的列名：在使用检索数据进行插入时，不一定要求列名匹配。事实上，MySql甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一列。这对于从使用不同列名的表中导入数据时非常有用的。

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

• 该列定义为允许NULL值(无值或空值)
• 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

如果对表中不允许NULL值且没有默认值的列不给出值，则MySql将产生一条错误信息，并且相应的行插入不成功。

更新和删除数据

更新数据(UPDATE)

• ​ 更新语法：

  • UPDATE customers
    SET cust_email = 'elmer@fudd.com'
    WHERE cust_id = 1005;
    --当不带WHERE子句时，进行全表数据更新
    

UPDATE与安全：可以限制和控制UPDATE语句的使用，更多内容参见后续章节

**在UPDATE语句中使用子查询：**UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

IGNORE关键字：如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出现一个错误，整个UPDATE操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。如果希望即使是发生错误，也继续进行更新，可以使用IGNORE关键字，如下所示：

UPDATE IGNORE customers......

删除数据(DELETE)

• 删除语法：

  • DELETE FROM customers
    WHERE cust_od = 1006;
    --以上语句不带WHERE时，进行全表数据删除，这种删除是逐行依次删除，不推荐
    

更快的删除整张表中的数据：如果想从表中删除所有行，不要使用DELETE，可以使用TRUNCATE TABLE语句，该语句用于重建表格(即删除原来的表并重新创建一个表，而不是逐行删除表中的数据)。

更新和删除的指导原则：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
• 使用强制实施引用完整性的数据库，这样MySql将不允许删除具有与其他表相关联的数据的行。
• 保证每个表都有主键，尽可能像WHERE子句那样使用它(可以指定各个主键、多个值或值得范围)

小心使用：MySql没有撤销(undo)按钮。

创建和操纵表

创建和操纵表

• 创建表

CREATE  TABLE IF NOT EXISTS  customers(
    cust_id INT NOT NULL  AUTO_INCREMENT,
    cust_name CHAR(50) NOT NULL ,
    cust_address CHAR(50) NULL ,
    cust_city CHAR(50) NULL ,
    cust_state CHAR(5)  NULL ,
    cust_zip CHAR(10) NULL ,
    cust_country CHAR(50) NULL,
    cust_contact CHAR(50) NULL ,
    cust_email CHAR(50) NULL,
    PRIMARY KEY (cust_id)
) ENGINE = MyISAM CHARSET =UTF8;

• 查看当前数据库中所有可用的表

SHOW TABLES;

• 查看表详情

SHOW CREATE TABLE tableName;

• 查看表的字段信息

SHOW COLUMNS FROM tableName;
DESC tableName;
DESCRIBE tableName;

注：以上三种方式返回的结果一致

• 删除表

DROP TABLE tableName;

• 修改表名

RENAME TABLE tableNameOld TO tableNameNew;

• 修改表引擎和字符集

ALTER TABLE tableName ENGINE=INNODB/MYISAM CHARSET=UTF8/GBK;

• 添加表字段

--最后添加表字段
ALTER TABLE tableName ADD colName colType;
--最前面添加表字段
ALTER TABLE tableName ADD colName colType FIRST;
--指定某个字段后添加表字段
ALTER TABLE tableName ADD colName1 colType AFTER colName2;

• 删除表字段

ALTER TABLE tableName DROP colName;

• 修改表字段的名称和类型

ALTER TABLE tableName CHANGE colNameOld colNameNew colTypeNew;

• 修改表字段的类型和位置

ALTER TABLE tableName MODIFY colNameOld colTypeNew FIRST/AFTER colNameOther; 

创建表的注意事项

使用NULL值

TODO：

主键(PRIMARY KEY)

​ 主键值必须唯一。即表中的每个行具有唯一的主键值。如果主键使用单个列 ，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

--创建组合主键
CREATE TABLE orderitems(
	order_num INT NOT NULL,
	order_item INT NOT NULL,
	prod_id CHAR(10) NOT NULL
	item_price DECIMAL(8,2) NOT NULL,
	PRIMARY KEY(order_num,order_item)
)ENGINE=InnoDB;
--创建组合主键的错误形式
CREATE TABLE orderitems(
	order_num INT NOT NULL PRIMARY KEY,
	order_item INT NOT NULL PRIMARY KEY,
	prod_id CHAR(10) NOT NULL
	item_price DECIMAL(8,2) NOT NULL
)ENGINE=InnoDB;
--创建单个主键
--1.
CREATE TABLE orderitems(
	order_num INT NOT NULL,
	order_item INT NOT NULL,
	prod_id CHAR(10) NOT NULL
	item_price DECIMAL(8,2) NOT NULL,
	PRIMARY KEY(order_num)
)ENGINE=InnoDB;
--2.
CREATE TABLE orderitems(
	order_num INT NOT NULL PRIMARY KEY,
	order_item INT NOT NULL,
	prod_id CHAR(10) NOT NULL
	item_price DECIMAL(8,2) NOT NULL
)ENGINE=InnoDB;
--错误创建主键
CREATE TABLE orderitems(
	order_num INT NOT NULL,
	order_item INT NULL PRIMARY KEY,--不能作用在默认为NULL的字段
	prod_id CHAR(10) NOT NULL
	item_price DECIMAL(8,2) NOT NULL
)ENGINE=InnoDB;

主键和NULL值：主键为其值唯一标识表中的每行的列，主键中只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识。

使用自增(AUTO_INCREMENT)

使用注意：一张表中只允许一个AUTO_INCREMENT列，而且它必须被索引(如，通过使它成为主键)。

覆盖AUTO_INCREMENT：一个列被指定为AUTO_INCREMENT时，可以在插入时进行自动递增，也可以使用特殊的值，只要其是唯一的即可(至今尚未使用过)，该值将被用来代替自动生成的值，后续的增量将开始使用该手工插入的值进行自增(即从历史最大值基础上进行自增)。

确定AUTO_INCREMENT：自动生成的缺陷在于，这些自动生成的值对于我们来说是未知的。

考虑这个场景：你正在增加一个新订单。这要求在orders表中创建一行，然后在orderitems表中对订购的每项物品创建一行。order_num在orderitems表中与订单细节一起存储。和就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。

通过使用last_insert_id()可以获得最后一个AUTO_INCREMENT值

指定默认值(DEFAULT)

CREATE TABLE orderitems(
	order_num INT NOT NULL,
	prod_id CHAR(10) NOT NULL,
	quantity INT NOT NULL DEFULT 1,
	item_price decimal(8,2) NOT NULL,
	PRIMARY KEY(order_num,order_item)
)ENGINE=InnoDB;

使用注意：

• 与大多数DBMS不同，MySql不允许使用函数作为默认值，它只支持常量
• 建议使用默认值而不是NULL值

引擎类型

​ MySql不同于其他DBMS，它具多种引擎。它打包多个引擎，这些引擎都隐藏在MySql服务器内，全都能执行CREATE TABLE 和SELECT 的指令。

不要使用默认引擎：一般如果省略ENGINE=语句，则使用默认引擎(很可能是MyISAM)，多数SQL语句都会默认使用它。但是并不是所有语句都默认使用它，因此最好加上ENGINE=语句。

​ 引擎种类：

• InnoDB：是一个可靠的事物处理引擎，它不支持全文本搜索，支持外键
• MEMORY：在功能上等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表)
• MyISAM：是一个性能极高的引擎，它支持全文本搜索，但不支持事物处理，不支持外键
• 更多引擎参见官方文档

引擎类型可以混用：当你需要支持事务又需要进行全文本搜索时，可以使用InnoDB和MyISAM分别作为相关表的引擎。

外键不能跨引擎：混用引擎类型有一个缺陷。外键(用于强制实施引用完整性)不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表(ALTER)

更新表语基本法

• 在ALTER TABLE之后给出要更改的表名(该表必须存在，否则将出错)
• 紧跟在表名后面的是所做更改的列表，如果对同表有多条更改，利用逗号进行分隔进行连接。

一般更新操作

ALTER TABLE  vendors
    RENAME TO vendors_new,--修改表名
    ADD vend_email VARCHAR(10) AFTER vend_city,--添加字段在指定字段位置，某人添加在表末尾
    ADD vend_phone VARCHAR(11) FIRST,--添加字段到表最前面
    CHANGE vend_zip vend_zip_new VARCHAR(10),--修改字段名和字段数据类型
    MODIFY vend_zip INT,--修改字段数据类型 发生错误 对同一字段CHANGE和MODIFY不能一起使用
    DROP vend_address,--删除字段
    ENGINE = MyISAM CHARSET = UTF8;--修改引擎和字符集

多操作冲突问题：

​ 在进行同表多修改操作时，需要注意不同更改操作之间顺序问题，比如对同一个字段进行操作时，删除字段操作在修改操作之前会发生错误，或是修改了字段名后，又用原字段名进行操作。

GANGE和MODIFY同字段操作不能一起使用：TODO: ???

ALTER  TABLE vendors
    CHANGE vend_zip vend_zip_new VARCHAR(1),
    MODIFY  vend_zip_new VARCHAR(3);

以上语句，不论是使用原字段名还是新字段名进行操作，都会发生错误[1054] Unknown column 'vend_zip' in 'vendors'

定义外键操作

基本语法

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name,...) 
REFERENCES tbl_name(index_col_name,..)
[ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]
[ON UPADTE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]

​ 该语法可以在CREATE TABLE 和 ALTER TABLE 时使用，如果不指定 CONSTRAINT symbol，MySql会自动生成一个名字。

​ ON DELETE 和 ON UPADTE表示事件触发限制，参数说明会如下：

• RESTRICT：限制外表中的外键改动，默认值
• CASCADE：跟随外键改动
• SET NULL：设空值
• NO ACTION：无动作，默认

修改实例：

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

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表
• 使用INSERT SELECT 语句从旧表复制数据到新表。可以哦使用转换函数和计算字段
• 检验包含所需数据的新表
• 重命名旧表(如果确定，可以删除它)
• 用旧表原来的名字重命名新表
• 根据需要，重新创建触发器、存储过程、索引和外键

删除表(DROP TABLE)

DROP TABLE customers2;

删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

重命名表(RENAME TABLE)

RENAME TABLE customers2 TO customers;
ALTER TBALE customers2 RENAME [TO] customers;

---

使用视图

​ 视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询，因此视图返回的数据是从其他表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据。

​ 下面来看一个例子 ，来理解视图：

输入：

--创建视图
CREATE VIEW productcustomers AS
SELELCT 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';

将整个查询包装成一个名为productcustomers的虚拟表，则可以使用如下检索出相同的数据：

--利用视图进行查询
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

为什使用视图

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

性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

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

使用视图

• 创建视图：

  CREATE VIEW viewName AS SELECT ......
  

• 查看创建的视图：

  SHOW CREATE VIEW viewName;
  

• 删除视图：

  DROP VIEW viewName;
  

• 更新视图：

  可以先使用DROP再用CREATE，也可以直接使用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第二条更新语句会创建一个视图；如果要更新的视图不存在，则第二条更新语句会创建一个视图；如果要更新的视图存在，则第二条更新语句会替换原有视图。

利用视图简化复杂的联结

视图最常见的应用之一是隐藏复杂的SQL，这通常会涉及联结。请看下面的例子：

输入：

CRATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = order.cust_id
 AND orderitems.order_num = orders.order_num;

分析：

以上语句创建了一个名为productcustomers的视图，该视图联结了三个表，以返回了已经订购了任意产品的所有客户的列表。

为了检索订购了产品TNT2的客户，可如下进行：

输入：

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

分析：

仅仅是通过简单的WHERE就可以完成复杂的三表联结查询，在MySql处理此查询时，它将指定的WHERE子句添加到视图查询中的已有WHERE子句中，以便正确过滤数据。

创建可重用的视图：建议在创建视图的时，多考虑其良好的重用性，尽量减少其受特定数据限制的影响。

例如，上面创建的视图返回生产所有产品的客户而不仅仅是生产TNT2的客户。扩展视图的返回不仅使得它能被重用，而且甚至可以通过添加简单的WHERE子句对数据进行再次过滤，这样不需要创建个维护多个类似视图，减少代码冗余。

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

下面的SELECT语句在单个组合计算列中返回供应商名和位置：

输入：

SELECT Concat(RTrim(vend_name),'(',Rtrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;

将其创建为视图：

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',Rtrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;

利用视图输出指定格式的数据：

SELECT * FROM vendorlocations;

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

输入：

CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

使用视图：

SELECT * 
FROM customeremaillist;

如果视图检索数据时使用了一条WHERE子句，则两组子句(一组在视图中，另一组是传递给视图的)将自动组合。

使用视图与计算字段

创建视图：

CREATE VIEW orderitemsexpanded AS
SELECT order_num,prod_id,quantity,item_price,
quantity*item_price AS expanded_price
FROM orderitems;

使用视图：

SELECT * 
FROM orderitemsexpanded
WHERE order_num = 20005;

更新视图

视图的更新视情况而定。

​ 通常，视图是可以更新的(即，可以对它们使用INSERT、UPDATE和DELETE)。更新一个视图将更新其基表。因为视图本身没有数据，如果对视图增加或删除行，实际上是对其基表增加或删除行。、

​ 但是，MySql不能正确地确定被更新的基数据，则不允许更新(包括插入和删除)。

视图定义中有以下操作，则不能进行视图的更新：

• 分组：GROUP BY 和 HAVING
• 联结
• 子查询
• 并
• 聚集函数：Min()、Count()、Sum()等
• DISTINCT：每行的内容有且唯一不重复
• 导出(计算)列

因此一般将视图用于检索(SELECT)，而不是用于更新(INSERT、UPDATE和DELETE)

使用存储过程(TODO：MyBaits中使用存储过程和游标)

MySql5之后添加了对存储过程的支持

为什么要使用存储过程

• 通过把处理封装在容易使用的单元中，简化复杂的操作
• 由于不要求反复建立一系列处理步骤，保证了数据的完整性
• 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员不需要知道这些变化
• 提高性能。因为使用存储过程比使用单独的SQL语句要快
• 存在一些只能用在单个请求中的MySql元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

总而言之，使用存储过程的三个好处即简单、安全、高性能。

不能编写存储过程？依然可以使用：MySql将编写存储过程的安全和访问与执行存储过程的安全和访问区分开。这使得即使不能编写自己的存储过程，也可以在适当的时候执行别的存储过程。

使用存储过程

执行存储过程

CALL productpricing(
	@pricelow,
	@pricehigh,
	@priceaverage
);

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

分析：此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()任然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。

删除存储过程

DROP PROCEDURE productpricing;

分析：这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名。

使用参数

实例一：

​ productpricing只是一个简单的存储过程，它简单的显示SELECT语句的结果。一般，存储过程并不显示结果，而是把结果返回给你指定的变量。

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prod_price) INTO pl FROM products;
	SELECT Max(prod_price) INTO ph FROM products;
	SELECT Avg(prod_price) INTO pa FROM products;
END;

分析：此存储过程接受3个参数。每个参数必须具有指定的类型，这里使用十进制。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySql支持IN(传递给存储过程)、OUT(从存储过程传出，如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见吗，它是一系列SELECt语句，用来检索值，然后保存到相应的变量(通过指定INTO关键字)

调用以上存储过程：(必须指定3个变量名)

CALL productpricing(
	@procelow,
    @pricehigh,
    @priceaverage
);

变量名：所有MySql变量名都必须以@开始

实例二：

CREATE PROCEDURE  ordertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity) FROM orderitems
	WHERE order_num = onumber
	INTO ototal;
END;

分析：onumber定义为IN，ototal定义为OUT。SELECT语句使用这两个参数，WHERE子句使用哦number选择正确的行，INTO使用ototal存储计算出来的合计。

调用以上存储过程：

CALL ordertotal(20005,@total);

分析：必须给ordertotoal传递两个参数；

存储过程的创建和调用与面向过程编程方法的创建个调用及其相似。不过在存储过程中，将传入参数和返回参数都定义在了括号内，用IN和OUT关键字区分。BEGIN和END关键字之间的就是相当于方法体中的代码块，对传入参数进行处理返回传出参数。

显示此合计(传出参数)：

SELECT @total;

建立智能存储过程

CREATE PROCEDURE  ordertotal(
	IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax.'
BEGIN
	--Declare variable for total
	DECLARE total DECIMAL(8,2);
	--Declare tax precentage
	DECLARE taxtrate INT DEFAULT 6;
	
	--Get the order total
	SELECT  Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onmuber
	INTO total;
	
	--Is this taxable
	IF  taxable THEN
		SELECT total+(total/100*taxtare) INTO total;
	END  IF;
	
	--Add finallyl, save to  out variable
	SELECT total INTO otoal;
END;

分析：在该存储过程中，用DECLARE语句定义了两个局部变量。(DECLARE要求指定变量名和数据类型，它也支持可选的默认值)。IF语句检查taxable是否为真，如果为真，则用另一个SELECT语句增加营业税到局部变量total。最后用另一个SELECT语句将total保存到ototal。

试用上述存储过程：

CALL ordertotal(20005, 0 @total);
SELECT @total;
--BOOLEAN值指定为1表示真，指定为0表示假(实际上非零值都表示为真)

检查存储过程(SHOW [CR] PROCEDURE)

--获取指定存储过程的信息
SHOW CREATE PROCEDURE ordertotal;
--获得包括何时、由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS;
--限制过程状态结果，利用LIKE子句
SHOW PROCEDURE STATUS LIKE 'ordertotal';

使用游标(DECLARE … CURSOR)

MySql5添加了对游标的支持

只能用于存储过程：不像多数DBMS，MySql游标只能用于存储过程(和函数)

使用游标

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

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作

创建游标(DECLARE)

输入：

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

分析：这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失(因为它局限于存储过程)

打开和关闭游标(OPEN/COLSE CURSOR)

输入：

OPEN　ordernumbers;

分析：在处理OPEN语句执行查询，存储检索出的数据以供浏览和滚动。

输入：

CLOSE ordernumbers;

分析：COLSE释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭

一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不再需要再次声明，用OPEN语句打开它就可以。

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

对之前例子的改进：·

输入：

CREATE PROCEDURE processorders()
BEGIN 
 	-- Declare the cursor
 	DECLARE ordernumbers CURSOR
 	FOR
 	SELECT order_num FROM orders;
 	--Open the cursor
 	OPEN ordernumbers;
 	--Close the cursor
 	CLOSE ordernumbers;
END;

分析：这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。

使用游标数据(FETCH)

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行(不重复读取同一行)

从游标中检索单个行

输入：

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
	DECLARE o INT;
	
	--Declare the cursor
	DECLARE ordernumbers CUREOR
	FOR
	SELECT order_num FROM orders;
	
	--Open the cursor
	Open ordernumbers;
	
	--Get order number
	FETCH ordernumbers INTO o;
	
	--Close the cursor
	CLOSE ordernumbers;
END;

分析：其中FETCH用来检索当前行的order_num列(将自动从第一行开始)到一个名为o的局部声明变量中。对检索出的数据不做任何处理。

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

输入：

CREATE PROCEDURE processorders()
BEGIN 
	--Declare local variables
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	--Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	--Declare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	--Open the cursor
	OPEN ordernumbers;
	
	--Loop through all rows
	REPEAT
		--Get order number
		FETCH ordernumbers INTO o;
	
	--End of loop
	UNTIL done END REPEAT;
	
	--Close the cursor
	CLOSE ordernumbers;
END;

分析：与前面一个例子一样，这个例子使用FETCH检索当前order_num赋值到声明的名为o的变量中。但与前面一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。为使它起作用，用一个DEFAULT 0定义变量done。那么，done怎样才能在结束时被设置为真呢？答案是用以下语句：

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;

这条语句定义了一个COUNTINUE HANDLER，它是条件出现时被执行的代码。这里，它指出当SQLSTATE ‘02000‘出现时，SET done=1。SQLSTATE ’02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

使用触发器

​ 触发器是MySql响应DELETE、INSERT、UPDATE三种语句而自动执行的一条MySql语句(或位于BEGIN和END语句之间的一组语句)，需要注意的是，除了以上三种语句，其他MySql语句不支持触发器。

需要MySql5：对于触发器的支持是在MySql5中增加的。

使用场景：

• 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确
• 每当订购一个产品时，都从库存数量中减去订购的数量
• 无论何时删除一行，都在某个存档表中保留一个副本

创建触发器

创建四要素：

1. 唯一的触发器名
2. 触发器关联的表
3. 触发器应该响应的活动(DELETE、INSERT、UPDATE)
4. 触发器何时执行(处理之前或之后)
   1. 之前：用于数据验证和净化(目的是保证插入表中的数据确实使能需要的数据)

保持每个数据库的触发器名唯一：触发器名必须在每个表中唯一，但不是在每个数据库中唯一。但是建议实际开发过程中，保证在数据库范围内使用唯一的触发器名。

仅支持表：只有表才支持触发器，视图不支持(临时表也不支持)

输入：

CREATE TRIGGER  newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

分析：CREATE TRIGGER 用来创建名为newproduct的新触发器。触发器可以在一个操作发生之前或之后执行，这里给出了AFTER INSERT ，所以此触发器将在INSERT 语句执行后执行。这个触发器还指定了FOR EACH ROW，因此代码对每个插入行执行。同时文本 Product added将对每个插入的行显示一次。

触发器支持数量：触发器按每个表每个时间每次定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE操作执行触发器，则应该定义两个触发器。

触发器失败：如果BEFORE触发器失败，则MySql将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySql将不执行AFTER触发器(如果有的话)

删除触发器

DROP TRIGGER newproduct;

使用触发器

INSERT触发器

• 在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，访问被插入的行。
• 在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改被插入的值)
• 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含的自动生成值。

输入：

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

分析：此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。

DELETE触发器

​ DELETE触发器在DELETE语句执行之前或之后执行，需要知道一下两点：

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

输入：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW 
BEGIN
	INSERT INTO archive_orders(order_num,order_date,cust_id)
	VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

分析：在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的中的值(要被删除的订单)保存到一个名为archive_orders的存档表中(为实际使用这个例子，需要用到与orders相同的列创建一个名为archive_orders的表)

多语句触发器：触发器使用BEGIN和END语句标记触发体。该好处是能够容纳多条SQL语句。

UPDATE触发器

​ UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道一下几点：

• 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问新更新的值
• 在BEFORE UPDATE触发器中，NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值)
• OLD中的值全都是只读的，不能更新

一下语句保证州名缩写总是大写的(不管UPDATE语句中给出的是大写还是小写)

输入：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH EOW SET NEW.vend_state = Upper(NEW.vend_state);

关于触发器的进一步介绍

• 触发器的一种非常有意义的使用时创建审计跟踪。使用触发器，把更改记录到另一个表非常容易
• MySql触发器中不支持CALL语句，这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
• 应用触发器来保证数据的一致性(大小写、格式等)，在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户应用无关。

管理事务处理

​ 事务处理是一种机制，用来管理必须成批执行的MySql操作，以保证数据库不包含不完整的操作结果。

事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的MySql操作要么完全执行，要么完全不执行。

相关术语：

• 事务(transaction)：指一组SQL语句
• 回退(rollback)：指撤销指定SQL语句的过程
• 提交(commit)：指将为存储的SQL语句结果写入数据库表
• 保留点(savepoint)：指事务处理中的设置的临时占位符(place-holder)，回退到指定事务节点

哪些语句可以回退：事务处理用来管理INSERT、UPDATE和DELETE。不能回退SELECT，也没有意义。

控制事务处理

开启事务：

START TRANSACTION；

使用ROLLBACK​：

ROLLBACK;

ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)

使用COMMIT：

​ 一般的MySql语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit)，但是在事务处理块中，提交不回你隐含地提交。为进行明确的提交，需要使用COMMIT语句，如下所示：

START TRANSACTION；
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

隐含事务关闭：当 COMMIT或ROLLBACK语句执行后，事务会自动关闭(将来的会更改隐含提交)

使用保留点

​ 简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理需要部分提交或回退。

设置保留点：

SAVEPOINT deletel;

回退到保留点：

ROLLBACK TO deletel;

释放保留点：保留点在事务 处理完成(执行一条ROLLBACK或COMMIT)后自动释放。自MySql5以来，也可以使用RELEASE SAVEPOINT明确的释放保留点。

更改默认的提交行为

默认的MySql行为是自动提交所有更改。为指示MySql不自动提交更改，需要使用以下语句：

SET AUTOCOMMIT= 0；

分析：autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置AUTOCOMMIT为0(假)指示MySql不自动提交更改(直到autocommit被设置为真为止)

全球化个本地化

使用字符集和校对顺序

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

• SHOW CHARACTER SET;
  --这条语句显示所有可用的字符集以及 每个字符集的描述和默认校对
  

• SHOW COLLATION;
  --此语句显示所有可用的校对，以及它们适用的字符集
  

• SHOW VARIABLES LIKE 'character%';
  SHOW VARIABLES LIKE 'collation%';
  --实际上，字符集很少是服务器范围(甚至数据库范围)的设置。不同的表，甚至不同的列可能需要不容的字符集，而且两者都可以创建表时指定。
  

• CREATE TABLE mytable(
  	columnn1 INT,
      columnn2 VARCHAR(10)
  )DEFAULT CHARCTER SET hebrew COLLATE hebrew_general_ci;
  --使用带子句的CREATE TABLE
  --此语句创建一个包含两列的表，并指定一个字符集和一个校对顺序
  

安全管理

管理用户

​ MySql用于账号和信息存储在名为mysql的MySql数据库中。一般不需要直接访问mysql数据库和表，但有时需要直接访问，需要直接访问它的时机之一是在需要获得所有用户账号列表。可使用以下代码：

USE mysql SELECT user FROM user;

mysql数据库有一个名为user的表，它包含所有用户账号。

创建用户账号

创建

输入：

--创建用户账号(不添加口令)
CREATE USER ben;
--创建含有口令的用户账号
CREATE USER ben IDENTIFIED  BY 'p@$$wOrd';

分析：创建一个新用户账号。在创建用户账号时不一定需要口令。

指定散列口令：IDENTIFIED BY指定的口令为纯文本，MySql将在保存到吧user表之间对起进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD。

使用GRANT或INSERT GRANT 语句也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。此外，也可以通过直接插入行到user表来增加用户，不过为安全起见，一般不建议这样做。MySql用来存储用户账号信息的表(以及表模式)极为重要，对他们的任何毁坏都可能严重地伤害到MySql服务器。因此，相对于直接处理来说，最好是用标记和函数来处理这些表

重命名

输入：

RENAME USER ben TO bforta;

MySql5之后的版本才支持RENAME USER。对于之前的MySql中重命名一个用户可以使用UPADTE直接更新user表。

删除用户账号

DROP USER bforta;

MySql之前：自MySql 5以来，DROP USER 删除用户账号和所有相关的账号权限，在MySql 5以前，DROP USER只能用来删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySql，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

设置和撤销访问权限(GRANT/REVOKE)

​ 在创建用户账号后，必须分配访问权限。新创建的用户账号没有访问权限。它们能登陆MySql，但不能看到数据，不能执行任何数据库操作。

查看用户账号权限

--查询根本没有权限的用户账号
--输入：
SHOW GRANTS FOR bforta;
--输出：
GRANT USAGE ON *.* TO 'bforta'@'%'
--输出结果显示用户bforta有一个权限USAGE ON *.*。 USAGE表示根本没有权限，所以，此结果表示任意数据库和任意表上对任何东西没有权限。

用户定义为user@host MySql的权限用 用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%(授予用户访问权限而不管主机名)

设置访问权限三要素

• 要授予的权限
• 被授予访问权限的数据库或表
• 用户名

设置访问：

GRANT SELECT,UPDATE,DELETE,DROP ON newbd.* TO test;

分析：此GRANT允许用户在newdb.*(newdb数据库的所有表)上使用SELECT,UPDATE,DELETE,DROP操作。

更改权限后使用SHOW GRANTS查询如下：

每个GRANT添加(更新)用户的一个权限。MySql读取所有授权，并根据它们确定权限。

撤销特定权限

REVOKE SELECT ON crashcourse.* FROM bforta;
--取消赋予用户bforeta的SELECT访问权限

必须存在：被撤销的访问权限必须存在，否则会出错。

GANT和REVOKE可在几个层次上控制访问权限：

• 整个服务器，使用GRANT ALL 和REVOKE ALL
• 整个数据库，使用ON database.*;
• 特定的表，使用ON database.table;
• 特定的列
• 特定的存储过程

权限	说明
ALL	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE TABLE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE
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 DATABASE	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWM	使用mysqladmin shutdowm(用来关闭MySql)
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

未来的授权：在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有在这个要求。这允许管理员在创建数据库和标之前设计和实现安全措施。

​ 这样做的副作用是，当某个数据库或表被删除时(用DROP语句)，相关的访问权限任然存在。而且，如果将来重新创建该数据库或表，这些权限任然起作用

更改口令

SET PASSWORD FOR bforta = Password('n3w p@$$wOrd');

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。

SET PASSWORD = Password('n3w p@$$wOrd')

在不指定用户名时，SET PASSWORD更新当前登录用户的口令。

数据库维护

备份数据

对于打开和使用状态的文件，普通的文本副本备份不一定总是有效的，其可能解决方案如下：

• 使用命令行使用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确的备份转储文件。
• 可用命令行实用程序musalhotcopy从一个数据库复制所有数据(并非所有数据库引擎都支持这个实用程序)
• 可以使用MySql的BACKUP TABLE 或 SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都将接受要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。

首先刷新未写数据：为了保证所有数据被写到磁盘(包括索引数据)，可能需要在进行备份前使用FLUSH TABLES语句

进行数据库维护

MySql提供了一系列的语句，可以(应该)用来保证数据库正确和正常运行。

以下是一些常见的维护语句：

• ANALYZE TABLE：

  用来检查表键是否正确

  ANALYZE TABLE orders;
  

• CHECK TABLE：

  用来你针对许多问题对表进行检查，在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检车以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的连接并进行键检验，QUICK只进行快速扫描。

• REPAIR TABLE：

  如果MyISAM表访问产生不正确和不一致的结果，可能需要用该语句来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。

• OPTIMIZE TABLE：

  如果从一个表中删除大量数据，应该使用该语句来收回所用的空间，从而优化表的性能。

诊断启动问题

​ 服务器启动问题通常在对MySql配置或服务器本身进行更改时出现。MySql在这个问题发生时报告错误，但由于多数MySql服务器是作为系统进程或服务自动启动的，这些消息可能看不到。

​ 以下是几个重要的mysql的命令行选项：

• –help：显示帮助
• –safe-mode：装载减去某些最佳配置的服务器
• –verbose：显示全文本消息(为获得更详细的帮助消息与–help联合使用)
• –version：显示版本信息，然后退出

查看日志文件

主要的日志文件类别：

• 错误日志：它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录。此日志名可用**--log-error**命令行选项更改。
• 查询日志：它记录所有MySql活动，在诊断问题时非常有用。此日志文件可能会变得非常大，因此不应该长期使用。此日志通常名为hostname.log，位于data目录中。此名字可以用--log命令选项更改。
• 二进制日志：它记录更新过数据(或者可能更新过数据)的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用**--log-bin**命令行选项更改。注意，这个日志文件是MySql5中添加的，以前版本中使用的是更新日志。
• 缓慢查询日志：此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log，位于data目录中。此名字可以用**--log-slow-queries**命令行选项更改。

在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

改善性能

• MySql是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。(为查看当前设置，可使用SHOW VARTABLES;和SHOW STATUS;)

• MySql是一个多用户多线程的DMBS。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程(以及它们的线程ID和执行时间)。你也可以用KILL命令终结某个特定的进程(使用这个命令需要作为管理员的登录)

• 试验联结、并、子查询等寻找最佳SELECT操作

• 使用EXPLAIN语句让MySql解释它将如何执行一条SELECT语句。TODO？

• 一般来说，存储过程执行得比一条一条的执行其中的各条MySql语句快

• 使用正确的数据类型

• 不要使用SELECT *

• 有的操作(包括INSERT)支持一个可选的DELAYED关键字，如果使用它，将把控制理解返回给调用程序，并且一旦有可能就实际执行该操作

• 在导入数据时，应该关闭自动提交。

• 必须索引数据库表来改善数据检索性能

• 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，提高性能

• 索引改善数据检索性能，但损害数据插入、删除和更新的性能

• LIKE很慢。一般来说，最好使用FULLTEXT而不是LIKE

• 数据库是不断变化的实体。一组优化良好的表一会儿可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变

• 以上建议在某些情况下都会被打破

MySql数据类型

• 串数据类型

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

使用引号：不管使用何种形式的串数据类型，串值都必须括在引号内(通常单引号更好)

• 数值数据类型

数据类型	说明
BIT	为字段，1~64位。(在MySql5之前，BIT在功能上等价于TINYINT)
BIGINT	整数值
BOOLEAN(或BOOL)	布尔标志，或者为0或者为1，主要用于开/关(on/off)标志
DECIMAL(或DEC)	精度可变的浮点值
DOUBLE	双精度浮点值
FLOAT	单精度浮点值
INT(或INTEGER)	整数值
MEDIUMINT	整数值，支持-83886088388607(如果是UNSIGNED，为016777215)的数
REAL	4字节浮点值
SMALLINT	整数值，支持-3276832767(如果是UNSIGNED，为016777215)的数
TINYINT	整数值，支持-128127(如果为UNSIGNED，为0255)的数

有符号或无符号：所有数值数据类型(除BIT和BOOLEN外)都可以有符号或无符号。有符号数值可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号。

存储货币数据类型：MySql中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8,2)

• 日期和时间数据类型

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

• 二进制数据类型

二进制数据类型可存储任何数据类型(甚至包括二进制信息)，如图像、多媒体、字处理文档等

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