联合表查询、用户的授权及权限撤销、MySQL管理密码的恢复

一、多表查询
复制表（备份表 快速建表）
• 将源表 xxx 复制为新表 yyy
– CREATE TABLE yyy SELECT * FROM xxx;

• 将指定的查询结果复制为新表 zzz
– CREATE TABLE zzz SQL 查询语句 ;

• 复制源表 xxx 的结构到新表 vvv
– CREATE TABLE vvv SELECT * FROM xxx WHERE FALSE;

• 将源表 vvv 的名称改为 www
– ALTER TABLE vvv RENAME TO www;

mysql> create database db4;
mysql> use db4;
mysql> create table db4.t1 select * from db3.user;	
						// 备份表（*可以是表中的任意字段）
mysql> create table t2 select * from db3.user where  1=2;// 复制表结构不复制内容

• 多表查询(又称 连接查询)
– 将 2 个或 2 个以上的表 按某个条件连接起来,从中选取需要的数据
– 当多个表中 存在相同意义的字段(字段名可以不同)时,可以通过该字段连接多个表
准备材料：

mysql> create table db4.t4 select  name,uid,shell from  db3.user limit 3;
mysql> create table t5 select  name,id,uid,comment,homedir from db3.user  limit  5;

• 格式 1
– select 字段名列表 from 表 a, 表 b ;

mysql> select * from t4,t5;    //在两张表中查询，共显示15行，t4在前t5在后

• 格式 2
– select 字段名列表 from 表 a, 表 b where 条件;

mysql> select  t5.name,t4.name from t5,t4 where  t4.name=t5.name  and t4.uid=t5.uid; 
						//查询t5,t4表中名字相同且uid相同的信息
mysql> select t5.*,t4.shell  from  t5,t4  where t4.name=t5.name  and t4.uid=t5.uid;

以上格式的查询结果叫笛卡尔集 显示查询结果的总条目数是 (表 a 的纪录数 * 表 b 的纪录数)

where 子查询
• 使用 where 子查询
– 把内层查询结果作为外层查询的查询条件

• 格式 1
– select 字段名列表 from 表 A where
条件 ( select 字段名列表 from 表 A ) ;
– select 字段名列表 from 表 A where
条件 ( select 字段名列表 from 表 A where 条件 ) ;
– 输出年龄小于平均年龄的学生的名字和年龄

mysql> select name,age from db3.user  where  age< (select avg(age)  from db3.user);  			
						//查询年龄小于平均年龄的
mysql> select name,age from db3.user  where age > (select avg(age)  from db3.user);
					//查询年龄大于平均年龄的
mysql> select name from db3.user where name in  (select name from db4.t4);  
						//查询db3.user表与db4.t4表中名字相等的信息	
mysql> select name from db3.user where name not in (select name from db4.t4);			
			//查询db3.user与db4.t4表中名字不相等的信息

左连接查询——当条件成立时，以左表为主显示查询结果
• 基本用法
– select 字段名列表 from 表a LEFT JOIN 表b ON 条件表达式;

右连接查询——当条件成立时，以右表为主显示查询结果
• 基本用法
– select 字段名列表 from 表a RIGHT JOIN 表b ON 条件表达式;

准备素材：
mysql> create table t6 select name,uid,shell from db3.user limit 4;
mysql> create table t7 select name,uid,shell from db3.user limit 6;
mysql> select * from t6 left join t7 on t6.uid = t7.uid;
					//左连接查询，以t6表为主显示查询结果
mysql> select *  from t6 right join t7 on t7.uid=t6.uid; 
				 //右连接查询，显示t7表中的所有表记录，t6表中没有的用NULL补充
mysql> select t6.name,t6.uid,t7.name,t7.uid  from t6 right join t7  on t7.uid=t6.uid;
	 			 //右连接查询，显示t7表中的表记录，只显示两张表的name和uid字段

二、MYSQL管理工具
管理工具：

PhpMyAdmin
• 基本思路

