13、14、15、视图、用户管理、触发器、游标

十三、视图

需求引出

现在我们这里有一张表，现在要将这个表中的某些数据提供给别人，如果全部提供给别人，可能有些信息对别人没用对于别人来讲就不太友好。或者是有些信息不能提供给别人。那怎么办呢？

只需要将表中的某些信息提供给别人。-》视图

视图是一个虚拟表，里面的内容来自于真实表，视图也包含行列，看起来就是一张表。

1、create view视图名as select语包
2、alter view视图名as select语句 --更新成新的视图
3、SHOW CREATE VIEW视图名
4、drop view视图名1，视图名2

这里的基表还用我们上面常用的emp表

代码演示

-- 视图的使用
-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息

-- 创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno, ename, job, deptno FROM emp;   
-- 查看视图
DESC emp_view01

SELECT * FROM emp_view01;  -- 图1
SELECT empno, job  FROM emp_view01;

-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW emp_view01;


-- 视图的细节

-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) 
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

-- 修改视图 会影响到基表

UPDATE emp_view01 
	SET job = 'MANAGER' 
	WHERE empno = 7369
	
SELECT * FROM emp; -- 查询基表


SELECT * FROM emp_view01

-- 修改基本表， 会影响到视图

UPDATE emp 
	SET job = 'SALESMAN' 
	WHERE empno = 7369

-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
DESC emp_view01

CREATE VIEW emp_view02
	AS
	SELECT empno, ename FROM emp_view01
	
SELECT * FROM emp_view02


-- 视图的课堂练习
-- 针对 emp ，dept 和 salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]

/*
	分析: 使用三表联合查询，得到结果
	将得到的结果，构建成视图
		
*/
CREATE VIEW emp_view03
	AS
	SELECT empno, ename, dname, grade
	FROM emp, dept, salgrade
	WHERE emp.deptno = dept.deptno AND 
	(sal BETWEEN losal AND hisal) 

DESC emp_view03
SELECT * FROM emp_view03

视图的好处

➢ 安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
➢ 性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
➢ 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

十四、用户管理

-- Mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用

-- 1. 创建新的用户
-- (1) 'jack'@'localhost' 表示用户的完整信息 'jack' 用户名 'localhost' 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码
--     *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'jack'@'localhost' IDENTIFIED BY '123456'


-- 2. 删除用户
DROP USER 'jack'@'localhost'

-- 3. 登录 -- 创建完，就可以用这个账号登录了

-- root 用户修改 jack@localhost 密码, 是可以成功.
SET PASSWORD FOR 'jack'@'localhost' = PASSWORD('123456')
-- 修改自己密码
SET PASSWORD = PASSWORD('abcdef')

用户限权

授权基本语法:
 grant权限列表 on 库.对象名 to ‘用户名’ @ ‘登录位置’ [identified by ‘密码’ ]
  说明:
   1,权限列表，多个权限用逗号分开
   grant select on…
   grant select, delete, create on …
   grant all [privileges] on … //表示赋予该用户在该对象上的所有权限

回收用户权限基本语法

 revoke 限权列表 on 库.对象名 from ‘用户名’@‘登录位置’

权限生效指令

 如果权限没有生效，可以执行flush privileges;

-- 演示 用户权限的管理

-- 创建用户 jack  密码 123 , 从本地登录
CREATE USER 'jack'@'localhost' IDENTIFIED BY '123'

