day03(连接查询，子查询，分页查询)-CSDN博客

本文链接：https://blog.csdn.net/qq_44132400/article/details/105105003

sql99标准

-- sql99
#一、内连接
/*
语法：

SELECT 查询列表
FROM 表名1 别名
    【INNER】 JOIN  表名2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表;
SQL92和SQL99的区别：
    SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！
*/
#一）等值连接
#①简单连接
#案例：查询员工名和部门名
select e.last_name,d.department_name
from employees e inner join departments d on e.department_id = d.department_id;
select e.last_name,d.department_name
from departments d inner join employees e on e.department_id = d.department_id;
#②添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
select department_name,city,department_id from departments join locations l on departments.location_id = l.location_id where department_id > 100;
#③添加分组+筛选
#案例1：查询每个城市的部门个数
select count(*),city
from locations join departments d on locations.location_id = d.location_id group by city;
#④添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按员工个数降序
select count(*) 个数,department_name
from departments join employees e on departments.department_id = e.department_id group by e.department_id having count(*) > 10 order by 个数 desc;
#二）非等值连接
#案例1：查询员工的工资和工资级别
select salary,grade_level
from employees join job_grades where salary between lowest_sal and highest_sal;
#三）自连接
#案例：查询员工名和对应的领导名
select e.last_name,l.last_name
from employees e join employees l on e.manager_id = l.employee_id;
#二、外连接

/*
说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null
应用场景：一般用于查询主表中有但从表没有的记录
特点：
1、外连接分主从表，两表的顺序不能任意调换
2、左连接的话，left join左边为主表
   右连接的话，right join右边为主表
语法：
select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件;
*/
USE girls;
#案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
-- 连接条件没有，从表显示为null，查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null
select beauty.*,boys.boyName
from beauty left join boys on beauty.boyfriend_id = boys.id;
select beauty.*,boys.boyName
from boys right join beauty on beauty.boyfriend_id = boys.id;
-- 内连接在连接条件上是严格要求的
select beauty.*,boys.boyName
from beauty  join boys on beauty.boyfriend_id = boys.id;

select boys.*,beauty.name
from boys left join beauty on beauty.boyfriend_id = boys.id;
#案例2：查哪个女神没有男朋友
select beauty.*,boys.boyName
from beauty left join boys on beauty.boyfriend_id = boys.id where boyName is null;
#案例3：查询哪个部门没有员工，并显示其部门编号和部门名
use myemployees;
select distinct departments.department_id ,department_name
from departments left join employees e on departments.department_id = e.department_id where e.last_name is null;

子查询

#子查询
/*
说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。
分类：
按子查询出现的位置进行分类：
1、select后面
   要求：子查询的结果为单行单列（标量子查询）
2、from后面
   要求：子查询的结果可以为多行多列
3、where或having后面 ★
   要求：子查询的结果必须为单列
      单行子查询
      多行子查询
4、exists后面
   要求：子查询结果必须为单列（相关子查询）
特点：
   1、子查询放在条件中，要求必须放在条件的右侧
   2、子查询一般放在小括号中
   3、子查询的执行优先于主查询
   4、单行子查询对应了 单行操作符：> < >= <= = <>
      多行子查询对应了 多行操作符：any/some  all in
*/
#一、放在where或having后面
#一）单行子查询
#案例1：谁的工资比 Abel 高?
select *
from employees where salary > (select salary from employees where last_name = 'Abel');
#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
select last_name,job_id,salary from employees where job_id = (select job_id from employees where employee_id = 141) and salary > (select salary from employees where employee_id = 143);
#案例3：返回公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary
from employees where salary = (select min(salary) from employees);
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
select min(salary),department_id
from employees group by department_id having min(salary) > (select min(salary)from employees where department_id = 50);
#二）多行子查询
/*
in:判断某字段是否在指定列表内
x in(10,30,50)
any/some:判断某字段的值是否满足其中任意一个
x>any(10,30,50)
x>min()
x=any(10,30,50)
x in(10,30,50)
all:判断某字段的值是否满足里面所有的
x >all(10,30,50)
x >max()
*/
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
select last_name
from employees where department_id in (select DISTINCT department_id from departments where location_id in (1400,1700));
#题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select salary
from employees where job_id = 'IT_PROG';
select employee_id,last_name,job_id,salary
from employees where salary < any(select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';
-- 等价于
select salary
from employees where job_id = 'IT_PROG';
select employee_id,last_name,job_id,salary
from employees where salary < (select max(salary) from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';
#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
select salary
from employees where job_id = 'IT_PROG';
select employee_id,last_name,job_id,salary
from employees where salary < all(select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';
-- 等价于
select salary
from employees where job_id = 'IT_PROG';
select employee_id,last_name,job_id,salary
from employees where salary < (select min(salary) from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';
#二、放在select后面
#案例；查询部门编号是50的员工个数
select (select count(*) from employees where department_id = 50);
#三、放在from后面
#案例：查询每个部门的平均工资的工资级别
#四、放在exists后面
#案例1 ：查询有无名字叫“张三丰”的员工信息
select exists(select * from employees where last_name = '张三丰');
#案例2：查询没有女朋友的男神信息
use girls;
select boys.*
from boys where not EXISTS (select boyfriend_id from beauty where boys.id = beauty.boyfriend_id);

分页查询

/*
应用场景：当页面上的数据，一页显示不全，则需要分页显示
分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面
语法：
select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序列表
limit 起始条目索引,显示的条目数
执行顺序：
1》from子句
2》join子句
3》on子句
4》where子句
5》group by子句
6》having子句
7》select子句
8》order by子句
9》limit子句
特点：
①起始条目索引如果不写，默认是0
②limit后面支持两个参数
参数1：显示的起始条目索引
参数2：条目数
公式：
假如要显示的页数是page，每页显示的条目数为size
select *
from employees
limit (page-1)*size,size;
page   size=10
1           limit 0,10
2        limit 10,10
3        limit 20,10
4        limit 30,10
*/
#案例1：查询员工信息表的前5条
use myemployees;
select *
from employees limit 0,5;
#案例2：查询有奖金的，且工资较高的第11名到第20名
select *
from employees where commission_pct is not null order by salary desc limit 10,10;