SQL Server 从入门到实战：基础操作与 T-SQL 查询全解析

SQL Server 是微软推出的高性能关系型数据库广泛应用于企业级开发、后台系统等场景。对于开发者而言掌握其基础操作库 / 表创建、数据增删改和核心查询语法T-SQL是实现数据存储与检索的必备技能。本文基于 SQL Server 2022整合 “基础操作 T-SQL 查询” 两大核心模块从数据库创建到复杂查询每个知识点都配完整可运行代码帮你一站式掌握 SQL Server 核心用法。一、SQL Server 基础数据库与表的核心操作1.1 数据库基础认知1. 什么是数据库数据库是结构化存储和管理数据的仓库相比文件存储具备以下优势支持海量数据存储与快速检索保证数据一致性、完整性支持多用户共享访问可通过 SQL 语句灵活操作数据。2. 数据库的发展历程没有数据库使用磁盘文件存储数据层次结构模型数据库网状结构模型数据库关系结构模型数据库使用二维表格来存储数据关系-对象模型数据库SQL Server 是关系型数据库数据以「二维表格」形式存储核心层级关系数据库服务器 → 数据库 → 表 → 行记录/列字段。3. 系统数据库与用户数据库安装 SQL Server 后默认包含 4 个系统数据库不可随意修改master主数据库存储服务器配置、用户数据库信息model模板数据库创建新数据库时的模板msdb代理数据库用于作业调度、备份还原tempdb临时数据库存储临时表、查询中间结果。实际开发中需为每个项目创建用户数据库独立存储项目数据。1.2 数据库操作DDL1. 创建数据库两种方式快速创建默认配置和自定义配置指定文件大小、路径。-- 1. 快速创建数据库默认配置 CREATE DATABASE StudentDB; -- 2. 自定义创建数据库指定数据文件、日志文件 CREATE DATABASE StudentDB ON PRIMARY ( NAME StudentDB_Data, -- 主数据文件名称 FILENAME C:\SQLData\StudentDB.mdf, -- 存储路径 SIZE 10MB, -- 初始大小 MAXSIZE 100MB, -- 最大大小 FILEGROWTH 5MB -- 自动增长幅度 ) LOG ON ( NAME StudentDB_Log, -- 日志文件名称 FILENAME C:\SQLData\StudentDB.ldf, SIZE 5MB, MAXSIZE 50MB, FILEGROWTH 2MB );关键文件说明.mdf主数据文件必选存储核心数据.ldf事务日志文件必选记录数据操作日志用于恢复.ndf次要数据文件可选用于拆分大数据库。2. 查看与切换数据库-- 查看所有数据库 SELECT name FROM sys.databases; -- 查看当前连接的数据库 SELECT DB_NAME(); -- 切换数据库操作表前必须切换到目标库 USE StudentDB;3. 删除数据库慎用-- 删除数据库不存在时会报错 DROP DATABASE StudentDB; -- 安全删除不存在时不报错SQL Server 2016支持 DROP DATABASE IF EXISTS StudentDB;1.3 表操作DDL表是数据库存储数据的核心载体需先定义「表结构」字段名 数据类型 约束再插入数据。1. 核心数据类型选择SQL Server 数据类型需根据业务场景精准选择避免浪费空间或存储失败常用类型如下表格类型分类常用类型适用场景示例数值类型tinyint0-255 的整数如年龄age tinyintint常规整数如 ID、数量id intdecimal(p,s)固定精度小数如金额、分数salary decimal(7,2)字符串nchar(n)固定长度字符串如手机号、性别phone nchar(11)nvarchar(n)可变长度字符串如姓名、地址name nvarchar(50)日期时间date仅日期如生日birthday datedatetime2(n)日期 时间如创建时间create_time datetime22. 创建表含约束约束是保证数据完整性的规则常用约束主键PRIMARY KEY、非空NOT NULL、唯一UNIQUE、检查CHECK。-- 切换到目标数据库 USE StudentDB; -- 创建学生表stu CREATE TABLE stu ( sid INT PRIMARY KEY IDENTITY(1,1), -- 主键自增长起始1步长1 sname NVARCHAR(50) NOT NULL, -- 姓名非空 age TINYINT CHECK(age 0 AND age 150), -- 年龄0-150 gender NCHAR(1) CHECK(gender IN (男, 女)), -- 性别仅男/女 phone NCHAR(11) UNIQUE, -- 手机号唯一 create_time DATETIME2 DEFAULT GETDATE() -- 创建时间默认当前时间 ); -- 创建部门表dept CREATE TABLE dept ( deptno INT PRIMARY KEY, -- 部门编号主键 dname NVARCHAR(50) NOT NULL, -- 部门名称非空 loc NVARCHAR(50) -- 部门地址 ); -- 创建员工表emp含外键关联部门表 CREATE TABLE emp ( empno INT PRIMARY KEY, -- 员工编号主键 ename NVARCHAR(50) NOT NULL, -- 员工姓名非空 job NVARCHAR(50), -- 职位 sal DECIMAL(7,2), -- 月薪 deptno INT, -- 关联部门编号 -- 外键约束emp.deptno 关联 dept.deptno CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept(deptno) );关键约束说明主键PRIMARY KEY一行数据的唯一标识非空且唯一自增长IDENTITY (起始值步长)仅整型主键可用自动生成唯一值外键FOREIGN KEY关联两张表保证数据一致性如员工的部门编号必须在部门表中存在。3. 表结构修改与删除-- 1. 给stu表添加字段邮箱 ALTER TABLE stu ADD email NVARCHAR(100); -- 2. 修改stu表字段类型邮箱改为非空 ALTER TABLE stu ALTER COLUMN email NVARCHAR(100) NOT NULL; -- 3. 重命名stu表的gender字段为sex EXEC sp_rename stu.gender, sex, COLUMN; -- 4. 删除stu表的email字段 ALTER TABLE stu DROP COLUMN email; -- 5. 删除表慎用 DROP TABLE IF EXISTS stu;1.4 数据操作DML增删改1. 插入数据INSERT-- 1. 插入完整数据按表字段顺序 INSERT INTO dept VALUES(10, 研发部, 北京); INSERT INTO dept VALUES(20, 市场部, 上海); -- 2. 插入指定字段数据推荐顺序可自定义 INSERT INTO emp (empno, ename, job, sal, deptno) VALUES(7369, SMITH, CLERK, 800.00, 20); -- 3. 批量插入数据 INSERT INTO stu (sname, age, sex, phone) VALUES (张三, 20, 男, 13800138000), (李四, 22, 女, 13900139000), (王五, 25, 男, 13700137000);2. 修改数据UPDATE-- 修改单个字段更新张三的年龄为21 UPDATE stu SET age 21 WHERE sname 张三; -- 修改多个字段更新李四的职位和薪资 UPDATE emp SET job MANAGER, sal 2975.00 WHERE empno 7369; -- 带条件修改仅更新研发部员工的薪资加10% UPDATE emp SET sal sal * 1.1 WHERE deptno 10;3. 删除数据DELETE/TRUNCATE-- 1. 按条件删除删除手机号为13700137000的学生 DELETE FROM stu WHERE phone [phone]; -- 2. 删除表中所有数据保留表结构可回滚 DELETE FROM stu; -- 3. 清空表数据删除表后重建效率更高不可回滚 TRUNCATE TABLE stu;DELETE vs TRUNCATEDELETE逐条删除记录支持 WHERE 条件可通过事务回滚TRUNCATE直接重建表效率更高不支持 WHERE不可回滚。二、T-SQL 查询实战从基础到复杂查询T-SQLTransact-SQL是 SQL Server 的查询语言核心是 DQL数据查询语言用于从表中检索数据语法灵活且功能强大。2.1 基础查询SELECT1. 查询所有字段-- 查询stu表所有记录*代表所有字段开发中不推荐效率低 SELECT * FROM stu;2. 查询指定字段-- 查询学生的姓名、年龄、性别 SELECT sname, age, sex FROM stu; -- 给字段起别名AS可省略 SELECT sname AS 姓名, age 年龄, sex 性别 FROM stu;3. 去除重复记录DISTINCT-- 查询emp表中所有不重复的职位 SELECT DISTINCT job FROM emp;2.2 条件查询WHERE通过 WHERE 子句筛选满足条件的记录支持比较运算符、逻辑运算符、IN、BETWEEN 等。-- 1. 比较运算查询年龄大于20的学生 SELECT * FROM stu WHERE age 20; -- 2. 逻辑运算查询年龄20-25且性别为男的学生 SELECT * FROM stu WHERE age BETWEEN 20 AND 25 AND sex 男; -- 3. IN查询查询部门编号为10或20的员工 SELECT * FROM emp WHERE deptno IN (10, 20); -- 4. NULL值查询查询地址为空的部门 SELECT * FROM dept WHERE loc IS NULL; -- 5. 非NULL查询查询有手机号的学生 SELECT * FROM stu WHERE phone IS NOT NULL;2.3 模糊查询LIKE用于模糊匹配字符串支持通配符%匹配 0~n 个任意字符_匹配 1 个任意字符。-- 1. 查询姓名以张开头的学生 SELECT * FROM stu WHERE sname LIKE 张%; -- 2. 查询姓名包含四的学生 SELECT * FROM stu WHERE sname LIKE %四%; -- 3. 查询手机号以138开头的学生11位手机号 SELECT * FROM stu WHERE phone LIKE 138_______; -- 4. 查询姓名第二个字为三的学生 SELECT * FROM stu WHERE sname LIKE _三%;2.4 排序查询ORDER BY按指定字段排序ASC升序默认、DESC降序支持多字段排序。-- 1. 按年龄升序排序默认ASC SELECT * FROM stu ORDER BY age; -- 2. 按薪资降序排序 SELECT * FROM emp ORDER BY sal DESC; -- 3. 多字段排序先按部门编号升序再按薪资降序 SELECT * FROM emp ORDER BY deptno ASC, sal DESC;2.5 聚合查询聚合函数聚合函数对一列数据进行纵向计算常用函数COUNT计数、SUM求和、AVG平均值、MAX最大值、MIN最小值。-- 1. 统计学生总数 SELECT COUNT(*) AS 学生总数 FROM stu; -- 2. 统计有手机号的学生数COUNT忽略NULL SELECT COUNT(phone) AS 有手机号人数 FROM stu; -- 3. 计算员工平均薪资、最高薪资、最低薪资 SELECT AVG(sal) AS 平均薪资, MAX(sal) AS 最高薪资, MIN(sal) AS 最低薪资 FROM emp; -- 4. 计算研发部deptno10员工的薪资总和 SELECT SUM(sal) AS 研发部薪资总和 FROM emp WHERE deptno 10;2.6 分组查询GROUP BY HAVINGGROUP BY 按指定字段分组HAVING 筛选分组后的结果WHERE 筛选分组前的数据。-- 1. 按部门分组统计每个部门的员工数 SELECT deptno AS 部门编号, COUNT(*) AS 员工数 FROM emp GROUP BY deptno; -- 2. 按部门分组计算每个部门的平均薪资筛选平均薪资2000的部门 SELECT deptno AS 部门编号, AVG(sal) AS 平均薪资 FROM emp GROUP BY deptno HAVING AVG(sal) 2000; -- 3. 按职位和部门分组统计每个组合的员工数 SELECT deptno, job, COUNT(*) AS 员工数 FROM emp GROUP BY deptno, job;2.7 分页查询分页是后台系统必备功能SQL Server 提供了多种实现分页查询的方式以下是常用的几种方法1. OFFSET-FETCH推荐-- 语法查询第N页每页M条 SELECT * FROM emp ORDER BY empno ASC -- 分页必须排序 OFFSET (页码-1)*每页条数 ROWS -- 跳过前N条 FETCH NEXT 每页条数 ROWS ONLY; -- 取后续M条 -- 示例查询第2页每页5条员工数据 SELECT * FROM emp ORDER BY empno ASC OFFSET 5 ROWS -- 跳过前5条第1页 FETCH NEXT 5 ROWS ONLY; -- 取第6-10条第2页2. ROW_NUMBER ()兼容旧版本-- 示例查询第2页每页5条 WITH NumberedRows AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY empno) AS RowNum -- 给每条记录编号 FROM emp ) SELECT * FROM NumberedRows WHERE RowNum BETWEEN 6 AND 10; -- 第2页5*116 到 5*210三、实战案例综合查询练习1. 练习1准备阶段:学生表stu段名称字段类型说明sidnchar(6)学生学号snamenvarchar(50)学生姓名ageint学生年龄gendernvarchar(50)学生性别USE master; -- 创建表 CREATE TABLE stu ( sid NCHAR(6), sname NVARCHAR (50), age INT, gender NVARCHAR (50) ) ; -- 添加数据 INSERT INTO stu VALUES(S_1001, liuYi, 35, male); INSERT INTO stu VALUES(S_1002, chenEr, 15, female); INSERT INTO stu VALUES(S_1003, zhangSan, 95, male); INSERT INTO stu VALUES(S_1004, liSi, 65, female); INSERT INTO stu VALUES(S_1005, wangWu, 55, male); INSERT INTO stu VALUES(S_1006, zhaoLiu, 75, female); INSERT INTO stu VALUES(S_1007, sunQi, 25, male); INSERT INTO stu VALUES(S_1008, zhouBa, 45, female); INSERT INTO stu VALUES(S_1009, wuJiu, 85, male); INSERT INTO stu VALUES(S_1010, zhengShi, 5, female); INSERT INTO stu VALUES(S_1011, xxx, NULL, NULL);这是我们创建的表接下来我们做一些练习1. 查询性别为女并且年龄50的记录SELECT * FROM stu WHERE gender female AND age 50 ;2.查询学号为S_1001或者姓名为liSi的记录SELECT * FROM stu WHERE sid S_1001 OR snameliSi;3.查询学号为S_1001S_1002S_1003的记录SELECT * FROM stu WHERE sid IN (S_1001,S_1002,S_1003);4.查询学号不是S_1001S_1002S_1003的记录SELECT * FROM stu WHERE sid NOT IN (S_1001,S_1002,S_1003);5.查询年龄为null的记录SELECT * FROM stu WHERE age IS NULL;这里提到一个知识点NULL的特殊性1. null与任何进行比较, 结果为false解决方案: null的判断: is null 是null is not null 不是null2. null 与任何进行计算, 结果都为null null任何 null-任何 null*任何 null/任何 结果都是null 解决方案: 对null进行转换 isnull()如果写成 select * from stu whereage null; 将查不到数据select * from stu whereage is null; --正确写法6.查询年龄在20到40之间的学生记录SELECT * FROM stu WHERE age20 AND age40; -- 或者 SELECT * FROM stu WHERE age BETWEEN 20 AND 40;2. 练习2雇员表emp字段名称字段类型说明empnoint员工编号enamenvarchar(50)员工姓名jobnvarchar(50)员工工作mgrint领导编号hiredatedate入职日期saldecimal(7,2)月薪commdecimal(7,2)奖金deptnoint部分编号-- 创建表 CREATE TABLE emp ( empno INT, ename NVARCHAR (50), job NVARCHAR (50), mgr INT, hiredate DATE, sal DECIMAL (7, 2), comm DECIMAL (7, 2), deptno INT ) ; -- 插入数据 INSERT INTO emp values(7369,SMITH,CLERK,7902,1980-12-17,800,NULL,20); INSERT INTO emp values(7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30); INSERT INTO emp values(7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30); INSERT INTO emp values(7566,JONES,MANAGER,7839,1981-04-02,2975,NULL,20); INSERT INTO emp values(7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30); INSERT INTO emp values(7698,BLAKE,MANAGER,7839,1981-05-01,2850,NULL,30); INSERT INTO emp values(7782,CLARK,MANAGER,7839,1981-06-09,2450,NULL,10); INSERT INTO emp values(7788,SCOTT,ANALYST,7566,1987-04-19,3000,NULL,20); INSERT INTO emp values(7839,KING,PRESIDENT,NULL,1981-11-17,5000,NULL,10); INSERT INTO emp values(7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30); INSERT INTO emp values(7876,ADAMS,CLERK,7788,1987-05-23,1100,NULL,20); INSERT INTO emp values(7900,JAMES,CLERK,7698,1981-12-03,950,NULL,30); INSERT INTO emp values(7902,FORD,ANALYST,7566,1981-12-03,3000,NULL,20); INSERT INTO emp values(7934,MILLER,CLERK,7782,1982-01-23,1300,NULL,10);部门表dept字段名称字段类型说明deptnoint部门编码dnamenvarchar(50)部门名称locnvarchar(50)部门所在地点CREATE TABLE dept ( deptno INT, dname NVARCHAR (14), loc NVARCHAR (13) ) ; -- 插入数据 INSERT INTO dept VALUES(10, ACCOUNTING, NEW YORK); INSERT INTO dept VALUES(20, RESEARCH, DALLAS); INSERT INTO dept VALUES(30, SALES, CHICAGO); INSERT INTO dept VALUES(40, OPERATIONS, BOSTON);这是我们创建的员工表和部门表1.查看雇员的月薪与奖金之和因为sal和comm两列的类型都是数值类型所以可以做加运算。如果sal或comm中有一个字段不是数值类型那么会出错。SELECT *,salcomm total FROM emp;这里使用正常方法查询工资和的时候会发现许多行total一列数据为NULL这里就涉及到上面提到的NULL的特殊性null与任何进行计算, 结果都为null面对这种情况我们就可以使用把NULL转换成数值0的函数IFNULLSELECT *,salISNULL(comm,0) total FROM emp;2.查看雇员的月薪与奖金之和查询所有雇员按月薪降序排序如果月薪相同时按编号升序排序SELECT * FROM emp ORDER BY sal DESC,empno ASC;3.查询员工人数查询员工人数即表的行数我们用到COUNT聚合函数 因为COUNT是不统计NULL所以结果可能不准确select count(empno) 员工人数 from emp; --效率高, 可能结果不准确 select count(comm) 员工人数 from emp; --有NULL值结果不准确-- count(*) : 对所有列一起统计 准确,效率低select count(*) from emp; --效率低-- count(1) 效果高, 准确 推荐select 1 from emp; select count(1) 员工人数 from emp;解释select 1 from emp; 是 “逐行返回固定值1”行数等于表的总行数无统计作用count(1) 是对select 1 from emp;的结果计数因此能高效、准确统计总行数推荐count(1)的核心原因无需读取表字段仅计数行存在性效率更高且无NULL值遗漏问题。四、总结基础操作重规范库表创建需合理设计数据类型与约束主键、外键等增删改多条数据时用事务保障一致性重要操作前备份数据库避免数据丢失查询编写讲效率避免使用SELECT *优先通过 WHERE 子句过滤无用数据给主键、外键、高频查询字段创建索引提升查询性能语法使用守规则SQL 字符串需用单引号包裹分页查询必须指定排序规则多表关联需明确关联条件保证查询结果的准确性与兼容性。SQL Server 的核心是 “数据操作与查询”建议结合本文代码在 SQL Server Management StudioSSMS中实际运行测试加深理解。后续可进一步学习事务、索引、存储过程等高级特性提升数据库操作效率。