晨魅--MySQL常用命令

来源：互联网发布：淘宝店铺做淘宝客编辑：程序博客网时间：2024/06/15 07:01

MySQL常用命令

MySQL初步命令

1 MySQL数据库基础命令

1.1 创建、查看数据库

1.1.1 查看系统数据库

SHOW DATABASES

1.1.2 创建用户数据库

CREATE DATABASE mytest

1.2 在数据库中创建表

1.2.1 切换当前数据库

USE mytest

1.2.2 创建表

CREATE TABLE user

(

id intauto_increment not null primary key,

usernamevarchar(10) not null,

passwordvarchar(10) not null

)

user是表名；id,username和password是列名；int是整形；auto_increment是系统自增；notnull是字段不允许为空；primary key是主键；varchar是字符类型，（10）是限制不超过10个字符。

1.2.3 查看表信息

SHOW TABLES

1.3 增删改查

1.3.1 插入

INSERT INTO user VALUES(1,'Tom','123456')

1.3.2 修改

UPDATE user SET id = 2,username = 'chenmei',password = 123

1.3.3 查询

SELECT * FROM user

1.3.4 删除

DELETE FROM user

WHERE id = 1

1.4 删除数据库

DROP DATABASE mytest

MySQL命令详解

用[ ]表示可选项，语法用灰底色，例子用黄底色

2 MySQL数据库和表

2.1 MySQL数据库

2.1.1 创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] 数据库名

[选项 …]

选项：

[DEFAULT] CHARACTER SET 字符集

| [DEFAULT] COLLATE 校队规则名

IF NOT EXISTS：创建数据库前进行判断，该数据库不存在时才执行CREATE DATABASE操作。用此选项可以避免出现数据库已经存在而在新建的错误。

DEFAULT：指定默认值

字符集：指定数据库采用的字符集

COLLATE：指定字符集的校对规则

【例】创建学生成绩数据库xscj。

CREATE DATABASE IF NOT EXISTS xscj

2.1.2 修改数据库

语法：

ALTER DATABASE [数据库名]

选项 …

选项：

[DEFAULT] CHARACTER SET 字符集名

| [DEFAULT] COLLATE 校对规则名

【例】修改学生成绩数据库（xscj）默认字符集和校对规则。

ALTER DATABASE xscj

DEFAULT CHARACTER SET gb2312

DEFAULT COLLATE gb2312_chinese_ci

2.1.3 删除数据

语法：

DROP DATABASE [IF EXISTS] 数据库名

IF EXISTS：避免删除不存在的数据库时出现错误信息

2.2 MySQL表

2.2.1 创建表

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名

[([列定义] …| [表索引定义])]

[表选项] [select语句]

TEMPORARY：表示创建临时表。

IF NOT EXISTS：创建表前判断该表是否存在，不存在时创建。

【例】在学生成绩数据库（xscj）中也创建一个学生情况表，表名为xs。

CREATE TABLE xs

(

学号 char(6) not nullprimary key,

姓名 char(8) not null,

专业名 char(10) null,

姓别 tinyint(1) not nulldefault 1,

出生日期 date not null,

总学分 tinyint(1) null,

照片 blob(8) null,

备注 text(8) null

)

xs是表名；学号，姓名…等是列名；int是整形； not null是字段不允许为空；primarykey是主键；default 1表示姓名默认值为1。

2.2.2 复制表

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名

[ ( ) LIKE 已有表名 [ ] ]

| [AS (表达式) ];

LIKE:创建一个与已有表名相同结构的新表，复制表结构，不复制表内容。

AS:复制表内容，但索引和完整性约束是不会复制的。

【例】在mytest数据库中，用复制的方式创建一个名为user_copy1的表，表结构直接取自user表；另外再创建一个名为user_copy2的表，其结构和数据都取自user表。

（1）创建表user_copy1表（只复制表结构）

CREATE TABLE user_copy1 LIKE user;

（2）创建表user_copy2表（复制表结构和数据）

CREATE TABLE user_copy2 AS (SELECT * FROM user)

2.2.3 修改表

语法：

ALTER TABLE 表名

选项…

添加列：ALTER TABLE 表名ADD COLUMN列名类型;

【例】ALTER TABLE user ADDCOLUMN a VARCHAR(30)

修改列名：ALTER TABLE 表名CHANGE 原列名新列名类型;

【例】ALTER TABLE userCHANGE a b INT

修改表名：ALTER TABLE 原表名RENAME TO新表名;

【例】ALTER TABLEuser_copy1 RENAME TO usera

删除列：ALTER TABLE 表名DROP COLUMN列名;

【例】ALTER TABLE xsDROP COLUMN 姓名;

2.2.4 删除表

语法：

DROP TABLE [IF EXISTS] 表名

IF EXISTS:判断如果该表存在就删除

【例】DROP TABLE IF EXISTSusera

2.3 MySQL表记录操作

2.3.1 插入新纪录

【例】向xs表里插入一条记录。

INSERT INTO xs (学号,姓名,性别,出生日期,总学分)

VALUES('081101','王林',1,'1994-02-10','50')

2.3.2 替换旧记录

REPLACE语句可以在插入数据前将与新记录冲突的旧记录删除，从而使新纪录能替换旧记录，正常插入。

REPLACE INTO xs (学号,姓名,性别,出生日期,总学分)

VALUES('081101','王林',1,'1994-02-10','50')

2.3.3 从已有表中插入新纪录

【例】将user表记录插入到user1表中。

INSERT INTO user1 SELECT * FROM user

2.3.4 修改记录

1. 修改单个表语法：

UPDATE 表名

SET 列表 1=expr1 [,列名 2=expr2 …]

[WHERE 条件]

[ORDER BY …]

【例】将学生表（xs）中的所有学生的总学分都增加10。将姓名为“刘华”的同学备注填写为“辅修计算机专业”，学号为“081250”

UPDATE xs

SET 总学分 =总学分+10;

UPDATE xs

SET 学号 = '081250',备注 = '辅修计算机专业'

WHERE 姓名 = '刘华'

2. 修改多个表语法：

UPDATE 表名，表名 …

SET 列名 1 = expr1 [,列名 2 = 2expr2 …]

[WHERE 条件]

【例】表user和表user2中都有两个字段：idint(11)、password varchar(10),其中id为主键。当表user中id值与user2中id值相同时，将表user中对应的password值修改为11111111，将表user2中对应的password值改为22222222。

UPDATE user,user2

SET user.password = '11111111',user2.password ='22222222'

WHERE user.id = user2.id

2.3.5 删除记录

1. 删除满足条件行（单行）语法：

DELETE FROM 表名

[WHERE 条件]

[ORDER BY …]

【例】将user2表中id为2的记录删除。

DELETE FROM user2

WHERE id = 2

2. 从多个表中删除行语法：

DELETE 表名，表名

FROM table_references

[WHERE 条件]

表名，表名是指删除那些表里的数据

table_references是指用到了哪些表

【例】删除user1中id值等于user的id值的所有行和user2中id值等于user的id值的所有行。

DELETE user1,user2

FROM user1,user2,user

WHERE user1.id = user.id AND user2.id = user.id

3. 清除表数据语法:

TRUNCATE TABLE 表名

2.4 数据库显示信息

1. 显示当前数据库中所有表的名称：SHOW TABLES

2. 显示MySQL中所有数据库的名称：SHOW DATABASES

3. 显示表中列名：SHOW COLUMNS FROM user

4. 显示表索引：SHOW INDEX FROM user

5. 显示一些系统特定资源的信息，例如正在运行的线程数量：SHOW STATUS

6. 显示系统变量名称和值：SHOW VARIABLES

7. 显示系统中正在运行的所有进程：SHOW PROCESSLIST

8. 显示当前数据库中每个表的信息：SHOW TABLE STATUS

9. 显示服务器所支持的不同权限：SHOW PRIVILEGES

10. 显示最后一条语句所产生的错误：SHOW ERRORS

11. 显示安装后的可用存储引擎和默认引擎：SHOW ENGINES

12. 显示数据库中所有存储过程基本信息：SHOW PROCEDURE STATUS

13. 显示某一个存储过程的详细信息：SHOW CREATE PROCEDURE sp_name

14. 查看表信息：DESCRIBE 表名

15. 查看列信息：DESCRIBE表名列名

3 MySQL查询和视图

3.1 MySQL数据库查询

3.1.1 SELECT子句

3.1.1.1 选择指定列

语法：

SELECT* |列名,列名,… FROM表名

【例】查询xs表中各个同学的姓名，专业名和总学分

SELECT 姓名,专业名,总学分

FROM xs

3.1.1.2 定义列别名

不允许where子句中使用列别名

语法：

SELECT… 列名 [AS列别名]

【例】查询xs表中计算机专业同学的学号，姓名和总学分，结果中各列的标题分别制定为number,name和mark

SELECT 学号 AS number,姓名 AS name,总学分 AS mark

FROM xs

WHERE 专业名 = '计算机'

3.1.1.3 替换查询结果中的数据

语法：

SELECT列名

CASE

WHEN 条件 1 THEN表达式1

WHEN 条件 2 THEN表达式2

…

ELSE 表单式 n

END

FROM 表名

[WHERE条件]

【例】若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50~52之间，替换为合格；若总学分大于52，替换为“优秀”。总学分列的标题更改为“等级”。

