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

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 > '25' 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 '123456';
--删除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('135246');  --新密码必须使用password函数进行加密处理
--修改当前登录用户的密码
set password = Password('xxxx');

 

  

 

posted @ 2017-12-17 20:21  迷~途  阅读(184)  评论(0编辑  收藏  举报