25、MySQL:关系型数据库管理系统

一、不相关子查询

一条SQL语句含有多个select，先执行子查询，再执行外查询；子查询可以独立运行。称为不相关子查询。

• 单行子查询：子查询的结果是1条记录
• 多行子查询：子查询的结果是多条记录

  • 子串询的结果是多条记录，此时不能使用=，<  >  需要借助 ANY  ALL in来实现

二、相关子查询

子查询不可以独立运行，并且先运行外查询，再运行子查询。

• 好处：简单   功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
• 缺点：稍难理解

三、索引和事务

1、索引

• 作用：提高了查询的速度
• 使用：
  • 查看索引：show index from 表名
  • 创建索引：create index 索引名 on 表名(列名)
  • 删除索引：
    • drop index 索引名 on 表名
    • alter table 表名 drop index 索引名
  • 修改索引：先删除，再添加

2、事务：

• 概念：
  
  指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。
• 特性：
  
  一个操作序列要成为事务，必须满足事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性简称为ACID特性。

• 使用：

  • start transaction；
  • commit
  • rollback

• 事务的隔离级别：
  
  事务的隔离级别从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作。​​​​​​

  • 查看隔离级别：select @@transaction_isolation;

  • 设置隔离级别：set session transaction isolation level 隔离级别名称;

三、视图和存储过程

1、视图

• 概念：
  
  视图是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。
• 好处：
  • 简化用户操作
  • 对机密数据提供安全保护
• 操作：
  • 创建或替换单表视图：create view 视图名 as 查询语句
  • 删除视图：drop 试图名

2、存储过程

• 概念：
  
         存储过程是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。
• 优点：
  • 提高执行性能。
  • 可减轻网络负担。
  • 可将数据库的处理黑匣子化。
• 使用：​​​​​​​

定义：
create  procedure myproc2(in name varchar(20),out num int(4))  
    begin  
     if name is null or name = '' then  
        select * from emp;  
     else   
        select * from emp where ename like CONCAT("%",name,"%");  
     end if;  
    select found_rows() into num;  
end 

运行：
call myproc2("AR",@num);

删除：
drop procedure myproc1;  

四、用户管理

1、用户管理：

• 创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';

2、权限管理：

• 授予权限：grant privileges ON dbname.tablename TO 'username'@'host'
• 撤销权限：revoke privileges on dbname.tablename from 'username'@'host'

3、角色管理：

• CREATE ROLE 角色创建
• DROP ROLE 角色删除
• GRANT 为用户和角色分配权限
• REVOKE 为用户和角色撤销权限
• SHOW GRANTS 显示用户和角色的权限
• SET DEFAULT ROLE 指定哪些帐户角色默认处于活动状态
• SET ROLE 更改当前会话中的活动角色
• CURRENT_ROLE() 显示当前会话中的活动角色

1.	-- 创建角色  
2.	create role 'dev_read','dev_write'  
3.	  
4.	-- 给角色分配权限  
5.	grant select on mydb.* to 'dev_read';  
6.	grant update,insert,delete on mydb.* to 'dev_write';  
7.	  
8.	-- 创建用户  
9.	create user 'user1'@'localhost' identified by 'user1';  
10.	create user 'user2'@'localhost' identified by 'user2';  
11.	create user 'user3'@'localhost' identified by 'user3';  
12.	  
13.	-- 给用户分配角色  
14.	grant dev_read to 'user1'@'localhost'  
15.	grant dev_read to 'user2'@'localhost'  
16.	grant dev_write to 'user3'@'localhost'  
17.	  
18.	-- 激活角色，设置用户帐户连接到数据库服务器时指定哪些角色应该处于活动状态
19.	set default role all to 'user1'@'localhost','user2'@'localhost'  ,'user3'@'localhost'    
20.	  
21.	-- 给用户撤销角色  
22.	revoke select on mydb.* from 'dev_read';  
23.	revoke update,insert,delete on mydb.* from 'dev_write'  
24.	  
25.	-- 删除角色  
26.	drop role 'dev_read';  
27.	drop role 'dev_write'  
28.	  
29.	-- 删除用户  
30.	drop user  'user1'@'localhost','user2'@'localhost'  ,'user3'@'localhost'