一、DQL(Data Query Language):数据查询语言。
进阶1:基础查询
语法:
SELECT 要查询的东西
【FROM 表名】;
类似于Java中 :System.out.println(要打印的东西);
特点:
①通过select查询完的结果 ,是一个虚拟的表格,不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
进阶2:条件查询
条件查询:根据条件过滤原始表的数据,查询到想要的数据
语法:
select
要查询的字段|表达式|常量值|函数
from
表
where —————>其实就是一个行过滤器,返回值是boolean,true为真
条件 ;
分类:
一、条件表达式
示例:salary>10000
条件运算符:
> < >= <= = != <>
二、逻辑表达式
示例:salary>10000 && salary<20000
逻辑运算符:
and(&&):两个条件如果同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true
三、模糊查询 %
示例:last_name like ‘a%’
进阶3:排序查询
语法:
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名 【asc|desc】
进阶4:常见函数
一、单行函数
二、分组函数
功能:
用作统计使用,又称为聚合函数或统计函数或组函数
sum()求和
max()最大值
min()最小值
avg()平均值
count()计数
特点:
1、以上五个分组函数都忽略null值,除了count(*)
2、sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果
4、count的参数可以支持:
字段、*、常量值,一般放1
建议使用 count(*)
进阶5:分组查询
语法:
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
注意:
查询列表必须特殊,要求是分组函数和 group by后出现的字段
当需求涉及的语句比较多,我们一眼看不出来,我们可以分步来做。
① --- ②----
特点:
1、分组查询中的筛选条件分为两类
数据源(筛选的表) 位置 使用关键字
分组前筛选: 原始表 group by的前面 where
分组后筛选: 分组后的结果集 group by的后面 having
①分组函数做条件肯定是放在 having子句中
②能用分组前筛选的,就优先考虑使用分组前筛选
2、groupby子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(用得较少)
3、添加排序(排序放在整个分组查询的最后)
分组+筛选的案例:
#案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
manager_id>102
SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>5000;
--------------------------------------------------------------------------------------
①查询每个领导手下的员工固定最低工资
SELECT MIN(salary), manager id
FROM employees GROUP BY manager id
②添加筛选条件:编号>102
SELECT MIN(salary),manage_id
FROM employees GROUP BY manager id
③添加筛选条件:最低工资>5000
SELECT MIN(salary), manager id
FROM employees
WHERE manager id>102
GROUP BY manager id HAVING MIN (salary)>5000;
进阶6:多表连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件。本质就是过滤条件,是有特殊意义的过滤条件。
sql99语法:通过join关键字实现连接
含义:1999年推出的sql语法
支持:
等值连接(内连接)
外联结:
左右外联结,就是选择保留左右外联结表的完整数据
交叉连接
语法:
select 字段,...
from 表1
【inner|left outer|right outer|cross】join 表2 on 联结条件——>on后面,理解为特殊筛选条件
【inner|left outer|right outer|cross】join 表3 on 联结条件
【where 筛选条件】——>理解为普通筛选条件
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】——>排序的条件放在最后
好处:语句上,连接条件和筛选条件实现了分离,简洁明了!
不错的一道多表连接题目:
3、查询所有国家的首都和官方语言
SELECT
co.`Capital`,
cl.`Language`,
co.`Name`
FROM
country co
LEFT OUTER JOIN
countrylanguage cl
ON
co.code = cl.`CountryCode`
LEFT OUTER JOIN
city
ON
co.`Capital` = city.`ID`
AND --------->这里不能用while,用while就把多表联结中主表的包含null的值全行过滤掉了,导致前面的左/右连接失效。
--------->用and,就能解决过滤问题,保留有null的行数据。
cl.`IsOfficial` = 't'
进阶7:子查询
含义
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
使用
在查询时基于未知的值时,应使用子查询。分别考虑查询语句。
分类:
1、按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:
标量子查询(单行单列,就是一个单元格)
列子查询 (多行)
行子查询(用的少)
exists后面(相关子查询)
表子查询
2、按结果集的行列数不同:
标量子查询(单行子查询):结果集只有一行一列
列子查询(多行子查询):结果集为多行一列
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一、where或having后面
1、标量子查询(单行单列子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
3、子查询优先于主查询执行,主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类:
① 单行子查询
结果集返回只有一行
一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集返回多行,列子查询
一般搭配多行操作符使用:any/some、all、in/not in
in: 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
1.标量子查询★
1.标量子查询★
#案例1:谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
#②查询员工的信息,满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
#③查询员工的姓名,job_id 和工资,要求job_id=①并且salary>②
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
);
2.列子查询(多行子查询)★ 结果集是一列多行
#2.列子查询(多行子查询)★结果集是一列多行
#案例1:返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
#②查询员工姓名,要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
3、行子查询(结果集一行多列或多行多列)-用的不多
#案例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
-----------------------以下是之前方法,以上是列子查询
#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees
#②查询最高工资
SELECT MAX(salary)
FROM employees
#③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)AND salary=(
SELECT MAX(salary)
FROM employees
);
二、select后面 —一般都能用其他方式写出。弱化。
仅仅支持标量子查询
案例:见官网mysql基础_视频名:91_尚硅谷_MySQL基础_select后面的子查询使用
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
三、from/join后面
from后面一般都是表。
将子查询结果集充当一张表,作为数据源使用,结果集几行几列根据需求来。要求必须起别名
#案例:查询每个部门的平均工资的工资等级 ——官网视频_92_尚硅谷_MySQL基础_from后面的子查询使用-已看
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
SELECT * FROM job_grades;
#②连接①的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exists后面(相关子查询)—-未看
语法:
exists(完整的查询语句)
结果:
1或0
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
#案例1:查询有员工的部门名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
)
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
进阶8:分页查询-未看
应用场景:
实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法:
select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引,】条目数;
特点:
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式:select * from 表 limit (page-1)*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
进阶9:联合查询-未看
引入:
union 联合、合并
语法:
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】
特点:
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重,union all代表不去重