7種SQL的進階用法

1.自定義排序（ORDER BY FIELD）

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

CREATE TABLE movies (  id INT PRIMARY KEY AUTO_INCREMENT,  movie_name VARCHAR(255),  actors VARCHAR(255),  price DECIMAL(10, 2) DEFAULT 50,  release_date DATE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO movies (movie_name, actors, price, release_date) VALUES
('咱們結婚吧', '靳東', 43.2, '2013-04-12'),
('四大名捕', '劉亦菲', 62.5, '2013-12-21'),
('獵場', '靳東', 68.5, '2017-11-03'),
('芳華', '范冰冰', 55.0, '2017-09-15'),
('功夫瑜伽', '成龍', 91.8, '2017-01-28'),
('驚天解密', '靳東', 96.9, '2019-08-13'),
('銅雀臺', null, 65, '2025-12-16'),
('天下無賊', '劉亦菲', 44.9, '2004-12-16'),
('建國大業', '范冰冰', 70.5, '2009-09-21'),
('賽爾號4：瘋狂機器城', '范冰冰', 58.9, '2021-07-30'),
('花木蘭', '劉亦菲', 89.0, '2020-09-11'),
('警察故事', '成龍', 68.0, '1985-12-14'),
('神話', '成龍', 86.5, '2005-12-22');

用法如下：

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

在這里插入圖片描述

2.空值NULL排序（ORDER BY IF(ISNULL）)

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

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

在這里插入圖片描述

3.CASE表達式（CASE···WHEN）

在實際開發中我們經常會寫很多if ··· else if ··· else，這時候我們可以使用CASE···WHEN表達式解決這個問題。
以學生成績舉例。比如說：學生90分以上評為優秀，分數80-90評為良好，分數60-80評為一般，分數低于60評為“較差”。那么我們可以使用下面這種查詢方式：

CREATE TABLE student (student_id varchar(10) NOT NULL COMMENT '學號',sname varchar(20) DEFAULT NULL COMMENT '姓名',sex char(2) DEFAULT NULL COMMENT '性別',age int(11) DEFAULT NULL COMMENT '年齡',score float DEFAULT NULL COMMENT '成績',PRIMARY KEY (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生表';INSERT INTO student (student_id, sname, sex, age , score)
VALUES ('001', '張三', '男', 20,  95),('002', '李四', '女', 22,  88),('003', '王五', '男', 21,  90),('004', '趙六', '女', 20,  74),('005', '陳七', '女', 19,  92),('006', '楊八', '男', 23,  78),('007', '周九', '女', 20,  55),('008', '吳十', '男', 22,  91),('009', '劉一', '女', 21,  87),('010', '孫二', '男', 19,  60);

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

在這里插入圖片描述

4.分組連接函數（GROUP_CONCAT）

分組連接函數可以在分組后指定字段的字符串連接方式，并且還可以指定排序邏輯；連接字符串默認為英文逗號。
比如說根據演員進行分組，并將相應的電影名稱按照票價進行降序排列，而且電影名稱之間通過“_”拼接。用法如下：

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;

在這里插入圖片描述

5.分組統計數據后再進行統計匯總（with rollup）

在MySql中可以使用 with rollup在分組統計數據的基礎上再進行數據統計匯總，即將分組后的數據進行匯總。

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

在這里插入圖片描述

6.子查詢提取（with as）

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

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 = '劉亦菲';

在這里插入圖片描述

7.優雅處理數據插入、更新時主鍵、唯一鍵重復

在MySql中插入、更新數據有時會遇到主鍵重復的場景，通常的做法就是先進行刪除在插入達到可重復執行的效果，但是這種方法有時候會錯誤刪除數據。
1.插入數據時我們可以使用IGNORE，它的作用是插入的值遇到主鍵或者唯一鍵重復時自動忽略重復的數據，不影響后面數據的插入，即有則忽略，無則插入。示例如下：

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');

2.還可以使用REPLACE關鍵字，當插入的記錄遇到主鍵或者唯一鍵重復時先刪除表中重復的記錄行再插入，即有則刪除+插入，無則插入，示例如下：

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');

在這里插入圖片描述

3.更新數據時使用on duplicate key update。它的作用就是當插入的記錄遇到主鍵或者唯一鍵重復時，會執行后面定義的UPDATE操作。相當于先執行Insert 操作，再根據主鍵或者唯一鍵執行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;

在這里插入圖片描述

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/160432.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/160432.shtml
英文地址，請注明出處：http://en.pswp.cn/news/160432.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！