1. 安装 httpd 、 mysql 、 php-mysql 及相关包

2. 启动 httpd 服务程序

3. 解压 phpMyAdmin 包,部署到网站目录

4. 配置 config.inc.php ,指定 MySQL 主机地址

5. 创建授权用户

6. 浏览器访问、登录使用

    [root@client ~]#yum -y install httpd php  php-mysql
    [root@client ~]# tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz  -C /var/www/html/
    [root@client ~]# cd /var/www/html/
    [root@client html]# mv  phpMyAdmin-2.11.11-all-languages/ phpmyadmin
    [root@client html]# cd phpmyadmin/
    # cp config.sample.inc.php  config.inc.php
    # vim config.inc.php
     17 $cfg['blowfish_secret'] = 'asd123';
     31 $cfg['Servers'][$i]['host'] = 'localhost';  //指定要管理的服务器
    [root@client html]# systemctl start httpd
    [root@client html]# firefox http://192.168.4.50/phpmyadmin
   

三、用户授权及权限撤销
1.密码恢复及设置——必须是操作系统的管理员进行操作
a.修改数据库管理员密码

	[root@client html]# mysqladmin -uroot -p654321 password
	New password: ——新密码
	Confirm new password: ——确认新密码
	[root@client html]# mysqladmin -uroot -p password '654321'
	Enter password: ——旧密码

b. 恢复MYSQL管理密码

[root@client html]# vim /etc/my.cnf
	#validate_password_policy=0      //密码复杂度
	#validate_password_length=6		//密码长度
	skip-grant-tables			//跳过密码检查
[root@client html]# systemctl restart mysqld
[root@client html]# mysql
mysql> select host,user,authentication_string  from mysql.user;
							//查询所有密码登陆用户
mysql> update mysql.user set authentication_string=password("654321")    \
 > where user="root" and host="localhost";
							//修改root用户的密码为654321
mysql> flush privileges;			//刷新配置
[root@client html]# vim /etc/my.cnf			//修改配置文件
[root@client html]# systemctl restart mysqld
	validate_password_policy=0
	validate_password_length=6
	#skip-grant-tables

2. 权限的存储位置
   授权库——存储在MYSQL库里

3. 用户授权
   MySQL 授权库和表信息
   • 授权库 mysql ,主要的几个表
   – user 表,存储授权用户的访问权限
   – db 表,存储授权用户对数据库的访问权限
   – tables_priv 表,存储授权用户对表的访问权限
   – columns_priv 表,存储授权用户对字段的访问权限
   GRANT 配置授权
   • 基本用法
   – GRANT 权限列表 … … ON 库名 . 表名
   – TO 用户名 @’ 客户端地址 ’
   – IDENTIFIED BY ’ 密码 ’ ——设置密码
   – [ WITH GRANT OPTION ]; ——是否有授权权限

   • 注意事项
   – 当库名 . 表名 为 . 时,匹配所有库所有表
   – 授权设置存放在 mysql 库的 user 表
   • 权限列表
   – all :匹配所有权限
   – SELECT,UPDATE,INSERT … …
   – SELECT,UPDATE ( 字段 1, … … , 字段 N)

   • 客户端地址
   – % :匹配所有主机
   – 192.168.1.% :匹配指定的一个网段
   – 192.168.1.1 :匹配指定 IP 地址的单个主机
   – %.tarena.com :匹配一个 DNS 区域
   – svr1.tarena.com :匹配指定域名的单个主机
   授权：新建用户 mydba ,对所有库、表有完全权限允许从任何地址访问,密码设为 123456； 允许该用户为其他用户授权
   mysql> grant all on . to mydba@"%" identified by “123456” with grant option;
   查看
   • 通过查看表记录的方式查看授权用户的权限

   mysql> select  * from mysql.user where user='mydba';
   

   • 管理员可查看其他用户的权限

   mysql> show grants for mydba@'%';
   

   • 用户查看自己的权限

   mysql> show grants;
   

