多表查询 图形工具 用户授权与撤销

多表查询 复制表

复制表

语法

CREATE 库名.表名 select * from 库名.表名;

功能

1. 备份表
2. 快速建表
3. 只保留表结构

mysql> create database db4;

#把 db1.use 表复制到 db4.t1 表中
mysql> create table db4.t1 select * from db1.user;
#复制的表不带键值(KEY 值)
mysql> desc db1.user;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(2)              | NO   | PRI | NULL    | auto_increment |
| name     | char(30)            | YES  | MUL | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
mysql> desc db4.t1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | int(2)              | NO   |     | 0       |       |
| name     | char(30)            | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+

#从 db1.user 复制id,name前十行到 db4.t2
mysql> create table db4.t2 select id,name from db1.user limit 10;

#复制db1.user表结构到db4.t3
mysql> create table db4.t3 select * from db1.user where 1=2;

#将原表 vvv 名称改为 www
mysql> alter table t3 rename t4;

多表查询

概述

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

where 嵌套查询

• 把内层的查询结果作为外层查询的查询条件

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

#同表嵌套 找出uid小于平均值的uid
mysql> select name,uid from db1.user where uid < (select avg(uid) from db1.user);
+----------+------+
| name     | uid  |
+----------+------+
| root     |    0 |
| bin      |    1 |
| daemon   |    2 |
| adm      |    3 |
| lp       |    4 |
| sync     |    5 |
| shutdown |    6 |
| halt     |    7 |
| mail     |    8 |
| operator |   11 |
| games    |   12 |
| ftp      |   14 |
| dbus     |   81 |
| sshd     |   74 |
| ntp      |   38 |
| mysql    |   27 |
+----------+------+

#找出uid大于平均值的uid
mysql> select name,uid from t1 where uid > (select avg(uid) from t1);
+-----------------+------+
| name            | uid  |
+-----------------+------+
| systemd-network |  192 |
| polkitd         |  999 |
| nginx           |  998 |
+-----------------+------+


#不同表嵌套 查询db4.t1表中 name 且 要求在mysql.user中存在 host=localhost
mysql> select name from db4.t1 where name in (select user from mysql.user where host="localhost");
+------+
| name |
+------+
| root |
+------+

多表查询语法

select 字段名列表 from 表a,表b;

select 字段名列表 from 表a,表b where 条件;

• 以上格式的查询结果叫笛卡尔集,查询结果的总条目数是 = ( 表a的记录数 x 表b的记录数 )

示例

mysql> create table t5 select name,uid,shell from db1.user limit 3;
mysql> create table t6 select name,uid,gid,homedir from db1.user limit 5;
mysql> select * from t5;
+--------+------+---------------+
| name   | uid  | shell         |
+--------+------+---------------+
| root   |    0 | /bin/bash     |
| bin    |    1 | /sbin/nologin |
| daemon |    2 | /sbin/nologin |
+--------+------+---------------+
mysql> select * from t6;
+--------+------+------+----------+
| name   | uid  | gid  | homedir  |
+--------+------+------+----------+
| root   |    0 |    0 | /student |
| bin    |    1 |    1 | /student |
| daemon |    2 | NULL | /student |
| adm    |    3 |    4 | /student |
| lp     |    4 |    7 | /student |
+--------+------+------+----------+
#笛卡尔集
mysql> select * from t5,t6;
+--------+------+---------------+--------+------+------+----------+
| name   | uid  | shell         | name   | uid  | gid  | homedir  |
+--------+------+---------------+--------+------+------+----------+
| root   |    0 | /bin/bash     | root   |    0 |    0 | /student |
| bin    |    1 | /sbin/nologin | root   |    0 |    0 | /student |
| daemon |    2 | /sbin/nologin | root   |    0 |    0 | /student |
| root   |    0 | /bin/bash     | bin    |    1 |    1 | /student |
| bin    |    1 | /sbin/nologin | bin    |    1 |    1 | /student |
| daemon |    2 | /sbin/nologin | bin    |    1 |    1 | /student |
| root   |    0 | /bin/bash     | daemon |    2 | NULL | /student |
| bin    |    1 | /sbin/nologin | daemon |    2 | NULL | /student |
| daemon |    2 | /sbin/nologin | daemon |    2 | NULL | /student |
| root   |    0 | /bin/bash     | adm    |    3 |    4 | /student |
| bin    |    1 | /sbin/nologin | adm    |    3 |    4 | /student |
| daemon |    2 | /sbin/nologin | adm    |    3 |    4 | /student |
| root   |    0 | /bin/bash     | lp     |    4 |    7 | /student |
| bin    |    1 | /sbin/nologin | lp     |    4 |    7 | /student |
| daemon |    2 | /sbin/nologin | lp     |    4 |    7 | /student |
+--------+------+---------------+--------+------+------+----------+

