CH05_模糊查询和聚合函数

本章目标

掌握LIKE、BETWEEN、IN进行模糊查询
掌握聚合函数统计和汇总查询信息

模糊查询

LIKE关键字查询

LIKE 是 SQL 语言中的一种模式匹配操作符，它用于在查询中指定模糊匹配条件。在 LIKE 操作符中，可以使用两个通配符：百分号（%）和下划线（_）。百分号表示任意数量的字符，而下划线表示单个字符。

LIKE的语法如下：

1	SELECT * FROM 表名称 WHERE 列名称 LIKE '字符';

案例1：查询姓高的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName LIKE '高%';

1705564850172

案例2：查询姓名以丽结尾的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName LIKE '%丽';

1705566405157

案例3：查询名字中包含 “丽” 的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName LIKE '%丽%';

1705565161573

案例4：查询两字姓高的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName LIKE '高_';

1705566289143

BETWEEN 范围查询

BETWEEN 是一个 SQL 操作符，用于在 WHERE 子句中指定范围条件。它通常用于过滤结果集，只选择满足指定范围内的值。

BETWEEN 的语法如下：

1	SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2;

案例1：查询成绩表中在80~100分的考试成绩信息，成绩逆序排序

SELECT * 
FROM Result 
WHERE StudentResult BETWEEN 80 and 100
ORDER BY StudentResult DESC;

1705567364915

IN多值查询

在 SQL Server 中，IN 关键字用于在 WHERE 子句中指定多个可能的值。它可以用于筛选数据，返回符合指定条件的结果集。

IN语法如下：

1	SELECT * FROM 表名 WHERE 字段名 IN (值1, 值2, 值3);

案例2：查询三名指定姓名的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName IN ('高育良', '高洋', '严丽丽');

1705568030738

案例3：排除指定姓名以外的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName NOT IN ('高育良', '高洋', '严丽丽');

1705568344827

聚合函数

什么是聚合函数？

在 SQL Server 中，聚合函数用于对一组值执行计算，并返回单个值。SQL Server 提供了多种聚合函数，用于不同的计算需求。以下是一些常用的 SQL Server 聚合函数：

数学聚合函数

数学聚合函数是一类在数据库和编程中用于数学计算的函数。这些函数通常对一组数值进行计算，并返回一个单一的结果。以下是一些常用的数学聚合函数：

SUM(): 返回某列的总和。

1	SELECT SUM(column_name) FROM table_name;

案例1：成绩表统计所有学员的总成绩

1 2	SELECT SUM(StudentResult) as 总成绩 FROM Result

1705568930494

AVG(): 返回某列的平均值。

1	SELECT AVG(column_name) FROM table_name;

案例2：成绩表统计所有学员的平均成绩

1 2	SELECT AVG(StudentResult) as 平均分 FROM Result

1705569089522

MIN(): 返回某列的最小值。

1	SELECT MIN(column_name) FROM table_name;

案例3：统计班级最低分

1 2	SELECT MIN(StudentResult) as 最低分 FROM Result

1705570173852

MAX(): 返回某列的最大值。

1	SELECT MAX(column_name) FROM table_name;

案例4：统计班级最高分

1 2	SELECT MAX(StudentResult) as 最低分 FROM Result

1705570284954

COUNT(): 返回某列的行数。

1	SELECT MAX(column_name) FROM table_name;

案例5：统计班级总人数

1 2	SELECT COUNT(StudentNo) as 班级人数 FROM Student

1705570404450

CEILING():返回大于或等于给定表达式的最小整数。

1	SELECT CEILING(10.75) as 返回值;

1705570840472

FLOOR():返回不大于给定表达式的最大整数。

1	SELECT FLOOR(10.75) as 返回值;

1705570918226

ROUND():将数字四舍五入到指定的小数位数。

1	SELECT ROUND(10.756, 2) as 返回值。

1705571021705

ABS():返回某列值的绝对值。

1	SELECT ABS(-11) AS 返回值;

1705571829684

RAND() :函数返回一个介于 0 和 1 之间的随机浮点数

1	SELECT RAND() AS 返回值;

1705630098744

逻辑运算符

在SQL中，逻辑运算符用于在WHERE子句中组合多个条件。

