mysql常用语句与函数大全及举例

来源：互联网 2026-03-25 19:11:05

目录各个子句的执行顺序where子句group by子句having子句distinct关键字order by子句limit关键字常用函数数值函数日期函数其他函数单条件分支函数：if多条件分支函数：case窗口函数窗口函数介绍over函数解析排名函数关联查询关联分类交叉连接union [all]操作符子查询列题各个子句的执行顺序了解mysql的查询语句的执行顺序，会对编写sql语句有一定的帮助。from子句:基于表进行查询操作where子句:进行条件筛选或者条件过滤group by子句:对剩下的数据进行分

各个子句的执行顺序

要想写出高效、准确的SQL语句，理解MySQL查询语句的执行顺序是个不错的切入点。这就好比做菜，先放什么后放什么，顺序对了味道才对。

from子句: 这是查询的起点，确定要从哪张表获取数据
where子句: 对原始数据进行第一轮筛选，过滤掉不需要的记录
group by子句: 对筛选后的数据进行分组，为后续的聚合计算做准备
having子句：分组完成后，再进行第二轮的条件筛选
select子句：此时才决定最终要显示哪些字段
order by子句：对结果集进行排序
limit子句: 最后进行分页或者限制返回的记录数

where子句

where关键字后面跟着的就是筛选条件，可以说是SQL中最常用的过滤工具。常见的条件类型包括：

1、比较运算： = < <= = != <>

2、逻辑连接： AND（同时满足） OR（满足其一）

3、区间判断： BETWEEN … AND … 相当于 ≥ 且 ≤；NOT BETWEEN … AND … 相当于 < 或 >

4、集合匹配：IN (…) 匹配列表内任一值；NOT IN (…) 排除列表内所有值

ALL (…) 大于集合中最大值；ANY (…) 大于集合中最小值

（MySQL中ALL/ANY通常只在子查询中使用）

5、模糊匹配： LIKE '_abc%' _ 代表任意1个字符，% 代表任意0个或多个字符

6、空值判断： IS [NOT] NULL

group by子句

分组查询离不开聚合函数，常用的有：

count(column name|常量|*) - 返回每组中的总记录数
sum (column namel常量) - 返回每组中指定字段的值的总和
avg (column name) - 返回每组中指定字段的值的平均值
max (column name) - 返回每组中指定字段的值的最大值
min (column name) - 返回每组中指定字段的值的最小值

having子句

group by分组后，如果还需要对分组结果进行筛选，having子句就派上用场了。它的条件写法与where基本相同，但作用时机不同。

distinct关键字

置于列名前。
- 当select子句中有多个字段时，distinct必须位于第一个字段前面，表示对这些字段值的组合进行去重
- 也可以在函数中指定字段的第一个字段前面使用

order by子句

这个子句在select之后执行，专门用来对查询结果进行排序。

order by colName [asc|desc] [,colName [asc|desc]]...

asc：表示升序排序，是默认值，可以省略

desc：表示降序排序

支持多字段排序，当前一个字段的值相同时，后一个字段的排序规则才会生效

limit关键字

当查询结果数据量过大时，limit关键字就能很好地控制返回的记录数，常用于分页查询。

limit [off,] size;

size：要查询的记录数量

off参数：从第几条记录开始查询，记录索引从0开始。如果省略该参数，默认从第一条开始查询size条

常用函数

数值函数

函数	函数说明
pow(x,y)/power(x,y)	返回x的y次幂
sqrt(n)	返回非负数n的平方根
pi()	返回圆周率
rand()、rand(n)	返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
truncate(n,d)	保留数字n的d位小数并返回
least(x,y,...)、greatest(x,y,...)	求最小值或最大值
mod(n,m)	取模运算,返回n被m除的余数
ceil(n)、ceiling(n)、floor(n)	向上/向下取整函数
round(n[,d])	返回n的四舍五入值,保留d位小数(d的默认值为0)

日期函数