select 学号,姓名,

case

when 总学分 is unll then '尚未选课'

when 总学分 < 50 then '不及格'

when 总学分 >= 50 and总学分 <= 52 < then '合格'

else '优秀'

end as 等级

from xs

where 专业名 = '计算机 ';

3.1.1.4 计算列值

语法：

SELECT表达式 …

【例】查询xs表中学号为081101的学生成绩信息，按120分制重新计算成绩。

SELECT 学号,成绩 * 1.20 AS成绩120

FROM xs

WHERE 学号 = '081101'

3.1.1.5 消除结果集中的重复行

性能比较慢，不建议用，可以使用groupby

语法：

SLECTDISTINCT|DISTINCTROW 列名 …

【例】用distinct关键字消除重复行

SELECTDISTINCT 专业名,总学分

from xs

3.1.1.6 聚合函数

1) COUNT函数

用于统计总行数，返回select语句检索到的行中非null值的数目，若找不到匹配行，则返回0。

【例】求学生的总人数

SELECT COUNT(*) AS '学生总人数'

FROM xs

2) MAX和MIN

【例】求选修101课程的学生的最高分和最低分

SELECT MAX(总学分),MIN(总学分)

FROM xs

WHERE 课程号 = '101'

3) SUM函数和AVG函数

SUM：求总和；AVG：求平均值（用法同COUNT函数）

4) VARIANCE和STDDEV函数

VARIANCE：方差；STDDEV：标准差（用法同COUNT函数）

3.1.2 FROM子句

3.1.2.1 引用一个表

【例】SELECT * FROM db2.tb;

db2.tb是指数据库db2下的tb表

3.1.2.2 多表连接

1. 全连接

【例】查询所有学生选过的课程名和课程号

SELECT DISTINCT kc.课程名,xs.课程号

FROM kc,xs

WHERE kc.课程号 = xs.课程号

2. JOIN连接

1) 内连接：指定INNER关键字的连接是内连接

【例】查询所有学生选过的课程名和课程号

SELECT DISTINCT kc.课程名,xs.课程号

FROM kc

INNER JOIN xs ON kc.课程号 = xs.课程号

2) 外链接：分为左外链接（LEFTJOIN），右外链接（RIGHT JOIN）和自然连接。自然连接又分为自然左外连接（NATURAL LEFT JOIN）和自然右外链接（NATURAL RIGHT JOIN）

【例】左连接，主要查xs表里的数据，kc表里不匹配的行的列设置为null

SELECT xs.*,kc.课程号

FROM xs

LEFT JOIN kc ON xs.学号 = kc.学号

【例】右连接，主要查kc表里的数据，xs表里不匹配的行的列设置为null

SELECT xs.*,kc.课程号

FROM xs

RIGHT JOIN kc ON xs.学号 = kc.学号

【例】自然连接

SELECT 课程名,课程号

FROM kc

WHERE 课程号 IN

( SELECTDISTINCT 课程号

FROMkc

NATURALRIGHT JOIN xs

)

3) 交叉连接：指定了CROSS JOIN关键指定连接是交叉连接

【例】SELECT xs.学号,xs.姓名,kc.课程号,kc.课程名

FROM xsCROSS JOIN kc

3.1.3 WHERE子句

where 子句是用来在from子句之后选择行。

3.1.3.1 比较运算

MySQL支持的运算符有 =，<，>，<=，>=，<=>（相等或都等于空），<>（不等于），!=（不等于）。

【例】SELECT 姓名,学号,总学分

FROM xs

WHERE 学号 = '081101'

3.1.3.2 模式匹配

1. LIKE运算符：用LIKE匹配时，常用特殊符号_和%进行模糊查询。%代表0个或多个字符，_代表单个字符。

【例】查询xs表中，学号倒数第二个数字为0的学生的学号，姓名及专业名。

SELECT 学号,姓名,专业名

FROM xs

WHERE 学号 LIKE '%0_'

如果想要查找特殊符号的一个或全部（_和%），必须使用一个转义字符。

【例】查询xs表中名字包含下划线的学生学号和姓名。

SELECT 学号,姓名

FROM xs

WHERE 学号 LIKE '%#_%'ESCAPE '#'

2. REGEXP运算符：用来执行更复杂的字符串比较运算。

【例】查询行李的同学的学号，姓名和专业名

SELECT 学号,姓名,专业名

FROM xs

WHERE 姓名 REGEXP '^李'

1) ^ ：匹配字符串的开始部分

2) [abc]：匹配方括号里出现的字符串abc

3) .：匹配任何一个字符（包括回车和新行）

4) *：匹配星号之前的0个或多个字符任何序列

3.1.3.3 范围比较

用于范围比较的关键字有两个：BETWEEN和IN。IN关键字最主要的作用是表单子查询。

【例】查询xs表中不在1993年出生的学生情况。

SELECT 学号,姓名,专业名,出生日期

FROM xs

WHERE 出生日期 NOT BETWEEN'1993-1-1' AND '199312-31'

【例】查询xs表中专业名为“计算机”，“通信工程”或“无线电”的学生的情况。

SELECT *

FROM xs

WHERE 专业名 IN ('计算机','通信工程','无线电')

上面的IN语句等价于下面的OR语句

SELECT *

FROM xs

WHERE 专业名 = '计算机' OR专业名 = '通信工程' OR专业名 = '无线电'

3.1.3.4 空值比较

当需要判定一个表达式的值是否为空值时，使用ISNULL关键字为空；NOT NULL不为空，格式为

表达式 IS [NOT] NULL

【例】查询总学分不确定的学生情况。

SELECT *

FROM xs

WHERE 总学分 IS NULL

3.1.3.5 子查询

1) IN子查询

IN子查询用于进行一个给定值是否在子查询结果集中的判断。格式为：

表达式 [NOT] IN (子查询)

【例】查找选修了课程号为206的课程的学生的姓名，学号。

SELECT 姓名,学号

FROM xs

WHERE 学号 IN

(

SELECT学号

FROMxs_kc

WHERE课程号 = '206'

)

2) 比较子查询

这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式如下：

表达式{<|<=|=|>|>=|!=|<>} {ALL|SOME|NAY} (子查询)

ALL是与子查询结果集里的每一个值进行比较，都满足时返回true，否则false。

SOME和NAY是同义词，表示只要与子查询结果集里某个值满足比较关系就返回true，否则false。

【例】查找xs表中比所有计算机系的学生年龄都大的学生学号，姓名，出生日期。

SELECT 学号,姓名,出生日期

FROM xs

WHERE 出生日期 < ALL

(

SELECT出生日期

FROMxs

WHERE专业名 = '计算机'

)

3) EXISTS子查询

用于测试子查询的结果是否为空表，若子查询的结果不为空返回true，否则false。EXISTS与NOT结合使用，其返回值刚好相反。

[NOT] EXISTS (子查询)

【例】查找选修206号课程的学生姓名。

SELECT 姓名

FROM xs

WHERE EXISTS

(

SELECT*

FROMxs_kc

WHERE学号 = xs.学号 AND课程号 = '206'

)

子查询还可以用在FROM子句中，但必须为子查询产生的中间表定义一个别名。SELECT关键字后面也可用子查询，在WHERE子句中还可以将一行数据与子查询中的结果通过比较运算进行比较。

【例】FROM子句

select 姓名,学号,总学分

from( select 姓名,学号,性别,总学分

fromxs

where 总学分>50

) as student

where 性别 = '1'

【例】SELECT子句

select 学号,姓名,year(出生日期) – year(

(select出生日期

from xs

where 学号 = '081101')

) as 年龄差距

from xs

where 性别 = '0'

【例】WHERE子句

select 学号,姓名

from xs

wehre (性别,总学分) = (select性别,总学分

from xs

wehre 学号 = '081101')

3.1.4 GROUP BY子句

GROUP BY子句主要用于根据字段对行分组。

【例】将个专业名输出

SELECT 专业名

FROM xs

GROUP BY 专业名

使用带ROLLUP操作符的GROUP BY子句：指定在结果集内不仅包含由GROUP BY提供的正常行，还包含汇总行。

【例】SELECT 性别,专业名,COUNT(*) AS '人数'

FROM xs

GROUP BY 专业名,性别

WITH ROLLUP

3.1.5 HAVING子句

having子句的目的和where子句类似，having子句用来在group by子句后选择行。

【例】查找平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号,AVG(成绩) AS '平均成绩'

FROM xs_kc

GROUP BY 学号

HAVING AVG(成绩) >= 85

3.1.6 ORDER BY子句

order by子句是用来排序的，关键字ASC表示升序，DESC表示降序，系统默认为ASC升序。order by子句中可以包含子查询。orderby子句将空值作为最小值对待，故按升序排列时空值在最上方。

【例】将通信工程专业的学生按出生日期先后排序。

SELECT 学号,姓名,专业名,出生日期

FROM xs

WHERE 专业名 = '通信工程'

ORDER BY 出生日期

3.1.7 LIMIT子句

例如，LIMIT 5表示返回5行，LIMIT 3,5表示从第4行开始返回5行。

【例】查找xs表中从第4位同学开始的5位学生的信息。

SELECT 学号,姓名,专业名,出生日期

FROM xs

ORDER BY 学号

LIMIT 3,5

3.1.8 UNION子句