#只显示条件匹配的值  显示t5,t6 uid相等的集
mysql> select * from t5,t6 where t5.uid=t6.uid;
+--------+------+---------------+--------+------+------+----------+
| name   | uid  | shell         | name   | uid  | gid  | homedir  |
+--------+------+---------------+--------+------+------+----------+
| root   |    0 | /bin/bash     | root   |    0 |    0 | /student |
| bin    |    1 | /sbin/nologin | bin    |    1 |    1 | /student |
| daemon |    2 | /sbin/nologin | daemon |    2 | NULL | /student |
+--------+------+---------------+--------+------+------+----------+

#显示uid相等 t5 shell字段 t6全部字段
mysql> select t5.shell,t6.* from t5,t6 where t5.uid=t6.uid;
+---------------+--------+------+------+----------+
| shell         | name   | uid  | gid  | homedir  |
+---------------+--------+------+------+----------+
| /bin/bash     | root   |    0 |    0 | /student |
| /sbin/nologin | bin    |    1 |    1 | /student |
| /sbin/nologin | daemon |    2 | NULL | /student |
+---------------+--------+------+------+----------+

#显示uid相等 t5 name字段 t6 name字段
mysql> select t5.name,t6.name from t5,t6 where t5.uid=t6.uid;
+--------+--------+
| name   | name   |
+--------+--------+
| root   | root   |
| bin    | bin    |
| daemon | daemon |
+--------+--------+

连接查询:

• 包括: 交叉连接 自然连接 内连接 外连接

外连接 ( 生产环境主要使用 )	功能
左连接	当条件成立时,以左表为主显示查询结果
右连接	当条件成立时,以右表为主显示查询结果

左连接

select  字段名列表 from 表a LEFT JOIN 表b on 条件表达式;

#左连接查询t5t6表 显示所有字段 要求uid相等
mysql> select * from t5 left join t6 on t5.uid=t6.uid;
+--------+------+---------------+--------+------+------+----------+
| name   | uid  | shell         | name   | uid  | gid  | homedir  |
+--------+------+---------------+--------+------+------+----------+
| root   |    0 | /bin/bash     | root   |    0 |    0 | /student |
| bin    |    1 | /sbin/nologin | bin    |    1 |    1 | /student |
| daemon |    2 | /sbin/nologin | daemon |    2 | NULL | /student |
+--------+------+---------------+--------+------+------+----------+
#左连接查询t5t6 显示t5表所有字段 要求uid相等
mysql> select t5.* from t5 left join t6 on t5.uid=t6.uid;
+--------+------+---------------+
| name   | uid  | shell         |
+--------+------+---------------+
| root   |    0 | /bin/bash     |
| bin    |    1 | /sbin/nologin |
| daemon |    2 | /sbin/nologin |
+--------+------+---------------+
#左连接查询t5,t6 显示需要字段 要求uid相等
mysql> select t5.name,t5.uid,t6.gid,t6.homedir,t5.shell from t5 left join t6 on t5.uiid=t6.uid;
+--------+------+------+---------+---------------+
| name   | uid  | gid  | homedir | shell         |
+--------+------+------+---------+---------------+
| root   |    0 |    0 | /root   | /bin/bash     |
| bin    |    1 |    1 | /bin    | /sbin/nologin |
| daemon |    2 |    2 | /sbin   | /sbin/nologin |
+--------+------+------+---------+---------------+

