第4章T-SQL与可编程对象

第4章T-SQL与可编程对象SQL语言是结构化查询语言（StructureQueryLanguage）的简称，它是一个通用的、功能强大的关系数据库操作语言，最初由IBM公司在20世纪70年代中期开发成功。并被国际标准化组织采纳为关系数据库语言的国际标准。Transact-SQL（

简称T-SQL）是SQLServer中支持的扩展SQL语言，它提供了数据定义、数据操纵、数据控制等语句，支持对数据库的操纵和管理，是开发数据库应用程序的基本语言。SQL

Server支持采用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增加的语言元素

变量、运算符、函数、流程控制语句和注解等。

3．T-SQL语言书写规则①在T-SQL语句中，不区分字母大小写，即大小写字母的意义完全相同。②一条语句可以写在一行，也可以写在若干行上。在本讲义中：为了阅读方便，用大写字母书写SQL语句的关键词，用小写字母书写标识符、表达式及各种参数；为了体现语法结构，一条语句写在多行上。例如：SELECT*FROM

Student

4．注释为了增强程序代码的可读性，可在适当的地方加上注释。T-SQL有两种注释方法：①单行注释（--）。语法格式如下：--注释文本内容②多行注释（/*…*/）。语法格式如下：/*注释文本内容*/

例如：USESchool/*打开School数据库。当第一次访问某数据库时，需要使用USE语句打开数据库，否则其他访问该数据库的SQL语句不能执行*/SELECT*FROMStudent--查询显示学生表中所有学生的信息操作对象操作方式创建删除修改使用数据库CREATEDATABASEDROPDATABASEALTERDATABASEUSE基本表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEWALTERVIEW索引CREATEINDEXDROPINDEX存储过程CREATEPROCEDUREDROPPROCEDUREALTERPROCEDURE触发器CREATETRIGGERDROPTRIGGERALTERTRIGGER数据定义语言主要是定义数据库中的对象，包括数据库、数据表、视图、索引、存储过程、触发器等的建立或修改。常用语句如下：4.1.2数据定义语言DDL

1．DDL概述如果想查看或保存已存在的数据库对象的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

:CREATEDATABASE

SchoolTest

--数据库名提示：[]表示可选项以系统数据库model作为模板在SQLServer安装文件夹的Data文件夹下（一般为C:\ProgramFile\MicrosoftSQLServer\MSSQL\Data）建立数据库文件SchoolTest.mdf（大小为3MB）和日志文件SchoolTest_log.LDF（大小为1MB）。答案2

:CREATEDATABASE

SchoolTest

--数据库名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,LiveInDormBIT

