一、多表查询
1、交叉连接:不使用任何匹配条件,生成笛卡尔积
》》select * from 表1,表2;就是将表1的每条记录和表2中所有记录组成一条记录
》》笛卡尔积:AxB 就是将A中的每个元素和B中的所有可能组成有序对
》》作用:就是将两张表合为一张表(虚拟表)
2、内连接:inner join,只连接匹配上的记录
》》原理:select * from 表1,表2 where 表1.字段=表2.字段 先生成笛卡尔积,再按约束条件筛选,最后合成一张虚拟表,但是一般我们不这么写
》》select * from 表1 inner join 表2 on 表1.字段=表2.字段
利用inner join和on的组合,on 后是以什么条件从笛卡尔积中筛选出来想要的表,也就是连接条件
后面也可以跟where和其它条件,用表名.字段名来表示,因为两张表中可能有重复的字段名
》》作用:取出两张表中的共同部分
3、外连接之左连接:left join
》》作用:在内连接的基础上保留左表全部的记录,也就是左表有而右表没有的记录,先写谁谁就是左表
》》select * from 表1 left join 表2 on 连接条件 ,后面也可以跟where和其它条件
4、外连接之右连接:right join
》》作用:在内连接的基础上,保留右表的全部记录,也就是右表有而左表没有的记录
》》select * from 表1 right join 表2 on 连接条件 ,后面也可以跟where和其它条件
5、全外连接:union
》》作用:在内连接的基础上,保留左表和右表中的全部记录
》》注意mysql不支持full join
》》union all不会去掉重复的部分
6、优先级问题
》》首先执行from,找到左表和右表,并生成笛卡尔积
》》按照on的连接条件得到内连接的结果(也就是两张表共同部分组成的虚拟表)
》》然后再执行 inner/left/right join(在上一得到的虚拟表的基础上)又得到一个新的虚拟表
》》之后再继续where和后面条件的优先级
7、3张表或多张表连接
》》可以用两张表连接的最终结果(一张虚拟表)经过as改名后,再跟第三张表连接,以此类推
》》select * from (两张表连接产生的虚拟表) as 新表名 inner join 表3 on 连接条件
》》注意经过练习,应该多张表做连接最后生成的表中不能有2个以上的完全相同的列,就是字段名和字段中所有的值都相同
》》》》》》补充:null值会被聚合函数count计数为0
8、子查询
》》作用:一个查询语句嵌套在另一个查询语句内;内层查询语句的结果可作为外层查询语句的条件,注意内层查询语句不能有分号
》》 in 关键字
#查询employee表,但dep_id必须在department表中出现过 select * from employee where dep_id in (select id from department);
》》比较运算
#比较运算符:=、!=、>、>=、<、<=、<> #查询平均年龄在25岁以上的部门名 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); #查看技术部员工姓名 select name from employee where dep_id in (select id from department where name='技术'); #查看不足1人的部门名 select name from department where id in (select dep_id from employee group by dep_id having count(id) <=1);
》》exists关键字:内层查询语句不会返回一个记录结果,而是返回True或False,当返回True时外层查询语句才会执行,返回False时外层查询语句不会执行
#department表中存在dept_id=203,Ture mysql> select * from employee -> where exists -> (select id from department where id=200); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ #department表中存在dept_id=205,False mysql> select * from employee -> where exists -> (select id from department where id=204); Empty set (0.00 sec)