DBA DAY04
一、多表查询
stu_id name class
缴费表
班级表
成绩表
就业表
1.1 复制表 （作用： 备份表 、 快速建表）
mysql> create table 表名 sql查询；create database db4;
use db4;
create table user2 select * from db3.user;
show tables;
select * from user2;
create table user3 select name,uid,shell from db3.user order by uid desc limit 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;
1.2 多表查询
格式 1 : select 字段名列表 from 表名列表[ where 条件]；
use db4;
create table t1 select name,uid,shell from db3.user limit 3;
select * from t1;
create table t2 select name,password,uid,homedir from db3.user limit 5;
select * from t2;
笛卡尔集 3 * 5 = 15
select * from t1,t2; //会产生笛卡尔积
select t1.name ,t2.name from t1,t2;
select t1.* ,t2.password,t2.homedir from t1,t2;
select t1.* , t2.password , t2.homedir from t1,t2 where t1.uid = t2.uid;
格式2 嵌套查询： 把内层的查询结果作为外层查询的查询条件
select 字段名列表 from 表名 where 条件 （select 字段名列表 from 表名 where 条件）；
select name from db3.user order by uid desc limit 1;
select avg(uid) from db3.user;
select name,uid from db3.user where uid<( select avg(uid) from db3.user);
select name from db4.t1;
select name from db3.user where name in (select name from db4.t1);
格式3 连接查询
左连接查询 ： 以左边的表为主显示查询结果
select 字段名列表 from 表名A left join 表名B on 条件；
右连接查询 ： 以右边的表为主显示查询结果
select 字段名列表 from 表名A right join 表名B on 条件；
create table db4.t3 select name,uid,shell from db3.user limit 5;
select * from db4.t3;
create table db4.t4 select name,uid,shell from db3.user limit 7;
select * from db4.t4;
select * from t3 left join t4 on t3.uid = t4.uid;
select * from t3 right join t4 on t3.uid = t4.uid;
select t3.name,t4.name from t3 right join t4 on t3.uid = t4.uid;
二、MySQL图形管理工具 *
192.168.4.50
phpMyAdmin-2.11.11-all-languages.tar.gz
步骤1 ：部署运行环境lamp 或 lnmp
rpm -q httpd
yum -y install httpd
systemctl start httpd
systemctl enable httpd
rpm -q php
rpm -q php-mysql
yum -y install php php-mysql
systemctl restart httpd
步骤2 ：安装软件phpMyAdmin
tar -zxvf phpMyAdmin-2.11.11-all-languages.tar.gz
ls phpMyAdmin-2.11.11-all-languages
mv phpMyAdmin-2.11.11-all-languages /var/www/html/phpadmin
cd /var/www/html/
步骤3 : 创建配置，指定管理数据库服务器。
]#cd phpadmin
]#cp config.sample.inc.php config.inc.php
]#vim config.inc.php
<?php
.....
17 $cfg['blowfish_secret'] = 'pljabc';
31 $cfg['Servers'][$i]['host'] = 'localhost';
......
?>
:wq
步骤4 : 客户端访问
打开浏览器输入URL http://192.168.4.50/phpadmin
用户 root
密码 123456
三、用户授权 ** （在数据库服务器上添加 连接时使用的用户名）
用户授权： 在数据库服务器上添加新的连接用户名，默认只有数据库管理员root用户在数据库服务器本机登录有授权权限。默认情况下管理员只能从数据库本机连接服务，在没有其他用户能够连接数据库服务器了。
授权命令格式：
]# mysql -uroot -p123456
mysql>
grant 权限列表 on 数据库名 to 用户名@" 客户端地址"
identified by "密码" [ with grant option];
with grant option 可选， 让添加的用户连接服务器后，也有授权权限。
identified by "密码" 新添加的用户连接数据库服务器时，使用的密码
客户端地址 作用：网络中的那些主机可以使用新添加的用户连接数据库服务器。有如下表示方式：
所有主机 %
网段 192.168.4.%
指定主机 192.168.4.51
本机 localhost
主机名 pc1.tedu.cn
域名 %.tedu.cn
用户名 客户端主机连接数据库服务器时使用的名字，授权时自定义既可名字要有标识性。
数据库名 ： 新添加的连接用户，访问后可以操作的库，表示方式如下：
*.* 所有库 所有表
库名.* 库下的所有表
库名.表明 某张表
权限列表 ： 新添加的连接用户，对可以操作的库的访问权限，权限的表示方式如下：
all 所有权限
命令,命令 某种权限 (例如 select , insert ,delete）
授权库 mysql 库记录授权信息，使用不同的表记录不同的授权信息
use mysql
user 记录已经添加的连接用户名
db 记录已添加的连接用户对库的访问权限
tables_priv 记录已添加的连接用户对表的访问权限
columns_priv 记录已添加的连接用户对表字段的访问权限
desc user;
desc db;
desc tables_priv;
desc columns_priv;
select * from user\G;
select * from db;
select * from tables_priv;
select * from columns_priv;
select user from mysql.user;
select user,host from mysql.user;
查看授权用户的访问权限
show grants for 用户@"客户端地址"；
show grants for root@"localhost";
select * from user where user="root" and host="localhost"\G;
授权例子1
在50服务器上添加用户admin
mysql> grant select,insert on db3.* to admin@"192.168.4.%" identified by "123qqq...A";
mysql> select user from mysql.user where user="admin";
在客户端51使用服务新添加的用户连接主机50 验证例子1 授权
]# mysql -h192.168.4.50 -uadmin -p123qqq...A
mysql> select @@hostname; //查看当前主机的主机名
mysql> select user(); //查看当前用户的名称和登录的主机
mysql> show grants; //查看自己拥有的权限
mysql> 执行sql命令验证权限
授权例子2
在50服务器 授权主机52 可以使用root 连接自己，对所有库表有完全权限且有授权权限 登录密码是123qqq...A
grant all on *.* to root@"192.168.4.52" identified by "123qqq...A" with grant option;
在客户端使用服务新添加的用户连接主机50 验证例子2 授权
[root@host52 ~]# mysql -h192.168.4.50 -uroot -p123qqq...A
mysql> select @@hostname;
mysql> select user();
mysql> show grants;
mysql> 测试对库表的访问权限
mysql> grant select,update(name,uid) on db3.user to webuser@"%" identified by "123qqq...A"; #测试授权权限
在客户端使用有授权权限用户 添加用户连接数据库服务器50
]# mysql -h192.168.4.50 -uwebuser -p123qqq...A
mysql> show grants;
mysql> 测试访问权限
在50主机上查看授权信息
select user,host from mysql.user;
select * from mysql.db \G;
select * from mysql.tables_priv \G;
select * from mysql.columns_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"; //本质上还是把user表中的Grant_priv修改为Y
show grants for root@"192.168.4.52";
select * from mysql.user where user="root" and host="192.168.4.52"\G;
修改记录的方式 撤销用户的权限
mysql> update mysql.user set Delete_priv="N" where user="root" and host="192.168.4.52"；
MySQL>flush privileges;
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 用户名@"客户端地址";
mysql> drop user root@"192.168.4.52";
SELECT, INSERT, UPDATE, CREATE, DROP, 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
修改密码：
授权用户连接数据库服务器后修改连接密码
mysql> set password=password("新密码"); //本质是修改user表中的authentication_string字段
管理员重置授权用户的连接密码
mysql> set password for 用户名@"客户端地址"=password("新密码");
