SQL Server 数据库核心操作

一、基础查询操作

1. 基础查询（最常用）

-- 查询表中所有列
SELECT * FROM emp;

-- 查询指定列，并重命名列名（别名）
SELECT empno AS 员工编号, ename AS 员工姓名, sal AS 工资 FROM emp;

-- 条件查询（WHERE）
SELECT ename, sal FROM emp WHERE sal > 3000 AND deptno = 10;

-- 排序（ORDER BY，ASC升序/ DESC降序，默认升序）
SELECT ename, sal FROM emp ORDER BY sal DESC;

-- 去重查询（DISTINCT）
SELECT DISTINCT deptno FROM emp;

-- 分页查询（SQL Server 2012+ 推荐用 OFFSET FETCH，兼容旧版用 TOP+ROW_NUMBER）
-- 方式1：新版分页（第6-10条数据）
SELECT empno, ename, sal FROM emp ORDER BY sal DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

-- 方式2：旧版分页（兼容SQL Server 2008及以下）
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn, empno, ename, sal FROM emp) t WHERE rn BETWEEN 6 AND 10;

2. 聚合查询（统计分析）

-- 求和、平均值、最大值、最小值、计数
SELECT 
	COUNT(*) AS 总人数, 
	SUM(sal) AS 工资总和, 
	AVG(sal) AS 平均工资,
	MAX(sal) AS 最高工资, 
	MIN(sal) AS 最低工资 
FROM emp 
	WHERE deptno = 20;

-- 分组查询（GROUP BY）
SELECT 
	deptno AS 部门编号, 
	COUNT(*) AS 部门人数 
FROM emp 	
	GROUP BY deptno 	
	HAVING COUNT(*) > 5; -- 分组后筛选（区别于WHERE，WHERE是分组前筛选）

二、表管理操作

1. 创建表

-- 基础建表语法（含常用字段类型、主键、非空约束）
CREATE TABLE emp_copy (
	empno INT PRIMARY KEY, -- 整数类型，主键（唯一且非空）
	ename VARCHAR(20) NOT NULL, -- 字符串类型，长度20，非空
	sal DECIMAL(7,2), -- 小数类型，总长度7，小数位2（如 12345.67）
	hiredate DATE, -- 日期类型（仅日期）
	deptno TINYINT, -- tinyint 范围 0-255，适合部门编号
	create_time DATETIME DEFAULT GETDATE() -- 带默认值的时间字段
);

2. 修改表

-- 添加列
ALTER TABLE emp_copy ADD phone VARCHAR(11);

-- 修改列类型/长度
ALTER TABLE emp_copy ALTER COLUMN phone VARCHAR(15);

-- 删除列
ALTER TABLE emp_copy DROP COLUMN phone;

-- 添加主键约束（如果建表时未加）
ALTER TABLE emp_copy ADD CONSTRAINT pk_empno PRIMARY KEY (empno);

-- 添加唯一约束
ALTER TABLE emp_copy ADD CONSTRAINT uk_ename UNIQUE (ename);

3. 删除 / 清空表

-- 删除表（结构+数据，需先删除依赖约束）
DROP TABLE emp_copy;

-- 清空表数据（可回滚，速度慢，日志量大）
DELETE FROM emp_copy;

-- 清空表数据（不可回滚，速度快，适合大数据量）
TRUNCATE TABLE emp_copy;

-- 注意：如果表有外键约束，TRUNCATE 会失败，需先禁用约束或删除外键

三、数据操作（增删改）

1. 插入数据

-- 插入指定列
INSERT INTO emp_copy (empno, ename, sal, hiredate) VALUES (1001, '张三', 5000.00, GETDATE());

-- 插入多行数据（SQL Server 2008+ 支持）
INSERT INTO emp_copy (empno, ename, sal, hiredate, deptno)
VALUES (1002, '李四', 6000.00, '2024-01-01', 20), (1003, '王五', 5500.00, '2024-02-15', 30);

-- 批量插入（从现有表复制数据）
INSERT INTO emp_copy (empno, ename, sal) SELECT empno, ename, sal FROM emp WHERE deptno = 10;

2. 修改数据

-- 修改指定数据（务必加WHERE，否则修改全表）
UPDATE emp_copy SET sal = 5500.00, deptno = 30 WHERE empno = 1001;

