mysql 视图，存储过程，游标，触发器，用户管理简单应用

时间：2024-05-02 03:04:15

mysql视图——是一个虚拟的表，只包含使用时动态查询的数据

　　　　优点：重用sql语句，简化复杂的SQL操作，保护数据，可以给用户看到表的部分字段而不是全部，更改数据格式和表现形式

　　　　规则： 名称唯一，必须有创建视图访问权限，order by可以用与视图，但是检索视图的sql语句中包含order by 将覆盖视图中的order by

　　　　使用视图：

create view viewName as  select * from tablename    --创建视图

show create view viewName        --查看创建视图的语句

drop view  viewName   --删除视图

　　对视图更新会更新视图的基表，也并非所有的视图都可以更新若视图中包含 分组（group by 和 having），联结，子查询，并及（union ） ，聚集函数（min(),count(),sum），disinct，导出计算列

存储过程 —— 创建存储过程

delimiter //　　　　　　　　　　　　　　--改变默认的语句结束符

create procedure userName ()　　　　　　--创建一个名称为 userName的存储过程 不带参数

begin

    select name from user;

end //

delimiter ;　　　　　　　　　　　　　　　　　　--
call userName();　　　　　　　　　　--调用存储过程
drop  procedure  userName;    -- 删除存储过程




带参数的存储过程 以及调用




--带输入参数，查询指定名称人的年龄

delimiter //

CREATE PROCEDURE getUserAge(in userName VARCHAR(20))

BEGIN

        SELECT age from user where name = userName;

END //

delimiter ;

call getUserAge('zhangsan');

--带输出参数，查询指定名称人的年龄

delimiter //

CREATE PROCEDURE getUserAge2(in userName VARCHAR(20),inout userAge int)

BEGIN

        SELECT age into userAge from user where name = userName;

END //

delimiter ;

call getUserAge2('zhangsan',@userAge);        --调用执行函数，执行完后给参数赋值

select @userAge                             --查询被赋值的参数的结果




带参数-带判断的复杂存储过程------  关键字  declare  xxName  存储过程内部定义变量




-- 带参数 以及内部参数  带判断的复杂存储过程

delimiter //

CREATE PROCEDURE getUserAge(in userName varchar(20),in flag  boolean,inout userAge int)

BEGIN

    declare  myAge int ;  -- 定义内部参数

    SELECT age INTO userAge  from user where name=userName  limit 1;

    IF flag THEN

        SELECT MAX(age) INTO myAge  from user ;

    END IF;

    select myAge into userAge;  --内部参数赋值给外部参数

END //

delimiter ;

call getUserAge('zhangsan',true,@userAge);   --

select @userAge 
show create procedure getUserAge;  -- 查看存储过程的定义




--游标，某个查询结果集内部行的指针，只能在存储过程中使用


　　　　规则：  必须提前声明游标，然后打开游标，使用后必须结束游标，　　


　　　　　　　　




delimiter //

CREATE PROCEDURE saveAge()

BEGIN

    declare  uName varchar(20) //                 --游标使用的内部变量

    declare uAge int//

    declare done boolean default false//        --游标使用的内部变量

    declare  myAge cursor for                    --定义并使用游标

    SELECT age,name from user //                --查询所有的年龄和姓名

    declare continue HANDLER for not found set done = true;    -- 当游标指向下一行为空是给done变量赋值为true

    open myAge//                                --打开游标

        repeat                                    --循环开始

            FETCH next myAge into uAge,uName//                --获取游标指向行的数据

            if uAge > '' then                                --如果年龄大于25那么就存档到user1表中

                insert into user1(name) values(uName)//

            end if//

        until done end repeat  //                        --当变量done为ture的时候结束循环

    close myAge//                                --关闭游标

END //

delimiter ;




触发器——当达到某个执行条件进行自动处理的sql代码 ，只有 insert，delete，update 语句可以定义触发器


　　　　定义触发器的要求：必须是实体表（不支持视图，临时表），触发的条件（只有 insert，delete，update 语句可以定义触发器），什么时候触发（指定语句之前还是之后）


　　　　触发器失败：MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚，但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。


　　　　删除触发器：drop  trigger  triggerName；


　　　　insert 触发器：在insert触发器代码内可以引用一个名为new的虚拟表，访问被插入的行；对于before insert触发器中，new的值也可以被更改，对于自增列则，new在insert执行之前包含0，在insert执行知乎自动包含新生成的值




--AFTER触发之后，before触发之前  new虚拟表在insert触发器中使用，old虚拟表在update或delete语句的触发器中使用

--insert  触发器

delimiter //

create trigger newUser AFTER  insert on user for each row

begin

    insert into user1(name) values(NEW.name);

end;

--delete触发器 ，删除时存在一个OLD虚拟表 以供使用

create trigger deleteUser before  delete on user for each row

begin

    insert into user1(name) values(OLD.name);        --删除的时候将名称存档到user1表中

end;

--update触发器

create trigger updateUser before  update on user for each row

begin

    insert into user1(name) values(OLD.name);   --发生修改时，将被修改之前的名称存档到user1表中

end;




mysql 访问控制 ——mysql内部以user表管理当前数据库所有的用户账户信息


　　　　




--创建登录用户名为zhangsa 密码为123456  也可以直接用insert  语句直接创建用户（不建议使用）

create user zhangsan identified by '';

--删除zhangsan这个用户

drop user zhangsan ;

--查看用户权限

show grants for zhangsan;

--修改某个用户的权限 多个权限用分号隔开

grant select,update on test.*  to zhangsan;   -- 给zhangsan用户授予查看和更新的权限

--查看 用户权限操作记录

show grants for zhangsan

--grant的反操作符  revoke 取消用户的指定权限，该权限必须存在 否则将报错

revoke update on test.* from zhangsan   -- 取消zhangsan的修改权限  

---授权的简化操作

        整个服务器  grant all  和revoke all

        整个数据库  on datebaseName.*

        特定的表  on datebaseName.tablename

        特定的列，以及特定的存储过程

--修改某个用户的登录密码

set password for zhangsan = Password('');  --新密码必须使用password函数进行加密处理

--修改当前登录用户的密码

set password = Password('xxxx');




　　
相关文章