14種SQL的進階用法分享(更高效地處理數(shù)據(jù))

更新時間：2024年01月30日 11:15:24 作者：沐知全棧開發(fā)

在實際的數(shù)據(jù)庫使用中除了CRUD還有很多高級應(yīng)用值得學(xué)習(xí)和掌握,能夠在平時的工作中得到很多便利,這篇文章主要給大家分享介紹了14種SQL的進階用法,通過文中介紹的方法可以更高效地處理數(shù)據(jù)庫數(shù)據(jù),需要的朋友可以參考下

摘要：
一、子查詢
二、連接查詢
三、聚合函數(shù)
四、GROUP BY 和 HAVING
五、窗口函數(shù)
六、CASE 語句
七、事務(wù)處理
八、自定義排序（ORDER BY FIELD）
九、空值NULL排序(ORDER BY IF(ISNULL))
十、CASE表達(dá)式(CASE···WHEN)
十一、分組連接函數(shù)(GROUP CONCAT)
十二、分組統(tǒng)計數(shù)據(jù)后再進行統(tǒng)計匯總 (with rollup)
十三、子查詢提取(with as)
十四、優(yōu)雅處理數(shù)據(jù)插入、更新時主鍵、唯一鍵重復(fù)
總結(jié)

摘要：

SQL（結(jié)構(gòu)化查詢語言）是用于管理和操作關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)編程語言。本文將分享7種 SQL 的進階用法，幫助您更高效地處理數(shù)據(jù)庫數(shù)據(jù)。

一、子查詢

子查詢是 SQL 查詢中的一部分，用于從表中檢索數(shù)據(jù)。子查詢可以嵌套，即一個查詢作為另一個查詢的條件。

單行子查詢：

SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM sub_table WHERE condition);

例如，從 students 表中選擇所有選修了 Math 課程的學(xué)生：

SELECT s.name
FROM students s
WHERE s.course_id = (SELECT course_id FROM courses WHERE name = 'Math');

多行子查詢：

SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM sub_table WHERE condition);

例如，從 students 表中選擇所有選修了 Math 或 Physics 課程的學(xué)生：

SELECT s.name
FROM students s
WHERE s.course_id IN (SELECT course_id FROM courses WHERE name IN ('Math', 'Physics'));

二、連接查詢

連接查詢用于合并兩個或多個表中的數(shù)據(jù)。連接查詢有多種類型，包括內(nèi)連接、左連接、右連接和全連接。

內(nèi)連接：

SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2;

例如，從 students 和 courses 表中選擇學(xué)生及其選修的課程：

SELECT s.name, c.name AS course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id;

左連接：

SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2;

例如，從 students 表中選擇所有學(xué)生及其選修的課程（即使某些學(xué)生沒有選修課程）：

SELECT s.name, c.name AS course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.course_id;

三、聚合函數(shù)

聚合函數(shù)用于對一組數(shù)據(jù)進行匯總，如求和、平均、最大值、最小值等。

SUM()：求和函數(shù)。

SELECT SUM(column_name)
FROM table_name;

例如，計算 sales 表中所有銷售額的總和：

SELECT SUM(amount)
FROM sales;

AVG()：平均值函數(shù)。

SELECT AVG(column_name)
FROM table_name;

例如，計算 employees 表中平均工資：

SELECT AVG(salary)
FROM employees;

四、GROUP BY 和 HAVING

GROUP BY 用于對查詢結(jié)果進行分組，而 HAVING 用于過濾分組后的結(jié)果。

GROUP BY：

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;

例如，按部門統(tǒng)計員工數(shù)量：

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

HAVING：

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 5;

例如，篩選出員工數(shù)量超過5的部門：

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

五、窗口函數(shù)

窗口函數(shù)用于對查詢結(jié)果進行分組和聚合，如計算排名、分組統(tǒng)計等。

RANK()：排名函數(shù)。

SELECT column_name, RANK() OVER (PARTITION BY column_name ORDER BY another_column)
FROM table_name;

例如，按銷售額對銷售人員進行排名：

SELECT name, RANK() OVER (PARTITION BY department ORDER BY amount DESC)
FROM sales;

六、CASE 語句

CASE 語句用于在 SQL 查詢中實現(xiàn)條件判斷和分支。

SELECT column_name,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE default_result
       END AS calculated_column
FROM table_name;

例如，根據(jù)性別計算員工的工資：

SELECT name,
       CASE
           WHEN gender = 'Male' THEN salary * 0.9
           WHEN gender = 'Female' THEN salary * 1.1
           ELSE salary
       END AS adjusted_salary
FROM employees;

七、事務(wù)處理

事務(wù)處理用于確保一組操作要么全部執(zhí)行，要么全部不執(zhí)行。

開始事務(wù)：

