Chapter4_T-SQL与可编程对象

第4章T-SQL与可编程对象,SQL语言是结构化查询语言（StructureQueryLanguage）的简称，它是一个通用的、功能强大的关系数据库操作语言，最初由IBM公司在20世纪70年代中期开发成功。并被国际标准化组织采纳为关系数据库语言的国际标准。Transact-SQL（简称T-SQL）是SQLServer中支持的扩展SQL语言，它提供了数据定义、数据操纵、数据控制等语句，支持对数据库的操纵和管理，是开发数据库应用程序的基本语言。SQLServer支持采用T-SQL语言定义服务器端应用程序，这些应用程序被统称为可编程对象。,4.1.1T-SQL概述,1.T-SQL语言的特点1）非过程化：使用T-SQL时，不必描述解决问题的全过程，只需提出“做什么”，至于“如何做”的细节则由语言系统本身去完成并给出操作的结果。2）两种不同的使用方式：两种方式下，T-SQL语言的语法结构基本相同。联机交互方式：SQL语言可独立使用嵌入程序设计语言中：例如嵌入C#、VB.net中。3)高度一体化：集数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）和T-SQL增加的语言元素于一体，可独立完成数据库生命周期的所有活动。4)语言简洁、易学易用。,面向过程的程序设计语言，必须一步一步地将解题的全过程描述出来，不仅要告诉计算机“做什么”，而且还要告诉计算机“怎么做”。,2.T-SQL语言的组成元素DDL(DataDefinitionLanguage)数据库定义语言，用于定义或修改数据库。包括Create、Alter、Drop等语句。例如：CreateDATABASEStudentDML(DataManipulationLanguage)数据库操纵语言，实现对数据库基本操作，包括Selete、Insert、Update、Delete等语句。DCL(DataControlLanguage)数据库控制语言，实现对数据对象的授权、数据完整性规则描述及控制事务等，包括Grant、Revoke、Deny等语句。T-SQL增加的语言元素变量、运算符、函数、流程控制语句和注解等。,3T-SQL语言书写规则,在T-SQL语句中，不区分字母大小写，即大小写字母的意义完全相同。,一条语句可以写在一行，也可以写在若干行上。,在本讲义中：为了阅读方便，用大写字母书写SQL语句的关键词，用小写字母书写标识符、表达式及各种参数；为了体现语法结构，一条语句写在多行上。,例如：SELECT*FROMStudent,4注释为了增强程序代码的可读性，可在适当的地方加上注释。T-SQL有两种注释方法：单行注释（-）。语法格式如下：-注释文本内容多行注释（/*/）。语法格式如下：/*注释文本内容*/例如：USESchool/*打开School数据库。当第一次访问某数据库时，需要使用USE语句打开数据库，否则其他访问该数据库的SQL语句不能执行*/SELECT*FROMStudent-查询显示学生表中所有学生的信息,数据定义语言主要是定义数据库中的对象，包括数据库、数据表、视图、索引、存储过程、触发器等的建立或修改。常用语句如下：,4.1.2数据定义语言DDL,1DDL概述,如果想查看或保存已存在的数据库对象的DDL语句，可在SQLServerManagementStudio中自动生成各种数据库对象的SQL脚本，也可将其保存到一个扩展名为.sql的文件中，在查询编辑器中可打开.sql文件，执行SQL脚本，重建相应的数据库。,提示：使用SQL定义脚本重建的数据库不包括表中数据。,2自动生成数据库定义的SQL脚本,前面已经学习过使用SQLServerManagementStudio创建各种数据库对象，实质上手工创建的过程就是在构建DDL语句。,SQL脚本的生成方法如下：,右击要生成SQL脚本的数据库或数据表，选“编写数据库(或表)脚本为/Create到/新查询编辑窗口”。,在“查询编辑”窗口可查看和复制SQL脚本。选择“文件/保存”或“文件/另存为”菜单可将这些SQL脚本保存到一个扩展名为“.sql”的文本文件。,使用“文件/打开”菜单可打开脚本文件。执行可创建新的表。,（1）创建数据库,【例4.1】创建一个名为SchoolTest的数据库。,3几个常用DDL语句,CREATEDATABASE数据库名ONNAME=逻辑文件名，FILENAME=物理文件名,答案1:CREATEDATABASESchoolTest-数据库名,提示：表示可选项,以系统数据库model作为模板在SQLServer安装文件夹的Data文件夹下（一般为C:ProgramFileMicrosoftSQLServerMSSQLData）建立数据库文件SchoolTest.mdf（大小为3MB）和日志文件SchoolTest_log.LDF（大小为1MB）。,答案2:CREATEDATABASESchoolTest-数据库名ON(NAME=SchoolTest,-主数据文件逻辑名FILENAME=D:SchoolTest.MDF)-主数据文件物理名,创建了一个数据库SchoolTest，其主数据文件为大小为3MB的D:SchoolTest.mdf“；并且自动创建一个大小为1MB的“D:SchoolTest_log.LDF”作为日志文件。,（2）打开和删除数据库语句,打开数据库：USE数据库名-其中数据库名是要打开作为当前数据库的数据库例如：USESchool-打开数据库School,当用户登录到SQLServer后，系统指定系统数据库master作为默认当前数据库。所谓当前数据库是指当前可以操作的数据库。,删除数据库：DROPDATABASE数据库名例如：DROPDATABASESchoolTest-删除SchoolTest,注意删除数据库时不允许该数据库有对象处于打开状态。,（3）建立数据表语句,CREATETABLE数据表名（列名列数据类型列约束Constraint约束说明）,【例4.2】在数据库“SchoolTest”中建立学生表StudentTest。,CREATETABLEStudentTest(StudentCodeCHAR(8)NOTNULLUNIQUE,/*不允许空，取值唯一*/StudentNameVARCHAR(16)NOTNULL,SexCHAR(2)NOTNULL,LiveInDormBITDEFAULT1，/*默认值为1*/ConstraintStudentPKPrimaryKey(StudentCode),/*StudentCode主键*/ConstraintSexCheckCheck(Sex=男orSex=女)/*Sex检查约束*/),【例4.3】删除StudentTest表。,（4）删除表语句,当数据表不再需要时，可以使用DROPTABLE语句将其删除。,DROPTABLE数据表名,DROPTABLEStudentTest,（5）建立索引语句,【例4.4】在StudentTest表的姓名（StudentName）和生日（Birthday）字段上建立唯一索引NameIndex,姓名按升序排列，姓名相同时，按生日降序排列。,（6）删除索引语句,CREATEINDEX索引名ON数据表名(字段ASC|DESC)-默认为索引顺序为ASC。,DROPINDEX数据表名.索引名,【例4.5】删除StudentTest表中名称为NameIndex的索引。,CREATEUNIQUEINDEXNameIndexonStudentTest(StudentName,BirthdayDESC),DROPINDEXStudentTest.NameIndex,4.1.3数据操纵语言DML,SELECT语句：从一个表或多个表查询数据INSERT语句：向一个表中添加一条记录UPDATE语句：修改表中某一个或几个字段的值DELETE语句：从一个表中删除记录,DML语句实现对数据的查询和更新，主要语句及功能如下：,1查询语句SELECT,又称为选择查询语句，用于从数据库的一个或多个表中检索满足一定条件的记录集，并按一定顺序显示指定的字段内容。,SELECT字段表FROM表名WHERE查询条件GROUPBY分组字段HAVING分组条件ORDERBY字段名ASC/DESC,表示可选项。第2行至最后一行称作SQL子句，不同的子句完成不同的功能。在书写SELECT语句时，字母大小写意义完全相同。语句可以写在若干行上（如上述语法格式中的描述），也可以不换行。,字段表位于关键字SELECT后面，这些字段将作为查询的结果被显示。,可选任意多个字段，字段与字段之间用逗号分隔。,【例4.6】查询Student表中的所有学生的姓名和性别。,(1)字段表,SELECTStudentName,SexFROMStudent,可以使用通配符“*”表示表中的所有字段。,【例4.7】查询Student表中所有学生的全部字段信息。,SELECT*FROMStudent,若字段名或表名中含有空格，则该字段名或表名必须用方括号括起来。,在字段表中，可以使用如下方式将原字段名以新的字段名显示：字段名AS新字段名,【例4.8】查询Class表中班号和班名班号别名为ClassNumber。,提示：“ClassNumber”含空格，要写为ClassNumber,SELECTClassCodeASClassNumber,ClassNameFROMClass,在字段名前可以加上一些关键字，以便进一步优化查询结果。常用的关键字有：,DISTINCT：若多条记录的字段值具有相同数据，只显示一条记录。,【例4.11】显示Course表中开课的学院名称Academy（与所开课程无关）。,SELECTDISTINCTAcademyFROMCourse,由于每个学院都开设了多门课程，因此多条记录在字段Academy具有部分相同的值。,增加Distinct限制后，查询结果中相同的值只显示一次。,TOPn|mPERCENT：查询前n条记录或前m%的记录。,【例4.9】查询Course表中的前3条记录。,【例4.10】查询Course表中前20%的记录，显示课程号和课程名称。,SELECTTOP3*FROMCourse,SELECTTOP20PERCENTCourseCode,CourseNameFROMCourse,新增字段查询。查询的字段可以是表中字段名，也可以是常量和表达式，表达式中可以使用内部聚合函数进行统计计算。,常用函数表,【例4.12】查询Grade表，显示学号、所选课程号及加2分调整后的成绩（Grade），并显示一列说明“情况属实”。,【例4.13】查询Grade表，统计所有学生的分数总和、平均分、最高分、最低分及总选课人次。,SELECTStudentCode,CourseCode,Grade+2AS成绩,情况属实AS说明FROMGrade,SELECTSum(Grade)AS总分,Avg(Grade)AS平均分,Max(Grade)AS最高分,Min(Grade)AS最低分,Count(StudentCode)AS总人次FROMGrade,【例4.14】将Student表中所有记录的学生姓名、出生日期复制到一张新表NewTable。,使用查询创建新表。在字段名后加上“INTO表名”可将查询结果复制到一张新表中。,StudentName,BirthDayINTONewTableFROMStudent,提示：语句运行后，数据库School中产生一张表名为NewTable的新表。刷新数据库，即可查看到。,（2）FROM子句,FROM子句是SELECT语句必不可少的一个子句，指定要查询的数据来自哪个或哪些表或视图。,FROM表名1，表名2，表名n,【例4.15】查询Student表和Grade表，显示学生的选课和成绩情况。,SELECTGrade.StudentCode,StudentName,CourseCode,GradeFROMStudent,GradeWHEREGrade.StudentCode=Student.Student.code,SELECTStudentCode,StudentName,CourseCode,GradeFROMStudent,Grade(),注意：表的排列顺序不影响执行结果；如果FROM子句中包含多个表名，且不同的表中具有相同的字段，那么SELECT子句的字段名必须表示成“表名.字段名”。,（3）WHERE子句,WHERE子句说明查询条件，它是一个可选的子句。它必须紧跟在FROM子句的后面。,WHERE查询条件,T-SQL中常用的关系和逻辑运算,其中，查询条件是一个关系或逻辑表达式。,比较和逻辑运算,【例4.16】查询Student表中所有女学生的姓名。,【例4.17】查询未住校的女学生的情况。,SELECTStudentCode,StudentName,LiveInDormFROMStudentWHERELiveInDorm=0ANDSex=女,SELECTStudentNameFROMStudentWHERESex=女,BETWEENAND运算,BETWEEN值1AND值2,NOTBETWEEN值1AND值2,【例4.18】在Grade表中查询选修课程号为“101”、成绩在70分到90分之间的所有学生，显示其学号及成绩。,查询某字段的值在指定范围内的数据记录（结果包括值1和值2）。,查询不在指定范围中的数据记录。,SELECTStudentCode,CourseCode,GradeFROMGradeWHERECourseCode=101ANDGradeBETWEEN70AND90,此例的WHERE子句也可以改为：WHERECourseCode=101ANDGrade=70ANDGrade=80,如果GROUPBY后的分组字段有多个，则表示多次分组。,【例4.31】统计各学院男、女教师的人数。,SELECTAcademy,Sex,Count(TeacherCode)AS教师人数FROMTeacherGROUPBYSex,Academy,注意GROUPBY的分组字段必须出现在SELECT后的字段列表中（可以是字段，也可在聚合函数中），否则不允许分组。如果GROUPBY后的分组字段有多个，则表示多次分组。,注意：使用GROUPBY子句时，分组字段必须出现在SELECT后的字段列表中（可以是字段，也可在聚合函数中），否则不允许分组。,【例4.32】按成绩升序显示Grade表中的所有数据。,(5)ORDERBY子句：按指定字段为查询结果排序。通常是SQL语句最后一项。,ORDERBY字段名ASC/DESC,其中：ASC为升序排序，DESC为降序排序。缺省排列次序为升序。,ASC:Ascending，DESC:Descending,SELECT*FROMGradeORDERBYGrade,可以指定多个排序的字段。规则：首先用指定的第一个字段对记录排序，然后对此字段中具有相同值的记录用第二个字段进行排序，依此类推。,若在SELECT语句中无此子句，则按原数据表的次序显示数据。,【例4.33】按姓名升序（若姓名相同，则按出生日期降序）显示班级代码、学生姓名及出生日期。,SELECTClassName,StudentCode,StudentNameFROMClassJOINStudentONClass.ClassCode=Student.ClassCodeORDERBYClassName,StudentNameDESC,ClassName默认为ASC,(6).联接查询：涉及多表的查询称为联接查询，可以用两种方法实现。,在FROM子句中指出需查询的表，在WHERE子句中说明两个表相关联字段的联接条件。,【例4.24】查询优异生的情况。要求显示学生姓名、所在班级、认定时间、学分积点。,SELECTStudentName,ClassName,GrantTime,GPAFROMExcellentStudent,Student,ClassWHEREExcellentStudent.StudentCode=Student.StudentCodeANDStudent.ClassCode=Class.ClassCode,注意：指明Student表和Grade表中StudentCode字段值相同的记录相联接。若在查询时无此条件，查询结果错误。,注意：Student表和Grade表都有StudentCode字段，所以要在Select后的该字段名前指明其中一个表名Student.StudentCode。,【例4.23】查询未住校学生的选课及成绩的情况。,SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveinDormFROMGrade,StudentWHEREGrade.StudentCode=Student.StudentCodeANDLiveInDorm=0,使用联接关键字JOINON说明两个表及相关联字段的联接条件。,FROM表1联接关键字表2ON表1.字段名1表2.字段名2,其中：表1、表2是被联接的表名；字段名是被联接的字段。必须有相同的数据类型并包含同类数据，但不需要有相同的名称。比较运算符：=、=、常用联接关键字：INNERJOIN（内联接）、LEFTOUTERJOIN（左外联接）、RIGHTOUTERJOIN（右外联接）,INNERJOIN（内联接）：查询结果仅包含两个表中每对联接匹配的行。内联接是系统默认的，可以将关键词INNER省略。,【例4.25】使用内联接查询未住校学生的选课及成绩情况。,SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveinDormFROMStudentINNERJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0,本句的查询结果与例4.23完全相同，但语句中表的联接关系表达更为清晰，WHERE子句只有一个条件“LiveInDorm=0”。,LEFTOUTERJOIN（左外联接）：结果除了包含两张表中符合联接条件的记录，还包含左表（写在关键字LEFTOUTERJOIN左边的表）中不符合联接条件、但符合WHERE条件的全部记录。可以将关键字OUTER省略。,【例4.26】使用左外联接查询未住校学生的选课及成绩情况。,SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveInDormFROMStudentLEFTJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0,有2条记录的CourseCode和Grade字段值为NULL。这2条记录是左表（Student）中未住校学生的信息，但在Grade表中没有其相关选课记录。,RIGHTOUTERJOIN（右外联接）：结果除了包含两张表中符合联接条件的记录，还包含右表（写在关键字RIGHTTOUTERJOIN右边的表）中不符合联接条件、但符合WHERE条件的全部记录。可以将关键字OUTER省略。,【例4.27】使用右外联接查询未住校学生的选课及成绩情况。,SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveInDormFROMStudentRIGHTJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0,结果与例4.25内联接完全相同。由于在表设计时，在表Student和Grade之间通过外键建立了参照完整性约束，表Grade中的所有StudentCode必须是Student中出现的值。,多表联接嵌套：如果查询所涉及的数据表在3个以上，形成联接嵌套。,1）使用WHERE子句设置查询条件,例如，查询学生所选课程成绩，显示学生姓名、课程名称，成绩。涉及Student、Grade、Course三张表。,SELECTStudentName,CourseName，GradeFROMStudent,Grade,CourseWHEREStudent.StudentCode=Grade.StudentCodeANDGrade.CourseCode=Course.CourseCode,2）使用联接关键字的语句,FROM表1JOIN表2ON表1.字段i表2.字段jJOIN表3ON表x.字段k表3.字段lJOIN表nON表y.字段m表n.字段nx=2,SELECTStudentName,CourseName,GradeFROMStudentJOINGradeJOINCourseONGrade.CourseCode=Course.CourseCodeONStudent.StudentCode=Grade.StudentCode,【例4.28】用嵌套联结实现例4.24查询优异生的情况。,SELECTStudentName,ClassName,GrantTime,GPAFROMExcellentStudentJOINStudentONExcellentStudent.StudentCode=Student.StudentCodeJOINClassONStudent.ClassCode=Class.ClassCode,结果与例4.24使用Where子句说明联接条件的结果完全相同。,【例4.29】查询班号是“61”的学生姓名、班号、所选课程名称和成绩。该查询需要用到3张表Student、Course和Grade。,SELECTStudentName,ClassCode,CourseName,GradeFROMStudentJOINGradeJOINCourseONGrade.CourseCode=Course.CourseCodeONStudent.StudentCode=Grade.StudentCodeWHEREClassCode=61,【例4.34】查询“林豆豆”同班同学的学号、姓名。,(7).子查询:当一个查询的结果是另一个查询的条件时，称该查询为子查询。也被称为嵌套查询。,SELECTStudentCode,StudentName,ClassCodeFROMStudentWHEREClassCode=(SELECTClassCodeFROMStudentWHEREStudentName=林豆豆),IN子查询：用来判断一个给定值是否在子查询的结果集中。,【例4.35】查询选修了课程代码为“101”的学生的学号、姓名和班号。,如果子查询结果返回多值则子查询要与下面介绍的IN、EXIST等结合使用。,SELECTStudentCode,StudentName,ClassCodeFROMStudentWHEREStudentCodeIN(SELECTStudentCodeFROMGradeWHERECourseCode=101),提示：在执行过程中，子查询得到选修课程“101”的所有学生学号返回给主查询，主查询再查询这些学生的学号、姓名和班号。,该查询也可以利用联接查询完成：,SELECTStudent.StudentCode,StudentName,ClassCodeFROMStudentINNERJOINGradeONStudent.StudentCode=Grade.StudentCodeWHERECourseCode=101,提示：多数情况下，包含子查询的语句可以用联接表示。但子查询与联接相比，有一个显著的优点，就是子查询可以计算一个变化的聚集函数值，并返回到主查询进行比较，而联接做不到。,【例4.36】查询年龄最大的学生的学号和姓名。,SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREBirthdayIN(SELECTMIN(Birthday)FROMStudent),提示：子查询获取Student表中最小的出生日期即年龄最大学生的出生日期，主查询再查询这一日期出生的学生的学号、姓名和出生日期。,EXISTS子查询：用于判断一个子查询的结果集是否为非空，如果非空则返回TRUE，否则返回FALSE。NOTEXISTS的返回值与EXISTS相反。,【例4.35】用EXISTS子查询实现：查询选修了课程代码为“101”的学生的学号、姓名和班级代号。,SELECTStudentCode,StudentName,ClassCodeFROMStudentWHEREEXISTS(SELECT*FROMGradeWHEREStudent.StudentCode=Grade.StudentCodeANDGrade.CourseCode=101),提示：本查询的执行过程是：首先查找主查询中Student表的第一行，将该行的学号提供给子查询，然后子查询执行，若结果集不为空，则把该行的StudentCode，StudentName，ClassCode作为结果集的第一行输出；然后再找Student表中的第2、3行，重复上述处理过程，直到Student中的数据行全部处理完毕。,【例4.37】查询所有未选修任何课程的学生。,SELECTStudentCode,StudentNameFROMStudentWHERENOTEXISTS(SELECT*FROMGradeWHEREStudent.StudentCode=Grade.StudentCode),数据插入语句INSERTINTO,向表中插入一条数据记录。,INSERTINTO表名(字段名1，字段名2，)VALUES（表达式1，表达式2，）,其中：（1）字段可以是某几个字段。表达式1、表达式2分别对应字段名1、字段名2，它们是所要添加的记录的值。（2）如果表中的字段没有在添加记录语句中出现，则对应的值为NULL。（3）如果在定义表结构时已经说明了某个字段为必填字段（不能是空值）则在语句中必须为该字段添加对应的数值。（4）当插入一条完整的记录时，可省略字段名，但字段值次序要与表中字段的次序一一对应。,【例4.38】向Grade表插入一条记录。,【例4.39】向Student表插入一条记录。,INSERTINTOGradeVALUES(2102,113,90,2012-12-20),INSERTINTOStudent(StudentCode,Sex,StudentName,ClassCode)VALUES(3105,女,张琳,31),本例插入完整记录，可省略字段名，但注意值要与表中字段次序一致。,本例插入记录的部分字段值，注意非空字段必须有值；字段次序可与表不同，但与值的对应顺序一致。,从其他表提取一组记录插入到目标表中。,INSERTINTO表名（字段1，字段2，）SELECT源字段名表FROM源表名WHERE添加条件,【例4.40】向StudentTest表插入记录，数据为Student中所有女同学。,INSERTINTOStudentTestSELECTStudentCode,StudentName,Sex,LiveInDormFromStudentWHERESex=女,提示：此处数据表必须已经存在，且其结构定义与SELECT语句返回的字段值类型一致。,3.修改记录语句UPDATE,UPDATE表名SET字段名1=表达式1，字段名2=表达式2WHERE条件表达式,实现对一条或多条符合条件记录中某个或某些字段值的修改。,提示：一个UPDATE语句可以更新多个字段值；如没有WHERE子句，将更新数据表中所有记录。,【例4.41】更新Student表中学号为“8101”的联系电话为“67792280”,【例4.42】更新Course表中信息，将所有开课学院为“计算机学院”的课程的实验学时增加10%，上课学时增加10。,UPDATEStudentSETTelephone=67792280WHEREStudentCode=8101,UPDATECourseSETLabHour=LabHour*(1+0.1),ClassHour=ClassHour+10WHEREAcademy=计算机学院,4.删除语句DELETE,DELETEFROM表名WHERE删除条件,【例4.43】删除Student表中学号为“3105”的记录。,【例4.44】删除Student表中平均成绩低于70的学生。,删除指定表中满足条件的一条或多条记录。如果没有WHERE子句，即没有指定删除条件，则该语句执行后将删除指定表中的所有记录。,DELETEFROMStudentWHEREStudentCode=3105,DELETEFROMStudentWHEREStudentCodeIN(SELECTStudentCodeFROMGradeGROUPBYStudentCodeHAVINGAVG(Grade)、!=3SETtext=你选了+LTRIM(STR(cn)+门课，很好！ELSEBEGINSELECT*FROMGradeWHEREStudentCode=1102-查询报告选课信息SETtext=你选了+LTRIM(STR(cn)+门课,选课太少,加油！ENDSELECTtextAS选课提示-查询报告选课提示信息,WHILE条件表达式SQL语句1BREAKSQL语句2CONTINUE,（3）WHILE语句实现一条SQL语句或SQL语句块重复执行。,说明：计算条件表达式的值，如果为TRUE，则执行WHILE后的语句块。重复上述过程,当条件表达式的值为False时,结束WHILE语句。2)BREAK为从本层WHILE循环中退出，当存在多层循环嵌套时，使用BREAK语句只能退出其所在的内层循环，然后重新开始外层的循环。3)CONTINUE为结束本次循环，开始下一次循环的判断。,【例4.48】调整课程号为“105”的课程成绩。当该课平均成绩小于80时，为每个同学的成绩加5分，循环操作直到最高分大于等于95分。,WHILE(SELECTAVG(Grade)FROMGradeWhereCourseCode=105)=80THENBWHENAVG(Grade)=70THENCWHENAVG(Grade)=60THENDWHENAVG(Grade)=90”，排序类型列选“降序”。,单击工具栏上的“”按钮可查看结果，保存视图为“V_StudentAvgGradeExcellent”。,2使用SQL语句创建视图在查询编辑窗口中执行SQL语句来完成视图创建,CREATEVIEW视图名称ASSELECT查询语句,【例4.53】创建一个性别为“男”的学生视图V_StudentSexMale，包括学生的学号、姓名、班号和性别。,CREATEVIEWV_StudentSexMaleASSELECTStudentCodeAs姓名,StudentNameAs姓名,ClassCodeAs班号,SexAs性别FROMStudentWHERESEX=男,SELECT课程名称,AVG(成绩)AS平均分FROMV_StudentGradeGROUPBY课程名称HAVING课程名称=大学英语,4.2.2使用视图创建后的视图与表的用法相同，可以通过视图查询和更新数据库。,1查询视图操作,【例4.54】在数据库School中，查询视图V_StudentGrade统计“大学英语”课程的平均分。,2更新视图操作通过视图来插入、删除和修改数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。,注意并不是所有的视图都可以执行各种更新操作。如果视图所依赖的基本表有多个时，对于更新操作有以下限制：不能向视图添加数据，因为这将影响多个基本表；一次只能修改一个基本表中的数据；不能通过视图删除数据。,视图更新方法：在ManagementStudio选中要更新的视图，打开即可更新；也可通过执行SQL语句更新视图。但要更新的视图必须符合更新规则，否则系统提示出错。,【例4.55】更新V_StudentSexMale。,1）向视图V_StudentSexMale中插入一个新的学生记录：（1104,赵谦,11,男）,INSERTINTOV_StudentSexMaleVALUES(1104,赵谦,11,男),说明：向视图V_StudentSexMale添加记录的命令实际上是对数据表Student添加记录。,2）修改该记录：将“赵谦”名字改为“赵廉”。,UPDATEV_StudentSexMaleSETStudentName=赵廉WHEREStudentName=赵谦,说明：对视图V_StudentSexMale修改，实际是修改Student表中相关的记录。,4.2.3修改和删除视图,1修改视图：修改视图的定义，即修改视图中指定字段的字段名、别名、表名、是否输出等属性。,在ManagementStudio中右击要修改的视图，选择“修改”命令，在视图设计窗口进行修改；直接修改视图定义语句。,2删除视图,在ManagementStudio中右击要删除的视图，选择“删除”命令，在“删除对象”对话框中，按“确定”按钮即可删除相应视图。,提示：删除视图对基本表没有任何影响，因为视图只是个虚拟表。,存储过程是T-SQL语句的集合，它作为数据库对象之一被存储在数据库中，用户的应用程序调用存储过程可实现对数据库的访问。,4.3存储过程,存储过程的作用和使用方式类似于一些编程语言中的过程。由应用程序调用执行，可以接受输入参数，并以输出参数的形式将多个值返回给调用它的程序。,使用存储过程有以下优点：可以在一个存储过程中执行多条SQL语句。可以通过输入参数的变化调用存储过程进行动态执行存储过程在创建时就在服务器端进行了编译，节省SQL语句的运行时间。提供了安全机制，它限制了用户访问SQL语句的权利，只为特定用户开放存储过程。,1使用SQL语句创建存储过程,4.3.1创建存储过程,CREATEPROCEDURE存储过程名形式参数数据类型=默认值OUTPUTASSQL语句1SQL语句n,说明：1）“形式参数”名称必须符合标识符规则；2）OUTPUT表示该参数是可以返回的，可将信息返回调用者；3）“默认值”表示输入参数的默认值，该值是常量或NULL，如果定义了默认值，不必提供实参，存储过程就可执行；4）如有多个参数，可依次列出，用逗号“，”隔开。,执行存储过程：,EXECUTE存储过程名形参=实参值|变量OUTPUT|DEFAULT,说明：1)“形参”是创建存储过程时定义的形参名；2)“实参值”是输入参数的值；3)“变量”表示用来保存参数或者返回参数的变量；OUTPUT表示指定参数为返回参数；4)DEFAULT表示使用该参数的默认值作为实参。5)如果有多个参数，依次列出，用逗号“，”隔开。,【例4.56】在School数据库中，创建无参数存储过程Proc_Student，查询所有学生信息。,在ManagementStudio中，打开一个查询编辑窗口，输入代码后执行。在“School/可编程性/存储过程”下可看到Proc_Student。,在查询编辑窗口执行以下调用存储过程Proc_Student的语句。,CREATEPROCEDUREProc_StudentASSELECT*FROMStudent,EXECProc_Student,2使用模板创建存储过程,在ManagementStudio中，提供了存储过程的创建模板，预先存放了主要语句代码结构，方便直接修改和补充模板中的代码来完成SQL语句。,可直接从模板资源管理器选中模板拖放到查询编辑窗口。,可通过“新建存储过程”命令使用模板。,【例4.57】在School中创建一个带有输入参数的存储过程proc_SearchStudentName，按输入姓名查询特定学生的信息。,在ManagementStudio的“对象资源管理器”中，展开“School/可编程性/存储过程”，右击“存储过程”，选择“新建存储过程”，打开查询编辑窗口。,在查询编辑窗口中，给出了创建存储过程的模板，修改相应代码后执行即可创建存储过程。该存储过程带有一个输入参数sname，接受实参传来的学生姓名。,调用Proc_SearchStudentName，查询学生“林豆豆”的信息。,CREATEPROCproc_SearchStudentsnamenvarchar(20)-sname表示姓名，是输入参数ASSELECT*FROMStudentWHEREStudentName=sname-按sname查询学生信息,EXECproc_SearchStudent林豆豆或者：EXECproc_SearchStudentsname=林豆豆,3存储过程的创建和应用实例,【例4.58】在School中创建一个带有输入和输出参数的存储过程proc_CountStudent，根据课程编号统计选修该课程的学生人数。,CREATEPROCEDUREproc_CountStudentccodechar(3),-ccode表示课程号，是输入参数numberintOUTPUT-number表示选修总人数，是输出参数AS-统计选修人数存放到numberSELECTnumber=count(StudentCode)FROMGradeWHEREGradeISNOTNULLANDCourseCode=ccode,创建存储过程proc_CountStudent。,调用proc_CountStudent，查询“101”课的选修人数。,DECLAREnumintEXECProc_CountStudent101,numOUTPUTPRINT选修101课程的学生人数：+str(num),【例4.59】在School中创建一个向Class表中插入班级记录的存储过程proc_ClassAdd。,调用存储过程proc_ClassAdd，完成记录添加。,创建存储过程proc_ClassAdd。,CREATEPROCEDUREproc_ClassAddccodechar(2),-ccode表示班号，是输入参数cnamenvarchar(10),-cname表示班名，是输入参数mjnamenvarchar(10)-mjname表示mjname，是输入参数AS-插入数据记录到ClassINSERTINTOClass(ClassCode,ClassName,MajorName)VALUES(ccode,cname,mjname),EXECproc_ClassAdd44,财务02,财务管理,【例4.60】在School中创建一个按学号和课程号修改成绩的存储过程proc_UpdateGrade。,创建存储过程proc_UpdateGrade。,调用存储过程proc_UpdateGrade，将2202号学生的106课程成绩改为80分。,CREATEPROCproc_UpdateGradestcodechar(4),cscodechar(3),scgradeNumeric(4,1)ASUPDATEGrade-以scgrade值修改成绩SETGrade=scgradeWHEREStudentCode=stcodeANDCourseCode=cscode,EXECproc_UpdateGrade2202,106,80,【例4.61】在School中创建一个存储过程proc_StudentPrize，查询某个学生所获得的奖学金。奖学金发放的规则为：平均成绩大于90分获奖学金1000元；平均成绩在80到90分之间获奖学金500元；平均成绩小于80分没有奖学金。,创建存储过程proc_StudentPrize。,本过程一个输入参数，无输出参数。它通过RETURN语句返回奖金额。CREA