14種SQL的進階用法分享(更高效地處理數(shù)據(jù))
摘要：
SQL（結(jié)構(gòu)化查詢語言）是用于管理和操作關(guān)系數(shù)據(jù)庫的標(biāo)準編程語言。本文將分享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表達式(CASE···WHEN)
在實際開發(fā)中我們經(jīng)常會寫很多if else if else，這時候我們可以使用CASE WHEN表達式解決這個問題。
以學(xué)生成績舉例。比如說:學(xué)生90分以上評為優(yōu)秀，分數(shù)80-90評為良好，分數(shù)60-80評為一般，分數(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ù)可以在分組后指定字段的字符串連接方式，并且還可以指定排序邏輯;連接字符串默認為英文逗號.
比如說根據(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ù)的場景，通常的做法就是先進行刪除在插入達到可重復(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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
SQLMap是一款自動執(zhí)行SQL注入的審計工具,支持多種SQL注入技術(shù),包括布爾型盲注、時間型盲注、報錯型注入、聯(lián)合查詢注入和堆疊查詢注入2025-01-01
海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案集合1/2
海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案集合1/2...2007-03-03
這篇文章主要介紹了Navicat for SQLite安裝教程 附安裝包,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-07-07
sqlserver中drop、truncate和delete語句的用法
這篇文章主要介紹了sqlserver中drop、truncate和delete語句的用法,本文圖文并茂,內(nèi)容清晰,需要的朋友可以參考下2014-09-09
樹形結(jié)構(gòu)數(shù)據(jù)庫表Schema設(shè)計的兩種方案
程序設(shè)計過程中,我們常常用樹形結(jié)構(gòu)來表征某些數(shù)據(jù)的關(guān)聯(lián)關(guān)系,如企業(yè)上下級部門、欄目結(jié)構(gòu)、商品分類等等,下面這篇文章主要給大家介紹了關(guān)于樹形結(jié)構(gòu)數(shù)據(jù)庫表Schema設(shè)計的兩種方案,需要的朋友可以參考下2021-09-09
sql 左連接和右連接的使用技巧(left join and right join)
今天做項目，發(fā)現(xiàn)左右連接是不一樣的。主要是說明了區(qū)別，是不是必須用左連接或右連接，大家可以根據(jù)需要選擇。2010-05-05
centos虛擬機部署opengauss數(shù)據(jù)庫詳細圖文教程
這篇文章主要給大家介紹了關(guān)于centos虛擬機部署opengauss數(shù)據(jù)庫的相關(guān)資料,文章詳細介紹了在CentOS上安裝和配置openGauss數(shù)據(jù)庫的過程,包括安裝步驟、環(huán)境配置、權(quán)限設(shè)置、預(yù)安裝和正式安裝等,需要的朋友可以參考下2024-12-12
這篇文章主要給大家介紹了關(guān)于SQL WHERE IN參數(shù)化編譯寫法的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學(xué)習(xí)或者使用SQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-11-11
公司要在openEuler系統(tǒng)中安裝openGauss數(shù)據(jù)庫,經(jīng)過幾天的查資料,終于是安裝成功了,并且能在navicat中遠程連接使用,本文給大家介紹如何使用navicat遠程連接openGauss,感興趣的朋友跟隨小編一起看看吧2023-10-10
本來以為資料都是丟了的，今天整理移動硬盤時發(fā)現(xiàn)found.000這個目錄超大，進去一看，我的媽呀，資料都在這里了，這下可把我樂壞了，我趕緊把一些有用的都發(fā)上來先2008-08-08