[近两万字] MySQL大全

目录

单元1 创建数据库

1.1 创建数据库

1.查看数据库

2.选择数据库

3.删除数据库

1.2 创建数据表

1.查看表结构

2.查看所有数据表

3.复制表结构

4.删除表

5.修改表数据

        5.1 修改表名

        5.2 添加字段

        5.3删除字段

        5.4修改字段的数据类型

        5.5修改字段的名称

        5.6修改字段的顺序

6.数据类型 

1.3数据表的约束

1.3.1非空与默认值的设置

        1.数据完整性的分类

        2.数据约束类型

        3.非空与默认值的设置

1.3.2唯一键与自增的设置

        1.创建表时设置唯一或自增

        2.为已存在的表设置唯一或自增

        3.删除唯一键

        4.设置自增的注意事项

1.3.3主键与外键的设置

        1.设置主键

        2.设置外键

        3.外键与主键的约束

单元2 数据表的操作

        2.1单表查询

        1.关系代数运算

        2.简单查询

        3.条件查询

        4.统计函数和分组排序 

2.2多表查询

        1.内连接查询（自查询）

        2.外连接查询

        3.子查询

2.3数据表的增删改

        1.数据表的插入操作

        2.更新操作

        3.删除操作

单元3 索引与视图的创建

3.1创建索引

1.索引的概念

不适合创建索引的情况：

2.索引的创建

 3.2创建视图

1.视图的概述  

2.创建视图

单元4 存储过程和触发器的创建

4.1创建存储过程

1.存储过程的基本语法

2.创建普通的存储过程

3.带参数的存储过程

4.创建带控制条件的存储过程

5.创建函数

4.2创建触发器

1.触发器概述

2.创建插入触发器

3.创建更新触发器

4.创建删除触发器

单元5 数据库的管理

5.1管理事务

1.事务的特性

2.事务控制的语法

5.2管理权限

1.事务授权语句

 2.查询权限

3.取消授权

4.刷新权限

5.3数据库的备份和恢复

1.备份一个数据库

2.备份多个数据库

3.备份所有数据库

5.4管理日志

1.日志类别

2.查询日志语句

---

单元1 创建数据库

1.1 创建数据库

create database database_name;

1.查看数据库

show databases;

2.选择数据库

use databases_name;

3.删除数据库

drop database database_name;

---

1.2 创建数据表

语法：

create table table_name(
col_name1 data_type1,
col-name2 data_type2,
        ....
col_namen data-typen
);

1.查看表结构

describe table_name;

2.查看所有数据表

show tables;

3.复制表结构

（创建一个和table_name2一样的数据表table_name1）

create  table table_name1 like table_name2;

4.删除表

drop table table_name;

5.修改表数据

5.1 修改表名

alter table old_table_name rename new_table_name;

5.2 添加字段

first表示添加字段在开头，默认添加在最后；after表示添加在那个字段后

alter table table_name add col_name data_typel [first | after col_name];

5.3删除字段

alter table table_name drop col_name;

5.4修改字段的数据类型

alter table table_name modify col_name data_type;

5.5修改字段的名称

alter table table_name
change old_col_name new_col_name old_data_type;

5.6修改字段的顺序

alter table table_name modify col_name data_type first/after col_name2;

---

6.数据类型 

（1）整数类型

（2）小数类型

（3）日期时间型

（4）字符串类型

CHAR(M)        定长字符串

VARCHAR(M)        不定长字符串

TEXT         文本

TEXT类型

（5）二进制类型

BINARY(M)        定长二进制

VARBINARY(M)        不定长二进制

（6）复合型

---

1.3数据表的约束

1.3.1非空与默认值的设置

1.数据完整性的分类

数据完整性是指数据的精确性和可靠性，防止数据库中存在不符合语义规定的数据和防止因数据库操作员错误数据的输入输出而造成数据库中存在的错误数据

（1）实体完整性(Entity lntegrity)：指关系中的主属性值不能为null且不能有相同值

