基础SQL的练习及解析

最新推荐文章于 2024-10-08 17:35:38 发布

ahytc

最新推荐文章于 2024-10-08 17:35:38 发布

阅读量423

点赞数 9

文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_57633481/article/details/140927148

版权

基础SQL的练习及解析

简单处理查询结果

去重：DISTINCT或GROUP BY

示例：

一：SELECT university from user_profile GROUP BY university;
二：SELECT DISTINCT university from user_profile;

解析：

distinct和group by区别

distinct：

distinct 只能放在查询字段的最前面，不能放在查询字段的中间或者后面。

distinct 对后面所有的字段均起作用，即 去重是查询的所有字段完全重复的数据，而不是只对 distinct 后面连接的单个字段重复的数据。

要查询多个字段，但只针对一个字段去重，使用distinct去重的话是无法实现的。

group by

一般与聚类函数使用（如count()/sum()等），也可单独使用。

group by 也对后面所有的字段均起作用，即 去重是查询的所有字段完全重复的数据，而不是只对 group by后面连接的单个字段重复的数据

查询的字段与group by 后面分组的字段没有限制。（在oracle中使用group by时，查询的字段必须是group by 分组的字段和聚类函数。如select name,sex from tb_students group by name这个sql）

限制返回行数：limit

示例：

SELECT DEVICE_ID FROM USER_PROFILE LIMIT 2;

解析：

LIMI用法：
--LIMIT n：从第0+1(m=0)条开始，取n条数据，是LIMIT 0,n的缩写
SELECT device_id
FROM user_profile
LIMIT 2 

--LIMIT m,n:从第m+1条开始，取n条数据
SELECT device_id
FROM user_profile
LIMIT 0,2

--LIMIT n OFFSET m：从第m+1条开始，取n条数据
SELECT device_id
FROM user_profile
LIMIT 2 OFFSET 0

列重命名：AS

示例：

select 
    device_id 'user_infos_example'
from 
    user_profile
limit 2;

解析：

语法：
1 列名 | 表达式 [ AS ] 新列名
或：
1 新列名＝列名 | 表达式

例：
SELECT 姓名, year (getdate())-year(出生日期) AS 年龄 FROM 学生表

条件查询：where

基础操作：等于、不等于、大于、小于、逻辑运算、范围查询、模糊查询、空值匹配

示例：

select device_id,university from user_profile where university = "北京大学";

解析：

以下是一些常见的WHERE子句用法示例，涵盖了基本比较、逻辑运算、范围查询、模式匹配等方面：

基本比较:
等于：
	WHERE column = value
	SELECT * FROM Products WHERE Price = 99.99;

不等于：
	WHERE column != value 或 WHERE column <> value
	SELECT * FROM Employees WHERE Department != 'Sales';

大于：
	WHERE column > value
    SELECT * FROM Orders WHERE OrderDate > '2023-01-01';

小于：
	WHERE column < value
	SELECT * FROM Students WHERE Grade < 70;

大于等于/小于等于：
	WHERE column >= value / WHERE column <= value

逻辑运算:
	AND：同时满足两个条件
	SELECT * FROM Books WHERE Author = 'J.K. Rowling' AND YearPublished > 2000; 
	
	OR：满足任一条件
	SELECT * FROM Customers WHERE Country = 'USA' OR Country = 'Canada';
	
	NOT：否定条件
	SELECT * FROM Products WHERE NOT Discontinued;
	
范围查询:
BETWEEN: 在两个值之间（包括边界）

     SELECT * FROM Employees WHERE HireDate BETWEEN '2018-01-01' AND '2025-12-31';
     

模式匹配:
LIKE: 用于模糊查询，%是通配符，代表零个、一个或多个字符
     SELECT * FROM Products WHERE ProductName LIKE '%Chocolate%';
     

IN: 列出一系列可能的值
     SELECT * FROM Departments WHERE DepartmentName IN ('HR', 'IT', 'Finance');
     

空值处理:
IS NULL: 查找空值
SELECT * FROM Customers WHERE Email IS NULL;

IS NOT NULL: 查找非空值
SELECT * FROM Orders WHERE ShippingAddress IS NOT NULL;  

这些只是冰山一角，WHERE子句还能与函数、子查询等配合使用，以应对更复杂的查询需求。