BEGIN TRANSACTION;

提交事務(wù)：

COMMIT;

回滾事務(wù)：

ROLLBACK;

例如，執(zhí)行一個更新操作，如果操作成功，則提交事務(wù)；如果操作失敗，則回滾事務(wù)：

BEGIN TRANSACTION;
UPDATE table_name SET column_name = new_value WHERE condition;
COMMIT;

或者：

BEGIN TRANSACTION;
UPDATE table_name SET column_name = new_value WHERE condition;
IF @@ROWCOUNT > 0
    COMMIT;
ELSE
    ROLLBACK;
END TRANSACTION;

八、自定義排序（ORDER BY FIELD）

在MySQL中ORDER BY排序除了可以用ASC和DESC之外，還可以使用自定義排序方式來實現(xiàn)。

select * from movies order by movie_name asc;
qselect * from movies ORDER BY FIELD(movie name，'神話'，'獵場'，'芳華'，'花木蘭!','銅雀臺'，'警察故事'，'天下無賊'，'四大名捕'，'驚天解密'，'建國大業(yè)'，'功夫瑜伽'，'咱們結(jié)婚吧'，'賽爾號4';'瘋狂機器城');

order by field(col_name,自定義排序結(jié)果集) --根據(jù)指定的順序進行排序

九、空值NULL排序(ORDER BY IF(ISNULL))

在MySQL中使用ORDER BY關(guān)鍵字加上我們需要排序的字段名稱就可以完成這字段的排序，如果字段中存在NULL值就會對我們的排序結(jié)果造成影響，這時候我們可以使用ORDER BY IF(ISNULL(字段),0,1)語法NULL值轉(zhuǎn)換成0或1，實現(xiàn)NULL值數(shù)據(jù)排序到數(shù)據(jù)集前面還是后面

select * from movies ORDER BY actors, price desc;
select * from movies ORDER BY if(ISNULL(actors),0,1),actors, price;

十、CASE表達(dá)式(CASE···WHEN)

在實際開發(fā)中我們經(jīng)常會寫很多if else if else，這時候我們可以使用CASE WHEN表達(dá)式解決這個問題。

以學(xué)生成績舉例。比如說:學(xué)生90分以上評為優(yōu)秀，分?jǐn)?shù)80-90評為良好，分?jǐn)?shù)60-80評為一般，分?jǐn)?shù)低于60評為“較差”，那么我們可以使用下面這種查詢方式:

select *,case when score>90 then '優(yōu)秀'
            when score>80 then '良好'
            when score>60 then '一般'
            else '較差' end level
from student;

十一、分組連接函數(shù)(GROUP CONCAT)

分組連接函數(shù)可以在分組后指定字段的字符串連接方式，并且還可以指定排序邏輯;連接字符串默認(rèn)為英文逗號.

比如說根據(jù)演員進行分組，并將相應(yīng)的電影名稱按照票價進行降序排列，而且電影名稱之間通過“_”拼接。

用法如下:

select actors,
GROUP CONCAT(movie name)，
GROUP_CONCAT(price) from movies GROUP BY actors;

select actors,
GROUP_CONCAT(movie_name order by price desc SEPARATOR '_'),
GROUP_CONCAT(price order by price desc SEPARATOR '_')
from movies GROUP BY actors;

十二、分組統(tǒng)計數(shù)據(jù)后再進行統(tǒng)計匯總 (with rollup)

在MySql中可以使用 with rollup在分組統(tǒng)計數(shù)據(jù)的基礎(chǔ)上再進行數(shù)據(jù)統(tǒng)計匯總，即將分組后的數(shù)據(jù)進行匯總

SELECT actors,SUM(price) FROM movies GROUP BY actors;
SELECT actors,SUM(price) FROM movies GROUP BY actors WITH ROLLUP;

十三、子查詢提取(with as)

如果一整句查詢中多個子查詢都需要使用同一個子查詢的結(jié)果，那么就可以用with as將共用的子查詢提取出來并取一個別名，后面查詢語句可以直接用，對于大量復(fù)雜的SQL語句起到了很好的優(yōu)化作用。
需求:獲取演員劉亦菲票價大于50且小于65的數(shù)據(jù)。

with m1 as (select * from movies where price > 50),
    m2 as (select * from movies where price >= 65)
select * from m1 where m1.id not in (select m2.id from m2) and m1.actors ='劉亦菲';

十四、優(yōu)雅處理數(shù)據(jù)插入、更新時主鍵、唯一鍵重復(fù)

在MySq中插入，更新數(shù)據(jù)有時會遇到主鍵重復(fù)的場景，通常的做法就是先進行刪除在插入達(dá)到可重復(fù)執(zhí)行的效果，但是這種方法有時候會錯誤刪除數(shù)據(jù)。

