DAB04 一、MySQL多表查询、二、MySQL管理工具、三、MySQL用户授权及撤销（重点）

最新推荐文章于 2023-01-16 09:04:34 发布

mid的博客

最新推荐文章于 2023-01-16 09:04:34 发布

阅读量428

点赞数

分类专栏： MySQL数据库知识集文章标签： MySQL多表查询 MySQL管理工具 MySQL用户授权及撤销

本文链接：https://blog.csdn.net/qq_41270538/article/details/86177493

版权

MySQL数据库知识集专栏收录该内容

18 篇文章 0 订阅

订阅专栏

一、多表查询二、
MySQL管理工具
三、用户授权及撤销（重点）

一、多表查询

1）复制表
功能：备份表、快速建表
1)备份表
注意：备份的表不会连键值索引备份。备份的数据取决于约束条件。

在这里插入图片描述

mysql> create table beifen.user1 select * from table userdb.user;
//将userdb.user表备份beifen.user1

mysql> create table db4.user2 select * from userdb.user order by uid desc limit 5;
//将userdb.user表中的前五行备份到db4.user2中

2）复制表（快速建表）

mysql> create table db4.user3 select * from userdb.user where 1>2;
//以userdb.user表作为模版创建新表，约束条件不成立即可

2）多表查询，也称连接查询
–将2个或2个以上的表，按某个条件连接起来，从中选取需要的数据。
–当多个表中，存在相同意义的字段（字段名可以不同）时，可以通过该字段连接多个表。

方法1: 命令格式：
1）select 字段名列表 from 表a,表b;
2)select 字段名列表 from 表a,表b where 条件;
3)select 表名1.字段名,… from 表名1,…;

mysql> create table db4.t1 select name ,uid,shell from userdb.user limit 3;//快速建表
mysql> create table db4.t2 select name ,uid,homedir,password from userdb.user limit 6;//快速建表
mysql> select * from t1,t2;	//匹配t1,t2的所有数据
mysql> select t1.name,t1.uid,t2.* from t1,t2;	//匹配t1表name,uid和t2表所有数据

mysql> > select * from t1,t2
    	-> where
  	 	-> t1.name="root" and t2.name="root"; //匹配t1表name为root,t2表name也为root才会显示

mysql> select t1.*,t2.homedir,t2.password from t1,t2
	> where t1.name="root" and t1.uid=1 and t1.name=t2.name;	//

方法2：where 子查询
-使用where子查询
–把内层查询结果作为外层查询的查询条件
语法格式：
select 字段名列表 from 表名 where 条件 (select 字段名列表 from 表名 where 条件);

mysql> select name,uid from user  where uid>(select avg(uid) from user);	//匹配uid值大于平均uid的值

mysql> select name,uid from userdb.user where name in
	>(select user from mysql.user where host="localhost" and user="root");

方法三：左连接查询查询条件成立时，以左边的表为主显示查询结果
select 字段名列表 from 表a left join 表b on 条件表达式;

mysql> create table t3 select name,uid,shell,password from userdb.user limit 6;
mysql> create table t4 select name,uid,shell,o=password from userdb.user limit 8;
mysql> select * from t3;
mysql> select * from t4;
mysql> select * from t3 left join t4 on t3.name=t4.name and t3.uid=t4.uid;//以t3为左显示

方法四：右连接查询查询条件成立时，以右边的表为主显示查询结果
select 字段名列表 from 表a right join 表b on 条件表达式;

mysql> select * from t3 right join t4 on t3.name=t4.name and t3.uid=t4.uid;//以t4为右显示

二、MySQL管理工具

在这里插入图片描述
MySQL Workbench

MySQL Front

1.1 问题
部署LAMP+phpMyAdmin平台
1.2 方案

安装httpd、mysql、php-mysql及相关包
启动httpd服务程序
解压phpMyAdmin包，部署到网站目录
配置config.inc.php，指定MySQL主机地址
创建授权用户
浏览器访问、登录

使用使用1台RHEL7虚拟机，其中一台作为数据服务（192.168.4.6）、另外一台作为测试用的Linux客户机（192.168.4.254）使用使用1台RHEL7虚拟机，其中一台作为数据服务器（192.168.4.6）、另外一台作为测试用的Linux客户机（192.168.4.254）
在这里插入图片描述
步骤一：准备软件的运行环境 lamp

[root@mysql6~]# rpm -q httpd  php  php-mysql    //检测是否安装软件包
未安装软件包 httpd 
未安装软件包 php 
未安装软件包 php-mysql
[root@mysql6~]# yum  -y   install   httpd     php    php-mysql  //装包
[root@mysql6~]# systemctl  start  httpd       //启动服务
[root@mysql6~]# systemctl  enable httpd      //设置开机自启
Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.

步骤二：测试运行环境