基础排序：单列排序、多列排序、多列混合排序

查询后排序是非常常见的需求，你可以使用SQL的ORDER BY子句来实现。ORDER BY允许你根据一列或多列的值对查询结果进行排序，你可以选择升序（ASC，默认）或降序（DESC）排列。
基本语法:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_to_sort ASC|DESC;

如果你需要按多个列排序，只需在ORDER BY后列出所有列名，并可以为每个列指定排序方向。
如果没有指定排序方向，默认为升序（ASC）。

示例:
单列升序排序: 假设你有一个Employees表，想要按姓名字母顺序显示所有员工：
	SELECT * FROM Employees ORDER BY Name ASC;  

单列降序排序: 按工资从高到低显示员工：
	SELECT * FROM Employees ORDER BY Salary DESC;

多列排序: 先按部门排序，然后在同一部门内按入职日期排序：
	SELECT * FROM Employees ORDER BY Department, HireDate ASC;

多列混合排序: 首先按部门降序，然后在每个部门内部按工资升序：

   SELECT * FROM Employees ORDER BY Department DESC, Salary ASC;
   
排序操作可能会消耗较多资源，特别是对于大数据集。因此，在考虑性能时，尽量避免对大型表进行全表排序，除非绝对必要。

计算函数：最大值与平均值

最大值:MAX()
SELECT MAX(column_name) AS max_value
FROM table_name;

平均值:AVG()
SELECT AVG(column_name) AS average_value
FROM table_name;

分组查询：分组计算、分组过滤、分组排序

分组计算 (GROUP BY + 聚合函数)

分组计算是指将数据按照一个或多个列的值分组，并对每个组应用聚合函数以计算各组的统计值，如总和、平均值、最大值、最小值或计数等。
示例：计算每个部门的总工资
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

分组过滤 (HAVING)

HAVING子句用于在分组后对结果进行过滤，它允许你设置条件来排除某些分组。
注意，HAVING不能被WHERE替代，因为WHERE是在分组操作之前过滤行，而HAVING是在分组之后。

示例：只显示总工资超过50000的部门
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 50000;

分组排序 (ORDER BY)

虽然ORDER BY本身不是分组操作的一部分，但它经常与GROUP BY一起使用，以便对分组后的结果进行排序。你可以根据分组的列或聚合函数的结果来排序。
示例：按总工资降序排列部门
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 50000
ORDER BY TotalSalary DESC;

多表查询：

子查询

子查询（Subquery）

子查询是指嵌套在另一个查询中的查询，它作为一个表达式被外部查询使用。
子查询可以用来提供一个值、一列值或一组行给外部查询作为条件或数据源。

示例：查找比平均工资高的员工
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
);

在这个例子中，子查询计算了所有员工的平均工资，外部查询则用这个平均值作为条件，找出工资高于平均值的员工。

链表查询

连接查询（JOIN）

连接查询用于结合来自两个或更多表中的行，基于这些表之间的相关列。最常见的连接类型包括：

内连接（INNER JOIN）：返回两个表中匹配的行。
左连接（LEFT JOIN）：返回左表的所有行，即使右表中没有匹配。
右连接（RIGHT JOIN）：返回右表的所有行，即使左表中没有匹配。
全外连接（FULL OUTER JOIN）：返回左表和右表中所有匹配和不匹配的行。

示例：内连接员工表和部门表
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

此查询将员工表和部门表根据部门ID连接起来，展示每位员工所属的部门名称。

组合查询

组合查询（Set Operations）

组合查询使用UNION、UNION ALL、INTERSECT和EXCEPT操作符来合并或比较来自多个SELECT语句的结果集。

UNION：合并两个结果集，自动去除重复行。
UNION ALL：合并两个结果集，保留所有行，包括重复行。
INTERSECT：返回两个结果集的交集，即同时存在于两个结果集中的行。
EXCEPT：返回第一个结果集中有但第二个结果集中没有的行。

示例：使用UNION合并两个查询结果
(SELECT EmployeeName, Salary FROM Employees WHERE Department='Sales')
UNION
(SELECT EmployeeName, Bonus FROM Bonuses WHERE Year=2023);

