MySQL【七】单表查询

MySQL单表查询

 

where条件

比较运算符

> < >= <= <> !=

select 字段 from 表 where 条件
    select * from 表名 where 字段>范围;  

between

between a and b  # 查询a,b之间的所有内容(双闭合)
    select * from 表名 where 字段  between a and b;

in

in(a,b,c) # 查询值为a或者b或者c的所有内容
    select * from 表名 where 字段 in(a,b);

like

select * from 表名 where 字段 like '匹配内容%'；
            
%是一个通配符,表示任意长度的任意內容
    select *  from 表名 where 字段 like '程%';
_也是一个通配符,表示一个长度的任意内容
    select * from 表名 where 字段 like '程咬_'

逻辑运算符 and or not

select * from 表名 where 字段 and 字段
    select * from employee where age=18 and salary<10000;

select * from 表名 where 字段 or 字段
    select * from employee where age=18 or salary<10000;

select * from 表名 where 字段 not in 字段
    select * from employee where post not in ('teacher');

身份运算符 is null/ is not null

select * from 表名 where 字段 is null; 查询这个字段里所有为空的值
    select * from employee where post_comment is null;

select * from 表名 where 字段 is not null; 查询这个字段中所有不为空的值
    select * from employee where post is not null;

正则匹配

所有人的身份证号,匹配所有身份证号是15位的居民  ^d{15}$
    select 字段 from 表 where age regex '^d{15}$';

group系列

group by分组

select * from 表名 group by 字段;
    select * from employee group by post;

group_concat 查看组内的名字

select group_concat(emp_name) from employee group by sex;

group_count  计数

select sex,count(emp_name) from employee group by sex;

having  过滤

对分组进行条件过滤 总是和group by 连用,where中不能出现聚合函数,所以和聚合函数有关的条件筛选也只能用having
总是对分组之后的结果进行一个条件筛选的时候用having

查各个岗位的员工个数
select post,count(id) from employee group by post having count(id) <2

查询各岗位平均薪资大于10000的岗位名、平均工资
select  post,avg(salary) from employee group by post having avg(salary) > 10000;
先将岗位分组然后分别对每个岗位的薪资求平均值，然后在筛选平均薪资大于10000的

order by 排序

# 默认从小到大排序  升序
    select * from employee  order by age;
# 从大到小排序 desc 降序
    select * from employee  order by age desc;

聚合函数

先from找到表
再用where的条件约束去表中取出记录
然后进行分组group by，没有分组则默认一组
然后进行聚合
最后select出结果

示例：
    select count(*) from employee;
    select count(*) from employee where depart_id=1;
    select max(salary) from employee;
    select min(salary) from employee;
    select avg(salary) from employee;
    select sum(salary) from  employee;
    select sum(salary) form employee WHERE depart_id=3;

limit

limit 取前n个或者web开发中做分页功能

# 显示前n条 limit n
# 从第m+1条开始,显示n条   limit m,n
        select * from employee  order by age limit 1,6;
# 从第m+1条开始,显示n条 limit n offset m
        select * from employee  order by age limit 6 offset 10;

=========limit：限制打印几条=========
1.select * from employee limit 3;#打印前三条
2.像这样表示的：指的是从哪开始，往后取几条 (这样的操作一般用来分页)
select * from employee limit 0,3;
select * from employee limit 3,4;
select * from employee limit 6,3;
select * from employee limit 9,3;
3.select * from employee order by id desc limit 3; #查看后三条

练习：查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列；

select post,avg(salary) as avg_salary from employee group by post having avg(salary)>10000 order by avg_salary

sql的解析顺序

select distinct 字段 from 表 where 条件 group by 分组 having 过滤条件 order by 排序 limit n;

关键字的执行优先级

from
where
group by
having
select
distinct
order by
limit