方法一、
[root@mysql6~]# echo "123" > /var/www/html/index.html	//编辑页面测试文件
[root@mysql6~]# curl http://192.168.4.50	 //查看页面测试文件
方法二、
[root@mysql6~]# vim  /var/www/html/test.php     //编辑页面测试文件
[root@mysql6~]# cat /var/www/html/test.php      //查看页面测试文件
<?php
$x=mysql_connect("localhost","root","123456");
if($x){   echo "ok";    }else{    echo "no";    };
?>
[root@mysql6~]# yum  -y   install  elinks     //安装测试网页工具
[root@mysql6~]#  elinks   --dump   http://localhost/test.php
   Ok                                                //验证测试页面成功

步骤三：安装软件包
1）物理机传输解压包给虚拟机192.168.4.6
（提前要有）

[root@localhost桌面]# scp phpMyAdmin-2.11.11-all-languages.tar.gz 192.168.4.6:/root/
root@192.168.4.6's password: 
phpMyAdmin-2.11.11-a 100% 4218KB 122.5MB/s   00:00

2）虚拟机192.168.4.6解压phpMyAdmin-2.11.11-all-languages.tar.gz压缩包

[root@mysql6~]# tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/   //-C 表示改变至目录
[root@mysql6~]# cd /var/www/html/
[root@mysql6~]# mv phpMyAdmin-2.11.11-all-languages  phpmyadmin //改变目录名
[root@mysql6~]#  chown  -R  apache:apache  phpmyadmin/ //改变phpmyadmin目录权限

步骤四：修改软件的配置文件定义管理的数据库服务器
切换到部署后的phpmyadmin程序目录，拷贝配置文件，并修改配置以正确指定MySQL服务器的地址

[root@mysql6html]# cd  phpmyadmin
[root@mysql6 phpmyadmin]# cp   config.sample.inc.php   config.inc.php  
//备份主配置文件
[root@mysql6 phpmyadmin]# vim   config.inc.php  //编辑主配置文件
17 $cfg['blowfish_secret'] = 'yes123';     //给cookie做认证的值，可以随便填写
31 $cfg['Servers'][$i]['host'] = 'localhost';  //指定主机名，定义连接哪台服务器

步骤五：在客户端访问软件管理数据库服务器
1）在客户端访问软件,打开浏览器输入http://192.168.4.6/phpmyadmin(数据库服务器地址) 访问软件，如图-2所示，用户名是root，密码是123456
在这里插入图片描述
2）登入成功后，如图-3示，即可在授权范围内对MySQL数据库进行管理。

三、用户授权及撤销（重点）

数据库管理员在本机登录才有授权权限
在这里插入图片描述
mysqladmin -hlocalhost -uroot -p password “密码” //修改本机数据库管理员密码

密码恢复及设置
1)恢复MySQL管理员密码

 ]#systemctl stop mysqld	//要先停止服务
    ]#vim /etc/my.cnf
    [mysqld]
    .....
    skip-grant-tables
    ......
    ]#systemctl restart mysqld

注意：如有设置了密码复杂度的配置要注释掉

mysql> use mysql;
mysql> show tables;
mysql> desc user;
mysql> select host,user,authentication_string from mysql.user;
//host存放本机、user存放用户、authentication_string存放用户密码
mysql> update mysql.user
	> set authentication_string=("123456")
	> where host="localhost" and user="root";
mysql> flush privileges;
mysql> quit

创建授权

MySQL授权库和表信息
@授权库mysql，主要的几个表：

-user表，存储授权用户的访问权限
-db表，存储授权用户对数据库的访问权限
-tables_priv表，存储授权用户对字段的访问权限

show grants for “用户名”@“客户端地址”; //给授权
show grants for root@""; //查看服务端已有的授权用户

grant配置授权
基本用法：
–all:匹配所有权限
grant 权限列表…（如 drop、select、insert…） on 库名.表名 to 用户名@‘客户端地址’ identified by '密码‘ [with grant option];
with grant option //是否有授权
使用with grant option。被授权用户可将所获得的权限再次授予其它用户或角色，并且权限的取消是级联的。级联的意思是，如果user_a使用“with grant option”语句将权限又授予了user_b，当管理员回收user_a的权限时，则user_b的权限也会被回收。但管理员不可以显式回收用户user_b的权限。

权限列表

all:匹配所有权限
select,update,insert …
select,update(字段1，… …,，字段N)
-注意事项：
当库名.表名为*.*时，匹配所有库所有表
授权设置存放在mysql库的user表中
mysql> select @@hostname; //查看客户端访问的主机名
mysql> select user(); //查看客户端用什么用户访问的
mysql> show grants; //显示访问权限
[root@mysql151 ~]# mysql -h192.168.4.50 -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host ‘mysql151’ is not allowed to connect to this MySQL server
报错信息：原因是192.168.4.50服务端数据库没有给192.168.4.51客户端授权。
解决方法：在服务端数据库给授权即可。如下：

