首先新建两张表（beanty表和boys表）
create table beauty( id int(55), bename varchar(55), sex varchar(55), boyfriendid int(55) ); create table boys( id int(55), byname varchar(55) );
分别向表中添加数据
insert into beauty(id,bename,sex) values(1,'柳岩','35'); insert into beauty(id,bename,sex,boyfriendid) values(2,'Angelababy','33',3); insert into beauty(id,bename,sex,boyfriendid) values(3,'小昭','28',1); insert into beauty(id,bename,sex,boyfriendid) values(4,'赵敏','26',1); insert into beauty(id,bename,sex,boyfriendid) values(5,'小龙女','30',2); insert into beauty(id,bename,sex,boyfriendid) values(6,'周芷若','29',1); insert into beauty(id,bename,sex) values(7,'周冬雨','26'); insert into beauty(id,bename,sex) values(8,'迪丽热巴','26'); insert into boys(id,byname) values(1,'张无忌'); insert into boys(id,byname) values(2,'杨过'); insert into boys(id,byname) values(3,'黄晓明'); insert into boys(id,byname) values(4,'猪八戒'); insert into boys(id,byname) values(5,'孙悟空');
查询每个女神以及对应的男朋友的姓名
select bename,byname from beauty,boys;
查询结果不符合现实：
笛卡尔乘积现象：表1有m行，表2有n行，查询结果=m*n;
发生的原因：没有有效的连接条件
正确：添加有效的连接条件
select bename,byname from beauty,boys where beauty.boyfriendid=boys.id;
多表查询又称连接查询
含义：当查询的字段来自于多个表时，就需要用到多表查询
分类：内连接、外连接、交叉连接
新建：员工表和部门表
#员工表 create table employee( id int(55), ename varchar(55), sex varchar(55), leaderid int(55), pay int(55), departmentid int(55) ); #部门表 create table department( id int(55), dname varchar(55) ); #等级表 create table grade( id int(55), min int(55), max int(55), level varchar(55) );
分别向表中添加数据：
insert into employee(id,ename,sex,leaderid,pay,departmentid) values(101,'张三','男','104',6999,201901); insert into employee(id,ename,sex,leaderid,pay,departmentid) values(102,'李四','男','104',5999,201901); insert into employee(id,ename,sex,leaderid,pay,departmentid) values(103,'王五','男','102',4999,201902); insert into employee(id,ename,sex,leaderid,pay,departmentid) values(104,'赵六','男','101',4999,201903); insert into employee(id,ename,sex,leaderid,pay,departmentid) values(105,'小强','男','101',3999,201901); insert into employee(id,ename,sex,leaderid,pay,departmentid) values(106,'老王','男','102',3999,201902); insert into employee(id,ename,sex,leaderid,pay,departmentid) values(107,'王二','男','101',2999,201903); insert into department(id,dname) values(201901,'技术部'); insert into department(id,dname) values(201902,'市场部'); insert into department(id,dname) values(201903,'后勤部'); insert into grade(id,min,max,level) values(1,2000,3000,'c'); insert into grade(id,min,max,level) values(2,3000,4000,'d'); insert into grade(id,min,max,level) values(3,4000,5000,'b'); insert into grade(id,min,max,level) values(4,5000,6000,'a');
内连接：等值连接、非等值连接、自连接
等值连接：
查询员工姓名以及对应的部门名称
select ename,dname from employee,department where employee.departmentid=department.id; select ename,dname from employee e,department d where e.departmentid=d.id; select e.ename,d.dname from employee e,department d where e.departmentid=d.id;
非等值连接：
查询员工的姓名以及对应的等级
select ename,level from employee e,grade g where e.pay<g.max and e.pay>g.min; select ename,level from employee e,grade g where e.pay between g.min and g.max;
自连接：
查询员工的姓名以及对应的上级姓名
select e.ename 领导姓名,m.ename 员工姓名 from employee e,employee m where m.leaderid=e.id;
sql92的语法：
select 查询列表 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;
sql99的语法：
select 查询列表 from 表1 别名 [连接类型] join 表2 别名 on 连接条件 where 筛选条件;
连接类型：
内连接 ：inner
外连接 ：
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】
交叉连接：cross
一、内连接：
语法：select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件 where 筛选条件;
1）、等值连接：
查询员工姓名以及对应的部门名称
select ename,dname from employee e inner join department d on e.departmentid=d.id;
2）、非等值连接
查询员工的姓名以及对应的等级
select ename,level from employee e inner join grade on e.pay>g.min and e.pay<g.max; select ename,level from employee e inner join grade g on e.pay between g.min and g.max;
3）、自连接
查询员工的姓名以及对应的上级姓名
select e.ename,m.ename from employee e inner join employee m on e.leaderid=m.id;
二、外连接
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值。如果从表中没有和它匹配的，则显示为null。
外连接的查询结果 = 内连接 + 主表中有而从表中没有的记录
左外连接中：left左边的是主表 ； 右外连接中：right join 右边的是主表 ； 左外和右外交换两个表的顺序可以实现同样的效果
新建学生表student和成绩表score
create table student( id varchar(55), sname varchar(55), age int(55), class varchar(55), sex varchar(55) ); create table score( id varchar(55), cname varchar(55), fraction int(55), sid varchar(55) );
分别向表中添加数据：
insert into student(id,sname,age,class,sex) values('101','张三',21,'软件技术班','男'); insert into student(id,sname,age,class,sex) values('102','李四',23,'软件技术班','女'); insert into student(id,sname,age,class,sex) values('103','王五',22,'软件技术班','男'); insert into student(id,sname,age,class,sex) values('104','赵六',24,'软件技术班','男'); insert into student(id,sname,age,class,sex) values('105','小强',18,'软件技术班','女'); insert into student(id,sname,age,class,sex) values('106','老王',40,'软件技术班','男'); insert into student(id,sname,age,class,sex) values('107','嗝屁老王',100,'软件技术班','男'); insert into score(id,cname,fraction,sid) values('m123','MySQL',90,'101'); insert into score(id,cname,fraction,sid) values('m123','MySQL',89,'102'); insert into score(id,cname,fraction,sid) values('m123','MySQL',96,'106'); insert into score(id,cname,sid) values('m123','MySQL','103'); insert into score(id,cname,sid) values('m123','MySQL','104'); insert into score(id,cname,sid) values('m123','MySQL','105'); insert into score(id,cname,fraction,sid) values('j456','Java',78,'101'); insert into score(id,cname,fraction,sid) values('j456','Java',85,'103'); insert into score(id,cname,fraction,sid) values('j456','Java',95,'106'); insert into score(id,cname,sid) values('j456','Java','102'); insert into score(id,cname,sid) values('j456','Java','104'); insert into score(id,cname,sid) values('j456','Java','105'); insert into score(id,cname,fraction,sid) values('m123','ps',99,'100');
1）、左外连接
查询所有学生的姓名和各科成绩
select sname,cname,fraction from student s left join score c on s.id=c.sid;
查询所有学生的姓名和MySQL的成绩
select sname,cname,fraction from student s left join score c on s.id=c.sid where c.cname='mysql';
2）、右外连接
查询所有学生的姓名和各科成绩
select sname,cname,fraction from score c right join student s on s.id=c.sid;
查询所有学生的姓名和Java的成绩
select sname,cname,fraction from score c right join student s on s.id=c.sid where c.cname='Java';
3）、全外连接
全外连接 = 内连接 + 表1中有但表2中没有 + 表2中有但表1中没有的记录
select sname,cname,fraction from score c full join student s on s.id=c.sid;
由于mysql不支持full join，所以会报错，可以用left join一次,加上union，再来right join实现全连接的效果。
select sname,cname,fraction from student s left join score c on s.id=c.sid UNION select sname,cname,fraction from student s right join score c on s.id=c.sid;
三、交叉连接
笛卡尔乘积
select sname,cname,fraction from score cross join student;
sql92和sql99比较
1、功能方面：sql99支持的较多
2、可读性：sql99实现了连接条件和筛选条件的分离，因此可读性较高