一多表查询
设计表结构的时候就应该考虑到表的查询。信息查询跟表结构的设计有很大的关系
select .... from user where 条件;
1.1复制表 (作用:备份表,快速建表)
create table 表名 sql 查询结果;
mysql> create database db4
mysql> use db4;
mysql> create table user2 select * from db3.user;
mysql> desc user2;
mysql> select * from user2;
创建的表有什么内容,完全取决与查询的结果
复制部分内容
mysql> create table user3 select name,uid,shell from db3.user order by uid desc limit 5;
Query OK, 5 rows affected (0.24 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from user3;
+-----------+-------+---------------+
| name | uid | shell |
+-----------+-------+---------------+
| nfsnobody | 65534 | /sbin/nologin |
| bob | 2000 | /bin/ |
| bob | 2000 | /bin/ |
| bob | 2000 | hon |
| lucy | 1998 | /sbin/nologin |
+-----------+-------+---------------+
5 rows in set (0.00 sec)
复制不存在的内容,就相当与复制表结构
——但是键值不复制,需要手动添加
mysql> create table user4 select * from db3.user where shell="abc";
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id | int(2) | NO | | 0 | |
| name | char(80) | YES | | NULL | |
| age | tinyint(2) unsigned | YES | | 18 | |
| password | char(1) | YES | | NULL | |
| uid | int(2) | YES | | NULL | |
| gid | int(2) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
mysql> desc db3.user;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | int(2) | NO |
PRI | NULL | auto_increment |
| name | char(80) | YES |
MUL | NULL | |
| age | tinyint(2) unsigned | YES | | 18 | |
| password | char(1) | YES | | NULL | |
| uid | int(2) | YES | | NULL | |
| gid | int(2) | YES | | NULL | |
| comment | varchar(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> select * from user4;
Empty set (0.00 sec)
1.2
多表查询:
多表查询
1.1使用SELECT子句进行多表查询 SELECT 字段名 FROM 表1,表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件 SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id 注:在上面的的代码中,以两张表的id字段信息相同作为条件建立两表关联,但在实际开发中不应该这样使用,最好用主外键约束来实现
select 字段列表 from 表名列表 where 条件;
mysql> create table t1 select name,uid,shell from db3.user limit 3;
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
+--------+------+---------------+
3 rows in set (0.00 sec)
mysql> create table t2 select name,password,uid,homedir from db3.user limit 5;
Query OK, 5 rows affected (0.24 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+--------+----------+------+----------------+
| name | password | uid | homedir |
+--------+----------+------+----------------+
| root | x | 1 | /root |
| bin | NULL | 2 | /bin |
| daemon | A | 3 | /sbin |
| adm | A | 4 | /var/adm |
| lp | A | 5 | /var/spool/lpd |
+--------+----------+------+----------------+
5 rows in set (0.00 sec)
笛卡尔集 3*5=15
mysql> select * from t1,t2;
+--------+------+---------------+--------+----------+------+----------------+
| name | uid | shell | name | password | uid | homedir |
+--------+------+---------------+--------+----------+------+----------------+
| root | 1 | /bin/bash | root | x | 1 | /root |
| bin | 2 | /sbin/nologin | root | x | 1 | /root |
| daemon | 3 | /sbin/nologin | root | x | 1 | /root |
| root | 1 | /bin/bash | bin | NULL | 2 | /bin |
| bin | 2 | /sbin/nologin | bin | NULL | 2 | /bin |
| daemon | 3 | /sbin/nologin | bin | NULL | 2 | /bin |
| root | 1 | /bin/bash | daemon | A | 3 | /sbin |
| bin | 2 | /sbin/nologin | daemon | A | 3 | /sbin |
| daemon | 3 | /sbin/nologin | daemon | A | 3 | /sbin |
| root | 1 | /bin/bash | adm | A | 4 | /var/adm |
| bin | 2 | /sbin/nologin | adm | A | 4 | /var/adm |
| daemon | 3 | /sbin/nologin | adm | A | 4 | /var/adm |
| root | 1 | /bin/bash | lp | A | 5 | /var/spool/lpd |
| bin | 2 | /sbin/nologin | lp | A | 5 | /var/spool/lpd |
| daemon | 3 | /sbin/nologin | lp | A | 5 | /var/spool/lpd |
+--------+------+---------------+--------+----------+------+----------------+
15 rows in set (0.00 sec)
mysql> select t1.name,t2.* from t1,t2;——使用表中的别名进行查询
+--------+--------+----------+------+----------------+
| name | name | password | uid | homedir |
+--------+--------+----------+------+----------------+
| root | root | x | 1 | /root |
| bin | root | x | 1 | /root |
| daemon | root | x | 1 | /root |
| root | bin | NULL | 2 | /bin |
| bin | bin | NULL | 2 | /bin |
| daemon | bin | NULL | 2 | /bin |
| root | daemon | A | 3 | /sbin |
| bin | daemon | A | 3 | /sbin |
| daemon | daemon | A | 3 | /sbin |
| root | adm | A | 4 | /var/adm |
| bin | adm | A | 4 | /var/adm |
| daemon | adm | A | 4 | /var/adm |
| root | lp | A | 5 | /var/spool/lpd |
| bin | lp | A | 5 | /var/spool/lpd |
| daemon | lp | A | 5 | /var/spool/lpd |
+--------+--------+----------+------+----------------+
15 rows in set (0.00 sec)
mysql> select t1.*, t2.password,t2.homedir from t1,t2;——使用的表里的别名进行查询
+--------+------+---------------+----------+----------------+
| name | uid | shell | password | homedir |
+--------+------+---------------+----------+----------------+
| root | 1 | /bin/bash | x | /root |
| bin | 2 | /sbin/nologin | x | /root |
| daemon | 3 | /sbin/nologin | x | /root |
| root | 1 | /bin/bash | NULL | /bin |
| bin | 2 | /sbin/nologin | NULL | /bin |
| daemon | 3 | /sbin/nologin | NULL | /bin |
| root | 1 | /bin/bash | A | /sbin |
| bin | 2 | /sbin/nologin | A | /sbin |
| daemon | 3 | /sbin/nologin | A | /sbin |
| root | 1 | /bin/bash | A | /var/adm |
| bin | 2 | /sbin/nologin | A | /var/adm |
| daemon | 3 | /sbin/nologin | A | /var/adm |
| root | 1 | /bin/bash | A | /var/spool/lpd |
| bin | 2 | /sbin/nologin | A | /var/spool/lpd |
| daemon | 3 | /sbin/nologin | A | /var/spool/lpd |
+--------+------+---------------+----------+----------------+
15 rows in set (0.00 sec)
mysql> select t1.name,t2.* from t1,t2 where t1.uid=t2.uid;——显示的并不是笛卡尔集,显示两个表中uid相同的
+--------+--------+----------+------+---------+
| name | name | password | uid | homedir |
+--------+--------+----------+------+---------+
| root | root | x | 1 | /root |
| bin | bin | NULL | 2 | /bin |
| daemon | daemon | A | 3 | /sbin |
+--------+--------+----------+------+---------+
3 rows in set (0.00 sec)
mysql> select t1.*,t2.uid,t2.password,t2.homedir from t1,t2 where t1.uid = t2.uid; ——检测上一条的输出是否正确
+--------+------+---------------+------+----------+---------+
| name | uid | shell | uid | password | homedir |
+--------+------+---------------+------+----------+---------+
| root | 1 | /bin/bash | 1 | x | /root |
| bin | 2 | /sbin/nologin | 2 | NULL | /bin |
| daemon | 3 | /sbin/nologin | 3 | A | /sbin |
+--------+------+---------------+------+----------+---------+
3 rows in set (0.00 sec)
嵌套查询
——把内层的查询结果作为外层查询条件
select 字段名列表 from 表名 where 条件 (select 字段名列表 from 表名 where 条件 );
mysql> select name from db3.user where uid=max(uid);——用这种方法出现语法错误
ERROR 1111 (HY000): Invalid use of
group function
mysql> select name,uid from db3.user order by uid desc limit 1;
+-----------+-------+
| name | uid |
+-----------+-------+
| nfsnobody | 65534 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> select name,uid from db3.user where uid<(select avg(uid) from db3.user);——用嵌套查询
mysql> select name from db3.user where name in (select name from db4.t1);
+--------+
| name |
+--------+
| root |
| bin |
| daemon |
+--------+
3 rows in set (0.00 sec)
注意事项:
mysql> select name from db3.user where name =(select name from db4.t1); ——这样就语法错误
能不嵌套 就不用, 会降低mysql的处理速度
左连接查询:以左边的表为主的显示查询结果
select 字段名列表 from 表名A left join 表名B on 条件;
右连接查询:以右边的表为主的显示查询结果
select 字段名列表 from 表名A right join 表名B on 条件;
1.创建2个表
mysql> create table db4.t4 select name,uid,shell from db3.user limit 7;
Query OK, 7 rows affected (0.18 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> create table db4.t3 select name,uid,shell from db3.user limit 5;
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
| adm | 4 | /sbin/nologin |
| lp | 5 | /sbin/nologin |
+--------+------+---------------+
5 rows in set (0.00 sec)
mysql> select * from t4;
+----------+------+----------------+
| name | uid | shell |
+----------+------+----------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
| adm | 4 | /sbin/nologin |
| lp | 5 | /sbin/nologin |
| sync | 6 | /bin/sync |
| shutdown | 7 | /sbin/shutdown |
+----------+------+----------------+
7 rows in set (0.00 sec)
1.2 左链接查询
mysql> select * from t3 left join t4 on t3.uid =t4.uid;
+--------+------+---------------+--------+------+---------------+
| name | uid | shell | name | uid | shell |
+--------+------+---------------+--------+------+---------------+
| root | 1 | /bin/bash | root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin | bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin | daemon | 3 | /sbin/nologin |
| adm | 4 | /sbin/nologin | adm | 4 | /sbin/nologin |
| lp | 5 | /sbin/nologin | lp | 5 | /sbin/nologin |
+--------+------+---------------+--------+------+---------------+
5 rows in set (0.00 sec)
1.3右连接查询
mysql> select * from t3 right join t4 on t3.uid =t4.uid;
+--------+------+---------------+----------+------+----------------+
| name | uid | shell | name | uid | shell |
+--------+------+---------------+----------+------+----------------+
| root | 1 | /bin/bash | root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin | bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin | daemon | 3 | /sbin/nologin |
| adm | 4 | /sbin/nologin | adm | 4 | /sbin/nologin |
| lp | 5 | /sbin/nologin | lp | 5 | /sbin/nologin |
| NULL | NULL | NULL | sync | 6 | /bin/sync |
| NULL | NULL | NULL | shutdown | 7 | /sbin/shutdown |
+--------+------+---------------+----------+------+----------------+
7 rows in set (0.00 sec)
1.4 按字段名进行右连接查询:
mysql> select t3.name,t4.name from t3 right join t4 on t3.uid =t4.uid;
+--------+----------+
| name | name |
+--------+----------+
| root | root |
| bin | bin |
| daemon | daemon |
| adm | adm |
| lp | lp |
| NULL | sync |
| NULL | shutdown |
+--------+----------+
7 rows in set (0.00 sec)
二MYSQL管理工具 ——图形管理工具
图形工具比较多——有部分可以跨平台
mysql的命令的局限性 ——忘记命令就不可以操作
mysql命令的好处:可以跨平台 传输速度快
装图形软件,主要是给程序员用 管理员也可以用
phpMyAdmin 跨平台 浏览器输入
要跑早LNMP/LAMP 平台上
rpm -qa httpd
[root@host50 ~]# yum -y install httpd
[root@host50 ~]# systemctl start httpd
[root@host50 ~]# systemctl enable httpd
[root@host50 ~]# rpm -q php
未安装软件包 php
[root@host50 ~]# rpm -q php-mysql
未安装软件包 php-mysql
是用来解释php代码,因为phpMyAdmin用php写 ,php-mysql 用来连接数据库
[root@host50 ~]# yum -y install php php-mysql
步骤2:
1.1解包
[root@host50 ~]# tar -zxvf phpMyAdmin-2.11.11-all-languages.tar.gz
[root@host50 ~]#ls
phpMyAdmin-2.11.11-all-languages
[root@host50 ~]# mv phpMyAdmin-2.11.11-all-languages /var/www/html/phpadmin
[root@host50 ~]# cd /var/www/html/phpadmin
[root@host50 phpadmin]# ls
index.php
步骤3:创建配置,指定管理数据库服务器:
[root@host50 phpadmin]# cp config.sample.inc.php config.inc.php
[root@host50 phpadmin]# vim config.inc.php
*/和
31 $cfg['Servers'][$i]['host'] = 'localhost';
17 $cfg['blowfish_secret'] = 'pljabc';
:wq
步骤4:
客户端访问
真机浏览器访问 :http://192.168.4.50/phpadmin
用户:root 密码123456
mysql -u root -p123456
http://192.168.4.50/phpadmin
——这两种连接数据库都是一样的
指定管理数据库服务器的ip地址
设置管理员root密码
定期会修改root的密码。为安全的考虑
修改root密码。是在命令行相操作(操作系统管理员才有修改权限)
[root@host50 ~]# mysqladmin -hlocalhost -u root -p password "654321"
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.
mysqladmin: unable to change password; error: 'Your password does not satisfy the current policy requirements'
[root@host50 ~]# mysqladmin -hlocalhost -u root -p password "123qq...A"
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@host50 ~]# mysql -u root -p123qq...A
恢复管理员root密码——忘记登陆密码
1.先把密码停调systemctl stop mysql
vim /etc/my.conf
#skip_grant_tables
:wq
[root@host50 ~]# systemctl start mysqld
[root@host50 ~]# mysql
31 rows in set (0.00 sec)
mysql> select host,user,authentication_string from mysql.user;
+-------------+-----------+-------------------------------------------+
| host | user | authentication_string |
+-------------+-----------+-------------------------------------------+
| localhost | root | *BD560FD510D20F65E3B38BFDE0AA0D8CE74C3897 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| 192.168.4.% | admin | *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 |
+-------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> update mysql.user set authentication_string=password ("123456") where user="root" and host ="localhost";
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@host50 ~]# systemctl stop mysqd
Failed to stop mysqd.service: Unit mysqd.service not loaded.
[root@host50 ~]# systemctl stop mysqld
[root@host50 ~]# systemctl start mysqld
[root@host50 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@host50 ~]# mysql -u root -p
Enter password: