mysql常用語句與函數(shù)大全及舉例
各個子句的執(zhí)行順序
了解mysql的查詢語句的執(zhí)行順序，會對編寫sql語句有一定的幫助。
- from子句:基于表進行查詢操作
- where子句:進行條件篩選或者條件過濾
- group by子句:對剩下的數(shù)據(jù)進行分組查詢。
- having子句：分組后，再次條件篩選或過濾
- select子句：目的是選擇業(yè)務(wù)需求的字段進行顯示
- order by子句：對選擇后的字段進行排序
- limit子句:進行分頁查詢，或者是查詢前n條記錄
where子句
where關(guān)鍵字后，用于書寫篩選的條件。條件可以是：
1、比較運算： = < <= = != <>
2、邏輯連接 ： AND（同時滿足） OR（滿足其一）
3、區(qū)間 ： BETWEEN … AND … 等價于 ≥ 且 ≤
NOT BETWEEN … AND … 等價于 < 或 >
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子句后，再次對數(shù)據(jù)進行篩選和過濾，篩選的條件與where相同
distinct關(guān)鍵字
- 置于列名前。
- 當(dāng)select子句中有多個字段時，distinct必須要位于第一個字段的前面，表示這些字段的值的組合進行去重。
- 也可以位于函數(shù)中指定字段的第一個字段前面
order by子句
它的執(zhí)行時機是在select子句后執(zhí)行，通常用于對查詢出來的數(shù)據(jù)進行排序的。
order by colName [asc|desc] [,colName [asc|desc]]...
asc：表示根據(jù)指定字段的值升序排序，是默認值，可以省略不寫。
desc：表示根據(jù)指定字段的值降序排序。
可以指定多個字段，分別進行排序。當(dāng)前一個字段的值相同時，后一個字段的排序規(guī)則才會生效。
limit關(guān)鍵字
當(dāng)查詢出來的數(shù)據(jù)量過大，導(dǎo)致當(dāng)前屏幕不能一次性全部展示出來時，我們可以使用limit關(guān)鍵字進行限制條數(shù)查詢。
limit [off,] size;
size：表示要查詢的數(shù)量
off參數(shù)： 表示從第幾條記錄開始查詢， 記錄的索引從0開始的。 如果沒有該參數(shù)，表示從第一條開始查詢size條。
常用函數(shù)
數(shù)值函數(shù)
函數(shù) | 函數(shù)說明 |
|---|---|
pow(x,y)/power(x,y) | 返回x的y次冪 |
sqrt(n) | 返回非負數(shù)n的平方根 |
pi() | 返回圓周率 |
rand()、rand(n) | 返回在范圍0到1.0內(nèi)的隨機浮點值(可以使用數(shù)字n作為初始值) |
truncate(n,d) | 保留數(shù)字n的d位小數(shù)并返回 |
least(x,y,...)、greatest(x,y,...) | 求最小值或最大值 |
mod(n,m) | 取模運算,返回n被m除的余數(shù) |
ceil(n)、ceiling(n)、floor(n) | 向上/向下取整函數(shù) |
round(n[,d]) | 返回n的四舍五入值,保留d位小數(shù)(d的默認值為0) |
日期函數(shù)
函數(shù) | 函數(shù)說明 |
curdate()\curtime()\now()\sysdate()\current_timestamp() | 獲取系統(tǒng)時間 |
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星期） | 日期格式化，根據(jù)format字符串格式化date的值 |
date_add(date,interval value unit) \date_sub(date,interval value unit) | 日期運算 |
adddate(date,interval value unit) \subdate(date,interval value unit) | 日期運算 |
其他函數(shù)
單條件分支函數(shù)：if
語法：if(express,value1,value2):
解析：如果express表達式成立，就返回value1,否則返回value2.
多條件分支函數(shù)：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;窗口函數(shù)
窗口函數(shù)介紹
窗口函數(shù)，也被稱為分析函數(shù)，是 MySQL 8.0 引入的一項強大功能 ，它能夠在查詢結(jié)果集中對數(shù)據(jù)進行分組、排序和計算，而無需使用臨時表或自連接。窗口函數(shù)的語法結(jié)構(gòu)如下：
window_function(expr) OVER ([PARTITION BY partition_expression, ...] [ORDER BY sort_expression [ASC | DESC], ...])
window_function有如下分類：
能類型 | 函數(shù)名 | 應(yīng)用場景 |
|---|---|---|
聚合類 | SUM, AVG, COUNT、max、min | 對窗口內(nèi)的數(shù)據(jù)進行聚合計算 |
排名類 | ROW_NUMBER, RANK, DENSE_RANK | 對數(shù)據(jù)進行排序并生成排名 |
分布類 | PERCENT_RANK, CUME_DIST | 計算分布情況 |
偏移類 | LAG, LEAD | 獲取上下行數(shù)據(jù) |
over函數(shù)解析
OVER() 函數(shù)是 窗口函數(shù)（Window Function） 的一部分，用于在查詢中執(zhí)行基于一組的行計算，同時保留這些行的原始記錄。這與傳統(tǒng)的聚合函數(shù)（如 SUM()、AVG() 等）不同，后者通常會將多行合并為一行輸出。
- PARTITION BY： 將數(shù)據(jù)劃分為多個邏輯分區(qū)（類似GROUP BY）,每個分區(qū)獨立計算
- ORDER BY： 定義窗口內(nèi)行的排序方式，影響如累計、排名等計算順序
排名函數(shù)
- 2）row_number()
- 給排序過的表記錄分配行號，從1開始的連續(xù)自然數(shù)
- 3）rank()
- 給排序過的表記錄分配名次。 相同的值名次一樣，后續(xù)的排名出現(xiàn)跳躍情況
- 4）dense_rank()
- 給排序過的表記錄分配名次。 相同的值名次一樣，后續(xù)的排名不出現(xiàn)跳躍情況
關(guān)聯(lián)查詢
關(guān)聯(lián)分類
分類 | 語法 | 解析 |
|---|---|---|
內(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連接或者逗號連接查詢，但是沒有使用on或where關(guān)鍵字來指定關(guān)聯(lián)條件時，就會出現(xiàn)交叉連接
這種交叉連接，產(chǎn)生的記錄數(shù)為兩張表的記錄數(shù)的乘積，這種結(jié)果也被稱之為笛卡爾積。
union [all]操作符
如果我們想要將兩個查詢的結(jié)果集合并到一起，我們就可以使用union [all] 操作符。
select column_name,column_name,.... from table_name union [all] select column_name,column_name,.... from table_name
union all：兩個子句中的重復(fù)部分，會保留，不去重。union：會去掉并集中的重復(fù)記錄
子查詢
有的時候，當(dāng)一個查詢語句A所需要的數(shù)據(jù)，不是直觀在表中體現(xiàn)，而是另外一個查詢語句B查詢出來的結(jié)果，那么查詢語句A就是主查詢語句，查詢語句B就是子查詢語句。這種查詢我們稱之為高級關(guān)聯(lián)查詢，也叫做子查詢。
子查詢語句的位置可以在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`)
);
--成績表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入學(xué)生表測試數(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' , '女');
--課程表測試數(shù)據(jù)
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');
insert into Course values('04' , '體育' , '01');
--教師表測試數(shù)據(jù)
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成績表測試數(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"課程成績高的學(xué)生的信息及課程分數(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"課程成績低的學(xué)生的信息及課程分數(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、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
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分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
(包括有成績的和無成績的)
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、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績
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é)過"張三"老師授課的同學(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、查詢沒學(xué)過"張三"老師授課的同學(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é)過編號為"01"并且也學(xué)過編號為"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é)過編號為"01"但是沒有學(xué)過編號為"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、查詢沒有學(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é)號為"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"號的同學(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、查詢沒學(xué)過"張三"老師講授的任一門課程的學(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é)號，姓名及其平均成績
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"課程分數(shù)小于60，按分數(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、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
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 數(shù)學(xué),
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;到此這篇關(guān)于mysql常用語句與函數(shù)大全及舉例的文章就介紹到這了,更多相關(guān)mysql常用語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql中varchar類型的日期進行比較、排序等操作的實現(xiàn)
在mysql使用過程中，日期一般都是以datetime、timestamp等格式進行存儲的，但有時會因為特殊的需求或歷史原因，日期的存儲格式是varchar，那么應(yīng)該怎么進行比較和排序等問題，本文就來介紹一下2021-11-11
淺談innodb的索引頁結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引
下面小編就為大家?guī)硪黄獪\談innodb的索引頁結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
字符集的概念大家都清楚，校對規(guī)則很多人不了解，一般數(shù)據(jù)庫開發(fā)中也用不到這個概念，mysql在這方便貌似很先進，大概介紹一下2012-07-07
詳解MySQL存儲過程參數(shù)有三種類型(in、out、inout)
MySQL 存儲過程參數(shù)有三種類型：in、out、inout。它們各有什么作用和特點呢2012-07-07
下面小編就為大家?guī)硪黄狹ySQL 按指定字段自定義列表排序的實現(xiàn)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-03-03
這篇文章主要介紹了Mysql事務(wù)索引知識匯總，mysql事務(wù)是用于處理操作量大、復(fù)雜性高的數(shù)據(jù)，索引能加快數(shù)據(jù)庫的查詢速度并高效獲取指定的數(shù)據(jù)，下文相關(guān)詳細內(nèi)容，需要的小伙伴可以參考一下2022-03-03
這篇文章主要介紹了MySQL中NULL和空值的區(qū)別及說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
mysql中g(shù)roup by語句用于分組查詢，可以根據(jù)給定數(shù)據(jù)列的每個成員對查詢結(jié)果進行分組統(tǒng)計，最終得到一個分組匯總表， 經(jīng)常和having一起使用,需要的朋友可以參考下2016-11-11
最近同事咨詢了一個問題,TDSQL（for MySQL）中的某張表主鍵需要改為聯(lián)合主鍵,是否必須先刪除現(xiàn)有的主鍵？因為刪除主鍵時,提示這個錯誤,所以本文給大家介紹了MySQL無法修改主鍵的問題分析及解決方案,需要的朋友可以參考下2024-01-01
這篇文章主要給大家介紹了關(guān)于MySql開發(fā)之自動同步表結(jié)構(gòu)的相關(guān)資料，這樣可以避免在開發(fā)中由于修改數(shù)據(jù)庫字段導(dǎo)致的數(shù)據(jù)庫表不一致問題，需要的朋友可以參考下2021-05-05