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

各个子句的执行顺序

了解mysql的查询语句的执行顺序，会对编写sql语句有一定的帮助。

from子句:基于表进行查询操作
where子句:进行条件筛选或者条件过滤
group by子句:对剩下的数据进行分组查询。
having子句：分组后，再次条件筛选或过滤
select子句：目的是选择业务需求的字段进行显示
order by子句：对选择后的字段进行排序
limit子句:进行分页查询，或者是查询前n条记录

where子句

where关键字后，用于书写筛选的条件。条件可以是：

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子句后，再次对数据进行筛选和过滤，筛选的条件与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], ...])

window_function有如下分类：

能类型	函数名	应用场景
聚合类	sum, avg, count、max、min	对窗口内的数据进行聚合计算
排名类	row_number, rank, dense_rank	对数据进行排序并生成排名
分布类	percent_rank, cume_dist	计算分布情况
偏移类	lag, lead	获取上下行数据

over函数解析

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

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

排名函数

2）row_number()
给排序过的表记录分配行号，从1开始的连续自然数
3）rank()
给排序过的表记录分配名次。相同的值名次一样，后续的排名出现跳跃情况
4）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常用语句与函数大全及举例的文章就介绍到这了,更多相关mysql常用语句内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

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

2026年01月08日 • Mysql •我要评论