-- 使用root 用户创建 testdb  ,表 news
CREATE DATABASE testdb
CREATE TABLE news (
	id INT ,
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;

-- 给 jack 分配查看 news 表和 添加news的权限
GRANT SELECT , INSERT 
	ON testdb.news
	TO 'jack'@'localhost'
	
-- 可以增加update权限
GRANT UPDATE  
	ON testdb.news
	TO 'jack'@'localhost'
	
	
-- 修改 jack的密码为 abc
SET PASSWORD FOR 'jack'@'localhost' = PASSWORD('abc');

-- 回收 jack 用户在 testdb.news 表的所有权限
REVOKE SELECT , UPDATE, INSERT ON testdb.news FROM 'jack'@'localhost'
REVOKE ALL ON testdb.news FROM 'jack'@'localhost'

-- 删除 jack
DROP USER 'jack'@'localhost'

-- 这里在默认情况下, jack 用户只能看到一个默认的系统数据库

-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限 
-- create user  xxx;

CREATE USER jack

-- 你也可以这样指定 
-- create user  'xxx'@'192.168.1.%'  表示 xxx用户在 192.168.1.*的ip可以登录mysql

CREATE USER 'smith'@'192.168.1.%'

-- 在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host值'

DROP USER jack -- 默认就是 DROP USER 'jack'@'%'

DROP USER 'smith'@'192.168.1.%'

alter

-- 添加主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY(字段);

-- 删除主键
alter table 表名  drop primary key ;

-- 添加唯一
ALTER TABLE 表名 ADD CONSTRAINT 唯一名 UNIQUE (字段);
-- 删除唯一
alter table 表名  drop index 唯一约束的名字 ;

-- 添加非空
alter table 表名 modify 字段名 varchar(50) not null default '无名氏';

-- 添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名称(外键字段);
-- 删除
drop foreign key 外键约束名;

十五、触发器、游标

1、触发器

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这 些事件包括INSERT、UPDATAE和 DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就 会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

比如一个公司来了一个员工，要把这个员工添加到公司的名单中，与此同时，也一定有一个部门人数会因此增加。

再比如，有一个vip制度，需要消费多少钱可以到达不一样的vip等级，所以vip等级就会随着消费的改变而改变。

只演示行触发器、语句触发器mysql不支持

/*
DELIMITER $$
CREATE TRIGGER 触发器名称 AFTER 增删改操作 ON 被操作的表 FOR EACH ROW
BEGIN
UPDATE 随之改变的表 SET 怎么改变 WHERE 条件;
END$$
DELIMITER ;
*/

-- 创建tri_demo触发器，在emp中添加一个员工信息，那么，部门表也会随之改变，即相关部门人数加一
-- 一旦在emp表上面进行 DELETE 操作，部门表就会自动随之改变
DELIMITER $$
CREATE TRIGGER tri_demo AFTER INSERT ON emp FOR EACH ROW
BEGIN
UPDATE dept SET deptnum=deptnum+1 WHERE deptid=new.deptid;
END$$
DELIMITER ;


-- 创建tri_demo2触发器，在emp中删除一个员工信息，那么，部门表也会随之改变，即相关部门人数减一
-- 一旦在emp表上面进行 DELETE 操作，部门表就会自动随之改变
DELIMITER $$
CREATE TRIGGER tri_demo2 AFTER DELETE ON emp FOR EACH ROW
BEGIN
UPDATE dept SET deptnum=deptnum-1 WHERE deptid=old.deptid;
END$$
DELIMITER ;


-- 创建tri_demo3触发器，在emp中修改一个员工信息，那么，部门表也会随之改变，即原来部门人数减一，新的部门人数加一
-- 一旦在emp表上面进行 UPDATE 操作，部门表就会自动随之改变
DELIMITER $$
CREATE TRIGGER tri_demo3 AFTER UPDATE ON emp FOR EACH ROW
BEGIN
IF old.deptid IS NOT NULL AND new.deptid IS NOT NULL THEN
UPDATE dept SET deptnum=deptnum-1 WHERE deptid=old.deptid;
UPDATE dept SET deptnum=deptnum+1 WHERE deptid=new.deptid;
ELSEIF new.deptid IS NOT NULL THEN
UPDATE dept SET deptnum=deptnum+1 WHERE deptid=new.deptid;
END IF;
END$$
DELIMITER ;

2、游标

游标是处理多行数据的，游标需要开启，抓取，关闭的

DELIMITER $$
CREATE PROCEDURE pro_ 05()
BEGIN
	DECLARE flag int DEFAULT 0;  -- 定义几个变量
	DECLARE `no` varchar(50);
	DECLARE `name` varchar(50) ;
	DECLARE sex char(3);
	
	DECLARE cur CURSOR for select studentno, studentname,sex from student;   -- select查看student中三列信息，游标指向这三列信息，初始时，游标指向这三列信息的第一行
	DECLARE CONTINUE HANDLER for not found set done=1;  -- 表示当游标指向，信息末尾时(最后一行时)，设置变量flag=1
	
	open cur;   -- 开启游标
		a: loop    -- 死循环赋值
			FETCH cur into `no`, `name`, sex;  -- 游标指的每一行都有三个信息，分别赋值
			if_ _done=1 then
				leave a;
			end if
			insert into stu_ bak values(`no`,`name`,sex);  -- 把得到的三个值添加到新的表中
		end loop;
		
		close_ cur;  -- 关闭游标
END$$
DELIMITER ;

call pro_ 05();