MySQL-视图和MySQL管理

视图

基本介绍

1）视图是一个虚拟表，其内容由查询定义。同真实的表一样（映射关系，视图显示基表中希望显示的数据），视图包含列，其数据来自对应的真实表（基表）；

2）视图和基表的关系：①视图是根据基表创建的；②视图有列，数据来自基表；③可以通过视图修改基表的数据；④基表中数据的改变，也会影响视图中显示的数据；

基本使用

-- 创建视图
CREATE VIEW 视图名 AS SELECT语句

-- 修改视图（重新定义一个视图，原来的视图被更新）
ALTER VIEW 视图名 AS SELECT语句

-- 显示创建视图的指令
SHOW CREATE　VIEW　视图

--　删除视图
DROP VIEW 视图1，视图2

#视图的使用
-- 创建视图
CREATE VIEW emp_view 
		AS
		SELECT empno,  ename, job, deptno FROM emp;
		
-- 查看视图结构
DESC emp_view;

-- 查看视图中的数据
SELECT * FROM emp_view;
SELECT empno, deptno FROM emp_view;

-- 修改视图
ALTER VIEW emp_view
		AS
		SELECT   empno, job, deptno FROM emp;
		
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view;

-- 删除视图
DROP VIEW emp_view;

使用细节

1）创建视图后，到数据库查看对应视图只有一个视图结构文件（形式：视图名.frm）；

2）视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert、update、delete】；

3）视图中可以再使用视图；

-- 视图的数据变化会影响到基表，
-- 基表的数据变化也会影响到视图【insert、update、delete】
CREATE VIEW emp_view
		AS
		SELECT empno,  ename, job, deptno FROM emp;

UPDATE emp_view SET job = 'MANAGER' WHERE empno = 7369;

-- 查询基表
SELECT * FROM emp; -- 基表中 7369 SMITH的job变化为MANAGERR

UPDATE emp SET job = 'SALEMAN' WHERE empno = 7369;
-- 查询视图
SELECT * FROM emp_view; -- 视图中 7369 SMITH的job变化为SALEMAN

-- 视图中还可以使用视图(同样和基表有映射关系)
CREATE VIEW emp_view01
		AS
		SELECT empno, deptno FROM emp_view;
SELECT * FROM emp_view01;

应用实例

1）安全。一些数据表有着很重要的信息，有些字段是保密的，不能够让用户直接看到。这时就需要创建一个视图，在这张是图表中只保留了一部分字段。用户可以查询自己需要的字段，但是不能查看保密的字段；

2）性能。关系数据库的数据常常会分表存储，使用外键建立这些表之间的关系。这时，数据库查询通常会用到链接（JOIN）。这要做不但繁琐，效率也相对比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据；

3）灵活。如果系统中有一张旧的表，这张表由于涉及的问题即将被废弃。然而，很多应用都是基于这张表，不宜修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表中。在做少的改动的同时，也达到了升级数据表的目的。

测试代码

-- 三表合一
SELECT empno, ename, dname, grade
		FROM emp, dept, salgrade
		WHERE emp.`deptno` = dept.`deptno` AND
		(emp.`sal` BETWEEN salgrade.`losal` AND salgrade.`hisal`);
-- 视图映射到emp,dept,salgrade表
CREATE VIEW emp_view02
	AS
	SELECT empno, ename, dname, grade
		FROM emp, dept, salgrade
		WHERE emp.`deptno` = dept.`deptno` AND
		(emp.`sal` BETWEEN salgrade.`losal` AND salgrade.`hisal`);
DESC emp_view02;
SELECT * FROM emp_view02;

MySQL管理

MySQL用户管理

不同的数据库用户登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表、视图、触发器）都不一样；mysql中的用户都存储在系统数据库mysql的user表中。

重要字段

1）host：允许登陆的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址；

2）user：用户名；

3）authentication_string：密码，是通过mysql的password()函数加密后的密码。

创建用户

# 创建用户的同时要指定密码
CREATE USER '用户名' @'允许登陆位置' IDENTIFIED BY '密码';

删除用户

DROP USER '用户名' @'允许登陆位置';

-- 用户管理
-- 在项目开发时，可以根据不同的开发人员，赋予相应的mysql操作权限
-- mysql数据库管理人员（root），根据需要创建不同的用户，赋予相应的权限使用