（2）参照完整性(Referentail Integrity)：两个表的主键和外键的数据应对应一致

        ·禁止在表中插入包含表中不存在的关键字的数据行

        ·禁止删除在从表中有对应记录的主表记录

        ·禁止会导致从表中相应值孤立的主表中的外键值改变

（3）用户自定义完整性(User-defined lntegrity)：针对某个特定关系数据库的约束条件

2.数据约束类型

3.非空与默认值的设置

语法:

alter table table_name modify col_name data_type not null | default;

---

1.3.2唯一键与自增的设置

1.创建表时设置唯一或自增

语法：

create table table_name(
col_name data_type UNIQUE | AUTO_INCREMENT,
    ......
);

2.为已存在的表设置唯一或自增

语法：

alter table table_name modify
col_name data_type UNIQUE | AUTO_INCREMENT;

3.删除唯一键

语法：

alter table table_name drop index
index_name;

4.设置自增的注意事项

·必须是整数类型才可以设置AUTO_INCEREMENT

·必须先定义为一个键(主键或者唯一键)才可以设置为AUTO_INCEREMENT

·可以用ALTER TABLE table_name AUTO_INCREMENT=default_value为字段设置自增的初始值

---

1.3.3主键与外键的设置

1.设置主键

（1）单字段主键

创建新表设置主键语法：

CREATE TABLE tabe_name(col_name data_type PRIMARY KEY,.....)

为已存在的表设置主键：

constraint_name为约束的名字，但建议最好不加约束的名字

ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
PRIMARY KEY(col_name)

删除主键：

ALTER TABLE table_name DROP PRIMARY KEY

（2）多主键联合主键

创建新表时写上primary key，将设置主键的几个字段放在括号里

语法：

CREATE TABLE table_name(col_name data_type,.....
[CONSTRAINT constraint_name]
PRIMARY KEY(col_name1,col_name2)...)

为已存在的表创建主键

语法：

ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
PRIMARY KEY(col_name1,col_name2...)

---

2.设置外键

外键不能单独设置，只有但设置了主键之后才能设置外键，外键必须参照另一个表的主键

语法：

FOREIGN KEY(col_name1)外键        REFERNCES table_name(col_name2)参照表的主键

