通过举例一文入门mysql查询基础
作者:YXN-sql 阅读量:68 发布日期:2024-05-24
单表查询基础
注意:非关键字的字段或表明可以不用返单引号`id`包裹
为了方便理解,下面有部分字段和表名直接采用中文形式了。
这里只是举例常用的关键点带领入门,弥补一下没有入门级基础教程的缺点,很多函数和用例不会讲太多。
1、select 查询字段、字段重命名、计算字段
SELECT * FROM `table`; -- 查询所有字段
SELECT `id`,`title` FROM `table`; -- 查询指定字段
SELECT 20; -- 查询常量
--字段重命名,使用as关键字,也可以去掉,用空格隔开
SELECT id as aa FROM table
-- 计算字段
select 分类名,单价*数量 as 销售额 from 商品表1
2、distinct 去重
SELECT DISTINCT `writer` FROM `table`; -- 根据作者去重
SELECT DISTINCT `writer`,`date` FROM `table`; -- 根据作者和日期两列去重
3、where条件语句
-- 查询id为1的行
SELECT * FROM `table` WHERE `id` = 1;
4、运算符
举例一部分
-- 比较运算符(> < = <= >= != <>)
-- > 查询单价大于1800的数据
select 分类名,单价 from 商品表1 where 单价>1800
-- <> != 查询分类名不是电冰箱的数据
select * from 商品表1 where 分类名!='电冰箱'
-- 逻辑运算符(举例几个 and not or)
-- 查询单价在500以上1500以下的商品
select * from 商品表1 where 单价>=500 and 单价<=1500
-- not 查询分类名不是电冰箱的数据
select * from 商品表1 where not 分类名='电冰箱'
-- or 查询分类名是电冰箱或者是电视机的产品
select * from 商品表1 where 分类名='电冰箱' or 分类名='电视机'
5、in 关键字的查询
-- 查询id在3,5,8里面的行
SELECT * FROM `table` WHERE `id` in (3,5,8);
6、between and 的范围查询
-- between and 查询单价在500以上1500以下的商品
select * from 商品表1 where 单价 between 500 and 1500
7、like 字符匹配查询
-- 查询 标题中包含sql的数据
SELECT * FROM `table` WHERE title LIKE '%sql%';
8、group by 分组统计
-- 各部门的平均工资
select avg(目前薪资) from 员工 group by 部门;
-- 统计员工薪资在5000-10000之间数据,以各部门分组,求出每个部门最高薪资和人数
select 部门, max(目前薪资) 最高薪资,count(*) 人数
from 员工
where 目前薪资 BETWEEN 5000 and 10000
group by 部门
9、having 分组过滤
-- 过滤出人数大于3个人以上的性别
select 性别,count(*) 人数
from 学生
group by 性别
having 人数>3
10、limit 查询指定数量数据
-- 查询 5条数据
SELECT * FROM `table` LIMIT 5;
-- 查询 从第3条开始的5条数据
SELECT * FROM `table` LIMIT 3,5;
查询进阶篇(进阶一点点)
1、case when表达式
-- 查询员工信息,将数字型性别转为男女描述
-- 简单函数
select 员工编号,
case 性别
when -1 then '女'
when 0 then '男'
else '不详'
end as 性别
from 员工
-- 搜索模式
select 员工编号,
case
when 性别 = -1 then '女'
when 性别 = 0 then '男'
else '不详'
end
from 员工
-- 对员工表中员工的年龄做出分类
select 姓名,出生日期,year(now())-year(出生日期) as 年龄,
case
when year(now())-year(出生日期) BETWEEN 0 and 18 then '少年'
when year(now())-year(出生日期) BETWEEN 19 and 30 then '青年'
when year(now())-year(出生日期) BETWEEN 31 and 50 then '中年'
else '老年'
end as 年龄分类
from 员工
2、if 语句
相关案例:mysql_case_if_ifnull
select 部门,if(性别=-1,'女','男') as 性别 from 员工
3、子查询
-- 单价比洗衣机最高单价还高的商品
select *
from 商品表1
where 单价 > (
select max(单价)
from 商品表1
where 分类名='洗衣机'
)
4、连表查询
1、内连接 inner join:取两表交集部分数据
2、外连接
- 左连接 left join:左表所有数据,以及两表交集数据
- 右链接 right join:右表所有数据,以及两表交集数据
3、自连接:也就是一个表连接自己(必须使用表别名)
写sql的时候 inner 也可以忽略不写
-- 内连接
select *
from dept join emp
on dept.id=emp.dept_id;
-- 笛卡尔积
select * from dept,emp
5、sql合并查询
union和union all两者区别在于是否保留重复值
- union :排除重复值
- union all:保留重复值
上面运算符的作用都是将两个集合并成一个集合,因此需要满足以下条件:
- 两边的集合中字段的数量和顺序必须相同;
- 两边的集合中对应字段的类型必须匹配或兼容。
select * from test1
union
select * from test2
6、函数的使用
更多函数参考:mysql常用函数总结
select trim(' 李 明 ') ==> '李 明'
select concat('aa','bb','cc') ==> aabbcc
select ifnull(1,'空') ==> 1
-- 会四舍五入操作,会使用千位符分割数据
select format(101.12545,2) ==> 101.13
select format(10000.14689,2) ==> 10,000.15
7、窗口函数
MySQL中的窗口函数是从mysql8.0版本以后出现的一个新特性,窗口的概念非常重要,它可以理解为记录的集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数,对于每一条记录都要在此窗口内执行函数,有的函数随着记录的不同,窗口的大小都是固定,这种属于静态窗口,但是有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫做滑动窗口。
窗口函数可以当成group by简单去理解,但是窗口函数又与普通的聚合函数不同,它不会对结果进行分组。它能使得输出中的行数与输入中的行数相同。
静态窗口语法
SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table
- 聚合功能:在上述例子中,我们用了SUM(),但是你也可以用COUNT(), AVG()之类的计算功能
- PARTITION BY:你只需将它看成GROUP BY子句,但是在窗口函数中,你要写PARTITION BY
- ORDER BY:ORDER BY和普通查询语句中的ORDER BY没什么不同。注意,输出的顺序要仔细考虑
滑动窗口语法
select 函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)
窗口函数的解读:一般情况下窗口函数分为两个部分
- 第一个部分是窗口函数名(可以是聚合函数也可以是窗口函数),在MySQL中已知的窗口函数有11个+聚合函数(所有的聚合函数都可以使用在窗口中),根据函数的性质,有的需要些参数,有的不需要些参数。
- 第二个部门是over窗口的部分,over()是必须要写的,代表的是整个窗口的意思,里面有三个参数,都是非必须要写的,根据要求来定。
- partition by [分组字段],将数据根据此字段分成多份,如果不加partition by [分组字段]参数的话,那么会把整个数据当作一个窗口。你只需将它看成GROUP BY子句,但是在窗口函数中,你要写PARTITION BY。
- order by [排序字段] asc/desc,代表每个窗口要不要进行排序操作,要就是用该参数。
- asc/desc rows/range between 起始位置 and 结束位置,这个参数仅针对滑动窗口有效,是在当前窗口下分出更小的子窗口。其中要指名其实位置和结束位置即可!
- current row:边界时当前行
- unbounded preceding:边界是分区中的第一行
- unbounded following:边界时分区中最后一行
- expr preceding:边界时当前行减去expr的值
- expr following:边界时机当前行加上expr的值
- rows是基于行数,range是基于值的大小
这里不在阐述过多了,可以直接从此了解:mysql_窗口函数
但是里面只提供资料,为了方便理解在此举例讲解:
执行过程
正常没有窗口函数的情况下就是一条直线,当碰到窗口函数的时候会复制一份having后的数据,然后通过再进行窗口函数处理后匹配到表头里,再返回通过order by -> limit -> ...
举例:
静态窗口
比如存在数据表student如下图:
我们先来写一个简单窗口函数:
比如:我们需要将男女生分别按年龄排序并且编号,我们可以用窗口函数如下代码:
SELECT
*,
row_number() over ( PARTITION BY s_sex ORDER BY s_birth ) AS 年龄排序
FROM
student
代码表示根据 s_sex 分组,s_birth 排序,然后使用 row_number() 进行编号,查询后输出如下图所示:
我们用红色框表示 partition by 窗口,按每种性别作为一个窗口,然后通过绿色框的年份排序,再通过黄色框使用窗口函数对同一个窗口内的元素进行编号,最终达到我们的目的。
滑动窗口
接下来我们来看看滑动窗口的案例,还是这张表,
比如:我们需要将男女生分别按年龄排序,并且计算每个人与其相同性别,且年龄刚好大于自己的人的id的和(字段和结果有无意义不重要,只是举例),也就是计算滑动窗口内两个人的id和。
我们先来看sql:
SELECT
*,
row_number() over ( PARTITION BY s_sex ORDER BY s_birth ) AS 年龄排序 ,
sum(s_id) over(partition by s_sex order by s_birth rows between 1 preceding and current row) as 和前一位的s_id和
FROM
student
其中 rows between 1 preceding and current row 表示的是把前一行到当前行作为滑动窗口,不懂得上面链接窗口函数中有。
运行结果如图:
如上图,我们分组排序后,窗口是根据左侧 s_id 的框所示,把每行的当前行和前一行作为一个滑动窗口,我们通过聚合函数 sum 求的值就是对每个滑动窗口的 id 聚合求和,由于第一行没有前一行,所以就只有自身的 7 作为和。
YXN-sql
2024-05-24