CH05_模糊查询和聚合函数

CH05_模糊查询和聚合函数

本章目标

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

模糊查询

LIKE关键字查询

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

LIKE的语法如下：

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

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

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

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

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

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

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

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

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

BETWEEN 范围查询

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

BETWEEN 的语法如下：

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

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

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

IN多值查询

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

IN语法如下：

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

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

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

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

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

聚合函数

什么是聚合函数？

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

数学聚合函数

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

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

SELECT SUM(column_name) FROM table_name;

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

SELECT SUM(StudentResult) as 总成绩
FROM Result 

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

SELECT AVG(column_name) FROM table_name;

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

SELECT AVG(StudentResult) as 平均分
FROM Result

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

SELECT MIN(column_name) FROM table_name;

案例3：统计班级最低分

SELECT MIN(StudentResult) as 最低分
FROM Result 

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

SELECT MAX(column_name) FROM table_name;

案例4：统计班级最高分

SELECT MAX(StudentResult) as 最低分
FROM Result

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

SELECT MAX(column_name) FROM table_name;

案例5：统计班级总人数

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

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

SELECT CEILING(10.75) as 返回值;

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

SELECT FLOOR(10.75) as 返回值;

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

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

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

SELECT ABS(-11) AS 返回值;

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

SELECT RAND() AS 返回值;

逻辑运算符

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

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

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

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

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

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

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

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

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

• NOT:对条件进行否定。

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

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

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

逻辑聚合函数

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

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

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

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

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

字符串聚合函数

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

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

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

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

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

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

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

• REVERSE()：反转字符串。

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

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

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

时间聚合函数

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

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

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

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

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

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

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

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

SELECT GETDATE() AS CurrentDateTime;

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

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

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

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

• EOMONTH(): 返回指定日期所在月份的最后一天的日期。

SELECT EOMONTH('2024-01-01') AS EndOfMonth;

课后作业

1.查询学生表地址在天津学员信息

2.查询学生表手机号以135开头的学员信息

3.查询学生表年龄在1995~2000年之间学员信息

4.查询课程表JAVA所属课程信息

5.查询课程表JAVA除外课程信息

6.查询课程表一年级和三年级课程信息

7.统计全校在籍学员人数

8.统计在籍学员最大年龄

9.统计在籍学员最小年龄

---

参考答案