测试MYSQL表关于内外连接
表一：雇员表(雇员ID,雇员名称，雇员入职时间，薪水，部门号)
createtableempo(
empoidint(6)notnullprimarykeyauto_increment,
empnamevarchar(32)notnull,
empdatedatetimenotnull,
salaryint(6)notnull,
deptnoint(6)notnull
)engine=innodbcharset=utf8
insertintoempovalues(1,'zhangsan','-2-:00:00',6000,1);
insertintoempovalues(2,'lisi','-2-:00:00',6000,2);
insertintoempovalues(3,'王五','-2-:00:00',6000,1);
insertintoempovalues(4,'zhaoliu','-2-:00:00',6000,3);
insertintoempovalues(5,'zhaoliu','-2-:00:00',6000,6);
insertintoempovalues(6,'zhaoliu','-2-:00:00',6000,8);
表二(部门号，部门名称)
createtabledept(
deptidint(6)primarykey,
deptnamevarchar(32)notnull
)engine=innodbcharset=utf8
insertintodeptvalues(1,'xinzhengbu');
insertintodeptvalues(2,'jixubu');
insertintodeptvalues(3,'yunying');
insertintodeptvalues(3,'shejibu');
题目：
例1：内连接(内连接实际上就是利用where子句对两张表形成的笛卡尔积进行筛选,)
mysql>select*fromempo,dept;
+--------+----------+---------------------+--------+--------+--------+------------+
|empoid|empname|empdate|salary|deptno|deptid|deptname|
+--------+----------+---------------------+--------+--------+--------+------------+
|1|zhangsan|-02-:00:00|6000|1|1|xinzhengbu|
|2|lisi|-02-:00:00|6000|2|1|xinzhengbu|
|3|王五|-02-:00:00|6000|1|1|xinzhengbu|
|4|zhaoliu|-02-:00:00|6000|3|1|xinzhengbu|
|1|zhangsan|-02-:00:00|6000|1|2|jixubu|
|2|lisi|-02-:00:00|6000|2|2|jixubu|
|3|王五|-02-:00:00|6000|1|2|jixubu|
|4|zhaoliu|-02-:00:00|6000|3|2|jixubu|
|1|zhangsan|-02-:00:00|6000|1|3|yunying|
|2|lisi|-02-:00:00|6000|2|3|yunying|
|3|王五|-02-:00:00|6000|1|3|yunying|
|4|zhaoliu|-02-:00:00|6000|3|3|yunying|
|1|zhangsan|-02-:00:00|6000|1|4|shejibu|
|2|lisi|-02-:00:00|6000|2|4|shejibu|
|3|王五|-02-:00:00|6000|1|4|shejibu|
|4|zhaoliu|-02-:00:00|6000|3|4|shejibu|
+--------+----------+---------------------+--------+--------+--------+------------+
16rowsinset(0.00sec)
mysql>select*fromempoinnerjoindept;
+--------+----------+---------------------+--------+--------+--------+------------+
|empoid|empname|empdate|salary|deptno|deptid|deptname|
+--------+----------+---------------------+--------+--------+--------+------------+
|1|zhangsan|-02-:00:00|6000|1|1|xinzhengbu|
|2|lisi|-02-:00:00|6000|2|1|xinzhengbu|
|3|王五|-02-:00:00|6000|1|1|xinzhengbu|
|4|zhaoliu|-02-:00:00|6000|3|1|xinzhengbu|
|1|zhangsan|-02-:00:00|6000|1|2|jixubu|
|2|lisi|-02-:00:00|6000|2|2|jixubu|
|3|王五|-02-:00:00|6000|1|2|jixubu|
|4|zhaoliu|-02-:00:00|6000|3|2|jixubu|
|1|zhangsan|-02-:00:00|6000|1|3|yunying|
|2|lisi|-02-:00:00|6000|2|3|yunying|
|3|王五|-02-:00:00|6000|1|3|yunying|
|4|zhaoliu|-02-:00:00|6000|3|3|yunying|
|1|zhangsan|-02-:00:00|6000|1|4|shejibu|
|2|lisi|-02-:00:00|6000|2|4|shejibu|
|3|王五|-02-:00:00|6000|1|4|shejibu|
|4|zhaoliu|-02-:00:00|6000|3|4|shejibu|
+--------+----------+---------------------+--------+--------+--------+------------+
16rowsinset(0.00sec)
上面两个SQL等价的。
下面是加where条件的等价SQL(select*fromempo,deptwheredeptno=deptid;)
mysql>select*fromempoinnerjoindeptondeptno=deptid;
+--------+----------+---------------------+--------+--------+--------+------------+
|empoid|empname|empdate|salary|deptno|deptid|deptname|
+--------+----------+---------------------+--------+--------+--------+------------+
|1|zhangsan|-02-:00:00|6000|1|1|xinzhengbu|
|3|王五|-02-:00:00|6000|1|1|xinzhengbu|
|2|lisi|-02-:00:00|6000|2|2|jixubu|
|4|zhaoliu|-02-:00:00|6000|3|3|yunying|
+--------+----------+---------------------+--------+--------+--------+------------+
4rowsinset(0.00sec)
例2：左连接
mysql>select*fromempoleftjoindeptondeptno=deptid;
+--------+----------+---------------------+--------+--------+--------+------------+
|empoid|empname|empdate|salary|deptno|deptid|deptname|
+--------+----------+---------------------+--------+--------+--------+------------+
|1|zhangsan|-02-:00:00|6000|1|1|xinzhengbu|
|2|lisi|-02-:00:00|6000|2|2|jixubu|
|3|王五|-02-:00:00|6000|1|1|xinzhengbu|
|4|zhaoliu|-02-:00:00|6000|3|3|yunying|
+--------+----------+---------------------+--------+--------+--------+------------+
4rowsinset(0.00sec)
例3：右连接
mysql>select*fromemporightjoindeptondeptno=deptid;
+--------+----------+---------------------+--------+--------+--------+------------+
|empoid|empname|empdate|salary|deptno|deptid|deptname|
+--------+----------+---------------------+--------+--------+--------+------------+
|1|zhangsan|-02-:00:00|6000|1|1|xinzhengbu|
|3|王五|-02-:00:00|6000|1|1|xinzhengbu|
|2|lisi|-02-:00:00|6000|2|2|jixubu|
|4|zhaoliu|-02-:00:00|6000|3|3|yunying|
|NULL|NULL|NULL|NULL|NULL|4|shejibu|
+--------+----------+---------------------+--------+--------+--------+------------+
5rowsinset(0.00sec)
例4：将子查询替换成连接
表1
mysql>select*fromempo;
+--------+----------+---------------------+--------+--------+
|empoid|empname|empdate|salary|deptno|
+--------+----------+---------------------+--------+--------+
|1|zhangsan|-02-:00:00|6000|1|
|2|lisi|-02-:00:00|6000|2|
|3|王五|-02-:00:00|6000|1|
|4|zhaoliu|-02-:00:00|6000|3|
|5|zhaoliu|-02-:00:00|6000|6|
|6|zhaoliu|-02-:00:00|6000|8|
+--------+----------+---------------------+--------+--------+
6rowsinset(0.00sec)
表2
mysql>select*fromdept;
+--------+------------+
|deptid|deptname|
+--------+------------+
|1|xinzhengbu|
|2|jixubu|
|3|yunying|
|4|shejibu|
+--------+------------+
4rowsinset(0.00sec)
题目1、找出雇员不在部门表中的用户(雇员ID号为5,6的不在部门列表中)
select*fromempowheredeptnonotin(selectdeptidfromdept)
子查询写法
mysql>select*fromempowheredeptnonotin(selectdeptidfromdept);
+--------+---------+---------------------+--------+--------+
|empoid|empname|empdate|salary|deptno|
+--------+---------+---------------------+--------+--------+
|5|zhaoliu|-02-:00:00|6000|6|
|6|zhaoliu|-02-:00:00|6000|8|
+--------+---------+---------------------+--------+--------+
内连接写法(和上面写法同等)
mysql>select*fromempoleftjoindeptonempo.deptno=dept.deptidwheredeptidisNULL;
+--------+---------+---------------------+--------+--------+--------+----------+
|empoid|empname|empdate|salary|deptno|deptid|deptname|
+--------+---------+---------------------+--------+--------+--------+----------+
|5|zhaoliu|-02-:00:00|6000|6|NULL|NULL|
|6|zhaoliu|-02-: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条件
