您现在的位置是：网站首页 > 博客日记 >

通过举例一文入门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表达式

相关案例：mysql进阶篇之行列转置|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：保留重复值

上面运算符的作用都是将两个集合并成一个集合，因此需要满足以下条件： 

  1. 两边的集合中字段的数量和顺序必须相同； 
  2. 两边的集合中对应字段的类型必须匹配或兼容。
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

  1. 聚合功能：在上述例子中，我们用了SUM()，但是你也可以用COUNT(), AVG()之类的计算功能
  2. PARTITION BY：你只需将它看成GROUP BY子句，但是在窗口函数中，你要写PARTITION BY
  3. ORDER BY：ORDER BY和普通查询语句中的ORDER BY没什么不同。注意，输出的顺序要仔细考虑

滑动窗口语法

select 函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)

窗口函数的解读：一般情况下窗口函数分为两个部分

  1. 第一个部分是窗口函数名(可以是聚合函数也可以是窗口函数)，在MySQL中已知的窗口函数有11个+聚合函数（所有的聚合函数都可以使用在窗口中），根据函数的性质，有的需要些参数，有的不需要些参数。
  2. 第二个部门是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