MySQL语句的语法总结
1、数据库方面
1.1、新建数据库
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];
1.1.1、字符集和校对规则
-
查看所支持的字符集的完整列表
SHOW CHARACTER SET; #显示所有可用的字符集以及每个字符集的描述和默认校对规则
-
查看所有可用的校对规则
SHOW COLLATION;
字符集和校对规则大多用在表和列层面,也可以用在 SELECT 查询。
1.2、查看系统中有哪些数据库
SHOW DATABASES;
1.3、选择要操作的数据库
USE 数据库名;
1.4、删除数据库
DROP DATABASE 数据库名;
2、表方面
2.1、查看数据库中的表
SHOW TABLES;
2.2、创建表
CREATE TABLE 表名(列名1 数据类型 [列的约束条件],
列名2 数据类型 [列的约束条件],
......
[表级别的约束条件])
)[ENGINE=存储引擎类型] [CHARACTER SET 字符集] [COLLATE 校对规则];
-
数据类型有:
-
列级别的约束条件有:
- 非空约束:NOT NULL|NULL
- 默认约束:DEFAULT
- 唯一约束:UNIQUE
- 自动增长:AUTO_INCREMENT
- 主键约束:PRIMARY KEY
- 字符集:CHARACTER SET(查看 SHOW CHARACTER SET;)
- 校对规则:COLLAT(查看 SHOW COLLATION;)
-
表级别的约束条件有:
- 主键约束:PRIMARY KEY(列a,列b,…)
- 外键约束:CONSTRAINT 外键名(自定义) FOREIGN KEY(外键列)REFERENCES 表名(外键列指向的列)
-
存储引擎类型有:
- InnoDB:一个可靠的事务处理引擎,它不支持全文搜索;
- MEMORY:功能等同于 MyISAM,但由于数据存储在内存,速度很快;
- MyISAM:是一个性能极高的引擎,支持全文搜索,但不支持事务处理。
2.3、查看表的信息
2.3.1、desc 命令查看表的详细信息
desc 表名;
2.3.2、查看建表信息
SHOW CREATE TABLE 表名;
2.4、删除表
DROP TABLE 表名;
2.5、修改表
2.5.1、修改表的结构
ALTER TABLE 表名(
MODIFY 列名 数据类型 约束, #修改列的类型
ADD 列名 数据类型 约束, #增加列
DROP 列名, #删除列
CHANGE 旧列名 新列名 数据类型 约束, #列修改
[FIRST|AFTER 列名] #修改列的排列顺序
);
RENMAE TABLE 旧表名 TO 新表名; #改表名
2.5.2、修改表中的数据
2.5.2.1、插入数据
INSERT INTO
表名(列名1,列名2,...,列名n)
VALUES
(值1,值2,...,值n),
(值1,值2,...,值n),
...
(值1,值2,...,值n);
还可以插入检索出来的数据:
INSERT INTO 表名(列名1,列名2,...,列名n) SELECT语句;
2.5.2.2、更新数据
UPDATE [IGNORE] 表名 #IGNORE 关键字,即使其中几行发生错误,其余的更新动作也将继续完成
SET
列名1 = 值1,
列名2 = 值2,
...
列名n = 值n
[WHERE 过滤条件]; #WHERE 还是必要的,如果没有的话,将会更新表中的所有行
2.5.2.3、删除数据
DELETE FROM 表名
[WHERE 过滤条件]; #如果不加上 WHERE 子句,将删除所有行。不过注意的是,并不是删除整个表
DELETE 删除整行而不是删除列。为了删除指定的列,请使用 UPDATE 语句。
TRUNCATE TABLE 表名;
#更快地从表中删除所有行
2.6、查询表
2.6.1、单个表
2.6.1.1、普通查询
SELECT [DISTINCT] [表名.]列名/列名1,列名2,.../*/Concat(列名1,列名2,...) AS 别名/列名1 +、-、*、/ 列名2 AS 别名/常用函数
FROM 表名 [AS 别名]
WHERE 过滤条件 [AND/OR/IN/NOT 过滤条件][LIKE 通配符过滤][REGEXP 正则表达式过滤]
ORDER BY 列名a[,列名b,...][DESC/ASC]
LIMIT n;
-
常用函数有:
-
过滤条件有:
- =、<>、!=、<、>、>=、<=、BETWEEN…AND…、IS NULL
-
通配符有:(匹配整个列。如果被匹配的文本在列值中出现,LIKE 将不会找到它,相应的行也不会被返回)
- 百分号通配符:% 表示任何字符出现的任意次数
- 下划线通配符:_ 表示一个字符
-
正则表达式有:(在列值内进行匹配。如果被匹配的文本在列值中出现,REGEXP 将会找到它,相应的行也会被返回)
-
基本字符匹配:. 代表匹配任意一个字符
-
进行 OR 匹配:|
-
匹配几个字符之一:[character]
-
匹配范围:[char1-char2]
-
匹配特殊字符:\\ (转义字符)+ 特殊字符
-
匹配字符类:
字符类 说明 [:alnum:] 任意字母和数字(同 [a-zA-Z0-9]) [:alpha:] 任意字符(同 [a-zA-Z]) [:blank:] 空格和制表符(同 [\\t]) [:cntrl:] 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 个或多个匹配 ? 0 个或 1 个匹配 {n} 指定数目的匹配 {n,} 不少于指定数目的匹配 {n,m} 匹配数目的范围 -
定位符:
元字符 说明 ^ 文本的开始 $ 文本的结尾 [[:<:]] 词的开始 [[:>:]] 词的结尾
-
2.6.1.2、分组查询:
SELECT [DISTINCT] 聚集函数
FROM 表名 [AS 别名]
WHERE 过滤条件 [AND/OR/IN/NOT 过滤条件][LIKE 通配符过滤][REGEXP 正则表达式过滤]
GROUP BY 列名a[,列名b,...] [WITH ROLLUP] #仅在按组进行聚集时使用
HAVING 分组过滤条件 #仅在按组进行聚集时使用
ORDER BY 列名a[,列名b,...][DESC/ASC]
LIMIT n;
-
聚集函数有:
聚集函数 说明 AVG() 某列值的平均值 COUNT() 某列值的行数 MAX() 某列值的最大值 MIN() 某列值的最小值 SUM() 某列值之和 -
分组数据的规定:(分组允许把数据分为多个逻辑组,以便每个分组进行聚集函数)
- GROUP BY 子句可以包含任意数目的列;
- 如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总;
- GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数);
- 除聚集函数语句外,SELECT 语句中的每个列都必须在 GROUP BY 子句中给出;
- 如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL,它们将分为一组;
- GROUP BY 子句必须出现在 WHERE 子句之后,ORDER BY 子句之前。
-
分组过滤条件:
HAVING 支持所有的 WHERE 操作符。与 WHERE 的差别在于,WHERE 在数据分组之前过滤,HAVING 在数据分组后进行过滤。
2.6.1.3、子查询
SELECT 列名1[,列名2,...]
FROM 表名
WHERE 列名a[,列名b,...] IN (SELECT 列名a[,列名b,...]
FROM 表名
WHERE 列名c[,列名d,...] IN (SELECT 列名c[,列名d,...]
.......
))
唯一需要注意的是,在 WHERE 子句中使用子查询,应该保证 SELECT 语句与 WHERE 子句中有相同的列。
2.6.2、多个表
2.6.2.1、创建联结表
SELECT 列名1,列名2[,列名3,...]
FROM 表名1,表名2[,表名3,...]
WHERE 表名1.列名a = 表名2.列名a [AND 联结条件[AND 联结条件,...]] #如果不使用 WHERE 将返回的是笛卡尔积,因此 WHERE 在多表联结时很必要
ORDER BY 列名1[,列名2,...]
上述的为等值联结,也称为内部联结。内部联结也可以使用以下形式:
SELECT 列名1,列名2[,列名3,...]
FROM 表名1 INNER JOIN 表名2
ON 表名1.列名a = 表名2.列名a;
2.6.2.2、创建高级联结
-
自联结
SELECT 别名1.列名a,别名1.列名b[,别名1.列名c,...] FROM 表名 AS 别名1,表名 AS 别名2 WHERE 别名1.列名1 = 别名2.列名2 [AND ...]
-
自然联结
无论何时对表进行联结,应该至少有一个列出现在不至一个表中(被联结的列)。标准的联结返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使得每个列只返回一次。
系统不能完成这项工作,由你自己完成。*自然联结是这样一种联结,其中你选择那些唯一的列。这一般是对表进行使用通配符(SELECT ),对所有其他表的列使用明确的子集来完成的。
SELECT 别名1.*,列名2[,列名3,...] FROM 表名1 AS 别名1,表名2 AS 别名2[,表名3 AS 别名3,...] WHERE 别名1.列名1 = 别名2.列名1 [AND ...]
-
外部联结
联结包含了那些在相关表中没有关联行的行,称为外部联结。分为左联结和右联结。
SELECT 表名1.列名1,表名2.列名2[,...] FROM 表名1 [LEFT|RIGHT] OUTER JOIN 表名2 ON 别名1.列名1 = 别名2.列名1 [AND ...]
2.6.2.3、组合查询
SELECT 语句 UNION SELECT 语句
#默认去掉了重复的行
UNION 的规则:
- UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔;
- UNION 中的每个查询必须包含相同的列、表达式或聚集函数;
- 列数据类型必须兼容;类型不必完全相同,但必须是 DBMS 可以隐含转换的类型。
SELECT 语句 UNION ALL SELECT 语句
#包含了重复的行
3、视图
视图主要是用来数据检索。为什么使用视图?
- 重用 SQL 语句;
- 简化复杂的 SQL 操作;
- 使用表的组成部分而不是整个表;
- 保护数据;
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
视图的一些使用限制:
- 与表一样,视图必须唯一命名;
- 对于可以创建的视图数目没有限制;
- 为了创建视图,必须具有足够的访问权限;
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图;
- ORDER BY 可以用在视图中,但如果从该视图检索数据的 SELECT 语句中也含有 ORDER BY,那么该视图中的 ORDER BY 将被覆盖;
- 视图不能被索引,也不能有关联的触发器或默认值;
- 视图可以和表一起使用。
创建视图的语法为:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED|LOCAL]CHECK OPTION]
修改视图的语法为:
ALTER [ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED|LOCAL]CHECK OPTION]
用户可以一次删除一个或者多个视图,前提是必须有这个视图的 DROP 权限:
DROP VIEW [IF EXISTS] view_name [,view_name]... [RESTRICT|CASCADE]
更新视图:
通常,视图是可更新的。更新一个视图将更新其基表(因为视图本身没有数据)。如果你对视图增加或删除行,实际上是对其基表增加或删除行。但并非所有的视图都是可以更新的。基本上可以说,如果 MySQL 不能正确地确定被更新的基数据,则不允许更新(包括插入和删除)。这意味着,如果视图定义中有以下操作,则不能进行视图的更新:
- 分组(使用 GROUP BY 和 HAVING);
- 联结;
- 子查询;
- 并;
- 聚集函数;
- DISTINCT;
- 导出(计算)列。
4、使用存储过程
以上的大多数 MySQL 语句都是针对一个或多个表的单条语句。并非所有的操作都这么简单,经常会有一个完整的操作需要多条语句才能完成。存储过程简单地说,就是为以后的使用而保存的一条或多条 MySQL 语句的集合。可将其视为批文件,虽然他们的作用不仅限于批处理。
4.1、使用存储过程
4.1.1、创建存储过程
请看下面的一个实例——一个返回平均价格的存储过程:
mysql> DELIMITER // #临时改变语句结束分隔符
mysql> CREATE PROCEDURE productpricing()
-> BEGIN
-> SELECT Avg(prod_price) AS priceaverage
-> FROM products;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
此存储过程的过程名为 productpricing,用 CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数,它们将在 () 中列举出来。此存储过程没有参数,但后跟的 () 仍然重要。BEGIN 和 END 语句来限定存储过程体。
4.1.2、执行存储过程
MySQL 称存储过程的执行为调用,因此 MySQL 执行存储过程的语句为 CALL。CALL 接受存储过程的名字以及需要传递给它的任意参数。
mysql> CALL productpricing();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
其实,就是跟调用函数一样。也可以在括号内定义参数。
4.1.3、删除存储过程
mysql> DROP PROCEDURE productpricing;
Query OK, 0 rows affected (0.00 sec)
如果想当过程不存在时删除不产生错误的话,可使用 DROP PROCEDURE ID EXISTS。
4.1.4、使用参数
一般,存储过程并不显示结果,而是把结果返回给你指定的变量。例如:
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
为了调用这个存储过程,必须指明 3 个参数:
mysql> CALL productpricing(@pricelow,@pricehigh,@priceaverage);
Query OK, 1 row affected, 1 warning (0.01 sec)
在调用时,这条语句并不显示任何数据。它返回以后可以显示的变量。
mysql> SELECT @priceaverage,@pricelow,@pricehigh;
+---------------+-----------+------------+
| @priceaverage | @pricelow | @pricehigh |
+---------------+-----------+------------+
| 16.13 | 2.50 | 55 |
+---------------+-----------+------------+
1 row in set (0.00 sec)
接下来的例子使用 IN 和 OUT 参数——ordertotal 接受订单号并返回订单的合计:
mysql> DELIMITER //
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 //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
为了调用这个存储过程,可使用如下语句:
mysql> CALL ordertotal(20005,@total);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
4.1.5、建立智能存储
只有在存储过程内包含业务规则和智能处理时,它们的威力才能真正地显示出来。考虑这样的场景,你需要获得与之前一样的订单合同,但需要对合计增加营业税,不过只针对某些顾客。那么,你需要做下面几件事情:
- 获得合计;
- 把营业税有条件的添加到合计;
- 返回合计。
#Name:ordertotal
#Parameters:onumber = order number
# taxable = 0 if not taxable,1 if taxable
# ototal = order total variable
CREATE PROCEDURE ordertotal_new(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMIT 'Obtain order total,optionally adding tax'
BEGIN
#Declare variable for total
DECLARE total DECIMAL(8,2);
#DECLARE tax percentage
DECLARE taxrate INT DEFAULT 6;
#Get the order total
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
#Is this taxable?
IF taxable THEN
#Yes,so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
#And finally,dave to out variable
SELECT total INTO ototal;
END;
调用:
mysql> CALL ordertotal_new(20005,0,@total);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
mysql> CALL ordertotal_new(20005,1,@total);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 158.86 |
+--------+
1 row in set (0.00 sec)
4.1.5、检查存储过程
SHOW CREATE PROCEDURE ordertotal;
5、使用游标
有时,需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标是一个存储在 MySQL 服务器上的数据库查询,它不是一条 SELECT 语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
5.1、使用游标
使用游标的几个明确步骤:
- 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的 SELECT 语句;
- 一旦声明后,必须使用游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来;
- 对于填有数据的游标,根据需要取出(检索)各行;
- 在结束游标使用时,必须关闭游标。
5.1.1、创建游标
游标使用 DECLARE 语句创建。DECLARE 命名游标,并定义对应的 SELECT 语句,根据需要带 WHERE 和其他子句。
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
5.1.2、打开和关闭游标
游标用 OPEN CURSOR 语句来打开,游标使用完成后,应当使用 CLOSE CURSOR 关闭。
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
但对于检索出的数据也是什么也没做。
5.1.3、使用游标数据
在一个游标被打开后,可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索什么数据(所需的列),检索出来的数据存储在什么地方。它还可以向前移动游标中的内部指针,使下一条 FETCH 语句检索下一行。
CREATE PROCEDURE processorders_new()
BEGIN
#Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
#Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
#Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
#Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT,total DECIMAL(8,2));
#Open the cursor
OPEN ordernumbers;
#Loop through all rows
REPEAT
#Get order number
FETCH ordernumbers INTO o;
#Get the total for this order
CALL ordertotal_new(o,1,t);
#Insert order and total into ordertotals
INSERT INTO ordertotals(order_num,total)
VALUES(o,t);
#End of loop
UNTIL done END REPEAT;
#Close the cursor
CLOSE ordernumbers;
END;
此过程不返回任何数据,但它能够创建和填充另一个表:
mysql> select * from ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+
6 rows in set (0.00 sec)
6、触发器
触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句(或位于 BEGIN 和 END 语句之间的一组语句):
- DELETE
- INSERT
- UPDATE
其他 MySQL 语句不支持触发器。
6.1、创建触发器
在创建触发器时,需要给出 4 条信息:
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(DELETE、INSERT 或 UPDATE);
- 触发器何时被执行(处理之前或之后)。
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @asd;
#表示代码对每个插入行执行,文本 Product added 将对每个插入的行显示一次,导入的变量之中,可以 SELECT 查看
INSERT INTO
products(prod_id,vend_id,prod_name,prod_price,prod_desc)
values
('TNT3',1003,'TNT(6 sticks)',10.00,NULL),
('TNT4',1003,'TNT(8 sticks)',15.00,NULL);
SELECT @asd;
只有表才支持触发器,视图不支持。触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器。因此,每个表最多支持 6 个触发器(每条 INSERT、UPDATE 和 DELETE 之前和之后)。单一触发器不能与多个事件和多个表关联。
6.2、删除触发器
DROP TRIGGER newproduct;
6.3、使用触发器
6.3.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 INTO @asd;
INSERT INTO orders(order_date,cust_id)
values(Now(),10001);
mysql> SELECT @asd;
+-------+
| @asd |
+-------+
| 20011 |
+-------+
1 row in set (0.00 sec)
6.3.2、DELETE 触发器
DELETE 触发器在 DELETE 语句执行之前或之后执行。需要知道一下两点:
- 在 DELETE 触发器代码内,你可以引用一个名为 OLD 的虚拟表,访问被删除的行;
- OLD 中的值全部都是只读的,不能更新。
#使用 OLD 保存将要被删除的行到一个存档表中
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orseers(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;
6.3.3、UPDATE 触发器
UPDATE 触发器在 UPDATE 语句执行之前或之后执行。需要知道以下几点:
- 在 UPDATE 触发器代码中,你可以引用一个名为 OLD 的虚拟表访问以前(UPDATE 语句之前)的值,引用一个名为 NEW 的虚拟表访问新更新的值;
- 在 BEFORE UPDATE 触发器中,NEW 中的值可能也被更新(允许更改将要用于 UPDATE 语句中的值);
- OLD 中的值全都是只读的,不能更新。
#保证州名称缩写总是大写的(不管 UPDATE 语句中给出的是大写还小写)
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
6.4、触发器的进一步介绍
- 创建触发器可能需要特殊的安全访问权限,但是,触发器的执行是自动的;
- 应该用触发器来保证数据的一致性(大小写、格式等)。在触发器中执行这种类型的处理的优点是它总是进行这种处理,而且是透明的,与客户机无关;
- 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器,把更改(如果有需要,甚至还有之前和之后的状态)记录到另一个表非常容易;
- MySQL 触发器不支持 CALL 语句。这表示不能从触发器内调用存储过程。所需要的存储过程代码需要复制到触发器内。
7、管理事务处理
InnoDB 存储引擎支持事务处理。事务处理可以用来维护数据库的完整性,它保证成批的 MySQL 操作要么完全执行,要么完全不执行。利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。
举一个例子,给系统添加订单,过程如下:
- 检查数据库中是否存在相应的客户,如果不存在,添加他/她;
- 提交客户信息;
- 检索客户的 ID;
- 添加一行到 orders 表;
- 如果在添加行到 orders 表时出现错误,回退;
- 检索 orders 表赋予的新订单 ID;
- 对于订购的每项物品,添加新行到 orderitems 表;
- 如果在添加新行到 orderitems 时出现故障,回退所有添加的 orderitems 行和 orders 行;
- 提交订单信息。
几个专有名词:
- 事务:指一组 SQL 语句;
- 回退:指撤销指定的 SQL 语句的过程;
- 提交:指将未存储的 SQL 语句结果写入数据表;
- 保留点:指事务处理中设置的临时占位符,你可以对它发布回退(与回退整个事务处理不同)。
7.1、控制事务处理
管理事务处理的关键在于将 SQL 语句分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。
MySQL 使用下面的语句来标识事务的开始:
START TRANSACTION
7.1.1、使用 ROLLBACK
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
显然,ROLLBACK 只能在一个事务处理内使用(在执行一条 START TRANSACTION 之后)。事务处理用来管理 INSERT、UPDATE 和 DELETE 语句,不能回退 SELECT 语句,也不能回退 CREATE 和 DROP语句。
7.1.2、使用 COMMIT
一般的 MySQL 语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交,即提交(写或保存)操作是自动进行的。但是,在事务处理块中,提交不会隐含的进行。为进行明确的提交,使用 COMMIT 语句,如下:
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
#从系统中完全删除订单 20010。因为涉及到两张表 orders 和 orderitems,所以使用事务处理块来保证订单不会被部分删除,最后 COMMIT 语句仅在不出错时写出更改
7.1.3、使用保留点
简单的事务可以整体回退,更复杂的事务处理可能需要部分提交或回退。为了支持回退部分事务处理,必须能在事务处理块中找到合适的位置放置占位符。这样,如果需要回退,可以回退到某个占位符。这些占位符称为保留点,为了创建占位符,可使用如下语句:
SAVEPOINT delete1;
每个保留点都取标识它的唯一名字,以便在回退时知道回退到何处:
ROLLBACK TO delete1;
7.1.4、更改默认提交行为
默认的 MySQL 行为是自动提交所有更改的。为指示 MySQL 不自动提交更改,可使用如下语句:
SET autocommit = 0;
autocommit 标志决定是否自动提交更改,不管有没有 COMMIT 语句。设置 autocommit 为 0(假)指示 MySQL 不自动提交更改(直到 autocommit 被设置为真为止)。
8、安全管理
8.1、访问控制
MySQL 服务器的安全基础是:用户应该对他们需要的数据具有适当的访问权,既不能多也不能少。即你需要给用户提供他们所需的访问权,且仅提供他们所需的访问权。这就是所谓的访问控制,管理访问控制需要创建和管理用户账号。root 用户对整个 MySQL 具有完全的控制。
8.2、管理用户
MySQL 用户账号和信息存储在名为 mysql 的数据库中。mysql 数据库中有一个名为 user 的表,它包含所有用户账号。
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT user FROM user;
+---------------+
| user |
+---------------+
| root |
| mysql.session |
| mysql.sys |
| root |
+---------------+
4 rows in set (0.00 sec)
8.3、创建用户账号
为了创建一个新的用户账号,使用 CREATE USER 语句:
CREATE USER 用户名 [IDENTIFIED BY '密码'];
mysql> CREATE USER ben IDENTIFIED BY 'opfordream'; #使用了一个密码
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER li; #不使用密码
Query OK, 0 rows affected (0.00 sec)
为了重命名一个用户账号,使用 RENAME USER 语句:
RENAME USER 旧用户名 TO 新用户名;
mysql> RENAME USER ben TO bforta;
Query OK, 0 rows affected (0.00 sec)
8.4、删除用户账号
为了删除一个用户账号(以及相关权限),使用 DROP USER 语句:
mysql> DROP USER bforta;
Query OK, 0 rows affected (0.00 sec)
8.5、设置访问权限
在创建用户账号后,必须接着分配访问权限。新创建的用户账号没有访问权限。它们可以登陆 MySQL,但看不到数据,不能执行任何数据库的操作。
为了查看用户的权限,使用 SHOW GRANTS FOR:
mysql> SHOW GRANTS FOR li;
+--------------------------------+
| Grants for li@% |
+--------------------------------+
| GRANT USAGE ON *.* TO 'li'@'%' |
+--------------------------------+
1 row in set (0.00 sec)
输出结果显示用户 li 有一个权限 USAGE ON *.*。USAGE 表示根本没有权限。
此外,用户定义为 user@host:MySQL 的权限用用户名和主机名结合定义。如果不指定主机名,则使用默认的主机名%(授予用户访问权限而不管主机名)。
为设置权限,使用 GRANT,GRANT 要求至少给出以下信息:
- 要授予的权限;
- 被授予访问权限的数据库或表;
- 用户名。
GRANT 要授予的权限 ON 数据库或表 TO 用户名;
mysql> GRANT SELECT ON crashcource.* TO li;
Query OK, 0 rows affected (0.00 sec)
此 GRANT 允许用户在 crashcourse 数据库的所有表上使用 SELECT。SHOW GRANTS 反映这个更改:
mysql> SHOW GRANTS FOR li;
+---------------------------------------------+
| Grants for li@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'li'@'%' |
| GRANT SELECT ON `crashcource`.* TO 'li'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
GRANT 的反操作为 REVOKE,用它撤销特定的权限:
REVOKE 要授予的权限 ON 数据库或表 FROM 用户名;
mysql> REVOKE SELECT ON `crashcource`.* FROM li;
Query OK, 0 rows affected (0.00 sec)
GRANT 和 REVOKE 可在几个层次上控制访问权限:
- 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
- 整个数据库,使用 ON database.*;
- 特定的表,使用 ON database.table;
- 特定的列;
- 特定的存储过程。
8.6、更改密码
为了更改用户密码,可使用 SET PASSWD 语句:
SET PASSWORD FOR 用户名 = Password('密码');
mysql> SET PASSWORD FOR li = Password('opfordream1996');
Query OK, 0 rows affected, 1 warning (0.00 sec)