dba4 多表查询 mysql管理工具 用户授权

一：多表查询

1.复制表（作用：备份表，快速表)

格式：mysql>create table  新表名  sql查询    旧表;(将指定的查询结果复制为新表)

use db4;

mysql> create table user2 select * from db3.user;(指定表的内容复制到新表作为新表内容)

mysql> show tables;

mysql> select * from t22;

create table user3 select name,uid,shell from db3.user order by uid desc limit 5;(指定条件的前5行以排序的方式复制到新表中作为新表内容)

select * from user3;

select * from user where shell="abc";   (到源库打命令)

create table user4 select * from db3.user where 1=2;

select * from user4;

desc user4;

2，多表查询

格式：select 字段名列表  from   表a,表b;

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

mysql> create table t3 select name,uid,shell from db3.user limit 3;

mysql> select * from t3; 

mysql> create table t4 select name,password,uid,homedir  from db3.user limit 5;

mysql> select * from t4;

迪卡尔集

mysql> select * from t3,t4;

mysql> select t3.name,t4.name  from  t3,t4;

mysql> select t3.*,t4.password,t4.homedir  from t3,t4;
mysql> select t3.*, t4.password,t4.homedir  from  t3,t4  where t3.uid =t4.uid;
mysql> select t3.*,t4.password,t4.homedir,t4.uid from t3,t4 where t3.uid=t4.uid;

嵌套查询(把内层的查询结果作为外层查询的查询条件)

select   字段名列表  from  表名  where  条件  (select  字段名列表  from  表名  where   条件);

mysql> select name from db3.user order by uid desc limit 1

mysql> select avg(uid) from db3.user;
mysql> select name,uid from db3.user where uid<(select avg(uid) from db3.user);   avg:平均值

 

mysql> select name from db4.t3;                                                   （尽量不用嵌套查询)

mysql> select name from db3.user where name in (select name from db4.t3);

 

左连接查询:以左边表为主显示查询结果

格式：select  字段名列表  from  表名a  left   join   表名b     on   条件;

先建表

mysql> create table db4.t5 select name,uid,shell from db3.user limit 5;

mysql> select * from db4.t5;

mysql> create table db4.t6 select name,uid,shell from db3.user limit 7;

mysql> select * from db4.t6;

(相同的显示出来,以左边为主)

mysql> select * from t5 left join t6 on t5.uid=t6.uid;      left(左边)   join(加入)

mysql> select * from t5 right join t6 on t5.uid=t6.uid;        right(右边)

右连接查询：以右边的表为主显示查询结果

格式：select  字段名列表   from  表名a   right  join  表名b   on    条件;

mysql> select t5.name,t6.name from t5 right  join t6 on t5.uid=t6.uid;(相同的显示出来，以右边为主)

二： mysql管理工具*

      常见的管理工具：

mysql  界面(命令行)操作系统(跨平台)说明(mysql官方bundle包自带)

 mysql-Workbench  界面（图形) 操作系统(跨平台)说明(mysql官方提供

mysql-Feont 界面(图形) 操作系统（Windows)  说明(开源，轻量级客户端软件)

phpMyAdmin 界面（浏览器) 操作系统(跨平台)  说明(开源，需LAMP平台)

Navicat  界面（图形) 操作平台(windows) 说明(专业，功能强大，商业版)

1.准备包：phpMyAdmin-2.11-all-languages.tar.gz

部署运行环境lamp

root@host50 ~]# rpm -q httpd
root@host50 ~]# yum -y install httpd
[root@host50 ~]# systemctl restart httpd
root@host50 ~]# systemctl enable httpd
[root@host50 ~]# yum -y install php-mysql  php
2，安装软件

[root@host50 ~]# tar -xf phpMyAdmin-2.11.11-all-languages.tar.gz

root@host50 ~]# ls

[root@host50 ~]# mv phpMyAdmin-2.11.11-all-languages  /var/www/html/phpadmin
root@host50 ~]# cd /var/www/html
[root@host50 html]# ls
[root@host50 html]# ls phpadmin/index.php
3.创建配置，指定管理数据库服务器。
root@host50 html]# cd phpadmin
[root@host50 phpadmin]# cp  config.sample.inc.php  config.inc.php

