MySQL DAY5
回顾
数据行
临时表
(select * from tb where id>10)
指定映射
select id,name,1,sum(x)/count()
条件
case when id>8 then xx else xx end
三元运算
if(isnull(xx),0,1
补充
左右连表join
上下连表union
自动去重:union
select id,name from tb1
union
select num,sname from tb2
不去重:union all
select sid,sname from student
UNION ALL
select sid,sname from student
基于用户权限管理
参考表结构:
用户信息
id username pwd
1 alex 123123
权限
1 订单管理
2 用户劵
3 Bug管理
....
用户类型&权限
1 1
1 2
2 1
3 1
程序:
用户登录
基于角色的权限管理
用户信息
id username pwd role_id
1 alex 123123 1
2 eric 123123 1
权限
1 订单管理
2 用户劵
3 Bug管理
....
角色表:
1 IT部门员工
2 咨询员工
3 IT主管
角色权限管理
1 1
1 2
3 1
3 2
3 3
===>
1. 基于角色的权限管理
2. 需求分析
视图
创建视图
--格式:CREATE VIEW 视图名称 AS SQL语句
CREATE VIEW v1 AS
SELECT nid,
name
FROM
A
WHERE
nid > 4
删除视图
DROP VIEW v1
修改视图
-- 格式:ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
SELECT A.nid,
B.name
FROM
A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
A.id > 2
AND C.nid < 5
使用视图
select * from v1
触发器
创建基本语法
trigger :触发器
create trigger 名称 时间序列 on 表名 for each now
begin
...
end
# 插入前
CREATE TRIGGER t1 before INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER t1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER t1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER t1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER t1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER t1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
插入前触发器(源码)
if
后接end if
delimiter //
CREATE TRIGGER t1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. NAME == 'alex'
THEN
INSERT INTO tb2 (NAME)
VALUES
('aa')
END if
END//
delimiter ;
插入后触发器
delimiter //
CREATE TRIGGER t1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
IF NEW. num = 666 THEN
INSERT INTO tb2 (NAME)
VALUES
('666'),
('666') ;
ELSEIF NEW. num = 555 THEN
INSERT INTO tb2 (NAME)
VALUES
('555'),
('555') ;
END IF;
END//
delimiter ;
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。
删除触发器
DROP TRIGGER t1;
使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。
insert into tb1(num) values(666)
函数
内置函数
执行函数
select curdate();
blog | title | ctime |
---|---|---|
1 | asdf | 2020-5-20-5:20 |
2 | asdf | 2020-5-21-5:20 |
3 | asdf | 2019-5-21-5:20 |
4 | asdf | 2019-5-20-5:20 |
select ctime,count(1) from blog group ctime
以时间年月日时分为组分组
select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")
只以时间年月进行分组
更多函数方法见
点击这里可见
自定义函数(有返回值):
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int default 0;
set num = i1 + i2;
return(num);
END \\
delimiter ;
SELECT f1(1,100);
删除函数
drop function f1;
执行函数
# 获取返回值
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;
# 在查询中使用
select f1(11,nid) ,name from tb2;
存储过程
保存在MySQL上的一个别名 => 一坨SQL语句
别名()
用于替代程序员写SQL语句
方式一:
MySQL: 存储过程
程序:调用存储过程
方式二:
MySQL:。。
程序:SQL语句
方式三:
MySQL:。。
程序:类和对象(SQL语句)
创建存储过程
无参
-- 创建存储过程
delimiter //
create procedure p1()
BEGIN
select * from t1;
END//
delimiter ;
-- 执行存储过程
call p1()
对于存储过程,可以接收参数,其参数有三类:
in
仅用于传入参数用
out
仅用于返回值用,如果传入的不是变量会报错
inout
既可以传入又可以当作返回值,传入什么就是什么
有参
-- 创建存储过程
delimiter \\
create procedure p1(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;--任何变量都要先声明
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 + 100;
end\\
delimiter ;
-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
事务
伪代码
delimiter //
create procedure p4(
out status int
)
begin
1.声明如果出现异常则执行{
set status = 1;
rollback
}
开始事务
--zx账户减去100
--zyx账户加90
--top账户加10
结束
set status = 2;
end //
delimiter ;
代码
delimiter \\
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT;
-- SUCCESS
set p_return_code = 2;
END\\
delimiter ;
游标
delimiter //
create procedure p6()
begin
declare row_id int; -- 自定义变量1
declare row_num int; -- 自定义变量2
declare done INT DEFAULT FALSE;
declare temp int;
declare my_cursor CURSOR FOR select id,num from A;
declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor;
xxoo: LOOP
fetch my_cursor into row_id,row_num;
if done then
leave xxoo;
END IF;
set temp = row_id + row_num;
insert into B(number) values(temp);
end loop xxoo;
close my_cursor;
end //
delimiter ;
动态执行SQL(防SQL注入)
伪代码
delimiter //
create procedure p7(
in tpl varchar(255),
in arg int
)
begin
1. 预检测某个东西 SQL语句合法性
2. SQL =格式化 tpl + arg
3. 执行SQL语句
set @xo = arg;
PREPARE xxx FROM 'select * from student where sid > ?';
EXECUTE xxx USING @xo;
DEALLOCATE prepare prod;
end //
delimter ;
call p7("select * from tb where id > ?",9)
?表示占位符,9代替它
代码
delimiter \\
CREATE PROCEDURE p8 (
in nid int
)
BEGIN
set @nid = nid;
PREPARE prod FROM 'select * from student where sid > ?';
EXECUTE prod USING @nid;
DEALLOCATE prepare prod;
END\\
delimiter ;