函数	函数说明
curdate()\curtime()\now()\sysdate()\current_timestamp()	获取系统时间
dayofweek(date) \weekday(date) \dayname(date)	获取星期几
dayofmonth(date) \dayofyear(date) \monthname(date)	获取第几天
year(date)\month(date)\day(date) \ hour(date) \minute(date) \second(date)	获取时间分量
date_format(date,format) （%Y年 %m月 %d日 %h时 %i分 %s秒 %p上下午 %W星期）	日期格式化，根据format字符串格式化date的值
date_add(date,interval value unit) \date_sub(date,interval value unit)	日期运算
adddate(date,interval value unit) \subdate(date,interval value unit)	日期运算

其他函数

单条件分支函数：if

语法：if(express,value1,value2):

解析：如果express表达式成立，就返回value1,否则返回value2。

多条件分支函数：case

写法1
case  column_name     
    when value1 then returnValue1      
    when value2 then returnValue2     
    ...    
    else returnValueN 
end;

写法2
case  
    when condition1 then returnValue1  
    when condition2 then returnValue2 
    ...
    else returnValueN 
end;

窗口函数

窗口函数介绍

窗口函数，也被称为分析函数，是MySQL 8.0引入的一项强大功能。它能够在查询结果集中对数据进行分组、排序和计算，而无需使用临时表或自连接。窗口函数的语法结构如下：

window_function(expr) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
)

窗口函数主要分为以下几类：

功能类型	函数名	应用场景
聚合类	SUM, AVG, COUNT、max、min	对窗口内的数据进行聚合计算
排名类	ROW_NUMBER, RANK, DENSE_RANK	对数据进行排序并生成排名
分布类	PERCENT_RANK, CUME_DIST	计算分布情况
偏移类	LAG, LEAD	获取上下行数据

over函数解析

OVER()函数是窗口函数的核心部分，用于在查询中执行基于一组行的计算，同时保留这些行的原始记录。这与传统的聚合函数不同，后者通常会将多行合并为一行输出。

PARTITION BY：将数据划分为多个逻辑分区（类似GROUP BY），每个分区独立计算
ORDER BY：定义窗口内行的排序方式，影响如累计、排名等计算顺序

排名函数

row_number() - 给排序过的表记录分配行号，从1开始的连续自然数
rank() - 给排序过的表记录分配名次。相同的值名次一样，后续的排名出现跳跃情况
dense_rank() - 给排序过的表记录分配名次。相同的值名次一样，后续的排名不出现跳跃情况

关联查询

关联分类

分类	语法	解析
内连接	table_name [inner] join table_name on condition	返回满足条件的记录组合
左外连接	table_name left [outer] join table_name on condition	左表为主表，除了返回满足条件的记录组合外，左表中剩余记录也返回，右表字段以null形式占位
右外连接	table_name right [outer] join table_name on condition	右表为主表，除了返回满足条件的记录组合外，右表中剩余记录也返回，左表字段以null形式占位

交叉连接

在使用join连接或者逗号连接查询时，如果没有使用on或where关键字指定关联条件，就会出现交叉连接。

这种连接会产生两张表记录数的乘积条记录，也就是所谓的笛卡尔积。实际开发中要尽量避免这种情况，除非确实需要所有可能的组合。

union [all]操作符

当需要将两个查询的结果集合并时，union [all]操作符就派上用场了。

select  column_name,column_name,.... from table_name 
union [all]
select  column_name,column_name,.... from table_name

union all：保留所有记录，包括重复部分
union：自动去除重复记录

子查询

有时候，一个查询语句A需要的数据并不是直接来自表，而是另一个查询语句B的结果。这种情况下，查询语句A就是主查询，查询语句B就是子查询，这种结构被称为高级关联查询或子查询。

子查询可以出现在where、from、having、select这四种子句中，为复杂的数据处理提供了灵活的解决方案。

列题

