快捷導(dǎo)航

mysql常用語(yǔ)句與函數(shù)大全及舉例

更新時(shí)間：2026年01月07日 11:23:27 作者：海馬

文章主要介紹了MySQL查詢語(yǔ)句的執(zhí)行順序,包括FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY和LIMIT等子句的使用方法,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

各個(gè)子句的執(zhí)行順序

了解mysql的查詢語(yǔ)句的執(zhí)行順序，會(huì)對(duì)編寫(xiě)sql語(yǔ)句有一定的幫助。

from子句:基于表進(jìn)行查詢操作
where子句:進(jìn)行條件篩選或者條件過(guò)濾
group by子句:對(duì)剩下的數(shù)據(jù)進(jìn)行分組查詢。
having子句：分組后，再次條件篩選或過(guò)濾
select子句：目的是選擇業(yè)務(wù)需求的字段進(jìn)行顯示
order by子句：對(duì)選擇后的字段進(jìn)行排序
limit子句:進(jìn)行分頁(yè)查詢，或者是查詢前n條記錄

where子句

where關(guān)鍵字后，用于書(shū)寫(xiě)篩選的條件。條件可以是：

1、比較運(yùn)算： = < <= = != <>

2、邏輯連接： AND（同時(shí)滿足） OR（滿足其一）

3、區(qū)間： BETWEEN … AND … 等價(jià)于 ≥ 且 ≤
NOT BETWEEN … AND … 等價(jià)于 < 或 >

4、集合匹配： IN (…) 在列表內(nèi)任一值
NOT IN (…) 不在列表內(nèi)所有值

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

（MySQL 中 ALL/ANY 僅子查詢可用）

5、模糊匹配： LIKE '_abc%' _ 任意 1 字符，% 任意 0+ 字符

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

group by子句

count (column name|常量|*) 返回每組中的總記錄數(shù)
sum (column namel常量) 返回每組中指定字段的值的總和
avg (column name) 返回每組中指定字段的值的平均值
max (column name) 返回每組中指定字段的值的最大值
min (column name) 返回每組中指定字段的值的最小值

having子句

使用了group by子句后，再次對(duì)數(shù)據(jù)進(jìn)行篩選和過(guò)濾，篩選的條件與where相同

distinct關(guān)鍵字

置于列名前。
- 當(dāng)select子句中有多個(gè)字段時(shí)，distinct必須要位于第一個(gè)字段的前面，表示這些字段的值的組合進(jìn)行去重。
- 也可以位于函數(shù)中指定字段的第一個(gè)字段前面

order by子句

它的執(zhí)行時(shí)機(jī)是在select子句后執(zhí)行，通常用于對(duì)查詢出來(lái)的數(shù)據(jù)進(jìn)行排序的。

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

asc：表示根據(jù)指定字段的值升序排序，是默認(rèn)值，可以省略不寫(xiě)。

desc：表示根據(jù)指定字段的值降序排序。

可以指定多個(gè)字段，分別進(jìn)行排序。當(dāng)前一個(gè)字段的值相同時(shí)，后一個(gè)字段的排序規(guī)則才會(huì)生效。

limit關(guān)鍵字

當(dāng)查詢出來(lái)的數(shù)據(jù)量過(guò)大，導(dǎo)致當(dāng)前屏幕不能一次性全部展示出來(lái)時(shí)，我們可以使用limit關(guān)鍵字進(jìn)行限制條數(shù)查詢。

limit [off,] size;

size：表示要查詢的數(shù)量

off參數(shù)：表示從第幾條記錄開(kāi)始查詢，記錄的索引從0開(kāi)始的。如果沒(méi)有該參數(shù)，表示從第一條開(kāi)始查詢size條。

常用函數(shù)

數(shù)值函數(shù)