root@host50 phpadmin]# vim config.inc.php   (修改17行和31行)可以乱加

4.客户端访问。

打开浏览器输入URL  http://192.168.4.50/phpadmin  (用户root,密码123456)

三，用户授权** （在数据库服务器上添加  连接时使用的用户名)
mysql> grant select,insert on db3.* to admin@"192.168.4.%" identified by "123qqq...A";

改密码：（加密恢复密码)

 

root@host50 ~]# systemctl stop mysqld
[root@host50 ~]# vim /etc/my.cnf       (加skip_grant_tables）不注释空密码登陆，一般注释掉
root@host50 ~]# systemctl  start  mysqld
[root@host50 ~]# mysql
ysql> select host,user,authentication_string from mysql.user;
mysql> update mysql.user set authentication_string=password("123456") where user="root" and host="localhost";

用户授权及权限撤销：

用户授权：在数据库服务器上添加新的连接用户名，默认只有数据库管理员root用户

with grant option 让添加的用户连接服务器后，也有授权权限。

identified by “密码"  新添加的用户连接数据库时，使用的密码

客户端地址 作用：网络中的那些主机使用新添加的用户连接数据库服务器。有如下表示方式：

所有主机  %

网段  192.168.4.%

指定主机 192.168.4.51

本机   localhost

主机名   pc1.tedu.cn

域名    %.tedu.cn

用户名  客户端主机连接数据库服务器时使用的名字，授权时自定义既可名字要有表示性。

数据库名：新添加的连接用户，访问后可以操作的库，表示方式如下：

* . *   所有库  所有表

库名.*   库下的所有表

库名.表名   某张表

权限列表：  新添加的连接用户，对可以操作的库访问权限，权限的表示方式如下：

all  所有权限

命令：命令某种权限 （例如 select,insert,delete)

授权库mysql库记录授权信息，使用不同的表记录不同的授权信息

user  已经添加的连接用户

db  记录已经添加的连接用户对库的访问权限

tables_priv 记录已添加的连接用户对表的访问权限

columns_priv  记录已添加的连接用户对表字段的访问权限

select * from user\G;(竖着显示)

show frants  for 用户@"客户端地址";     查看授权用户的访问权限

授权例子：

mysql> grant select,insert on db3.* to admin@"192.168.4%"  identified by "123qqq...A"; （本机授权给用户只能执行对本机执行指定的权限)

root@host51 ~]# mysql -h192.168.4.50 -uadmin -p123qqq...A  (客户端验证）