这个例子展示了如何合并两组数据：一个是销售部门员工的薪资，另一个是2023年发放的奖金记录，结果集中会去除重复行。

常用函数

条件函数

CASE表达式允许你根据一个或多个条件来返回不同的值，它在处理复杂的条件逻辑时非常有用。

CASE表达式基本语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE else_result]
END;

WHEN conditionN THEN resultN：如果conditionN为真，则返回resultN。
[ELSE else_result]：可选部分，如果没有匹配的WHEN条件，则返回这里的默认值。

示例

假设有一个员工表，你要根据员工的等级（Level）计算奖金（Bonus），规则如下：
等级1：奖金为工资的10%
等级2：奖金为工资的15%
其他等级：奖金为工资的20%
可以使用CASE表达式来实现这个逻辑：
SELECT 
    EmployeeID, 
    Salary,
    CASE 
        WHEN Level = 1 THEN Salary * 0.1
        WHEN Level = 2 THEN Salary * 0.15
        ELSE Salary * 0.2
    END AS Bonus
FROM Employees;

在这个查询中，CASE表达式根据每个员工的等级来决定奖金的计算比例，然后将计算结果作为Bonus列返回。


其他类似功能的函数

在某些数据库系统中，还可能有其他函数提供类似条件处理的功能，比如Oracle的DECODE函数，它也允许根据条件返回不同值，但语法与CASE有所不同。然而，CASE表达式由于其标准性和灵活性，在多数情况下是首选的解决方案。

日期函数

MySQL中的日期函数

CURRENT_DATE 或 CURRENT_DATE() - 返回当前日期。
CURDATE() - 同CURRENT_DATE，返回当前日期。
CURRENT_TIME 或 CURRENT_TIME() - 返回当前时间。
CURTIME() - 同CURRENT_TIME，返回当前时间。
NOW() - 返回当前的日期和时间。
DATE(date) - 提取日期部分，从日期/时间表达式。
YEAR(date) - 返回日期的年份部分。
MONTH(date) - 返回日期的月份部分。
DAY(date) - 返回日期的日部分。
HOUR(time) - 返回时间的小时部分。
MINUTE(time) - 返回时间的分钟部分。
SECOND(time) - 返回时间的秒部分。
ADDDATE(date, INTERVAL expr unit) - 在日期上添加指定的时间间隔。
SUBDATE(date, INTERVAL expr unit) - 从日期中减去指定的时间间隔。
DATE_FORMAT(date, format) - 格式化日期时间值，如'%Y-%m-%d'。
DATEDIFF(date1, date2) - 计算两个日期之间的天数差。


SQL Server中的日期函数

GETDATE() - 返回当前的日期和时间。
DATEPART(part, date) - 返回日期时间的指定部分，如年、月、日等。
DATEADD(interval, number, date) - 在日期上增加指定的时间间隔。
DATEDIFF(interval, date1, date2) - 计算两个日期之间的时间间隔，单位由interval指定。
CONVERT(data_type, date_expression, style_number) - 转换日期/时间数据类型或格式化日期输出。

示例：

MySQL 示例

获取当前日期和时间
   SELECT NOW();
获取今天的日期
   SELECT CURDATE();
获取时间的部分，例如小时
   SELECT HOUR(NOW());
计算两个日期之间的天数差
   SELECT DATEDIFF('2024-08-01', '2024-07-31');
格式化日期输出
   SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
向日期添加一个月
   SELECT ADDDATE(NOW(), INTERVAL 1 MONTH);

SQL Server 示例

获取当前日期和时间
   SELECT GETDATE();
获取年份
   SELECT DATEPART(YEAR, GETDATE());
获取月份
   SELECT DATEPART(MONTH, GETDATE());
获取日期的部分，例如天
   SELECT DATEPART(DAY, GETDATE());
计算两个日期之间的月数差
   SELECT DATEDIFF(MONTH, '2024-06-01', '2024-07-31');
格式化日期输出
   SELECT CONVERT(VARCHAR, GETDATE(), 120); -- 'YYYY-MM-DD'
向日期添加一周
   SELECT DATEADD(WEEK, 1, GETDATE());

文本函数

UPPER(text): 将文本字符串转化为大写。
   SELECT UPPER('Hello World'); 
   -- 结果: HELLO WORLD