1. 使用union子句必须遵守以下原则：

2. 列于每个select语句的对应位置的被选择的列，应具有相同的数目和类型。例如，被第一个语句选择的第一列，应当和被其他语句选择的第一列具有相同的类型。

3. 只有最后一个select语句可以使用into outfile。

4. high_priority不能与作为union一部分的select语句同时使用。

5. order by 和limit子句只能在整个语句最后指定，同时还应对单个的select语句加圆括号。排序和限制行数对整个最终结果起作用。

【例】查找学号为081101和学号为081210的两位同学的信息。

SELECT 学号,姓名,专业名,性别,出生日期

FROM xs

WHERE 学号 = '081101'

UNION

SELECT 学号,姓名,专业名,性别,出生日期

FROM xs

WHERE 学号 = '081210'

3.1.9 HANDLER语句

handler语句能一行一行地浏览表中的数据。

1. 打开一个表

HANDLER 表名 OPEN

2. 浏览表中的行

HANDLER 表名 READ{FIRST|NEXT}

[WHERE 条件] [LIMIT…]

3. 关闭打开的表

HANDLER 表名 CLOSE

3.2 MySQL 视图

3.2.1 创建视图

使用视图时，注意下列事项：

1) 在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为“数据库名.视图名”。

2) 视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同的用户，即使是定义相同的视图，也必须使用不同的名字。

3) 不能把规则，默认值或触发器与视图关联。

4) 不能在视图上建立任何索引，包括全文索引。

语法格式：

CREATE

[OR REPLACE]

VIEW 视图名 [(列名…)]

AS SELECT 语句

OR REPLACE：替换已有的同名视图。

列名：视图的列名称，由逗号隔开。

【例】创建计算机专业学生的平均成绩视图xs_kc_avg，包括学号（在视图中的列名为unm）和平均成绩（在视图中列名为score_avg）

CREATE VIEW xs_kc_avg(num,score_avg)

SELECT 学号,AVG(成绩)

FROM xs_kc

GROUP BY 学号

3.2.2 查询视图

【例】查找平均成绩在80分以上的学生的学号和平均成绩。

SELECT *

FROM xs_kc_avg

WHERE score_avg >=80

3.2.3 更新视图

由于视图是一个虚拟表，所以更新视图（包括插入，修改和删除）数据也就等于在更新与其关联的基本表的数据。不是所有视图都可以更新，只有满足条件才能更新。

3.2.3.1 可更新视图

包含以下结构的视图不可以更新：

1) 聚合函数

2) DESTINCT关键字

3) GROUP BY子句

4) ORDER BY子句

5) HAVING子句

6) UNION运算符

7) 位于选择列表中的子查询

8) FROM子句中包含多个表

9) SELECT语句中引用了不可更新视图

10) WHERE子句中的子查询，引用FROM子句中的表

11) ALGORITHM选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）

3.2.3.2 插入数据

【例】创建视图cs_xs，视图中包含计算机专业的学生信息，并向cs_xs视图中插入一条记录。

创建视图：

CREATE OR REPLACE VIEW cs_xs

SELECT *

FROM xs

WHERE 专业名 = '计算机'

WITH CHECK OPTION

WITH CHECK OPTION子句会在更新数据的时候检查数据是否符合视图定义中WHERE子句的条件。WITH CHECK OPTION子句只能和可更新视图一起使用。

插入记录：

INSERT INTO cs_xs(学号,姓名,专业名,性别,出生日期)

VALUES('081255','李牧','计算机',1,'1994-10-14')

INSERT语句有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。

3.2.3.3 修改数据

通过视图修改基本表数据。

【例】将cs_xs视图中所有学生的总学分增加8分。

UPDATE cs_xs

SET 总学分 =总学分 + 8

3.2.3.4 删除数据

通过视图删除基本表数据。

【例】删除cs_xs中李牧同学（学号’081255’）的记录

DELETE FROM cs_xs

WHERE 学号 = '081255'

3.2.4 修改视图

语法：

ALTER VIEW 视图名 [(列 …)]

AS SELECT 语句

【例】将cs_xs视图修改为只包含计算机专业学生的学号，姓名和总学分三列。

ALTER VIEW cs_xs

SELECT学号,姓名,总学分

FROMxs

WHERE 专业名 = '计算机'

3.2.5 删除视图

语法：

DROP VIEW [IF EXISTS]

view_name [, view_name] …

[RESTRICT | CASCADE]

view_name：视图名

IF EXISTS判断视图是否存在

DROP VIEW 一次可删除多个视图。

【例】删除视图cs_xs

DROP VIEW cs_xs

4 MySQL索引与完整性约束

4.1 MySQL索引

4.1.1 索引作用及分类

索引是根据表中一列或若干列安装一定顺序建立的列值与记录行之间的对应关系表，它能加快查询速度。

索引分为BTREE索引和哈希索引（HASH），BTREE索引又分为：（括号内为该索引的关键字）

1) 普通索引（INDEX）：最基本的索引类型，没有唯一性。

2) 唯一性索引（UNIQUE）：索引列的所有值都只能出现一次，即必须是唯一的。

3) 主键（PRIMARY KEY）：主键是一种唯一性索引。

4) 全文索引（FULLTEXT）：只能在varchar或text类型的列上创建，并且只能在MyISAM表中创建。

4.1.2 创建索引

4.1.2.1 用CREATE INDEX语句创建

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名

[索引类型]

ON 表名 (索引列名…)

[索引选项] …

索引列名 =：

列名 [(长度)] [ASC|DESC]

UNIQUE：表示创建唯一性索引

FULLTEXT：表示创建全文索引

SPATIAL：表示为空间索引

索引名：索引在一个表中名称必须是唯一的

索引类型：支持的索引类型为BTREE索引和哈希索引（HASH）

索引可以按升序或降序排列，CREATE INDEX语句不能创建主键。

【例】根据xs表的学号列上的前5个字符建立一个升序索引xh_xs。

CREATE INDEX xh_xs

ON xs(学号(5) ASC)

可以在一个索引的定义中包含多个列，中间用逗号隔开，但它们属于同一个表，这样的索引叫复合索引。

【例】在xs_kc表的学号列和课程号列上建立一个复合索引xskc_in。

CREATE INDEX xskc_in

ON xs_kc(学号,课程号)

4.1.2.2 用ALTER TABLE语句创建

语法：

ALTER TABLE 表名

…

|ADD {INDEX|KEY} [索引名] /*添加索引 */

[索引类型] (索引列名 …) [索引选项] …

|ADD [CONSTRAINT [symbol]] PRIMARY KEY /*添加主键 */

[索引类型] (索引列名 …) [索引选项] …

|ADD [CONSTRAINT [symbol]]UNIQUE [INDEX|KEY][索引名]/*添加唯一性索引 */

[索引类型] (索引列名 …) [索引选项] …

|ADD FULLTEXT [INDEX|KEY] [索引名] /*添加全文索引 */

(索引列名…) [索引选项] …

索引类型：语法格式为USING{BTREE|HASH}

【例】在xs表的姓名列上创建一个非唯一的索引。

ALTER TABLE xs

ADD INDEX xs_xm USING BTREE (姓名)

【例】创建复合索引。

ALTER TABLE xs

ADD INDEX mark(出生日期,性别)

4.1.2.3 查看索引

SHOW INDEX FROM xs

4.1.2.4 在建立表时创建索引

【例】创建成绩（cj）表，学号和课程号的联合主键，并在成绩列上创建索引。

CREATE TABLE xs_kc

(

学号 CHAR(6) NOT NULL,

课程号 CHAR(3) NOT NULL,

成绩 TINYINT (1),

学分 TINYINT (1),

PRIMARYKEY (学号,课程号),

INDEXcj(成绩)

)

4.1.2.5 删除索引

1) 使用DROP INDEX删除

语法：

DROP INDEX 索引名 ON表名

2) 使用ALTER TABLE删除

语法：

ALTER TABLE 表名

…

|DROP PRIMARY KEY /* 删除主键 */

|DROP {INDEX|KEY} 索引名 /*删除索引 */

|DROP FOREIGN KEY fk_symbol /* 删除外键 */

【例】删除xs表上的mark索引。

ALTER TABLE xs

DROP INDEX mark

4.2 MySQL数据完整性约束

4.2.1 主键约束

主键是唯一的，且不能为空，建议使用无意义的列表示主键。

【例】创建表xsl，将姓名定义为主键。

CREATE TABLE xsl

(

学号 VARCHAR(6) NULL,

姓名 VARCHAR(8) NOT NULLPRIMARY KEY

)

4.2.2 替代键约束

1) 替代键是没有被选作主键的候选键。关键字是UNIQUE。

2) 一个表可以有若干个替代键。

3) 替代键可以取空值，但是必须使用NULL或NOT NULL声明。

4) 创建UNIQUE约束时，系统自动产生UNIQUE索引。

5) UNIQUE KEY列上不允许出现重复值。

【例】在表xsl中将姓名列定义为一个替代键。

create table xsl

(

学号 varchar(6) null,

姓名 varchar(8) not nullunique,

primarykey (学号)

)

4.2.3 外键约束

工作中不建议使用外键，语法格式为：

REFERENCES 表名 [(索引列名…)]

[ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}]

[ON UPDATE {RESTRICT|CASCADE|SET NULL|NOACTION}]