mysql> select @@hostname;(客户端验证）

mysql> select user();

mysql> show grants;

授权例子2：

在50服务器 授权主机52 可以使用root 连接自己，对所有库表有完全权限且有授权权限 登陆密码是123qqq...A

mysql> grant all on *.* to root@"192.168.4.52" identified by "123qqq...A"  with grant option;(可以对授权的客户机对本机执行增删改查等权限）

[root@host52 ~]# mysql -h192.168.4.50 -uroot -p123qqq...A

select @@hostname;

select user();

show grants;(查看授权)

grant select,update(name,uid) on db3.user to webuser@"%" identified by "123qqq...A";(可以修改查看)

mysql -h192.168.4.50   -uwebuser  -p123qqq...A

在50主机上查看授权信息

select user,host from mysql.user;

select * from mysql.db \G;

select * from mysql.tables_priv \G;

select * from mysql.colimns_priv \G;

权限撤销(删除新添加用户的访问权限)

MYSQL>revoke  权限 on  数据库名   from    用户名@"客户端地址"

select user,host from mysql.user;

show grants for root@"192.168.4.52";

revoke grant option on *.* from root@"192.168.4.52";

select * from mysql.user where user="root" and host="192.168.4.52"/G;

两种方法：(撤销单个权限)

update mysql.user set Delete_priv="N" where user="root" and host="192.168.4.52";

flush frivleges;

select * from mysql.user where user="root" and host="192.168.4.52"\G;

show grants for root@"192.168.4.52";

(撤销所有权限)

select * from  mysql.user where user="root" and host="192.168.4.52"\G;

revoke all on *.* from root@192.168.4.52";

show grants for root@"192.168.4.52";

删除授权用户(删除添加的连接用户)

mysql>drop user 用户名@"客户端地址:;

drop user root@"192.168.4.52";

授权信息的授权库mysql里  user  db  tables_priv  columns_priv

grant  revoke  drop  user  用户@"客户端地址";

    show  grants for 用户@"客户端地址";

show grants;

修改密码：

授权用户连接数据库服务器后修改连接密码

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

管理员重置授权用户的密码

mysql>set password for 用户名@"客户端地址"=password（"新密码");(只能用字母数字符号组合)

 

 

数据库管理
NSD DBA 基础
DAY04内容
09:00 ~ 09:30
上午
09:30 ~ 10:20
10:30 ~ 11:20
11:30 ~ 12:00
作业讲解和回顾
多表查询
MySQL 管理工具
14:00 ~ 14:50
下午
15:00 ~ 15:50
用户授权及撤销
16:10 ~ 17:00
17:10 ~ 18:00
总结和答疑多表查询
多表查询
复制表 复制表
多表查询 多表查询概述
Where 嵌套嵌套查询
多表查询
左连接查询
右连接查询复制表复制表
• 将源表 xxx 复制为新表 yyy
– CREATE TABLE yyy SELECT * FROM xxx;
知
识
讲
解
• 将指定的查询结果复制为新表 zzz
– CREATE TABLE zzz SQL 查询语句 ;复制表(续 1 )
• 复制源表 xxx 的结构到新表 vvv
– CREATE TABLE vvv SELECT * FROM xxx WHERE FALSE;
知
识
讲
解
• 将源表 vvv 的名称改为 www
– ALTER TABLE vvv RENAME TO www;多表查询多表查询概述
• 多表查询(又称 连接查询)
知
识
讲
解
– 将 2 个或 2 个以上的表 按某个条件连接起来,从中选
取需要的数据
– 当多个表中 存在相同意义的字段(字段名可以不同)
时,可以通过该字段连接多个表多表查询
• 格式 1
– select 字段名列表 from 表 a, 表 b ;
知
识
讲
解
• 格式 2
– select 字段名列表 from 表 a, 表 b where 条件;
以上格式的查询结果叫笛卡尔集 显示查询结果的总条目数是 (表 a 的纪
录数 * 表 b 的纪录数)where 子查询
• 使用 where 子查询
– 把内层查询结果作为外层查询的查询条件
• 格式 1
知
识
讲
解
– select 字段名列表 from 表 A where
lect 字段名列表 from 表 A ) ;
条件 ( se
– select 字段名列表 from 表 A where 条件 ( s
elect 字段名列表 from 表 A where 条件 ) ;
– 输出年龄小于平均年龄的学生的名字和年龄
select name,age from student where age < (select a
vg(age) from \ student);左连接查询
• 基本用法
知
识
讲
解
– select 字段名列表 from
LEFT JOIN
表b
ON
条件表达式;
表a右连接查询
• 基本用法
知
识
讲
解
– select 字段名列表 from
RIGHT JOIN
表b
ON
条件表达式;
表aMySQL 管理工
具
MySQL 管理方式
MySQL 管理方式
常见的 MySQL 管理工具
MySQL 管理工具
MySQL 管理工具
MySQL Workbench
MySQL Front
PhpMyAdminMySQL 管理方式MySQL 管理方式
• mysql 命令的局限性?
• 是否有图形化、更加直观的管理软件?
知
识
讲
解
• 能通过 Web 界面访问吗?常见的 MySQL 管理工具
• 常见的管理工具
类 型
知
识
讲
解
界 面
操作系统
说 明
mysql 命令行 跨平台 MySQL 官方 bundle 包自
带
MySQL-Workbench 图形 跨平台 MySQL 官方提供
MySQL-Front 图形 Windows 开源,轻量级客户端软件
phpMyAdmin 浏览器 跨平台 开源,需 LAMP 平台
Navicat 图形 Windows 专业、功能强大,商业版MySQL 管理工具MySQL Workbench
知
识
讲
解MySQL Front
知
识
讲
解PhpMyAdmin
• 基本思路
1. 安装 httpd 、 mysql 、 php-mysql 及相关包
知
识
讲
解
2. 启动 httpd 服务程序
3. 解压 phpMyAdmin 包,部署到网站目录
4. 配置 config.inc.php ,指定 MySQL 主机地址
5. 创建授权用户
6. 浏览器访问、登录使用PhpMyAdmin (续 1 )
#yum -y install httpd php php-mysql
#tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
#cd /var/www/html/
#mv phpMyAdmin-2.11.11-all-languages/ phpmyadmin
知
识
讲
解
#chown -R apache:apache phpmyadmin/
#cp phpmyadmin/config.sample.inc.php phpmyadmin/config.inc.php
# sed -n '17p;31p' /var/www/html/phpmyadmin/config.inc.php
$cfg['blowfish_secret'] = 'plj123';
$cfg['Servers'][$i]['host'] = 'localhost';
mysql>create database bbsdb;grant all on bbsdb.* to admin@”localhos
t” identified by “123456”;
#systemctl start httpd ; firefox http://localhost/phpmyadminPhpMyAdmin (续 2 )
知
识
讲
解案例 1 : MySQL 管理工具
• 部署 LAMP+phpMyAdmin 平台
课
堂
练
习用户授权及权限撤销
密码恢复及设置
恢复 MySQL 管理员密码
设置管理员密码
用户授权及权限撤
销
用户授权
MySQL 授权库和表信息
GRANT 配置授权
查看用户授权
重设用户密码
权限撤销
撤销用户权限密码恢复及设置恢复 MySQL 管理密码
• 密码忘了怎么办?
1. 停止 MySQL 服务程序
知
识
讲
解
2. 跳过授权表启动 MySQL 服务程序
skip-grant-tables
写入
写入 /etc/my.cnf
/etc/my.cnf 配置文件
配置文件
3. 重设 root 密码(更新 user 表记录)
4. 以正常方式重启 MySQL 服务程序恢复 MySQL 管理密码(续 1 )
• 主要操作过程
知
识
讲
解
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
......
skip-grant-tables
::wq
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql
[mysql> update mysql.user set
authentication_string=password("888888")
-> where
-> user="root" and host="localhost";
mysql> flush privileges;
mysql> quit恢复 MySQL 管理密码(续 2 )
• 主要操作过程
知
识
讲
解
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
......
#skip-grant-tables
::wq
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p888888
[mysql>设置管理员密码
• 查看模块、修改配置文件、重启服务
知
识
讲
解
[root@localhost ~]# find / -name "validate_password.so"
/usr/lib64/mysql/plugin/debug/validate_password.so
/usr/lib64/mysql/plugin/validate_password.so
[root@localhost ~]#
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
plugin-load=validate_password.so // 加载模块
validate-password=FORCE_PLUS_PERMANENT // 永久启用模块
validate_password_policy=0 // 只验证密码长度
validate_password_length=6 // 指定密码长度
[root@localhost ~]# systemctl restart mysqld设置管理员密码(续 1 )
• 在 Shell 命令行修改登陆密码
知
识
讲
解
– 需要验证旧密码
– 不适用于跳过授权表启动的情况
[root@localhost ~]# mysqladmin -hlocalhost -uroot -p password
" 新密码 "
Enter password: // 输入旧密码
mysqladmin: [Warning] Using a password on the command line
interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl
connection to ensure password safety.
[root@localhost ~]#案例 2 :密码恢复及设置
1. 恢复 MySQL 管理密码
2. 正常设置管理密码
课
堂
练
习创建授权MySQL 授权库和表信息
• 授权库 mysql ,主要的几个表
知
识
讲
解
– user 表,存储授权用户的访问权限
– db 表,存储授权用户对数据库的访问权限
– tables_priv 表,存储授权用户对表的访问权限
– columns_priv 表,存储授权用户对字段的访问权限GRANT 配置授权
• 基本用法
知
识
讲
解
– GRANT 权限列表 .. .. ON 库名 . 表名
– TO 用户名 @' 客户端地址 '
设置密码
– IDENTIFIED BY ' 密码 '
– [ WITH GRANT OPTION ];
是否有授权权限
• 注意事项
– 当库名 . 表名 为 *.* 时,匹配所有库所有表
– 授权设置存放在 mysql 库的 user 表GRANT 配置授权(续 1 )
• 权限列表
知
识
讲
解
– all :匹配所有权限
– SELECT,UPDATE,INSERT .. ..
– SELECT,UPDATE ( 字段 1, .. .. , 字段 N)GRANT 配置授权(续 2 )
• 客户端地址
知
识
讲
解
– % :匹配所有主机
– 192.168.1.% :匹配指定的一个网段
– 192.168.1.1 :匹配指定 IP 地址的单个主机
– %.tarena.com :匹配一个 DNS 区域
– svr1.tarena.com :匹配指定域名的单个主机GRANT 配置授权(续 3 )
• 应用示例
知
识
讲
解
– 新建用户 mydba ,对所有库、表有完全权限
– 允许从任何地址访问,密码设为 s1cr2t!
– 允许该用户为其他用户授权GRANT 配置授权(续 4 )
• 应用示例
知
识
讲
解
– 授权 sqler01 用户,允许从 192.168.4.0/24 网段访问
,对 userdb 库的 user 表有查询权限,登陆密码 8888
88
– 授权 sqler02 用户,允许从本机访问,允许对 userdb
库的任何表有查询
更新 / 插入
mysql>
grant select on / userdb.user
to / 删除权限,密码为 1
sqler01@"192.168.4.0/24"
identified by "888888";
23456
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant select,insert,update,delete on userdb.* to
sqler02@"localhost" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)查看用户授权
• 用户查看自己的权限
– SHOW GRANTS;
知
识
讲
解查看用户授权(续 1 )
• 管理员可查看其他用户的权限
– SHOW GRANTS FOR 用户名 @' 客户端地址 ';
知
识
讲
解重设用户密码
• 授权用户修改自己的密码
– SET PASSWORD=PASSWORD(' 新密码 ');
知
识
讲
解
• 管理员可重设其他用户的密码
– SET PASSWORD
FOR 用户名 @' 客户端地址 '=PASSWORD(' 新密码 ');撤销授权撤销用户权限
• 基本用法
知
识
讲
解
– REVOKE 权限列表 ON 库名 . 表名
– FROM 用户名 @' 客户端地址 ';案例 3 :用户授权及撤销
1. 允许 root 从 192.168.4.0/24 网段 访问,对所有库
/ 表有完全权限,密码为 tarena
课
堂
练
习
2. 添加一个管理账号 dba007 ,完全控制及授权
3. 撤销 root 从本机访问的权限,然后恢复
4. 允许 webuser 从任意客户机登录,只对 webdb 库
有完全权限,密码为 888888
5. 撤销 webuser 的完全权限,改为查询权限总结和答疑
撤销用户权限
问题现象
故障分析及排除
总结和答疑撤销用户权限问题现象
• 无法撤销用户的授权
知
识
讲
解
– 报错 1 : ERROR 1147 (42000)
– 报错 2 : no such grant defined for user
mysql> revoke select on webdb.a from
'webadmin'@'172.40.51.106';
ERROR 1147 (42000): There is no such grant defined for user
'webadmin' on host '172.40.51.106' on table 'a'
mysql>故障分析及排除
• 原因分析
知
识
讲
解
– 授权用户对目标没有权限
– 只有给用户对目标库做过授权才可以撤销权限
• 解决办法
– 查看用户的授权信息,看用户是否对库有权限
[root@dbsvr1 ~]# mysql -uroot -p123
mysql> show grants for 'webadmin'@'172.40.51.106';