①插入數(shù)據(jù)時我們可以使用IGNORE，它的作用是插入的值遇到主鍵或者唯一鍵重復(fù)時自動忽路重復(fù)的數(shù)據(jù)，不影響后面數(shù)據(jù)的插入，即有則忽路，無則插入，示例如下:

select * from movies where id >= 13;

INSERT INTO movies (id, movie_name,actors，price, release_date) VALUES
(13，'神話'，'成龍'，100，'2005-12-22');

INSERT IGNORE INTO movies (id, movie_name,actors,price,release_date) VALUES
(13，'神話'，'成龍'，100，'2005-12-22');

INSERT IGNORE INTO movies (id, movie_name, actors, price,release_date) VALUES
(14，"'神話2'，'成龍'，114，'2005-12-22');

②還可以使用REPLACE關(guān)鍵字，當(dāng)插入的記錄遇到主鍵或者唯一鍵重復(fù)時先刪除表中重復(fù)的記錄行再插入，即有則刪除+播入，無則插入，示例如下

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14，'神話2”，‘成龍'，100，‘2005-12-22');

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(15，'神話3'，'成龍'，115，'2005-12-22');

③更新數(shù)據(jù)時使用on duplicate key update,它的作用是當(dāng)插入的記錄遇到主鍵或者是唯一鍵重復(fù)時，會執(zhí)行后面定義的update操作。相當(dāng)于先執(zhí)行insert操作，再根據(jù)主鍵或者唯一鍵執(zhí)行update操作，即有就更新，沒有就插入，示例如下：

INSERT INTO movies (id,movie_name, actors, price, release_date) VALUES
(15,'神話3'，'成龍'，115，'2005-12-22') on duplicate key update price = price + 10;

INSERT INTO movies (id,movie_name, actors, price, release_date) VALUES
(16,'神話4'，'成龍'，75，'2005-12-22') on duplicate key update price = price + 10;

總結(jié)

本文分享了7種 SQL 的進階用法，包括子查詢、連接查詢、聚合函數(shù)、GROUP BY 和 HAVING、窗口函數(shù)、CASE 語句和事務(wù)處理。這些高級特性可以幫助您更高效地處理數(shù)據(jù)庫數(shù)據(jù)，實現(xiàn)復(fù)雜的業(yè)務(wù)邏輯。隨著 SQL 技術(shù)的不斷發(fā)展，您可以學(xué)習(xí)更多高級特性，以進一步提高開發(fā)效率和性能。

到此這篇關(guān)于7種SQL的進階用法分享的文章就介紹到這了,更多相關(guān)SQL進階用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

14種SQL的進階用法分享(更高效地處理數(shù)據(jù))

目錄

摘要：

一、子查詢

二、連接查詢

三、聚合函數(shù)

四、GROUP BY 和 HAVING

五、窗口函數(shù)

六、CASE 語句

七、事務(wù)處理

八、自定義排序（ORDER BY FIELD）

九、空值NULL排序(ORDER BY IF(ISNULL))

十、CASE表達(dá)式(CASE···WHEN)

十一、分組連接函數(shù)(GROUP CONCAT)

十二、分組統(tǒng)計數(shù)據(jù)后再進行統(tǒng)計匯總 (with rollup)

十三、子查詢提取(with as)

十四、優(yōu)雅處理數(shù)據(jù)插入、更新時主鍵、唯一鍵重復(fù)

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

14種SQL的進階用法分享(更高效地處理數(shù)據(jù))

目錄

摘要：

一、子查詢

二、連接查詢

三、聚合函數(shù)

四、GROUP BY 和 HAVING

五、窗口函數(shù)

六、CASE 語句

七、事務(wù)處理

八、自定義排序（ORDER BY FIELD）

九、空值NULL排序(ORDER BY IF(ISNULL))

十、CASE表達(dá)式(CASE···WHEN)

十一、分組連接函數(shù)(GROUP CONCAT)

十二、分組統(tǒng)計數(shù)據(jù)后再進行統(tǒng)計匯總 (with rollup)

十三、子查詢提取(with as)

十四、優(yōu)雅處理數(shù)據(jù)插入、更新時主鍵、唯一鍵重復(fù)

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

一、子查詢

二、連接查詢

三、聚合函數(shù)

五、窗口函數(shù)

六、CASE 語句

八、自定義排序（ORDER BY FIELD）

九、空值NULL排序(ORDER BY IF(ISNULL))

十一、分組連接函數(shù)(GROUP CONCAT)

十二、分組統(tǒng)計數(shù)據(jù)后再進行統(tǒng)計匯總 (with rollup)

十三、子查詢提取(with as)

十四、優(yōu)雅處理數(shù)據(jù)插入、更新時主鍵、唯一鍵重復(fù)