复习和练习2 连接查询，子查询，分页查询

1.SQL99语法

1.1 内连接

1. 语法：

select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
where 筛选条件
group by 分组条件
having 分组后筛选
order by 排序列表
limit子句；

2. 特点：

• a.表的顺序可以调换
• b.内连接的结果=多表的交集
• c.n表连接至少需要n-1个连接条件

3. 分类：

• 等值连接
• 非等值连接
• 自连接

1.2.外连接

1. 语法

select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组条件
having 分组后筛选
order by 排序列表
limit子句；

2. 特点：

• a.查询的结果=主表中所有的行，其中从表和他匹配的将显示匹配行，如果从表没有匹配的则显示为null
• b.left outer左边的就是主表，right join右边的就是主表
  full join两边都是主表
• c.一般用于查询除了交集部分剩余的不匹配的行

1.3.交叉连接

1. 语法：

select 查询列表
from 表1 别名
cross join 表2 别名;

2. 特点：类似于笛卡尔乘积

2.子查询

1. 含义：嵌套在其他语句内部的select语句称为子查询或内查询
   外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
   外面如果为select语句，则此语句成为外查询或主查询

2. 分类：

3.     * 按出现位置：select后面：
                                   仅仅支持标量子查询
                    from后面：
                                     表子查询
                    where或having后面：（*）
                                 标量子查询（*），列子查询（*），行子查询
                    exists后面：
                                 标量子查询，列子查询，行子查询，表子查询
   
       * 按结果集的行列：
                    标量子查询（单行子查询）：结果为一行一列
                    列子查询（多行子查询）：结果集为多行一列
                    行子查询：结果集为多行多列
                    表子查询（嵌套子查询）：结果集为多行多列
   

4. 示例

   where或having后面

     (1).标量子查询
   

案例：查询最低工资的员工姓名和工资

select last_name,salary from employees where salary=(select min(salary) from employees);

      (2).列子查询

*** 案例：查询所有是领导的员工姓名***

select last_name from employees where id in (select manager_id from employees);

3.分页查询

1. 应用场景：当要查询的条目数太多，一页显示不全

2. 语法：

select 查询列表 from 表 limit 【offset, 】size;

• 注意：offset代表的是起始的条目索引，默认从0开始
  size代表的是现实的条目数

• 公式：假如要显示的页数为page，每一页的条目数为size

    `select 查询列表 from 表 limit （page-1）*size,size;` 
  

4.练习

*** 1.查询工资最低的员工信息：last_name,salary***

select last_name,salary from employees where salary=(select min(salary) from employees);

*** 2.查询平均工资最低的部门信息***

• 方式一：

  • a.查询各部门的平均工资
    select department_id,avg(salary) from employees group by department_id;
  • b.查询最低平均工资
    select min(ag) from ( select department_id,avg(salary) ag from employees group by department_id ) avg_dep
  • c.查询平均工资最低的部门编号
    select department_id,avg(salary) from employees group by department_id having avg(salary)=( select min(ag) from ( select department_id,avg(salary) ag from employees group by department_id ) avg_dep );
  • d.查询平均工资最低的部门信息
    select * from departments d where d.department_id=( select department_id from employees group by department_id having avg(salary)=( select min(ag) from ( select department_id,avg(salary) ag from employees group by department_id ) avg_dep ) );

• 方式二：

  • a.各部门的平均工资
    select avg(salary),e.department_id from employees e group by e.department_id;
  • b.分页查询获取平均工资最低的部门id
    select avg(salary),e.department_id from employees e group by e.department_id order by avg(salary) asc limit 1;
  • c.查询平均工资最低的部门信息
    select * from departments d where d.department_id = (select e.department_id from employees e group by e.department_id order by avg(salary) asc limit 1);

3.查询平均工资最低的部门信息和该部门的平均工资

select d.*,e.avg(salary) from departments d inner join employees e on d.department_id = e.department_id group by e.department_id order by avg(salary) asc limit 1;

4.查询平均工资最高的job信息

select * from jobs j where j.job_id = (select e.job_id from employees e group by e.job_id order by avg(salary) desc limit 1);

5.查询平均工资高于公司平均工资的部门有哪些

select department_id,avg(salary) from employees group by department_id having avg(salary) > (select avg(salary) from employees);

6.查询出公司中所有manager的详细信息

select * from employees where employee_id in (select manager_id from employees);

7.各个部门中，最高工资中最低的那个部门的最低工资是多少

select min(salary) from employees group by department_id having department_id = (select department_id from employees group by department_id order by max(salary) asc limit 1);

8.查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary

select last_name,department_id,email,salary from employees where employee_id = (select manager_id from employees group by department_id order by avg(salary) desc limit 1);

9.查询每个专业的学生人数

select majorid,count(*) from student group by majorid;

10.查询参加考试的学生中，每个学生的平均分，最高分

select avg(score),max(score) from result group by studentno;

11.查询姓张的每个学生的最低分大于60的学号，姓名

select s.studentno,s.studentname,min(score) from student s inner join result r on s.studentno=r.studentno where s.studentname like '张%' group by s.studentno having min(score) >60;

12.查询生日在"1998-1-1"后的学生姓名，专业名称

select studentname,majorname from student s inner join major m on s.majorid=m.majorid where datediff(borndate,'1988-1-1')>0;

13.查询每个专业的男生人数和女生人数分别是多少

• 方式一：
  select count(*),sex,majorid from student group by sex,majorid;

• 方式二：
  select majorid, (select count(*) from student where sex = '男' and majorid=s.majorid) 男 (select count(*) from student where sex = '女' and majorid=s.majorid) 女 from student s group by majorid;

14.查询专业和张翠山一样的学生的最低分

select min(score) from result group by majorid having studentno in (select studentno from student where majorid = (select majorid from student where studentname='张翠山'));

15.查询大于60分的学生的姓名、密码、专业名

select studentname,loginpwd,majorname from student s inner join major m on s.majorid = m.majorid inner join result r on s.studentno=r.studentno where r.score>60;

16.按邮箱位数分组，查询每组的学生个数

select count(*),length(email) from student group by length(email);

17.查询学生名，专业名，分数

select studentname,score,majorname from student s inner join major m on s.majorid = m.majorid inner join result r on s.studentno=r.studentno;

18.查询哪个专业没有学生，分别用左连接和右连接实现

• 左连接
  select m.majorid,m.majorname,s.studentno from major m left outer join student s on m.majorid=s.majorid where s.studentno is null;

• 右连接
  select m.majorid,m.majorname,s.studentno from student s right outer join major m on m.majorid=s.majorid where s.studentno is null;

19.查询没有成绩的学生人数

select count(*) from student s left join result r on s.studentno=r.studentno where r.id is null