--建表--
--学生表
CREATE TABLE `Student`(
        `s_id` VARCHAR(20),
        `s_name` VARCHAR(20) NOT NULL DEFAULT '',
        `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
        `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
        PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
        `c_id`  VARCHAR(20),
        `c_name` VARCHAR(20) NOT NULL DEFAULT '',
        `t_id` VARCHAR(20) NOT NULL,
        PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
        `t_id` VARCHAR(20),
        `t_name` VARCHAR(20) NOT NULL DEFAULT '',
        PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
        `s_id` VARCHAR(20),
        `c_id`  VARCHAR(20),
        `s_score` INT(3),
        PRIMARY KEY(`s_id`,`c_id`)
);

--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
insert into Course values('04' , '体育' , '01');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数   
select st.s_id , st.s_name , s1.s_score a1 ,  s2.s_score a2
from student st 
LEFT join score s1 on st.s_id = s1.s_id AND s1.c_id = '01'   
LEFT JOIN score s2 on st.s_id = s2.s_id AND s2.c_id = '02'
where s1.s_score IS NOT NULL AND
s1.s_score > COALESCE(s2.s_score, 0);

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select st.s_id , st.s_name , s1.s_score a1,  s2.s_score a2
from student st 
LEFT join score s1 on st.s_id = s1.s_id AND s1.c_id = '01'
LEFT JOIN score s2 on st.s_id = s2.s_id AND s2.c_id = '02'
where COALESCE(s1.s_score, 0)  < COALESCE(s2.s_score, 0)

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select st.s_id , st.s_name , avg(sc.s_score)AS avg_score
from student st
JOIN score sc on st.s_id = sc.s_id
group by st.s_id, st.s_name
HAVING AVG(sc.s_score) >= 60;

SELECT 
    st.s_id, 
    st.s_name, 
    AVG(sc.s_score) AS avg_score
FROM 
    Student st
JOIN 
    Score sc ON st.s_id = sc.s_id
GROUP BY 
    st.s_id, st.s_name
HAVING 
    AVG(sc.s_score) >= 60;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩                 
(包括有成绩的和无成绩的)
SELECT 
    st.s_id, 
    st.s_name, 
    AVG(sc.s_score) 平均成绩
FROM 
    Student st
LEFT JOIN 
    Score sc ON st.s_id = sc.s_id
GROUP BY 
    st.s_id, st.s_name 
HAVING 
    AVG(sc.s_score) < 60 or AVG(sc.s_score) is NULL ;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT 
    st.s_id,
    st.s_name,
    count(sc.c_id),
    SUM(sc.s_score)
FROM student st 
LEFT JOIN score sc on st.s_id=sc.s_id
GROUP BY st.s_id;

6、查询"李"姓老师的数量 
SELECT
    count(0)
from
    teacher
WHERE
    t_name LIKE '李%';

7、查询学过"张三"老师授课的同学的信息 
SELECT
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex
FROM
    student st
JOIN score  sc on st.s_id=sc.s_id
JOIN course c on sc.c_id=c.c_id
JOIN teacher t on c.t_id=t.t_id
WHERE
    t.t_name='张三'
GROUP BY st.s_id;

8、查询没学过"张三"老师授课的同学的信息 
SELECT *
FROM student 
WHERE s_id not in (
    SELECT sc.s_id
    FROM score  sc 
    JOIN course c on sc.c_id=c.c_id
    JOIN teacher t on c.t_id=t.t_id
    WHERE t.t_name ='张三' 
    GROUP BY sc.s_id 
);

SELECT *
FROM student 
WHERE s_id not in (
    SELECT st.s_id
    FROM student st
    JOIN score  sc on st.s_id=sc.s_id
    JOIN course c on sc.c_id=c.c_id
    JOIN teacher t on c.t_id=t.t_id
    WHERE
        t.t_name='张三'
    GROUP BY st.s_id 
);

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
    st.s_id,
    st.s_name,
    st.s_birth,
    st.s_sex
FROM
    student st
JOIN score s1 on st.s_id=s1.s_id and s1.c_id='01' 
JOIN score s2 on st.s_id=s2.s_id and s2.c_id='02' 

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT st.* 
FROM
    student st
JOIN score s1 on st.s_id=s1.s_id and s1.c_id='01' 
WHERE st.s_id NOT IN (
    SELECT
        st.s_id
    FROM
        student st
    JOIN score s2 on st.s_id=s2.s_id and s2.c_id='02'
);

