04__mysql-权限管理

一、查询语句

1. 常见符号

2. 其他关键字

3. 查询举例

1、 基本查询

1 基本简单查询

select 字段1,字段2,... from 表名;

查看所有数据
select * from emp;
查看指定列
select empno,ename,job,sal from 0.
给列指定别名
select empno 工号,ename 姓名,job 工作,sal 工资 from emp;
或者
select empno as 工号,ename as 姓名,job as 工作,sal as 工资 from emp;

2，去重

select distinct 字段 from 表名称;

select distinct deptno from emp;

3、 四则运算

操作对象：字段
语法：
select 运算式 from 表名称;

select 1+2;
select 1-3;
select 1*4;
select 1/4;
select 1 + 1, (10-1)/3, 2*2/2;
select 1 + 1 from dual;		dual表,俗称万能表
乘幂需要调用函数：-3表示2的三次幂取倒数
mysql> select pow(2,3),power(2,3),pow(2,-3),power(2,-3);
+----------+------------+-----------+-------------+
| pow(2,3) | power(2,3) | pow(2,-3) | power(2,-3) |
+----------+------------+-----------+-------------+
|        8 |          8 |     0.125 |       0.125 | 
+----------+------------+-----------+-------------+
1 row in set (0.00 sec)

2、五子句

1-带where条件查询

使用逻辑运算符
select * from t3 where english >60 && english <90;
select * from t3 where math >= 60 and english >= 60;
select * from t3 where math >= 60 or english >= 60;
select * from t3 where math >= 60 and not english >= 60;

使用正则表达式匹配
select 字段 from 表名称 where 字段 regexp '表达式';
select * from t3 where name regexp '^h';
select * from t1 where name regexp '.*[0-9]';
select * from t1 where name regexp '.*[0-9]+.*';
select * from t1 where name regexp '.*[0-9]{2}.*' ;

sal不在3000与5000之间的记录 
select * from emp where sal not between 3000 and 5000;

sal在500-1000或者3000-5000之间
select * from emp where sal >= 3000 and sal <= 5000 or sal >=500 and sal <=1000;

模糊查询
select * from 表名称 where 字段 like '条件'

select * from class where name like 'a%'
select * from class whrer name regexp '^a'
select * from class whrer name regexp 'a.*'

2、分组和统计 group by

根据给定==数据列==的查询结果进行分组统计，最终得到一个==分组汇总表==

注：一般情况下group by需与==统计函数==一起使用才有意义

操作对象：字段

语法：
select 统计函数（字段） from 表名称 group by 被分组字段;

每个部门最大工资和部门人数:
select deptno,max(sal),count(*) from emp group by deptno;
+--------+----------+----------+
| deptno | max(sal) | count(*) |
+--------+----------+----------+
|     10 |  5000.00 |        3 |
|     20 |  3000.00 |        5 |
|     30 |  2850.00 |        6 |
+--------+----------+----------+
查找10号和20号部门最大工资和人数
select deptno,max(sal),count(*) from emp where deptno in (10,20) group by deptno;
+--------+----------+----------+
| deptno | max(sal) | count(*) |
+--------+----------+----------+
|     10 |  5000.00 |        3 |
|     20 |  3000.00 |        5 |
+--------+----------+----------+

3、 having

1. having与where类似，根据条件对==数据==进行过滤筛选
2. ==where==针对表中的==列==发挥作用，查询数据
3. ==having==针对查询==结果集==发挥作用，筛选数据

• 举例说明

查询员工数大于5个的部门编号和最大工资及员工数
select deptno,max(sal),count(*) from emp group by deptno having count(*)>5;
查询底薪+提成大于2000的员工相关信息
select empno,ename,sal+ifnull(comm,0) sum from emp having sum >2000;
查询每个部门最大工资的员工信息
select ename,empno,deptno,sal from emp where sal in (select max(sal) from emp group by deptno);
+-------+-------+--------+---------+
| ename | empno | deptno | sal     |
+-------+-------+--------+---------+
| BLAKE |  7698 |     30 | 2850.00 |
| SCOTT |  7788 |     20 | 3000.00 |
| KING  |  7839 |     10 | 5000.00 |
| FORD  |  7902 |     20 | 3000.00 |
+-------+-------+--------+---------+
4 rows in set (0.00 sec)

查询20和30号部门基本工资加提成大于2000的人员信息
select deptno,empno,ename,sal+ifnull(comm,0) sum from emp where deptno in(30,20)having sum >2000;

• IFNULL函数扩展

IFNULL()函数：
MySQL中的IFNULL函数类似于Oracle中的NVL函数，其表达式为：IFNULL(expr1,expr2)，如果第一个参数不为空，则返回第一个参数，否则返回第二个参数。
ifnull(comm,0)

IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

IF()函数：
IF(expr1,expr2,expr3)类似于Oracle中的NVL2函数，如果第一个表达式的值为TRUE（不为0或null），则返回第二个参数的值，否则返回第三个参数的值
       
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