AND:所有条件都必须为真，结果才为真。

1	SELECT column1, column2 FROM table_name WHERE column1 = 'A' AND column2 = 'B';

案例1：查找姓名为高洋，性别为男的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName = '高洋' AND Sex='男';

1705626252561

OR:只要有一个条件为真，结果就为真。

1	SELECT column1, column2 FROM table_name WHERE column1 = 'A' OR column2 = 'B';

案例2：查找姓王或住在东莞的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE StudentName LIKE  '王%' OR Address LIKE '东莞%';

1705626478622

NOT:对条件进行否定。

1	SELECT column1, column2 FROM table_name WHERE NOT (column1 = 'A' AND column2 = 'B');

案例3：查找不住在北京的学员信息

1
2
3

SELECT * 
FROM Student 
WHERE NOT(Address  LIKE '北京%');

1705626634581

逻辑聚合函数

IIF():类似于其他编程语言中的三元运算符，根据给定的条件返回两个值中的一个。

1	SELECT IIF(2=2,'正确的','错误的') as 返回值;

1705571224935

CASE:根据条件返回不同的值。
- 简单case表达式，它通过将表达式与一组简单的表达式进行比较来确定结果。
- case搜索表达式，它通过计算一组布尔表达式来确定结果。

案例4：根据性别返回不同的结果

SELECT 
  CASE sex
    WHEN '男' THEN '男性'  
    WHEN '女' THEN '女性'  
    ELSE '保密'  
  END AS '性别'  
FROM Student;

1705627500417

字符串聚合函数

字符串聚合函数是用于处理和组合字符串数据的函数。这些函数在数据库查询和编程中非常常见，尤其是在处理文本数据时。以下是一些常用的字符串聚合函数：

LEFT() / RIGHT() / SUBSTRING():这些函数用于从字符串中提取子字符串。

--提取左侧字符：
SELECT LEFT('example', 4); --返回 'exam'
--提取右侧字符：
SELECT RIGHT('example', 4); --返回 'xample'
--提取子字符串：
SELECT SUBSTRING('example', 3, 4); --返回 'lpha'

LOWER() / UPPER():函数用于将字符串转换为小写或大写。

1 2	SELECT LOWER('EXAMPLE'); --返回 'example' SELECT UPPER('example'); --返回 'EXAMPLE'

LEN()：返回字符串的长度

1	SELECT LEN('example'); --返回 5

REVERSE()：反转字符串。

1	SELECT REVERSE('example'); --返回 'emexa'

CONCAT():将多个字符串值连接起来。

1	SELECT CONCAT('app', 'liaction'); --返回 'appliaction'

时间聚合函数

时间聚合函数是用于处理和计算与时间相关的数据的函数。在 SQL Server 中，有一些内置的时间聚合函数，它们可以帮助您对日期和时间值进行操作和计算。以下是一些常用的时间聚合函数：

DATEDIFF(): 返回两个日期之间的差异

1	SELECT DATEDIFF(day, '日期时间1', '日期时间2')

案例1：计算两个时间相隔天数

1	SELECT DATEDIFF(day, '2023-01-01', '2023-01-02') AS DiffDays

1705570646566

DATEADD(): 此函数用于在日期上添加或减去指定的时间间隔。

1	SELECT DATEADD(day, 5, '2020-01-01') AS NewDate;

1705627922188

GETDATE(): 返回当前日期和时间。

1	SELECT GETDATE() AS CurrentDateTime;

1705628043519

YEAR()/ MONTH()/DAY(): 分别返回日期的年份和月份及天数。

1
2
3

SELECT YEAR('2024-01-20') AS Year, 
	   MONTH('2024-01-20') AS Month,
	   DAY('2024-01-20') AS Day;

1705628288984

DATEPART()：用于从日期或时间值中提取指定的部分，如年、月、日、小时、分钟或秒等

server

SELECT CONVERT(VARCHAR, GETDATE(), 121) AS CurrentDateTime,  
       DATEPART(HOUR, GETDATE()) AS Hour,  
       DATEPART(MINUTE, GETDATE()) AS Minute,  
       DATEPART(SECOND, GETDATE()) AS Second;