SQL Server 常用操作、SQL整理

参考

环境

软件/系统	版本	说明
Windows	windows 10 专业版 22H2 64 位操作系统, 基于 x64 的处理器	客户机
Microsoft Visual Studio	Community 2022 (64 位) - Current 版本 17.13.6	客户机
.NET SDK	9.0.203	客户机
SQL Server Management Studio	20.2.37.0	客户机
Windows Server	Windows Server 2019 Standard Evaluation (10.0)	服务器
SQL Server	Microsoft SQL Server 2019 Enterprise Edition	服务器

正文

本文只是部分记录和验证，不代表仅仅只有这些语法。

演示环境准备

创建数据库与数据表

USE [School_2025_Xiaqiuchu]
GO
/****** Object:  Table [dbo].[ClassRoom_Course_Teacher]    Script Date: 2025/5/8 10:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClassRoom_Course_Teacher](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ClassRoomId] [int] NULL,
	[CourseId] [int] NULL,
	[TeacherId] [int] NULL,
 CONSTRAINT [PK_ClassRoom_Course_Teacher] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ClassRooms]    Script Date: 2025/5/8 10:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClassRooms](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_ClassRooms] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Courses]    Script Date: 2025/5/8 10:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Courses](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Students]    Script Date: 2025/5/8 10:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Students](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Age] [tinyint] NULL,
	[ClassRoomId] [int] NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Teachers]    Script Date: 2025/5/8 10:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teachers](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Age] [tinyint] NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ClassRooms', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'课程名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Courses', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学生姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Students', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Students', @level2type=N'COLUMN',@level2name=N'Age'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Students', @level2type=N'COLUMN',@level2name=N'ClassRoomId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'老师名字' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Teachers', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'老师年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Teachers', @level2type=N'COLUMN',@level2name=N'Age'
GO

填充模拟数据

-- 创建课程
INSERT INTO [dbo].[Courses]
		   ([Name])
	 VALUES
		   ('语文'), ('数学'), ('英语'), ('历史'),('地理'),('生物')
GO
-- 创建老师
INSERT INTO Teachers
		   (Name,Age)
	 VALUES
		   ('赵老师',22), ('钱老师',26),('周老师',43),('王老师',38),('李老师',26),('刘老师',55),('孙老师',52),('薛老师',30),('吴老师',33),('毕老师',35)
GO
-- 创建教室
INSERT INTO ClassRooms
		   (Name)
	 VALUES
		   ('一年级1班'),('一年级2班'),('二年级1班'),('二年级2班'),('三年级1班'),('三年级2班')
GO
-- 创建学生
INSERT INTO Students (Name, Age, ClassRoomId) VALUES ('张小明', 7, 1),('王丽华', 6, 1),('李志强', 7, 1),('刘芳芳', 6, 1),('陈小伟', 7, 1),('周雨婷', 6, 2),('吴天宇', 7, 2),('徐萌萌', 6, 2),('孙浩然', 7, 2),('朱心怡', 6, 2),('郑博文', 8, 3),('高静雅', 7, 3),('林宇航', 8, 3),('何诗涵', 7, 3),('罗俊杰', 8, 3),('梁思琪', 7, 4),('谢振轩', 8, 4),('宋佳琪', 7, 4),('唐一凡', 8, 4),('邓欣妍', 7, 4),('许嘉豪', 9, 5),('董雨欣', 8, 5),('崔俊熙', 9, 5),('贾若曦', 8, 5),('丁浩然', 9, 5),('马欣怡', 8, 6),('钟子轩', 9, 6),('彭雨桐', 8, 6),('袁博涛', 9, 6),('曾佳慧', 8, 6);
GO
-- 创建教室、课程、老师的关系
INSERT INTO ClassRoom_Course_Teacher (ClassRoomId, CourseId, TeacherId)
VALUES
-- 班级1（一年级1班）：6门课程，老师ID 1~6
(1, 1, 1), (1, 2, 2), (1, 3, 3), (1, 4, 4), (1, 5, 5), (1, 6, 6),
-- 班级2（一年级2班）：6门课程，老师ID 7~10, 1~2
(2, 1, 7), (2, 2, 8), (2, 3, 9), (2, 4, 10), (2, 5, 1), (2, 6, 2),
-- 班级3（二年级1班）：6门课程，老师ID 3~8
(3, 1, 3), (3, 2, 4), (3, 3, 5), (3, 4, 6), (3, 5, 7), (3, 6, 8),
-- 班级4（二年级2班）：6门课程，老师ID 9~10, 1~4
(4, 1, 9), (4, 2, 10), (4, 3, 1), (4, 4, 2), (4, 5, 3), (4, 6, 4),
-- 班级5（三年级1班）：6门课程，老师ID 5~10
(5, 1, 5), (5, 2, 6), (5, 3, 7), (5, 4, 8), (5, 5, 9), (5, 6, 10),
-- 班级6（三年级2班）：6门课程，老师ID 1~6
(6, 1, 1), (6, 2, 2), (6, 3, 3), (6, 4, 4), (6, 5, 5), (6, 6, 6);
GO

DCL 数据控制语言

数据控制（授权用户数据库、表权限时，需要先选择数据库）

-- 选择指定数据库，后续所有授权都基于本数据库
USE Xiaqiuchu;

-- 创建 SQL Server 登录账户（用于身份验证）
CREATE LOGIN Xiaqiuchu_Login WITH PASSWORD = 'Xiaqiuchu@2025';
-- 创建用户并映射到登录账户
CREATE USER Xiaqiuchu_User FOR LOGIN Xiaqiuchu_Login WITH DEFAULT_SCHEMA = dbo;
-- 删除用户（需先处理关联的架构或对象）
DROP USER IF EXISTS Xiaqiuchu_User;
-- 删除登录账户
DROP LOGIN Xiaqiuchu_Login;
-- 修改默认架构
ALTER USER Xiaqiuchu_User WITH DEFAULT_SCHEMA = sales;  
-- 查看所有登录账户
SELECT name AS LoginName FROM sys.server_principals WHERE type_desc = 'SQL_LOGIN';
-- 查看数据库用户
SELECT name AS UserName FROM sys.database_principals WHERE type_desc = 'SQL_USER';
-- 选择指定数据库，后续所有授权都基于本数据库
USE School_2025_Xiaqiuchu;
-- 授予服务器级角色（如 sysadmin，慎用！）
-- ALTER SERVER ROLE sysadmin ADD MEMBER Xiaqiuchu_Login;
-- 授予只读权限（db_datareader）
ALTER ROLE db_datareader ADD MEMBER Xiaqiuchu_User;
-- 授予读写权限（db_datawriter）
ALTER ROLE db_datawriter ADD MEMBER Xiaqiuchu_User;
-- 授予 DDL 权限（db_ddladmin）
ALTER ROLE db_ddladmin ADD MEMBER Xiaqiuchu_User;
-- 授予完全控制（db_owner，慎用！）
ALTER ROLE db_owner ADD MEMBER Xiaqiuchu_User;
-- 授予对某个架构的所有权限（如 dbo 架构）
-- GRANT CONTROL ON SCHEMA::dbo TO Xiaqiuchu_User;
-- 允许用户在架构中创建对象
GRANT CREATE TABLE, CREATE VIEW TO Xiaqiuchu_User;
-- 授予 SELECT、INSERT 权限
GRANT SELECT, INSERT ON Students TO Xiaqiuchu_User;
-- 授予 UPDATE 特定列权限
GRANT UPDATE (Name, Age) ON Students TO Xiaqiuchu_User;
-- 授予所有操作权限
GRANT ALL ON Students TO Xiaqiuchu_User;
-- 回收 SELECT 权限
REVOKE SELECT ON Students FROM Xiaqiuchu_User;
-- 拒绝 DELETE 权限（优先级高于 GRANT）
DENY DELETE ON Students TO Xiaqiuchu_User;
-- 移除用户从角色
ALTER ROLE db_datareader DROP MEMBER Xiaqiuchu_User;
-- 创建自定义角色
CREATE ROLE Xiaqiuchu_Auditor;
-- 授予角色权限
GRANT SELECT, INSERT ON AuditLog TO Xiaqiuchu_Auditor;
-- 将用户添加到角色
ALTER ROLE Xiaqiuchu_Auditor ADD MEMBER Xiaqiuchu_User;
-- 查看用户直接授予的权限
SELECT 
	permission_name,
	state_desc,
	object_name(major_id) AS ObjectName
FROM sys.database_permissions 
WHERE grantee_principal_id = USER_ID('Xiaqiuchu_User');
-- 查看数据库角色成员
SELECT 
	r.name AS RoleName,
	m.name AS MemberName
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id;

DDL 数据定义语言

定义

-- 创建数据库（使用默认值补充相关文件位置）
CREATE DATABASE Qiu_Chu_2025;
-- 创建数据库（显式指定相关配置）
CREATE DATABASE Qiu_Chu_2025 ON
(NAME = Qiu_Chu_2025_dat,
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Qiu_Chu_2025_dat.mdf',
	SIZE = 10,
	MAXSIZE = 50,
	FILEGROWTH = 5)
LOG ON
(NAME = Qiu_Chu_2025_log,
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Qiu_Chu_2025_log.ldf',
	SIZE = 5 MB,
	MAXSIZE = 25 MB,
	FILEGROWTH = 5 MB);
GO

-- 删除数据库
-- DROP DATABASE Qiu_Chu_2025 ;  

-- 不建议的命令
-- 修改数据库 重命名（无法重命名系统数据库、在其他用户正在访问数据库时，无法更改数据库名称、需要对数据库拥有 ALTER 权限）
-- 将数据库设置为单用户模式，修改后再回复为多用户模式；修改数据库名称，此操作会立即中断所有活跃连接，可能导致业务中断或数据操作回滚。建议在维护窗口执行。
ALTER DATABASE Qiu_Chu_2025 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE Qiu_Chu_2025 MODIFY NAME = Qiu_Chu_2026;
GO
ALTER DATABASE Qiu_Chu_2026 SET MULTI_USER;
GO
-- 如果要重命名的数据库被设置为 SQL Server 登录名的默认数据库，则可能会遇到错误 4064 Can't open user default database。 请使用以下命令将默认设置更改为已重命名的数据库：
-- USE [master]
-- GO
-- ALTER LOGIN [login] WITH DEFAULT_DATABASE=[new-database-name];
-- GO

-- 移动数据库(1. 分离  2. 移动文件 3. 附加)
EXEC sp_detach_db @dbname = N'Qiu_Chu_2026';  
GO  
-- 手动移动数据库文件 将数据库文件（Qiu_Chu_2026.mdf 和 Qiu_Chu_2026_log）分别复制到需要移动的位置，如：C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Qiu_Chu_2025_dat.mdf 和 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Qiu_Chu_2025_log.ldf
CREATE DATABASE Qiu_Chu_2026   
	ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Qiu_Chu_2025_dat.mdf'),  
	(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Qiu_Chu_2025_log.ldf')  
	FOR ATTACH;  
GO  

-- 数据库快照、生成与恢复（恢复并不适用于介质恢复。 数据库快照是不完整的数据库文件副本，因此，如果数据库或数据库快照损坏，则不可能从快照进行恢复。）

use School_2025_Xiaqiuchu;
GO

-- 计算值{expression} = NULL和{expression} <> NULLFalse值{expression}是否为 NULL。 此行为符合 ANSI
SET ANSI_NULLS ON
GO
-- 使 SQL Server 遵从关于引号分隔标识符和文字字符串的 ISO 规则。 由双引号分隔的标识符可以是 Transact-SQL 保留关键字，也可以包含 Transact-SQL 标识符语法规则通常不允许的字符。
SET QUOTED_IDENTIFIER ON
GO

-- 查看当前默认文件组
SELECT 
	name AS [Filegroup Name],
	is_default AS [Is Default]
FROM sys.filegroups;

-- 查看所有表
SELECT * FROM sys.tables;
-- 查看表结构
EXEC sp_help 'Employees';
-- 查看表的约束
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'Employees';
-- 查看表的索引
EXEC sp_helpindex 'Employees';
-- 创建表
CREATE TABLE Employees (
	-- 设置主键 PRIMARY KEY 与自增 IDENTITY(1,1)
	EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
	-- 设置默认值
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	BirthDate DATE,
	-- 设置自动填充默认值
	HireDate DATETIME DEFAULT GETDATE(),
	-- 不指定则允许NULL
	DepartmentID INT,
	-- 不指定则允许NULL
	Salary DECIMAL(10,2)
) ON [PRIMARY] ; -- 显式指定文件组 
-- 添加主键
ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);
-- 创建非聚集索引
CREATE INDEX IX_LastName ON Employees(LastName);
CREATE INDEX IX_LastName_DepartmentID ON Employees (LastName, DepartmentID);
-- 创建唯一索引（确保 Email 唯一）
CREATE UNIQUE INDEX UQ_Email ON Employees (Email);
-- 唯一复合索引（确保 FirstName + LastName 组合唯一）
CREATE UNIQUE INDEX UQ_FullName ON Employees (FirstName, LastName);
-- 唯一聚集索引
-- CREATE UNIQUE CLUSTERED INDEX IX_EmployeeID ON Employees(EmployeeID);
-- 删除索引
DROP INDEX IX_LastName ON Employees;
-- 唯一约束
ALTER TABLE Employees ADD CONSTRAINT UQ_Email UNIQUE (Email);
-- 检查约束 -- 限制工资必须大于 0
ALTER TABLE Employees ADD CONSTRAINT CHK_Salary CHECK (Salary > 0);
-- 删除约束
ALTER TABLE Employees DROP CONSTRAINT UQ_Email;
-- 添加字段
ALTER TABLE Employees ADD Email NVARCHAR(100) NULL; 
-- 删除字段
ALTER TABLE Employees DROP COLUMN Email;
-- 为 Employees 表的 Salary 列添加注释
EXEC sp_addextendedproperty 
	@name = N'MS_Description', 
	@value = N'员工月薪（税前）',
	@level0type = N'SCHEMA', @level0name = 'dbo',
	@level1type = N'TABLE', @level1name = 'Employees',
	@level2type = N'COLUMN', @level2name = 'Salary';
-- 查看 Employees 表所有列的注释
SELECT 
	obj.name AS [Table],
	col.name AS [Column],
	ep.value AS [Description]
FROM sys.extended_properties ep
INNER JOIN sys.objects obj ON ep.major_id = obj.object_id
INNER JOIN sys.columns col 
	ON ep.major_id = col.object_id 
	AND ep.minor_id = col.column_id
WHERE 
	obj.name = 'Employees' 
	AND ep.name = 'MS_Description';
-- 修改数据类型
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2); 
-- 重命名字段（引用内置存储过程）
EXEC sp_rename 'Employees.Email', 'Email1', 'COLUMN';
-- 重命命名表
EXEC sp_rename 'Employees', 'Employees1';
-- 复制表结构（不复制数据）
SELECT * INTO Employees_Backup FROM Employees WHERE 1 = 0;
-- 复制表结构（包含数据）
-- SELECT * INTO Employees_Backup FROM Employees;
-- 删除表 （先检查表是否存在）
IF OBJECT_ID('Employees', 'U') IS NOT NULL
	DROP TABLE Employees;

-- 创建存储过程
CREATE PROCEDURE Xiaqiuchu_InsertStudent
	@Name NVARCHAR(50),
	@Age INT
AS
BEGIN
	-- 校验年龄是否合法（假设年龄范围 6~30）
	IF @Age < 6 OR @Age > 30
	BEGIN
		RAISERROR('年龄必须在 6 到 30 岁之间！', 16, 1);
		RETURN;
	END

	-- 插入数据
	INSERT INTO Students (Name, Age)
	VALUES (@Name, @Age);

	-- 返回新生成的 Id
	SELECT SCOPE_IDENTITY() AS NewStudentID;
END
GO

-- 修改存储过程
ALTER PROCEDURE Xiaqiuchu_InsertStudent
	@Name NVARCHAR(50),
	@Age INT
AS
BEGIN
	-- 校验年龄是否合法（假设年龄范围 20~60）
	IF @Age < 20 OR @Age > 60
	BEGIN
		RAISERROR('年龄必须在 20 到 60 岁之间！', 16, 1);
		RETURN;
	END

	-- 插入数据
	INSERT INTO Students (Name, Age)
	VALUES (@Name, @Age);

	-- 返回新生成的 Id
	SELECT SCOPE_IDENTITY() AS NewStudentID;
END
GO

-- 查看存储过程定义
EXEC sp_helptext 'Xiaqiuchu_InsertStudent';

-- 查询所有存储过程（系统视图）
SELECT name AS ProcedureName
FROM sys.procedures
WHERE name LIKE 'Xiaqiuchu_%';


-- 使用存储过程
EXEC Xiaqiuchu_InsertStudent @Name = '张三', @Age = 44; 

-- 删除存储过程
DROP PROCEDURE IF EXISTS Xiaqiuchu_InsertStudent;

-- 创建触发器
CREATE TRIGGER Xiaqiuchu_LogInsert
ON Students
AFTER INSERT
AS
BEGIN
	PRINT '新学生已插入！';
END
GO
-- 修改触发器：添加日志记录
ALTER TRIGGER Xiaqiuchu_LogInsert
ON Students
AFTER INSERT
AS
BEGIN
	DECLARE @Count INT = (SELECT COUNT(*) FROM inserted);
	PRINT '新增学生数量：' + CAST(@Count AS VARCHAR);
END
GO

-- 查看触发器定义
EXEC sp_helptext 'Xiaqiuchu_LogInsert';

-- 查询所有触发器（系统视图）
SELECT 
	t.name AS TriggerName,
	o.name AS TableName
FROM sys.triggers t
INNER JOIN sys.objects o ON t.parent_id = o.object_id
WHERE t.name LIKE 'Xiaqiuchu_%';

-- 删除触发器
DROP TRIGGER IF EXISTS Xiaqiuchu_LogInsert;

-- 创建视图
CREATE VIEW Xiaqiuchu_AdultStudents
AS
	SELECT Id, Name, Age
	FROM Students
	WHERE Age >= 18;
GO

-- 修改视图：添加年龄分组
ALTER VIEW Xiaqiuchu_AdultStudents
AS
	SELECT 
		Id, 
		Name, 
		Age,
		CASE 
			WHEN Age BETWEEN 18 AND 25 THEN '青年'
			ELSE '成年'
		END AS AgeGroup
	FROM Students
	WHERE Age >= 18;
GO

-- 查看视图定义
EXEC sp_helptext 'Xiaqiuchu_AdultStudents';

-- 查询所有视图（系统视图）
SELECT name AS ViewName
FROM sys.views
WHERE name LIKE 'Xiaqiuchu_%';

-- 禁用触发器
DISABLE TRIGGER Xiaqiuchu_LogInsert ON Students;

-- 启用触发器
ENABLE TRIGGER Xiaqiuchu_LogInsert ON Students;

-- 强制刷新视图元数据（如基表结构变更后）
EXEC sp_refreshview 'Xiaqiuchu_AdultStudents';

-- 删除视图
DROP VIEW IF EXISTS Xiaqiuchu_AdultStudents;

DQL 数据查询语言

查询

-- 选择并进入数据库
USE [School_2025_Xiaqiuchu]
GO
-- 查询全部
SELECT * FROM Students;
-- 查询列别名
SELECT Name name, Age age  FROM Students;
SELECT Name AS  name, Age AS age  FROM Students;
-- 查询表列别名
SELECT s.*  FROM Students s;
SELECT s.*  FROM Students AS s;
-- 排序 正序
SELECT *  FROM Students ORDER BY Age;
SELECT *  FROM Students ORDER BY Age ASC;
-- 排序 倒序
SELECT *  FROM Students ORDER BY Age DESC;
-- 条件查询
SELECT *  FROM Students WHERE Name = '许嘉豪';
-- 多条件查询
SELECT *  FROM Students WHERE Name IS NOT NULL AND Name = '许嘉豪';
-- In 查询
SELECT *  FROM Students WHERE Name IN ( '许嘉豪','张小明');
SELECT *  FROM Students WHERE Id IN ( 3, 5);
-- 模糊查询
SELECT *  FROM Students WHERE Name LIKE '许%';
SELECT *  FROM Students WHERE Name LIKE '%李%';
-- 范围查询
-- 没有时间字段，仅作演示
-- SELECT *  FROM Students WHERE Create_Date BETWEEN '2023-01-01' AND '2023-12-31';
SELECT *  FROM Students WHERE Id BETWEEN 1 AND 5;
-- NULL 值查询
SELECT * FROM Students WHERE Name IS NULL;
SELECT * FROM Students WHERE Name IS NOT NULL;
SELECT * FROM Students;

-- 聚合函数  数量
SELECT COUNT(*) as COUNTStudent FROM Students;
-- 聚合函数  总和
SELECT SUM(Age) AS SUMAge FROM Students;
-- 聚合函数 平均数
SELECT AVG(Age) AS AVGAge FROM Students;
-- 聚合函数 查找最大和最低小龄
SELECT MAX(Age) AS MaxAge, MIN(Age) AS MinAge FROM Students;
-- 聚合函数 统计年龄差
SELECT STDEV(Age) AS STDEVAge FROM Students;
-- 常用函数 系统目前的日期与时间
SELECT GetDate() as Date;
-- 常用函数 获取对应 年、月、日
SELECT
	YEAR('2025-05-08 12:00:00') AS Year,
	MONTH ('2025-05-08 12:00:00') AS Month,
	DAY ('2025-05-08 12:00:00') AS DAY;
SELECT
	YEAR('2025-05-08') AS Year,
	MONTH ('2025-05-08') AS Month,
	DAY ('2025-05-08') AS DAY;
-- 常用函数 获取对应的日期的部分,参数是合法的日期表达式即可
SELECT
	DATEPART(year, '2025-05-08 12:00:00') AS year,
	DATEPART(yyyy, '2025-05-08 12:00:00') AS yyyy,
	DATEPART(month, '2025-05-08') AS month,
	DATEPART(mm, '2025-05-08') AS mm,
	DATEPART(day, '2025-05-08 12:00:00') AS day,
	DATEPART(dd, '2025-05-08 12:00:00') AS dd,
	DATEPART(hour, '2025-05-08 12:00:00') AS hour,
	DATEPART(hh, '2025-05-08 12:00:00') AS hh,
	DATEPART(minute, '2025-05-08 12:00:00') AS minute,
	DATEPART(second, '2025-05-08 12:00:00') AS second;
-- 常用部分 获取日期差值
SELECT 
	DateDiff(year,'2005-07-20','2015-7-25 22:56:32') as year,
	DateDiff(month,'2005-07-20','2015-7-25 22:56:32') as month,
	DateDiff(day,'2005-07-20','2015-7-25') as day,
	DateDiff(hour,'2005-07-20','2015-7-25 22:56:32') as hour,
	DateDiff(minute,'2005-07-20','2015-7-25 22:56:32') as minute;
-- 获取日期中的苏哦在某年、季度、月、一周的日数、一年中的第几日、天
SELECT 
	DatePart(year,'2005-7-25 22:56:32') as year,
	DatePart(quarter,'2005-7-25') as quarter,
	DatePart(month,'2005-7-25') as month,
	DatePart(DW,'2005-7-25') as dw,
	DatePart(DY,'2005-7-25') as dy,
	DatePart(day,'2005-7-25 22:56:32') as day,
	DatePart(hour,'2005-7-25 22:56:32') as hour,
	DatePart(minute,'2005-7-25 22:56:32') as minute,
	DatePart(second,'2005-7-25 22:56:32') as second;
-- 时间计算
SELECT DATEADD(day,2,'2004-10-15') ;
-- 分页查询 查询范围数据 前n条
SELECT Top 10 * FROM Students;
-- 分页查询 查询范围数据 前n%条
SELECT Top 10 Percent * FROM Students;
-- 分页查询（SQL Server 2012 及以上版本支持 OFFSET FETCH 分页）
DECLARE @PageNumber INT = 1;  -- 当前页码
DECLARE @PageSize INT = 10;    -- 每页行数

SELECT *
FROM Students
ORDER BY Id  -- 必须指定排序字段
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- 查询列与计算
SELECT Id,Name,Age * 10 AS Age  FROM Students WHERE Name = '许嘉豪';
-- 去重查询（针对单列或多列）
SELECT DISTINCT ClassRoomId FROM Students;
SELECT DISTINCT ClassRoomId,Name FROM Students;
-- 分组查询
SELECT MAX(Id) as Max_Id, ClassRoomId FROM Students GROUP BY ClassRoomId ORDER BY ClassRoomId;
-- HAVING 
SELECT MAX(Id) as Max_Id, ClassRoomId FROM Students GROUP BY ClassRoomId HAVING ClassRoomId > 1 ORDER BY ClassRoomId;
-- 内连接 返回两个表中匹配的行
SELECT 
	s.Name,
	c.Name
FROM Students s
INNER JOIN ClassRooms c 
	ON s.ClassRoomId = c.Id;

-- 左连接 返回左表所有行，右表无匹配时显示 NULL。
SELECT 
	s.Name,
	c.Name
FROM Students s
LEFT JOIN ClassRooms c 
	ON s.ClassRoomId = c.Id AND c.Name = '一年级1班'

-- 右连接 返回右表所有行，左表无匹配时显示 NULL。
SELECT 
	s.Name,
	c.Name
FROM Students s 
RIGHT JOIN ClassRooms c 
	ON s.ClassRoomId = c.Id AND c.Name = '一年级1班'


-- 全外连接 返回两个表的所有行，无匹配时显示 NULL。
SELECT 
	s.Name,
	c.Name
FROM Students s 
FULL  JOIN ClassRooms c 
	ON s.ClassRoomId = c.Id AND c.Name = '一年级1班'

-- 交叉连接 返回两个表的笛卡尔积（所有可能的行组合）。
SELECT 
	s.Name,
	c.Name
FROM Students s 
CROSS JOIN ClassRooms c;

-- 自关联查询 将表与自身关联，常用于层级数据（如员工与上级经理）。

-- 多表关联
SELECT 
	cr.Name as ClassRoom,
	c.Name as Course,
	t.Name as Teacher
FROM ClassRooms cr 
INNER JOIN ClassRoom_Course_Teacher cct ON cct.ClassRoomId = cr.Id
INNER JOIN Courses c ON c.Id = cct.CourseId
INNER JOIN Teachers t on t.Id = cct.TeacherId;
-- 子查询 （EXISTS 检查子查询是否返回任何行。如果子查询返回至少一行，那么 `EXISTS` 子句的结果为 `TRUE`；）
SELECT * FROM Students WHERE EXISTS(SELECT * FROM ClassRooms WHERE Name = '一年级1班');
-- 子查询 （IN 如果子查询返回至少一行与外部查询中的值匹配的行，那么 `IN` 子句的结果为 `TRUE`）
SELECT * FROM Students WHERE ClassRoomId IN (SELECT Id FROM ClassRooms WHERE Name = '一年级1班');
-- UNION  去重
SELECT *  FROM Students WHERE Name = '许嘉豪'
UNION
SELECT *  FROM Students WHERE Name = '张小明'
OPTION (MERGE UNION);
GO
-- UNION ALL 显示所有
SELECT *  FROM Students WHERE Name = '许嘉豪'
UNION ALL
SELECT *  FROM Students WHERE Name = '许嘉豪'
OPTION (MERGE UNION);
GO

DML 数据操作语言

DML

-- 插入单条数据（明确指定列）
INSERT INTO Students(Name, Age, ClassRoomId) VALUES ('张三', 9, 8);
-- 插入单条数据（省略列名，需按表结构顺序填充所有列）
INSERT INTO Students VALUES ('张三', 9, 8);
-- 插入多行数据
INSERT INTO Students VALUES ('张三', 9, 8),  ('张三', 9, 8),  ('张三', 9, 8);
-- 通过 SELECT 插入其他表的数据
INSERT INTO Students (Name, Age, ClassRoomId)
	SELECT Name, Age, ClassRoomId
	FROM Students
	WHERE Name = '张三';
-- SELECT INTO 创建临时表(本地、全局两种；不同类型自动删除模式不一样) SQL Server 2016 (13.x) SP2 和更高版本。
IF OBJECT_ID(N'#Temp_Table', N'U') IS NOT NULL
DROP TABLE #Temp_Table;
GO
-- 临时表查询 SQL Server 2016 (13.x) SP2 和更高版本。
SELECT *
INTO #Temp_Table
FROM Students
WHERE Id > 1;
GO
-- SELECT INTO 创建永久表 SQL Server 2016 (13.x) SP2 和更高版本。
IF OBJECT_ID(N'Temp_Table', N'U') IS NOT NULL
DROP TABLE Temp_Table;
GO
-- 永久表查询
SELECT *
INTO Temp_Table
FROM Students
WHERE Id > 1;
GO
-- 更新数据(切记更新带上条件，否则会造成全表更新)
UPDATE Students SET Name='李四' WHERE Name = '张三';
-- 删除数据
DELETE FROM Students WHERE Name = '李四';

-- 删除所有记录（慎用！）
-- DELETE FROM Students;  -- 逐行删除，可回滚
-- 清空表（快速删除，不可回滚）
-- TRUNCATE TABLE Students;  -- 重置自增列，不记录日志

-- 显式事务操作 
BEGIN TRANSACTION;  
-- 执行插入/更新/删除操作
INSERT INTO Students (Name, Age, ClassRoomId) VALUES ('李四', 10, 2);
-- COMMIT TRANSACTION;  
ROLLBACK TRANSACTION;

其他

语言元素参考 https://learn.microsoft.com/zh-cn/sql/t-sql/language-elements/language-elements-transact-sql?view=sql-server-ver16 ，支持变量、流程控制等等。

数据库备份、恢复(需要时请先在测试环境验证后再操作正式数据库)

手动备份

-- 完整备份 备份到本地磁盘
BACKUP DATABASE School_2025_Xiaqiuchu
TO DISK = 'D:\Backup\School_2025_Xiaqiuchu_Full.bak'
WITH 
	NAME = 'Full Backup School_2025_Xiaqiuchu',
	COMPRESSION,  -- 启用压缩（减少备份大小）
	STATS = 5;    -- 每完成5%显示进度
-- 差异备份 基于完整备份的增量备份
BACKUP DATABASE School_2025_Xiaqiuchu
TO DISK = 'D:\Backup\School_2025_Xiaqiuchu_Diff.bak'
WITH 
	DIFFERENTIAL,  -- 差异备份
	NAME = 'Diff Backup School_2025_Xiaqiuchu',
	STATS = 5;

-- 仅备份事务日志（需数据库处于完整恢复模式）
BACKUP LOG School_2025_Xiaqiuchu
TO DISK = 'D:\Backup\School_2025_Xiaqiuchu_Log.trn'
WITH 
	NAME = 'Log Backup School_2025_Xiaqiuchu',
	STATS = 5;

-- 从完整备份恢复（覆盖原数据库）
RESTORE DATABASE School_2025_Xiaqiuchu
FROM DISK = 'D:\Backup\School_2025_Xiaqiuchu_Full.bak'
WITH 
	REPLACE,  -- 强制覆盖现有数据库
	RECOVERY;  -- 完成恢复并允许访问
-- 需完整备份 + 日志备份
USE master;
GO

-- 恢复完整备份（NORECOVERY 状态）
RESTORE DATABASE School_2025_Xiaqiuchu
FROM DISK = 'D:\Backup\School_2025_Xiaqiuchu_Full.bak'
WITH NORECOVERY;

-- 恢复事务日志到指定时间
RESTORE LOG School_2025_Xiaqiuchu
FROM DISK = 'D:\Backup\School_2025_Xiaqiuchu_Log.trn'
WITH 
	STOPAT = '2023-10-01 12:00:00',  -- 指定时间点
	RECOVERY;  -- 完成恢复

自动化备份
1. 使用 SQL Server 代理作业
  打开 SQL Server Management Studio (SSMS) → SQL Server 代理 → 右键作业 → 新建作业。
  在步骤中添加备份脚本：
```
BACKUP DATABASE School_2025_Xiaqiuchu
TO DISK = 'D:\Backup\School_2025_Xiaqiuchu_Full_$(ESCAPE_SQUOTE(DATE)).bak'
WITH COMPRESSION;
```
  在计划中设置定时任务（如每天凌晨2点）。
2. 使用维护计划向导
  右键数据库 → 任务 → 维护计划向导 → 选择备份数据库任务。
  配置备份类型、路径、压缩选项和计划。

验证备份完整性

校验备份文件

	-- 验证备份文件是否可读
	RESTORE VERIFYONLY
	FROM DISK = 'D:\Backup\School_2025_Xiaqiuchu_Full.bak';

查看备份历史

-- 查询备份历史记录
SELECT 
	database_name,
	backup_start_date,
	type,  -- D=完整, I=差异, L=日志
	physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE database_name = 'School_2025_Xiaqiuchu';

posted @ 2025-05-08 17:22 夏秋初阅读(192) 评论(0) 收藏举报

刷新页面返回顶部

夏秋初的学习笔记

夏末秋初遇见最美好

SQL Server 常用操作、SQL整理

参考

环境

相关问题

正文

演示环境准备

DCL 数据控制语言

DDL 数据定义语言

DQL 数据查询语言

DML 数据操作语言

其他

公告