连接查询复习
一、含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,...;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
二、分类
按年代分类:
sql92:
等值
非等值
自连接
也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99【推荐使用】
内连接
等值
非等值
自连接
外连接
左外
右外
全外(mysql不支持)
交叉连接
三、SQL92语法
1、等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2、非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
四、SQL99语法
1、内连接
语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
等值连接
非等值连接
自连接
2、外连接
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
②left join 左边的就是主表,right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
3、交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积
子查询复习
一、含义
嵌套在其他语句内部的select语句称为子查询或内查询,
外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
外面如果为select语句,则此语句称为外查询或主查询
二、分类
1、按出现位置
select后面:
仅仅支持标量子查询
from后面:
表子查询
where或having后面:
标量子查询
列子查询
行子查询
exists后面:
标量子查询
列子查询
行子查询
表子查询
2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列⭐
列子查询(多行子查询):结果集为多行一列⭐
行子查询:结果集为多行多列
表子查询:结果集为多行多列
三、示例
where或having后面
1、标量子查询
案例:查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资,要求工资=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);
2、列子查询
案例:查询所有是领导的员工姓名
①查询所有员工的 manager_id
select manager_id
from employees
②查询姓名,employee_id属于①列表的一个
select last_name
from employees
where employee_id in(
select manager_id
from employees
);
分页查询
应用场景:当需要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表格
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset,size;
offset:要显示条目的起始索引(起始索引从0开始)
size:要显示的条目个数
特点:
1.limit语句放在查询语句的最后
2.公式
要显示的页数page,每页的条目数size
select 查询列表
from 表
limit (page-1)*10,size
size=10
page
1 0
2 10
3 20
案例1:查询前五条员工信息;
SELECT * FROM employees LIMIT 0,5;
#等价于:
SELECT * FROM employees LIMIT 5;
案例2:查询第11条到第25条;
SELECT * FROM employees 10,15;
综合练习题
案例1:查询所有学员的邮箱的用户名(注:邮箱中 @前面的字符)
SELECT SUBSTRING(email,1,INSTR(email,'@')-1) 用户名
FROM stuinfo;
案例2:查询男生和女生的个数
SELECT COUNT(*) 个数, sex
FROM stuinfo
GROUP BY sex;
案例3:查询年龄>18岁的所有学生的姓名和年级名称
SELECT NAME,gradeName
FROM stuinfo s
INNER JOIN grade g
ON s.gradeId=g.id
WHERE age>18;
案例4:查询哪个年级的学生最小年龄>20岁
# 1.每个年级的最小年龄
SELECT MIN(age),gradeId
FROM stuinfo
GROUP BY gradeId
# 2.在1的结果上进行筛选
SELECT MIN(age),gradeId
FROM stuinfo
GROUP BY gradeId
HAVING MIN(age)>20;