数据库基础五"/>
MySQL数据库基础五
1 复制表 作用：1 备份表 2快速建表
（1）复制的表结构和数据，主键和自增属性不会复制
格式 ：create table yy select *from passwd;
mysql> create1 table db4.passwd1 select * from db3.passwd;
（2） 复制表结构：（不复制数据记录）
mysql> create table db4.passwd2 select * from db3.passwd where 1=2;（where false效果一样)
（3） 复制表的指定字段和指定行数
mysql> create table passwd3 select id,name,uid,gid from db3.passwd limit 8;
2 多表查询 （连接查询）
将两个和两个以上的表，按某个条件连接起来，从中选取需要的数据
当多个表中存在相同意义的字段（字段名可以不同）时，可以通过该字段连接多个表
格式1：select 字段名列表 from 表a,表b;
格式2： select 字段名列表 from 表a,表b where 条件;
以上格式的查询结果叫迪卡尔集 （a记录×b记录 ） 显示总记录数为此乘积
（1）创建表t2（3条记录）
mysql> create table db4.t2 select name,uid,shell from db3.passwd limit 3;
mysql> select * from t2;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 0 | /bin/bash |
| bin | 1 | /sbin/nologin |
| daemon | 2 | /sbin/nologin |
+--------+------+---------------+
3 rows in set (0.00 sec)
（2）创建表t3 （5条记录）
mysql> create table db4.t3 select name,uid,homedir from db3.passwd limit 5
mysql> select * from t3;
+--------+------+----------------+
| name | uid | homedir |
+--------+------+----------------+
| root | 0 | /root |
| bin | 1 | /bin |
| daemon | 2 | /sbin |
| adm | 3 | /var/adm |
| lp | 4 | /var/spool/lpd |
+--------+------+----------------+
（3）迪卡尔集查询 （3×5条记录 查询的总条目数=表a的记录数×表b的记录数）
mysql> select * from t2,t3;
+--------+------+---------------+--------+------+----------------+
| name | uid | shell | name | uid | homedir |
+--------+------+---------------+--------+------+----------------+
| root | 0 | /bin/bash | root | 0 | /root |
| bin | 1 | /sbin/nologin | root | 0 | /root |
| daemon | 2 | /sbin/nologin | root | 0 | /root |
| root | 0 | /bin/bash | bin | 1 | /bin |
| bin | 1 | /sbin/nologin | bin | 1 | /bin |
| daemon | 2 | /sbin/nologin | bin | 1 | /bin |
| root | 0 | /bin/bash | daemon | 2 | /sbin |
| bin | 1 | /sbin/nologin | daemon | 2 | /sbin |
| daemon | 2 | /sbin/nologin | daemon | 2 | /sbin |
| root | 0 | /bin/bash | adm | 3 | /var/adm |
| bin | 1 | /sbin/nologin | adm | 3 | /var/adm |
| daemon | 2 | /sbin/nologin | adm | 3 | /var/adm |
| root | 0 | /bin/bash | lp | 4 | /var/spool/lpd |
| bin | 1 | /sbin/nologin | lp | 4 | /var/spool/lpd |
| daemon | 2 | /sbin/nologin | lp | 4 | /var/spool/lpd |
+--------+------+---------------+--------+------+----------------+
15 rows in set (0.00 sec)
（4）带条件查询，显示uid相同的记录
mysql> select * from t2,t3 where t2.uid=t3.uid;
+--------+------+---------------+--------+------+---------+
| name | uid | shell | name | uid | homedir |
+--------+------+---------------+--------+------+---------+
| root | 0 | /bin/bash | root | 0 | /root |
| bin | 1 | /sbin/nologin | bin | 1 | /bin |
| daemon | 2 | /sbin/nologin | daemon | 2 | /sbin |
+--------+------+---------------+--------+------+---------+
3 rows in set (0.00 sec)
3 where嵌套查询
–把内层查询结果作为外层查询的查询条件
–输出stu表中年龄小于平均年龄的学生的名字和年龄
查看stu表
mysql> select * from stu;
+----------+--------+-----+
| name | gender | age |
+----------+--------+-----+
| tom | boy | 24 |
| kenji | boy | 22 |
| lily | girl | 25 |
| lucy | girl | 18 |
| bingbing | girl | 21 |
| harry | boy | 26 |
+----------+--------+-----+
6 rows in set (0.00 sec)
（1） 输出stu表中年龄小于平均年龄的学生的名字和年龄（相同数据库相同表）
mysql> select name,age from stu where age < (select avg(age) from stu);
+----------+-----+
| name | age |
+----------+-----+
| kenji | 22 |
| lucy | 18 |
| bingbing | 21 |
+----------+-----+
3 rows in set (0.00 sec) （2）不同数据库表查询
mysql> select name,homedir from db4.passwd1 where name in (select name from db3.stu);
+-------+----------------+
| name | homedir |
+-------+----------------+
| root | /root |
| mysql | /var/lib/mysql |
+-------+----------------+
2 rows in set (0.00 sec)
4 左连接，右连接，内连接查询
左连接格式
select 字段名列表 from 表a left join 表b on 条件表达式;(以表a为参照，显示相同的和不同的）
右连接格式
select 字段名列表 from 表a right join 表b on 条件表达式;（以表b为参照，显示相同的和不同的）
内连接格式
select 字段名列表 from 表a inner join 表b on 条件表达式;（相互为参照，显示相同的数据，不同的数据不显示）
（1）查看表记录
mysql> select * from w1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+mysql> select * from w2;
+------+------+
| id | age |
+------+------+
| 1 | 20 |
| 3 | 30 |
+------+------+
（2）左连接查询 （以左侧表为参照）
mysql> select * from w1 left join w2 on w1.id=w2.id;
+------+------+------+------+
| id | name | id | age |
+------+------+------+------+
| 1 | aaa | 1 | 20 |
| 2 | bbb | NULL | NULL |
+------+------+------+------+
（3） 右连接查询 （以右侧表为参照）
mysql> select * from w1 right join w2 on w1.id=w2.id;
+------+------+------+------+
| id | name | id | age |
+------+------+------+------+
| 1 | aaa | 1 | 20 |
| NULL | NULL | 3 | 30 |
+------+------+------+------+（4） 内连接查询（显示相同的数据）
mysql> select * from w1 inner join w2 on w1.id=w2.id;
+------+------+------+------+
| id | name | id | age |
+------+------+------+------+
| 1 | aaa | 1 | 20 |
+------+------+------+------+
1 row in set (0.00 sec)5 mysql图形化管理工具 ：部署LAMP+phpMyAdmin平台
（1）安装httpd、mysql、php-mysql及相关包
（2）启动httpd服务程序
（3） 解压phpMyAdmin包，部署到网站目录
[root@mysql50 ~]# yum -y install httpd php php-mysql
[root@mysql50 ~]# systemctl start httpd
[root@mysql50 ~]# systemctl enable httpd
[root@mysql50 ~]# tar -xf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/ww/html/
[root@mysql50 ~]# cd /var/www/html/
[root@mysql50 ~]# mv phpMyAdmin-2.11.11-all-languages phpmyadmin //改变目录名
[root@mysql50 ~]# chown -R apache:apache phpmyadmin/ //改变phpmyadmin目录权限
(4） 修改软件的配置文件定义管理的数据库服务器
切换到部署后的phpmyadmin程序目录，拷贝配置文件，并修改配置以正确指定MySQL服务器的地址
[root@mysql50 html]# cd phpmyadmin
[root@mysql50 phpmyadmin]# cp config.sample.inc.php config.inc.php //备份主配置文
[root@mysql50 phpmyadmin]# vim config.inc.php //编辑主配置文件
17 $cfg['blowfish_secret'] = 'plj123'; //给cookie做认证的值，可以随便填写
31 $cfg['Servers'][$i]['host'] = 'localhost'; //指定主机名，定义连接哪台服务器
（5）在客户端访问软件 管理数据库服务器
在客户端访问软件,打开浏览器输入http://192.168.4.6/phpmyadmin(数据库服务器地址) 访问软件,可以利用数据库root用户和密码登录
登入成功后，即可在授权范围内对MySQL数据库进行管理。
6 密码恢复及设置
（1）在知道数据库密码的前提下修改数据库密码
[root@mysql50 ~]# mysqladmin -uroot -p pasword "123456"
Enter password: //输入原始密码
（2)在不知道数据库密码的情况下修改数据库密码（前提时要有数据库所在服务器操作系统管理员root的权限）
[root@mysql50 ~]# vim /etc/myf
[mysqld]
#validate_password_policy=0
#validate_password_length=6
skip-grant-tables // 加入此字段跳过授权表
----重启mysql服务
[root@mysql50 ~]# systemctl restart mysqld
----无密码进入数据库
[root@mysql50 ~]# mysql
----查看mysql数据库中的相关密码和用户数据
mysql> show databases;
mysql> use mysql;
mysql> show tables;
mysql> desc user;
mysql> select user,host,authentication_string from user;
+-----------+-----------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
mysql> update user set authentication_string=password("654321") ### 重置密码-> where user="root" and host="localhost";
mysql> flush privileges;
----退出数据库 ，修改配置文件，重启Mysql服务
[root@mysql50 ~]# vim /etc/myf
[mysqld]
validate_password_policy=0 //只验证数字密码
validate_password_length=6 //密码长度为6
#skip-grant-tables // 注释掉
[root@mysql50 ~]# systemctl restart mysqld
修改密码完成在后续对数据库的操作中仍然会报错
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
在数据库内执行一下操作：
mysql> alter user root@localhost identified by "123456";
彻底完成
6 用户授权管理
（1） 设置权限：
//本机用户admin对db3下的所有表拥有所有权限
mysql> grant all on db3.* to admin@"localhost" identified by "123456"; //任何主机以root用户登录，对所有数据库和所有表具有所有权限，并且具有授权的权限mysql> grant all on *.* to root@'%' identified by "123456" with grant option; //主机192.168.4.254以webadmin用户登录后，仅仅对ddb4下的passwd1表有查询和插入的权限。
mysql> grant select,insert on db4.passwd1 to webadmin@"192.168.4.254" identified by '123456';
（2）查看权限
mysql> select user(); // 查看当前登录用户
mysql> show grants; //查看当前用户的权限
mysql> show grants for admin@"localhost" \G; //管理员查看其他用户权限
mysql> show grants for webadmin1@"192.168.4.% ";
mysql> show grants for webadmin@192.168.4.254 ;
（3）撤销用户的授权权限
mysql> show grants for root@"%";
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
mysql> revoke grant option on *.* from root@"%";
mysql> show grants for root@"%";
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
（4） 撤销用户的其他授权权限
mysql> revoke insert,update(uid,name) on db4.passwd1 from webadmin1@"192.168.4.%";
mysql> revoke all on *.* from root@"%";
（5） 删除授权的用户
mysql> drop user root@"%";
7 修改授权用户密码
授权用户连接后自行修改密码
mysql> select user();
+------------------------+
| user() |
+------------------------+
| webadmin1@192.168.4.51 |
+------------------------+
mysql> set password=password("654321");
Query OK, 0 rows affected, 1 warning (0.00 sec)数据库管理员为授权用户修改密码
mysql> set password for webadmin@"192.168.4.254"=password("654321");
更多推荐
MySQL数据库基础五
发布评论