SQL Server数据库与C# ADO.NET数据库编程

本周学习总结SQL Server 数据库与 C# ADO.NET 数据库编程学习学习周期本周学习方向关系型数据库基础、T‑SQL 语法、约束、CRUD、高级查询、多表联查、事务变量、C# ADO.NET 数据库访问编程一、本周学习概述本周主要完成SQL Server关系型数据库系统学习从数据库分类、数据库安装基础到T‑SQL完整语法覆盖库、表、约束、增删改查基础CRUD、聚合函数、分页、子查询、多表联查、事务、局部变量最后延伸到C#语言通过ADO.NET实现程序和SQL Server数据库交互。数据库是绝大多数业务系统的数据底层支撑不管是桌面程序、Web项目数据持久化都离不开数据库存储。本次学习重点掌握SQL语句编写逻辑理解表与表之间的关系同时掌握C#程序连接数据库完成数据读写的完整流程为后续WinForm项目、业务系统开发打下基础。本周学习内容量较大知识点分为两大块第一块是数据库理论与T‑SQL脚本编写第二块是C# ADO.NET编程把程序和数据库打通。学习过程中也遇到语法细节、约束含义、多表联查区分内外连接、事务回滚逻辑、数据库连接对象使用等难点通过手写SQL脚本、敲示例代码理清每一条语句执行含义。二、数据库基础认知2.1 常见数据库分类数据库可以理解为专门用来存储、管理数据的软件不同场景选用不同数据库各自优缺点差异明显。MySQL大型开源关系数据库多用于大型 APP、Web 大型程序开源免费互联网项目使用非常广泛。SQL Server微软推出的关系型数据库和 C# 语言适配度极高适合 Windows 平台企业项目、VS 开发环境。部分版本付费也提供免费学习版本。安装为傻瓜式向导安装但步骤不能随意跳过一旦安装出错卸载修复比较麻烦。数据库备份还原时如果出现权限报错可以在文件属性安全选项中添加everyone对象开放访问权限解决还原失败问题。SQLite轻量级小型数据库适合小型APP、单机小程序不需要独立服务进程。但是支持SQL语法不完整数据处理效率一般适合数据量不大的单机软件。MongoDB网络分布式非关系数据库文档型数据库适合海量分布式数据存储场景。本次课程重点学习SQL Server熟悉T‑SQL语法T‑SQL是SQL Server扩展的结构化查询语言可以完成建库建表、数据操作、业务逻辑编写。2.2 SQL Server 常用数据类型在建表的时候需要为每一列指定数据类型约束存入数据格式。varchar(n)字符串类型n 代表字符串允许最大长度存储文本。datetime完整日期时间类型同时保存年月日时分秒。date仅保存日期不保存时分秒。float浮点小数类型保存小数数值。bit位类型SQL Server 没有 bool 布尔类型业务中使用 bit 替代布尔取值 0 或者 1。2.3 数据库约束约束的作用是保证表中数据的合法性防止脏数据存入数据表。主键约束 Primary Key主键用来唯一标识一行记录主键字段不允许为空、不允许重复常常搭配identity(起始值,步长)实现自增新增数据不需要手动传入主键值数据库自动生成编号。唯一约束 Unique字段内容不能重复但是允许为空适合手机号、身份证这类业务唯一字段。CHECK 约束自定义校验规则例如校验身份证长度必须等于18位不符合规则的数据无法插入。主外键约束实现多张数据表之间关联关系保证关联数据完整性。三、T‑SQL基础语法学习T‑SQL 操作层级操作数据库库→操作数据表表→操作表内数据CRUD。3.1 数据库操作创建数据库可以自定义数据文件存放路径、初始大小、最大容量、文件增长大小。CreateDatabaseTest02onprimary(nameTest02,filenameD:\Test02.mdf,size5MB,maxsize1000MB,filegrowth1mb)使用指定数据库use Test02切换当前操作数据库后续建表写表操作都作用于该库。 删除数据库drop database Test02删除数据库风险极高实际开发禁止随意执行。### 3.2 数据表操作删除表drop table Student直接删除整张数据表表结构和数据全部消失。 创建表定义列名、数据类型、是否为空、主键自增约束。sqlcreatetableStudent(IdintNOTNULLidentity(1,1)primarykey,Namevarchar(50)NULL)修改表AltertableStudent用于后续新增列、删除列、修改列属性。 添加约束使用altertable给已有表追加约束。sql--添加唯一约束ALTERTABLEStudentADDCONSTRAINTUCUNIQUE(CardNo)--添加check校验约束ALTERTABLEStudentADDCONSTRAINTCKCheck(len(CardNo)18)3.3CRUD 增删改查 CRUD 代表数据新增Create、查询Read、修改Update、删除Delete是数据库最核心四大操作。 新增Insert主键设置自增时insert语句不需要书写主键字段数据库自动生成 id。 全字段插入sqlinsertintoStudentvalues(张三)一次性插入多条记录sqlinsertintoStudentvalues(李四),(王五)指定部分字段插入只给部分列赋值sqlinsertintoStudent(Name)values(赵六)删除Delete/Truncatedelete按条件删除行数据sqldeleteStudentwhereId6不带where条件会删除整张表全部数据但不会重置自增计数器。sqldeletefromStudentTruncateTABLE清空整张表全部数据并且重置自增标识回到初始值执行速度快。sqlTRUNCATETABLEEmployee;注意生产环境删除语句务必携带where条件避免误删整张表业务数据。 修改Updateupdate修改表数据必须带上where条件否则会修改表里面全部行。sqlupdateStudentsetName新名字whereid5查询Select查询是业务中使用频率最高的语句用来读取数据表数据。 查询全部列select*fromStudent*代表返回表所有字段。 指定需要返回的字段selectId,NamefromStudent减少不必要字段提升查询性能。 字段别名as修改返回结果列名sqlselectNameas学生姓名,Idas编号fromStudent 额外构造虚拟列可以拼接固定文本作为返回列。sqlselect*,在读as状态fromStudentwhere条件筛选数据支持多条件and、orsqlselect*fromStudentwhereid%2!0andName张三模糊查询like通配符%匹配任意字符%内容%字段任意位置包含该内容 内容%字段以内容开头%内容字段以内容结尾sqlselect*fromStudentwhereNamelike%张%排序orderbyasc升序默认desc降序sqlselect*fromStudentorderbyIddesc类型转换convert函数时间格式转换120代表标准时间格式sqlselectCONVERT(varchar(50),getdate(),120)fromStudent3.4聚合函数 聚合函数做统计计算统计行数、最大最小、求和聚合函数直接返回统计结果不能和普通字段随意混用。 count 统计行数count(0)性能优于count(*)sqlselectcount(0)fromStudent max 最大值、min 最小值sqlselectmax(Id),min(Id)fromStudent sum 求和selectsum(Id)fromStudentbetween…and数值区间筛选select*fromStudentwhereIdbetween1and103.5in子查询 当条件很多写多个or比较繁琐使用in匹配括号内任意值notin匹配不在集合中的数据。sqlselect*fromStudentwhereIdin(1,3,5,7)in还支持嵌套子查询括号内部可以写一条select查询语句。3.6分页实现原理 早期SQLServer 版本没有内置分页关键字使用top关键字完成分页逻辑。 假设每页20条获取第2页数据排除前20条再取后面20条。sqlselecttop20*fromStudentwhereIdnotin(selecttop20IdfromStudent)分页本质就是跳过前面 N 条读取 M 条数据业务系统列表页面离不开分页查询。 四、多表联查 真实业务系统不会把全部数据存放在一张表里会拆分多张数据表表和表依靠关联字段建立关系需要多表联查同时取出多张表的数据。4.1内连接innerjoin内连接只返回两张表能够匹配成功的数据两边匹配不到的数据不会出现在结果集。 隐式内连接写法sqlselect*fromStudent,ClasswhereStudent.ClassIdClass.Id 标准innerjoin写法sqlselect*fromStudentinnerjoinClassonStudent.ClassIdClass.Id4.2左外联leftjoin以左边表作为主表左边表所有记录全部查询出来右表匹配不到数据右边字段填充null。适合场景查询全部学生同时关联班级部分学生没有分配班级也要显示学生信息。sqlselect*fromStudentleftjoinClassonStudent.ClassIdClass.Id 右外联rightjoin逻辑相反以右边表为主表。4.3多张表连续联查 业务复杂时可以连续多次leftjoin关联第三张、第四张表。sqlselect*fromStudentleftjoinClassonStudent.ClassIdClass.IdleftjoinScoreonStudent.IdScore.StuIdwhere114.4交叉连接crossjoin生成笛卡尔积第一张表行数 × 第二张表行数一般业务极少使用。sqlselect*fromStudentcrossjoinClass4.5全连接fulljoin返回左右表全部数据两边匹配不上的数据保留业务场景使用较少。 五、子查询、分组、事务、局部变量5.1子查询 子查询把一条select查询语句嵌套在另一条SQL当中当做条件使用。in子查询括号内查询返回一组集合。条件子查询括号内部查询只能返回单行单列一个值用来做等值判断。sqlselect*fromStudentwhereId(selectmax(Id)fromStudent)5.2groupby分组 分组统计数据分组规则查询列要么是分组字段要么是聚合函数不能写普通非聚合非分组字段。常用于统计每个班级人数、每个分类总数量等统计需求。5.3事务 事务核心思想一组数据库操作要么全部执行成功要么全部失败回滚防止一部分SQL执行成功一部分失败造成数据错乱。 典型场景转账扣钱和加钱必须同时成功。 T‑SQL事务关键字begintran开启事务committran提交事务把修改真正写入数据库rollbacktran回滚事务撤销全部修改 ERROR系统变量捕获上一条语句错误编号大于0代表发生错误。 基础事务伪代码逻辑sqldeclareerrintbegintran--多条数据库操作语句写在这里seterrERRORif(err0)beginrollbacktranendelsebegincommittranend5.4局部变量 T‑SQL支持声明局部变量变量名称必须开头指定数据类型可以赋值打印输出。sql--声明变量declarenumint100declarenamevarchar(20)--set赋值setname小明--打印输出printname有convert(varchar(20),num)块也可以使用select完成变量赋值。 六、C# ADO.NET数据库编程学会SQL脚本之后下一步是在 C# 程序当中调用 SQL Server实现软件界面操作数据库这就是ADO.NET。需要引入命名空间using System.Data.SqlClient;专门适配 SQL Server 数据库。完整操作流程分为导入命名空间→编写连接字符串→创建 SqlConnection 连接对象→打开数据库连接→编写SQL语句→创建执行命令对象→执行SQL获取返回数据→关闭数据库连接。 数据库连接字符串填写数据库服务器地址、数据库名称账号密码认证信息。 csharp 运行 string connStrserver.;databaseTest02;Integrated SecurityTrue;创建连接对象 csharp 运行 SqlConnection connnew SqlConnection(connStr);打开数据库连接 csharp 运行 conn.Open();编写SQL语句字符串 csharp 运行 stringsqlselect * from Student;创建执行对象两种 SqlCommand执行增删改以及 SqlDataReader 流式读取 SqlDataAdapter 适配器自动打开关闭连接填充 DataTable 内存数据表。 适配器填充 DataTable 示例 csharp 运行 SqlDataAdapter sdanew SqlDataAdapter(sql,conn);DataTable dtnew DataTable();sda.Fill(dt);SqlCommandSqlDataReader 逐行读取数据流 csharp 运行 SqlCommand cmdnew SqlCommand(sql,conn);SqlDataReader drcmd.ExecuteReader();while(dr.Read()){//读取字段转为对应类型string namedr[Name].ToString();} 使用完毕关闭数据库连接 csharp 运行 conn.Close();学习注意点数据库连接属于稀缺资源用完必须关闭实际开发推荐using语法块自动释放连接资源。ExecuteReader 是向前只读数据流DataTable 是把全部数据加载到内存。 七、本周学习遇到问题与心得体会 本周数据库知识点体系庞大刚开始学习的时候很容易混淆概念。 约束方面一开始分不清主键、唯一约束区别。主键非空且唯一一张表只能一个主键唯一约束允许null一张表可以多个唯一约束。CHECK约束用来自定义业务规则。 多表联查难点内连接、左连接容易写混。内连接只拿两边匹配记录左连接保证左表全部数据右表匹配不到返回null写SQL的时候要先理清谁是主表。多表联查on条件不要写到where里面。 事务逻辑事务要理解原子性多条修改语句包裹事务出现错误及时rollback回滚防止业务数据不一致。 ADO.NET编程踩坑连接字符串写错服务器名、数据库名大小写、符号错误直接打不开数据库忘记Open()打开连接执行语句直接报错读取 DataReader 时要循环Read()不调用Read拿不到行数据数据库用完记得Close关闭连接。SQL编写习惯delete、update语句必须养成写where条件的习惯防止误操作清空整张表select尽量不要直接写select*按需查询字段提升性能。 数据库是整个软件的数据底座后续不管做 WinForm 桌面软件还是 Web 开发业务逻辑都离不开数据库存取数据。现在已经掌握基础 T‑SQL能够独立建库建表完成单表、多表增删改查掌握事务和变量同时掌握 C# 程序访问 SQL Server 的完整流程。但是复杂子查询、存储过程、索引优化方面还没有深入学习后续继续练习手写 SQL多写多调试把 SQL 功底练扎实同时结合 WinForm 窗体完成完整窗体增删改查小项目把数据库知识和程序开发结合起来。八、下周学习计划 继续巩固 T‑SQL多练习复杂多表查询、子查询、分组统计SQL脚本。 熟悉ADO.NET封装把数据库操作封装公共工具类避免重复写连接、关闭数据库代码。 结合 WinForm 窗体完成完整学生信息管理小案例实现界面新增、修改、删除、查询、分页。 了解数据库索引基础概念理解索引对查询性能的影响