NULLIF()函数：
NULLIF(expr1,expr2),如果expr1=expr2为真，返回null；否则返回expr1

NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

4、排序 order by

操作对象：字段

从小到大(升序)
order by asc
select * from 表名称 order by 字段;		#不指定默认升序
select * from 表名称 order by 字段 asc;
--------------------------------------------------------
从大到小(降序)
order by desc
select * from 表名称 order by 字段 desc;

5、函数附加条件 limit

常配合其他函数使用，如排序

操作对象：结果集

语法：limit 起始位置 数量		 #起始位置从0开始
select * from 表名称 limit n;			显示前n行
select * from 表名称 limit n,m;		显示n+1行至表中的n+m行

select * from 表名称 limit 10;			显示前10行
select * from 表名称 limit 10,10;		显示11至20行

3、高级查询

1、合并列函数(concat)

select concat(字段1,'分隔符，可不加',字段2) as 临时字段 from 表名称; 
临时字段如有数字或字符，可以用引号引起来

select concat(user,'    ',password) as 用户名和密码 from mysql.user;
create table t3(path1 varchar(255),homedir varchar(255));
insert into t3 set path1='/www/itcast',homedir='u01';
select concat(path1,'/', homedir) from t3;
select concat(path1,'/', homedir) as '家目录' from t3;

2、多表联合查询

1<常规查询表：

常规查询表：
select * from emp,dept where emp.deptno=dept.deptno;

select dept.deptno,dept.dname,emp.ename,emp.job from emp,dept where emp.deptno=dept.deptno;

select e.ename,e.job,e.hiredate,d.dname,e.deptno from emp e,dept d where e.deptno=d.deptno;

2<左连接/右连接

• 显示主表全部及匹配的附表

左连接（以左表为标准连接右表）:
    left join=left [outer] join
    select 显示的内容 from 表1 left join 表2 on 条件;
    #条件可以用运算式或匹配符号

select emp.ename,emp.job,dept.deptno from dept left join emp on emp.deptno=dept.deptno;
--------------------------------------------------------------------------------------------------------------------
右连接（以右表为标准连接左表）:
    right join= right [outer] join
    select 显示的内容 from 表1 right join 表2 on 条件;	
    #条件可以用运算式或匹配符号

select emp.ename,emp.job,dept.deptno from dept right join emp on emp.deptno=dept.deptno;

3<内连接

• 取多表之间的交集

方法1：
    select 显示的内容 from 表1，表2 where 条件;	
    #条件可以用运算式或匹配符号
方法2：
    select 显示的内容 from 表1 inner join 表2 on 条件;	
    #条件可以用运算式或匹配符号

inner join
select emp.ename,emp.job,dept.deptno from emp inner join dept on emp.deptno=dept.deptno;
select * from emp inner join dept on emp.deptno=dept.deptno;
select * from dept inner join emp no emp.deptno=dept.deptno;

4<纵向连接

• 合并【表结构相同】

纵向连接:合并【表结构相同】
#select 显示的内容 from 表1，表2 where 条件;
 
select name,math,english,'' as 'chinese' from t2 union select name,math,english,chinese from t3;

3、查询嵌套

• 查询中嵌套一个查询

1

2、事务处理

• 检测sql语句事务是否全部执行完毕，是则写入数据库，否则丢弃。
• 只能使用innodb引擎

三、创建事务
T-SQL中管理事务的语句：
1 开始事务: begin transaction
2 提交事务：commit transaction
3 回滚事务: rollback transaction

三、事务处理
1、什么是事务处理
事务：一系列将要发生或正在发生的连续操作。

事务安全，是一种保护连续操作同时实现（完成）的机制。事务安全的意义就是，保证数据操作的一致性与完整性。

2、举个栗子：借钱
涛哥最近手头紧，没钱，找许少华借钱。
涛哥：0.20元
银行转账
少华：1000.20元

要完成一系列操作：转账（关联很多SQL语句）
少华银行卡扣钱-1000
update 数据表 set money=money-1000 where name='少华';

少华：0.20元
银行机器突然故障、断电了，问题：导致数据不一致，数据不完整。(事务安全)
涛哥：0.20元

涛哥银行卡增钱+1000
update 数据表 set money=money+1000 where name='涛哥';
当以上两个SQL语句同时完成，则认为交易成功，交易成功才能写入数据库。

3、事务处理的前提
MySQL引擎：MyISAM引擎（查）与InnoDB引擎（安全）
记住：事务处理只能发生在InnoDB引擎上，从MySQL5.5以后版本开始，系统默认的引擎都是InnoDB引擎。

实验：事务处理必须使用InnoDB引擎
create table it_bank(
   id int not null auto_increment primary key,
   name varchar(40),
   money decimal(11,2)
) engine=innodb default charset=utf8;
#插入测试数据
insert into it_bank values (null,'许少华',1000.20);
insert into it_bank values (null,'王金涛',0.20);