-- 批量修改（根据关联表数据更新）
UPDATE a SET a.sal = a.sal * 1.1 FROM emp_copy a JOIN dept d ON a.deptno = d.deptno
WHERE d.dname = '销售部'; -- 工资上涨10%

3. 删除数据

-- 删除指定数据（加WHERE，否则删除全表）
DELETE FROM emp_copy WHERE empno = 1002;

-- 关联删除（根据关联表条件删除）
DELETE a FROM emp_copy a JOIN dept d ON a.deptno = d.deptno WHERE d.dname = '临时部门';

四、用户与权限管理（运维常用）

1. 创建登录名与数据库用户

-- SQL Server 区分「登录名（服务器级）」和「数据库用户（数据库级）」
-- 1. 创建服务器登录名（Windows身份验证）
CREATE LOGIN [WIN-PCusername] FROM WINDOWS;

-- 2. 创建SQL Server身份验证登录名
CREATE LOGIN test_login WITH PASSWORD = '123456aA!', DEFAULT_DATABASE = test_db;

-- 3. 创建数据库用户（关联登录名）
USE test_db; CREATE USER test_user FOR LOGIN test_login;

2. 授权

-- 授予数据库级角色（常用快速授权）

USE test_db;
EXEC sp_addrolemember 'db_datareader', 'test_user'; -- 只读权限（查询所有表）
EXEC sp_addrolemember 'db_datawriter', 'test_user'; -- 只写权限（增删改所有表）

-- 授予具体表的操作权限
GRANT SELECT, INSERT, UPDATE ON emp_copy TO test_user;

-- 授予执行存储过程权限
GRANT EXECUTE ON proc_get_emp TO test_user;

-- 授予管理员权限（谨慎使用）
EXEC sp_addsrvrolemember 'test_login', 'sysadmin';

3. 撤销权限

-- 撤销表的删除权限
REVOKE DELETE ON emp_copy FROM test_user;

-- 移除数据库角色
EXEC sp_droprolemember 'db_datawriter', 'test_user';

4. 修改登录名密码 / 删除用户

-- 修改登录名密码
ALTER LOGIN test_login WITH PASSWORD = '654321aA!';

-- 删除数据库用户
USE test_db; DROP USER test_user;

-- 删除服务器登录名
DROP LOGIN test_login;

五、其他高频操作

1. 查看表结构

-- 方式1：查询系统视图（详细字段信息）
SELECT COLUMN_NAME AS 列名, DATA_TYPE AS 数据类型, CHARACTER_MAXIMUM_LENGTH AS 长度,
IS_NULLABLE AS 是否允许空, COLUMN_DEFAULT AS 默认值
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'emp_copy';

-- 方式2：在SSMS中简写（需开启SQLCMD模式）
sp_help 'emp_copy';

2. 事务控制

-- 开始事务

BEGIN TRANSACTION;
UPDATE emp_copy SET sal = 6000 WHERE empno = 1001; -- 执行操作
COMMIT TRANSACTION; -- 提交事务（确认修改，永久生效）
ROLLBACK TRANSACTION; -- 回滚事务（撤销未提交的修改）

-- 带保存点的事务
BEGIN TRANSACTION;
UPDATE emp_copy SET sal = 6500 WHERE empno = 1001;
SAVE TRANSACTION sp1; -- 设置保存点
UPDATE emp_copy SET sal = 7000 WHERE empno = 1002;
ROLLBACK TRANSACTION sp1; -- 回滚到保存点
COMMIT TRANSACTION;

3. 查看当前用户 / 数据库信息

-- 查看当前登录用户
SELECT SUSER_SNAME();

-- 查看当前数据库
SELECT DB_NAME();

-- 查看数据库版本
SELECT @@VERSION;

-- 查看表空间使用情况（SQL Server 用文件组/数据文件）
SELECT name AS 数据文件名, physical_name AS 文件路径, size/128 AS 大小MB,

file_id AS 文件ID FROM sys.database_files;

六、高频时间函数（日常开发必备）

1. 获取系统时间（最常用）

-- 获取当前日期时间（含时分秒，精度到毫秒）
SELECT GETDATE() AS 当前时间;

-- 获取当前日期时间（SQL Server 2008+，推荐，精度更高）
SELECT SYSDATETIME() AS 高精度时间;

-- 获取当前日期（仅日期，无时分秒）
SELECT CAST(GETDATE() AS DATE) AS 当前日期;

SELECT CONVERT(DATE, GETDATE()) AS 当前日期; -- 等价于上面

