关系型数据库MySQL-联合查询-视图-用户权限

关系型数据库MySQL

今天的分享的内容有

 1. 联合查询union 		
 2. 视图view 		
 3. 用户权限管理 		
 4. 事务
 5. 联合查询

1.1 用途

将不同表格内，检索出来的数据放到一个结果集中！

select `student_name` from `student` union select `class_name` from `class`;

不是所有的语句的结果集都可以连起来，需要满足以下要求：

如果使用union连接多个查询，必须保证每个查询的字段数相同
列名由第一个查询的字段名决定
union不要求字段类型相同

1.2 结果集选项

1.2.1 重复数据

所有数据

select * from `student` union all select * from `student`

去除结果集中重复数据

select * from `student` union distinct select * from `student`
这是一个默认选项，也就是说你不写distinct也表示去重。

1.2.2 排序

如果有子句中带有order by必须将所有子句外面添加括号

(SELECT * FROM `student` ORDER BY `cid`) UNION (SELECT * FROM `student`)

1.2.3 巩固

将1班的学生升序然后与2班的学生降序组成一个结果集

(select * from `student` where `cid`=1 order by `sid` asc ) union (select * from `student` where `cid`=2 order by `sid` desc);

 
但是很明显，2班并没有按sid降序！ 
如果需要针对子句做独立的排序，需要使用limit子句配合order by一起使用才能使order by生效

(SELECT * FROM `student` WHERE `cid`=1 ORDER BY `sid` ASC LIMIT 100) UNION (SELECT * FROM `student` WHERE `cid`=2 ORDER BY `sid` DESC LIMIT 100)

2. 视图view

如果要得到比赛列表，需要进行表连接操作，并且会使用到两次连接，比较麻烦，如果有一个临时表能够保存比赛结果的信息，而我们可以直接查询临时表的内容，将会极大的提高效率。这就是视图。 
视图其实是一张虚拟的表，内部由一条查询语句实现。用于简化业务逻辑或者隐藏真实表结构的一种技术

2.1 创建视图

基本语法：

create view `视图名称` AS 形成视图的查询语句！

视图，就是虚拟表（但是客户端是区分不出来虚拟或者是真实表）

现在这个视图，就可以作为查询的数据源了：

	CREATE VIEW `student_class` AS SELECT * FROM `student` LEFT JOIN `class` USING(`cid`);
SELECT * FROM `student_class`; 
#执行效果等同于
	SELECT * FROM (SELECT * FROM `student` LEFT JOIN `class` USING(`cid`)) AS `t`

2.2 查看视图

show create view `视图名字`

查看有哪些视图

USE `information_schema`;
SELECT * FROM `views`;

2.3 删除视图
drop view 视图名;
2.4 修改视图
alter view 视图名字 AS 新的查询语句
2.5 使用细则
视图内是不真实保存数据的，只保存一条需要执行的sql语句，相当于执行了一次子查询！
视图的使用场景

简化业务逻辑，将常用的复杂的业务逻辑建立成一个视图，便于后期使用
隐藏真实表结构
如果真实表结构发生了变化，客户端代码不用修改： 
比如原来的height字段改为了stu_height，只需要更改视图即可不需要更改客户端SQL语句

2.6 视图的算法（了解）

我们来看一个案例：

分别使用子查询和视图获得每个班中身高最高的学生信息(假设身高无重复)

子查询：

select * from (select * from `student` order by `height` desc) group by `cid`;

使用视图：

可以看到两个答案完全不同，这时由于视图使用的算法导致的

其实完整的创建视图的语句是这样的：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement

ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。 
如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。 
对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。 
对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。 
对于UNDEFINED，MySQL自己选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE， 
这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

2.6.1 merge算法
select * from (select * from student order by height desc) group by cid;
等同于：
SELECT * FROM stu AS t GROUP BY cid ORDER BY height DESC
2.6.2 temptable算法
select * from (select * from student order by height desc) group by cid;
等同于：
select * from (select * from student order by height desc) group by cid;

2.7 练习

创建一个“账户”视图：其中只有用户名和密码信息，并按用户名有序排列。

3. 用户权限管理
3.1 mysql用户

mysql中的用户，都存储在系统数据库mysql中 user 表中。

字段	释义	备注
host	允许登录的“位置”	localhost表示该用户只允许本机登录，可以是ip地址，比如:192.168.3.4
user	用户名	
password	密码	是通过mysql的password()函数加密之后的密码。比如：password(“123”) *23AE809DDACAF96AF0FD78ED04B6A265E05AA257

用户的登录，必须这3部分都满足条件才可以

3.1.1 创建用户
create user ‘用户名’@‘允许登录位置’ identified by ‘密码’

其中%表示除本机外的所有主机

3.1.2 删除用户
drop user ‘用户名’@‘允许登录位置’
3.1.3 修改密码

修改自己的密码

set password = password('密码')

修改其他用户密码

set password for '用户名'@'登录位置' = password('密码')

3.2 用户权限

mysql中的权限，其实就是一些“单词”（名词）而已，每个单词，就代表了某种可以进行（允许执行）的操作。主要如下 

别一厢情愿的以为ALL表示所有权限，其实人家是表示除了grant之外的“所有权限”

3.2.1 授予用户权限

语法

grant 权限列表 on `库名`.`下级对象名` to '用户名'@'登录位置' [identified  by  '密码']

权限列表 就是那些表示权限的“单词”，比如：select、delete、update等，多个权限用逗号分开

grant  select  on ...
grant  select, delete,create  on ...
grant  all [privileges]  on ... # 表示赋予该用户在该对象上的所有权限


库名就是数据库的名字


