MySQL 例题

第九章 存储过程

一、创建存储过程，首先当前用户需要有创建存储过程的权限才可以。

SELECT create_routine_priv
FROM mysql.user
WHERE USER=‘root’

二、创建存储过程

1. 创建一个存储过程，输入学生姓名则输出学生所有信息。

DELIMITER //
CREATE PROCEDURE sp_name(IN s_name VARCHAR(20))
BEGIN
IF s_name IS NULL OR s_name=’’ THEN
SELECT *
FROM student;
ELSE
SELECT *
FROM student
WHERE sname=s_name;
END IF;
END //

DROP PROCEDURE sp_name;
调用存储过程使用
CALL sp_name;
创建一个存储过程sp_2, 输入学生年龄，则输出学生的所有信息和出生年份
CREATE PROCEDURE sp_2(IN age INT)
BEGIN
IF age<=0
THEN
SELECT ‘请输入大于0的数值！’
ELSE
SELECT *, YEAR(NOW)-sage
FROM student
WHERE sage=age;
END IF;
END //

CALL sp_2(18);

三、查看存储过程

1. 状态
   SHOW PROCEDURE STATUS

2. 创建信息
   SHOW CREATE PROCEDURE sp_1

3. 从表中查看存储过程
   SELECT *
   FROM information_schema.ROUTINES
   WHERE routine_name=‘sp_1’ AND routine_type=‘procedure’

四、修改存储过程

SELECT alter_routine_priv
FROM mysql.user
WHERE USER=‘root’

五、删除存储过程

DROP PROCEDURE sp_1

‘局部变量以及光标都是在存储过程内部定义完成的，独立定义将会出现语法错误不能运行’

六、声明局部变量

DECLARE a INT DEFAULT 10;

七、光标的使用（sql server中叫游标）

在SQL SERVER中游标的五种状态：
声明-打开-使用（读取/获取）-关闭-删除
在MySQL中打开光标的四种状态：
声明-打开-使用（读取/获取）-关闭

1. 声明光标
   DECLARE NAME CHAR(5);
   DECLARE cur_1 CURSOR FOR SELECT sname FROM student

2. 打开光标
   OPEN cur_1;

3. 使用光标
   FETCH cur_1 INT NAME;

4. 关闭光标
   CLOSE cur_1

八、定义条件和处理程序

‘定义条件：是事先定义程序执行过程中可能遇到的问题。’

‘处理程序：对遇到的这些问题时应采取相应的处理方式，并且保证存储过程或函数在遇到警告或者错误的时候能继续执行’

定义条件和处理程序 可以增强存储程序处理问题的能力，避免程序异常停止工作。

1. 定义条件
   DECLARE con_error CONDITION FOR SQLSTATE ‘20020’

DECLARE con_name_error CONDITION FOR 1120

‘定义了条件之后，还需要对应的处理程序。’
2. 处理程序
DECLARE CONDITION HANDLER FOR SQLSTATE ‘20020’
SET @infor=‘nosearch’;

九、流程控制

1. if语句
   根据给出的学生成绩参数输出学生学号和姓名
   DELIMITER //
   CREATE PROCEDURE sp_score(IN p_score CHAR(1))
   BEGIN
   IF p_score = ‘A’ THEN
   SELECT sno,sname FROM student WHERE sno IN(SELECT sno
   FROM sc WHERE score>=90);
   ELSEIF p_score = ‘B’ THEN
   SELECT sno,sname FROM student WHERE sno IN(SELECT sno
   FROM sc WHERE score>=80 AND score <90);
   ELSEIF p_score = ‘C’ THEN
   SELECT sno,sname FROM student WHERE sno IN(SELECT sno
   FROM sc WHERE score>=70 AND score <80);
   ELSEIF p_score = ‘D’ THEN
   SELECT sno,sname FROM student WHERE sno IN(SELECT sno
   FROM sc WHERE score>=60 AND score <70);
   ELSE
   SELECT sno,sname FROM student WHERE sno IN(SELECT sno
   FROM sc WHERE score<60);
   END IF;
   END //

