第3章 使用MySQL
-
SQL语句是由简单的英语单词构成的。这些单词称为关键字,每个SQL语句都是由一个或多个关键字构成的。
-
SHOW DATABASES;
: 返回可用数据库的一个列表。 -
SHOW TABLES;
: 返回当前选择的数据库内可用表的列表。 -
自动增量:在每个行添加到表中时,MySQL可以自动地为每个行分配下一个可用编号,不用在添加一行时手动分配唯一值,这个功能就是所谓的自动增量。
如果需要它,则必须在用
CREATE
语句创建表时把它作为表定义的组成部分。 -
DESCRIBE customers;
是SHOW COLUMNS FROM customers;
的一种快捷方式,用来显示表列。 -
SHOW STATUS;
: 显示广泛的服务器状态信息。 -
SHOW CREATE DATABASE db_name;
和SHOW CREATE TABLE tbl_name;
: 分别显示创建特定数据库或表的MySQL语句。 -
SHOW GRANTS FOR user;
: 显示授予用户的安全权限。 -
SHOW ERRORS;
和SHOW WARNINGS;
: 显示服务器错误和警告消息。 -
HELP SHOW;
: 显示允许的SHOW语句。
第4章 检索数据
-
SELECT
语句:从一个或多个表中检索信息。 -
检索单个列:
SELECT prod_name FROM products;
从
products
表中检索一个名为prod_name
的列。 -
检索多个列:
SELECT prod_id, prod_name, prod_price FROM products;
从表
products
中检索三个列,列名之间用逗号分隔。 -
检索所有列:
SELECT * FROM products;
使用通配符
*
,返回表中的所有列。 -
检索不同的行:
SELECT DISTINCT vend_id FROM products;
从表
products
中返回不同的vend_id
行。DISTINCT
关键字应用于所有列。 -
限制结果:
SELECT prod_name FROM products LIMIT 5;
其中的
LIMIT 5
指示MySQL返回不多于5行。SELECT prod_name FROM products LIMIT 3, 4;
其中的
LIMIT 3, 4
指示MySQL返回从行3开始的4行,从行0开始计数。等价于SELECT prod_name FROM products LIMIT 4 OFFSET 3;
-
使用完全限定的表名:
SELECT products.prod_name FROM crashcourse.products;
表名和列名都可以使用完全限定。
第5章 排序检索数据
本章将讲授如何使用SELECT
语句的ORDER BY
子句,根据需要排序检索出的数据。
-
子句:SQL语句由子句构成,有些子句是必需的,而有些子句是可选的。一个子句通常由一个关键字和所提供的数据组成。
-
ORDER BY
子句取一个或多个列的名字,据此对输出进行排序。SELECT prod_name FROM products ORDER BY prod_name;
这条语句表示从
products
表中检索出列prod_name
,然后指示MySQL对prod_name
列以字母顺序排序数据。注意⚠️:可通过非选择列进行排序。
-
按多个列排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
-
指定排序方向
默认的数据排序方式为升序排序
ASC
。为了进行降序排序,必须指定DESC
关键字,DESC
关键字只应用到直接位于其前面的列名。SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
上例中,
prod_price
列以降序排序,而prod_name
列仍然按标准的升序排序。 -
使用
ORDER BY
和LIMIT
的组合,能够找到一个列中最高或最低的值。SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
从
products
表中返回最昂贵的产品价格。 -
ORDER BY
子句位于FROM
子句之后,位于LIMIT
子句之前。
第6章 过滤数据
本章将讲授如何使用SELECT
语句的WHERE
子句指定搜索条件。
-
只检索所需数据需要指定搜索条件,搜索条件也称为过滤条件。
-
在
SELECT
语句中,数据根据WHERE
子句中指定的搜索条件进行过滤,WHERE
子句位于FROM
子句之后,位于ORDER BY
之前。SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
-
WHERE
子句操作符:=
、<>
、!=
、<
、<=
、>
、>=
、BETWEEN
。 -
范围值检查
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
BETWEEN
匹配范围中所有的值,包括指定的开始值和结束值。 -
空值检查
SELECT cust_id FROM customers WHERE cust_email IS NULL;
第7章 数据过滤
本章讲授如何组合WHERE
子句以建立功能更强的更高级的搜索条件,我们还将学习如何使用NOT
和IN
操作符。
-
操作符:用来联结或改变
WHERE
子句中的子句的关键字。也称为逻辑操作符。 -
AND
操作符:用在WHERE
子句中的关键字,用来指示检索满足所有给定条件的行。SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
-
OR
操作符:用在WHERE
子句中的关键字,用来指示MySQL检索匹配任一条件的行。SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
-
计算次序:任何时候使用具有
AND
和OR
操作符的WHERE
子句,都应该使用圆括号明确地分组操作符。 -
IN
操作符:WHERE
子句中用来指定要匹配值的清单的关键字,功能与OR
相当。SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
-
NOT
操作符:WHERE
子句中用来否定后跟条件的关键字。SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
注意⚠️:MySQL支持使用
NOT
对IN
、BETWEEN
和EXISTS
子句取反。
第8章 用通配符进行过滤
-
通配符:用来匹配值的一部分的特殊符号。通配符本身实际是SQL的
WHERE
子句中有特殊含义的字符。 -
搜索模式:由字面值、通配符或两者组合构成的搜索条件。
-
LIKE
操作符:指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。从技术上说,LIKE
是谓词而不是操作符。 -
百分号
%
通配符:代表搜索模式中给定位置的任意个字符,但是%
不能匹配NULL
。搜索可以区分大小写。SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%'
搜索模式
'%anvil'
表示匹配任何位置包含文本anvil
的值,而不论它之前或之后出现什么字符。 -
下划线
_
通配符:只匹配单个字符。 -
注意⚠️:1、不要过度使用通配符。2、把通配符置于搜索模式的开始处,搜索效率最低。3、仔细注意通配符的位置。
第9章 用正则表达式进行搜索
本章将学习如何在MySQL的WHERE
子句内使用正则表达式来更好地控制数据过滤。
-
正则表达式:用来匹配文本的特殊的串(字符集合)。正则表达式用正则表达式语言来建立,MySQL仅支持多数正则表达式实现的一个很小的子集。
-
正则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较。
-
REGEXP
操作符:指示MySQL,后跟的东西作为正则表达式处理。SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
当正则表达式
1000
与prod_name
中的1000
匹配时,就检索出相应的行。 -
.
是正则表达式语言中一个特殊的字符,它表示匹配任意一个字符。SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
这里使用了正则表达式
.000
,1000和2000都匹配且返回。 -
LIKE
匹配整个列,而REGEXP
在列值内进行匹配。 -
MySQL中的正则表达式匹配不区分大小写。为区分大小写,可使用
BINARY
关键字。SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'JetPack .000' ORDER BY prod_name;
-
进行OR匹配:为搜索两个串之一,使用
|
。|
为正则表达式的OR操作符,它表示匹配其中之一。SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
-
匹配几个字符之一:使用
[]
完成匹配几个字符之一的工作,是另一种形式的OR语句。SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
这里,正则表达式
[123] Ton
为[1|2|3] Ton
的缩写,也可以使用后者。[123]
定义一组字符,它的意思是匹配1或2或3。字符集合也可以被否定,即它们将匹配指定字符外的任何东西。为否定一个字符集,在集合的开始处放置一个
^
即可,比如:[^123]
表示匹配除字符1、2、3之外的任何东西。 -
匹配范围:集合可用来定义要匹配的一个或多个字符。
[0123456789]
,这个集合将匹配数字0到9,可简化为[0-9]
。[a-z]
匹配任意字母字符。SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
这里使用正则表达式
[1-5] Ton
。[1-5]
定义了一个范围,这个表达式意思是匹配1到5。 -
匹配特殊字符:正则表达式内具有特殊意义的所有字符都必须通过
\\
进行转义。MySQL要求两个反斜杠,MySQL自己解释一个,正则表达式库解释另一个。SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
\\
也用来引用元字符(具有特殊含义的字符):\\f
换页、\\n
换行、\\r
回车、\\t
制表、\\v
纵向制表。 -
匹配字符类:为了更方便工作,可以使用预定义的字符集,称为字符类。
[: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]
) -
匹配多个实例:有时需要对匹配的数目进行更强的控制。
重复元字符
*
:0个或多个匹配+
:1个或多个匹配(等于{1,}
)
?
:0个或1个匹配(等于{0,1}
)
{n}
:指定数目的匹配
{n,}
:不少于指定数目的匹配
{n,m}
:匹配数目的范围(m不超过255)SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
在使用正则表达式时,编写某个特殊的表达式几乎总是不止一种方法。
-
定位符:为了匹配特定位置的文本,需要使用定位符。
定位元字符
^
:文本的开始$
:文本的结尾[[:<:]]
:词的开始[[:>:]]
:词的结尾如果想找出以一个数(或小数点)开始的所有产品,就需要使用
^
定位符。SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
-
如何使用
REGEXP
起类似LIKE
的作用?利用定位符
^
和$
,通过用^
开始每个表达式,用$
结束每个表达式,可以使REGEXP
的作用与LIKE
一样。 -
简单的正则表达式测试:
SELECT 'hello' REGEXP '[0-9]';
,这个例子显然将返回0。
第10章 创建计算字段
-
字段:基本上和列的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
-
计算字段并不实际存在于数据库表中,计算字段是运行时在
SELECT
语句内创建的。 -
拼接字段:将多个字段按照自己的想法联结到一起构成单个字段。
-
在MySQL的
SELECT
语句中,可使用Concat()
函数来拼接两个列。Concat()
拼接串,即把多个串连接起来形成一个较长的串。Concat()
需要一个或多个指定的串,各个串之间用逗号分隔。 -
RTrim()
函数去掉串右边的所有空格,LTrim()
去掉串左边的空格,Trim()
去掉串左右两边的空格。 -
别名:别名是一个字段或值的替换名,别名用
AS
关键字赋予。别名有时也称为导出列。SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
-
执行算术计算:计算字段的另一常见用途是对检索出的数据进行算术计算。
MySQL算术操作符:
+
加、-
减、*
乘、/
除。
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
第11章 使用数值处理函数
大多数SQL实现支持以下类型的函数。
- 用于处理文本串的文本函数。
- 用于在数值数据上进行算术操作的数值函数。
- 用于处理日期和时间值中提取特定成分的日期和时间函数。
- 返回DBMS正使用的特殊信息的系统函数。
(1)文本处理函数
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');
Soundex()
是一个将任意文本串转换为描述其语音表示的字母数字模式的算法,使得能对串进行发音比较而不是字母比较。
(2)日期和时间处理函数
MySQL使用的日期格式为year-month-day
,比如2005-09-01
。
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';
如果想检索出2005年9月的所有订单,解决办法如下
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
还有另外一种办法
SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
(3)数值处理函数
第12章 汇总数据
- 聚焦函数:运行在行组上,计算和返回单个值的函数。
-
COUNT(*)
对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。COUNT(column)
对特定列中具有值的行进行计数,忽略NULL值。 -
DISTINCT
参数只能用在指定的列名前,不允许使用形如COUNT(DINTINCT *)
。类似地,DISTINCT
必须使用列名,不能用于计算或表达式。SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
第13章 分组数据
本章将介绍如何分组数据,以便能汇总表内容的子集。这涉及两个新SELECT
语句子句,分别是GROUP BY
子句和HAVING
子句。
-
创建分组:分组是在
SELECT
语句的GROUP BY
子句中建立的。分组允许把数据分为多个逻辑组,以便能对每个组进行聚焦计算。 -
GROUP BY
子句可以包含任意数目的列。 -
如果在
GROUP BY
子句中嵌套了分组,数据将在最后规定的分组上进行汇总。 -
GROUP BY
子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚焦函数)。同时,不能在GROUP BY
子句中使用别名。 -
除聚焦计算语句外,
SELECT
语句中的每个列都必须在GROUP BY
子句中给出。 -
如果分组列中具有
NULL
值,则NULL
将作为一个分组返回。 -
GROUP BY
子句必须出现在WHERE
子句之后,ORDER BY
子句之前。 -
使用
WITH ROLLUP
关键字,可以得到每个分组以及每个分组汇总级别的值。SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
-
WHERE
过滤行,而HAVING
过滤分组。WHERE
在数据分组前进行过滤,而HAVING
在数据分组后进行过滤。WHERE
排除的行不包含在分组中。SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2
这段代码列出具有2个(含)以上、价格为10(含)以上的产品的供应商。
-
分组和排序
一般在使用GROUP BY
子句时,应该也给出ORDER BY
子句。
SELECT
子句顺序
第14章 使用子查询
- 子查询:嵌套在其他查询中的查询。
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2');
在SELECT
语句中,子查询总是从内到外处理,子查询一般与IN
操作符结合使用。
- 使用子查询的另一个方法是创建计算字段。
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
orders
是一个计算字段,它是由圆括号中的子查询建立的。在这里使用到了完全限定列名,防止出现二义性。
- 相关子查询:涉及外部查询的子查询。
第15章 联结表
-
SQL最强大的功能之一就是能在数据检索查询的执行中联结表。联结是利用SQL的
SELECT
能执行的最重要的操作。 -
关系表的设计就是要保证把信息分解成多个表,一类数据一个表。各表通过某些常用的值(即关系设计中的关系)互相关联。
-
主键:一列(或一组列),其值能够唯一区分表中每个行。
-
外键:外键作为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
-
联结是一种机制,用来在一条
SELECT
语句中关联表,因此称之为联结。 -
两个表可用
WHERE
子句正确联结。SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
-
笛卡尔积:由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
-
等值联结也称为内部联结。
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
两个表之间的关系是
FROM
子句的组成部分,以INNER JOIN
指定。在使用这种语法时,联结条件使用特定的ON
子句而不是WHERE
子句给出。 -
子查询的嵌套语句可使用联结完成相同的查询。
第16章 创建高级联结
-
SQL允许给表名起别名,好处:1. 可缩短SQL语句;2. 允许在单条
SELECT
语句中多次使用相同的表。 -
表别名不仅能用于
WHERE
子句,它还可以用于SELECT
的列表、ORDER BY
子句以及语句的其他部分。SELECT cust_name, cust_contact 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 = 'TNT2';
-
联结分为内部联结、自联结、自然联结和外部联结。
-
自联结:在查询中需要将两个相同的表进行联结。用自联结而不用子查询。
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';
-
自然联结:每个列只返回一次。自然联结只能选择那些唯一的列,一般通过对表使用通配符,对所有其他表的列使用明确的子集来完成。
-
外部联结:联结包含了那些在相关表中没有关联行的行。外部联结分为右联结(
RIGHT OUTER JOIN
)和左联结(LEFT OUTER JOIN
)。SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
在使用
OUTER JOIN
语法时,必须使用RIGHT
或LEFT
关键字指定包括其所有行的表(RIGHT
指出的是OUTER JOIN
右边的表,而LEFT
指出的是OUTER JOIN
左边的表)。 -
使用带聚集函数的联结
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
第17章 组合查询
-
MySQL允许执行多个查询(多条
SELECT
语句),并将结果作为单个查询结果集返回。这些组合查询通常称为并或复合查询。 -
有两种基本情况,其中需要使用组合查询:
- 在单个查询中从不同的表返回类似结构的数据;
- 对单个表执行多个查询,按单个查询返回数据。
-
多数情况下,组合相同表的两个查询完成的工作与具有多个
WHERE
子句条件的单条查询完成的工作相同。 -
利用
UNION
,可给出多条SELECT
语句,将它们的结果组合成单个结果集。 -
UNION
规则UNION
必须由两条或两条以上的SELECT
语句组成,语句之间用关键字UNION
分隔。UNION
中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。- 列数据类型必须兼容。
-
UNION
从查询结果集中自动去除了重复的行,而UNION ALL
则返回所有的匹配行。 -
对组合查询结果排序:在用
UNION
组合查询时,只能使用一条ORDER BY
子句,它必须出现在最后一条SELECT
语句之后。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) ORDER BY vend_id, prod_price;
第18章 全文本搜索
-
MySQL支持几种基本的数据库引擎,其中最常使用的引擎为
MyISAM
和InnoDB
,前者支持全文本搜索,而后者不支持。 -
在使用全文本搜索时,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;
MySQL根据子句
FULLTEXT(note_text)
的指示对它进行索引。在定义之后,MySQL自动维护该索引。可以在创建表时指定
FULLTEXT
,或者在稍后指定(在这种情况下所有已有数据必须立即索引)。不要在导入数据时使用FULLTEXT
。 -
在索引之后,使用两个函数
Match()
和Against()
执行全文本搜索,其中Match()
指定被搜索的列,Against()
指定要使用的搜索表达式。SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
此
SELECT
语句检索单个列note_text
。由于WHERE
子句,一个全文本搜索被执行。Match(note_text)
指示MySQL针对指定的列进行搜索,Against('rabbit')
指定词rabbit作为搜索文本。传递给
Match()
的值必须与FULLTEXT()
定义中的相同。全文本搜索不区分大小写,除非使用
BINARY
方式。 -
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能是你真正想要的行)。全文本搜索排除那些等级为0的行,按等级以降序排序。
-
在使用查询扩展时,MySQL对数据和索引进行两遍扫描来完成搜索:
- 首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
- 其次,MySQL检查这些匹配行并选择所有有用的词(我们将会简要地解释MySQL如何断定什么有用,什么无用)。
- 再其次,MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词。
利用查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
-
MySQL支持全文本搜索的另外一种形式,称为布尔方式。即使没有定义
FULLTEXT
索引,也可以使用它。但这是一个非常缓慢的操作(其性能将随着数据量的增加而降低)。可通过布尔操作符指定要匹配的词、要排斥的词、排列提示、表达式分组等。SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
匹配包含
heavy
但不包含任意以rope
开始的词的行。匹配词heavy
,但-rope*
明确地指示MySQL排除包含rope*
的行。
下面举几个例子,说明某些操作符如何使用:
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
,降低后者的等级。
- 全文本搜索的使用说明
- 在索引全文本数据时,短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
- MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表。
- MySQL规定了一条
50%
规则,如果一个词出现在50%
以上的行中,则将它作为一个非用词忽略。50%
规则不用于IN BOOLEANMODE
。 - 如果表中的行数少于
3
行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%
的行中)。 - 忽略词中的单引号。例如,
don't
索引为dont
。 - 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
- 仅在
MyISAM
数据库引擎中支持全文本搜索。
第19章 插入数据
本章介绍如何利用SQL的INSERT
语句将数据插入表中。
-
插入完整的行
把数据插入表中的最简单的方法是使用基本的
INSERT
语法,它要求指定表名和被插入到新行中的值。INSERT INTO customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
上面的SQL语句高度依赖于表中列的定义次序,并且还依赖于其次序容易获得的信息。即使可得到这种次序信息,也不能保证下一次表结构变动后各个列保持完全相同的次序。因此,编写依赖于特定列次序的SQL语句是很不安全的。
编写
INSERT
语句的更安全的方法如下:INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
因为提供了列名,
VALUES
必须以其指定的次序匹配指定的列名,即使表的结构改变,此INSERT
语句仍然能正确工作。由于
cust_id
是自动增量,cust_id
的NULL
值是不必要的,cust_id
列并没有出现在列表中,所以不需要任何值。 -
省略列:如果表的定义允许,则可以在
INSERT
操作中省略某些列。省略列必须满足:1. 该列定义为允许NULL
值(无值或空值);2. 在表定义中给出默认值。 -
如果数据检索是最重要的,则可以通过在
INSERT
和INTO
之间添加关键字LOW PRIORITY
,指示MySQL降低INSERT
语句的优先级。 -
插入多个行
第一种方法:可以使用多条
INSERT
语句,每条语句用一个分号结束。第二种方法:只要每条
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 SELECT
,它是由一条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;
事实上,不要求列名匹配,MySQL甚至不关心
SELECT
返回的列名,它使用的是列的位置。在
INSERT SELECT
中SELECT
语句可包含WHERE
子句以过滤插入的数据。
第20章 更新和删除数据
本章介绍如何利用UPDATE
和DELETE
语句进一步操纵表数据。
20.1 更新数据
为了更新(修改)表中的数据,可使用UPDATE
语句。
可采用两种方式使用UPDATE
:
- 更新表中特定行;
- 更新表中所有行。
注意⚠️:如果不是更新表中所有行,一定要加上WHERE
子句。
基本的UPDATE
语句由3部分组成,分别是:
- 要更新的表;
- 列名和它们的新值;
- 确定要更新行的过滤条件。
比如:要更新客户10005的名字和电子邮件地址。
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
注意⚠️:在UPDATE
语句中可以使用子查询,使得能用SELECT
语句检索出的数据更新列数据。
如果在更新数据的过程中,产生错误,希望继续更新数据,可使用IGNORE
关键字,如下所示:
UPDATE IGNORE customers...
为了删除某个列的值,可设置它为NULL
(假如表定义允许NULL
值),如下进行:
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
其中NULL
用来去除cust_email
列中的值。
20.2 删除数据
为了删除(去掉)表中的数据,可使用DELETE
语句。
可采用两种方式使用DELETE
:
- 删除表中特定行;
- 删除表中所有行。
注意⚠️:如果不是删除表中所有行,一定要加上WHERE
子句。
下面的语句从customers
表中删除一行:
DELETE FROM customers
WHERE cust_id = 10006;
注意⚠️:DELETE
删除整行或表中所有行,但是不删除表本身。
可使用UPDATE
删除指定的列值。
如果想删除表中所有行,使用TRUNCATE TABLE
语句效率更高,TRUNCATE
实际是删除原来的表并重新创建一个表。
MySQl将不允许删除具有与其他表相关联的数据的行。
MySQL没有撤销按钮。
第21章 创建和操纵表
本章讲授表的创建、更改和删除的基本知识。
21.1 创建表
- 为了用程序创建表,可使用SQL的
CREATE TABLE
语句,在该语句中必须给出下列信息:- 新表的名字,在关键字
CREATE TABLE
之后给出; - 表列的名字和定义,用逗号分隔。
- 新表的名字,在关键字
表的主键可以在创建表时用PRIMARY KEY
关键字指定。
注意⚠️ : 如果要防止意外覆盖已有的表,SQL要求首先手工删除该表(请参阅后面的小节),然后再重建它。
如果你仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS
。
允许NULL
值的列也允许在插入行时不给出该行的值。
主键值必须唯一,且不允许存在NULL
值。主键可以单列,也可以是多个列组成。主键可以在创建表时定义,也可以在创建表之后定义。
AUTO_INCREMENT
告诉MySQL,本列每当增加一行时自动增量。每个表只允许一个AUTO_INCREMENT
列,而且它必须被索引。若指定了值替换自动生成的值,那么后续的增量将开始使用该手工插入的值。
SELECT last_insert_id()
返回最后一个AUTO_INCREMENT
值,然后可以将它用于后续的MySQL语句。
- 在创建表时,可以为指定列添加默认值,在
CREATE TABLE
语句的列定义中,用DEFAULT
关键字指定。MySQL中只支持常量作为默认值。 - 引擎类型
InnoDB
是一个是一个可靠的事务处理引擎(参见第26章),它不支持全文本搜索;MEMORY
在功能等同于MyISAM
,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);- MyISAM是一个性能极高的引擎,它支持全文本搜索(参见第18章),但不支持事务处理。
21.2 更新表
-
为更新表定义,可使用
ALTER TABLE
语句,在该语句中必须给出下列信息:- 在
ALTER TABLE
之后给出要更改的表名。 - 所做更改的列表。
ALTER TABLE vendors ADD vend_phone CHAR(20);
这条语句给
vendors
表增加一个名为vend_phone
的列,必须明确其数据类型。ALTER TABLE vendors DROP COLUMN vend_phone;
删除刚刚添加的列。
- 在
-
ALTER TABLE
的一个常见用途是定义外键。ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders(order_num);
21.3 删除表
DROP TABLE customers2;
这条语句永久删除customers2
表。
21.4 重命名表
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors;
第22章 使用视图
-
视图是虚拟的表,视图只包含使用时动态检索数据的查询。
-
使用视图的好处:
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
-
在视图创建之后,可以用与表基本相同的方式利用它们。视图仅仅是用来查看存储在别处的数据的一种设施。
-
视图创建和使用的一些规则和限制:
- 与表一样,视图必须唯一命名。
- 对于可以创建的视图数目没有限制。
- 为了创建视图,必须具有足够的访问权限。
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖。
- 视图不能索引,也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。
-
视图的创建、更新、删除的语句:
- 视图用
CREATE VIEW
语句来创建。 - 使用
SHOW CREATE VIEW viewname;
来查看创建视图的语句。 - 用
DROP
删除视图,其语法为DROP VIEW viewname;
。 - 更新视图时,可以先用
DROP
再用CREATE
,也可以直接用CREATE OR REPLACE VIEW
。
- 视图用
-
利用视图简化复杂的联结
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;
创建了一个名为
productcustomers
的视图,它联结了三张表,以返回已订购了任意产品的所有客户的列表。为检索订购了产品
TNT2
的客户,可如下进行:SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
-
利用视图重新格式化检索出的数据
-
用视图过滤不想要的数据
-
使用视图与计算字段
-
更新一个视图将更新其基表,并非所有视图都可更新。
如果视图定义为以下操作,则不能进行视图的更新:
- 分组(使用
GROUP BY
和HAVING
); - 联结;
- 子查询;
- 并;
- 聚集函数(
Min()
、Count()
、Sum()
等); DISTINCT
;- 导出(计算)列。
- 分组(使用
注意⚠️:一般,应该将视图用于检索而不用与更新。
第23章 使用存储过程
23.1 存储过程
存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。存储过程实际上是一种函数。
23.2 为什么要使用存储过程
- 封装多条处理语句,简化复杂的操作。
- 由于不要求反复建立一系列处理步骤,保证了数据的完整性,防止错误。
- 简化对变动的管理,保证数据的安全性。
- 提高性能。
- 存储过程中使用游标,可编写功能更强更灵活的代码。
换句话说,使用存储过程有3个主要的好处,即简单、安全、高性能。
23.3 使用存储过程
-
创建存储过程
CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products; END;
如果存储过程接受参数,它们将在
()
中列举出来。BEGIN
和END
语句用来限定存储过程体。注意⚠️:临时更改命令行实用程序的语句分隔符,如下所示:
DELIMITER // CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products; END // DELIMITER ;
其中,
DELIMITER //
告诉命令行实用程序使用//
作为新的语句结束分隔符,可以看到标志存储过程结束的END
定义为END//
而不是END;
。除
\
符号外,任何字符都可以用作语句分隔符。 -
执行存储过程
MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为
CALL
。CALL
接受存储过程的名字以及需要传递给它的任意参数。CALL productpricing();
当需要传递参数给存储过程时
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
其中,执行名为
productpricing
的存储过程,它计算并返回产品的最低、最高和平均价格。 -
删除存储过程
DROP PROCEDURE productpricing;
这条语句删除刚创建的存储过程。请注意没有使用后面的
()
,只给出存储过程名。仅在存在时删除
DROP PROCEDURE IF EXISTS productpricing;
-
使用参数
一般,存储过程并不显示结果,而是把结果返回给指定的变量。
变量:内存中一个特定的位置,用来临时存储数据。
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;
-
MySQL支持
IN
(传递给存储过程)、OUT
(从存储过程传出)和INOUT
(对存储过程传入和传出)类型的参数。 -
调用上述的存储过程,可使用以下语句:
CALL ordertotal(20005, @total);
-
-
建立智能存储过程
只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来。
考虑这个场景。你需要获得与以前一样的订单合计,但需要对合计增加营业税,不过只针对某些顾客(或许是你所在州中那些顾客)。
那么,你需要做下面几件事情:
- 获得合计(与以前一样);
- 把营业税有条件地添加到合计;
- 返回合计(带或不带税)。
存储过程的完整工作如下:
-- 存储过程的名称:ordertotal -- 参数:onumber(订单号) -- taxable(是否缴税的标志位:如果要增加税,置为1;否则,置为0) -- ototal(订单合计) CREATE PROCEDURE ordertotal( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2) ) COMMENT 'Obtain order total, optionally adding tax' BEGIN -- 定义变量total DECLARE total DECIMAL(8,2); -- 定义税收比例taxrate DECLARE taxrate INT DEFAULT 6; -- 将订单合计存储到total SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total; -- 通过taxable的取值判断是否加税 IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF; -- 最后将订单合计存储到ototal SELECT total INTO ototal; END;
此存储过程有很大的变动。首先,增加了注释(前面放置
--
)。在存储过程复杂性增加时,这样做特别重要。添加了另外一个参数taxable
,它是一个布尔值(如果要增加税则为真,否则为假)。在存储过程体中,用DECLARE语句定义了两个局部变量。DECLARE
要求指定变量名和数据类型,它也支持可选的默认值(这个例子中的taxrate
的默认被设置为6%
)。SELECT
语句已经改变,因此其结果存储到total
(局部变量)而不是ototal
。IF
语句检查taxable
是否为真,如果为真,则用另一SELECT
语句增加营业税到局部变量total
。最后,用另一SELECT
语句将total
(它增加或许不增加营业税)保存到ototal
。注意⚠️:COMMENT关键字起注释的作用,将在
SHOW PROCEDURE STATUS
的结果中显示。可调用以下语句试验它:
CALL ordertotal(20005, 0, @total); select @total; CALL ordertotal(20005, 1, @total); select @total;
-
检查存储过程
显示创建存储过程的
CREATE
语句:SHOW CREATE PROCEDURE ordertotal;
获取存储过程的详细信息列表:
SHOW PROCEDURE STATUS LIKE 'ordertotal';
第24章 使用游标
24.1 游标
游标是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储游标之后,应用程序可以根据需要滚动或者浏览其中的数据。
注意⚠️:MySQL游标只能用于存储过程(和函数)。
24.2 使用游标
使用游标的步骤:
-
首先声明(定义)游标。这个过程没有检索数据,只是定义要使用的
SELECT
语句。 -
然后打开游标以供使用。这个过程用前面定义的
SELECT
语句把数据实际检索出来。 -
对于填有数据的游标,根据需要取(检索)各行。
-
在结束游标使用时,必须关闭游标。
CREATE PROCEDURE processorders()
BEGIN
-- 定义局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- 定义游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- 定义CONTINUE HANDLER,它是在SQLSTATE '02000'条件出现时被执行,SET done=1。
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- 创建一个表存储结果
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT,
total DECIMAL(8,2));
-- 打开游标
OPEN ordernumbers;
--循环检索所有行
REPEAT
-- 获取订单号
FETCH ordernumbers INTO o;
-- 获取订单合计
CALL ordertotal(0, 1, t);
-- 存储订单号和订单合计
INSERT INTO ordertotals(order_num, total)
VALUES(o, t);
-- 结束循环
UNTIL done END REPEAT;
-- 关闭游标
CLOSE ordernumbers;
END;
在这个例子中,我们增加了另一个名为t
的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话),名为ordertotals
。这个表将保存存储过程生成的结果。FETCH
像以前一样取每个order_num
,然后用CALL
执行另一个存储过程(我们在前一章中创建)来计算每个订单的带税的合计(结果存储到t
)。最后,用INSERT
保存每个订单的订单号和合计。
此存储过程不返回数据,但它能够创建和填充另一个表,可以用一条简单的SELECT语句查看该表:
SELECT *
FROM ordertotals;
-
在一个游标被打开后,可以使用
FETCH
语句分别访问它的每一行。FETCH
指定检索什么数据(所需的列),检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH
语句检索下一行。 -
当
REPEAT
由于没有更多的行供循环而不能继续时,出现SQLSTATE '02000'
这个条件。注意⚠️:
DECLARE
语句的发布存在特定的次序。用DECLARE
语句定义的局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义。
第25章 使用触发器
25.1 触发器
触发器是MySQL响应以下任意语句而自动执行的一条MySQL
语句(或位于BEGIN
和END
语句之间的一组语句):DELETE
、INSERT
、UPDATE
。
25.2 创建触发器
在创建触发器时,需要给出4个信息:
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(
DELETE
、INSERT
或UPDATE
); - 触发器何时执行(处理之前或之后)。
在MySQL中,触发器名必须在每个表中唯一,但是最好保持每个数据库的触发器名唯一。
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
CREATE TRIGGER
用来创建名为newproduct
的新触发器。触发器可在一个操作发生之前或之后执行,这里给出了AFTER INSERT
,所以此触发器将在INSERT
语句成功执行后执行。这个触发器还指定FOR EACH ROW
,因此代码对每个插入行执行。在这个例子中,文本Productadded
将对每个插入的行显示一次。
每个表最多支持6个触发器(每条INSERT
、UPDATE
和DELETE
的之前和之后)。
25.3 删除触发器
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器,必须先删除它,然后再重新创建。
25.4 使用触发器
25.4.1 INSERT触发器
INSERT
触发器在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
执行。在插入一个新订单到orders
表时,MySQL生成一个新订单号并保存到order_num
中。触发器从NEW.order_num
取得这个值并返回它。此触发器必须按照AFTER INSERT
执行,因为在BEFORE INSERT
语句执行之前,新order_num
还没有生成。对于orders
的每次插入使用这个触发器将总是返回新的订单号。
测试这个触发器,如下所示:
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);
注意⚠️:通常,将BEFORE
用于数据验证和净化(目的的保证插入表中的数据确实是需要的数据)。本提示也适用于UPDATE
触发器。
25.4.2 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
语句(在BEGIN END
块中一条挨着一条)。
25.4.3 UPDATE触发器
UPDATE
触发器在UPDATE
语句执行之前或之后执行。需要知道以下几点:
- 在
UPDATE
触发器代码中,你可以引用一个名为OLD
的虚拟表访问以前(UPDATE
语句前)的值,引用一个名为NEW
的虚拟表访问新更新的值; - 在
BEFORE UPDATE
触发器中,NEW
中的值可能也被更新(允许更改将要用于UPDATE
语句中的值); OLD
中的值全都是只读的,不能更新。
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
显然,任何数据净化都需要在UPDATE
语句之前进行,就像这个例子中一样。每次更新一个行时,NEW.vend_state
中的值(将用来更新表行的值)都用Upper(NEW.vend_state)
替换。
注意⚠️:
-
应该用触发器来保证数据的一致性(大小写、格式等)。
-
触发器可用于创建审计跟踪。使用触发器,很容易把更改记录到另一个表中。
-
MySQL触发器中不支持
CALL
语句。
第26章 管理事务处理
26.1 事务处理
MyISAM
不支持明确的事务处理管理,而InnoDB
支持事务处理管理。
事务处理可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。
事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。
如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。
关于事务处理的几个术语:
- 事务:一组SQL语句;
- 回退:撤销指定SQL语句的过程;
- 提交:将未存储的SQL语句结果写入数据库表;
- 保留点:事务处理中设置的临时占位符(placeholder),你可以对它发布回退(与回退整个事务处理不同)。
26.2 控制事务处理
管理事务处理的关键在于将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。
26.2.1 使用ROLLBACK
SELECT * FROM ordertotals
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
ROLLBACK
只能在一个事务处理内使用(在执行一条START TRANSACTION
命令之后)。
事务处理用来管理INSERT
、UPDATE
和DELETE
语句。
26.2.2 使用COMMIT
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
在事务处理块中,提交不会隐含地进行,为进行明确的提交,使用COMMIT
语句。
当COMMIT
或ROLLBACK
语句执行后,事务会自动关闭(将来的更改会隐含提交)。
26.2.3 使用保留点
为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符。这样,如果需要回退,可以回退到某个占位符。这些占位符称为保留点。
start transaction;
insert into wallet values(4,1000);
savepoint four;
insert into wallet values(5,600);
savepoint five;
rollback to four;
commit;
保留点在事务处理完成后自动释放,也可以用release savepoint
明确地释放保留点;
26.2.4 更改默认的提交行为
更改默认的提交方式,为指示MySQL不自动提交更改,需要使用set autocommit=0;
,autocommit
标志是针对每个连接而不是服务器的。