函數(shù)	函數(shù)說(shuō)明
pow(x,y)/power(x,y)	返回x的y次冪
sqrt(n)	返回非負(fù)數(shù)n的平方根
pi()	返回圓周率
rand()、rand(n)	返回在范圍0到1.0內(nèi)的隨機(jī)浮點(diǎn)值(可以使用數(shù)字n作為初始值)
truncate(n,d)	保留數(shù)字n的d位小數(shù)并返回
least(x,y,...)、greatest(x,y,...)	求最小值或最大值
mod(n,m)	取模運(yùn)算,返回n被m除的余數(shù)
ceil(n)、ceiling(n)、floor(n)	向上/向下取整函數(shù)
round(n[,d])	返回n的四舍五入值,保留d位小數(shù)(d的默認(rèn)值為0)

日期函數(shù)

函數(shù)	函數(shù)說(shuō)明
curdate()\curtime()\now()\sysdate()\current_timestamp()	獲取系統(tǒng)時(shí)間
dayofweek(date) \weekday(date) \dayname(date)	獲取星期幾
dayofmonth(date) \dayofyear(date) \monthname(date)	獲取第幾天
year(date)\month(date)\day(date) \ hour(date) \minute(date) \second(date)	獲取時(shí)間分量
date_format(date,format) （%Y年 %m月 %d日 %h時(shí) %i分 %s秒 %p上下午 %W星期）	日期格式化，根據(jù)format字符串格式化date的值
date_add(date,interval value unit) \date_sub(date,interval value unit)	日期運(yùn)算
adddate(date,interval value unit) \subdate(date,interval value unit)	日期運(yùn)算

其他函數(shù)

單條件分支函數(shù)：if

語(yǔ)法：if(express,value1,value2):

解析：如果express表達(dá)式成立，就返回value1,否則返回value2.

多條件分支函數(shù)：case

寫(xiě)法1
case  column_name 
    when value1 then returnValue1  
    when value2 then returnValue2 
    ...
    else returnValueN end;
寫(xiě)法2
case  
when condition1 then returnValue1  
when condition2 then returnValue2 
...
else returnValueN end;

窗口函數(shù)

窗口函數(shù)介紹

窗口函數(shù)，也被稱為分析函數(shù)，是 MySQL 8.0 引入的一項(xiàng)強(qiáng)大功能，它能夠在查詢結(jié)果集中對(duì)數(shù)據(jù)進(jìn)行分組、排序和計(jì)算，而無(wú)需使用臨時(shí)表或自連接。窗口函數(shù)的語(yǔ)法結(jié)構(gòu)如下：

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

window_function有如下分類：

能類型	函數(shù)名	應(yīng)用場(chǎng)景
聚合類	SUM, AVG, COUNT、max、min	對(duì)窗口內(nèi)的數(shù)據(jù)進(jìn)行聚合計(jì)算
排名類	ROW_NUMBER, RANK, DENSE_RANK	對(duì)數(shù)據(jù)進(jìn)行排序并生成排名
分布類	PERCENT_RANK, CUME_DIST	計(jì)算分布情況
偏移類	LAG, LEAD	獲取上下行數(shù)據(jù)

over函數(shù)解析

OVER() 函數(shù)是窗口函數(shù)（Window Function）的一部分，用于在查詢中執(zhí)行基于一組的行計(jì)算，同時(shí)保留這些行的原始記錄。這與傳統(tǒng)的聚合函數(shù)（如 SUM()、AVG() 等）不同，后者通常會(huì)將多行合并為一行輸出。

PARTITION BY：將數(shù)據(jù)劃分為多個(gè)邏輯分區(qū)（類似GROUP BY）,每個(gè)分區(qū)獨(dú)立計(jì)算
ORDER BY：定義窗口內(nèi)行的排序方式，影響如累計(jì)、排名等計(jì)算順序

排名函數(shù)