4、事务处理步骤
第一步：开启事务
start transaction;
第二步：执行SQL语句
update it_bank set money=money-1000 where id=1;
update it_bank set money=money+1000 where id=2;
...
第三步：如果成功，则提交。反之，则回滚
commit;  #提交
或
rollback;  #回滚
注意：以上事务处理全部是手工完成的，实际应用中，事务处理都是自动完成的。
# mysql > show variables like 'autocommit';

5、事务处理有4个特性（ACID、背下来）
原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

3、

二、用户管理

1. 创建用户(create user)

注意：MySQL中不能单纯通过用户名来说明用户，必须要指定主机名或IP地址。如jack@10.1.1.1

① 语法

创建用户设置密码
create user '用户名'@'host主机名'identified by '密码';
create user 'user'@'host主机名' identified by 'password';

说明：用户的信息保存在mysql数据库中的user表中，验证用户是否创建成功如下：
select user,host from mysql.user; 

② 用户主机表示方式

'user'@'localhost'				表示user只能在本地通过socket登录数据库
	create user  'tom'@'localhost' identified by '123';
'user'@'192.168.0.1'			表示user用户只能在192.168.0.1登录数据库
	create user  'harry'@'localhost' identified by '123';
'user'@'192.168.0.0/24'			表示user用户可以在该网络任意的主机登录数据库
	create user  'tony'@'10.1.1.1' identified by '123';
'user'@'%'						表示user用户可以在所有的机器上登录数据库
	create user  'jack'@'%' identified by '123';

2. 用户权限管理

① 权限分类

USAGE	无权限,只有登录数据库,只可以使用test或test_*数据库
-----------------------------------------------------------------------------
ALL		所有权限
-----------------------------------------------------------------------------
以下权限为指定权限
select/update/delete/super/slave/reload...
============================================================================

with grant option 选项表示允许把自己的权限授予其它用户和从其他用户收回自己的权限

② 权限表查询和保存位置

查询权限表
select 显示的内容 from 保存位置；
select user,host from mysql.user; 

-----------------------------------------------------------------------------------
mysql.user				所有mysql用户的账号和密码，以及用户对全库全表权限（*.*）
mysql.db				非mysql库的授权都保存在此(库.*)
mysql.table_priv		某库某表的授权(库.表)
mysql.columns_priv		某库某表某列的授权(库.表.字段)
mysql.procs_priv		某库存储过程的授权

③ 给用户授权

1) 语法

grant 权限1,权限2 on 库.表 to '用户'@'主机名'；

精细化授权（精确到字段）
grant 权限(字段1,字段2,...) on 库.表 to 用户@主机

2) 用户授权示例

给tom@10.1.1.1用户授予查看db01库里所有表权限
mysql> grant select on db01.* to 'tom'@'10.1.1.1';
grant all on zblog.* to 'zbloguser'@'94.191.118.119' identified by '1234qwer';
刷新授权表
mysql> flush privileges;

给tom@10.1.1.1用户授予修改db01库的ID字段的权限
mysql> grant update(ID) on db01.tt1 to 'tom'@'10.1.1.1';
mysql> flush privileges;

3.查询权限

查看当前用户权限
mysql> show grants;
查看指定用户权限
mysql> show 权限 grants on 库.表 for 'tom'@'10.1.1.1';

3) 使用grant创建用户

grant all on *.* to 'harry'@'10.1.1.%' identified by '123'; 

4) 允许指定用户授予和收回权限

1>授予权限

with grant option选项

with grant option:用户是否可以下放和回收权限

grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option; 
 

测试harry用户是否可以将自己的权限下放：
harry用户登录：

mysql> grant select on db01.* to 'tom'@'10.1.1.1';
ERROR 1044 (42000): Access denied for user 'harry'@'10.1.1.%' to database 'db01'

扩展补充

• 从MySQL 5.7.6开始，不赞成使用grant修改密码;使用 ALTER USER来代替。

ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; 

④ 回收用户权限

revoke 权限 on 库.表 from '用户'@'主机名';
撤消指定的权限
mysql> revoke update on db01.tt1 from 'tom'@'10.1.1.1';
撤消所有的权限
mysql> revoke all on *.* from 'tom'@'10.1.1.1';

3. 删除用户(==drop user==)

① 语法

drop user 用户;

② 示例

删除'user01'@'localhost'用户
mysql> drop user 'user01'@'localhost';
默认删除user01从任意主机登录
mysql> drop user 'user01';
mysql> drop user 'user01'@'%';

重命名用户名
mysql> rename user 'harry'@'10.1.1.%' to 'harry'@'10.1.1.1';

删除一个匿名用户
mysql> drop user ''@'localhost';
删除mysql中的匿名用户
mysql> delete from mysql.user where user='';

删除root用户从本机::1登录（::1表示IPv6地址）
mysql> delete from mysql.user where user='root' and host='::1';
mysql> flush privileges;

注意：如果tcp/ip登录，服务器端口不是默认3306，则需要加端口号