11、查询没有学全所有课程的同学的信息
SELECT st.* 
FROM
    student st 
WHERE st.s_id NOT IN (
    SELECT st.s_id 
    FROM
        student st 
    JOIN score s1 on st.s_id=s1.s_id and s1.c_id='01' 
    WHERE st.s_id IN (
        SELECT st.s_id 
        FROM student st
        JOIN score s2 on st.s_id=s2.s_id and s2.c_id='02'
    )
    AND st.s_id  in (
        SELECT st.s_id 
        FROM student st
        JOIN score s3 on st.s_id=s3.s_id and s3.c_id='03'
    )
)

SELECT st.* 
FROM student st 
LEFT JOIN score sc on st.s_id=sc.s_id
GROUP BY st.s_id
HAVING COUNT(1)<3;

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
SELECT *
FROM student st
JOIN score s2 on st.s_id=s2.s_id
WHERE st.s_id <> '01' 
AND s2.c_id in(
    SELECT s.c_id
    FROM score s 
    WHERE s.s_id='01'
) 
GROUP BY st.s_id;

SELECT s.c_id
FROM score s 
WHERE s.s_id='01';

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
SELECT st.*
FROM Student st
JOIN (
    SELECT s_id,
           COUNT(*)AS cnt,                -- 该生修的课程门数
           GROUP_CONCAT(c_id ORDER BY c_id) AS courses -- 课程按字典序拼成串
    FROM Score
    GROUP BY s_id 
) t ON st.s_id = t.s_id
WHERE st.s_id <> '01'                         -- 排除 01 自己  
  AND t.cnt = (SELECT COUNT(*) FROM Score WHERE s_id = '01')        -- 门数相同  
  AND t.courses = (SELECT GROUP_CONCAT(c_id ORDER BY c_id) 
                   FROM Score WHERE s_id = '01');                   -- 课程串相同

14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
SELECT t.s_name
FROM student t
WHERE t.s_id not in(
    SELECT s.s_id
    FROM score s
    WHERE s.c_id in(
        SELECT c_id 
        FROM teacher t 
        JOIN course c on t.t_id=c.t_id
        WHERE t.t_name='张三'
    )
);

SELECT s.s_id
FROM score s
WHERE s.c_id in(
    SELECT c_id 
    FROM teacher t 
    JOIN course c on t.t_id=c.t_id
    WHERE t.t_name='张三'
);

SELECT c_id 
FROM teacher t 
JOIN course c on t.t_id=c.t_id
WHERE t.t_name='张三';

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT t.s_id ,t.s_name,avg(s.s_score)
FROM student t
join score s on t.s_id=s.s_id
WHERE s.s_score<60
GROUP BY s.s_id
HAVING  count(0)>=2;

SELECT s.s_id
FROM score s
WHERE s.s_score<60
GROUP BY s.s_id
HAVING  count(0)>=2;

16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT t.*,s.s_score
FROM student t
join score s on t.s_id=s.s_id and c_id='01'
WHERE s.s_score<60
order by s.s_score DESC;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
    st.s_id,
    st.s_name,
    MAX(CASE WHEN sc.c_id = '01' THEN sc.s_score END) AS 语文,
    MAX(CASE WHEN sc.c_id = '02' THEN sc.s_score END) AS 数学,
    MAX(CASE WHEN sc.c_id = '03' THEN sc.s_score END) AS 英语,
    ROUND(AVG(sc.s_score), 2) AS avg_score
FROM Student st
JOIN Score sc ON st.s_id = sc.s_id
GROUP BY st.s_id, st.s_name
ORDER BY avg_score DESC;

这篇关于MySQL常用语句与函数大全的文章到这里就告一段落了。希望这些实用的SQL技巧能为你的数据库操作带来便利。如果想深入学习MySQL，建议多动手实践，毕竟熟能生巧。

侠游戏发布此文仅为了传递信息，不代表侠游戏网站认同其观点或证实其描述