执行存储过程
DELIMITER//
CALL sp_score (‘F’)

SELECT sc.sno,sname
FROM student,sc
WHERE student.scoer=sc.scoer AND scoer=90

2. case语句
   DELIMITER //
   CREATE PROCEDURE sp_grade(IN p_grade CHAR(1))
   BEGIN
   DECLARE num INT DEFAULT 0;
   CASE p_grade
   WHERE ‘A’ THEN
   SET num=90;
   WHERE ‘B’ THEN
   SET num=80;
   WHERE ‘C’ THEN
   SET num=70;
   WHERE ‘D’ THEN
   SET num=60;
   ELSE
   SET num=0;
   END CASE;
   SELECT * FROM student, sc WHERE sc.sno=student.sno AND score>num;
   END //

CALL sp_grape(‘A’)

3. while语句
   DELIMITER //
   CREATE PROCEDURE sp_test(IN p_num INT, OUT p_result)
   BEGIN
   SET p_result=1;
   WHILE p_num>1 DO
   SET p_result=p_num+p_result;
   SET p_num=p_num-1;
   END WHILE;
   END

CALL sp_test(5,@result)
SELECT @result;

十、事件调度器

1. 查看是否开启事假调度器
   SELECT @@event_scheduler

2. 开启事件调度器
   SELECT GLOBAL event_scheduler=ON

3. 创建事件调度器
   （1）创建一个表
   CREATE TABLE test
   (id INT PRIMARY KEY AUTO_INCREMENT,event_time DATETIME);
   （2）创建事件调度器
   CREATE EVENT test1
   ON SCHEDULE
   EVERY 5 SECOND
   DO
   INSERT INTO test(event_time)
   VALUE (NOW());
   SELECT *
   FROM test;

第10章 触发器

一、创建触发器

CREATE TRIGGER trigger_name
trigger_time – before/after
trigger_event ON tb1_name
FOR EACH ROW
trigger_stmt
不能同时在一个表上创建两个类型相同的触发器
DELIMITER //
CREATE TRIGGER tri_1
AFTER DELETE ON sc
FOR EACH ROW
BEGIN
UPDATE sc SET score=60 WHERE score<60;
END //

DELIMITER //
CREATE TRIGGER tri2
BEFORE DELETE ON student
FOR EACH ROW
BEGIN
INSERT INTO student VALUES(‘22010’,‘zhang’,’’,’’,’’);
END //

二、查看触发器

1. 使用 SHOW TRIGGERS 语句查看触发器
   SHOW TRIGGERS
2. 从information_schema.triggers表中查看触发器
   DESC information_schema.TRIGGERS
   SELECT *
   FROM information_schema.TRIGGERS
   WHERE trigger_name = ‘tri_1’

三、删除触发器

DROP TRIGGER mydatabase.tri_1

第11章 数据库事务

事务 - 是整体操作
事务的属性 - 原子性 一致性 隔离性 持久性

并发操作产生的不一致性：脏读、不可重复读、幻读、丢失修改
并发控制：加锁机制：共享锁（S锁）、排它锁（X锁）
产生现象：死锁（一次性加锁）、活锁（先到先得）

第12章 MySQL高级操作

备份与还原

1.权限的授予

GRANT DELETE,UPDATE,INSERT,SELECT ON . TO ‘user’@‘localhost’
IDENTIFIED BY ‘123456’;

SELECT HOST,USER,PASSWORD,insert_priv,select_priv
FROM mysql.user WHERE USER=‘user1’

GRANT SELECT,UPDATE ON . TO ‘user2’@‘localhost’

2.查看权限

SHOW GRANT FOR ‘user’@‘localhost’

3.收回权限

REVOKE UPDATE ON . FROM ‘user2’@‘localhost’