🔍SQL Server常用语句【查询语句】-习听风雨丨专注WorkFine开发

💡 简记：增(INSERT)删(DELETE)改(UPDATE)查(SELECT)是数据库操作的四大基础功能，简称CRUD（Create, Read, Update, Delete）。

本文目录 显示

查 (SELECT)

作用：从表中检索数据
示例：SELECT 字段 FROM 表名 WHERE 条件
用途：获取信息，如查询订单、生成报表、数据分析

以下是使用USE语句先指定数据库，然后执行查询操作的完整SQL示例：

1. 基础查询

-- 切换到目标数据库
USE [数据库名];
GO

-- 简单查询
SELECT [字段1], [字段2], [字段3]
FROM [架构名].[表名]
WHERE [条件字段] = 条件值;
GO

示例：

USE [销售数据库];
GO

SELECT [订单编号], [客户名称], [订单金额], [下单时间]
FROM [dbo].[订单表]
WHERE [订单状态] = '待付款';
GO

2. 多条件查询

USE [人力资源数据库];
GO

SELECT [员工编号], [员工姓名], [部门], [职位], [基本工资]
FROM [hr].[员工表]
WHERE 
    [部门] = '技术部'
    AND [入职日期] > '2020-01-01'
    AND [在职状态] = '在职';
GO

3. 排序查询

USE [库存数据库];
GO

SELECT [产品编号], [产品名称], [库存数量], [最近入库日期]
FROM [dbo].[产品表]
WHERE [库存数量] < 100
ORDER BY [库存数量] ASC, [产品名称] DESC;
GO

4. 分组统计查询

USE [销售数据库];
GO

SELECT 
    [销售区域],
    COUNT(*) AS [订单数量],
    SUM([订单金额]) AS [总金额],
    AVG([订单金额]) AS [平均金额]
FROM [dbo].[订单表]
WHERE YEAR([下单时间]) = 2023
GROUP BY [销售区域]
HAVING COUNT(*) > 50
ORDER BY [总金额] DESC;
GO

5. 多表关联查询

USE [电商数据库];
GO

SELECT 
    o.[订单编号],
    c.[客户姓名],
    p.[产品名称],
    o.[购买数量],
    o.[订单金额]
FROM [dbo].[订单表] o
INNER JOIN [dbo].[客户表] c ON o.[客户ID] = c.[客户ID]
INNER JOIN [dbo].[产品表] p ON o.[产品ID] = p.[产品ID]
WHERE o.[订单状态] = '已完成'
    AND o.[下单时间] BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY o.[下单时间] DESC;
GO

6. 分页查询

USE [内容管理数据库];
GO

-- SQL Server 2012及以上版本
SELECT [文章ID], [文章标题], [作者], [发布时间], [阅读量]
FROM [dbo].[文章表]
WHERE [分类] = '科技'
ORDER BY [发布时间] DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
GO

-- SQL Server 2008及以下版本
SELECT TOP 10 *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [发布时间] DESC) AS RowNum,
        [文章ID], [文章标题], [作者], [发布时间], [阅读量]
    FROM [dbo].[文章表]
    WHERE [分类] = '科技'
) AS TempTable
WHERE RowNum > 20;
GO