2. 日期格式转换（CONVERT/FORMAT）

-- 方式1：CONVERT（性能高，支持标准格式）
SELECT CONVERT(VARCHAR(10), GETDATE(), 23) AS 日期格式; -- 2026-03-09（ISO标准）
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS 完整时间; -- 2026-03-09 15:45:30
SELECT CONVERT(VARCHAR(20), GETDATE(), 111) AS 日式格式; -- 2026/03/09

-- 方式2：FORMAT（SQL Server 2012+，灵活自定义格式）
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS 日期格式;
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日 HH:mm:ss') AS 中文格式;
SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS 时分秒;

-- 常用格式代码：23=yyyy-MM-dd | 120=yyyy-MM-dd HH:mm:ss | 111=yyyy/MM/dd | 108=HH:mm:ss

3. 日期计算（DATEADD/DATEDIFF）

-- 1. DATEADD：日期加减（语法：DATEADD(单位, 数值, 日期)）
SELECT 
	GETDATE() AS 当前时间,
	DATEADD(DAY, 1, GETDATE()) AS 明天此时,
	DATEADD(WEEK, -1, GETDATE()) AS 1周前,
	DATEADD(MONTH, 3, GETDATE()) AS 3个月后,
	DATEADD(HOUR, 2, GETDATE()) AS 2小时后;

-- 2. DATEDIFF：计算两个日期的间隔
SELECT 
	DATEDIFF(DAY, '2026-01-01', GETDATE()) AS 间隔天数,
	DATEDIFF(MONTH, '2026-01-01', GETDATE()) AS 间隔月数,
	DATEDIFF(HOUR, '2026-03-09 10:00:00', GETDATE()) AS 间隔小时数;

-- 3. 计算当月天数
SELECT DAY(EOMONTH(GETDATE())) AS 当月天数; -- EOMONTH 返回当月最后一天

4. 提取日期元素（DATEPART/YEAR/MONTH/DAY）

SELECT 
	YEAR(GETDATE()) AS 年, 
	MONTH(GETDATE()) AS 月, 
	DAY(GETDATE()) AS 日,
	DATEPART(HOUR, GETDATE()) AS 小时, 
	DATEPART(MINUTE, GETDATE()) AS 分钟,
	DATEPART(WEEKDAY, GETDATE()) AS 星期几; -- 1=周日，7=周六

5. 日期截断（DATEFROMPARTS/CAST）

SELECT 
	CAST(GETDATE() AS DATE) AS 今天零点,
	DATEFROMPARTS(YEAR(GETDATE()), 
	MONTH(GETDATE()), 1) AS 本月初,
	DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS 本年初,
	DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS 当日起始;

6. 实际业务场景示例

-- 示例1：查询近30天内入职的员工
SELECT ename, hiredate FROM emp WHERE hiredate >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE));

-- 示例2：统计2026年每个月的工资总和
SELECT FORMAT(hiredate, 'yyyy-MM') AS 月份, SUM(sal) AS 工资总和

FROM emp WHERE YEAR(hiredate) = 2026 GROUP BY FORMAT(hiredate, 'yyyy-MM') ORDER BY 月份;

-- 示例3：查询本月入职的员工数
SELECT COUNT(*) AS 本月入职人数 FROM emp&#x20;

WHERE MONTH(hiredate) = MONTH(GETDATE()) AND YEAR(hiredate) = YEAR(GETDATE());

（注：文档部分内容可能由 AI 生成）

SQL Server 数据库核心操作

SQL Server 数据库核心操作

一、基础查询操作

1. 基础查询（最常用）

2. 聚合查询（统计分析）

二、表管理操作

1. 创建表

2. 修改表

3. 删除 / 清空表

三、数据操作（增删改）

1. 插入数据

2. 修改数据

3. 删除数据

四、用户与权限管理（运维常用）

1. 创建登录名与数据库用户

2. 授权

3. 撤销权限

4. 修改登录名密码 / 删除用户

五、其他高频操作

1. 查看表结构

2. 事务控制

3. 查看当前用户 / 数据库信息

六、高频时间函数（日常开发必备）

1. 获取系统时间（最常用）

2. 日期格式转换（CONVERT/FORMAT）

3. 日期计算（DATEADD/DATEDIFF）

4. 提取日期元素（DATEPART/YEAR/MONTH/DAY）

5. 日期截断（DATEFROMPARTS/CAST）

6. 实际业务场景示例

目录