【MySQL】多表查询

知识点小结：

1.分页查询：

语法：select XXX  limit start，rows（表示从第start+1行开始取数，取出row行数据，start从0开始计算）

规律：select XXX limit 每页查询数据数*（第几页-1），每页显示数据数

2.group by，having，limit，order by的排序：

一个select语句中，同时包含group by，having，limit，order by函数时，排序顺序依次为：group by，having，order by，limit

3.笛卡尔积：

笛卡尔积的结果为两个数据表数据条数相乘数量，例如表1有10条数据，表2有5条数据，笛卡尔积后数据总数为50条。

在默认情况下，两个表查询时，规则如下：

①从第一张表中，取出第一行和第二张表每一行进行组合，返回结果（含第二张表所有列）

②一共返回的记录数，为第一张表行数和第二张表行数的乘积（笛卡尔积）

③解决多表的关键在于写出正确的过滤条件，即where子句

注意：多表查询的条件不能少于表的个数-1，否则会出现笛卡尔积。

4.自连接：

自连接指的是在同一张表上连接查询（将一张表看作两张表）

小结：

①自连接是将一张表看作两张表

②自连接需要给表取别名  ：表名 表别名

③列名不明确时，可指定列别名：列名 as 列别名

#多表查询
#创建员工表emps，部门表dept，工资级别表salgrade
create table emps (empno int,ename varchar(256),job varchar(256),mgr int,birthday datetime,sal double,comm double,deptno int);
create table dept(deptno int,deptname varchar(256),loc varchar(256));
create table salgrade(grade int,lowsal double,highsal double);
insert into emps values(1234,'abby','cleck',3456,'1990-12-12',8000,null,10),
                       (2345,'bitty','saleman',1234,'1987-12-12',6000,500,20),
                       (3456,'cindy','manager',null,'1980-12-12',120000,1200,30),
                       (4567,'doller','cleck',1234,'2000-12-12',7000,800,10),
                       (5678,'eggie','saleman',4567,'1994-12-12',9000,1000,20);
select * from emps;
insert into dept values(10,'accouting','shanghai'),						 
                        (20,'sales','beijing'),
						(30,'management','guangzhou');
select * from dept;
insert into salgrade values(1,5000,6500),(2,6800,7500),(3,8000,15000);		
select * from salgrade;			

#查询出生日期在1990-10-10日后的职工
select * from emps where birthday > '1990-10-10'

#查询首字母为a的职工
select * from emps where ename like 'a%'

#查询第三个字母为n的职工
select * from emps where ename like '__n%'

#查询没有上级的职工
select * from emps where mgr is null

#根据工资高低显示职工信息
select * from emps order by sal desc

#根据部门号升序，工资降序显示职工信息
select * from emps order by deptno asc,sal desc

#显示每个岗位的职工总数和平均工资
select count(*)as '职工总数',avg(sal)as '平均工资',job as '岗位名称'from emps group by job

#显示职工总数量以及获得补助的职工数量
#count可以统计不为null的数据
select count(*) ,count(comm)from emps 

#统计没有获得补助的职工数
select count(*)-count(comm) from emps;

#显示销售员总数
select count(*) from emps where job = 'saleman';

#显示职工最高与最低工资差
select max(sal)- min(sal) from emps;

#显示职工名字，工资以及所在的部门名称
select ename,sal,deptname from emps,dept where emps.deptno = dept.deptno;

#显示部门号为10的部门名称，职工名称和工资
select emps.deptno, deptname,ename,sal from emps,dept where emps.deptno = dept.deptno and emps.deptno = 10;

#显示职工的姓名，工资及工资级别		
select ename,sal,grade from emps,salgrade where sal BETWEEN lowsal and highsal;


#分页查询
#语法：select XXX limit start,rows :表示从start+1行开始取数据，取出row行，start是从0开始计算的
#按职工编号升序取出，每页显示2条记录，请显示前2页数据
select * from emps order by empno limit 0,2;    #取出的第一页数据
select * from emps order by empno limit 2,2;    #取出的第二页数据
#小结：limit 每页查询的记录数*（第几页-1），每页显示的记录数

#笛卡尔积 	
#该查询语句的查询结果为15条数据，emps5*dept3=15	
#emps中每条数据与dept中中每条数据合成一条新数据显示，及笛卡尔积
select * from emps,dept;    

#自连接
#显示职工名字和其上级名字
select emp.ename as '职工名字',boss.ename as '上级名字' from emps emp,emps boss where emp.mgr = boss.empno;