下级对象名是指一个数据库的下级“单位”，比如表、视图、存储过程、函数。比如：php38.product php38.学生表 php38.v1（这是一个视图名)
除此之外还有两个特殊写法：
*.* ：代表本系统中的所有数据库的所有下级单位
XX库名.*：表示某个数据库中的所有下级单位


identified by可以省略，也可以写出，此时就是修改该用户的密码。但是如果该用户之前不存在，此时其实就是创建该用户！并且，此时identified  by必须写出。

添加权限

GRANT SELECT ON *.* TO 'kunx'@'%' IDENTIFIED BY '123456'
GRANT UPDATE ON *.* TO 'kunx'@'%' IDENTIFIED BY '123456'

上面语句的意思是先赋予select权限，然后再此基础上添加update权限，由于前后两次密码一致，所以不会修改密码

3.2.2 查看用户权限
show grants for ‘用户名’@‘登陆位置’
3.2.3 取消授权
revoke 权限列表 on 库名.下级对象名 from ‘用户名’@‘登录位置’

取消该用户的update权限，其它权限不受影响
REVOKE UPDATE ON *.* FROM 'kunx'@'%'

3.2.4 注意

对赋予或取消的权限并不能让其立即生效，而是需要该用户“重新”登录后才生效。 
即：如果一个用户已经登录，而且没有退出，修改其权限，不影响该用户继续使用之前权限的（即没有立即生效）。 
要让修改的权限“立即”生效，使用如下语句:

**FLUSH PRIVILEGES;**

4. 事务

4.1 什么是事务

一段连续的不可分割sql的组成一个事务，事务内的所有的sql语句，要么集体成功，要么集体失败，如果存在部分成功，但是另一部分失败，应该将成功对数据的影响恢复（去掉）

事务的目的是在 mysql数据库的层面保证一个事务（一件事）内的所有的sql都处理统一的状态（成功 or 失败）。

事务是数据完整性（安全性）的一个保证技术

4.2 案例引入

比如转账的操作，张三给李四转了100块钱，可以分为两步：

张三的余额-100
李四的余额+100

两个步骤应当是有依赖关系的，任何一个失败，都意味着整个过程的失败。

此时就可以使用 事务 保证借钱一定能够成立（在没有成功的情况下不会产生额外的错误影响）

4.3 使用事务的前提

数据表必须是 innodb 存储引擎的表

4.4 使用步骤
4.4.1 开启事务
start transaction;

一旦开启事务，后续的sql语句的执行的结果，不会马上持久化到表中的。而是暂时保存在一个临时区域，只有自己能看到，别人看不到！

start transaction 可以使用 begin 代替

4.4.2 执行修改

执行更新操作：

update ...
update ...

此时查询的结果，只能够在当前的连接中被看到，其他的连接（会话）是不能看到修改的

4.4.3 检查执行结果

当事务中所有的sql都执行完毕时，此时，判断当前事务中的所有的sql是否都执行成功了。 
在命令行可以根据是否看到Query OK来判断，在php中可以通过mysqli_query()的返回值是否是false来进行判断。

4.4.4 提交或者回滚

提交
如果全部成功：意味着事务内所有的任务都正确完成，应该将事务中所处理的数据持久化（同步）到真实的表中

**commit;**

此时在其他链接（客户端）查看的话会发现数据也变化了

回滚
如果出现了错误的操作，应该将其他生成的影响撤销，回到事务开始前的状态

rollback;
4.5 注意

无论提交还是回滚，当前事务都已经结束，下次如果需应该再次开启事务

4.6 基本原理

事务安全型存储引擎（innodb），在执行sql语句时分成两个步骤：

执行（执行结果）
提交（将执行结果持久化到表中）

这会带来一个问题，那就是数据的写操作会变慢，这就是为什么在执行数据批量插入的时候为什么innodb比myisam慢。所以在执行大量数据插入的时候可以先关闭自动提交，然后执行完毕后再提交。

4.6.1 自动提交

默认模式下，执行的sql语句是立即生效的，也就是自动提交（autocommit），此模式下无需手动指定commit即可使操作生效。 
比如：

update student set `money`=`money`+10 where `sid`=1;

此时打开多个数据库连接查询学号为1的钱数都是一样的，也就是说这个更新是立即生效的。

更改模式
自动提交可以通过mysql的autocommit变量来获取和设置。

show variables like 'autocommit';
set autocommit=0; # 关闭自动提交模式
set autocommit=1; #开启自动提交模式

关闭自动提交模式，写操作不会立即生效，而是需要手动执行commit

4.6.2 事务日志

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。

简单的说就是，innodb在写操作的时候其实是操作的内存中的数据，然后在合适的时候把数据写回到磁盘文件中，但是为了避免突然崩溃，所以把修改操作记录到了日志中。

事务日志文件其实这个是存放在了数据库文件夹下的data目录（默认是这个，当然可以在my.cnf中修改）。 

4.7 事务的特点

ACID：原子性，一致性，隔离性，持久性

原子性（Atomicity）

事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务前后数据的完整性必须保持一致。也就是说在事务执行的过程中数据不会被外界干扰，可以做一个实验，开启一个事务： 
UPDATE test.stu SET height=100 WHERE sid=1; 
不提交 
打开一个新的连接，执行 
UPDATE test.stu SET height=height+5 WHERE sid=1; 
你会发现后面的语句一直在执行中，没有执行结束，原因就是第一个操作还没有提交，所以别的连接无权操作该行数据（行写操作被锁住了，也就是常说的行锁）

隔离性（Isolation）

多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间的数据要相互隔离。

持久性（Durability）

一个事务一旦被提交，它对数据库中的数据改变就是永久性的。