mysql> grant all on 库名.表名 to root@“192.168.4.51” identified by “123456789@qq.Com”
-> with grant option;

现在客户端访问就可以了。

[root@mysql151 ~]# mysql -h192.168.4.50 -u root -p123456789@qq.Com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> select @@hostname;	//查看主机名
+------------+
| @@hostname |
+------------+
| mysql150   |
+------------+
1 row in set (0.00 sec)

mysql> select user();	//查看连接用户名及客户端地址
+---------------+
| user()        |
+---------------+
| root@mysql151 |
+---------------+
1 row in set (0.01 sec)

mysql> show grants;	//显示访问权限
+------------------------------------------------------------------------+
| Grants for root@192.168.4.51                                           |
+------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.4.51' WITH GRANT OPTION |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

客户端地址的表示方式

%：匹配所有主机
192.168.1.%：匹配指定的一个网段
192.168.1.1：匹配指定的IP地址单个主机
%.tedu.com：匹配一个DNS区域
www.tedu.com：匹配指定域名的单个主机

192.168.4.51操作

mysql> grant all on testdb.* to admin@"%" identified by "123456789@qq.Com";
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select user.host from mysql.user;
+--------------+
| host         |
+--------------+
| %            |
| 192.168.4.51 |
| localhost    |
| localhost    |
+--------------+
4 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+-----------+--------------+
| user      | host         |
+-----------+--------------+
| admin     | %            |
| root      | 192.168.4.51 |
| mysql.sys | localhost    |
| root      | localhost    |
+-----------+--------------+
4 rows in set (0.01 sec)

mysql> show grants for admin@"%";
+---------------------------------------------------+
| Grants for admin@%                                |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%'                 |	//admin用户对数据库任何表没有权限
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'admin'@'%' |	//admin用户对testdb有权限
+---------------------------------------------------+
2 rows in set (0.00 sec)

192.168.4.53数据库验证

[root@mysql153 ~]# mysql -h192.168.4.50 -u admin -p123456789@qq.Com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

192.168.4.50操作

mysql> grant select,insert,update(name,uid) on userdb.user to haha@"192.168.4.%" identified by "A123456..a"

192.168.4.51验证

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| userdb             |
+--------------------+
2 rows in set (0.00 sec)

mysql> drop database userdb;
ERROR 1044 (42000): Access denied for user 'haha'@'192.168.4.%' to database 'userdb';

问题：不能创建

mysql> create database df;
ERROR 1044 (42000): Access denied for user 'haha'@'192.168.4.%' to database 'df';

撤销授权用户

mysql> select user,host from mysql.user;
+-----------+--------------+
| user      | host         |
+-----------+--------------+
| admin     | %            |
| haha      | 192.168.4.%  |
| root      | 192.168.4.%  |
| root      | 192.168.4.51 |
| mysql.sys | localhost    |
| root      | localhost    |
+-----------+--------------+
6 rows in set (0.00 sec)

mysql> show grants for root@"192.168.4.51";
+------------------------------------------------------------------------+
| Grants for root@192.168.4.51                                           |
+------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.4.51' WITH GRANT OPTION |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> revoke grant option on * . * from root@"192.168.4.51";	//撤销4.51权限给其他人
Query OK, 0 rows affected (0.00 sec)

mysql> revoke delete,update  on *.* from root@"192.168.4.51";	//撤销删除、更新、权限
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all  on * . * from root@"192.168.4.51";	//撤销所有权限
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user="root" and host="192.168.4.51";
+------+--------------+
| user | host         |
+------+--------------+
| root | 192.168.4.51 |
+------+--------------+
1 row in set (0.00 sec)

删除授权用户

mysql> drop user root@"192.168.4.51";
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user="root" and host="192.168.4.51";
Empty set (0.00 sec)

授权用户自己修改密码

mysql> set password=password("新密码");

管理员重置授权用户连接密码

mysql> set password for 用户名@"客户端地址"=password("新密码");

授权可以在本机登陆的用户

mysql> grant all on 库名.表名 to 用户名@"客户端地址"
    -> identified by "密码";
***验证***
]#mysql -u tom -p密码

刷新权限
对用户做了权限变更之后，一定记得重新加载一下权限，将权限信息从内存中写入数据库。

mysql> flush privileges;

mid的博客

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DAB04 一、MySQL多表查询、二、MySQL管理工具、三、MySQL用户授权及撤销（重点）

一、多表查询二、MySQL管理工具三、用户授权及撤销（重点）一、多表查询1）复制表功能：备份表、快速建表1)备份表注意：备份的表不会连键值索引备份。备份的数据取决于约束条件。mysql&amp;amp;amp;amp;amp;amp;amp;gt; create table beifen.user1 select * from table userdb.user;//将userdb.user表备份beifen.user1mys...
复制链接

扫一扫