多表查询 图形工具 用户授权与撤销
多表查询 复制表
复制表
语法
CREATE 库名.表名 select * from 库名.表名;
功能
- 备份表
- 快速建表
- 只保留表结构
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 搭建
部署软件运行环境
- LNMP nginx+mysql+phpmysqlamdin包
- 启动 nginx
- 解压phpMyAdmin包,部署到网站目录
- 配置config.inc.php,指定MySQL主机地址
- 创建授权用户
- 浏览器访问,登陆使用
安装
- php需要扩展如下 php-cli php-fpm php-mbstring php-mysqlnd
yum install nginx php73-cli php73-php-fpm php73-php-mbstring php73-php-mysqlnd
部署好环境,解压下载的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连接远程数据库
用户授权 权限撤销
密码恢复及设置
重置 root 密码
#本机命令行 重置root用户密码 需要输入原密码
mysqladmin -hlocalhost -uroot -p password
Enter password:
New password:
Confirm new password:
恢复 root 密码
停止 MySQL 服务器程序
跳过授权表启动 MySQL 服务程序
# my.cnf 文件 增加跳过授权表配置
[mysqld]
skip-grant-tables
- 重启 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
- 恢复 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 用户名@"客户端地址";
