MySQL中的存储过程+触发器+视图+函数+DCL数据库权限
程序员文章站
2022-06-04 07:56:54
...
一.存储过程
1.概述
概念
`存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似与我们Java中的方法`
2.语法
DELIMITER $$
CREATE
PROCEDURE `数据库名称`.`存储过程名称`(参数列表)
BEGIN
存储过程体（一组SQL语句）;
END$$
DELIMITER ;
注意事项:
DELIMITER $$定义存储过程的结束标记 DELIMITER ;表示恢复 ;为SQL语句的结束标记
参数列表包含三部分：参数模式 参数名 参数类型
举例：in stuname varchar
1、参数模式：
IN：该参数作为输入
OUT：该参数作为输出
INOUT：该参数既可以作为输入，也可以作为输出
2、如果存储过程体只要一句SQL，那么begin...end可以省略；
3、存储过程体的每条SQL语句的结尾必须加分号，存储过程使用delimiter 结束标记来设置结束标记
存储过程的调用:
语法：call myTestPro(9527,@rr)
查询结果： select @rr
3.空参调用
DELIMITER $$
CREATE
-- 空参调用
PROCEDURE `mydb2`.`myTestPro`()
BEGIN
SELECT * FROM student2;
END$$
DELIMITER ;
-- 存储过程的调用
CALL myTestPro;
4. IN模式的参数
DELIMITER $$
CREATE
-- IN模式的参数
PROCEDURE `mydb2`.`myTestPro2`(IN id INT)
BEGIN
SELECT * FROM USER,orders WHERE user.id=orders.`uid`;
END$$
DELIMITER ;
-- 调用存储过程 in模式
SET @num=1;
CALL myTestPro2(@num);
5. OUT模式的参数
类似于Java中的返回值
DELIMITER $$
CREATE
PROCEDURE `mydb2`.`myTestPro3`(OUT r INT)
BEGIN
SELECT COUNT(*) INTO r FROM USER;
END$$
DELIMITER ;
-- 调用存储过程 out模式
SET @num=0;
CALL myTestPro3(@num);
SELECT @num; -- 4
6. IN和OUT模式的参数
DELIMITER $$
CREATE
-- INOUT模式的参数
PROCEDURE `mydb2`.`myTestPro4`(IN num1 INT,OUT num2 INT)
BEGIN
DELETE FROM student WHERE student.`s_id`=num1;
SELECT COUNT(*) INTO num2 FROM student;
END$$
DELIMITER ;
-- 调用存储过程 in out模式
-- 第一个参数表示订单的id,第二个参数表示返回值
SET @num2=0;
CALL myTestPro4(1,@num2);
SELECT @num2;
7. INOUT模式
DELIMITER $$
CREATE
-- 存储过程的创建,INOUT模式inout
PROCEDURE `mydb2`.`myTestPro5`(INOUT num INT)
BEGIN
DELETE FROM student WHERE student.`s_id` = num;
SELECT COUNT(*) INTO num FROM student;
END$$
DELIMITER ;
SET @num2=2;
CALL myTestPro5(@num2);
SELECT @num2;
8.存储过程的删除和查看
-- 三、删除存储过程
语法： drop procedure myTestPro;
-- 四：查看存储过程
show procedure status\G; -- 查看所有的存储过程状态
show create procedure 存储过程名字\G; -- 查看创建存储过程的语句
二.带有逻辑的存储过程
1.带有IF逻辑的存储过程
DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
SET str='星期一';
ELSEIF num=2 THEN --注意elseif 连在一块
SET str='星期二';
ELSEIF num=3 THEN
SET str='星期三'; -- 注意要用分号结束
ELSE
SET str='输入错误';
END IF; -- 注意要结束if 后面有分号
END $$
2.带有while循环的存储过程
-- 计算1-输入数之间的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
-- 定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum = vsum+i;
SET i=i+1;
END WHILE; -- 要记得结束循环
SET result=vsum;
END $
3.其他循环
3.1 REPEAT 循环
DELIMITER $
CREATE PROCEDURE proRepeat(OUT outnum INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i=i+1;
UNTIL i>=5 -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
END REPEAT; -- 记着结束
SET outnum=i;
END $
CALL proRepeat(@num);
SELECT @num;
3.2 loop 循环
DELIMITER $$
CREATE
PROCEDURE `mydemo3`.`proLoop`(OUT outnum INT)
BEGIN
DECLARE i INT DEFAULT 0;
myloop:LOOP -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
SET i=i+1;
IF i>=5 THEN
LEAVE myloop; -- 根据我的循环标号中断循环 leave 是中断循环的意思
END IF;
END LOOP;
SET outnum=i;
END$$
DELIMITER ;
CALL proLoop(@num);
SELECT @num;
控制循环的两个关键字
`leave 相当于java中的 break `
`iterate相当于java中的continue`
4.变量
全局变量（内置变量）：可以在多个会话中去访问他
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量： set 变量名=新值
-- character_set_client: mysql服务器的接收数据的编码
-- character_set_results：mysql服务器输出数据的编码
SET character_set_client=gbk –设置数据库编码
set character_set_results=gbk
SET character_set_client=utf8; –设置数据库编码
set character_set_results=utf8;
SELECT @@character_set_client --查看数据库编码
-- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
-- 定义会话变量: set @变量=值
-- 查看会话变量： select @变量
-- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
定义局部变量的语法：DECLARE i INT DEFAULT 1;
给变量设置值 set i=10；
三.触发器 Trigger
1.简介
触发器：数据库中的一个对象，相当于JS中的监听器，触发器可以监听 增删改 三个动作
比如说我想监听一张表，只要我增删改了这张表中的数据，我就可以触发这个触发器，去往另外一张表中记录一下日志
2.语法
DELIMITER $$
CREATE
TRIGGER 数据库名.触发器名 BEFORE/AFTER INSERT/UPDATE/DELETE
ON 数据库名.表名
FOR EACH ROW BEGIN
END$$
DELIMITER ;
BEFORE 行为发生之前就触发
AFTER 行为发生之后触发
FOR EACH ROW 行级触发，每操作一行就触发
3.练习
案例1:往student表里插入一条数据,往log里插入一条记录
DELIMITER $$
CREATE
-- 往student表里插入一条数据,往log里插入一条记录
TRIGGER `mydb2`.`test1` AFTER INSERT
ON `mydb2`.student
FOR EACH ROW BEGIN
INSERT INTO LOG VALUES('2','你插入了一条数据',NOW());
END$$
DELIMITER ;
案例2:从student里删除一条数据,往log里加入一条记录
DELIMITER $$
CREATE
-- 往student表里插入一条数据,往log里插入一条记录
TRIGGER `mydb2`.`test2` AFTER DELETE
ON `mydb2`.student
FOR EACH ROW BEGIN
INSERT INTO LOG VALUES('3','你删除了一条数据',NOW());
END$$
DELIMITER ;
4. old和new
old.字段 可以获取到被监听的表中的字段的旧值
new.字段 可以获取到被监听表中更新后的字段的新值 比如插入新值或者修改旧值
案例1:我往一张表student中添加一条数据，另一张表student2也要添加一条同样的数据
DELIMITER $$
CREATE
TRIGGER `mydb2`.`test3` AFTER INSERT
ON `mydb2`.student
FOR EACH ROW BEGIN
INSERT INTO student2 VALUES(new.s_id,new.s_name);
END$$
DELIMITER ;
案例2:当我修改student表中一条记录时，student2表中也要修改该条记录
DELIMITER $$
CREATE
TRIGGER `mydb2`.`test4` AFTER UPDATE
ON `mydb2`.student
FOR EACH ROW BEGIN
UPDATE student2 SET student2.`s_id`=new.s_id,student2.`s_name`=new.s_name WHERE student2.`s_id`=old.s_id;
END$$
DELIMITER ;
四.视图 View
1.概念
视图:有结构(有行有列),但没有结果(结构中不真实存储数据)的虚拟的表,
虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图数据的来源)
2.创建视图语法
create view 视图名称 as select语句(这个语句可以是一张或多张表的的普通查询,或多表查询)
例如:创建单表视图
create view my_v1 as select * from student;
例如:创建多表视图 注意:不要查询两张表中的同名字段 不然会报错
create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;
注意：MySQL中视图不支持封装子查询查出来的数据
3.查看视图 其实视图是一张 虚拟表 那关于查询表的语句 对视图都是可以用的
比如:show tables; desc my_v1
只是在查看视图创建语句的的时候 把table 改成view
如:show create view my_v1;
4.视图一旦创建,系统会在视图对应的数据库文件夹下,创建一个对应的结构文件:frm文件.
5.视图的使用: 视图的使用,只是为了简化查询,你可以把 视图当作表一样去使用 例如:select * from my_v1;
视图的执行:其实本质就是执行封装的select语句
7.删除视图: drop view 视图名称
例如:drop view my_v1
6.修改视图:视图本身不可以修改,但是视图的来源是可以修改的(其实就是修改select 语句)
语法: alter view 视图名字 as 新的select语句
7.视图的意义:(1)视图可以节省SQL语句,将一条复杂的查询语句,使用视图进行保存,以后可以直接对视图进行操作.
(2)数据安全,视图操作注意是针对查询语句的,如果对视图结构进行处理(比如删除),不会影响基表的数据.
所以相对来说数据比较安全
(3)视图往往是在大项目中去使用,而且是多系统中去使用.我可以对外提供一些有用的数据,隐藏一些关键的数据.
(4)视图对外可以提供友好的数据:不同的视图提供不同的数据,对外提供的数据好像是经过专门设计的一样.
(5)视图可以更好的进行权限控制 比如对外隐藏我的一些基表的名称
8.视图数据的操作:视图是可以进行数据操作的(比如 增,删,改,视图中的数据),但是有很多限制
视图插入数据:
(1)多表视图不能插入数据
(2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)
(3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)
视图删除数据
(1):多表视图不能删除数据
(2):单表视图可以删除数据,也会影响到基表
视图更新数据
(1):单表视图,多表视图都可以更新数据
更新限制:with check option
例如:create view my_v1 as select * from student where age>30 with check option;
表示视图数据的来源都是年龄大于30的,with check option 决定通过视图更新的时候,不能将已得到
数据age>30的学生 改成age<30 的.
那么:update my_v1 set age=20 where id=1; 就会报错 不允许改 因为做了限制
五.函数
1.概述
和存储过程具有类似的功能。
函数和存储过程的区别
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有一个返回，适合做处理数据后返回一个结果
函数：包括内置函数，和自定义函数
2.函数创建和调用的语法
DELIMITER $$
CREATE
FUNCTION 函数名(参数列表)
RETURNS 返回类型
BEGIN
函数体;
END$$
DELIMITER ;
注意事项:
1、参数列表包含两部分，参数名 参数类型，没有IN/OUT/INOUT;
2、函数体肯定有return语句，如果没有会报错;
调用语法
SELECT 函数名(参数列表);
函数和存储过程的区别
1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
2.存储过程有in out inout 这几个参数类型 函数的参数全是用来收实参
案例1:计算user中的用户数量
DELIMITER $$
CREATE
FUNCTION `mydb2`.`test1`()
RETURNS INT
-- 统计user中的用户数量
BEGIN
DECLARE num INT DEFAULT 0;
SELECT COUNT(*) INTO num FROM USER ;
RETURN num;
END$$
DELIMITER ;
-- 函数的调用
SELECT test1();
案例2:计算两数之和
DELIMITER $$
CREATE
FUNCTION `mydb2`.`test3`(a INT,b INT)
RETURNS INT
-- 统计user中的用户数量
BEGIN
DECLARE num INT DEFAULT 0;
SET num=a+b;
RETURN num;
END$$
DELIMITER ;
-- 函数的调用
SELECT test3(10,2);
案例3：根据id返回用户姓名
DELIMITER $$
CREATE
FUNCTION `mydb2`.`test4`(id INT)
RETURNS VARCHAR(8)
-- 根据id返回用户姓名
BEGIN
DECLARE uuname VARCHAR(8);
SELECT username INTO uuname FROM USER WHERE user.`id`=id;
RETURN uuname;
END$$
DELIMITER ;
函数的查看和删除
-- 四、函数的查看和删除
SHOW CREATE FUNCTION myFunction3;
DROP FUNCTION myFunction1;
六.DCL 数据库的权限
1.权限的分配
-- mysql数据库权限问题：root ：拥有所有权限（可以干任何事情）
-- 权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表
-- 如何修改mysql的用户密码？
-- password:
password('123456')
md5加密函数(单向加密)
SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
-- mysql数据库，用户配置 : user表
USE mysql; -- 使用数据库
Select password(‘root’ ) 查询用户密码
SELECT * FROM USER; -查询数据库用户
-- 修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
-- 分配权限账户
权限： select insert delete update drop create/ 或,all
@ 后面可以是localhost 也可以是ip 也可以给% 那%代表任意一台计算机都可以连接上来
语法
GRANT 权限 ON 数据库名.某张表名 TO '用户名'@'localhost' IDENTIFIED BY '123456';
GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
注意分配多个权限用逗号隔开
GRANT DELETE,SELECT,UPDATE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
--方式2：分配账户和权限
INSERT INTO USER
(HOST, USER, PASSWORD,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
删除用户
Delete FROM user Where User='eric' and Host='localhost';
--- 数据库备份和还原 注意备份还原不需要登陆数据库
备份:mysqldump -uroot -p day02 > d:/back.sql
C:\Documents and Settings\Administrator>mysqldump -uroot -p day02 > d:/back.sql
Enter password: ****
恢复：mysql -uroot -p day02 < d:/back.sql
注意恢复之前 先创建跟你原来一样的名称的数据库 相当于一个空的数据库,然后再还原
C:\Documents and Settings\Administrator>mysql -uroot -p day02 < d:/back.sql
Enter password: ****
2.远程登录数据库
1.切换库
use mysql;
2.授权远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
3.刷新
flush privileges;
4.然后退出mysql
exit;
5.启动服务
service mysqld start
6.查看状态
service mysqld status
7.远程登录：mysql -h 192.168.17.123 -P 3306 -u root -p123456
3.数据库表设计
数据库设计
引入
需求分析 - 需求分析师 -》 原始需求- > 抽取业务模型
图书模型：图书名称，版本号，作者，出版社
学生模型： 学号，学生姓名 手机号码
......
角色：学生 老师，图书管理员
《需求说明书》
需求设计 -
概要设计：
抽取实体：业务模型 -> 实体模型（java 类 c++类）内存
class Book{ name, bookNo,author }
数据库设计:
业务模型/实体模型 - > 数据模型 (硬盘)
数据库表设计
问题： 如何设计？
详细设计
类详细，属性和方法
三大范式
设计原则： 建议设计的表尽量遵守三大范式。
第一范式： 要求表的每个字段必须是不可分割的独立单元
student : name -- 违反第一范式
张小名|狗娃
sutdent ： name old_name --符合第一范式
张小名 狗娃
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
employee（员工）: 员工编号 员工姓名 部门名称 订单名称 --违反第二范式
员工表：员工编号 员工姓名 部门名称
订单表： 订单编号 订单名称 -- 符合第二范式
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
员工表： 员工编号（主键） 员工姓名 部门编号 部门名 --符合第二范式，违反第三范式 （数据冗余高）
员工表：员工编号（主键） 员工姓名 部门编号 --符合第三范式（降低数据冗余）
部门表：部门编号 部门名
下一篇： HTML禁止网页文字复制，禁止查看源代码
推荐阅读
-
MySQL 系列（三）你不知道的 视图、触发器、存储过程、函数、事务、索引、语句
-
Mysql-自带的一些功能，基本用法（视图，触发器，事务，存储过程，函数，流程控制）
-
mysql 查询数据库中的存储过程与函数的语句
-
mysql 查询数据库中的存储过程与函数的语句_MySQL
-
[MySQL] 存储过程、函数、触发器和视图的权限检查_MySQL
-
[MySQL] 存储过程、函数、触发器跟视图的权限检查
-
MySQL中的存储过程+触发器+视图+函数+DCL数据库权限
-
【数据库学习笔记】MySQL_03_存储过程,触发器,权限,视图,函数
-
MySQL数据库的视图、存储过程、触发器、函数的学习
-
MySQL 之【视图】【触发器】【存储过程】【函数】【事物】【数据库锁】【数据库备份】...