LOWER(text): 将文本字符串转化为小写。
   SELECT LOWER('Hello World'); 
   -- 结果: hello world

LENGTH(text) 或 LEN(text): 返回文本字符串的长度（字符数）。
   SELECT LENGTH('Hello'); 
   -- 结果: 5

SUBSTRING(text, start, length) 
或 SUBSTR(text, start, length): 从文本字符串中提取子串，start是开始位置，length是要提取的字符数。
   SELECT SUBSTRING('Hello World', 7, 5); 
   -- 结果: World
   
TRIM([characters FROM] text): 移除文本字符串首尾的空格或指定字符。
   SELECT TRIM('   Hello   '); 
   -- 结果: Hello
   
LTRIM(text): 移除文本字符串开头的空格。
   SELECT LTRIM('   Hello'); 
   -- 结果: Hello
   

RTRIM(text): 移除文本字符串末尾的空格。
   SELECT RTRIM('Hello   '); 
   -- 结果: Hello
   

REPLACE(text, search, replacement): 替换文本字符串中的某个子串。
   SELECT REPLACE('Hello World', 'World', 'Universe'); 
   -- 结果: Hello Universe

CONCAT(text1, text2, ...) 或 ||（在某些数据库中）: 连接两个或多个文本字符串。
   SELECT CONCAT('Hello', ' ', 'World');
   -- 结果: Hello World
   

INSTR(text, substring) 
或 POSITION(substring IN text) 
或 LOCATE(substring, text): 返回子串在文本字符串中的起始位置。如果未找到，返回值可能为0或NULL，具体取决于数据库系统。
    SELECT INSTR('Hello World', 'World'); 
    -- 结果: 7
    
Soundex(text): 返回文本字符串的Soundex值，这是一种编码方式，用于表示字符串的英语发音，常用于模糊搜索或姓名匹配。
    SELECT Soundex('Smith'); 
    -- 结果可能为: S500

窗口函数

窗口函数详解链接：https://zhuanlan.zhihu.com/p/92654574

窗口函数在SQL中是一种强大的分析工具，它允许你在一组相关的行（称为“窗口”）上执行计算，而不是在整个查询结果集上。窗口函数通过引入OVER子句来定义这个窗口的范围，并且可以在每个窗口内进行排序、聚合等操作，而不会影响到整个查询的行结构。以下是一些关键点和示例来帮助理解窗口函数：


窗口函数的关键概念：

窗口定义：通过OVER()子句定义，可以包括分区（PARTITION BY）、排序（ORDER BY）和帧（ROWS BETWEEN）等子句来精确控制窗口的范围。

分区（PARTITION BY）：将数据分成多个独立的组（或分区），在每个分区内独立执行窗口函数。

排序（ORDER BY）：在窗口内对行进行排序，这对于许多需要顺序计算的函数（如排名函数）至关重要。

帧（Frame）：定义窗口的一个子集，可以是固定的行数（如前N行、后N行或两者之间的行），或基于当前行的偏移量。


常见的窗口函数示例：

ROW_NUMBER()：为窗口内的每一行分配一个唯一的、连续的整数。
   SELECT EmployeeID, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
   FROM Employees;
   

RANK() 和 DENSE_RANK()：为窗口内的行提供排名，其中RANK()会跳过相同排名后的序号，而DENSE_RANK()则不会跳过。
RANK:1 2 2 4
DENSE_RANK():1 2 2 3
	SELECT EmployeeID, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
  	FROM Employees;


LAG() 和 LEAD()：获取当前行之前或之后的行的值。
   SELECT EmployeeID, Salary, LAG(Salary) OVER (ORDER BY Salary) AS PrevSalary
   FROM Employees;
   

SUM()、AVG()、COUNT()等聚合函数：在窗口内执行聚合操作。
   SELECT EmployeeID, Salary, AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgDeptSalary
   FROM Employees;
   

NTILE(n)：将行分入指定数量的桶（或组）中。
   SELECT EmployeeID, Salary, NTILE(4) OVER (ORDER BY Salary) AS Quartile
   FROM Employees;
   

窗口函数的应用非常广泛，特别是在数据分析和报表制作中，它们可以帮助进行复杂的分组分析、动态计算以及实现各种排名和百分位统计等。