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)文章
海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案集合1/2
海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案集合1/2...2007-03-03sqlserver中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-09sql 左連接和右連接的使用技巧(left join and right join)
今天做項目,發(fā)現(xiàn)左右連接是不一樣的。主要是說明了區(qū)別,是不是必須用左連接或右連接,大家可以根據(jù)需要選擇。2010-05-05centos虛擬機部署opengauss數(shù)據(jù)庫詳細圖文教程
這篇文章主要給大家介紹了關(guān)于centos虛擬機部署opengauss數(shù)據(jù)庫的相關(guān)資料,文章詳細介紹了在CentOS上安裝和配置openGauss數(shù)據(jù)庫的過程,包括安裝步驟、環(huán)境配置、權(quán)限設(shè)置、預(yù)安裝和正式安裝等,需要的朋友可以參考下2024-12-12