CREATE TABLE table_name
(col_name data_type,....
[CONSTRAINT constraint_name] FOREIGN KEY(col_name1)
REFERNCES table_name(col_name2)

为已存在的表设置外键语法：

ALTER TABLE table_name1 ADD [CONSTRAINT constraint_name]
FOREINGN KEY(col_name1)
REFERENCES table_name2(col_name2)

删除外键：

删除外键时一定要加上约束的名称：

ALTER TABLE table_name1 DROP FOREIGN KEY constraint_name

删除外键两个重要的事项：

1.如果没有设置外键的名称，系统则会自动加上一个外键的名称，用命令Show create table table_name进行查看，然后删除

2.如果要彻底删除外键，应删除一个在建立外键时自动创建的索引，如果没有定义约束的名称，索引一般是约束的名称，索引查看Show indexes from table_name

---

3.外键与主键的约束

·Restrict：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父类的删除或更新操作

·Cacade：从父表删除或更新时自动删除或更新子表中匹配的行

·Set null：当父表删除或更新行时，设置子表中与之对应的外键列为NULL，如果外键列没有指定NOT NULL限定词，这就是合法的

·No action：不采取动作，如果有一个外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样

---

单元2 数据表的操作

2.1单表查询

1.关系代数运算

（1）并(union)：

设置两个关系R和S具有相同的关系模式，关系R和S的并是属于R或属于S的元组组成的集合，记为：RUS

和并相同的元组，加上不同的元组

（2）差(difference)：

关系R和S具有相同的关系模式，R和S的差是由属于R但不属于S的元组组成的集合，记为：R-S

去掉相同的得到R-S

（3）笛卡尔积：

设关系R和S的元数分别为r和s，定义R和S的笛卡尔积是一个(r+s)元的元组集合，每个元组的前r个分量来自R的一个元组，后s个分量来自S的一个元组，记为RxS

（4）投影(projection)

是对关系进行垂直分割，消去某些列，并重新安排列的顺序，再删除去重复的元组

将关系R投影A,C字段，投影A字段，投影C字段，得到的结果：

（将投影表按投影字段覆盖在被投影表上）

（5）选择（selection）

是根据某些条件对关系作水平分割，即选择符合条件的元组

选择关系R中B的属性为b的元组，满足条件的元组有2个：

（6）交(intersection)

关系R和关系S的交由属于R又属于S的元组组成的集合，记为

取相同的元组

---

2.简单查询

单表查询术语

简单查询语法格式：

SELECT * 表示查询所有元组，SELECT DISTINCT表示去重复查询

也可以实现（+，-，*，/，%）运算查询

AS更改查询结果的字段名

SELECT col_name1,col_name2,...col_name
FROM table_name;

---

3.条件查询

条件查询术语

条件查询语法：

WHERE：在简单查询的基础是多一个where语句

LIMINT：限制输出记录的个数

offset：指定初始位置

row count：指定查询的行数

codition：查询条件AND，IN，NOT IN等

SELECT col_name1,col_name2,...col_namen
FROM table_name
WHERE condition
LIMIT [offset] row_count

t条件查询的关键字表达：

·链接多个条件用AND或OR

·查询某个指定范围用BETWEEN AND，不在某个范围用NOT BETWEEN AND

·查询空值用IS NULL，不是空值用IS NOT NULL

·查询在某个集合用IN,不在某个集合用NOT IN

·模糊查询用LIKE，匹配任意字符用%，匹配单个字符用_

---

4.统计函数和分组排序 

术语语：

统计函数类型

语法：

        ASC：升序    DESC：降序     什么都不写默认为升序

SELECT founction(col_name)
FROM table_name
WHERE condition
GROUP BY col_name
HAVING condition
ORDER BY col_name [ASC | DESC]

---

2.2多表查询

1.内连接查询（自查询）

内连接查询中只保留表关系中的所有匹配的数据记录

（1）自连接查询（为同一张表多次查询）

表与其自身进行笛卡尔积连接，根据相同名称的字段进行记录匹配，查询结果仅包含符合连接条件与筛选条件的行,为了区别表的每一次出现，需要为表分别定义别名

INNER JOIN连接待查询的表

ON后面表示表与表连接匹配

语法：

SELECT A.col_name1,B.col_name2
FROM table1 A INNER JOIN table1 B
ON A.col_nameX=B.col_nameX

（2）等值或不等连接查询

该查询要两个表满足拥有公共的字段

等值语法：

INNER JOIN使用比较运算符=匹配每个表共有列的列值，查询结果仅包含符合连接条件与筛选条件的行

SELECT tableA.col_name1 tableB.col_name2
FROM tableA INNER JOIN tableB
ON tableA.col_nameX=tableB.col_name

不等查询语法：

INNER JOIN使用BETWEEN或除=的运算符匹配每个表共有列的列值，查询结果仅包含符合连接条件与筛选条件的行

SELECT tableA.col_name1,tableB.col_name2
FROM tableA INNER JOIN tableB
ON tableA.col_nameX BETWEEN tableB.col_nameM AND tableB.col_nameN

---

2.外连接查询

需要查询结果不仅包含符合连接条件的行，而且还包括左表，右表或多个连接表中所有数据行，则应该所有外连接

（1）左外连接查询        LEFT [OUTER]JOIN

在表关系的笛卡尔积记录中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的记录

语法：

SELECT col_name1,col_name2,...col_namen
FROM table_name1 LEFT JOIN table_name2
ON join_condition;

（2）右外连接查询        RIGHT[OUTER]JOIN

在表关系的笛卡尔积记录中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的记录

语法：

SELECT col_name1,col_name2,...col_namen
FROM table_name1 RIGHT JOIN table_name2
ON join_condition;

---

3.子查询

子查询也称为嵌套查询，是嵌套在外层查询WHERE子句中的查询

谓词ALL,ANY通常搭配逻辑运算符一起使用

（1）ANY子查询

>=ANY

大于等于查询结果中的某个值，返回子查询中最小的还要大于等于的数据记录

<=ANY

小于等于查询结果中的某个值，返回子查询中最大的还要小于等于的数据记录

语法：

SELECT table1.col_name FROM table1
WHERE col_name1 >=ANY|<=ANY
(SELECT col_name1 FROM table2
WHERE condition);

（2）ALL子查询

>=ALL

大于等于查询结果中的所有值，返回比子查询中最大的还要大于等于的数据记录

<=ALL

小于等于查询结果中的所有值，返回比子查询中最小的还要小于等于的数据记录

语法：

SELECT table1.col_name FROM table1
WHERE col_name1 >=ALL|<=ALL
(SELECT col_name FROM table2
WHERE condition);

（3）IN子查询

在内层查询中返回某列的值，外层所用相同的列和子查询的返回列值比较，所有关键词IN时，返回值可以有多个，所用关系运算符时，返回值至多一个

语法：

SELECT table1.col_name FROM table1
WHERE col_name1 IN|NOT IN
(SELECT col_name FROM table2
WHERE condition);

（4）EXISTS子查询

EXISTS用来检查子查询是否有查询结果返回，主要返回结果为true，外查询语句将进行查询；反之结果为flase，此时外层语句将不进行查询

语法：

SELECT table1.col_name FRON table1
WHERE EXISTS|NOT EXISTS
(SELECT col_name1 FROM table2
WHERE condition);

---

2.3数据表的增删改

1.数据表的插入操作

表中有多少个字段，括号里就应该有多少个对应的值，且值的顺序必须与字段的顺序一致

插入完整数据记录： 

INSERT INTO table_name VALUES
(values1,values2....valusen);

插入数据记录的一部分，选择字段名，然后插入对应记录：

INSERT INTO table_name
(col_name1,col_name2....col_namen)
VALUES
(values1,values2...valuesn);

插入来自另一个表的记录：

INSERT INTO table_name1
(col_name1,col_name2...col_namen)
SELECT name
(col_name1,col_nam2...col_namen)
FROM table_name2;

---

2.更新操作

 语法：

UPDATE table_name
SET col_name1=values1,
    col_name2=vaues2,
    ...
    COL_namen=valuesn
    WHERE condition

---

3.删除操作

语法：

DELETE FROM table_name
WHERE condition;

级联删除：

主表删除操作时设置主键和外键的约束删除

RESTRICT：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新

CASCADE：从父表删除或更新行时自动删除或更新子表中匹配的行

SET NULL：当从父表删除或更新行时，设置子表中与之对应的外键列为NULL，如果外键列没有指定NOT NULL限定词，这就是合法的

NO ACTION：不采取动作，如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样

---

单元3 索引与视图的创建

3.1创建索引

1.索引的概念

建立索引的目的：

·快速取数据

·保证数据记录的唯一性

·实现表与表之间的参照完整性

·使用ORDER by, group by子句进行数据索引时，减少排序和分组的时间

索引的分类：

（1）根据类型分

        普通索引        INDEX

        唯一索引        UNIQUE INDEX

        全文索引        FULLTEXT INDEX

（2）根据创建索引的字段分

        单列索引

        多列索引

适合创建索引的情况：

经常被查询的字段，在WHERE子句中出现的字段

在分组的字段，GROUP BY子句中出现的字段

存在依赖关系的子表和父表之间的联合查询，主键和外键

设置唯一完整性约束的字段

需要排序，分组和表连接操作的字段

不适合创建索引的情况：

查询中很少被使用的字段

拥有许多重复值的字段

经常更新的表

数据量小的表

2.索引的创建

创建表时创建索引

语法：

CREATE TABLE table_name
(
col_name col_definition
col_name col_definition
.....
col_name col_definition
[UNIUQE | FULLTEXT] INDEX
index_name(col_name ASC|DESC)
);

为已存在的表创建索引

语法一：

CREATE [UNIQUE|FULLTEXT] INDEX
index_name ON table_name(co_name ASC|DESC)

语法二：

ALTER TABLE table_name ADD
[UNIOUE|FULLTEXT] INDEX index_name
(col_name ASC|DESC)

---

 3.2创建视图

1.视图的概述  

视图是从表中抽取查询频率高的记录重新组成的虚表

视图特点：

·列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系

·由基本表产生的表

·视图的创建和删除不影响基本表

·视图内容的更新直接影响基本表

视图来自多个表时，不允许添加和删除数据

---

2.创建视图

（1）创建视图

语法：

AS SELECT视图的定义规则

CASCADED检查底层视图的规则

LOCAL检查当前视图的规则

CREATE VIEW view_name
AS SELECT 语句
[WITH [CASCADED|LOCAL]
CHECK OPTION]

（2）查看视图

语法一：

查询结果只显示视图名

SHOW TABLE [LIKE 'view_name'];

语法二：

查询结果显示视图名，版本，创建时间等

SHOW TABLE STATUS [LIKE 'view_name'];

语法三：

视图名不需要引号包裹，查询结果显示视图名，字符信息

SHOW CREATE VIEW view_name;

语法四：

视图名不需要引号包裹，DESC是DESCRIBE的缩写，查询结果显示图包括列名，类型等

DESCRIBE|DESC view_name;

（3）修改视图

语法：

ALTER VIEW view_name
AS SELECT语句
[WITH[CASCSDED|LOCAL]
CHECK OPTION];

（4）删除视图

语法：

DROP VIEW IF EXISTS:存在则删除视图

CASCADED:自动删除依赖此视图的对象

RESTRICT:依赖对象存在拒绝删除此视图

DROP VIEW IF EXISTS view_name
[CASCADED|RESTRICT];

---

单元4 存储过程和触发器的创建

4.1创建存储过程

1.存储过程的基本语法

（1）常量

作用：参与运算或变量赋值

类型：字符串型，数值型，日期时间型，布尔型，空null

---

（2）变量

作用：用于记录或暂时存放某一时段的状态值

分类：

        ·用户变量，先定义才能使用

        .系统变量，可以直接使用

用户变量又分为：会话用户变量，局部变量

会话用户变量定义：

SET @var1=值1
或
SELECT 值1 INTO var1

局部变量定义：

DECLARE var1 数据类型 DEFAULT 默认值

---

（3）程序结构

·顺序结构

语句按顺序执行

·分支结构

分支语句有if和case

        IF语句：

语法:

IF 条件1 THEN 语句序列1
ELSEIF 条件2 THEN 语句序列2
....
ELSE 语句序列n
END IF

        CASE语句：

语法1：

CASE
WHEN 条件1 THEN 语句序列1
WHEN 条件2 THEN 语句序列2
...
ELSE 语句序列n
END CASE

语法2：

CASE 表达式
WHEN 值1 THEN 语句序列1
WHEN 值2 THEN 语句序列2
...
ELSE 语句序列n
END CASE

·循环结构

循环语句：WHILE语句，REPEAT语句，LOOP语句

WHILE语句：当条件满足时执行

语法：

WHILE条件 DO
程序段
END WHILE

REPEAT语句：满足条件时执行repeat后的语句

语法：

REPEAT
程序段
UNTIL 条件
END REPEAT

LOOP语句：当条件满足时跳出循环

语法：

LOOP
程序段
END LOOP

---

2.创建普通的存储过程

存储过程是具有名字的一段代码，用来完成一个特定的功能

  存储过程语法：

procedure_name:要创建到存储过程的名字

procedure_parameter：表示存储过程的参数

characteristic：存储过程的特性

routine_body：存储过程的SQL语句

可以用BEGIN....END来标记SQL语句的开始和结束

CREATE PROCEDURE procedure_name
([procedure_parameter[....]])
[characteristic...] routine_body

查看存储过程状态信息

语法：

SHOW PROCEDURE STATUS LIKE
'procedure_name'

查看存储过程定义的信息

语法：

SHOW CREATE PROCEDURE
procedure_name;

修改存储过程

语法：

该语法用于修改存储过程的某些特征，比如读写权限，如要修改存储过程的内容，可以先删除该层次过程，再重新创建

ALTER PROCEDURE procedure_name
[charateristic...];

删除存储过程

语法：

DROP PROCEDURE procedure_name;

存储过程的语句结构：

DELIMITER $
BEGIN
.....
END $
DELIMITER;

实例：创建一个存储过程，查询所有读者的基本信息

代码

调用存储过程：CALL p1();

DELIMITER $
CREATE PROCEDURE P1()
COMMENT '查询所有读者的信息'
    BEGIN
        SELECT*
        FROM t_reader
    END $
DELIMITER;

---

3.带参数的存储过程

参数类型

·输入参数IN：表示调用者向过程传入值，传入参数可以是常量或变量

·输出参数OUT：表示过程向调用者传出值，可以返回多个值，传出值只能是变量

·输入输出参数INOUT：表示调用者向过程传入值，又表示过程向调用者传出值，值只能是变量

输入参数实例：

创建带输入参数的存储过程，查询某个读者ID对应的读者姓名

调用存储过程实例：CALL p_in('101101')

DELIMITER $
CREATE PROCEDURE p_in(in pid CHAR(6))
COMMENT '查询某个ID对应的读者姓名'
BGIN
    SELECT reader_name
    FROM t_reader
    WHERE reader_id=pid;
END $
DELIMITER;

输出参数实例：

创建一个带输出参数的存储过程，查询图书的最高价格，最低价格和平均价格

调用实例：CALL p_out(@para_min,@para_max,@para_avg);

                  SELECT @para_min,@para_max,@para_avg;

DELIMITER $
CREATE PROCEDURE p_out
(out para_min float,out para_max float,out para_avg float)
COMMENT "查询图书的最高价格，最低价格和平均价格"
BEGIN
    SELECT min(book_price),
    max(book_price),
    avg(book_price)
INTO para_min,para_max,para_vag
FROM t_book;
END $
DELIMITER;

输入输出参数实例：

创建一个带输入输出参数的存储过程，对某本书的价格增加10元

调用实例：

SET @currentprice=12;

CALL p_inout(@currentprice);

SELECT (@cerrentprice);

DELIMITER $
CREATE PROCEDURE p_inout(inout price float)
COMMENT '对某本书的价格增加10元'
BEGIN
    SET price=price+10;
END $
DELIMITER;

---

4.创建带控制条件的存储过程

 （1）循环条件实例

创建一个存储过程，计算100以内的所有整数之和

DELIMITER $
CREATE PROCEDURE p2(OUT total INT)
BGIN
    DECLARE num int DEFAULT 0;
    SET total=0;
WHILE num<=100 DO
    SET num=num+1;
    SET total=total+num;
END WHILE;
END $

(2)  IF分支条件实例

创建一个存储过程，比较两位读者年龄的大小

调用实例：CALL compare_age(@age1,@age2,'小花','小红',@result);

                   SELECT @age1，@age2，@result；

DELIMITER $
CREATE PROCEDURE compare_age
(OUT age1 INT,OUT age2 INT,IN name1 VARCHAR(50),IN name2 VARCHAR(50),OUT result CHAR(20))
BEGIN
    SELECT year(curdate())-year(reader-birthday) INTO age1
    FROM t_reafer WHERE reader_name=name1;
    SELECT year(curdate())-year(reader-birthday) INTO age2
    FROM t_reafer WHERE reader_name=name2;
    IF age1>age2 THEN
        SET result=CONCAT(name1,'的年龄大于',name2)
    ELASE age1=age2 THEN
        SET result=COMCAT(name1,'与',name1,'同岁')
    ELSE
        SET result=CONCAT(name1,'的年龄小于',name2)
    END IF;
END $
DELIMINER;

 （3）CASE分支条件实例

创建一个存储过程，判断书的价格

DELIMITER $
CREATE PROCEDURE pro_price
(IN b_name VARCHAR(50),OUT price FLOAT,OUT result  VARCHAR(10))
BEGIN
    SELECT book_price INTO price
    FROM t_book
    WHERE book_name=bname;
    CASE
    WHEN price>=100 THEN SET reuslt="昂贵";
    WHEN price<=50 THEN SET result="便宜";
    WHEN price is null THEN SET result="无此书";
    ELSE SET result="中等";
END CASE;
END $
DELIMITER;

---

5.创建函数

创建函数语法：

CREATE FUNCTION：用来创建函数的关键字

func_name：表示函数的名称

parameters：函数的参数列表

type：函数的类型

body：函数体，一般格式为begin和end之间包裹return

CREATE FUNCTION func_name(parameters)
RETURNS type
body

函数创建实例：

创建一个函数，根据图书的ID查找该书的复本量

DELIMITER $
CREATE FUNCTION func_bookcopy(bookid CHAR(17))
RETURNS int(3)
 BGIN
    RETURN(SELECT book_copy
    FROM t_book
    WHERE isbn=bookid);
 END $
DELIMINER;
//调用
SELECT func_bookcopy(‘123-4324-525-234’);

---

4.2创建触发器

1.触发器概述

触发器是当数据表发生插入，删除或更新事件时数据库系统会自动触发预先编写好的若干条sql语句

触发器的特点及作用

特点：具有原子性，要么全部执行，要么全部不执行

作用：保证数据的完整性，起到约束的作用

触发器类型：

·插入触发器

·删除触发器

·更新触发器

创建触发器命令：

create trigger：是创建触发器的命令

trigger_time：触发器执行的时间，有after和before两种选择

trigger_event：指触发器的触发事件，insert插入触发器，update更新触发器，delete删除触发器

for each row：表示在表中任意一条记录进行操作都会触发

table_name：表示触发事件作用在哪张表上

trigger_body：表示触发器要执行的具体语句

CREATE TRIGGER trigger_name trigger_time
trgger_event
ON table_name FOR EACH ROW [trigger_order]
trigger_body

查看触发器：

语句一：

查看所有的触发器

SHOW TRIGGERS;

语句二：

后面跟trigger_name表示对一个具体的触发器进行详细查看

SHOW CREATE TRIGGER trigger_name;

触发器的工作原理：

触发器主要依赖于MySQL数据库系统中提供的两张临时表，NEW表和OLD表，这两张表主要用来引用触发器中发生变化的记录内容

（1）在insert类型的触发器中，用NEW来临时存储插入的新数据

（2）在delete类型的触发器中，用OLD来临时存储被删除的原数据

（3）在update触发器中，用OLD表来保存修改之前的原数据，用NEW表保存修改之后的新数据

---

2.创建插入触发器

实例1：

每向t_book中插入一条记录后，则向t_log表中插入该表的表名t_book和插入的时间

CREATE TABLE t_log
(
logno INT AUTO_INCREMENT PRIMARY KEY,
tname VARCHAR(20),
logtime DATETIME
);
CREATE TRIGGER trigger_log
ALTER INSERT
    ON t_book1 FOR EACH ROW
    INSERT INTO t_log(tname,logtime) VALIES('t_book',now());

实例2：

创建一个t_borrow_record表的插入触发器，当向t_borrow_record表中插入一条数据时，该记录对应图书的库存量自动-1，对应的读者的借阅量自动+1

CREATE TRIGGER trigger_insert2
AFTER INSERT
ON t_borrow_record FOR EACH ROW
BEGIN
    UPDATE t_reader SET reader_borrowtatal=reader_borrowtotal+1
    WHERE reader_id=new.read_id;
    UPDATE t_book SET book_inventory-1
    WHERE ISBN=new.ISBN;
END

---

3.创建更新触发器

实例：

CREATE TRIGGER trigger_u2
AFTER UPDATE
ON t_press FOR EACH ROW
BEGIN
 IF(NEW.press_name=OLD.press_name) THEN
    SELECT "相同" INTO @result;
 ELSE
    SELECT "不相同" INTO @result;
 END IF;
END

---

4.创建删除触发器

实例：

CREATE TRIGGRT triggrt_delete
AFTER DELETE
ON t_reader1 FOR EACH ROW
BEGIN
    DELETE FROM t_borrow_record1
    WHERE reader_id=old.reader_id;
END

---

单元5 数据库的管理

5.1管理事务

1.事务的特性

（1）原子性（Atomicity）

事务中所有的操作视为一个原子单元，对于事务进行的数据修改操作只能完全提交或者完全回滚

（2）一致性（Consistency）

事务在完成时，必须使得所有的数据从一种一致性状态转换到另一种一致性状态，所有的变更都必须应用于事务的修改，以此保证数据的完整性

（3）隔离性（lsolation）

多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰

（4）持久性（Durability）

事务完成以后，所做的修改对数据的影响是永久的，接下来进行其他操作或系统故障时，都不能对其执行结果有任何影响

2.事务控制的语法

语法：

START TRANSACTION|BEGIN语句可以开始一项新的事务

COMMIT,RELLBACK语句用来定义提交，回滚事务

CHAIN,RELEASE语句用来定义在事务提交或回滚之后的操作，CHAIN会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接

SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务需要通过明确的命令进行提交或者回滚

START TRANSACTION|BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT={0|1} 

---

5.2管理权限

1.事务授权语句

GRANT priv_type [(column_list)] ON table
To user [IDENTIFIED BY [PASSWORD] 'password']
[user [IDENTIFIED BY [PASSWORD] 'password']]
....
[WITH with-option[with_option]...]

 2.查询权限

SHOW GRANT FOR 'username'@'hostname'

3.取消授权

REVOKE priv_type [(column_list)] ON table
FROM user1 [IDENTIFIED BY [PASSWORD] 'password']
...
[user2 [IDENTIFIED BY [PASSWORD] 'password']]

4.刷新权限

FLUSH PRIVILEGES

 实例：

创建一个本地访问的数据库账号

CREATE USER 'MYUN'@'locahost' IDENTIFIED BY '123456';

为MYUN赋予访问图书基本表的查询权限

GRANT INSERT ON db_library.t_book To'MYUN'@'localhost'
IDENTIFIED BY '123456';

---

5.3数据库的备份和恢复

1.备份一个数据库

username：表示用户名

dbname：表示数据库

table：表示要备份的表，如果没有table，则表示备份整个数据库

backupname：表示所生成的备份文件名

语法：

MYSQLDUMP -uusername -p dbname
table1 table2...tablen
>backupname.sql

2.备份多个数据库

语法：

MYSQLDUMP -u username -p --databases 
dbname1 dbname2...dbnamen
>Backupname.sql

3.备份所有数据库

语法：

MYSQLDUMP -u username -p -all-database
>Backupname.sql

4.数据库的恢复

语法：

MYSQL -u username -p [dbname] <backupname.sql

---

5.4管理日志

1.日志类别

（1）错误日志

定位数据库报错原因

存储MySQL服务的启停信息

运行过程中抛出的错误信息

错误日志默认开启且无法被禁止

（2）查询日志

MySQL服务器的启停信息

客户端连接信息

增删改查数据记录的sql语句

查询日志默认关闭：因为查询日志会记录用户的所有操作

（3）慢查询日志

定位数据库查询性能瓶颈

记录执行时间超过指定时间的各种操作

（4）二进制日志

以二进制的形式记录数据库除查询以外的各种操作也叫变更日志

2.查询日志语句

查看查询日志是否开启

语法：

SHOW WARIBLES LIKE '%general%';

开启查询日志

语法：

SET GLOBAL general_log=ON;

关闭查询日志

语法：

SET GLOBAL general_log=OFF;

---

完