4.2.4 CHECK完整性约束

【例】创建表student，只包括学号和性别两列，性别只能是男或女。

CREATE TABLE student

(

学号 CHAR(6) NOT NULL,

性别 CHAR(1) NOT NULL

CHECK(性别 IN ('男','女'))

)

4.2.5 命名完整性约束

CONSTRAINT关键字用来指定完整性约束的名字，语法：

CONSTRAINT [指定的名字]

4.2.6 删除完整性约束

删除一个表，所有的完整性约束就都自动被删除了。

【例】删除创建的表xsl的主键。

ALTER TABLE xsl DROP PRIMARY KEY

5 MySQL语言结构

5.1 变量

5.1.1 创建变量

变量名前面必须加符号@。

【例】创建用户变量name，并赋值为“王林”，user1赋值为1。

SET @name = '王林',@user1 = 1

5.1.2 查询变量

【例】查询变量name的值。

SELECT @name

5.1.3 系统变量

1. 获取现在使用的MySQL版本

SELECT @@VERSION

2. 获得系统当前时间

SELECT CURRENT_TIME

3. 查看系统变量清单

SHOW VARIABLES

4. 查看所有全局变量

SHOW GLOBAL VARIABLES

5. 查看所有会话系统变量

SHOW SESSION VARIABLES

6. 查看名称与样式匹配的变量的清单，用通配符“%”。

SHOW VARIABLES LIKE 'character%'

5.2 运算符与表达式

5.2.1 算数运算符

算数运算符有：+（加），-（减），*（乘），/（除）和%（求模）5种。

5.2.2 比较运算符

比较运算符有：=，<> ，<=，>=，< 和 >。

5.2.3 逻辑运算符

逻辑运算符有：NOT或！（逻辑非），AND或&&（逻辑与），OR或||（逻辑或），XOR（逻辑异或）

5.2.4 位运算符

位运算符有：&（位AND），|（位OR），^（位XOR），~（位取反），>>（位右移），<<（位左移）

5.3 系统内置函数

5.3.1 数学函数

5.3.1.1 GREATEST()和LEAST()函数

GREATEST：获取一组数中的最大值；LEAST：获取一组数中的最小值。他们可以嵌套使用，例如：

SELECTGREATEST(-2,LEAST(0,3)),LEAST(1,GREATEST(1,2))

显示结果如图：

5.3.1.2 FLOOR()函数和CEILING()函数

FLOOR()函数用于获得小于一个数的最大整数值，CEILING()函数用于获得大于一个数的最小整数值，例如：

SELECTFLOOR(-1.2),CEILING(-1.2),FLOOR(9.9),CEILING(9.9)

显示结果如图：

5.3.1.3 ROUND()函数和TRUNCATE()函数

ROUND()函数用于获得一个数的四舍五入的整数值。

SELECT ROUND(5.1),ROUND(25.501),ROUND(9.8)

TRUNCATE()函数用于把一个数字截取为一个指定小数个数的数字，逗号后面的数字表示指定小数的个数：

SELECT TRUNCATE(1.54578,2),TRUNCATE(-76.12,5)

5.3.1.4 ABS()函数

ABS()函数用来获得一个数的绝对值，例如：

SELECT ABS(-878),ABS(-8.345)

5.3.1.5 SIGN()函数

SIGN()函数返回数字的符号，返回的结果是正数（1），负数（-1）或零（0）：

SELECT SIGN(-2),SIGN(2),SIGN(0)

5.3.1.6 SQRT()函数

SQRT()函数返回一个数的平方根：

SELECT SQRT(25),SQRT(15),SQRT(1)

5.3.1.7 POW()函数

POW()函数以一个数作为另外一个数的指数，并返回结果：

SELECT POW(2,2),POW(10,2),POW(0,3)

5.3.1.8 SIN()，COS()，TAN()函数

SIN()，COS()，TAN()函数返回一个角度的正弦，余弦和正切值：

SELECT SIN(1),COS(1),TAN(RADIANS(45))

ASIN()反正弦函数，ACOS()反余弦函数，ATAN()反正切值函数。

BIN()二进制函数，OTC()八进制函数，HEX()十六进制函数。

5.3.2 聚合函数

包括求一个和，平均，频次等，而不是单个的值。（如COUNT和SUM）

5.3.3 字符串函数

5.3.3.1 ASCII()函数

【例】返回字母A的ASCII码值

SELECT ASCII('A')

5.3.3.2 CHAR()函数

【例】返回ASCII码值为65,66,67的字符串，组成一个字符串。

SELECT CHAR(65,66,67)

5.3.3.3 LEFT和RIGHT函数

语法：

LEFT|RIGHT(str,x)

返回从字符串str左边和右边开始指定x个字符。

SELECT LEFT(课程名,3)

FROM kc

5.3.3.4 TRIM,LTRIM和RTRIM函数

语法:

TRIM|LTRIM|RTRIM(str)

TRIM：删除字符串首部和尾部的所有空格。

LTRIM：删除字符串前面的空格。

RTRIM：删除字符串尾部空格。

以上返回值为字符串，参数str为字符型表达式，返回值类型为varchar。

SELECT TRIM(' MySQL ')

5.3.3.5 RPAD和LPAD函数

语法格式：

RPAD|LPAD(str,n,pad)

使用RPAD和LPAD分别用字符串pad对字符串str的右边和左边进行填补，直至str中字符数目达到n个，最后返回填补后的字符串。若str中的字符个数大于n，则返回str的前n个字符。

SELECT RPAD('中国梦',8,'！'),LPAD('welcom',10,'*')

5.3.3.6 REPLACE函数

语法：

REPLACE(str1,str2,str3)

REPLACE函数用于用字符串str3替换str1中出现的字符串str2，最后返回替换后的字符串。

SELECT REPLACE('welcome to CHINA','o','K')

5.3.3.7 CONCAT函数

语法：

CONCAT(s1,s2,…,sn)

CONCAT函数用于连接指定的几个字符串。

SELECT CONCAT('中国梦','我的梦')

5.3.3.8 SUBSTRING函数

语法：

SUBSTRING (expression,Start,Lengtl)

该函数返回expression中指定的部分数据，参数expression可为字符串，二进制串，text，image字段或表达式。Start，Length均为整形，前者指定子串的开始位置，后者指定子串的长度（要返回字节数）。如果expression是字符类型和二进制类型，则返回值类型与expression的类型相同。如果为text类型，则返回的是varchar类型。

【例】在一列中返回xs表中所有女同学的姓氏，在另一列中返回名字。

SELECT SUBSTRING(姓名, 1, 1) AS性,SUBSTRING(姓名,2,LENGTH(姓名)-1) AS 名

FROM xs

WHERE 性别 = 0

ORDER BY 姓名

5.3.3.9 STRCMP函数

STRCMP函数用于比较两个字符串，相等返回0，s1大于s2返回1，s1小于s2返回-1.

语法：

STRCMP(s1,s2)

【例】SELECTSTRCMP('A','A'),STRCMP('ABC','OPQ'),STRCMP('T','B')

5.3.4 日期和时间函数

5.3.4.1 NOW()

获得当前日期和时间，格式为YYYY-MM-DDHH:MM:SS

SELECT NOW()

5.3.4.2 CURTIME()和CURDATE

CURTIME：返回当前时间，没有参数

CURDATE:返回当前日期，没有参数

SELECT CURTIME(),CURDATE()

5.3.4.3 YEAR()

YEAR：分析日期值并返回其中关于年的部份。

SELECT YEAR(20080512142800),YEAR('1982-11-02')

5.3.4.4 MOTNTH()和MONTHNAME()

MOTNTH()和MONTHNAME()函数分别以数值和字符串的格式返回月的部分：

SELECTMONTH(20080512142800),MONTHNAME('1982-11-02')

5.3.4.5 DAYOFYEAR(),DAYOFWEEK()和DAYOFMONTH()

DAYOFYEAR(),DAYOFWEEK()和DAYOFMONTH()函数分别返回这一天在一年，一星期及一个月中的序数：

SELECTDAYOFYEAR(20080512),DAYOFMONTH('2008-05-12'),DAYOFWEEK(20080512)

5.3.4.6 DAYNAME()

DAYNAME()以字符形式返回星期名：

SELECT DAYNAME('2008-06-01')

5.3.4.7 WEEK()和YEARWEEK()

WEEK：返回指定的日期是一年的第几个星期

YEARWEEK：返回指定的日期是哪一年的哪一个星期。

SELECT WEEK('2008-05-01'),YEARWEEK(20080501)

5.3.4.8 HOUR(),MINUTE()和SECOND()

HOUR(),MINUTE()和SECOND()函数分别返回时间值的小时，分钟和秒的部分：

SELECTHOUR(155300),MINUTE('15:53:00'),SECOND(143415)

5.3.4.9 DATE_ADD()和DATE_SUB()

DATE_ADD()和DATE_SUB()函数可以对日期和时间进行算数操作，它们分别用来增加和减少日期值。

语法：

DATE_ADD|DATE_SUB(date,INTERVAL int keyword)

1) date：需要的日期和时间

2) INTERVAL：表示一个时间间隔

3) int：表示需要计算的时间值

4) keyword：表示需要用到的日期或时间关键字

DATE_ADD：计算date加上间隔时间后的值

DATE_SUB：计算date减去时间间隔后的值

