日志
mysql多表联查练习
||
内连接
mysql> select * from emp inner join dept on emp.emp_id=dept.emp_id;
+--------+------+--------+------+
| emp_id | name | emp_id | name |
+--------+------+--------+------+
| 1 | aa | 1 | hh |
| 2 | bb | 2 | dd |
+--------+------+--------+------+
2 rows in set (0.00 sec)
左连接：
mysql> select * from emp left join dept on emp.emp_id=dept.empt_id;
ERROR 1054 (42S22): Unknown column 'dept.empt_id' in 'on clause'
mysql> select * from emp left join dept on emp.emp_id=dept.emp_id;
+--------+------+--------+------+
| emp_id | name | emp_id | name |
+--------+------+--------+------+
| 1 | aa | 1 | hh |
| 2 | bb | 2 | dd |
| 3 | cc | NULL | NULL |
+--------+------+--------+------+
3 rows in set (0.00 sec)
右连接
mysql> select * from emp right join dept on emp.emp_id=dept.emp_id;
+--------+------+--------+------+
| emp_id | name | emp_id | name |
+--------+------+--------+------+
| 1 | aa | 1 | hh |
| 2 | bb | 2 | dd |
| NULL | NULL | 5 | ff |
+--------+------+--------+------+
3 rows in set (0.00 sec)
全连接：
mysql> select * from emp right join dept on emp.emp_id=dept.emp_id union
-> select * from emp left join dept on emp.emp_id=dept.emp_id;
+--------+------+--------+------+
| emp_id | name | emp_id | name |
+--------+------+--------+------+
| 1 | aa | 1 | hh |
| 2 | bb | 2 | dd |
| NULL | NULL | 5 | ff |
| 3 | cc | NULL | NULL |
+--------+------+--------+------+
4 rows in set (0.00 sec)
三表联查：
1.利用内连接---两边都匹配的
mysql> select * from emp inner join dept on emp.emp_id=dept.emp_id inner join
-> model on model.mo_id=emp.emp_id;
+--------+------+--------+------+-------+---------+---------+
| emp_id | name | emp_id | name | mo_id | mo_name | mo_file |
+--------+------+--------+------+-------+---------+---------+
| 1 | aa | 1 | hh | 1 | cc | 0 |
| 2 | bb | 2 | dd | 2 | hh | 1 |
+--------+------+--------+------+-------+---------+---------+
2 rows in set (0.00 sec)
2.直接对等---三边都匹配的。
mysql> select * from emp,dept,model where emp.emp_id=dept.emp_id and emp.emp_id
=model.mo_id;
+--------+------+--------+------+-------+---------+---------+
| emp_id | name | emp_id | name | mo_id | mo_name | mo_file |
+--------+------+--------+------+-------+---------+---------+
| 1 | aa | 1 | hh | 1 | cc | 0 |
| 2 | bb | 2 | dd | 2 | hh | 1 |
+--------+------+--------+------+-------+---------+---------+
2 rows in set (0.00 sec)
====================================================================================
mysql> select * from emp,dept,model where emp.emp_id=dept.emp_id=model.mo_id;
+--------+------+--------+------+-------+---------+---------+
| emp_id | name | emp_id | name | mo_id | mo_name | mo_file |
+--------+------+--------+------+-------+---------+---------+
| 1 | aa | 1 | hh | 1 | cc | 0 |
| 2 | bb | 2 | dd | 1 | cc | 0 |
+--------+------+--------+------+-------+---------+---------+
2 rows in set (0.00 sec)
当emp.emp_id=dept_id确定了结果，导致全为1；
3.嵌套查询，先两表后三表
mysql> select * from model,(select emp.emp_id temp_id from emp inner join dept
on emp.emp_id=dept.emp_id) temp where model.mo_id=temp_id;
+-------+---------+---------+---------+
| mo_id | mo_name | mo_file | temp_id |
+-------+---------+---------+---------+
| 1 | cc | 0 | 1 |
| 2 | hh | 1 | 2 |
+-------+---------+---------+---------+
2 rows in set (0.00 sec)
注意相同字段的问题。
mysql> select * from (select * from emp inner join dept on emp.emp_id=dept.emp_i
d) temp;
ERROR 1060 (42S21): Duplicate column name 'emp_id'
mysql> select * from (select emp.emp_id temp_id,dept.emp_id tept_name from emp i
nner join dept on emp.emp_id=dept.emp_id) temp;
+---------+-----------+
| temp_id | tept_name |
+---------+-----------+
| 1 | 1 |
| 2 | 2 |
+---------+-----------+
2 rows in set (0.00 sec)
//为什么会报重名错误，把查询结果当作了 一张表temp.
mysql> create table temp select emp.emp_id temp_id,dept.name temp_name from emp
,dept,model where emp.emp_id=dept.emp_id and emp.emp_id =model.mo_id;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from temp;
+---------+-----------+
| temp_id | temp_name |
+---------+-----------+
| 1 | hh |
| 2 | dd |
+---------+-----------+
2 rows in set (0.00 sec)
/2 
