MySQL_DAY5

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 ;