sql连表查询、子查询、组合查询

1.连表查询

连表查询大多数都作用在外键得基础 ------表与表之间有关联

表与表之间存在得关系

1. 一对多: 在多得一方添加外键列

2. 多对多: 需要在创建一个中间表，该表中至少有两个外键列  

1.1 内连接:

 

-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
   -- 连表查询时，如果不使用连表条件则出现笛卡尔集。
   -- 所谓笛卡尔集 就是A表中每一条记录关联B中中得每条记录
-- 1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
-- 如果表的名字很长 可以为表起别名
select * from tb_emp e,tb_dept d where e.dept_id=d.id;

-- 显示连接: A表 inner join B表 on 连表条件。 
-- 2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)
select * from tb_emp  inner join tb_dept on tb_emp.dept_id=tb_dept.id;
-- 如果表的名字很长 可以为表起别名
select * from tb_emp e inner join tb_dept d on e.dept_id=d.id;
-- 上面的inner可以省略
select * from tb_emp e join tb_dept d on e.dept_id=d.id;

1.2 外连接：

-- 语法: select 查询列集 from A表 left join B表 on 连表条件
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id=d.id;
select * from tb_emp e left  join tb_dept d on e.dept_id=d.id;

-- 2．查询dept表的所有数据，和对应的员工信息(右外连接)
select * from tb_emp e right outer join tb_dept d on e.dept_id=d.id;
select * from tb_emp e right  join tb_dept d on e.dept_id=d.id;

 1.3 自联查询：

   自己和自己相连接查询  ---- select * from A表 join A表 on 连表条件

-- 自联查询
-- 自己和自己相连接查询 select * from A表 join A表 on 连表条件。
-- -- 1．查询员工及其所属领导的名字
  -- 你要查询的结果再一张表中，但是还不能使用单表查询得到结果
	-- 显示内连接
select a.name,b.name from tb_emp a join tb_emp b on a.managerid=b.id;

-- 2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来
-- 左外连接
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

2. 子查询---嵌套查询

-- 子查询-- 嵌套查询
-- 查询市场部的员工信息：市场部-- tb_dept 市场部的编号，然后再根据编号查询员工信息
-- 1 根据市场部 查询部门编号
select id from tb_dept where name='市场部';
-- 2 根据部门编号 查询该部门下的员工信息
select * from tb_emp where dept_id=2;

-- 查询市场部的员工信息:子查询返回的结果一列一条记录, 这个时候可以用=
select * from tb_emp where dept_id=(select id from tb_dept where name='市场部');

-- 子查询能能解决的都可以用连表查询，但是连表查询能解决的子查询不一定解决
select e.*from tb_emp e,tb_dept d where e.dept_id=d.id and d.name='市场部';

-- 查询市场部和研发部员工的信息  in 
-- 查询市场部和研发部员工的信息
-- 1 查询市场部和研发部的编号
select id from tb_dept where name in('市场部','研发部');
-- 2 再员工表中根据部门编号查询员工信息
select * from tb_emp where dept_id in(1,2);
select * from tb_emp where dept_id in(select id from tb_dept where name in('市场部','研发部'));

-- 查询在“方东白”入职之后的员工信息
-- 1 查询方东白入职日期
select entrydate from tb_emp where name='方东白';
-- 2 根据方东白的入职日期查询其他员工的信息
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白');

-- 查询比财务部所有人工资都高的员工信息
-- 1 求出财务部最高的工资
select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部';
--  根据财务部最高工资查询其他员工信息
select * from tb_emp where salary>(select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部');

3. 组合查询

-- 组合查询
-- 多个查询的结果 组合到一起
  -- sql union sql --->把这两条sql查询的结果组合到一起,如果有重复记录则合并成一条
  -- sql union all sql --->把这两条sql查询的结果组合到一起,如果有重复记录，不合并 
-- 注意: 这两条sql返回的字段必须一样

select * from tb_emp where salary>8000
union all
select * from tb_emp where age>40;

select * from tb_emp where salary>8000
union 
select * from tb_emp where age>40;

例题1：

create table tb_emp(
    id int primary key auto_increment COMMENT '员工编号',
		name varchar(20) COMMENT '员工姓名',
		age int COMMENT '员工年龄',
		job varchar(20) COMMENT '岗位',
		salary int COMMENT '薪水',
		entrydate date COMMENT '员工入职时间',
		managerid int COMMENT '员工领导编号',
		dept_id int COMMENT '员工所在部门编号'
);
create table tb_dept(
    id int primary key auto_increment,
		name varchar(20)
);
insert into tb_dept(name) values('研发部'),('市场部'),('财务部'),('销售部'),('总经办');

insert into tb_emp values 
(null,'金庸',66,'总裁',20000,'2000-01-01',null,5),
(null,'张无忌',20,'项目经理',12500,'2005-12-01',1,1),
(null,'杨逍',33,'开发',8400,'2000-11-03',2,1),
(null,'韦一笑',48,'开发',11000,'2002-03-05',2,1),
(null,'常豫川',43,'开发',10500,'2004-09-01',2,1),
(null,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1),
(null,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
(null,'周芷若',60,'会计',48000,'2006-06-01',7,3),
(null,'丁敏君',19,'出纳',4000,'2009-06-01',7,3),
(null,'赵敏',23,'市场总监',14000,'2009-06-01',1,2),
(null,'鹿仗客',56,'职员',3750,'2009-06-01',10,2),
(null,'鹤比翁',19,'职员',3750,'2009-06-01',10,2),
(null,'方东白',19,'职员',3750,'2009-06-01',10,2),
(null,'张三丰',88,'销售总监',14000,'2004-06-01',1,4),
(null,'玉莲舟',38,'销售',4600,'2009-06-01',