右连接

select 字段名列表 from 表a right jion 表b on 条件表达式;

#右连接查询t5t6表 显示所有字段 要求uid相等
mysql> select * from t5 right join t6 on t5.uid=t6.uid;
+--------+------+---------------+--------+------+------+----------+
| name   | uid  | shell         | name   | uid  | gid  | homedir  |
+--------+------+---------------+--------+------+------+----------+
| root   |    0 | /bin/bash     | root   |    0 |    0 | /student |
| bin    |    1 | /sbin/nologin | bin    |    1 |    1 | /student |
| daemon |    2 | /sbin/nologin | daemon |    2 | NULL | /student |
| NULL   | NULL | NULL          | adm    |    3 |    4 | /student |
| NULL   | NULL | NULL          | lp     |    4 |    7 | /student |
+--------+------+---------------+--------+------+------+----------+

MySQL管理工具

常见工具

类型	界面	操作系统	说明
MySQL-cli	命令行	跨平台	MySQL官方bundle包自带
MySQL-Workbench	图形	跨平台	MySQL官方提供
MySQL-Front	图形	Windows	开源,轻量级客户端
phpMyAdmin	浏览器	跨平台	开源,需要LNMP
Navicat	图形	Windows	专业,功能强大,商业版

phpMyAdmin 搭建

部署软件运行环境

1. LNMP nginx+mysql+phpmysqlamdin包
2. 启动 nginx
3. 解压phpMyAdmin包,部署到网站目录
4. 配置config.inc.php,指定MySQL主机地址
5. 创建授权用户
6. 浏览器访问,登陆使用

安装

• php需要扩展如下 php-cli php-fpm php-mbstring php-mysqlnd

yum install nginx php73-cli php73-php-fpm php73-php-mbstring php73-php-mysqlnd

下载phpmyadmin

部署好环境,解压下载的phpmyadmin至 web服务器 根目录

修改配置

#cp -a config.sample.inc.php config.inc.php
#sed -n '17p;31p' /data/www/phpmyadmin/config.inc.php
$cfg['blowfish_secret'] = 'sdfsblkekebl';  #需32位字符,否则会告警太短
$cfg['Servers'][$i]['host'] = 'localhost';  #默认连接本地数据库,可修改ip连接远程数据库

访问 http://IP/myphpamdin

用户授权 权限撤销

密码恢复及设置

重置 root 密码

#本机命令行 重置root用户密码 需要输入原密码
mysqladmin -hlocalhost -uroot -p password
Enter password: 
New password: 
Confirm new password: 

恢复 root 密码

1. 停止 MySQL 服务器程序

2. 跳过授权表启动 MySQL 服务程序

# my.cnf 文件 增加跳过授权表配置
[mysqld]
skip-grant-tables

3. 重启 MySQL 服务,进入命令行,重设root密码

#查看 mysql.user 表.
mysql> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *83083E0D6B82A44D06017382670A658A9A4EE1F4 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+

#password函数用法
mysql> select password("123123");
+-------------------------------------------+
| password("123123")                        |
+-------------------------------------------+
| *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-------------------------------------------+

#修改mysql.user root用户对应密码 authentication_string 字段.
mysql> update mysql.user set authentication_string=password("123123") where user="root";

#使修改密码生效
mysql> flush privileges

4. 恢复 my.cnf 配置文件 ,重启 MySQL 服务.使用新密码登陆.

用户授权

• 为数据库添加可以连接的新用户,并设置新用户的访问权限.

• 默认只有数据库管理( root@localhost ) 在本机能够访问数据库服务.

MySQL 授权库和表

• 授权存储在 mysql 库里.使用不同的表存储授权信息

mysql库里重要表	说明
user	存储授权用户的访问权限
db	存储授权用户对数据库的访问权限
tables_priv	存储授权用户对表的访问权限
columns_priv	存储授权用户对字段的访问权限

