多表查询

多表查询

设计表结构时，必须要有一个唯一字段，可以标识一个人

格式1：
select 字段名列表 from 表a，表b;

格式2：
select 字段名列表 from 表a，表b
where 条件;

工作中，尽量避免多表查询，因为查询越多，处理速度就越慢。

mysql> use db4;
mysql> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| t1 |
| user |
| user2 |
+---------------+

mysql> create table t2 select name,uid,shell from db3.user
-> limit 3;

mysql> select * from db4.t2;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
+--------+------+---------------+

mysql> create table t3 select name,uid,homedir from db3.user
-> limit 5;

mysql> select * from t3;
+--------+------+----------------+
| name | uid | homedir |
+--------+------+----------------+
| root | 1 | /root |
| bin | 2 | /bin |
| daemon | 3 | /sbin |
| adm | 4 | /var/adm |
| lp | 5 | /var/spool/lpd |
+--------+------+----------------+

mysql> select * from t2 ; select * from t3; 用分号；分隔，可以同时显示2张表！！
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 1 | /bin/bash |
| bin | 2 | /sbin/nologin |
| daemon | 3 | /sbin/nologin |
+--------+------+---------------+

+--------+------+----------------+
| name | uid | homedir |
+--------+------+----------------+
| root | 1 | /root |
| bin | 2 | /bin |
| daemon | 3 | /sbin |
| adm | 4 | /var/adm |
| lp | 5 | /var/spool/lpd |
+--------+------+----------------+

mysql> select * from t2,t3; 不加条件时，就是笛卡尔集！显示2个表的记录总数相乘的结果！
+--------+------+---------------+--------+------+----------------+
| name | uid | shell | name | uid | homedir |
+--------+------+---------------+--------+------+----------------+
| root | 1 | /bin/bash | root | 1 | /root |
| bin | 2 | /sbin/nologin | root | 1 | /root |
| daemon | 3 | /sbin/nologin | root | 1 | /root |
| root | 1 | /bin/bash | bin | 2 | /bin |
| bin | 2 | /sbin/nologin | bin | 2 | /bin |
| daemon | 3 | /sbin/nologin | bin | 2 | /bin |
| root | 1 | /bin/bash | daemon | 3 | /sbin |
| bin | 2 | /sbin/nologin | daemon | 3 | /sbin |
| daemon | 3 | /sbin/nologin | daemon | 3 | /sbin |
| root | 1 | /bin/bash | adm | 4 | /var/adm |
| bin | 2 | /sbin/nologin | adm | 4 | /var/adm |
| daemon | 3 | /sbin/nologin | adm | 4 | /var/adm |
| root | 1 | /bin/bash | lp | 5 | /var/spool/lpd |
| bin | 2 | /sbin/nologin | lp | 5 | /var/spool/lpd |
| daemon | 3 | /sbin/nologin | lp | 5 | /var/spool/lpd |
+--------+------+---------------+--------+------+----------------+
15 rows in set (0.00 sec)

mysql> select t2.name,t2.uid,t3.*
-> from t2,t3;
+--------+------+--------+------+----------------+
| name | uid | name | uid | homedir |
+--------+------+--------+------+----------------+
| root | 1 | root | 1 | /root |
| bin | 2 | root | 1 | /root |
| daemon | 3 | root | 1 | /root |
| root | 1 | bin | 2 | /bin |
| bin | 2 | bin | 2 | /bin |
| daemon | 3 | bin | 2 | /bin |
| root | 1 | daemon | 3 | /sbin |
| bin | 2 | daemon | 3 | /sbin |
| daemon | 3 | daemon | 3 | /sbin |
| root | 1 | adm | 4 | /var/adm |
| bin | 2 | adm | 4 | /var/adm |
| daemon | 3 | adm | 4 | /var/adm |
| root | 1 | lp | 5 | /var/spool/lpd |
| bin | 2 | lp | 5 | /var/spool/lpd |
| daemon | 3 | lp | 5 | /var/spool/lpd |
+--------+------+--------+------+----------------+
15 rows in set (0.00 sec)

mysql> select t2.name,t2.uid,t3.* from t2,t3
-> where t2.uid=t3.uid; 只显示条件匹配的,表t2和表t3的uid一样的才显示出来
+--------+------+--------+------+---------+
| name | uid | name | uid | homedir |
+--------+------+--------+------+---------+
| root | 1 | root | 1 | /root |
| bin | 2 | bin | 2 | /bin |
| daemon | 3 | daemon | 3 | /sbin |
+--------+------+--------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from t2,t3
-> where t2.uid=t3.uid;
+--------+------+---------------+--------+------+---------+
| name | uid | shell | name | uid | homedir |
+--------+------+---------------+--------+------+---------+
| root | 1 | /bin/bash | root | 1 | /root |
| bin | 2 | /sbin/nologin | bin | 2 | /bin |
| daemon | 3 | /sbin/nologin | daemon | 3 | /sbin |
+--------+------+---------------+--------+------+---------+