SELECT DATE_ADD('2014-08-08', INTERVAL 17 DAY)

SELECT DATE_SUB('2014-08-08 10:25:35', INTERVAL 20MINUTE)

5.3.5 加密函数

5.3.5.1 AES_ENCRYPT和AES_DECRYPT函数

语法：

AES_ENCRYPT | AES_DECRYPT(str,key)

1) AES_ENCRYPT：加密的

2) AES_DECRYPT：解密的

5.3.5.2 ENCODE和DECODE函数

不介绍

5.3.5.3 ENCRYPT函数

不介绍

5.3.5.4 PASSWORD函数

语法：

PASSWORD(str)

该加密过程不可逆。

【例】返回字符串“MySQL”的加密版本

SELECT PASSWORD('MySQL')

5.3.6 控制流函数

5.3.6.1 IFNULL和NULLIF函数

IFNULL函数的语法：

IFNULL(expr1,expr2)

IFNULL：判断参数expr1是否为NULL，当参数expr1为NULL时返回expr2，不为NULL时返回expr1。IFNULL的返回值是数字或字符串。

【例】SELECTIFNULL(1,2),IFNULL(NULL,'MySQL'),IFNULL(1/0,10)

NULLIF函数的语法：

NULLIF：检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等就返回第一个参数

【例】SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,3+4)

5.3.6.2 IF函数

语法：

IF(expr1,expr2,expr3)

这个函数有3个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数；如果为假，IF()将会返回第三个参数。

【例】判断2*4是否大于9-5，是则返回“是”，否则返回“否”。

SELECT IF(2*4 > 9-5,'是','否')

IF()函数在只有两种可能结果时才适合使用。

5.3.7 格式化函数

5.3.7.1 FORMAT()函数

语法：

FORMAT(x,y)

FORMAT()函数把数值格式化为以逗号间隔的数字序列。FORMAT()的第一个参数x是被格式化的数据，第二个参数y是结果的小数位数。

【例】SELECTFORMAT(11111111111.23654,2),FORMAT(-5468,4)

5.3.7.2 DATE_FORMAT()和TIME_FORMAT()函数

DATE_FORMAT()和TIME_FORMAT()函数可以用来格式化日期和时间值。

【例】SELECTDATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')

注意：这两个函数区分大小写。

5.3.7.3 INET_NTOA()和INET_ATON()函数

INET_NTOA()和INET_ATON()函数可以把IP地址转换为数字或者进行相反的操作。

【例】SELECTINET_ATON('192.168.1.1')

5.3.8 类型转换函数

5.3.8.1 CAST函数

CAST函数用于类型转换。语法：

CAST(expr,AS type)

【例】将当前日期显示成数值形式。

SELECT CAST(CURDATE() AS SIGNED)

5.3.9 系统信息函数

5.3.9.1 DATABASE(),USER()和VERSION

DATABASE(),USER()和VERSION函数可以分别返回当前所选数据库，当前用户和MySQL版本信息。

SELECT DATABASE(),USER(),VERSION()

5.3.9.2 BENCHMARK()函数

BENCHMARK()函数用于重复执行n次表达式expr。它可以用于计算MySQL数量表达式的速度。

SELECTBENCHMARK(10000000,ENCODE('hello','goodbye'))

计算表达式10000000次用了

5.3.9.3 FOUND_ROWS()函数

FOUND_ROWS()函数显示在没有LIMIT子句的情况下，SELECT语句所返回的行数。

6 MySQL过程式数据库对象

6.1 存储过程

在MySQL中，可以定义一段程序存放在数据库中，这样的程序称为的存储过程。

6.1.1 创建存储过程

语法：

CREATE PROCEDURE 存储过程名 ([参数 …])

[特征 …]主体

6.1.1.1 存储过程参数

[IN|OUT|INOUT] 参数名参数类型

在特定数据库中创建存储过程时，要在名称前面加上数据库的名称。

格式为：数据库名.存储过程名

存储过程有多个参数时中间用逗号分隔。MySQL存储过程支持三种类型的参数：

IN：输入参数使数据库可以传递给一个存储过程。

OUT：当需要返回一个答案或结果的时候，存储过程使用输出参数。

INOUT：输入/输出参数既可以充当输入参数也可以充当输出参数。

注意：存储过程可以有0个，1个或多个参数。存储过程不加参数，但是名称后面的括号是不可省略的。参数的名字不要采用列的名字，否则虽然不会返回出错消息，但是存储过程中的SQL语句会将参数名看作列名，从而引发不可预知的结果。

6.1.1.2 存储过程主体

存储过程主体称为存储过程体。这部分总是以BEGIN开始，以END结束。当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。MySQL中，服务器处理语句时以分号为结束标志。创建存储工程时，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以使用“DELIMITER结束符号”命令将MySQL语句的结束标志修改为其他符号。最后再使用“OPDELIMITER;”恢复以分号为结束标志。

【例】用存储过程实现删除一个特定学生的信息。

DELIMITER $$

CREATE PROCEDURE delete_student (IN xh CHAR(6))

BEGIN

DELETE FROMxs WHERE 学号 = xh;

END $$

DELIMITER;

说明：调用存储过程的命令是CALL命令，后面讲。

6.1.2 存储过程体

6.1.2.1 局部变量

声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。如果不指定默认为NULL。

语法：

DECLARE 变量名 …类型 [默认值]

例如，声明一个整形变量和两个字符变量。

declare nu mint(4);

declare str1,str2varchar(6);

说明：局部变量只能在BEGIN-END语句块中声明，而且必须在存储过程的开头。声明完后，可以在声明它的BEGIM-END语句块中使用该变量，其他语句块中不可以使用它。

局部变量和用户变量区别：局部变量前面没有使用@符号，局部变量在BEGIN-END语句块处理完后就消失了，而用户变量存在于整个会话当中。

6.1.2.2 使用SET语句赋值

语法：

SET 变量名=表达式 [,变量名=表达式] …

例如，在存储过程中给局部变量赋值。

set unm=1, str1=’hello’;

6.1.2.3 SELECT … INTO语句

SELECT … INTO语句可以把选定的列值直接存储到变量中。因此，返回结果只能有一行。

语法：

SELECT 列名[, …] INTO变量名 [, …] table_expr

table_expr是SELECT语句中的FROM子句后面的部分。

例如，在存储过程体中，将xs表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。

select 姓名,专业名 intoname,project

from xs;

where 学号=’081101’;

说明：该语句只能在存储过程体中使用。变量name和project需要在之前经过声明。通过该语句赋值的变量可以在语句块的其他语句中使用。

6.1.2.4 流程控制语句

常见的过程式SQL语句可以用在一个存储过程体中。例如IF语句，CASE语句，LOOP语句，WHILE语句，ITERATE语句和LEAVE语句。

6.1.2.4.1 IF语句

IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

语法：

IF 条件 THEN语句

[ELSEIF 条件 THEN语句] …

[ELSE 语句]

END IF

【例】创建xscj数据库的存储过程，判断两个输入的参数哪一个更大。

delimiter $$

CREATE PROCEDURE xscj.compar

(IN k1 INTEGER,IN k2 INTEGER,OUT k3 CHAR(6))

BEGIN

IFk1>k2 THEN

SETk3='大于';

ELSEIFk1=k2 THEN

SETk3='等于';

ELSE

SETk3='小于';

ENDIF;

END $$

delimiter;

说明：delimiter $$为修改结束符号，可省略。

创建名为xscj.compar的存储过程，设输入参数k1和k2，输出参数k3。在存储过程体中，判断如果k1>k2，用set给k3赋值为大于，以此类推。

6.1.2.4.2 CASE语句

语法：

CASE case_value

WHEN when_value THEN 语句

[WHEN when_value THEN 语句] …

[ELSE 语句]

END CASE

或

CASE

WHEN 条件 THEN语句

[WHEN 条件 THEN语句] …

[ELSE 语句]

END CASE

【例】创建一个存储过程，针对参数的不同，返回不同的结果。

CREATE PROCEDURE xscj.result

(IN str VARCHAR(4),OUT sex VARCHAR(4))

BEGIN

CASEstr

WHEN'm' THEN SET sex = '男';

WHEN'f' THEN SET sex = '女';

ELSESET sex = '无';

ENDCASE;

END

或第二种格式

CREATE PROCEDURE xscj.result1

(IN str VARCHAR(4),OUT sex VARCHAR(4))

BEGIN

CASE

WHENstr = 'm' THEN SET sex = '男';

WHENstr = 'f' THEN SET sex = '女';

ELSESET sex = '无';

ENDCASE;

END

6.1.2.4.3 循环语句

MySQL支持3条循环语句：WHILE，REPEAT和LOOP语句，在存储过程中可以定义0个，1个或多个循环语句。

1. WHILE语句语法：

[begin_label:]

WHILE 条件 DO

语句

END WHILE [end_label]

begin_label和end_label是WHILE语句的标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，他们的名字必须是相同的。

【例】创建一个带WHILE循环的存储过程。

CREATE PROCEDURE dowhile()

BEGIN

DECLAREv1 INT DEFAULT 5;

WHILEv1>0 DO

SETv1 = v1-1;

ENDWHILE;

END

2. REPEAT语句语法：

[begin_label:]

REPEAT

语句

UNTIL 条件

END REPEAT [end_label]

【例】用REPEAT语句创建一个如WHILE语句例子的存储过程，程序片段如下。

repeat

v1=v1-1;

until v1<1;

end repeat;

REPEAT语句和WHILE语句的区别：REPEAT语句先执行语句，后进行判断，而WHILE语句是先判断，条件为真时才执行语句。

3. LOOP语句语法：

[begin_label:]

LOOP

语句

END LOOP [end_label]

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE语句。LEAVE语句经常和BEGIN…END或循环一起使用，结构如下：

LEAVE label

label是语句中标注的名字，这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

【例】创建一个带LOOP语句的存储过程。

CREATE PROCEDURE doloop()

BEGIN

SET@a=10;

label:LOOP

SET@a=@a-1;

IF@a<0 THEN

LEAVElabel;

ENDIF;

ENDLOOP label;

END

说明：语句中，首先定义了一个用户变量并赋值为10，接着进入LOOP循环，标注为Label，执行减1语句，然后判断用户变量a是否小于零，是则使用LEAVE语句跳出循环。

调用此存储过程来查看最后结果。调用该存储过程使用如下命令：

call doloop();

接着，查看用户变量的值：

SELECT @a;

循环语句中还有一个ITERATE语句，它只可以出现在LOOP，REPEAT和WHILE语句内，意味“再次循环”。它的格式为：

ITERATE label

LEAVE语句是离开一个循环，而ETERATE语句是重新开始一个循环。

6.1.2.4.4 处理程序和条件

每一个错误消息都有一个唯一代码和一个SQLSTATE代码，例如，SQLSTATE23000属于如下的出错代码：

Error 1022, “Can’t write;duplicate key intable”

Error 1048, ”Column cannot be null”

Error1052, “Column is ambiguous”

Error1062, “Duplicate entry for key”

错误处理DECLARE HANDLER语句语法：

DECLARE 处理程序的类型 HANDLER FORcondition_value[, …]存储过程语句

1) 处理程序类型有三种：CONTINUE，EXIT和UNDO。CONTINUE：MySQL不中断存储过程的处理。EXIT：当前BEGIN…END复合语句的执行被终止。UNDO暂不支持。

2) condition_value

condition_value：

SQLSTATE[VALUE] sqlstate_value

|condition_name

|SQLWARNING

|NOT FOUND

|SQLEXCEPTION

|mysql_error_code

condition_value给出SQLSTATE的代码表示。

condition_name是处理条件的名称。

SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOTFOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值外，MySQL错误代码也被支持，表示的形式为：ERROR=’xxxx’。

3) 存储过程语句

存储过程语句是处理程序激活时将要执行的动作。

【例】创建一个存储过程，向xs表插入一行数据（‘081101’，‘王民’，‘计算机’，1，‘1994-02-10’，50，NULL，NULL），已知学号081101在XS表中已存在。如果出现错误，则程序继续进行。

CREATE PROCEDURE my_insert()

BEGIN

DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

set @x=2;

INSERT INTO xs VALUES('081101','王民','计算机',1,'1994-02-10',50,null,null);

SET @x=3;

END

调用存储过程查看结果的语法格式为：

CALL my_insert();

SELECT @x;

说明：在调用存储过程后，未遇到错误消息时处理程序未被激活，当执行INSERT语句出现错误消息时，MySQL检查是否为这个错误代码定义了处理程序。如果有，则激活该处理程序，本例中，INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附加语句（SET @x2=1）。此后，MySQL检查处理程序的类型，这里的类型为CONTINUE，因此存储过程继续处理，将用户变量x赋值为3。如果这里的INSERT语句能够执行，处理程序将不被激活，用户变量x2将不被赋值。

注意：不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。

为了提高可读性，可以使用DECLARECONDITION语句为一个SQLSTATE或出错代码定义一个名字，并且可以在处理程序中使用这个名字。

DECLARE CONDITION语法格式为：

DECLARE condition_name CONDITION FORcondition_value

condition_name：是处理条件的名称。

condition_value：为要定义别名的SQLSTATE或错误代码。

【例】修改前例中的存储过程，将SQLSTATE‘23000’ 定义成NON_UNIQUE，并在处理程序中使用这个名称，程序片段为：

CREATE PROCEDURE my_insert1()

BEGIN

DECLAREnon_unique CONDITION FOR SQLSTATE '23000';

DECLARECONTINUE HANDLER FOR non_unique SET @x2=1;

set@x=2;

INSERTINTO xs VALUES('081102','王民2','计算机',1,'1994-02-10',50,null,null);

SET@x=3;

END

和前一个例子相同，只替换begin-end语句块中的内容。

6.1.2.4.5 游标

使用游标需要用到4个特殊语句：DECLARECURSOR（声明游标），OPEN CURSOR（打开游标），FETCH CURSOR（读取游标）和CLOSE CURSOR（关闭游标）。

1) 声明游标

语法：

DECLARE 游标名 CURSOR FORselect语句

说明：这个语句声明一个游标，也可以在存储过程中定义多个游标。但是一个块中的每一个游标必须有唯一的名字。

注意：这里的SELECT语句不能有INTO子句。

下面的定义符合一个游标的声明：

declare xs_curl cursor for

select学号,姓名,出生日期,总学分

fromxs

where 专业名=’计算机’;

注意：游标只能在存储过程或存储函数中使用，例中语句无法单独运行。

2) 打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。

OPEN 游标名 e

在程序中，一个游标可以打开多次，由于其他的用户或程序可能在其间已经更新了表，所以每次打开的结果可能不同。

3) 读取数据

语法：

FETCH 游标名 INTO变量名 …

说明：FETCH…INTO语句与SELECT…INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。变量名指定是存放数据的变量。

4) 关闭游标

游标使用完以后，要及时关闭。

CLOSE 游标名

语句参数的含义与OPEN语句中相同。例如关闭游标xs_cur2：

CLOSExs_cur2

【例】创建一个存储过程，计算xs表中行的数目。

CREATE PROCEDURE compute(OUT number INTEGER)

BEGIN

DECLARExh CHAR(6);

DECLAREfound boolean DEFAULT TRUE;

DECLAREnumber_xs CURSOR FOR

SELECT学号 FROM xs;

DECLARECONTINUE HANDLER FOR NOT found

SETfound=FALSE;

SETnumber=0;

OPENnumber_xs;

FETCHnumber_xs INTO xh;

WHILEfound DO

SETnumber=number+1;

FETCHnumber_xs INTO xh;

ENDWHILE;

CLOSEnumber_xs;

END

调用此存储过程并查看结果：

CALL compute(@num);

SELECT @num;

查看存储过程使用：SHOWPROCEDURE STATUS命令。

查看存储过程具体信息使用：SHOWCREATE PROCEDURE存储过程命令。

6.1.3 存储过程的调用，删除和修改

6.1.3.1 存储过程的调用

存储过程创建完后，可以在程序，触发器或者其他存储过程中被调用。CALL语句格式：

CALL 存储过程名 ([参数…])

说明：如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。另外，语句中的参数个数必须总是等于存储过程的参数个数。

【例】创建存储过程，实现查询xs表中学生人数的功能，该存储过程不带参数。

CREATE PROCEDURE do_query()

SELECTCOUNT(*) FROM xs ORDER BY 学号;

调用该存储过程：

CALL do_query();

【例】创建xscj数据库的存储过程，判断两个输入的参数哪一个更大，调用该存储过程。

（1）创建存储过程。

CREATE PROCEDURExscj.compar1

(in k1 INTEGER,in k2 INTEGER,OUT k3 CHAR(6))

BEGIN

IF k1>k2 THEN

SET k3='大于';

ELSEIF k1=k2 THEN

SET k3='等于';

ELSE

SET k3='小于';

END IF;

END

（2）调用存储过程。

CALLcompar1(3,6,@k);

SELECT @k;

【例】创建一个存储过程，有两个输入参数：xh和kcm，要求当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分。

CREATE PROCEDURE xscj.do_update(IN xh CHAR(6),INkcm CHAR(16))

BEGIN

DECLAREkch CHAR(3);

DECLARExf TINYINT;

DECLAREcj TINYINT;

SELECT课程号,学分 INTO kch,xf FROM kc WHERE课程名=kcm;

SELECT成绩 INTO cj FROM xs_kc WHERE学号=xh AND课程号=kch;

IFcj<60 THEN

UPDATExs_kc SET 学分=0 WHERE学号=xh AND课程号=kch;

ELSE

UPDATExs_kc SET 学分=xf WHERE学号=xh AND课程号=kch;

ENDIF;

END

接下来向xs_kc表中输入一行数据：

INSERT INTO xs_kc VALUES('081101','208',50,10,0);

然后，再调用存储过程并查询调用结果：

CALL do_update('081101','数据结构');

SELECT* FROM xs_kc WHERE 学号='081101' AND课程号='208'

成绩小于60时，学分被修改为0。

【例】创建一个存储过程do_insert1，作用是向xs表中插入一行数据；在创建另外一个存储过程do_insert2，在其中调用第一个存储过程，并根据条件处理该行数据。

创建第一个存储过程：

CREATE PROCEDURE xscj.do_insert1()

insertINTO xs VALUES('091101','陶伟','软件工程',1,'1994-03-05',50,null,null)

创建第二个存储过程：