客户端连接：

[root@mysql51 ~]# mysql -h192.168.4.50 -umydba -p123456;	//客户端登陆
mysql> select user();	//显示登陆主机（从哪台主机登陆）
mysql> select @@hostname;			//显示当前连接的数据库
mysql> show grants;			//查看权限
mysql> set password = password(123asd);	 //授权用户自己更改登陆密码

数据库管理员修改授权用户的连接密码（授权用户忘记登陆密码）

[root@client ~]# mysql -uroot -p654321
mysql> set password for mydba@"%" =password("123456");

测试mydba用户授权权限：
创建新库、新表、对表记录的增删改查、及授权权限

[root@mysql51~]# mysql -h 192.168.4.50 -u mydba  -p 123456  //客户端连接数据库
mysql> grant all on gamedb.*  to test1@'localhost' identified by '123456';	
				//授权test1用户在服务器本机登陆数据库

[root@client ~]# mysql -hlocalhost -utest1 -p123456		//检测是否能登陆成功
mysql> select * from mysql.db where db='gamedb';	//查看db表里的具体权限
mysql> show grants for  test1@'localhost';		//只查看用户test1的权限

4. 权限撤销
   撤销用户权限
   • 基本用法
   – REVOKE 权限列表 ON 库名 . 表名
   – FROM 用户名 @’ 客户端地址 ';
   撤销mydba用户的授权权限

   mysql> show grants for mydba@'%';
   mysql> revoke  grant option on  *.* from mydba@'%';
   mysql> grant all on teadm.*  to test2@'localhost' identified  > by '123456';
   ERROR 1044 (42000): Access denied for user 'mydba'@'%' to database 'teadm'
   

   撤销mydba用户对库和表的删除权限（delete、drop）

   mysql> revoke  delete,drop on *.*  from mydba@"%";
   
   mysql> show grants for mydba@'%';
   | GRANT SELECT, INSERT, UPDATE, CREATE, RELOAD, SHUTDOWN, PROCESS, 
   FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, 
   CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, 
   REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, 
   ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, 
   CREATE TABLESPACE ON *.* TO 'mydba'@'%' |			///剩余的权限
   

   撤销用户的所有权限

   mysql> revoke all on *.*  from mydba@"%";
   mysql> show  grants for mydba@'%';
   mysql> revoke  delete,drop on *.*  from test1@'localhost';
   

   删除授权用户mydba

   mysql> drop user mydba@"%";   //删除授权用户之后其相对应的权限也会被删除
   mysql> select user,host  from mysql.user;
   
   注：撤销权限时，库名的表示方式要与授权时一样
   

扩展一：
案例：
在50主机添加数据库连接用户，要求如下：
用户名：admin
客户端地址：网络中的所有主机
权限：只对db3库有完全权限且有授权权限
登陆密码：123456
mysql> grant all on db3.* to admin@"%" identified by ‘123456’ with grant option;
测试授权用户admin：
在客户端使用admin用户连接数据库服务器50对库的访问权限及授权权限

[root@mysql51 ~]# mysql -h192.168.4.50 -uadmin -p123456

授权权限：先加授权用户admin2，对所有库表有完全权限，密码：123456

mysql> grant all  on *.*  to  admin2@"%"  identified by  '123456';

客户端使用admin用户授权会失败，因为admin用户只拥有db3的完全权限，没有对mysql库的权限，而授权操作时需要对mysql库进行操作，因此无法进行授权操作

再进行以下操作后才可以进行授权，并且权限不能高于用户本身的权限

mysql> grant insert  on mysql.*  to admin@'%';

扩展2：
在50主机授权如下：

mysql> grant all on *.*  to root@"192.168.4.254" identified by '123456';
mysql> revoke delete on db44.* from root@"192.168.4.254";		
ERROR 1141 (42000): There is no such grant defined for user 'root' on
 host '192.168.4.254'				//撤销权限会失败

撤销权限时，库名的表示方式要与授权时一样