测试MYSQL表关于内外连接
表一:雇员表(雇员ID,雇员名称,雇员入职时间,薪水,部门号)
create table empo(
empoid int(6) not null primary key auto_increment,
empname varchar(32) not null,
empdate datetime not null,
salary int(6) not null ,
deptno int(6) not null
)engine=innodb charset=utf8
insert into empo values(1,‘zhangsan‘,‘2012-2-20 10:00:00‘,6000,1);
insert into empo values(2,‘lisi‘,‘2013-2-20 10:00:00‘,6000,2);
insert into empo values(3,‘王五‘,‘2014-2-20 10:00:00‘,6000,1);
insert into empo values(4,‘zhaoliu‘,‘2015-2-20 10:00:00‘,6000,3);
insert into empo values(5,‘zhaoliu‘,‘2015-2-20 10:00:00‘,6000,6);
insert into empo values(6,‘zhaoliu‘,‘2015-2-20 10:00:00‘,6000,8);
表二(部门号,部门名称)
create table dept(
deptid int(6) primary key ,
deptname varchar(32) not null
)engine=innodb charset=utf8
insert into dept values(1,‘xinzhengbu‘);
insert into dept values(2,‘jixubu‘);
insert into dept values(3,‘yunying‘);
insert into dept values(3,‘shejibu‘);
题目:
例1:内连接(内连接实际上就是利用 where 子句对两张表形成的笛卡尔积进行筛选,)
mysql> select * from empo,dept;
+--------+----------+---------------------+--------+--------+--------+------------+
| empoid | empname | empdate | salary | deptno | deptid | deptname |
+--------+----------+---------------------+--------+--------+--------+------------+
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 1 | xinzhengbu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 1 | xinzhengbu |
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 2 | jixubu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 2 | jixubu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 2 | jixubu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 2 | jixubu |
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 3 | yunying |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 3 | yunying |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 3 | yunying |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 3 | yunying |
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 4 | shejibu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 4 | shejibu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 4 | shejibu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 4 | shejibu |
+--------+----------+---------------------+--------+--------+--------+------------+
16 rows in set (0.00 sec)
mysql> select * from empo inner join dept;
+--------+----------+---------------------+--------+--------+--------+------------+
| empoid | empname | empdate | salary | deptno | deptid | deptname |
+--------+----------+---------------------+--------+--------+--------+------------+
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 1 | xinzhengbu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 1 | xinzhengbu |
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 2 | jixubu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 2 | jixubu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 2 | jixubu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 2 | jixubu |
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 3 | yunying |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 3 | yunying |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 3 | yunying |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 3 | yunying |
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 4 | shejibu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 4 | shejibu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 4 | shejibu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 4 | shejibu |
+--------+----------+---------------------+--------+--------+--------+------------+
16 rows in set (0.00 sec)
上面两个SQL等价的。
下面是加where条件的等价SQL(select * from empo,dept where deptno=deptid;)
mysql> select * from empo inner join dept on deptno=deptid;
+--------+----------+---------------------+--------+--------+--------+------------+
| empoid | empname | empdate | salary | deptno | deptid | deptname |
+--------+----------+---------------------+--------+--------+--------+------------+
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 2 | jixubu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 3 | yunying |
+--------+----------+---------------------+--------+--------+--------+------------+
4 rows in set (0.00 sec)
例2:左连接
mysql> select * from empo left join dept on deptno=deptid;
+--------+----------+---------------------+--------+--------+--------+------------+
| empoid | empname | empdate | salary | deptno | deptid | deptname |
+--------+----------+---------------------+--------+--------+--------+------------+
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 2 | jixubu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 3 | yunying |
+--------+----------+---------------------+--------+--------+--------+------------+
4 rows in set (0.00 sec)
例3:右连接
mysql> select * from empo right join dept on deptno=deptid;
+--------+----------+---------------------+--------+--------+--------+------------+
| empoid | empname | empdate | salary | deptno | deptid | deptname |
+--------+----------+---------------------+--------+--------+--------+------------+
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 | 1 | xinzhengbu |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 | 2 | jixubu |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 | 3 | yunying |
| NULL | NULL | NULL | NULL | NULL | 4 | shejibu |
+--------+----------+---------------------+--------+--------+--------+------------+
5 rows in set (0.00 sec)
例4:将子查询替换成连接
表1
mysql> select * from empo;
+--------+----------+---------------------+--------+--------+
| empoid | empname | empdate | salary | deptno |
+--------+----------+---------------------+--------+--------+
| 1 | zhangsan | 2012-02-20 10:00:00 | 6000 | 1 |
| 2 | lisi | 2013-02-20 10:00:00 | 6000 | 2 |
| 3 | 王五 | 2014-02-20 10:00:00 | 6000 | 1 |
| 4 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 3 |
| 5 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 6 |
| 6 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 8 |
+--------+----------+---------------------+--------+--------+
6 rows in set (0.00 sec)
表2
mysql> select * from dept;
+--------+------------+
| deptid | deptname |
+--------+------------+
| 1 | xinzhengbu |
| 2 | jixubu |
| 3 | yunying |
| 4 | shejibu |
+--------+------------+
4 rows in set (0.00 sec)
题目1、找出雇员不在部门表中的用户(雇员ID号为5,6的不在部门列表中)
select * from empo where deptno not in (select deptid from dept)
子查询写法
mysql> select * from empo where deptno not in (select deptid from dept);
+--------+---------+---------------------+--------+--------+
| empoid | empname | empdate | salary | deptno |
+--------+---------+---------------------+--------+--------+
| 5 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 6 |
| 6 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 8 |
+--------+---------+---------------------+--------+--------+
内连接写法(和上面写法同等)
mysql> select * from empo left join dept on empo.deptno=dept.deptid where deptid is NULL;
+--------+---------+---------------------+--------+--------+--------+----------+
| empoid | empname | empdate | salary | deptno | deptid | deptname |
+--------+---------+---------------------+--------+--------+--------+----------+
| 5 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 6 | NULL | NULL |
| 6 | zhaoliu | 2015-02-20 10:00:00 | 6000 | 8 | NULL | NULL |
+--------+---------+---------------------+--------+--------+--------+----------+
说明:mysql中join中使用where和on的区别
1.(left|right)join... on.. and 的写法:是先处理on后面的条件,在处理表之间的连接
2.(left|rgint)join... on.. where的写法: 是先表left连接,在处理where条件
原文:http://caozm.blog.51cto.com/1118764/1627583