多表查询 ——》交叉连接，内连接，外连接，子查询（嵌套子查询，相关子查询）

多表查询

使用单个select 语句从多个表格中取出相关的查询结果,多表连接通常是建立在有相互关系的父子表上;

多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

交叉连接

第一个表格的所有行 乘以 第二个表格中的所有行,也就是笛卡尔积

:A表中数据条数 * B表中数据条数 = 笛卡尔乘积.

语法:

隐式语法(不使用关键字): select * from customers,orders;

显式语法(使用关键字): select * from customers CROSS JOIN orders;

注: 交叉连接结果集是不正确的

内连接

因为交叉连接获得的结果集是错误的。因此内连接是在交叉连接的基础上

只列出连接表中与连接条件相匹配的数据行，匹配不上的记录不会被列出。

语法:
隐式语法:
	select * from customers,orders where customers.id=orders.customer_id;
显式语法:
	select * from customers c INNER JOIN orders o ON c.id=o.customer_id;

外连接

链接是以一张表为基表，其他表信息进行拼接,如果有就拼接上,如果没有显示null; 外链接分为左外连接和右下连接。

左外连接: 以关键字左边的表格为基表进行拼接
	语法: select * from customers c LEFT JOIN orders o ON c.id=o.customer_id; 
右外连接: 以关键字右边的表格为基表
	语法: select * from orders o RIGHT JOIN customers c ON c.id=o.customer_id;

右外链接

左外连接

子查询

当进行查询的时候,需要的条件是另外一个select语句的结果,这个时候就会用到子查询,为了给主查询(外部查询) 提供数据而首先执行的查询(内部查询)被叫做子查询; 子查询分为嵌套子查询和相关子查询。

第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用.

嵌套子查询:

内部查询的执行独立于外部查询，内部查询仅执行一次，执行完毕后将结果作为外部查询的条件使用（嵌套子查询中的子查询语句可以拿出来单独运行。）

语法及练习: 查询出id为1的老师教过的所有学生。

select * from students where id in(select s_id from teacher_student where t_id=1);

相关子查询：

内部查询的执行依赖于外部查询的数据，外部查询每执行一次，内部查询也会执行一次。每一次都是外部查询先执行，取出外部查询表中的一个元组，将当前元组中的数据传递给内部查询，然后执行内部查询。根据内部查询执行的结果，判断当前元组是否满足外部查询中的where条件，若满足则当前元组是符合要求的记录，否则不符合要求。然后，外部查询继续取出下一个元组数据，执行上述的操作，直到全部元组均被处理完毕。

求：每一科考试成绩大于平均分的学生的分数。
select * from score as a where a.score>(select avg(b.score) from score as b where a.cou_id=b.cou_id);

子查询练习题

-- 第一题 建表
create table departments(
dept_no char(4) primary key ,
dept_name1 varchar(40) not null unique
)

create table employees(
emp_no int primary key auto_increment,
birth_date DATE not null,
name varchar(14) not null,
hire_date DATE not null
)

create table salaries(
emp_no int  not null,
salary int not null,
month int not null,
level int not null,
constraint emp_id_fk foreign key(emp_no) references employees(emp_no)
)

create table dept_emp(
emp1_no int not null,
dept1_no char(4) not null,
constraint emp1_id_fk foreign key(emp1_no) references employees(emp_no),
constraint dept1_id_fk foreign key(dept1_no) references departments(dept_no)
)

-- 第二题
insert into departments values('1001','A')
insert into departments values('1002','B')
insert into departments values('1003','C')
insert into departments values('1004','D')

-- 第三题 插入数据

insert into employees values('100','1990-08-19','JACK','20160811')
insert into employees values('101','1970-08-12','TOM','20100606')
insert into employees values('102','1996-03-19','JAMES','20140101')
insert into employees values('103','1987-04-28','KETTY','20130910')
insert into employees values('104','1983-05-19','JIM','20160418')

-- 第四题 插入数据
insert into salaries values('100','12000','201601',2),('101','9000','201601',1),('102','90000','201601',10),('103','2300','201601',1),('104','4000','201601',1),('100','12000','201602',2),('101','9000','201602',1),('102','90000','201602',10),('103','2300','201602',1),('104','4000','201602',1),('100','12000','201603',2),('101','9000','201603',1),('102','90000','201603',10),('103','2300','201603',1),('104','4000','201603',1),('100','12000','201604',2),('101','9000','201604',1),('102','90000','201604',10),('103','2300','201604',1),('104','4000','201604',1)


-- 第五题 插入数据
insert into dept_emp values('100','1001')
insert into dept_emp values('101','1001')
insert into dept_emp values('102','1002')
insert into dept_emp values('103','1003')
insert into dept_emp values('104','1004')

-- 第六题 分别查看部门表和员工表中的所有记录
select * from departments
select * from employees

-- 第七题 将雇员表按照出生日期进行降序排列，并找到前三条数据

select birth_date from employees order by birth_date desc limit 3


-- 第八题 查询2016年1月份的工资平均值
select avg(salary) from salaries where month='201601'

select sum(salary)/count(201601) from salaries


-- 第九题 查询雇员ID小于103且名字以J开头的员工信息
select * from employees where emp_no <103 and name like'J%'


-- 第十题 查询雇员ID在101-103之间的员工信息（两种方式）
select * from employees where emp_no>=101 and emp_no<=103
select * from employees where emp_no in(101,102,103)
select * from employees where emp_no between 101 and 103

-- 第十一题 查询所有名字以M结尾的雇员信息，并按照入职时间进行倒叙排序
select * from employees where  name like'%M' order by birth_date desc

-- 第十二题 .查询每个员工所属的部门

select * from employees e LEFT JOIN dept_emp d on e.emp_no=d.emp1_no LEFT JOIN departments d1 on d.dept1_no=d1.dept_no;
select d1.dept_name1,e.name from employees e LEFT JOIN dept_emp d on e.emp_no=d.emp1_no LEFT JOIN departments d1 on d.dept1_no=d1.dept_no;

-- 第十三题  查询属于A部门的员工
select * from employees where emp_no in(select emp1_no from dept_emp where dept1_no=(select dept_no from departments where dept_name1='A'))

-- 第十四题 查询属于A部门员工的平均工资

select Avg(salary) from salaries where emp_no in(select emp_no from employees where emp_no in(select emp1_no from dept_emp where dept1_no=(select dept_no from departments where dept_name1='A')))

-- 第十五题 查询所有员工的薪水总和

select * from employees e  LEFT JOIN dept_emp d ON e.emp_no=d.emp1_no INNER JOIN departments d1 ON d.dept1_no=d1.dept_no INNER JOIN salaries s ON s.emp_no=e.emp_no; 

select SUM(salary) from salaries;


-- 第十六题 查询各个部门的员工平均工资


select AVG(s.salary) from departments d LEFT JOIN dept_emp d1 ON d.dept_no=d1.dept1_no LEFT JOIN salaries s ON d1.emp1_no=s.emp_no GROUP BY d.dept_name1


select * from departments d LEFT JOIN dept_emp d1 ON d.dept_no=d1.dept1_no LEFT JOIN salaries s ON d1.emp1_no=s.emp_no GROUP BY d.dept_name1