SQLserver数据查询基础

SQLServer数据查询基础从核心概念到复杂查询的系统入门指南Contents课程目录从基础概念到高级查询，系统掌握数据库核心技能01数据库基础概念02DDL数据定义语言03DML数据操作语言04基础查询技术05高级查询技术06数据完整性与常用函数CHAPTER01数据库基础概念理解数据库的本质、结构与核心组成要素Fundamentals为什么需要数据库数据库相比传统文件存储，在存储效率、数据安全、并发处理和标准化操作方面具有显著优势。理解数据库的核心价值是掌握SQL技术的前提，也是从"会用Excel"到"会管理数据"的认知跃迁。存储效率极高结构化存储占用空间远小于文件存储，支持持久化保存，断电不丢失数据。通过索引机制实现毫秒级数据检索。索引加速·压缩存储Efficiency数据安全性强通过用户权限管理和访问控制机制，精确控制每个用户对每张表的操作权限。支持数据加密和审计日志追踪。权限分级·加密传输Security并发处理能力强支持多人同时读写操作，通过事务机制保证数据一致性，文件系统无法实现。锁机制防止数据冲突和脏读。事务隔离·锁机制Concurrency操作标准化SQL作为统一操作语言，一套语法可跨平台使用，大幅降低学习和迁移成本。支持复杂查询和数据聚合分析。跨平台·标准化语法Standard简化应用开发封装复杂数据管理逻辑，开发者只需关注业务，不必自行处理文件读写和索引构建。提供成熟的驱动和ORM工具。ORM工具·驱动支持DevelopmentDatabaseFundamentals数据库核心概念解析数据库的世界由层层嵌套的结构组成：数据库包含多张表，表由字段定义结构、由记录填充内容，主键确保唯一性，外键建立表间关联。理解这套概念体系是编写任何SQL语句的基础。基础结构概念数据库（Database）：存储数据的容器，类似一个仓库，一个SQLServer实例可包含多个数据库表（Table）：数据库中的数据单元，类似仓库中的货架，每张表存储一类实体的数据字段（Column/Field）：表中每一列，定义数据的某一特征，如"姓名""年龄""工资"等记录（Row/Record）：表中每一行，由多个字段值组成，代表一个完整的数据实体关键约束概念主键（PrimaryKey）：表中每条记录的唯一标识符，不允许重复也不允许为空，如学号、身份证号外键（ForeignKey）：建立表与表之间的关联关系，保证引用数据的完整性和一致性约束（Constraint）：对字段值的限制规则，包括非空、唯一、默认值等，确保数据质量SystemDatabasesSQLServer系统数据库SQLServer内置四个系统数据库，它们是数据库引擎正常运行的基础。master掌控全局配置，model定义新建库模板，msdb管理自动化任务，tempdb提供临时运算空间。01master数据库系统核心库，存储所有系统级信息，包括登录账户、服务器配置、数据库列表等关键元数据。master数据库一旦损坏，将导致整个SQLServer实例无法启动，是运维中必须重点保护的对象。02model数据库新建数据库的默认模板，每次执行CREATEDATABASE语句时，系统都会自动复制model库的结构作为新库的初始状态。通过修改model库，可以统一设置所有新建数据库的默认配置。03msdb数据库SQLServer代理服务的运行基础，专门用于存储定时任务计划、作业调度、备份恢复历史记录、邮件通知配置等自动化运维信息，是数据库日常运维管理的核心支撑。04tempdb数据库全局临时数据存储空间，用于存放临时表、表变量、排序运算的中间结果以及版本存储行等。tempdb具有特殊性，每次SQLServer服务重启时都会自动清空并重新创建。CHAPTER02DDL数据定义语言掌握数据库与数据表的创建、修改和删除操作DDL·DATABASEMANAGEMENT数据库的创建与删除数据库级别的DDL操作是最基础的管理命令。CREATEDATABASE创建数据库时，SQLServer会自动生成.mdf数据文件和.ldf日志文件；DROPDATABASE则永久删除数据库及其全部对象，属于高危操作，需谨慎使用。创建数据库使用CREATEDATABASE语句，系统自动生成.mdf数据文件（存储表、索引等对象）和.ldf日志文件（记录事务操作，支持故障恢复）。.mdf+.ldf删除数据库使用DROPDATABASE语句，将永久删除数据库及其包含的所有表、视图、存储过程等对象，操作不可逆。不可逆附加与分离分离将数据库从服务器移除但保留文件；附加则通过.mdf和.ldf文件重新挂载数据库到服务器实例。挂载/卸载可视化管理在SQLServerManagementStudio中右键操作建库，可直观设置初始大小、自动增长策略和文件路径等参数。SSMSSQLDataDefinition数据表的创建与常用数据类型CREATETABLE语句是数据建模的核心工具，通过定义字段名和数据类型来构建表结构。正确选择数据类型是保证数据质量和存储效率的关键。建表语法结构CREATETABLE+表名+括号内字段定义，每个字段由"字段名+数据类型"组成，字段间用逗号分隔示例语句：CREATETABLEstudent(idINT,nameVARCHAR(50),salDECIMAL(5,2),lasttimeDATETIME)字段定义后可追加约束条件，如NOTNULL（非空）、DEFAULT（默认值）等，增强数据完整性常用数据类型速查INT整数类型，适用于ID、年龄、数量等无小数需求的数值，占用4字节存储空间DECIMAL精确小数类型，p为总位数s为小数位数，如(5,2)可存储999.99，适用于金额VARCHAR可变长度字符串，n为最大字符数，实际存储按内容长度动态调整，适用于姓名地址DATETIME日期时间类型，格式YYYY-MM-DDHH:MM:SS，适用于创建时间、更新时间等场景SQLOperations数据表结构的修改操作表结构修改是数据库维护的常见需求，SQLServer通过ALTERTABLE和sp_rename实现字段的增删改及重命名。01修改表名EXECsp_rename'原表名','新表名'，SQLServer特有语法，区别于MySQL的RENAMETABLEsp_rename02修改字段名EXECsp_rename'表名.原字段名','新字段名','column'，第三参数必须指定column03添加字段ALTERTABLE表名ADD字段名数据类型，新增字段对已有记录默认为NULL值add04删除字段ALTERTABLE表名DROPCOLUMN字段名，删除前需确认无外键依赖且数据不再需要drop05删除整张表DROPTABLE表名，永久删除表结构和所有数据，操作前应做好数据备份droptableChapter03DML数据操作语言掌握数据的增删改查（CRUD）核心操作SQL·DMLINSERT数据插入操作INSERT语句是数据写入的基础，支持全表添加和指定列添加两种模式。实际开发中推荐使用指定列添加方式，因为它对表结构变更具有更好的容错性，是编写健壮SQL代码的重要实践。01全表添加INSERTINTO表名VALUES(值1,值2,...)，值的顺序和数量必须与建表时的字段定义完全一致。FullInsert02指定列添加INSERTINTO表名(列1,列2)VALUES(值1,值2)，未指定的列取默认值或NULL，推荐使用此方式。推荐03日期时间值需用单引号包裹，格式为YYYY-MM-DDHH:MM:SS，如'2024-07-0715:20:03'。'2024-07-07'04字符串值用单引号包裹，若值包含单引号需两个连续单引号转义，如'O''Brien'。'O''Brien'05批量插入SQLServer支持INSERTINTO...SELECT...语法，可将查询结果直接插入另一张表，实现数据迁移。InsertSelectSQL基础命令UPDATE修改与DELETE删除UPDATE和DELETE是数据维护的核心命令，WHERE条件是安全操作的底线。不带WHERE条件的UPDATE将修改全表数据，不带WHERE的DELETE将清空全表，这两类误操作是生产环境中最常见的数据事故原因之一。01UPDATE基本语法：UPDATE表名SET字段=新值WHERE条件，支持同时修改多个字段用逗号分隔。02UPDATE示例：UPDATEstudentSETsal=60WHEREid=1，仅修改id为1的记录的sal字段。03DELETE基本语法：DELETEFROM表名WHERE条件，按条件删除匹配的记录。04DELETE示例：DELETEFROMstudentWHEREname='aa'，删除姓名为aa的所有记录。UPDATE修改数据不带WHERE的UPDATE将修改表中所有记录。如UPDATEstudentSETsal=0会让所有人工资归零——这是最常见的生产事故之一。DELETE删除数据不带WHERE的DELETE将清空整张表。TRUNCATETABLE比DELETE更高效，不逐行记录日志，但无法带WHERE条件，属于全表清空操作。CHAPTER04基础查询技术从SELECT基础到条件筛选、排序与分页的完整查询能力SQLFundamentalsSELECT基础查询与实用技巧SELECT是SQL的核心命令，基础用法包括全表查询和指定列查询。计算列、别名（AS）和去重（DISTINCT）是三个高频使用的实用技巧，能在不修改原始数据的前提下灵活呈现查询结果。全表查询SELECT*返回所有列，适合预览，生产环境应指定具体列减少开销SELECT*指定列查询仅返回需要的字段，是SQL性能优化的基本习惯SELECTcolƒ计算列查询时进行数学运算，支持加减乘除，不修改原始数据sal*12别名AS为结果列起易读名称，可省略AS直接写空格ASalias去重DISTINCT去除结果集中的重复行，常用于统计不重复类别DISTINCTSQL查询基础WHERE条件查询详解WHERE子句通过条件表达式精确筛选数据行，是SQL查询中最核心的过滤机制。掌握四类条件写法，能覆盖90%以上的日常查询需求。比较运算符=><<>支持等于、大于、小于、不等于等比较，如WHEREsal>5000筛选高薪记录。WHEREsal>5000区间查询BETWEEN区间查询包含边界值，等价于两个比较条件的AND组合，语法更直观。3000AND5000集合查询IN(...)等价于多个OR条件的组合，语法更简洁且可读性更强。dept_idIN(1,3,5)模糊查询LIKE'%'%代表任意字符，_代表单个字符，如匹配姓张的记录。nameLIKE'张%'空值判断ISNULL查找字段为空的记录，NULL不等于空字符串，不能用=或<>比较。emailISNULL非空判断ISNOTNULL查找字段有值的记录，常用于数据完整性检查。emailISNOTNULL逻辑与AND两个条件必须同时满足，用于组合多个筛选条件。sal>3000ANDdept=1逻辑或OR满足任一条件即可，可用IN替代以简化写法。dept_id=1ORdept_id=2SQL·PatternMatchingLIKE模糊查询与通配符LIKE语句配合通配符实现模糊匹配，是文本搜索的基础工具。%匹配任意长度字符、_匹配单个字符，二者组合可构建灵活的搜索模式。01%通配符：代表零个或多个任意字符，'张%'匹配所有姓张的名字，'%有限公司'匹配所有以'有限公司'结尾的企业名张%/%有限公司02_通配符：代表恰好一个任意字符，'张_'匹配两个字的张姓名字如'张三'，'张__'匹配三个字的张姓名字张_/张__03组合使用：'%手机%'匹配任何包含'手机'的文本，'[a-c]%'匹配以a、b或c开头的字符串（SQLServer字符范围语法）%手机%/[a-c]%04转义字符ESCAPE：搜索内容包含%或_时使用，如LIKE'%30!%%'ESCAPE'!'，声明!为转义符，其后的%被视为普通字符ESCAPE'!'05性能提示：LIKE'%关键词%'（前缀%）会导致全表扫描无法使用索引，大数据量场景建议改用全文检索（Full-TextSearch）Full-TextSearchSQL·查询控制ORDERBY排序与TOP分页ORDERBY控制查询结果的排列顺序，支持多列排序和升降序组合；TOP是SQLServer特有的分页关键字，用于限制返回行数。二者配合使用可实现"取TopN"类业务需求，是最常用的查询优化组合。ORDERBY排序基本语法：SELECT*FROMstudentORDERBYsalDESC，按工资降序排列，ASC为升序（默认值可省略）多列排序：ORDERBYdept_idASC,salDESC，先按部门升序排列，同一部门内再按工资降序排列计算列排序：ORDERBYsal*12DESC，可直接对计算表达式排序，无需先定义别名TOP分页查询TOPN：SELECTTOP5*FROMstudentORDERBYsalDESC，取工资最高的前5条记录TOPNPERCENT：SELECTTOP20PERCENT*FROMstudent，取结果集前20%的记录OFFSET-FETCH（2012+）：ORDERBYidOFFSET10ROWSFETCHNEXT5ROWSONLY，跳过前10条取后5条，精确分页CHAPTER05高级查询技术聚合函数、分组统计、子查询与多表连接的进阶能力SQL·AggregateFunctions五大聚合函数详解聚合函数将多行数据汇总为单一统计值，是数据分析和报表生成的基础工具。COUNT、SUM、AVG、MAX、MIN五大函数各有用途，需特别注意COUNT(*)与COUNT(列名)的区别以及NULL值对各聚合函数的不同影响。COUNT(*)统计结果集总行数，包含所有行（含NULL），常用于记录总数统计ALLROWSCOUNT(列名)仅统计该列非NULL行数，与COUNT(*)差值即为该列缺失数NON-NULLSUM(列名)对数值列求和，自动忽略NULL值，如SUM(sal)计算工资总额TOTALAVG(列名)计算数值列平均值，分母为非NULL行数，非全部行数AVERAGEMAX/MIN取最大值和最小值，适用数值与日期类型，如最近入职日期RANGESQLFundamentalsGROUPBY分组查询与HAVING过滤GROUPBY将数据按指定字段分组后进行聚合统计，是报表生成的核心语法。HAVING用于对分组后的汇总结果进行过滤，与WHERE的区别在于：WHERE在分组前过滤原始行，HAVING在分组后过滤汇总行。01基本语法：SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id，按部门分组计算各组平均工资02多字段分组：GROUPBYdept_id,gender，按部门和性别两个维度交叉分组，可生成更细粒度的统计报表03HAVING过滤：SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_idHAVINGAVG(sal)>5000，仅保留平均工资超5000的部门04WHEREvsHAVING：WHERE在GROUPBY之前执行，过滤原始数据行；HAVING在GROUPBY之后执行，过滤分组汇总结果05完整查询顺序：FROM→WHERE→GROUPBY→HAVING→SELECT→ORDERBY，理解此顺序有助于排查复杂查询的逻辑错误SQL·ADVANCEDQUERIES子查询（嵌套查询）子查询将一个SELECT语句嵌入另一个查询的WHERE、FROM或SELECT子句中，实现分步推理和复杂条件构建。虽然子查询逻辑直观，但深层嵌套会影响可读性和性能，实际开发中应权衡使用。子查询的三种位置01WHERE子查询SELECT*FROMempWHEREsal>(SELECTAVG(sal)FROMemp)—查找工资高于平均值的员工02FROM子查询（派生表）SELECT*FROM(SELECTdept_id,AVG(sal)avg_salFROMempGROUPBYdept_id)tWHEREavg_sal>500003SELECT标量子查询SELECTname,(SELECTCOUNT(*)FROMordersWHEREorders.emp_id=emp.id)AS订单数FROMemp子查询使用要点01单行vs多行子查询单行返回一个值，可用=、>、<比较；多行需配合IN、ANY、ALL运算符使用02EXISTS子查询WHEREEXISTS(SELECT1FROMordersWHEREorders.emp_id=emp.id)—判断关联数据是否存在，性能优于IN03可读性管理嵌套超过2层时建议改写为CTE（WITH语句）或临时表，提升代码可维护性SQL·Multi-TableQuery内连接查询（INNERJOIN）内连接通过关联字段将多张表的匹配记录合并为一个结果集，是多表查询的核心技术。INNERJOIN只返回两张表中都满足连接条件的记录，不匹配的行将被排除。JOIN·ONSELECT,d.dept_nameFROMemployeeeJOINdepartmentdONe.dept_id=d.id—推荐使用的标准语法Implicit隐式连接：SELECT,d.dept_nameFROMemployeee,departmentdWHEREe.dept_id=d.id—功能等价但可读性较差Alias表别名（AS）：为表起简短别名如employeeASe，简化后续字段引用，多表查询时别名几乎是必须的Multi-JOIN可连续使用多个JOIN，如employeeJOINdepartmentON…JOINsalary_levelON…，依次关联更多表Result内连接只保留两张表中关联字段值匹配的记录，任何一方缺失匹配都会导致该记录不出现在结果中SQL·Joins外连接与自连接外连接通过保留非匹配行扩展了内连接的能力，LEFTJOIN在业务报表中尤为常用。自连接则是表与自身关联的特殊技巧，适用于层级关系和自引用数据结构的查询。二者与内连接共同构成完整的连接查询体系。外连接（OUTERJOIN）LEFTJOIN—保留左表所有记录，右表无匹配时填NULL，如查询所有员工（含未分配部门的）及其部门信息RIGHTJOIN—保留右表所有记录，左表无匹配时填NULL，实际中可用LEFTJOIN调换表顺序替代FULLOUTERJOIN—两侧都保留，无匹配均填NULL，适用于需要完整数据对照的场景自连接（SelfJoin）概念—同一张表与自身做连接，需要为同一张表指定两个不同别名以区分典型场景—员工表中manager_id指向同表的员工ID，通过自连接可查出每个员工的上级姓名示例SELECTAS员工,AS上级FROMemployeeeLEFTJOINemployeemONe.manager_id=m.idSQL·集合操作UNION联合查询UNION将多个SELECT语句的结果集纵向合并为一个结果集，适用于需要从不同数据源汇总同类数据的场景。UNION自动去重，UNIONALL保留重复行且性能更优，二者选择取决于业务对重复数据的容忍度。01UNION语法：SELECTcol1FROMtableAUNIONSELECTcol1FROMtableB，两个查询的列数和数据类型必须一一对应02UNIONvsUNIONALL：UNION自动去除重复行（内部执行DISTINCT），UNIONALL保留所有行包括重复行，性能更好03使用场景：合并历史表和当前表数据、汇总不同条件查询结果、构建跨表统计报表04注意事项：ORDERBY只能出现在最后一个SELECT之后，对合并后的整体结果排序，不能对单个子查询单独排序05与JOIN的区别：JOIN是横向拼接（增加列），UNION是纵向拼接（增加行），解决不同维度的数据合并需求数据库开发工作场景CHAPTER06数据完整性与常用函数约束机制保障数据质量，内置函数扩展数据处理能力DatabaseConstraints数据完整性约束体系约束是数据库层面的数据质量防线，在建表时定义约束能从源头保证数据的唯一性、完整性和合法性。约束类型关键字作用说明示例主键约束PRIMARYKEY唯一标识每条记录，不允许重复和NULLidINTPRIMARYKEY唯一约束UNIQUE保证列值不重复，但允许NULLemailVARCHAR(100)UNIQUE非空约束NOTNULL该列不允许为空值nameVARCHAR(50)NOTNULL默认值约束DEFAULT未赋值时自动取默认值statusINTDEFAULT1检查约束CHECK限制列值必须满足指定条件ageINTCHECK(age>=0ANDage<=150)外键约束FOREIGNKEY引用另一张表的主键，保证引用完整性dept_idINTFOREIGNKEYREFERENCESdept(id)六种约束从不同维度保障数据质量，建表时应根据业务需求合理配置SQLServer·ColumnPropertyIDENTITY自增主键IDENTITY是SQLServer实现自动编号的核心特性，常用于主键列以避免手动分配ID。自增机制保证了唯一性和递增性，但不保证连续性——删除记录后ID不会回收。基本语法IDENTITY(种子,增量)定义起始值和步长，设置为主键列IDENTITY(1,1)自增规则每次INSERT新行自动取当前最大值加增量，无需手动赋值AUTOINCREMENT删除影响删除记录后ID不回收，下一条在被删ID基础上继续递增NORECYCLE手动插入需先SETIDENTITY_INSERTON指定值，插入后OFF关闭IDENTITY_INSERT重置方法TRUNCATETABLE重置种子值，DELETEFROM仅清空不重置TRUNCATESQLServer函数手册常用日期函数日期函数是处理时间维度业务逻辑的核心工具，涵盖获取当前时间、计算日期差值、日期加减运算和日期部分提取等能力。GETDATE()返回当前系统日期和时间，常用于记录创建时间、更新时间等字段的自动填充CURRENTTIMESTAMPDATEDIFF()计算两个日期的差值，如DATEDIFF(day,'2024-01-01',GETDATE())返回距今天数DATEINTERVALDATEADD()在日期上加减指定间隔，如DATEADD(month,3,hire_date)返回入职3个月后的日期DATEOFFSETYEAR/MONTH/DAY分别提取日期的年、月、日部分，如YEAR(hire_date)=2024筛选2024年入职员工DATEEXTRACTCONVERT/FORMAT日期格式化函数，CONVERT(VARCHAR(10),GETDATE(),120)输出'YYYY-MM-DD'格式DATEFORMATSQLFunctions空值处理与字符串函数NULL值处理和字符串操作是数据清洗与报表格式化的基础能力。ISNULL和COALESCE提供灵活的空值替代方案，LEN、SUBSTRING、CONCAT等字符串函数则支撑文本数据的提取、拼接和清洗工作。空值处理函数ISNULL(表达式,替代值)当表达式为NULL时返回替代值，如ISNULL(phone,'未填写')，仅接受两个参数COALESCE(值1,值2,...)