DEFAULT1，/*默认值为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的索引。CREATEUNIQUE

INDEX

NameIndex

onStudentTest(StudentName,

BirthdayDESC)DROPINDEXStudentTest.NameIndex4.1.3数据操纵语言DMLSELECT语句：从一个表或多个表查询数据INSERT语句：向一个表中添加一条记录UPDATE语句：修改表中某一个或几个字段的值DELETE语句：从一个表中删除记录DML语句实现对数据的查询和更新，主要语句及功能如下：1．查询语句SELECT又称为选择查询语句，用于从数据库的一个或多个表中检索满足一定条件的记录集，并按一定顺序显示指定的字段内容。SELECT字段表FROM表名[WHERE查询条件][GROUPBY分组字段[HAVING分组条件]][ORDERBY字段名[ASC/DESC]][]表示可选项。第2行至最后一行称作SQL子句，不同的子句完成不同的功能。在书写SELECT语句时，字母大小写意义完全相同。语句可以写在若干行上（如上述语法格式中的描述），也可以不换行。字段表位于关键字SELECT后面，这些字段将作为查询的结果被显示。①可选任意多个字段，字段与字段之间用逗号分隔。【例4.6】查询Student表中的所有学生的姓名和性别。(1)字段表SELECTStudentName,Sex

FROMStudent②可以使用通配符“*”表示表中的所有字段。【例4.7】查询Student表中所有学生的全部字段信息。SELECT*

FROMStudent③若字段名或表名中含有空格，则该字段名或表名必须用方括号括起来。④在字段表中，可以使用如下方式将原字段名以新的字段名显示：字段名AS新字段名【例4.8】查询Class表中班号和班名班号别名为ClassNumber。提示：“ClassNumber”含空格，要写为[ClassNumber]SELECTClassCodeAS[ClassNumber],ClassNameFROMClass⑤在字段名前可以加上一些关键字，以便进一步优化查询结果。常用的关键字有：DISTINCT：若多条记录的字段值具有相同数据，只显示一条记录。【例4.11】显示Course表中开课的学院名称Academy（与所开课程无关）。SELECTDISTINCTAcademyFROMCourse由于每个学院都开设了多门课程，因此多条记录在字段Academy具有部分相同的值。增加Distinct限制后，查询结果中相同的值只显示一次。TOPn|mPERCENT：查询前n条记录或前m%的记录。【例4.9】查询Course表中的前3条记录。【例4.10】查询Course表中前20%的记录，显示课程号和课程名称。SELECTTOP3*FROMCourseSELECTTOP20PERCENTCourseCode,CourseNameFROMCourse⑥新增字段查询。查询的字段可以是表中字段名，也可以是常量和表达式，表达式中可以使用内部聚合函数进行统计计算。常用函数表函数名函数功能AVG计算某一字段的平均值（字段值必须是数值型）COUNT统计某一字段的个数MAX查找某一字段的最大值MIN查找某一字段的最小值SUM计算某一字段的总和（字段的值必须是数值型）【例4.12】查询Grade表，显示学号、所选课程号及加2分调整后的成绩（Grade），并显示一列说明“情况属实”。【例4.13】查询Grade表，统计所有学生的分数总和、平均分、最高分、最低分及总选课人次。SELECTStudentCode,CourseCode,Grade+2AS成绩,'情况属实'AS说明FROMGradeSELECTSum(Grade)AS总分,Avg(Grade)AS平均分,Max(Grade)AS最高分,Min(Grade)AS最低分,Count(StudentCode)AS总人次FROMGrade【例4.14】将Student表中所有记录的学生姓名、出生日期复制到一张新表NewTable。⑦使用查询创建新表。在字段名后加上“INTO表名”可将查询结果复制到一张新表中。SELECTStudentName,BirthDayINTONewTableFROMStudent提示：语句运行后，数据库School中产生一张表名为NewTable的新表。刷新数据库，即可查看到。（2）FROM子句FROM子句是SELECT语句必不可少的一个子句，指定要查询的数据来自哪个或哪些表或视图。FROM表名1[，表名2，……，表名n]【例4.15】查询Student表和Grade表，显示学生的选课和成绩情况。SELECTStudent.StudentCode,StudentName,CourseCode,GradeFROMStudent,GradeWHERE

Grade.StudentCode=Student.StudentCodeSELECTStudentCode,StudentName,CourseCode,GradeFROMStudent,Grade

()注意：表的排列顺序不影响执行结果；如果FROM子句中包含多个表名，且不同的表中具有相同的字段，那么SELECT子句的字段名必须表示成“表名.字段名”。（3）WHERE子句WHERE子句说明查询条件，它是一个可选的子句。它必须紧跟在FROM子句的后面。

WHERE查询条件查询条件谓词比较>、>=、<、<=、=、<>(不等于)、!>(不大于)、!<(不小于)、!=(不等于)确定范围BETWEEN…AND、NOTBETWEEN…AND确定集合IN、NOTIN、EXISTS字符匹配LIKE、NOTLIKE空值ISNULL、ISNOTNULL否定NOT逻辑运算AND、ORT-SQL中常用的关系和逻辑运算其中，查询条件是一个关系或逻辑表达式。①

比较和逻辑运算【例4.16】查询Student表中所有女学生的姓名。

【例4.17】查询未住校的女学生的情况。

SELECTStudentCode,StudentName,

LiveInDormFROMStudentWHERELiveInDorm=0ANDSex=‘女’SELECTStudentNameFROMStudentWHERESex=‘女’②

BETWEEN…AND…运算BETWEEN值1AND值2NOTBETWEEN值1AND值2【例4.18】在Grade表中查询选修课程号为“101”、成绩在70分到90分之间的所有学生，显示其学号及成绩。查询某字段的值在指定范围内的数据记录（结果包括值1和值2）。查询不在指定范围中的数据记录。SELECTStudentCode,CourseCode,GradeFROMGradeWHERECourseCode='101‘ANDGradeBETWEEN70AND90此例的WHERE子句也可以改为：WHERECourseCode=‘101'ANDGrade>=70ANDGrade<=90【例4.19】查询Student表中不在1990年到1994年中出生的学生的学号和姓名。SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREBirthdayNOTBETWEEN'1990-01-01'AND'1994-12-30'在WHERE子句中可使用Year()函数：SELECTStudentCode,StudentName,BirthdayFROMStudentWHEREYear(Birthday)NOTBETWEEN1990AND1994注意：日期型常量的写法！③确定在集合中的IN运算：查询某字段值在或不在某集合的数据记录【例4.20】查询Student表中班号为“11”、“21”、“31”的学生。SELECTStudentCode,StudentName,Sex,ClassCodeFROMStudentWHEREClasscodeIN('11','21','31')

此例的WHERE子句也可以改为：WHEREClassCode='11'ORClassCode='21'ORClassCode='31'④字符串模式匹配的LIKE运算：确定某字符字段的子串值是否符合指定的模式，可查询满足字符串匹配的数据记录。例如：姓“王”的名字：‘王%’；第2个字是“小”的姓名：‘_小%’姓“张”的单名：‘张_’【例4.21】查询Course表中课程名称中包含“化学”两个字的课程号及课程名称。【例4.22】查询Student表中不姓“王”的学生。SELECTCourseCode,CourseNameFROMCourseWHERECourseNameLIKE'%化学%'SELECTStudentCode,StudentNameFROMStudentWHEREStudentNameLIKE'[^王]%'【例4.30】统计每门课程的选课人数和平均分。(4)GROUPBY子句：用于对数据记录进行分类汇总，即按指定字段把具有相同值的记录通过汇总计算合并成一条记录。GROUPBY分组[HAVING分组条件]HAVING子句：与WHERE子句作用类似，在使用GROUPBY完成分组后，显示满足HAVING子句中分组条件的所有记录。

SELECTCourseCode,Count(StudentCode)AS选课人数,AVG(Grade)AS平均分FROMGradeGROUPBYCourseCode如只查询平均分大于等于80的记录，则在语句后增加：[HAVINGavg(Grade)>=80]如果GROUPBY后的分组字段有多个，则表示多次分组。

【例4.31】统计各学院男、女教师的人数。SELECTAcademy,Sex,Count(TeacherCode)AS教师人数FROMTeacherGROUPBYSex,Academy注意：使用GROUPBY子句时，分组字段必须出现在SELECT后的字段列表中（可以是字段，也可在聚合函数中），否则不允许分组。【例4.32】按成绩升序显示Grade表中的所有数据。(5)ORDERBY子句：按指定字段为查询结果排序。通常是SQL语句最后一项。ORDERBY字段名[ASC/DESC]其中：ASC为升序排序，DESC为降序排序。缺省排列次序为升序。ASC:Ascending，DESC:DescendingSELECT*FROMGradeORDERBYGrade可以指定多个排序的字段。规则：首先用指定的第一个字段对记录排序，然后对此字段中具有相同值的记录用第二个字段进行排序，依此类推。若在SELECT语句中无此子句，则按原数据表的次序显示数据。【例4.33】按姓名升序（若姓名相同，则按出生日期降序）显示班级代码、学生姓名及出生日期。SELECTClassName,StudentCode,StudentNameFROMClass,StudentWHEREClass.ClassCode=Student.ClassCodeORDERBYClassName,StudentNameDESCClassName默认为ASC(6).联接查询：涉及多表的查询称为联接查询，可以用两种方法实现。①在FROM子句中指出需查询的表，在WHERE子句中说明两个表相关联字段的联接条件

。注意：Student表和Grade表都有StudentCode字段，所以要在Select后的该字段名前指明其中一个表名Student.StudentCode。【例4.23】查询未住校学生的选课及成绩的情况。SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveinDormFROMGrade,StudentWHEREGrade.StudentCode=Student.StudentCodeANDLiveInDorm=0【例4.24】查询优异生的情况。要求显示学生姓名、所在班级、认定时间、学分积点。

SELECTStudentName,ClassName,GrantTime,GPAFROMExcellentStudent,Student,ClassWHEREExcellentStudent.StudentCode=Student.StudentCode

ANDStudent.ClassCode=Class.ClassCode注意：指明Student表和Grade表中StudentCode字段值相同的记录相联接。若在查询时无此条件，查询结果错误。②使用联接关键字JOIN…ON说明两个表及相关联字段的联接条件。

FROM表1联接关键字表2ON表1.字段名1<比较运算符>

表2.字段名2其中：表1、表2是被联接的表名；字段名是被联接的字段。必须有相同的数据类型并包含同类数据，但不需要有相同的名称。比较运算符：=、<、>、<=、>=、<>常用联接关键字：INNERJOIN（内联接）、LEFTOUTERJOIN（左外联接）、RIGHTOUTERJOIN（右外联接）

INNERJOIN（内联接）：查询结果仅包含两个表中每对联接匹配的行。内联接是系统默认的，可以将关键词INNER省略。【例4.25】使用内联接查询未住校学生的选课及成绩情况。SELECTStudent.StudentCode,StudentName,CourseCode,Grade,LiveinDormFROMStudentINNER

JOINGradeONStudent.StudentCode=Grade.StudentCodeWHERELiveInDorm=0本句的查询结果与例4.23完全相同，但语句中表的联接关系表达更为清晰，WHERE子句只有一个条件“LiveInDorm=0”。Inner可省略

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.CourseCode2）使用联接关键字的语句FROM表1JOIN表2ON表1.字段i<比较运算符>表2.字段jJOIN表3ON表x.字段k<比较运算符>表3.字段l…[JOIN表nON表y.字段m<比较运算符>

表n.字段n]x<=2,y<=n-1SELECTStudentName,CourseName,GradeFROMStudent

JOIN

GradeON

Student.StudentCode=Grade.StudentCode

JOINCourseONGrade.CourseCode=Course.CourseCode或者写为如下格式：FROM表1JOIN表2JOIN表3…[JOIN表nON表n.字段i<比较运算符>表n-1字段jON表x.字段k<比较运算符>表n-2.字段l

…ON表y.字段m<比较运算符>表1.字段n]x>=n-1,y>=2SELECTStudentName,CourseName,GradeFROMStudent

JOIN

GradeJOINCourseONGrade.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,Grade

FROMStudentJOINGradeJOINCourseONGrade.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.修改记录语句UPDATEUPDATE表名SET字段名1=表达式1[，字段名2=表达式2……]WHERE

条件表达式实现对一条或多条符合条件记录中某个或某些字段值的修改。提示：一个UPDATE语句可以更新多个字段值；如没有WHERE子句，将更新数据表中所有记录。【例4.41】更新Student表中学号为“8101”的联系电话为“67792280”【例4.42】更新Course表中信息，将所有开课学院为“计算机学院”的课程的实验学时增加10%，上课学时增加10。UPDATEStudentSETTelephone='67792280'WHEREStudentCode='8101'UPDATECourseSETLabHour=LabHour*(1+0.1),ClassHour=ClassHour+10WHEREAcademy='计算机学院'4.删除语句DELETEDELETEFROM表名[WHERE删除条件]【例4.43】删除Student表中学号为“3105”的记录。【例4.44】删除Student表中平均成绩低于70的学生。删除指定表中满足条件的一条或多条记录。如果没有WHERE子句，即没有指定删除条件，则该语句执行后将删除指定表中的所有记录。DELETEFROMStudentWHEREStudentCode='3105'DELETEFROMStudentWHEREStudentCodeIN(SELECTStudentCodeFROMGrade

GROUPBYStudentCode

HAVINGAVG(Grade)<70)4.1.4T-SQL的运算功能和控制流程T-SQL虽然和高级语言不同，但它也有运算、控制等功能，以支持复杂的数据检索和集合操纵。

1.标识符、常量和变量（1）标识符：由用户定义的名称，用来标识各种对象如服务器、数据库、数据库对象、变量等。第一个字符必须是字母

、下划线(_)、@或#。注意：在SQLServer中，如果标识符中包含空格，要用双引号（“”）或方括号([])扩起来。如：mytable不合法，必须将其表示为[mytable]或＂mytable＂。（2）常量：在程序运行过程中值不变的量。

注意：在T-SQL语句中不同类型常量的写法数值型：直接写。如：23,45.6字符串：用西文单引号扩起来。如：‘男’。如字符串包含单引号，则可以使用两个单引号表示嵌入的单引号。

‘’’211’’

工程’---‘211’工程日期时间型：用西文单引号扩起来。如：'2008-08-08'，'2008-08-0820:08:08'Bit型：直接写。如：0,1@@ERROR:返回最后执行的Transact-SQL语句的错误代码。（3）变量：在程序运行过程中其值可以被改变的量。1)变量的分类局部变量：由用户定义和使用，其名称前有一个@符号。全局变量：全局变量由系统定义和维护，其名称前有两个@符号。例如：@@ERROR:返回最后执行的T-SQL语句的错误代码。2）局部变量的定义和赋值DECLARE@局部变量名数据类型说明：如在一条语句中声明多个变量，各变量之间用“，”分隔。局部变量被定义后其初始值为NULL。例如：DECLARE@xfloat,@varchar(8)该语句定义了变量@x是浮点实型，变量@var是长度为8的字符数据类型。【例4.45】使用课程号变量和成绩变量查询Grade表中课程代码为“105”且成绩低于80分的记录。

局部变量赋值的基本语法格式如下：SET@局部变量名=表达式SELECT@局部变量名=表达式DECLARE@codechar(3),@scoreNumeric(3,1)--变量@code表示课程号，@score表示成绩SET@code='105'

--给@code赋值图4.23变量查询示例SELECT@score=80

--给@score赋值SELECT*--使用变量@code和@score查询FROMGradeWHERECourseCode=@codeANDGrade<@score提示：变量类型要与Grade表中字段的类型一致，使用前要先赋值。【例4.46】将学号为“1102”的学生姓名存放到变量@sname中。DECLARE@snamevarchar(16)SELECT@sname=(SELECTStudentName

FROMT_StudentWHEREStudentCode=‘1102')SELECT@snameas'姓名'

提示：SELECT语句可用于将单个值赋于一个变量。如果子查询返回值有多个，则将最后一个返回值赋给变量；如果子查询没有返回值，变量就保留当前值。SET

@sname2．运算符和表达式(1)运算符1)算术运算符：+、-、*、/、模运算（%）2)比较运算符和逻辑运算符>=、<、<=、=、<>、!>、!<、!=BETWEENAND、NOTBETWEENANDIN、NOTINLIKE、NOTLIKEISNULL、ISNOTNULLNOT、AND、OR3）字符串连接运算符：+例如：‘ABCD’+‘1234’结果为：‘ABCD1234’例如：查询Grade表中还未输入成绩的学生代号和课程代号。SELECT*FROMGradeWHEREGradeIS

NULL(2)表达式

表达式是由运算对象、运算符及圆括号组成。在SQL查询分析器中可使用Select语句查看表达式的结果。例如：SELECT'ABCD'+'1234'

当一个表达式中有多个运算符时，运算符的优先级决定运算的先后次序。（1）数学函数：常用的数学函数及功能如表所示。3．常用函数RAND(n):返回0到1之间的随机float值。n是种子值，如果没有提供种子值，系统将生成它自己的不定种子值。如果用种子值调用RAND，则必须使用不定的种子值来生成随机数。如果用同一种子值多次调用RAND，它将返回相同的生成值。ROUND(x,n):当n为正数时，x四舍五入为n所指定的小数位数。当n为负数时，x则按n所指定的在小数点的左边四舍五入。例如：ROUND(878.586,

2)=878.590ROUND(878.586895,3)=878.587000

ROUND(878.586,0)=879.000

ROUND(878.586,-1)=880.000

ROUND(878.586,-3)=1000.000ROUND(878.586,-4)=.000函数名函数功能函数名函数功能ABS(x)计算x的绝对值SQRT(x)计算x的平方根ATN(x)计算x的反正切值SIGN(x)返回x的特征符号COS(x)计算x的余弦值TAN(x)计算x的正切值EXP(x)计算ex([n])产生[0,1)之间的随机float值,n为正数LOG(x)计算自然对数lnxROUND（x,n）将x四舍五入为指定精度，n为小数位数SIN(x)计算x的正弦值表4.7常用数学函数(2)日期时间函数：常用日期函数如表所示。SELECTYEAR(‘03/12/1998’)AS年份提示：在数据库设计时，如将日期型字段的默认值设置为GetDate()，则在添加新记录时，如果不赋值，自动用当前时间填入。SELECTDATEDIFF(year,'2000-01-01',getdate())SELECTGETDATE()AS今天的日期函数名函数功能DAY(x)返回指定日期中所表示的日，x是日期，例如：Day(‘2014-5-1’)=1MONTH(x)返回指定日期中所表示的月，例如：Month(‘2014-5-1’)=5YEAR(x)返回指定日期中所表示的年份，例如：YEAR(‘2014-5-1’)=2014GETDATE()返回当前系统日期和时间(3)字符串函数：常用字符串函数如表所示。SELECTLEN(‘Sun’),LEN(‘中国北京’)

--结果分别为数值3和4SELECTSUBSTRING(‘VB语言程序设计’,3,2)

--结果为字符串“语言”SELECTREPLACE('数据库系统','系统','应用')--结果为字符串“数据库应用”函数名函数功能函数名函数功能ASCII(s)返回字符串s最左端字符的ASCII码STR(n)将数字数据n转换为字符串CHAR(n)将ASCII转换为字符SPACE(n)返回n个空格LEFT(s,n)返回字符串s左边的n个字符SUBSTRING(s,m,n)返回字符串s起始m长度为n的子串RIGHT(s,n)返回字符串s右边的n个字符LOWER(s)将字符串中的字母转换为小写字母LEN(s)返回字符串s的长度（字符的个数）UPPER(s)将字符串中的字母转换为大写字母LTRIM(s)删除字符串s开始处的空格REPLACE(s1,s2,s3)用s3替换s1中包含的s2RTRIM(s)删除字符串s结尾处的空格SELECTstr(123.4,5)SELECTstr(123.4,5,1)SELECTstr(123.4,2)str(float_expression[,length[,decimal]])(4)聚合函数函数名函数功能AVG([ALL|DISTINCT]expression)计算某一字段的平均值（此字段的值必须是数值型）COUNT([ALL|DISTINCT]expression)统计某一字段的个数MAX([ALL|DISTINCT]expression)查找某一字段的最大值MIN([ALL|DISTINCT]expression)查找某一字段的最小值SUM([ALL|DISTINCT]expression)计算某一字段的总和（此字段的值必须是数值型）聚合函数对一组值进行计算并返回一个结果，常用于对记录的分类汇总，聚合函数经常与SELECT语句的GROUPBY子句一同使用。常用聚合函数如下：T-SQL语言也提供了一些流程控制语句，使得对数据库中数据的检索、更新、插入等操作更加方便。

BEGIN…END语句多条T-SQL语句使用BEGIN…END组合起来形成一个语句块。BEGIN…END可以嵌套使用。4．流程控制语句BEGINSQL语句1SQL语句2…ENDIF条件表达式

SQL语句1[ELSE

SQL语句1](2)IF…ELSE语句通过判定给定的条件来决定执行哪条语句或语句块。说明：1)首先计算条件表达式的值，如果为TRUE，则执行IF后面的语句块，否则执行ELSE后面的语句块。2)如果是单分支流程，可不含ELSE。3)如果条件表达式中包含SELECT语句，则必须用圆括号将SELECT语句括起来。

【例4.47】统计学号为“1102”的学生的选课总数，如果不少于三门课就报告选课门数，否则显示其选修的课程信息，并提示选课太少。

USESchoolDECLARE@cnsmallint,@textvarchar(100)SET@cn=(SELECTcount(StudentCode)FROMGradeWHEREStudentCode=‘1102’)

--查选课门数IF@cn>=3

SET@text=‘你选了’+LTRIM(STR(@cn))+’门课，很好！’ELSE

BEGIN

SELECT*FROMGradeWHEREStudentCode=‘1102’

--查询报告选课信息

SET@text=‘你选了’+LTRIM(STR(@cn))+’门课,选课太少,加油！’

ENDSELECT@textAS选课提示--查询报告选课提示信息WHILE条件表达式

SQL语句1[BREAK]

SQL语句2[CONTINUE]（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')<80IF(SELECTMAX(Grade)FROMGrade

WHERECourseCode='105')<95BEGIN

UPDATEGradeSETGrade=Grade+5WHERECourseCode='105'

CONTINUEENDELSEBREAKRETURN表达式

（4）RETURN语句使程序从一个查询或存储过程中无条件返回，并返回表达式的值，其后的语句不再执行。（5）CASE语句

CASE语句是多分支的选择语句。简单CASE函数形式：将某个表达式与一组简单表达式进行比较以确定结果。CASE搜索函数形式：计算一组条件表达式以确定结果。

1）简单CASE函数CASE输入表达式WHEN情况表达式THEN结果表达式...[ELSE结果表达式]END1)当输入表达式的值与某一个WHEN子句的情况表达式的值相等时，就返回该WHEN子句中结果表达式的值。2)如果所有都不相等，则返回ELSE子句后的结果表达式的值。若没有ELSE子句，则返回NULL值。【例4.49】查询1994年出生的男同学的住校情况。SELECTStudentNameAS'姓名',

CASELiveInDorm

WHEN0THEN'未住校'

WHEN1THEN'住校'

ENDAS'是否住校'FROMStudentWHERESex='男'ANDYEAR(Birthday)=19942）CASE搜索函数CASE

WHEN条件表达式THEN结果表达式

...

[ELSE结果表达式]END【例4.50】统计每个学生平均成绩并划分等级。

SELECTStudentCodeAS'学号',STR(AVG(Grade),5,2)AS'平均成绩',CASE

WHENAVG(Grade)>=90THEN'A'

WHENAVG(Grade)>=80THEN'B'

WHENAVG(Grade)>=70THEN'C'

WHENAVG(Grade)>=60THEN'D'

WHENAVG(Grade)<60THEN'E'ENDAS'等级'FROMGradeGROUPBYStudentCode

说明：按顺序计算WHEN子句的条件表达式，当布尔表达式的值为TRUE，则返回THEN后面的结果表达式的值，然后跳出CASE语句。4.2视图视图是数据库的外模式，通常用来为用户集中数据、简化用户的数据查询。视图是一个虚拟表，其内容来自对数据表的查询结果。和数据表一样，视图也是二维表结构。视图也可作为一种安全机制，允许用户通过视图访问数据，而不授予用户直接访问某些基础表的权限。：4.2.1创建视图在SQLServer中创建视图主要有两种方式：(1)在ManagementStudio中使用向导创建视图；(2)通过执行视图定义SQL语句CREATEVIEW创建视图。创建视图时必须遵循以下原则：只能在当前数据库中创建视图。视图在数据库中作为一个对象存储，视图名称不得与数据库中的表重名。1.在ManagementStudio中使用向导创建视图【例4.51】在数据库School中建立视图V_StudentGrade，查询学生姓名、课程名和成绩。①

展开“School/视图”，可看到数据库中已有一些系统视图。用鼠标右击“视图”，选择“新建视图”菜单可打开“添加表”对话框。②选择与视图有关的表、视图或函数（同时按Ctrl或Shift键可选择多项），单击“添加”按钮；或直接双击要添加的表，即可将其添加到视图设计窗口中。本例添加数据表Student、Course和Grade。单击“关闭”按钮，进入视图设计窗口。③在上格单击每个表字段前的复选框，可将该字段添加到视图中，也可在第二个窗格中选择视图字段，并可指定列的别名、排序方式和规则等,在选择过程中，第三个窗格中的SELECT语句也会随之自动改变。上格是表及其关系窗格中格是为视图选择表中列的网格下格是结果窗格SQL语句窗格提示:可省略第②~④步骤,直接在第三个窗格中输入实现查询的SELECT语句。④单击工具栏上的“”按钮执行，视图的查询结果显示在结果窗格中。⑤单击工具栏上的“”按钮，在弹出的“另存为”对话框中为视图命名，本例输入“V_StudentGrade”，单击【确定】按钮保存视图，从而完成视图创建。①在创建视图的“添加表”对话框中选择“视图”选项卡，添加视图V_StudentGrade到视图设计器中。

【例4.52】在数据库School中，建立视图V_StudentAvgGradeExcellent，选拔优培生（平均成绩大于等于90的学生），并要求按平均成绩从高到低排列。该例可参照上例方法在基本表Student和Grade上创建。由于视图的内容也可以来自另一个视图，这里基于视图V_StudentGrade创建。②在“视图设计”窗口的上格，首先为视图选择“姓名”和“成绩”字段。右击第二个窗格的字段“姓名”，选择快捷菜单的“添加分组依据”；在“成绩”行的“分组依据”列选“Avg”，筛选器列输入“>=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创建存储过程CREATEPROC[EDURE]存储过程名{@形式参数数据类型}[=默认值][OUTPUT]ASSQL语句1…SQL语句n说明：

1）“形式参数”名称必须符合标识符规则；2）OUTPUT表示该参数是可以返回的，可将信息返回调用者；3）“默认值”表示输入参数的默认值，该值是常量或NULL，如果定义了默认值，不必提供实参，存储过程就可执行；4）如有多个参数，可依次列出，用逗号“，”隔开。执行存储过程：EXEC[UTE]存储过程名[[@形参=]实参值|@变量[OUTPUT]|[DEFAULT]]说明：

1)“@形参”是创建存储过程时定义的形参名；2)“实参值”是输入参数的值；3)“@变量”表示用来保存参数或者返回参数的变量；OUTPUT表示指定参数为返回参数；4)DEFAULT表示使用该参数的默认值作为实参