Sql-Server-基础知识总结.docx

一、 语句建库、表1. 回顾1) 数据库文件 主数据文件(.mdf) 次数据文件(.ndf) 日志文件(.ldf)2. 建库1) 语法Create DataBase 数据库名称On primary(Name =逻辑名称,Filename =物理名称,Size =初始大小(单位：KB、MB、GB、TB),MaxSize =最大空间(UNLIMITED),FileGrowth=增长速度)Log on(Name =逻辑名称,Filename =物理名称,Size =初始大小(单位：KB、MB、GB、TB),MaxSize =最大空间(UNLIMITED),FileGrowth=增长速度)2) 必要属性 存储位置 初始大小 增长速度3) 示例-创建Xoa数据库-数据文件初始大小为MB，增长速度为%。无最大上限-日志文件初始大小为MB，增长速度为MB。最大上限为MBcreate database xoaon primary(name=Xoa_Data,fileName=e:dataXoa.mdf,Size=5Mb,MaxSize=unlimited,fileGrowth=15%)log on(name=Xoa_Log,fileName=e:dataXoa.ldf,Size=3MB,MaxSize=100MB,fileGrowth=1MB)4) 注意事项 单位必须完整（必须具有”B”，eg：KB、MB等） 数据库的逻辑名称不能出现重复5) 建议 将数据文件和日志文件进行分开存放 数据库的初始大小及增长速度计算后在定义3. 删除数据库1) 语法Drop database 数据库名2) 示例-2、删除数据库-练习：删除名为Xoa的数据库-检查数据库是否存在-databases：视图，存在与Master数据库中if exists(select * from sys.databases where name=xoa)drop database Xoa3) 建议删除数据库之前尽量使用判断语句检查数据库是否存在。4. 建表1) 数据类型 字符类型 Char：非unicode的固定长度的字符类型 Varchar：非unicode的可变长度的字符类型 Nchar：unicode的固定长度的字符类型 Nvarchar：unicode的可变长度的字符类型 Text 数值Int、float、decimal、number 布尔类型Bit 日期类型Datetime 二进制Image2) 语法Create table 表名( 字段名 类型(长度) 特征)注：特征非空(Not null)、自增(Identity)3) 示例-练习：创建部门表(Department)-记录Id(deptId,int,自增)、部门名称(deptName,varchar(50),非空)create table Department(deptId int identity(1,1),deptName varchar(50) not null)4) 数据完整性 实体完整性 要求唯一标识每行记录 实现主键、唯一、自增 域完整性 要求保证列数据的有效性 实现数据类型、非空、默认值、检查 引用完整性 要求引用完整性约束保证两(多)表之间数据的有效性 实现引用关系 自定义完整性 要求通过编写自定义的逻辑或规则实现数据的验证。 实现存储过程、触发器、规则5) 约束(Constraint) 主键约束(Primary Key Constraint)作用：数据唯一且不允许出现null值 唯一约束(Unique Key Constraint)作用：数据唯一但允许出现一次null值 检查约束(Check Constraint)作用：数据符合规则 引用约束(Foreign KeyReferences)作用：保证两表之间数据的有效性 默认值约束(Default Constraint)作用：提供默认的字段值6) 命名规则缩写_列名7) 分类 行内约束-行内约束create table Users(-主键约束-主键可以由多个字段组成(复合主键)，主键列字段不能为空UserId int identity(1,1) primary key,UserName varchar(20) not null,-唯一约束Account varchar(20) not null unique,-检查约束Pwdvarchar(20) not null check(len(pwd)=6 and len(pwd)=18 and age=6 and len(pwd)=6 and len(pwd)=18 and age=6 and len(pwd)=18 and age=6 and len(pwd)=18 and age100)print yesgo-b、if.else分支语句declare i intset i = 20if(i 100)print yeselseprint nogo-c、嵌套if.else语句declare i int,j intset i = 20set j = 80if(i 100)beginif(j 150)print if-yeselseprint if-noendelsebeginif(j 50)print else-yeselseprint else-noendgo Case语句 语法CaseWhen 条件1 then 值表达式1,，else 值表达式nEnd 示例-按照国际评分标准输出学生成绩信息-包含：学号、课程编号、成绩等级-优：-100-良：-90-中：-80-差：低于分-注：between.and相当于= 且=select StudentNo, SubjectId,level = case when (studentresult between 90 and 100) then 优when (studentresult between 80 and 89) then 良when (studentresult between 60 and 79) then 中when (studentresult between 0 and 59) then 差else 缺考endfrom Result 适用 表中数据结果值的转换，如示例 表结构的转换，如：竖表转横表或横表转竖表2) 循环语句 While语句 语法While 条件表达式Begin 循环体; break/continueend 示例-8、while循环-通过循环语句输出5等个数-声明变量并赋值declare i intset i = 1-循环输出i的值while i=5beginprint iset i = i+1endgo-横向输出个数-声明变量并赋值declare i int , r varchar(100)set i = 1set r = while i=5begin-拼接字符串set r = r + CAST(i as CHAR(5)set i = i+1end-输出拼接后的字符串print r-为所有学生的科目Id为的课程成绩提分-每次提分，且最高成绩不能高于分-如果所有学生均已及格则结束select * from Result where SubjectId =1-定义变量存储不及格学生的人数declare count int-获取不及格学生的人数set count = (select COUNT(Id) from Result where SubjectId=1 and StudentResult0)begin-更新所有学生成绩-条件：科目编号为且小于update Result set StudentResult = StudentResult+5where SubjectId=1 and StudentResult95-重新获取不及格学生的人数set count = (select COUNT(Id) from Result where SubjectId=1 and StudentResult20order by GradeId2) 执行顺序 From：从原始表中抓取所有数据并且存储一个虚拟表中 Where：将虚拟表中所产生数据执行条件处理并且筛选出新数据存储到第二个虚拟表中 Group by ：将虚拟表2中的数据进行分组且存储到虚拟表3中 Having：将虚拟表3中的数据进行筛选并存储到虚拟表4中 Select：从虚拟表4中抓取所有数据并且存储到虚拟表5中 (控制列的数量、列别名等) Order by：对虚拟表5中的数据进行排序并且存储到新的虚拟表中2. 概述所谓子查询是指将一个查询语句作为另一个语句的一部分进行使用。那么这个查询语句就称为子查询。子查询可以用在赋值、查询、修改及删等操作语句中。后三项一般作为条件进行使用。3. 等值子查询1) 概述在条件语句中是通过等值判断(=)使用子查询的语句就成等值子查询。2) 示例declare name varchar(20)set name = 杨阳select name,* from Resultwhere StudentNo=(select StudentNo from Student where StudentName=name)3) 注意事项等值子查询中,子查询只能返回单一的结果值。4. 非等值子查询1) 概述所谓非等值子查询是指不是使用“=”运算符进行连接的查询语句。2) 示例select * from resultwhere SubjectId != (select SubjectId from Subject where SubjectName=走进java编程世界)5. not in1) 使用当子查询中返回一组(多个)值时可以使用not in运算符进行连接。2) 示例select StudentNo,StudentName,DATEDIFF(year,bornDate,getDate() as age , sex,phonefrom Studentwhere GradeId in(select GradeId from Grade where GradeName =s1 or GradeName=y2)select a.StudentNo,a.StudentName,a.Sex,b.GradeNamefrom Student a left join Grade b on a.GradeId = b.GradeIdwhere StudentNo not in(select StudentNo from Resultwhere examdate = 2013-9-13 and SubjectId in (select SubjectId from Subject where SubjectName=走进java编程世界 or SubjectName=C#语言和数据库技术)3) 注意事项 In后的子查询只能有一个返回值列（可以有多行）6. not exists1) 概述Exists的返回值为布尔值，可以理解为是否有数据行存在。一般可以用来作为判断使用。2) 示例-编写代码更新email地址信息-如果存在emial地址为null的情况则将该生的emial地址改为未知并输出存在错误地址-否则则输出所有所有学生的信息(学号、姓名、Email)if exists(select * from Student where Email is null)beginupdate Student set Email=未知 where Email is nullprint 错误地址endelseselect StudentNo, StudentName, Email from Student7. 注意事项 子查询中的列如果没有在父查询中出现是不会被显示的 子查询无法在Text、NText和Image类型上使用 子查询中可以使用where、group by及having子句8. 对比 In子查询返回的是一个查询结果集；exists子查询返回的是布尔值9. 应用(什么场合使用连接查询；什么时候使用子查询) 需要返回多表字段时使用连接查询 只返回一个表中数据时使用子查询四、 事务、视图和索引1. 事务1) 概述事务是一个逻辑工作单元，事务中的多个操作视为一个整体，操作之间要么同时成功，要么同时失败。事务可以保证数据的有效性和一致行。2) 特点 原子性事务中的所有操作是整体，要么同时成功，要么同时失败 一致性事务所有操作具有一致状态 隔离性事务和事务之间是隔离的，不可相互操作的 永久性事务成功执行后数据将被永久存储到数据库系统中3) 分类 自动提交事务Server中将insert、delete及Update操作语句就是按照自动方式进行提交的。 隐式事务通过set IMPLICIT_TRANSACTIONS on启动事务，开启后其后的语句自动以事务的方式进行执行。当遇到提交语句后后面默认开启了一个新的事务(隐式事务实际中基本不被使用)。 显式事务显式事务是通过特定的语句进行开启。显式事务是实际应用中应用较多的方式。4) 语句 Begin transaction开启事务 Commit transaction提交事务 Rollback transaction回滚事务(取消前面的操作)5) 示例-业务：银行转账业务(Rose从Mike借了块钱)-转出一方(借方)：账户余额减少-转入一方(贷方)：账户余额增加-定义变量存储错误编号declare err int =0-a、开启事务begin tran-b、执行转账操作-Mike账户转出update account set blance = blance - 490 where UserName=Mikeset err = err + ERRORupdate account set blance = blance + 490 where UserName=Roseset err = err + ERROR-判断是否产生过错误if(err = 0)-提交事务commitelse-回滚事务rollbackgo6) 应用一些大型项目中基本都会应用，如：银行、电商、电信等业务中。事务一定涉及到多个表。7) 缺点事务会降低语句的执行效率。2. 视图1) 概述视图是一个虚拟表,视图中存储的是查询语句.使用视图时其实实在使用他的查询语句.2) 创建方式 视图方式 语句方式3) 语法Create view 视图名称As 查询语句4) 示例-a、定义视图create view vw_resultasselect a.StudentNo,StudentName,Sex,GradeName,d.SubjectName,StudentResult,ExamDatefrom student aleft join Grade b on a.GradeId = b.GradeIdleft join Result c on a.StudentNo = c.StudentNoleft join Subject d on c.SubjectId= d.SubjectIdgo5) 特点 简单视图(涉及一个表或不存在引用关系的)可以像表一样进行CRUD操作 如果视图中查询语句涉及多个表且存在引用关系时一般无法执行删除操作6) 注意事项 视图可以嵌套使用，嵌套层次一般不要超过3层（效率下降） 视图的查询语句不能使用虚拟表或表变量 视图中不能使用order by子句，除非使用了top 视图中不能使用into子句7) 视图的优点 将复杂的查询语句简单化 将多表查询变为简化为单表查询 有效保护隐私数据3. 索引1) 概述索引就是对数据进行重排的一种方式,通过所有可以有效的提高数据的检索(查询)速度.索引中存储了数据或数据的位置。2) 语法Create unique clustered | nonclustered index 索引名On 表名(字段名)Fillfactor = 数值(0100)3) 说明 索引分类 唯一索引：创建唯一约束将自动创建唯一索引，唯一索引要求数据唯一 主键索引：主键索引是唯一索引的一种特例 聚集索引：对表数据的物理顺序进行重排，一个数据表只能创建一个聚集索引 非聚集索引：对表中数据建立索引页。一个表可以创建多个非聚集索引 Fillfactor(填充因子)：设置索引页填充的百分比。取值为0100.填充因子建议设置在5080左右4) 示例-为学生信息表的姓名创建索引create nonclustered index idx_studentNameon student(studentname)5) 优点 索引可以保证数据的唯一行 索引可以提高检索效率6) 缺点 索引会增加额外的存储空间 索引会降低CUD的效率7) 建议8) 查询的列 创建原则 建议在较为短小的列上创建索引 经常被上创建索引 经常进行表连接的列上创建索引 不建议创建原则 列相同值较多的不建议创建索引 数据较少的列不建议创建索引 维护原则 定期进行重建索引9) 查看索引 视图方式-indexes：视图，存储了索引的相关信息select * from sys.indexes 存储过程-存储过程：查看相关表的索引信息sp_helpindex student10) 删除索引 语法Drop index 表名.索引名 示例-5、删除索引-删除表时索引同时被删除if exists(select * from sys.indexes where name=idx_studentName)drop index student.idx_studentName五、 存储过程1. 概述存储过程就是一个有名称的Sql代码。在存储过程中封装了一定的业务规则。存储过程可以有参数和返回值。2. 分类1) 系统存储过程 概述系统由数据库系统进行管理和维护，通常完成特定的操作。系统存储过程是使用sql语句进行编写的。 命名Sp_存储过程名称 示例-sp_databases：列出当前数据系统内的所有数据库信息sp_databases-sp_tables：列出当前数据库下所有的表/视图信息sp_tablessp_tables grade-sp_help：列出数据库内对象的信息sp_help-sp_helpindex：查看指定表的索引信息sp_helpindex student-查看相关表的约束信息sp_helpconstraint student-更改数据库名称sp_renamedb myschool,school2) 扩展存储过程 概述扩展存储过程是使用其他编程语句进行编写的，扩展存储过程一般一Dll的形式存在。 命名Xp_存储过程名称 示例Xp_cmdshell3) 自定义存储过程 概述自定义存储过程是使用Sql语句进行编写的存储过程，由用户进行创建和维护。3. 语法Create procedure 存储过程名称参数名 类型 =默认值 output，As Sql语句段4. 说明 参数：存储过程的参数可以包含输入、输出参数。同时可以对参数设置默认值 语句段：存储过程内的语句段可以包含：变量定义、事务、判断及循环语句5. 示例-a、无参存储过程-无参数只执行一个操作的存储过程create proc proc_selectasselect * from studentgo-c、带参数存储过程-按照学生名称查询学生成绩信息create proc proc_result-输入参数name varchar(20)asselect a.* from result aleft join student b on a.studentno=b.studentnowhere b.studentname=namego-e、多参数存储过程-查询指定科目的所有不及格学生信息-带有默认值的参数列表中,默认值参数需要定义在参数列表的最后create proc proc_select4name varchar(50),score int = 60asselect * from Studentwhere StudentNo in ( select StudentNo from Result where StudentResultscore and SubjectId=(select SubjectId from Subject where SubjectName=name)go-调用存储过程-传递一个参数(默认值参数被省略了)proc_select4 走进java编程世界proc_select4 走进java编程世界,30-g、带有输出参数的存储过程-修改存储过程proc_del3，要求存储过程可以返回受影响行数-如果输出参数带有默认值可以将输出参数定义参数列表的最后-否则，建议将输出参