2）row_number()
給排序過(guò)的表記錄分配行號(hào)，從1開(kāi)始的連續(xù)自然數(shù)
3）rank()
給排序過(guò)的表記錄分配名次。相同的值名次一樣，后續(xù)的排名出現(xiàn)跳躍情況
4）dense_rank()
給排序過(guò)的表記錄分配名次。相同的值名次一樣，后續(xù)的排名不出現(xiàn)跳躍情況

關(guān)聯(lián)查詢

關(guān)聯(lián)分類

分類	語(yǔ)法	解析
內(nèi)連接	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連接或者逗號(hào)連接查詢，但是沒(méi)有使用on或where關(guān)鍵字來(lái)指定關(guān)聯(lián)條件時(shí)，就會(huì)出現(xiàn)交叉連接

這種交叉連接，產(chǎn)生的記錄數(shù)為兩張表的記錄數(shù)的乘積，這種結(jié)果也被稱之為笛卡爾積。

union [all]操作符

如果我們想要將兩個(gè)查詢的結(jié)果集合并到一起，我們就可以使用union [all] 操作符。

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

union all：兩個(gè)子句中的重復(fù)部分，會(huì)保留，不去重。
union：會(huì)去掉并集中的重復(fù)記錄

子查詢

有的時(shí)候，當(dāng)一個(gè)查詢語(yǔ)句A所需要的數(shù)據(jù)，不是直觀在表中體現(xiàn)，而是另外一個(gè)查詢語(yǔ)句B查詢出來(lái)的結(jié)果，那么查詢語(yǔ)句A就是主查詢語(yǔ)句，查詢語(yǔ)句B就是子查詢語(yǔ)句。這種查詢我們稱之為高級(jí)關(guān)聯(lián)查詢，也叫做子查詢。

子查詢語(yǔ)句的位置可以在where、from、having、select這四種子句中。

列題

--建表
--學(xué)生表
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`)
);
--成績(jī)表
CREATE TABLE `Score`(
        `s_id` VARCHAR(20),
        `c_id`  VARCHAR(20),
        `s_score` INT(3),
        PRIMARY KEY(`s_id`,`c_id`)
);
--插入學(xué)生表測(cè)試數(shù)據(jù)
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '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' , '女');
--課程表測(cè)試數(shù)據(jù)
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');
insert into Course values('04' , '體育' , '01');
--教師表測(cè)試數(shù)據(jù)
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成績(jī)表測(cè)試數(shù)據(jù)
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"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)   
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"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)
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、查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
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、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
                 (包括有成績(jī)的和無(wú)成績(jī)的)
SELECT 
    st.s_id, 
    st.s_name, 
    AVG(sc.s_score) 平均成績(jī)
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、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)
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、查詢"李"姓老師的數(shù)量 
SELECT
		count(0)
from
		teacher
WHERE
		t_name LIKE '李%';
7、查詢學(xué)過(guò)"張三"老師授課的同學(xué)的信息 
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、查詢沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息 
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、查詢學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
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、查詢學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
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、查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息
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、查詢至少有一門課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息 
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"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息 
SELECT st.*
FROM Student st
JOIN (
    SELECT s_id,COUNT(*)AS cnt,                -- 該生修的課程門數(shù)
            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')        -- 門數(shù)相同
  AND t.courses = (SELECT GROUP_CONCAT(c_id ORDER BY c_id)
                   FROM Score WHERE s_id = '01');                   -- 課程串相同
14、查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名 
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、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
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"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
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、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
SELECT
    st.s_id,
    st.s_name,
    MAX(CASE WHEN sc.c_id = '01' THEN sc.s_score END) AS 語(yǔ)文,
    MAX(CASE WHEN sc.c_id = '02' THEN sc.s_score END) AS 數(shù)學(xué),
    MAX(CASE WHEN sc.c_id = '03' THEN sc.s_score END) AS 英語(yǔ),
    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;

到此這篇關(guān)于mysql常用語(yǔ)句與函數(shù)大全及舉例的文章就介紹到這了,更多相關(guān)mysql常用語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: