SQL 多表联查 +ADO.NET学习总结：从基础到实战

数据库查询与 C# 代码落地是后端学习的核心环节 —— 多表联查涉及关联逻辑的梳理ADO.NET包含多个抽象对象的使用。本文以 “基础概念→详细语法→实战案例→注意事项” 为逻辑将知识点拆解细化结构清晰、内容通俗适合作为复习资料帮你扎实掌握从 SQL 查询到 C# 数据库访问的全流程。一、SQL 多表联查关联查询核心逻辑多表联查的核心是 “从多张关联表中提取目标数据”核心原则是通过 “关联条件” 去除无用的笛卡尔积两张表无条件连接会产生 “表 1 行数 × 表 2 行数” 的冗余数据关联条件通常基于主外键关系如 emp 表的 deptno 与 dept 表的 deptno 关联。1.1 多表联查的应用场景当数据分散在多张表时需通过联查获取完整信息。例如员工姓名存储在 emp 表、部门名称存储在 dept 表需通过共同的 deptno 字段关联才能同时获取这两类信息。1.2 三大核心联查方式1.2.1 联合查询UNION/UNION ALL/INTERSECT/EXCEPT作用合并多个 SELECT 语句的结果集要求所有 SELECT 语句的列数、列类型完全一致。表格运算符核心作用特点适用场景UNION合并结果集 自动去除重复项去重性能稍慢合并无重复需求的数据UNION ALL合并结果集 保留重复项不去重性能更优无需去重的批量数据合并INTERSECT取两个结果集的交集只保留共同记录自动去重筛选两个查询的共同数据EXCEPT取第一个结果集的独有关联只保留第一个查询的独有数据对比两个查询的差异数实战案例-- 合并“薪资2000”和“10号部门”的员工数据优先使用UNION ALL提升性能 SELECT ename, sal, deptno FROM emp WHERE sal 2000 UNION ALL SELECT ename, sal, deptno FROM emp WHERE deptno 10; -- 注意列数不一致会直接报错如下为错误示例 SELECT ename, sal, deptno FROM emp WHERE sal 2000 UNION ALL SELECT ename, sal FROM emp WHERE deptno 10; -- 缺少deptno列运行报错1.2.2 连接查询INNER/LEFT/RIGHT JOIN通过关联条件匹配两张表的数据是最常用的联查方式重点掌握内连接和外连接。1内连接INNER JOIN作用只返回两张表中满足关联条件的记录不满足条件的记录会被过滤。语法结构SELECT 目标字段 FROM 表1 别名1 INNER JOIN 表2 别名2 ON 关联条件; -- 必须指定否则产生笛卡尔积实战案例-- 查询员工姓名、薪资、部门名称emp与dept通过deptno关联 SELECT e.ename AS 员工姓名, e.sal AS 薪资, d.dname AS 部门名称 FROM emp e INNER JOIN dept d ON e.deptno d.deptno; -- 关联条件员工表与部门表的部门编号一致2外连接LEFT/RIGHT JOIN核心逻辑保留某一张表的所有记录另一张表不匹配的字段显示为 NULL。表格连接方式核心逻辑适用场景左外连接LEFT JOIN保留左表所有记录右表不匹配显示 NULL需展示左表完整数据如所有员工无部门则标注右外连接RIGHT JOIN保留右表所有记录左表不匹配显示 NULL需展示右表完整数据如所有部门无员工则标注实战案例-- 左外连接显示所有员工无部门则标注“无部门” SELECT e.ename AS 员工姓名, ISNULL(d.dname, 无部门) AS 部门名称 -- ISNULL函数将NULL替换为指定值 FROM emp e LEFT JOIN dept d ON e.deptno d.deptno;3自连接作用将单张表视为两张表查询表内关联数据如员工与直属领导的关系。核心要求必须给表起不同别名避免字段归属混淆。实战案例-- 查询员工姓名及其直属领导姓名emp表的managerid关联自身empno SELECT a.ename AS 员工姓名, ISNULL(b.ename, 无领导) AS 领导姓名 FROM emp a -- 表1员工表 LEFT JOIN emp b -- 表2领导表同一张表的不同别名 ON a.managerid b.empno; -- 关联条件员工的领导ID领导的员工ID1.3 子查询嵌套查询定义SELECT 语句中包含另一个 SELECT 语句用一个查询的结果作为另一个查询的条件按返回结果分为三类。1标量子查询返回结果为单个值数字、字符串、日期常用在 、、 等比较运算符后。语法结构SELECT 字段 FROM 表 WHERE 条件 (子查询);实战案例-- 查询薪资高于ALLEN的员工 SELECT * FROM emp WHERE sal (SELECT sal FROM emp WHERE ename ALLEN); -- 子查询返回ALLEN的薪资单个值注意标量子查询需返回单个值若返回多个结果会直接报错。2列子查询返回结果为一列数据如多个部门编号、多个薪资常用运算符IN、NOT IN、ANY、ALL。表格运算符说明IN匹配集合内任意一个值NOT IN不匹配集合内的所有值ANY满足集合中任意一个条件即可ALL满足集合中所有条件实战案例-- 查询“销售部SALES”和“财务部ACCOUNTING”的员工 SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname IN (SALES, ACCOUNTING));3表子查询返回结果为多行多列的临时表常用在 JOIN 后作为关联表。实战案例-- 查询员工编号7369的姓名、薪资、部门名称 SELECT e.ename, e.sal, d.dname FROM emp e JOIN (SELECT deptno, dname FROM dept) d -- 子查询作为临时表 ON e.deptno d.deptno WHERE e.empno 7369;1.4 常用 SQL 函数辅助多表联查函数可简化数据处理以下为联查中高频使用的函数搭配案例说明用法。1.4.1 字符串函数表格函数名作用语法示例实战案例CONCAT拼接字符串CONCAT (字段 1, 字段 2)拼接员工姓名和职位CONCAT (ename, -, job)LEN计算字符串长度LEN (字段)查询姓名长度大于 3 的员工LEN (ename) 3TRIM去除字符串前后空格TRIM (字段)清洁员工姓名TRIM (sname)REPLACE替换字符串中的内容REPLACE (字段旧值新值)替换性别标识REPLACE (gender, male, 男 )1.4.2 日期时间函数表格函数名作用语法示例实战案例GETDATE()获取当前系统日期时间GETDATE()查询当日新增员工create_time GETDATE ()YEAR/MONTH/DAY提取年 / 月 / 日YEAR (日期字段)计算入职年份YEAR (GETDATE ()) - YEAR (hiredate)DATEDIFF计算两个日期的间隔DATEDIFF (单位日期 1, 日期 2)计算入职年限DATEDIFF (YEAR, hiredate, GETDATE ())1.4.3 流程控制函数表格函数名作用语法示例实战案例ISNULL替换 NULL 值ISNULL (字段默认值)无奖金显示 0ISNULL (comm, 0)IIF二选一判断IIF (条件结果 1, 结果 2)判断奖金状态IIF (comm IS NULL, 无 , 有 )CASE WHEN多条件判断CASE WHEN 条件 1 THEN 结果 1 ELSE 结果 2 END薪资分级CASE WHEN sal2500 THEN 高工资 ELSE 普通工资 END函数 多表联查实战-- 查询员工姓名、薪资、奖金状态、入职年限、部门名称 SELECT e.ename AS 员工姓名, e.sal AS 薪资, IIF(e.comm IS NULL OR e.comm0, 无奖金, 有奖金) AS 奖金状态, DATEDIFF(YEAR, e.hiredate, GETDATE()) AS 入职年限, d.dname AS 部门名称 FROM emp e JOIN dept d ON e.deptno d.deptno;二、ADO.NET编程C# 操作数据库核心实现ADO.NET是 C# 与数据库交互的核心技术核心流程为 “连接→执行 SQL→处理结果”需掌握关键对象的作用及使用方法。2.1 ADO.NET核心对象速查表表格对象名称核心作用常用方法 / 属性SqlConnection建立 C# 与 SQL Server 的连接Open ()打开、Close ()关闭SqlCommand执行 SQL 语句或存储过程ExecuteNonQuery ()增删改、ExecuteReader ()查询、ExecuteScalar ()查单个值SqlDataReader逐行读取查询结果连接式访问Read ()读下一行、[字段名]取数据SqlDataAdapter填充数据到内存断开式访问Fill ()填充、Update ()更新DataSet内存中的离线数据库Tables包含的表集合DataTable内存中的数据表DataSet 的核心Rows数据行、Columns数据列2.2 前期准备2.2.1 引入命名空间C# 操作 SQL Server 需引入以下命名空间复制到代码顶部即可using System.Data; // 包含DataTable、DataSet等 using System.Data.SqlClient; // 包含SqlConnection、SqlCommand等 // .NET Core/.NET 5 版本使用using Microsoft.Data.SqlClient;2.2.2 编写连接字符串连接字符串是 C# 访问数据库的 “地址和凭证”套用以下模板修改括号内内容// 模板1SQL Server身份验证需账号密码 string connectionString Serverlocalhost;Database数据库名;User Idsa;Password登录密码;; // 模板2Windows身份验证无需账号密码 string connectionString Serverlocalhost;Database数据库名;Integrated SecurityTrue;;2.3 核心流程 1连接数据库连接数据库的核心流程为 “创建连接对象→打开连接→使用→关闭连接”连接用完需及时关闭避免占用数据库资源。完整代码带异常处理using System; using System.Data; using System.Data.SqlClient; namespace ADO.NET基础 { class Program { static void Main(string[] args) { // 1. 定义连接字符串替换为实际数据库信息 string connectionString Serverlocalhost;Databasetestdb;Integrated SecurityTrue;; // 2. 使用using自动释放资源 using (SqlConnection conn new SqlConnection(connectionString)) { try { // 3. 打开连接 conn.Open(); Console.WriteLine(数据库连接成功); Console.WriteLine(当前连接状态 conn.State); // 输出Open // 后续执行SQL语句的代码写在此处 } catch (Exception ex) { // 捕获连接异常 Console.WriteLine(数据库连接失败 ex.Message); } // using会自动关闭连接无需手动调用Close() } } } }2.4 核心流程 2执行增删改 SQLExecuteNonQuery执行 INSERT、UPDATE、DELETE 语句时使用 SqlCommand 的 ExecuteNonQuery () 方法返回 “受影响的行数”可通过返回值判断操作是否成功。实战案例添加员工/// summary /// 向emp表添加员工 /// /summary /// param nameename员工姓名/param /// param namesal薪资/param /// param namedeptno部门编号/param /// returns受影响的行数/returns public static int AddEmployee(string ename, decimal sal, int deptno) { string connectionString Serverlocalhost;Databasetestdb;Integrated SecurityTrue;; // 采用参数化查询避免SQL注入 string sql INSERT INTO emp(ename, sal, deptno) VALUES(ename, sal, deptno); using (SqlConnection conn new SqlConnection(connectionString)) { try { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { // 给参数赋值 cmd.Parameters.AddWithValue(ename, ename); cmd.Parameters.AddWithValue(sal, sal); cmd.Parameters.AddWithValue(deptno, deptno); // 执行增删改返回受影响行数 int affectedRows cmd.ExecuteNonQuery(); Console.WriteLine(添加成功受影响行数 affectedRows); return affectedRows; } } catch (Exception ex) { Console.WriteLine(添加失败 ex.Message); return 0; } } } // 调用方法 AddEmployee(张三, 2500, 10);2.5 核心流程 3执行查询 SQL查询数据有两种常用方式连接式访问SqlDataReader和断开式访问SqlDataAdapterDataTable。2.5.1 连接式访问SqlDataReader适用于查询大量数据、仅需读取一次的场景特点是逐行读取、只能读不能改需保持连接打开。实战案例多表联查并读取结果/// summary /// 查询指定部门的员工信息多表联查 /// /summary /// param namedeptno部门编号/param public static void GetEmployeesByDept(int deptno) { string connectionString Serverlocalhost;Databasetestdb;Integrated SecurityTrue;; // 多表联查SQL string sql SELECT e.ename AS 员工姓名, e.sal AS 薪资, d.dname AS 部门名称 FROM emp e JOIN dept d ON e.deptno d.deptno WHERE e.deptno deptno; using (SqlConnection conn new SqlConnection(connectionString)) { try { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue(deptno, deptno); // 执行查询返回SqlDataReader对象 using (SqlDataReader reader cmd.ExecuteReader()) { Console.WriteLine(员工姓名\t薪资\t部门名称); Console.WriteLine(---------------------------); // 循环读取每一行数据 while (reader.Read()) { // 通过字段名取数据需做类型转换 string ename reader[员工姓名].ToString(); decimal sal (decimal)reader[薪资]; string dname reader[部门名称].ToString(); Console.WriteLine(${ename}\t{sal}\t{dname}); } } } } catch (Exception ex) { Console.WriteLine(查询失败 ex.Message); } } } // 调用方法查询10号部门员工 GetEmployeesByDept(10);2.5.2 断开式访问SqlDataAdapterDataTable适用于需修改查询结果、多次使用数据的场景特点是数据加载到内存后可断开连接离线操作。实战案例查询所有员工并存储到内存/// summary /// 查询所有员工保存到DataTable内存表 /// /summary /// returns内存中的员工表/returns public static DataTable GetAllEmployees() { string connectionString Serverlocalhost;Databasetestdb;Integrated SecurityTrue;; string sql SELECT e.ename, e.sal, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno;; // 创建DataTable内存中的表 DataTable dt new DataTable(员工表); // 创建SqlDataAdapter数据库与内存的桥梁 using (SqlDataAdapter adapter new SqlDataAdapter(sql, connectionString)) { try { // 填充数据自动处理连接的打开与关闭 adapter.Fill(dt); Console.WriteLine(数据填充成功共 dt.Rows.Count 条记录); } catch (Exception ex) { Console.WriteLine(填充失败 ex.Message); } } // 断开连接后仍可操作数据 foreach (DataRow row in dt.Rows) { string ename row[ename].ToString(); decimal sal (decimal)row[sal]; Console.WriteLine(${ename}的薪资{sal}); } return dt; }2.6 事务处理保证数据一致性事务的核心是 “一组操作要么全成功要么全失败”典型应用场景为银行转账、订单提交等。核心流程打开数据库连接开启事务SqlTransaction执行多个 SQL 操作全部成功→提交事务Commit ()任意失败→回滚事务Rollback ()。实战案例银行转账/// summary /// 银行转账张三给李四转2000元 /// /summary /// returns转账是否成功/returns public static bool TransferMoney() { string connectionString Serverlocalhost;Databasetestdb;Integrated SecurityTrue;; string sql1 UPDATE bank SET umoney - 2000 WHERE uname zhangsan;; // 张三扣钱 string sql2 UPDATE bank SET umoney 2000 WHERE uname lisi;; // 李四加钱 using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); SqlTransaction tran null; try { // 开启事务 tran conn.BeginTransaction(); // 创建命令对象关联事务 using (SqlCommand cmd new SqlCommand()) { cmd.Connection conn; cmd.Transaction tran; // 执行第一个操作张三扣钱 cmd.CommandText sql1; cmd.ExecuteNonQuery(); // 执行第二个操作李四加钱 cmd.CommandText sql2; cmd.ExecuteNonQuery(); } // 提交事务 tran.Commit(); Console.WriteLine(转账成功); return true; } catch (Exception ex) { // 回滚事务 tran?.Rollback(); Console.WriteLine(转账失败 ex.Message); return false; } } }2.7 通用工具类DBHelper 封装封装 DBHelper 类可简化重复代码提高开发效率直接复制到项目中即可使用。using System; using System.Data; using System.Data.SqlClient; /// summary /// SQL Server数据库辅助类 /// /summary public static class DBHelper { // 连接字符串替换为实际数据库信息 private static string connectionString Serverlocalhost;Databasetestdb;Integrated SecurityTrue;; #region 1. 执行增删改SQL返回受影响行数 public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } #endregion #region 2. 执行查询SQL返回DataTable public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { DataTable dt new DataTable(); using (SqlDataAdapter adapter new SqlDataAdapter(sql, connectionString)) { adapter.SelectCommand.Parameters.AddRange(parameters); adapter.Fill(dt); } return dt; } #endregion #region 3. 执行查询SQL返回单个值 public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } } #endregion }DBHelper 使用示例// 1. 添加员工 string addSql INSERT INTO emp(ename, sal, deptno) VALUES(ename, sal, deptno); SqlParameter[] addParams { new SqlParameter(ename, 李四), new SqlParameter(sal, 3000), new SqlParameter(deptno, 20) }; int rows DBHelper.ExecuteNonQuery(addSql, addParams); // 2. 多表联查 string querySql SELECT e.ename, e.sal, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno WHERE e.deptno deptno; SqlParameter[] queryParams { new SqlParameter(deptno, 20) }; DataTable dt DBHelper.ExecuteDataTable(querySql, queryParams); // 3. 查询员工总数 string countSql SELECT COUNT(*) FROM emp; object total DBHelper.ExecuteScalar(countSql); Console.WriteLine(员工总数 total);三、核心复习要点1. 多表联查核心原则多表查询必须指定关联条件避免笛卡尔积连接方式内连接匹配数据、左外连接保留左表、右外连接保留右表子查询标量子查询返回单个值列子查询用 IN表子查询作为临时表函数应用ISNULL 替换 NULLDATEDIFF 计算日期差CASE WHEN 实现多条件判断。2. ADO.NET核心对象SqlConnection连接、SqlCommand执行 SQL、SqlDataReader读数据、DataTable内存表连接字符串需准确配置Windows 身份验证无需账号密码安全规范采用参数化查询避免 SQL 注入资源管理使用 using 包裹对象自动释放资源工具类DBHelper 封装后可简化重复代码提升开发效率。3. 常见问题与避坑指南SQL 语法字符串需用单引号多表联查时表别名需规范避免字段混淆数据类型读取数据时需做类型转换确保与数据库字段类型一致事务特性事务需在同一个连接中执行否则回滚无效报错排查连接失败先检查连接字符串SQL 执行失败先在数据库中测试语句正确性。通过梳理核心知识点与实战案例可系统掌握多表联查与ADO.NET的核心用法。建议先在数据库中验证 SQL 语句再通过 C# 代码落地逐步积累实战经验夯实后端开发基础。