SQL server数据库开发 - 网站开发专业

河南新华电脑学院,数据库开发及ADO.Net,什么是数据库,数据库Database：数据的仓库DB仓库里有排货架(表)，货物会分类存放。比如牙膏、牙刷会放到一个排货架上，面包会单独放在一个排货架上。仓库又分很多种粮仓、武器仓库。仓库中还会有仓库管理员(DBA)对货物进行管理从仓库中拿货物需要凭证，取货和进货的人(程序员),我们怎么存储数据,学校的档案室计算机文件,文件操作，开发人员需要熟悉操作磁盘文件的函数、必须编写复杂的搜寻算法才能高效的把数据从文件中检索出来、当数据格式发生变化时，需要编写复杂的文件格式升级程序、很难控制并发修改。数据库（其实也是文件），DBMS(数据库管理系统)RDBMS对于数据不仅仅是需要存储，更重要的是将数据进行存储以后怎么才能方便快捷的查询、修改。数据库特点：海量存储、查找速度快、并发性问题控制、安全性、数据完整性(保存在数据库中的数据是正确的，真是的)。,数据库概述,DBMS（DataBaseManagementSystem，数据库管理系统）和数据库。平时谈到“数据库”可能有两种含义：MSSQLServer、Oracle等某种DBMS；存放一堆数据表的一个分类（Catalog）。不同品牌的DBMS有自己的不同的特点：MYSQL（速度很快，适合对数据要求并不是十分严谨的地方，去掉了很多中小型企业中不常用的功能）、MSSQLServer（与.net结合很好,只能运行在windows平台下，大数据量、大交易量表现并不十分好）、DB2（大型）、Oracle（大型）、Access（文件）、SQLite（极其轻量级数据库）、Sybase等。对于开发人员来讲，大同小异SQL(语言)SQLServerMSSQLServer。最常见的错误。除了Access、SQLServerCE、SQLite等文件型数据库之外，大部分数据库都需要数据库服务器才能运行。学习、开发时是连接本机的数据库，上线运行时是数据库运行在单独的服务器。,数据库中的概念,数据库DataBase，不同类的数据应该放到不同的数据库中便于对各个数据类别的进行个性化管理避免命名冲突安全性更高Table（表）：关系数据库中的关系指的就是表。不同的货物要放到各自的货物架，将这种区域叫做“表”（Table）。不同的表根据放的数据不同进行空间的优化，找起来也方便。列（Column）、字段（Field）、行(Row)元组用表格格式化数据：即便是引入了自动识别设备也很容易识别。,2003年5月入职，是产品开发部的，姓名马小虎,王二小，技术支持部，入职是2005年7月,思考：1.一张表中有很多条记录，有没有可能出现重复的记录？2.什么叫做重复的记录？3.我们可以允许表中有重复的记录吗？为什么？,主键（PrimaryKey）数据库的一种约束,主键就是数据行的唯一标识。不会重复的列才能当主键。一个表可以没有主键，但是会非常难以处理，因此没有特殊理由表都要设定主键主键有两种选用策略：业务主键和逻辑主键。业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键。,表间关联、外键（ForeignKey）冗余,SQLServer的管理,需要安装SQLServer2005或者SQLServer2008，若要使用SQLServer管理工具进行开发还要安装SQLServerManagementStudio，还可以使用VisualStudio进行管理使用免费的SQLServerExpress版本，Express版本的服务器名称.SQLEXPRESS，对于开发人员来讲和其他版本没有区别。(Express版本的数据库功能有一些限制，比如：数据文件最大大小为4GB等等。)SQLServer的两种验证方式：用户名验证和Windows验证，开发时用Windows验证就行。开发人员关注点在开发上，而不是配置、备份等之上，那是DBA做的事情。,创建数据库,创建数据库，创建表，设置主键数据库的分离和附加MSSQLServer的每个数据库包含：1个主数据文件(.mdf)必须。1个事务日志文件（.ldf）必须。可以包含：任意多个次要数据文件(.ndf)多个事务日志文件文件组：可将多个数据文件逻辑的分到一组，方便日后管理维护（备份、将表建在指定的文件组上等等。）创建：部门Id,部门名称：员工Id,身份证号，姓名，性别，入职日期，年龄，地址，电话，所属部门、Email,数据库中的数据类型,练习,创建一个School数据库。创建一个班级表：TblClass：tClassId（班级编号，自动编号，主键）、tClassName（班级名称）、tClassDesc（班级简介）。创建一个学生信息表：TblStudent：tSId（学生编号，自动编号，主键）、tSName（学生姓名）、tSGender（性别）、tSAddress（家庭地址）、tSPhone（电话）、tSAge（年龄）、tSBirthday（出生日期）、tSCardId（身份证号）、tClassId（班级Id）分离数据库在需要分离的数据库上点右键-任务-分离附加数据库(在其他计算机上，亲自测试！注意权限问题)在数据库节点上点右键-附加打开数据之前，要打开数据库服务,SQL语句入门(脚本、命令),SQL全名是结构化查询语言（StructuredQueryLanguage），是关系数据库管理系统的标准语言SQL语句是和DBMS“交谈”专用的语句，不同DBMS都认SQL语法。=SQL语句中字符串用单引号、单等号。select*、SeLeCT*：SQL语句是大小写不敏感的，不敏感指的是SQL关键字，字符串值还是大小写敏感的（大小写是否敏感取决于具体的数据库排序规则设置）-在数据库中两个表示一个【转义】=建库、删除数据库、创建表、删除表不仅可以手工完成，还可以执行SQL语句完成，在自动化部署、数据导入中用的很多简单的Insert语句。（*）SQL主要分DDL（数据定义语言,建表、建库等语句。）、DML（数据操作语言）和DCL（数据库控制语言）。CreateTable、DropTable、AlterTable等属于DDL，Select、Insert、Update、Delete等属于DML，GRANT授权、REVOKE取消授权属于DCL,使用sql语句创建数据库和表,使用SQL语句创建School数据库、TblClass表、TblStudent表。Go：将T-SQL语句分批发送到数据库实例执行。tSIdtSNametSGendertSAddresstSPhonetSAgetSBirthdaytSCardIdtSClassId,创建表练习,创建数据库School，创建一个学生表TblStudent-tSId（主键）-tSName-tSGender-tSAddress-tSPhone-tSAge-tSBirthday-tSCardId-tSClassId创建学生成绩表TblScoretScoreId（成绩id,主键,自动编号）、tSId（学生编号）、tEnglish（英语成绩）、tMath（数学成绩）创建老师表TblTeachertTId、tTName、tTGender、tTAge、tTSalary、tTBirthday,介绍生成脚本工具,数据库右键任务生成脚本可选择生成什么样的脚本选择生成的数据库版本是否包含某些脚本等。是否生成带数据的脚本（2005、2008都有该功能express没有。）,简单数据插入insert,向表中插入一行（该行的每一列都有数据）insertinto表(列名，列名)values(值1，值2)insert语句可以省略表名后的列名，但是不推荐。Insertinto表values(值1，值2)插入数据时，只向某些列插入数据：如果插入的行中有些字段的值不确定，那么Insert的时候不指定那些列即可。Insertinto表(列1)values(值1)自动编号列不需要手动插入。【SETIDENTITY_INSERT表名ON】注意：主键不能有重复值。插入数据时的单引号问题。Insertinto表（列）select列1，列2unionInsertinto表（列）select列1，列2from表Select列into新表名from旧表N前缀：N字符串，在服务器上执行的代码中（例如在存储过程和触发器中）显示的Unicode字符串常量必须以大写字母N为前缀。即使所引用的列已定义为Unicode类型，也应如此。如果不使用N前缀，字符串将转换为数据库的默认代码页。这可能导致不识别某些字符。在Northwind数据库下测试print中文中文。通过Ado操作时如果遇到sql字符串也需要加前缀N。如果使用带参数sql语句则不需要加N.,简单数据更新(数据修改),更新一个列：updateStudentsetsSex=男更新多个列：updateStudentsetsSex=女,sAge=18,sBirthday=1989-8-8更新一部分数据：updateStudentsetsClassId=4wheresClassId=1，用where语句表示只更新Name是tom的行，注意SQL中等于判断用单个=，而不是=。Where中还可以使用复杂的逻辑判断updateStudentsetsAge=30wheresName=华佗orsAge20andsAge、=、（或!=）等,简单数据删除,删除表中全部数据：DELETEFROMStudent。Delete只是删除数据，表还在，和DropTable不同。Delete也可以带where子句来删除一部分数据：DELETEFROMStudentWHEREsAge20=truncatetablestudent的作用与deletefromstudent一样，都是删除student表中的全部数据，区别在于：1.truncate语句非常高效。由于truncate操作采用按最小方式来记录日志，所以效率非常高。对于数百万条数据使用truncate删除只要几秒钟，而使用delete则可能耗费几小时。2.truncate语句会把表中的自动编号重置为默认值。3.truncate语句不触发delete触发器。,练习：,插入3条老师信息和5条成绩信息、用SQL插入2条员工信息（注意：bit类型，在写代码中用1或0来表示，不要用false,会进行类型转换的。）EmpId,EmpIdCardNo,EmpName,EmpGender,EmpJoinDate,EmpAge,EmpAddress,EmpPhone,EmpEmail,DeptId练习1：给studentId是1的英语成绩加10分练习2：考试题偏难，所有人的成绩加5分练习3：所有女学生的年龄减1岁删除工资大于2000的老师=将老师表清空=删除所有老师删除数据时候把自增长列的值还原成种子,约束-保证数据完整性,见备注，创建Employee表和Department表。先用设计器创建约束、再用代码创建约束。数据库约束是为了保证数据的完整性(正确性)而实现的一套机制见文件Employee.sql非空约束主键约束(PK)primarykeyconstraint唯一且不为空唯一约束(UQ)uniqueconstraint唯一，允许为空，但只能出现一次默认约束(DF)defaultconstraint默认值检查约束(CK)checkconstraint范围以及格式限制外键约束(FK)foreignkeyconstraint表关系增加外键约束时，设置级联更新、级联删除：ONDELETENOACTION|CASCADE|SETNULL|SETDEFAULTONUPDATENOACTION|CASCADE|SETNULL|SETDEFAULT,约束练习,Teacher表中tSex控制只能是男女，默认男tAge在30-40之间默认30Score表中studentId是外键先要把Student表中的sId设置为主键测试外键约束：1：在学生表（主表）中删除在成绩表中被引用的学生记录。2：成绩表中添加一条新成绩，studentId在学生表中没有。保存SQL脚本。再次打开即可执行。,用t-sql代码来创建约束,操作TblEmployee表。,数据检索（查询）,执行备注中的代码创建测试数据表。简单的数据检索：SELECT*FROMStudent只检索需要的列：SELECTsNameFROMStudent、ameFROMStudentWHEREsSex=女。还可以检索不与任何表关联的数据：select1+1;selectselectgetdate();,Top、Distinct,Top获取前几条数据，top一般都与orderby连用获得年纪最小的5个学生获得年纪最大的10%的学生(percent)Distinct去除重复数据selectdistinctsNamefromstudentselectdistinctsName,sAgefromstudentDISTINCT是对查询出的整个结果集进行数据重复处理的，而不是针对某一个列。1.建表，带重复数据。演示distinct2.通过altertable增加identity列，再演示distinct。altertableTT2addautoIdintidentity(1,1)3.演示查询除主键外的其他列重复数据。,聚合函数,SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG（平均值）、SUM（和）、COUNT（数量:记录的条数。）聚合函数对null值不计算。如果一行的数据都是null，count(*)包含对空值行、重复行的统计。平均成绩selectavg(english)fromscore男学生出生日期的最大值和最小值：selectmax(sBirthday),min(sBirthday)fromstudentwheresSex=男,带条件的查询,Selectfromwhere查询没有及格的学生的学号查询年龄在20-30岁之间的男学生Betweenand在之间查询年龄在20-30岁之间的男学生查询math成绩在80-90分之间的所有学生建议：使用betweenand。（闭区间）查询班级id为1，2，3的所有学生selectsName,sAgefromstudentwheresClassId=1orsClassId=2orsClassId=3selectsName,sAgefromstudentwheresClassIdin(1,2,3)如果可能in尽可能替换为=1and=60andmath=60ORDERBYenglishDESC,mathDESCOrderby语句一般要放到所有语句的后面，就是先让其他语句进行筛选，全部筛选完成后，最后排序一下。（表中数据是集合，集合是没有顺序的。Orderby返回的数据是有顺序的，故此我们把orderby以后返回的数据集合叫“游标”。）,数据分组groupby,在使用select查询的时候，有时需要对数据进行分组汇总（即：将现有的数据按照某列来汇总统计），这时就需要用到groupby语句。select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息。/分组一般都和聚合函数连用。1.请从学生表中查询出每个班的班级Id和班级人数：（见备注1）2.请从学生表中查询出每个班的班级Id和班级中男同学的人数：（见备注2）GROUPBY子句必须放到WHERE语句的之后,GroupBy与OrderBy都是对筛选后的数据进行处理，而Where是用来筛选数据的。没有出现在GROUPBY子句中的列是不能放到SELECT语句后的列名列表中的（聚合函数中除外）错误：selectsClassId,count(sName),sAgefromstudentgroupbysClassId正确：selectsClassId,count(sName),avg(sAge)fromstudentgroupbysClassId,Having语句(对组的筛选，哪些组显示哪些组不显示),对表中的数据分组后，会得到一个分组后的结果集，如何对该结果集在进行筛选？having查询班级人数超过三个人的班级。（见备注1）注意Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。Having是GroupBy的条件对分组后的数据进行筛选（与Where类似，都是筛选，只不过having是用来筛选分组后的组的。）在Where中不能使用聚合函数，必须使用Having，Having要位于GroupBy之后。Having的使用几乎是与where一样的，也可以用in。Havingcount(*)in(5,8,10),SQL语句的执行顺序,5Select5-2distinct,7top(应用top选项最后计算)5-1选择列1From表2Where条件3Groupby列4Having筛选条件6Orderby列,Groupby练习,从MyOrders表中查询：1.热销售商品排名表，【即按照每种商品的总销售数量排序】。2.请统计销售总价超过3000元的商品名称和销售总价,并按销售总价降序排序。3.统计各个客户对可口可乐的喜爱度(既统计每个购买人对“可口可乐”的购买量),类型转换函数,CAST(expressionASdata_type)CONVERT(data_type,expression,style)Select您的班级编号+1错误这里+是数学运算符SELECTFIdNumber,CAST(RIGHT(sNo,3)ASINTEGER)as后三位的整数形式,CAST(RIGHT(sNo,3)ASINTEGER)+1as后三位加1,CONVERT(INTEGER,RIGHT(sNo,3)/2as后三位除以2FROMstudent对编号排序，但编号是字符串类型。1、2、11、3、21、36对日期的转换。转换成各种国家格式的日期。selectconvert(varchar(20),getdate(),104)Style的格式，查sql帮助。（输入convert函数查询）将日期转换为指定格式的字符串。日期字符串,联合结果集union(集合运算符),集合运算符是对两个集合操作的，两个集合必须具有相同的列数，列具有相同的数据类型（至少能隐式转换的），最终输出的集合的列名由第一个集合的列名来确定。（可以用来连接多个结果）联合(union)与连接(join)不一样简单的结果集联合(老师、学生)：selecttName,tSexfromteacherunionselectsName,sSexfromstudent基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。selecttName,tSex,-1fromteacherunionselectsName,sSex,sClassIdfromstudent联合：将多个结果集合并成一个结果集。union(去除重复，相当于默认应用了distinct)、unionall（保留所有结果，不去除重复）常见应用：底部汇总。使用Unionall,Unionall,selecttName,tSexfromteacherunionselectsName,sSexfromstudentUNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条selecttName,tSexfromteacherunionallselectsName,sSexfromstudentUnion因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNIONALL,案例1,要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩查询结果为3行：selectenglish最高成绩,max(english)fromscoreunionallselectenglish最低成绩,min(english)fromscoreunionallselectenglish平均,avg(english)fromscore查询结果为1行：Selectmax(english),min(english),avg(english)fromscore;,案例2,查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资底部汇总：selecttName,tSalaryfromteacherunionallselect平均工资,avg(tSalary)fromteacherunionallselect最高工资,max(tSalary)fromteacher,练习,从MyOrders表中统计每种商品的销售总价，并且在底部做汇总。,一次插入多条数据,insertintoScore(studentId,english,math)select1,80,100unionallselect1,80,100unionallselect3,50,59unionallselect4,66,89unionallselect5,59,100此处如果用union同样会去除重复数据。,*一次插入多条数据,-把现有表的数据插入到新表（表不能存在）,为表建备份。-select*intonewStudentfromstudent（newStudent表在select查询的同时自动建立。）-把现有表的数据复制到一个已存在的表通过这种方式复制，只能复制表中的数据，以及列的名字和数据类型。对于约束，不会复制过来。Select*intonewTblfromoldTblwhere11,这样做可以只复制表结构，但效率并不高。建议：selecttop0*intonewTblfromoldTbl=如果表已经存在了=-insertintobackupStudentselect*fromstudents(backupStudent表必须提前建好),字符串函数（*）,LEN()：计算字符串长度（字符的个数。）datalength();/计算字符串所占用的字节数，不属于字符串函数。测试varchar变量与nvarchar变量存储字符串a的区别。见备注1.LOWER()、UPPER()：转小写、大写LTRIM()：字符串左侧的空格去掉RTRIM()：字符串右侧的空格去掉LTRIM(RTRIM(bb)LEFT()、RIGHT()截取取字符串SELECTLEFT(abcdefg,2)SUBSTRING(string,start_position,length)，索引从1开始。参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。SELECTSUBSTRING(abcdef111,2,3)尝试使用SQLServer的帮助。,日期函数(在帮助中输入“日期函数”),GETDATE()：取得当前日期时间DATEADD(datepart,number,date)，计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位，可选值见备注。DATEADD(DAY,3,date)为计算日期date的3天后的日期，而DATEADD(MONTH,-8,date)为计算日期date的8个月之前的日期。（入职一年以上的员工发1000$）,入职日期+1年6000金牌，5500银牌,4500铜牌，否则普通）在成绩表中，查询的时候增加一列，显示“优”、“良”、“中”、“差”,练习2,单号金额Rk110Rk220Rk3-30Rk4-10将上面的表输出为如下的格式：单号收入支出Rk1100Rk2200Rk3030Rk4010,练习3-热身,查询TeamScore表将原表数据：,转换为：,数据透视-横表转纵表,案例1：查询表NBAScore，将表中数据转换为：,练习，学生成绩表操作,作业：,从MyOrders表中统计出，每种商品，每个销售员的销售数量。如图：,子查询,把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用)子查询的几种情况：子查询作为数据源，子查询作为where条件，子查询作为列select列=(子查询)例如：Select*from(selectcol1,col2fromtab)ast子查询基本分类：独立子查询子查询可以独立运行相关子查询子查询中引用了父查询中的结果In、exists、notin、notexists,子查询,，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。SELECT*FROM(SELECT*FROMstudentwheresAge、=之后，或子查询用作表达式时，这种情况是不允许的。,子查询,如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。可以使用in关键字代替=号查询高一一班和高二一班的所有学生select*fromstudentwheresClassIdin(selectcIdfromclasswherecName=高一一班orcName=高二一班)等价于：备注1.查询刘关张的成绩selectstudentId,englishfromscorewherestudentIdin(selectsIdfromstudentwheresName=刘备orsName=关羽orsName=张飞)快速实现删除多个学生deletefromstudentwheresIdin(selectsIdfromstudentwheresName=刘备orsName=关羽orsName=张飞),分页,数据库中分页的实现,见备注。每页5条，数据，查询第4页的数据。思路：1.找出第(4-1)页的所有数据，2.从表中的数据中排除出去前(4-1)也的数据，用notin。剩下的数据orderby然后top5.上面是sql2000以前的实现方式。SQLServer2005后增加了Row_Number函数简化实现。限制结果集。返回第3行到第5行的数据（ROW_NUMBER不能用在where子句中，所以将带行号的执行结果作为子查询，就可以将结果当成表一样用了）：select*from(selectrow_number()over(orderbysIdasc)asnum,*fromstudent)asswheres.numbetween6and10orderbysIdascMSSQLServer9.0.1399版本中row_number()不变色，但可以使用。,over子句(*),在应用具体的聚合函数、排名函数前对行集的分区和排序。over子句，用于支持窗口的计算,所以一般与排名开窗函数、聚合开窗函数一起使用。窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。以前使用聚合函数必须分组，即便没有groupby子句，也是默认将表中所有的数据分成了1组，来聚合。通过使用over子句可以在不分组的情况下实现聚合运算，在查询结果集中既包含基础行的数据也包含聚合函数的值。（可以把over()子句理解成是“后台运行的数据”，只是为了让聚合函数或者是排名函数“用一下”，并不影响实际显示的数据。在后台提供数据。）over子句的两种使用方式：1.over子句与排名开窗函数一起用，语法：over(partitionby列1orderby列2)。必须有orderby子句2.over子句与聚合开窗函数一起用，语法：over(partitionby列1)不能使用orderby子句了。【先演示这个】注：当over()子句与聚合函数一起使用时，也可以直接写count(*)over()不在over()子句中进行任何分区，表示把整个表分为一个区。,案例练习,1.统计每个销售员的总销售金额。2.统计每个(订单/销售员)的销售金额占总销售金额的百分比。要求：表中其他列数据也必须同时显示。即在最后追加两列数据（每个销售员的总销售金额，百分比）3.使用row_number()函数实现分页。,连接查询(join),交叉连接(两种语法crossjoin和,)内连接(innerjoin),多表内连接。无论几张表连接，每次执行都是两张表进行连接。外连接左外联(leftouterjoin)右外联(rightouterjoin)连接查询的基本执行步骤：1笛卡尔积2应用on筛选器3添加外部行，到此from执行完毕。（注：添加外部行是在on筛选器执行完毕后才执行的，所以不要在on条件中继续通过and应用其他条件，应该把其他条件放在where中使用）,表连接Join(联接),案例1：查询所有学生的姓名、年龄及所在班级案例2：查询年龄超过20岁的学生的姓名、年龄及所在班级案例3：查询学生姓名、年龄、班级及成绩案例4：查询所有学生(参加及未参加考试的都算)及成绩案例5：请查询出所有没有参加考试（在成绩表中不存在的学生）的学生的姓名。InnerJoin、LeftJoin、RightJoin、(自连接)(*)CrossJoin交叉连接笛卡尔积其它连接的基础,练习1：查询所有英语及格的学生姓名、年龄及成绩练习2：查询所有参加考试的,english分数不为null学生姓名、年龄及成绩练习3：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于englishormath60分显示不及格,自连接（*）,请将TblArea表显示如下：采用自连接,视图概述View,回顾数据怎么存储的视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录。相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）视图的目的是方便查询，所以一般情况下不能对视图进行增删改优点：筛选表中的行降低数据库的复杂程度防止未经许可的用户访问敏感数据,普通视图并不存储数据（虚拟表），访问的是真实表中的数据使用视图注意事项：1.视图中的查询不能使用orderby，除非指定了top语句。视图被认为是一个虚拟表，表是一个集合，是不能有顺序的。而orderby则返回的是一个有顺序的，是一个游标。在视图中使用selecttoppercent+orderby问题。所有查询的列，必须有列名,且列名必须唯一createviewvw_nameas后不能跟beginend.（*）索引视图在视图上创建唯一聚集索引数据会保存在数据库中而不是引用表中的数据。（*）分区视图，一个表中的数据水平拆分成多个表，用union连接（可能放在多个服务器上）。select*fromsteve-pc.itcast2013.dbo.customers,视图（视图中不允许使用orderby）,createviewvw_tabasselecttop100percent*fromtaborderbycol1desc以上语句执行完毕以后查询select*fromvw_tab也不会排序，与select*fromvw_taborderbycol1desc不一样。top已经取得了上次orderby的结果前几个结果，并且top输出的结果没有再次排序，所以无法保证输出的结果是desc排序后的结果,局部变量_先声明再赋值,声明局部变量DECLARE变量名数据类型=默认值DECLAREnamevarchar(20)DECLAREidint,赋值SET变量名=值-set用于普通的赋值SELECT变量名=值-用于从表中查询数据并赋值，可以一次给多个变量赋值,例如：SETname=张三SETid=1SELECTname=sNameFROMstudentWHEREsId=id,输出变量的值,SELECT以表格的方式输出,可以同时输出多个变量PRINT以文本的方式输出，一次只能输出一个变量的值SELECTname,idPRINTnamePRINTidprintname,id-错误！,变量种类,变量分为：局部变量：局部变量必须以标记作为前缀，如Ageint局部变量：先声明，再赋值全局变量（系统变量）：全局变量必须以标记作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值,全局变量,IFELSE,IF(条件表达式)BEGIN-相当于C#里的语句1END-相当于C#里的ELSEBEGIN语句1END,计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生declareavgfloatselectavg=avg(english)fromscoreprint平均分数+convert(varchar(20),avg)if(avg60)Beginprint前三名selecttop3sName,englishfromstudentinnerjoinscoreonstudent.sId=score.studentIdorderbyenglishdescEndElsebeginprint后三名selecttop3sName,englishfromstudentinnerjoinscoreonstudent.sId=score.studentIdorderbyenglishascend,例：,WHILE循环,WHILE(条件表达式)BEGIN-相当于C#里的语句continueBREAKEND-相当于C#里的-计算1-100之间所有奇数的和。,如果english不及格的人超过半数(考试题出难了)，则给每个人增加2分,循环加，直到不及格的人数少于一半。,例：,把所有未及格的人的成绩都加及格,事务-为什么需要事务,如，转账问题：假定钱从A转到B，至少需要两步：A的资金减少然后B的资金相应增加,updatebanksetbalance=balance-1000wherecid=0001updatebanksetbalance=balance+1000wherecid=0002-都选中一起执行，查看结果。SELECT*FROMbank注意约束：金额不能小于10,会否出问题？,什么是事务(Transaction),事务：同生共死指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)-也就是由多个sql语句组成，必须作为一个整体执行这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行语法步骤：开始事务：BEGINTRANSACTION事务提交：COMMITTRANSACTION事务回滚：ROLLBACKTRANSACTION判断某条语句执行是否出错：全局变量ERROR；ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；例：SETerrorSum=errorSum+errorSETIMPLICIT_TRANSACTIONSON|OFF隐式事务,存储过程,存储过程-就像数据库中运行方法(函数)和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。前面学的ifelse/while/变量/insert/select等，都可以在存储过程中使用优点：执行速度更快在数据库中保存的存储过程语句都是编译过的允许模块化程序设计类似方法的复用提高系统安全性防止SQL注入（与带参数的sql语句一样可以防止Sql注入攻击，带参数的sql语句其实是调用了sp_executesql存储过程。）减少网络流通量只要传输存储过程的名称系统存储过程由系统定义，存放在master数据库中名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头。xp_cmd自定义存储过程由用户在自己的数据库中创建的存储过程usp,系统存储过程,创建存储过程,定义存储过程的语法CREATEPROCEDURE存储过程名参数1数据类型=默认值OUTPUT,参数n数据类型=默认值OUTPUTASSQL语句参数说明：参数可选参数分为输入参数、输出参数输入参数允许有默认值EXEC过程名参数,调用带参数的存储过程,无参数的存储过程调用：Execusp_upGrade有参数的存储过程两种调用法：EXECusp_upGrade260,55-按次序EXECusp_upGrade2english=55,math=60-参数名参数有默认值时：EXECusp_upGrade2-都用默认值EXECusp_upGrade21-第一个用默认值EXECusp_upGrade21,5-不用默认值若想英语的用默认值，数学的及格分数改了怎么办？,编写存储过程,作业：考试题出难了，降低及格分数线,(将该题目封装为一个存储过程。)编写存储过程usp_upGrade要求传入参数:passfloat调用存储过程，及格分数线，给没及格的人提分,存储过程中使用输出参数,如果希望在加分的过程中想输出总共加了多少次分输出参数关键字：OUTPUTdeclareaintexecusp_ppcanshu=aoutputprinta,通过执行存储过程,实现登陆的存储过程(作业)实现分页的存储过程（*）案例：使用存储过程、事务、winform实现转账。编写存储过程版的SQLHelper,通过存储过程实现，调用存储过程实现,作业：MyOrders表：查询出每个销售员的销售金额每个销售员的销售金额占总销售额的百分比统计每张订单销售金额咱总订单金额的百分比复习通过调用存储过程增删改查如果获取带输出参数的存储过程。存储过程实现对特定表的分页,复习：,1.复习2.分页存储过程3.存储过程+事务实现转账+实现4.处理作业问题,补充,问题：newSqlParameter(“参数名”,“值”);newSqlParameter(“id”,0);有问题？报错信息：参数化查询(tidbigint)insertintotblT1values(tid)需要参数tid，但未提供该参数。2.多层开发的时候（多个项目），app.config文件的位置。,索引Index(*),全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。如果没有目录，查汉语字典就要一页页的翻，而有了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。创建索引的方式，在表设计器中点击右键，选择“索引/键”添加在列中选择索引包含的列。使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、Update、Delete的速度。只在经常检索的字段上(Where)创建索引。（*）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。-删除索引dropindexT8.IX_T8_tage,索引意味着排序，排序后则可更高效的查询。,按年龄建索引,参考：,触发器trigger,触发器的作用：自动化操作，减少了手动操作以及出错的几率。触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。【在SQL内部把触发器看做是存储过程但是不能传递参数】一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。那究竟何为触发器？在SQLServer里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。,常见的触发器,DML触发器Insert、delete、update（不支持select）after触发器(for)、insteadof触发器（不支持before触发器）(*)DDL触发器Createtable、createdatabase、alter、drop.,inserted表与deleted表,inserted表与deleted表是干什么的？inserted表包含新数据insert、update触发器会用到deleted表包含旧数据delete、update触发器会用到,After触发器：在语句执行完毕之后触发按语句触发，而不是所影响的行数，无论所影响为多少行，只触发一次。只能建立在常规表上，不能建立在视图和临时表上。(*)可以递归触发，最高可达32级。update(列)，在update语句触发时，判断某列是否被更新，返回布尔值。介绍如何编写after触发器。insteadof触发器用来替换原本的操作不会递归触发可以在约束被检查之前触发可以建在表和视图上（*）介绍如何编写insteadof触发器,常用语法,CREATETRIGGERtriggerNameON表名after(for)（for与after都表示after触发器）|insteadofUPDATE|INSERT|DELETE（insert,update,delete）ASbeginend,触发器-插入,CREATETRIGGERtr_updateStudentONscoreforINSERTASBegindeclaresidint,scoreidintselectsid=studentId,scoreid=sidfrominsertedifexists(select*fromstudentwheresid=sid)print插入成功elsedeletefromscorewheresid=scoreIdEndInsertintoscore(studentId,english)v