-- 创建新用户
-- 用户名：pero01；登录IP：localhost；
-- 密码：123456（存放到user表中的是加密后的）
CREATE USER 'pero01' @'localhost' IDENTIFIED BY '123456';
 -- 查询数据库用户
 SELECT * FROM 
		mysql.`user`;
		
SELECT `host`, `user`, authentication_string FROM
		mysql.`user`;
-- 删除用户		
DROP USER 'pero01' @'localhost';

-- root 修改 pero01 的密码
SET PASSWORD FOR 'pero01'@'localhost' = PASSWORD('123456');  -- 成功

修改用户密码

# 修改自己的密码
SET PASSWORD = PASSWORD('密码');

# 修改他人密码（需要有修改用户的密码权限）
SET PASSWORD FOR '用户名' @'登陆地址' = PASSWORD('密码');

-- 修改密码
SET PASSWORD = PASSWORD('abcdef');

-- pero01修改root密码
-- 报错：Access denied for user 'pero01'@'localhost' to database 'mysql'
-- set password for 'root'@'localhost' = password('123456');

MySQL权限管理

权限	意义
ALL[PRIVILEGES]	设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARY TABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER，DROP USER，RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX 和 DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对拥有的SELECT权限的表使用LOCK TABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATION CLIENT	允许用户询问从属服务器或主服务器地址
REPLICATION SLAVE	用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT	允许使用SELECT
SHOW DATABASES	SHOW DATABASES 显示所有数据库
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	允许使用CHANGE MASTER，KILL，PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许连接(一次)，即使已达到了max_connections。
UPDATE	允许使用UPDATE
USAGE	"无权限"的同义词
GRANT OPTION	允许授予权限

用户授权

#基本语法
GRANT 权限列表 on 库.对象名 to '用户名' @'登陆位置' [identifued by '密码'];

细节说明：

1）权限列表，多个权限用逗号分开

grant select on......

grant select ,delete, create on......

grant all [privileges] on......        //表示赋予该用户在该对象上的所有权限

2）特别说明

*.*：代表本系统中的所有数据库的所有对象（表，视图，存储过程）；

库.*：表示某个数据库中的所有数据对象（表，视图，存储过程）；

3）identified by可以省略，也可以写出；

①如果该用户存在，就是修改该用户的密码；

②如果该用户不存在，就是创建该用户。

回收用户权限

#基本语法
RECOKE 权限列表 ON 库.对象名 from '用户名' @'登陆位置';

权限生效指令

#如果权限没有生效，可以使用如下指令：
FLUSH PRIVILEGES;

测试代码

-- 创建用户 nano
CREATE USER 'nano' @'localhost' IDENTIFIED BY '123456';

-- 使用root用户创建数据库和表
CREATE DATABASE testdb;

CREATE TABLE news(
		id INT,
		`name` VARCHAR(32));

-- 在表中添加数据
INSERT INTO news VALUES (1,'北京新闻');
SELECT * FROM news;

-- 给用户nano分配查看和添加权限
GRANT SELECT , INSERT 
		ON testdb.news 
		TO 'nano' @'localhost';

-- 给用户nano分配修改和删除权限
GRANT UPDATE, DELETE
		ON testdb.`news`
		TO 'nano' @'localhost';
		
-- 修改nano的密码为pero
SET PASSWORD FOR 'nano' @'localhost' = PASSWORD('pero');

-- 回收权限
REVOKE ALL ON testdb.`news` FROM 'nano' @'localhost';

-- 删除nano
DROP USER 'nano' @'localhost';

细节说明

1）在创建用户的时候，如果不指定HOST，则为%，%号表示所有IP都有连接权限；

2）也可以这样指定

CREATE USER '用户名' @'192.168.1.%' 表示用户在192.168.1.*的ip可以登录mysql

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

-- 在创建用户的时候，如果不指定HOST，则为%，%号表示所有IP都有连接权限；
CREATE USER tom;
SELECT `host`, `user` 
		FROM mysql.`user`;  -- host为%，所有IP都有连接权限

-- 创建用户
CREATE USER 'jack'@'192.168.1.%';

-- 删除用户
DROP USER tom;
DROP USER 'jack'@'192.168.1.%';