一、排重 distinct
在查询时,一个字段的值可能有多个重复的数据,通过distinct可以排除重复数据
select distinct 字段名,... from 表名;
--select distinct title from s_emp;
注意:
①distinct只能在select语句中使用
②distinct必须在所有字段名前面
③如果distinct对多个字段排重,当所有字段的内容完全一样时,才会被排重
二、算术运算符 + - * / %
select语句中,可以对表中的数据直接进行算术运算,并且把运算结果查询出来
select 字段名 +-*% ... from 表名;
--select (salary+200)*12,salary from s_emp;
注意:如果想要改变运算符优先级可以使用小括号
三、where子句
select 字段 from 表名 where 子句;
在where子句中可以使用关系运算符、逻辑运算符,当子句的条件为真的数据才会显示对应的字段数据
where 子句可以有:
1、关系运算符 = != > < >= <=
注意:因为在SQL中无需、也不能定义变量,因此=运算符只能用来判断关系是否相等
2、逻辑运算符
and or not
&& || ! 还可以继续使用
注意:在SQL中 not(!) 比关系运算符的优先级要低
3、 特殊条件
is null\is not null:
select * from 表名 where 字段 is null;
select * from 表名 where 字段 is not null;
对于null状态,只能通过 is null、is not null 来判断该字段是否为空状态,在SQL中null不是一个特定的值,而是一种状态,因此不能直接使用关系运算符比较
between a and b
select * from 表名 where 字段 between a and b;
当字段的值出现在[a,b]之间时为真,如果使用关系、逻辑配合也可以达到同样效果
in(a,b,c,...)
select * from 表名 where 字段 in(a,b,c,...);
当字段的值出现在in后面的数值列表中,为真
like '%str'
-- 查询名字以M开头的员工姓名
select first_name
from s_emp
where first_name like "M%";
进行模糊查询,%可以通配任意多个字符 -可以通配任意1个字符
注意:一般字符型字段比较适合模糊查询,但是数值型也可以,数据库会自动转换成字符串比较
4、子查询
把一条select语句的查询结果作为另一条select语句where子句的数据源
min(字段) 查询出该字段最小值
注意:min组函数不能直接用在where子句中
-- 找出比最低工资高的员工姓名和工资
select first_name,salary
from s_emp
where salary > (select min(salary) from s_emp);
四、排序
select 字段 from 表名 order by 排序字段 [asc/desc];
-- asc 升序 默认
-- desc 降序
-- MySQL排序中把null当做最小值,Oracle中当做最大值
与where子句配合:
select 字段
from 表名
where 条件
order by 排序字段 [asc/desc];
五、多表查询(连接查询)
1、交叉连接查询
当需要的数据分布在不同的表中,就需要把多张表进行多表查询,如果无任何限制条件地进行多表查询,会产生"笛卡尔积"有海量的无效数据,需要配合where子句进行多表查询,也称为交叉连接查询
select 表1.字段,表2.字段 from 表1,表2 where 表1.字段=表2.字段
select first_name,name
from s_emp,s_dept
where dept_id = s_dept.id;
注意:多表中的字段名可能重名, 需要 表名.字段名 进行区分
注意:交叉连接查询是先产生"笛卡尔积",然后再从海量的结果中根据where条件筛选出合适的结果,如果"笛卡尔积"非常大时,交叉连接查询的效率就很低
根据连接方式和where子句,进行不同的连接查询:
2、内连接
主要通过设置连接条件的方式,来移除查询结果中无效的数据行的交叉连接结果,就是消除了无效的"笛卡尔积",也称为"等值连接"
select 字段 from 表1 inner join 表2 on 连接条件;
3、外连接
①左连接:left join
Ⅰ以表1为主表,表2为副表,会把表1的数据都查询出来,表2只查询符合连接条件的数据
Ⅱ把表1的数据只显示不符合连接条件的数据
②右连接:right join
类似左连接
③全连接:full outer join 不常用
注意:MySQL中不支持 full outer join,但是可以用 union 替代 全连接的效果
(左连接1) union (右连接1) = 全连接1
(左连接2) union (右连接2) = 全连接2
-- 查询出每个部门的部门id、部门名、所属地区名(不能产生笛卡尔积)
select s_dept.id,s_dept.name,s_region.name
from s_dept inner join s_region
on s_dept.region_id = s_region.id;
六、取别名
①一次查询一张表无法查询出结果,有时需要同一张表查询多次,进行自连接查询
②自连接的表名是相同的,所以需要取别名进行区分,或者表名太长也可以通过取别名来简化表名
③如果两张表的字段数据有重复,需要通过排重筛选,以及需要通过内连接解决"笛卡尔积"问题
-- 查询出所有领导的名字和id
select distinct s2.id,s2.first_name
from s_emp as s1 inner join s_emp as s2
on s1.manager_id = s2.id;
-- 方法2:通过子查询,不会产生"笛卡尔积",也不会重复
select id,first_name
from s_emp
where id in(select manager_id from s_emp);
七、分组查询
把表中的数据按照分组标准划分成不同的组
select 分组标准字段或组函数处理过的字段
from 表名
group by 字段;
-- group_concat(字段a) 把每个分组中的字段a的所有值显示
-- 练习:分组查询出相同部门的员工姓名、部门id
select dept_id,group_concat(first_name)
from s_emp
group by dept_id;
注意:在分组查询语句中,select后面的字段要么是分组标准字段、要么是经过合适的组函数处理后的字段
常用的组函数 | 作用 |
---|---|
min(字段) | 求该字段分组后的最小值 |
max(字段) | 求该字段分组后的的最大值 |
count(字段) | 求该字段分组后数据的数量 |
sum(字段) | 求该字段分组后数据的和 |
avg(字段) | 求该字段分组后数据的平均值 |
group_concat(字段) | 把字段分组后每个组中的所有值显示 |
注意:组函数不能出现在where条件中
如何有条件地过滤分组后的数据?
select 分组标准字段或组函数处理过的字段
from 表名
group by 字段
having 过滤条件;
-- 查询出平均工资高于1100的部门id以及平均薪资
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) >= 1100;
-- 改为where 报错
注意:having支持where子句的所有语法和操作符
where和having的差异:
1、一般情况下,where用于过滤分组前数据行,having 用于过滤分组后的数组
2、where子句中不能使用组函数,而having可以
八、复杂的select语句的顺序要求
select [distinct] 字段、分组标准字段、组函数
from 表名 [连接方式] [表名] [as 别名]
where/on 条件/连接标准
group by 分组标准字段
having 分组过滤条件[组函数]
order by 排序字段 [desc/asc];
-- 练习:查询各个部门工资高于1000的员工的部门的平均薪资,只降序显示平均薪资高于1100的部门id、部门员工名、平均薪资
select dept_id,group_concat(first_name),avg(salary)
from s_emp
where salary > 1000
group by dept_id
having avg(salary) > 1100
order by avg(salary) desc;