CREATE PROCEDURE xscj.do_insert2(IN x bit(1),OUTstr char(8))

BEGIN

CALLdo_insert1();

IF x=0THEN

UPDATExs SET 姓名='刘英',性别=0 WHERE 学号='091101';

SETstr='修改成功';

ELSEIFx=1 THEN

DELETEFROM xs WHERE 学号='091101';

SETstr='删除成功';

ENDIF;

END

接下来调用存储过程do_insert2来查看结果：

CALL do_insert2(1,@str);

SELECT @str;

CALL do_insert2(0,@str);

SELECT @str;

6.1.3.2 存储过程的删除

创建存储过程后在需要删除时应使用DROPPROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

语法格式为：

DROP PROCEDURE (IF EXISTS) 存储过程名

说明：存储过程名是要删除的存储过程的名称。IFEXISTS子句是MySQL的扩展，它用于防止在程序或函数不存在时发生错误。

例如，删除存储过程dowhile。

DROP PROCEDURE IF EXISTS dowhile;

6.1.3.3 存储过程的修改

语法：

ALTER PROCEDURE 存储过程名 [特征 …]

特征：

(CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIESSQL DATA)

|SQL SECURITY (DEFINER|INVOKER)

|COMMENT ‘string’

如果要修改存储过程的内容，可以使用先删除在重新定义存储过程的方法。

【例】使用先删除后修改的方法修改存储过程。

DROP PROCEDURE IF EXISTS do_query;

CREATE PROCEDURE do_query()

BEGIN

SELECT* FROM xs;

END

完成后可调用：

CALL do_query();

会发现，该存储过程的作用由原先的只查询xs表学生人数，扩展为查询整个xs表全表学生的信息。

6.2 存储函数

存储函数和存储过程的区别：

（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；

（2）不能用CALL语句来调用存储函数；

（3）存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含在存储过程中。

6.2.1 创建存储函数

语法：

CREATE FUNCTION 存储过程名 ([参数 …])

RETURNStype

[特征…]主体

说明：

（1）存储函数不能拥有与存储过程相同的名字。

（2）存储函数的参数只有名称和类型，不能指定IN，OUT和INOUT。RETURNS type子句声明函数返回值的数据类型。

（3）主体也叫存储函数体，所有在存储过程中使用的SQL语句在存储函数中也适用，包括流程控制语句，游标等。但是存储函数体中必须包含一个RETURN value语句，value为存储函数的返回值。这是存储过程体中没有的。

（4）查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS命令。

【例】创建一个存储函数，它返回xs表中学生的数目作为结果。

CREATE FUNCTION unm_of_xs()

RETURNS INTEGER

BEGIN

RETURN(SELECT COUNT(*) FROM xs);

END

说明：RETURN子句中包含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。

【例】创建一个存储函数，返回某个学生的姓名。

CREATE FUNCTION name_of_stu(xh CHAR(6))

RETURNS CHAR(8)

BEGIN

RETURN(SELECT 姓名 FROM xs WHERE学号=xh);

END

【例】创建一个存储函数来删除xs_kc表中存在，但xs表中不存在的学号。

CREATE FUNCTION delete_stu(xh CHAR(6))

RETURNS boolean

BEGIN

DECLAREstu CHAR(6);

SELECT姓名 INTO stu FROM xs WHERE学号=xh;

IF stuIS NULL THEN

DELETEfrom xs_kc WHERE 学号=xh;

RETURNTRUE;

ELSE

RETURNFALSE;

ENDIF;

END

说明：如果调用存储函数时，参数中的学号在xs表中不存在，那么将删除xs_ck表中所有与该学号相关的行，之后返回1。如果学号在xs中存在则直接返回零。

6.2.2 存储函数的调用，删除和修改

6.2.2.1 存储函数的调用

语法：

SELECT 存储函数名 ([参数[, …]])

例如，无参数调用存储函数。命令如下：

SELECT num_of_xs();

例如，有参数调用存储函数，命令如下：

SELECT name_of_stu('081102')

存储函数中还可以调用另外一个存储函数或者存储过程。

【例】创建一个存储函数，通过调用存储函数NAME_OF_STU获得学号姓名，判断姓名是否是“王林”，是则返回王林的出身日期，不是则返回FALSE。

CREATE FUNCTION is_stu(xh CHAR(6))

RETURNSCHAR(10)

BEGIN

DECLAREname CHAR(8);

SELECTname_of_stu(xh) INTO name;

IFname='王林' THEN

RETURN(SELECT出生日期 FROM xs WHERE学号=xh);

ELSE

RETURN'false';

ENDIF;

END

接着调用存储函数is_stu查看结果；

SELECT is_stu('081101');

SELECT is_stu('081102');

6.2.2.2 删除存储函数

语法：

DROP FUNCTION [IF EXISTS] 存储过程名

例如，删除存储函数num_of_xs。

DROP FUNCTION IF EXISTS num_of_xs;

完成使用SHOW FUNCTION STATUS查看，已经没有这个函数了。

6.2.2.3 修改存储函数

语法：

ALTER FUNCTION 存储过程名 [特征 …]

说明：要修改存储函数的内容则要采用先删除后定义的方法。

6.3 触发器

触发器是一个被指定关联到一个表的数据库对象，触发器是不需要调用的，当对一个表的特别事件出现是，它就会被激活。

6.3.1 创建触发器

语法：

CREATE TRIGGER 触发器名触发时刻触发事件

ON表名 FOR EACH ROW触发器动作

查看触发器：

SHOW TRIGGERS

说明：

触发器名称在当前数据库中必须唯一。如果要在某个特定数据库中创建，名称前面应该加上数据库名称。

触发时刻：有两个选项，AFTER和BEFORE。在激活触发器的语句执行之后执行几个或更多的改变，用AFTER。如果想要验证新数据是否满足使用的限制，使用BEFORE。

触发事件：指激活触发程序的语句类型。可以是INSERT，UPDATE，DELETE。

FOR EACH ROW：这个声明用来指定对于受触发事件影响的每一行，都要激活触发器的动作。

表名：表示在该表上发生触发事件才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。

触发器动作：包含触发器激活时将要执行的语句。如果要执行多个语句，可使用BEGIN … END复合语句结构。这样，就能使用存储过程中允许的相同语句。

注意：触发器不能返回任何结果到客户端，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句。同样，也不能调用将数据返回客户端的存储过程。

【例】创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将用户变量str的值设为“trigger is working”

CREATE TABLE table1(a INTEGER);

CREATE TRIGGER table1_insert AFTER INSERT

ONtable1 FOR EACH ROW

SET@str='trigger is working'

向table1中插入一行数据：

INSERT INTO table1 VALUES(10)

查看str的值：

SELECT @str;

6.3.1.1 在触发器中关联表中的列

在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称作为标志，必须用这样的语法来标志：NEW.column_name或者OLD.column_name。NEW.column_name用来引用新行的一列，OLD.column_name用来引用更新或删除它之前的已有行的一列。

对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以与NEW或OLD同时使用。

【例】创建一个触发器，当删除表xs中某个学生的信息时，同时将xs_kc表中与该学生有关的数据全部删除。

CREATE TRIGGER xs_delete AFTER DELETE

ON xsFOR EACH ROW

BEGIN

DELETEFROM xs_kc WHERE 学号=old.学号;

END

验证触发器的功能：

DELETE FROM xs WHERE 学号='081101';

用SELECT语句查看xs_kc表中的情况：

SELECT * FROM xs_kc;

【例】创建一个触发器，当修改xs_kc表中的数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0分，否则将学分改成对应课程的学分。

CREATE TRIGGER xs_kc_update BEFORE UPDATE

ONxs_kc FOR EACH ROW

BEGIN

DECLARExf INT(1);

SELECT学分 INTO xf FROM kc WHERE课程号=new.课程号;

IFnew.成绩<60 THEN

SETnew.学分=0;

ELSE

SETnew.学分=xf;

ENDIF;

END

注意：当触发器涉及对触发表自身的更新操作时，只能使用BEFORE，AFTER触发器将不被允许。

【例】创建触发器，实现当向xs_kc表插入一行数据时，根据成绩对xs表的总学分进行修改。如果成绩>=60，则总学分加上该课程的学分，否则总学分不变。

CREATE TRIGGER xs_kc_zxf AFTER INSERT

ONxs_kc FOR EACH ROW

BEGIN

DECLARExf INT(1);

SELECT学分 INTO xf FROM kc WHERE课程号=new.课程号;

IFnew.成绩>=60 THEN

UPDATExs SET 总学分=总学分+xf WHERE学号=new.学号;

ENDIF;

END

6.3.1.2 在触发器中调用存储过程

【例】假设xscj数据库中有一个与xs表结构完全一样的表student，创建一个触发器，在xs表中添加数据的时候，调用存储过程，将student表中数据与xs表同步。

首先，定义存储过程：

CREATE PROCEDURE changes()

BEGIN

REPLACEINTO student SELECT * FROM xs;

END

接着创建触发器：

CREATE TRIGGER student_change AFTER INSERT

ON xsFOR EACH ROW

CALLchanges();

验证：

INSERT INTO xs VALUES ('091102','王大庆','计算机',201,'1994-08-14',1,8,88);

SELECT * FROM student;

6.3.2 删除触发器：

语法：

DROP TRIGGER [schema_name.]trigger_name

