14种SQL的进阶用法分享(更高效地处理数据)

目录

• 摘要：
• 一、子查询
• 二、连接查询
• 三、聚合函数
• 四、GROUP BY 和 HAVING
• 五、窗口函数
• 六、CASE 语句
• 七、事务处理
• 八、自定义排序（ORDER BY FIELD）
• 九、空值NULL排序(ORDER BY IF(ISNULL))
• 十、CASE表达式(CASE···WHEN)
• 十一、分组连接函数(GROUP CONCAT)
• 十二、分组统计数据后再进行统计汇总 (with rollup)
• 十三、子查询提取(with as)
• 十四、优雅处理数据插入、更新时主键、唯一键重复
• 总结

摘要：

SQL（结构化查询语言）是用于管理和操作关系数据库的标准编程语言。本文将分享7种 SQL 的进阶用法，帮助您更高效地处理数据库数据。

一、子查询

子查询是 SQL 查询中的一部分，用于从表中检索数据。子查询可以嵌套，即一个查询作为另一个查询的条件。

• 单行子查询：

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

例如，从 students 表中选择所有选修了 Math 课程的学生：

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 课程的学生：

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

二、连接查询

连接查询用于合并两个或多个表中的数据。连接查询有多种类型，包括内连接、左连接、右连接和全连接。

• 内连接：

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

例如，从 students 和 courses 表中选择学生及其选修的课程：

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

• 左连接：

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

例如，从 students 表中选择所有学生及其选修的课程（即使某些学生没有选修课程）：

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

三、聚合函数

聚合函数用于对一组数据进行汇总，如求和、平均、最大值、最小值等。

• SUM()：求和函数。

SELECT SUM(column_name)
FROM table_name;

例如，计算 sales 表中所有销售额的总和：

SELECT SUM(amount)
FROM sales;

• AVG()：平均值函数。

SELECT AVG(column_name)
FROM table_name;

例如，计算 employees 表中平均工资：

SELECT AVG(salary)
FROM employees;

四、GROUP BY 和 HAVING

GROUP BY 用于对查询结果进行分组，而 HAVING 用于过滤分组后的结果。

• GROUP BY：

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

例如，按部门统计员工数量：

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

• HAVING：

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

例如，筛选出员工数量超过5的部门：

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

五、窗口函数

窗口函数用于对查询结果进行分组和聚合，如计算排名、分组统计等。

• RANK()：排名函数。

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

例如，按销售额对销售人员进行排名：

SELECT name, RANK() OVER (PARTITION BY d编程epartment ORDER BY amount DESC)
FROM sales;

六、CASE 语句

CASE 语句用于在 SQL 查询中实现条件判断和分支。

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

例如，根据性别计算员工的工资：

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;

七、事务处理

事务处理用于确保一组操作要么全部执行，要么全部不执行。

• 开始事务：

BEGIN TRANSACTION;

• 提交事务：

COMMIT;

• 回滚事务：

ROLLBACK;

例如，执行一个更新操作，如果操作成功，则提交事务；如果操作失败，则回滚事务：

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之外，还可以使用自定义排序方式来实现。

select * from movies order by movie_name asc;
qselect * from movies ORDER BY FIELD(movie name，'神话'，'猎场'，'芳华'，'花木兰!','铜雀台'，'警察故事'，'天下无贼'，'四大名捕'，'惊天解密'，'建国大业'，'功夫瑜伽'，'咱们结婚吧'，'赛尔号4';'疯狂机器城');

order by field(col_name,自定义排序结果集) --根据指定的顺序进行排序

九、空值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;

十、CASE表达式(CASE···WHEN)

在实际开发中我们经常会http://www.devze.com写很多if  else if  else，这时候我们可以使用CASE WHEN表达式解决这个问题。

以学生成绩举例。比如说:学生90分以上评为优秀，分数80-90评为良好，分数60-80评为一般，分数低于60评为“较差”，那么我们可以使用下面这种查询方式:

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

十一、分组连接函数(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;

十二、分组统计数据后再进行统计汇总 (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;

十三、子查询提取(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 ='刘亦菲';

十四、优雅处理数据插入、更新时主键、唯一键重复

在MySq中插入，更新数据有时会遇到主键重复的场景，通常的做法就是先进行删除在插入达到可重复执行的效果，但是这种方法有时候会错误删除数据。

①插入数据时我们可以使用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,XPOAc price,release_date) VALUES
(14，"'神话2'，'成龙'，114，'2005-12-22');

②还可以使用REPLACE关键字，当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入，即有则删除+播入，无则插入，示例如下

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

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

③更新数据时使用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;

总结

本文分享了7种 SQL 的进阶用法，包括子查询、连接查询、聚合函数、GROUP BY 和 HAVING、窗口函数、CASE 语句和事务处理。这些高级特性可以帮助您更高效地处理数据库数据，实现复杂的业务逻辑。随着 SQL 技术的不断发展，您可以学习更多高级特性，以进一步提高开发效率和性能。

到此这篇关于7种SQL的进阶用法分享的文章就介绍到这了,更多相关SQL进阶用法内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！