MySQL多表联合查询

多表联合查询是建立在别名之上的 这是个人感觉      

多表关联查询 
            普通关联查询 
mysql> select u.id,u.username,u.password,p.age,p.sex from bbs_user as u,bbs_profile as p where u.id=p.uid;

mysql> use zhenyu;
Database changed
mysql> create table bbs_profile(
    -> uid int unsigned not null default 0,
    -> age tinyint,
    -> sex tinyint
    -> )engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from bbs_profile;
Empty set (0.00 sec)

mysql> create table `bbs_user`(
    -> id int unsigned not null auto_increment primary key,
    -> `username` varchar(50) not null,
    -> `password` varchar(32) not null
    -> )engine=myisam default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bbs_user;
Empty set (0.00 sec)

mysql> insert into bbs_user (username,password) values('zhang1',123);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbs_user (username,password) values('zhang2',234);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbs_user (username,password) values('zhang3',345);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbs_profile (uid,age,sex) values(1,20,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbs_profile (uid,age,sex) values(2,21,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bbs_profile (uid,age,sex) values(3,22,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bbs_user ;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhang1   | 123      |
|  2 | zhang2   | 234      |
|  3 | zhang3   | 345      |
+----+----------+----------+
3 rows in set (0.00 sec)

mysql> select * from bbs_profile;
+-----+------+------+
| uid | age  | sex  |
+-----+------+------+
|   1 |   20 |    1 |
|   2 |   21 |    2 |
|   3 |   22 |    1 |
+-----+------+------+
3 rows in set (0.00 sec)
 
mysql> select u.id,u.username,u.password,p.uid,p.age,p.sex from bbs_user as u,bb
s_profile as p where u.id=p.uid;
+----+----------+----------+-----+------+------+
| id | username | password | uid | age  | sex  |
+----+----------+----------+-----+------+------+
|  1 | zhang1   | 123      |   1 |   20 |    1 |
|  2 | zhang2   | 234      |   2 |   21 |    2 |
|  3 | zhang3   | 345      |   3 |   22 |    1 |
+----+----------+----------+-----+------+------+
3 rows in set (0.00 sec)

//是将U表当中的主键id与p表当中的外键（逻辑上，并没有物理外键）uid进行关联 直接作为相等的条件即可 
//(效率较高)
			连接关联查询 
左连接

mysql> select u.id,u.username,u.password,p.age,p.sex from bbs_user as u left joi
n bbs_profile as p on u.id=p.uid;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | zhang1   | 123      |   20 |    1 |
|  2 | zhang2   | 234      |   21 |    2 |
|  3 | zhang3   | 345      |   22 |    1 |
|  4 | zhang4   | 567      | NULL | NULL |
+----+----------+----------+------+------+
4 rows in set (0.00 sec)
//以左表（bbs_user）为主 先输出左表内容 然后按照on后的条件去右表（bbs_profile）当中查询与右表对应多得记录 如果没有显示为null  

右连接

mysql> select u.id,u.username,u.password,p.age,p.sex from bbs_user as u right jo
in bbs_profile as p on u.id=p.uid;
+------+----------+----------+------+------+
| id   | username | password | age  | sex  |
+------+----------+----------+------+------+
|    1 | zhang1   | 123      |   20 |    1 |
|    2 | zhang2   | 234      |   21 |    2 |
|    3 | zhang3   | 345      |   22 |    1 |
+------+----------+----------+------+------+
3 rows in set (0.00 sec) 

//以右表（bbs_profile）为主 先输出右表内容 然后按照on后的条件去左表（bbs_user）当中查询与右表对应多得记录 如果没有显示为null  

mysql> select u.id,u.username,u.password,p.age,p.sex from bbs_profile as p right
 join bbs_user as u on u.id=p.uid;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | zhang1   | 123      |   20 |    1 |
|  2 | zhang2   | 234      |   21 |    2 |
|  3 | zhang3   | 345      |   22 |    1 |
|  4 | zhang4   | 567      | NULL | NULL |
+----+----------+----------+------+------+
4 rows in set (0.00 sec)
//如果右连接想变成4条记录 把bbs_user  as u 位置和 bbs_profile as p 位置调换一下 方可行

              //通过左右连接可以看到数据表以谁为主进行连接 
内连接 

mysql> select u.id,u.username,u.password,p.age,p.sex from bbs_user as u inner jo
in bbs_profile as p on u.id=p.uid;         //inner join就是以内连接 
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | zhang1   | 123      |   20 |    1 |
|  2 | zhang2   | 234      |   21 |    2 |
|  3 | zhang3   | 345      |   22 |    1 |
+----+----------+----------+------+------+
3 rows in set (0.00 sec)
//不以任何表为主 直接查询on后的关联条件 类似于普通关联查询   其实这里的on可以看成普通查询里的where条件 

			嵌套关联查询（不推荐使用 效率极低 不到迫不得己 经量不要使用）

mysql> select * from bbs_user where id in(select uid from bbs_profile);
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhang1   | 123      |
|  2 | zhang2   | 234      |
|  3 | zhang3   | 345      |
+----+----------+----------+
3 rows in set (0.00 sec)
//1 将bbs_profile表中查询的 结果 作为查询bbs_user表的条件  
//2 一条语句的查询结果作为另外一条查询语句的条件