说明：

trigger_name：指要删除的触发器名称。

schema_name：所在数据库的名称，如果在当前数据库可省略。

例如，删除触发器xs_delete。

DROP TRIGGER xs_delete;

6.4 事件

事件和触发器有些相似，也被称为临时触发器。事件的主要作用如下：

（1）关闭账户；

（2）打开或关闭数据库指示器；

（3）使数据库中的数据在某个间隔后刷新；

（4）执行对进入数据的复杂的检查工作。

6.4.1 创建事件

语法：

CREATE EVENT [IF NOT EXISTS] 事件名

ONSCHEDULE schedule

[ONCOMPLETION [NOT] PRESERVE]

[ENABLE|DISABLE|DISABLEON SLAVE]

[COMMENT‘comment’]

DOsql 语句;

schedule：

AT timestamp [+ INTERVAL interval]

|EVERY interval

[STARTS timestamp [+ INTERVAL interval]]

[ENDS timestamp [+ INTERVAL interval]]

interval:

count {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|

DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}

说明：

schedule：时间调度，表示事件何时发生或者每隔多久发生一次。

AT子句：表示在某个时刻事件发生。

timestamp：表示一个具体的时间点，后面可以加上一个时间间隔，表示在这个时间间隔后事件发生。

interval：表示这个时间间隔，由一个数值和单位构成，count是间隔时间的数值。

EVERY子句：表示在指定时间区间内每隔多长时间事件发生一次。

STARTS子句：指定开始时间。

ENDS子句：指定结束时间。

sql语句：包含事件启动时执行的代码。如果包含多条语句，可以使用BEGIN … END复合结构。

ON COMPLETION [NOT] PRESERVE：ON COMPLETION NOT PRESERVE表示事件最后一次调用后将自动删除该事件；ON COMPLETIONPRESERVE表示事件最后一次调用后将保留该事件。默认为ON COMPLETIONNOT PRESERVE。

ENABLE|DISABLE|DISABLEON SLAVE：ENABLE表示该事件是活动的，活动意味着调度器检查事件动作是否必须调用。DISABLE表示该事件是关闭的，关闭意味着事件的声明存储到目录中，但是调度器不会检查它是否应该调用。DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定任何选项，在一个事件创建之后，它立即变为活动的。

一个打开的事件可以执行一次或多次。一个事件的执行称作调用事件。每次调用一个事件，MySQL都处理事件动作。

MySQL事件调度器负责调用事件。这个模块是MySQL数据库服务器的一部分。它不断地监视一个事件是否需要调用。要创建事件，必须打开调度器。可以使用系统变量EVENT_SCHEDULER来打开事件调度器，TRUE为打开，FALSE为关闭。

SET GLOBAL EVENT_SCHEDULER=TRUE;

【例】创建一个立即启动事件。

CREATE EVENT direct

ONSCHEDULE AT NOW()

DOINSERT INTO xs VALUES('091103','张建','软件工程师',1,'1994-06-05',50,null,null);

说明：这个事件只调用一次，在事件创建之后立即调用。

【例】创建一个30秒后启动的事件。

CREATE EVENT thrityseconds

ONSCHEDULE AT NOW()+INTERVAL 30 SECOND

INSERTINTO xs VALUES('091104','陈建','软件工程师',1,'1994-08-16',50,null,null);

【例】创建一个事件，它每个月启动一次，开始于下个月并且在2014年的12月31日结束。

CREATE EVENTstartmonth

ON SCHEDULE EVERY 1 MONTH

STARTS CURDATE()+INTERVAL 1 MONTH

ENDS '2014-12-31'

BEGIN

IF YEAR (CURDATE())<2014THEN

INSERT INTO xsVALUES ('091106','王建','软件工程师',1,'1994-03-16',48,null,null);

END IF;

END

（未成功）

6.4.2 修改和删除事件

6.4.2.1 修改事件

语法：

ALTER EVENTevent_name

[ON SCHEDULE schedule]

[ON COMPLETION [NOT] PRESERVE]

[RENAME TO new_event_name]

[ENABLE|DISABLE|DISABLE ON SLAVE]

[COMMENT ‘comment’]

[DO sql_statement]

说明ALTER EVENT 语句与CREATEEVENT语句格式相仿，用户可以用一条ALTER EVENT语句让一个事件关闭或再次让它活动。当然如果一个事件最后一次调用后已经不存在了，就无法修改了。用户还可以使用一条RENAME TO子句修改事件的名称。

【例】将事件startmonth的名字改成firstmonth。

ALTER EVENT startmonth

RENAMETO firstmonth;

查看修改结果：SHOW EVENTS

6.4.2.2 删除事件

语法：

DROP EVENT [IF EXISTS] [database name.]event name

【例】删除名为direct的事件。

DROP EVENT direct;

7 MySQL数据库备份与恢复

工作中，操作数据库时，要养成经常备份数据库的习惯。

使用mysqlimport恢复数据，前提是有备份。

用mysqlbinlog处理日志。

8 MySQL安全管理

8.1 用户管理

8.1.1 添加用户

语法：

CREATE USER 用户 [IDENTIFIED BY[PASSWORK] ‘密码’] …

用户：

‘用户名’@’主机名’

IDENTIFIED BY：为账户给定一个密码。特别是要在纯文本中指定密码，需要忽略PASSWORD关键词。

【例】添加两个新用户，king的密码为queen，palo的密码为530415。

CREATE USER

'king'@'localhost' IDENTIFIED by 'queen',

'palo'@'localhost' IDENTIFIED by '530415'

8.1.2 删除用户

语法：

DROP USER 用户 [,用户] …

【例】删除用户palo。

DROP USER palo@localhost

8.1.3 修改用户名

语法：

RENAME USER 老用户 TO新用户,

[,老用户 TO新用户] …

【例】将用户king的名字修改为ken。

RENAME USER

'king'@'localhost' TO 'ken'@'localhost'

8.1.4 修改用户密码

语法：

SET PASSWORD [FOR 用户]=PASSWORD(‘新密码’)

FOR：表示修改当前主机上的特定用户的密码，如果不加FOR，表示修改当前用户的密码。

【例】将用户ken的密码修改为qen。

SET PASSWORD FOR 'ken'@'localhost'=PASSWORD('qen')

8.2 权限控制

8.2.1 授予权限

【例】授予用户ken在xs表上的SELECT权限。

GRANT SELECT

ON xs

TO ken@localhost

8.2.2 权限转移和限制

GRANT语句的最后可以使用WITH子句。如果指定为WITHGRANT OPTION，则表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权利，而不管其他用户是否拥有该权限。

8.2.3 回收权限

【例】回收用户ken在xs表上的SELECT权限。

REVOKE SELECT

ON xs

FROM ken@localhost

8.3 表维护语句

8.3.1 索引列可压缩性语句

【例】更新表xs的索引的可压缩性，并随后显示。

ANALYZE TABLE xs;

SHOW INDEX FROM xs;

8.3.2 检查表是否有错语句

【例】检查xs表是否正确。

CHECK TABLE xs

8.3.3 获得表校验和语句

【例】获得表xs的效验和的值。

CHECKSUM TABLE xs

8.3.4 优化表语句

【例】优化xs表。

OPTIMIZE TABLE kc

8.3.5 修复表语句

如果一个表或索引已经损坏，可以使用REPAIRTABLE语句尝试修复它。REPAIR TABLE只对MyISAM和ARCHIVE表起作用。

9 MySQL多用户事务管理

9.1 事务管理

9.1.1 事务的概念

1 原子性

2 一致性

3 隔离性

4 持久性

9.1.2 事务处理

1 开始事务

STARTTRANSACTION|BEGIN WORK

2 结束事务

COMMIT[WORK] [AND [NO] CHAIN] [[ NO] RELEASE]

3 撤销事务

ROLLBACK[WORK] [AND [NO] CHAIN] [[NO] RELEASE]

4 回滚事务

用户可以使用ROLLBACK TO语句使事务回滚到某个点，在这之前需要使用SAVEPOINT语句来设置一个保存点：

SAVEPOINT保存点名称

ROLLBACK TO SAVEPOINT语句会向已命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对数据进行了更改，则这些更改会在回滚中被撤销。当事务回滚到某个保存点后，在该保存点之后的设置的保存点将被删除。

ROLLBACK[WORK] TO SAVEPOINT 保存点名称

RELEASE SAVEPOINT语句会从当前事务的一组保存点中删除已命名的保存点。不出现提交或回滚。如果保存点不存在，会出现错误。

RELEASESAVEPOINT 保存点名称

9.1.3 事务隔离级

1 序列化：SERIALIZABLE

2 可重复读：REPEATABLEREAD

3 提交读：READCOMMITTED

4 未提交读：READUNCOMMITTED

9.2 多用户访问

9.2.1 锁定的级别

1 表锁定

2 页锁定

3 行锁定

4 死锁

9.2.2 锁定与解锁

【例】在xs表上设置一个只读锁定。

LOCK TABLES xs READ

【例】在xs_kc表上设置一个写锁定。

LOCK TABLES xs_kc WRITE

【例】解除锁定。

UNLOCK TABLES

UNLOCK TABLES命令不需要指出解除锁定的表的名字。MySQL会自动对前面通过LOCK TABES锁定的所有表解除锁定。

阅读全文

2 0