示例

#查看 数据库 所有用户
mysql> select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| webuser       | 192.168.1.%   |
| yaya          | 192.168.1.%   |
| admin         | 192.168.1.102 |
| jing          | localhost     |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+

#查看 用户 yaya@'192.168.1.%' 权限
mysql> show grants for yaya@'192.168.1.%';
+--------------------------------------------------------------------------+
| Grants for yaya@192.168.1.%                                              |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya'@'192.168.1.%'                               |
| GRANT SELECT, UPDATE (uid, name) ON `db1`.`user` TO 'yaya'@'192.168.1.%' |
+--------------------------------------------------------------------------+

#查看用户 admin@192.168.1.102 的访问权限
mysql> select * from mysql.user where user="admin"\G;
*************************** 1. row ***************************
                  Host: 192.168.1.102
                  User: admin
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1
      password_expired: N
 password_last_changed: 2019-01-21 14:01:13
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

#查看用户 yaya@192.168.1.% 的 表 访问权限.
mysql> select host,user,db,table_name,table_priv from mysql.tables_priv where user="yaya" and host="192.168.1.% ";
+-------------+------+-----+------------+------------+
| host        | user | db  | table_name | table_priv |
+-------------+------+-----+------------+------------+
| 192.168.1.% | yaya | db1 | user       | Select     |
+-------------+------+-----+------------+------------+

MySQL 权限列表

命令	权限
usage	无权限
select	查询表记录
insert	插入表记录
update	更新表记录
delete	删除表记录
create	创建库,表
drop	删除库,表
reload	有重新载入授权 必须拥有reload权限,才可以执行flush [ tables | logs | privileges ]
shutdown	允许关闭mysql服务,使用mysqladmin shutdown 来关闭mysql
process	允许查看用户登陆数据库服务器的进程( show processlist; )
file	导入,导出数据
references	创建外键
index	创建索引
alter	修改表结构
show databases	查看库
super	关闭属于任何用户的线程
create temporary tables	允许在create table 语句中使用 temprory关键字
lock tables	允许使用lock tables语句

GRANT 语法

• 默认数据管理员root本机登陆有授权权限

grant 权限列表 on 数据库名 to  "用户名"@"客户端地址" identified by "密码" [with grant option];

示例

#所有权限
grant all on *.* to admin@'192.168.4.52' identified by "123123" with grant option;

#查询,插入,更新,删除权限
grant select,insert,update,delete on db1.* to webuser@"192.168.4.%" identified by "123123";

#查询,更新name和uid字段权限,必须写表名,否则无法识别字段
grant select,update(name.uid),delete on db1.user to yaya@"%" identified by "123123";

#无权限用户,只能连接
grant usage on *.* identified by "123123";

查看用户授权

用户查看自己的权限

SHOW GRANTS;

管理员查看其他用户权限

SHOW GRANTS FOR 用户名@'客户端地址';

示例

#查看当前用户权限
mysql> show grants;
+--------------------------------------------------------------------------+
| Grants for admin@192.168.1.102                                           |
+--------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.1.102' WITH GRANT OPTION |
+--------------------------------------------------------------------------+

#查看某一用户权限(管理员使用)
mysql> show grants for yaya@"%";
+--------------------------------------------------------------------+
| Grants for yaya@%                                                  |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya'@'%'                                   |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.`user` TO 'yaya'@'%' |
+--------------------------------------------------------------------+

#通过@@hostname 查看数据库服务器hostname
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| test1      |

#查看当前用户
mysql> select user();
+---------------------+
| user()              |
+---------------------+
| admin@192.168.1.102 |
+---------------------+

重设用户密码

授权用户连接后修改密码

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

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

set password for 用户名@'客户的地址'=password("新密码");

权限撤销

撤销用户权限.

• 对数据库有过授权,授权才可以撤销权限.

revoke 权限列表 on 数据库名 from 用户名@"客户端地址";

删除授权用户

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