数据库基础与项目实训教程-基于SQLServer第6章.ppt

第6章Transcat-SQL语言,知识导航,学习目标,理解：T-SQL语句基础Select语句的语法格式。掌握：Select语句的使用Insert、Update、Delete等语句的使用,T-SQL语言,6.1Transcat-SQL语言基础6.2数据操纵语言,6.1T-SQL语言基础,任务描述：使用T-SQL语言编写应用程序可以完成所有的数据库管理工作。本任务将讲述T-SQL的语言基础，为后续学习奠定基础。任务目标：理解SQLServer2000的系统函数；掌握变量、常量；掌握T-SQL语言的语法，并能熟练应用。,6.1T-SQL语言基础,6.1.1数据类型在设计和创建表的时候，要确定每个字段的数据类型和长度。所谓数据类型就是以数据的表现方式和存储方式来划分的数据的种类,6.1T-SQL语言基础,6.1T-SQL语言基础,一般从四个方面讨论数据类型。1）类别：如字符型、整数型、数值型等。2）存储的长度：指的是存储数据所使用的字节数。3）数值的精度：指数值数据中所存储的十进制数据的总位数。4）数值的小数位数：指数值数据中小数点右边可以有的数字位的最大值,6.1T-SQL语言基础,1整数数据类型整数数据类型是最常用的数据类型之一，它主要用来存储数值。整型数据类型包括以下几种。1）Int（Integer）：存储从-231到231-1之间的所有正负整数2）Smallint：存储从-215到215-1之间的所有正负整数3）Tinyint：存储从0到255之间的所有正整数4）Bigint：存储从-263到263-1之间的所有正负整数,6.1T-SQL语言基础,2浮点数据类型浮点数据类型用于存储十进制小数浮点数值的数据。浮点数据类型包括以下几种：1）Real：可精确到第7位小数，其范围从-3.40E-38到3.40E+38。2）Float：可精确到第15位小数，其范围从-1.79E-308到1.79E+308。3）Decimal：可以提供小数所需要的实际存储空间，但也有一定的限制。可以用它来存储从-1038-1到1038-1之间的数值。4）Numeric：与Decimal数据类型功能上完全相同，区别在于Decimal不能用于Identify关键字的列。,6.1T-SQL语言基础,3二进制数据类型二进制数据类型表示的是位数据流，包括Binary和Varbinary两种类型。1）Binary：用于存储二进制数据，其定义形式为Binary（n），n表示数据的长度，取值为1到8000。2）Varbinary：定义形式为Varbinary（n），它与Binary类型相似。,6.1T-SQL语言基础,4逻辑数据类型Bit占用1个字节的存储空间，其值为0或1，如果输入0或1以外的值将被视为1。Bit类型不能定义Null值。,6.1T-SQL语言基础,5字符数据类型字符数据类型是使用最多的数据类型。它可以用来存储各种字母、数字符号、特殊符号。1）Char：定义形式为Char(n)。以Char类型存储的每个字符或符号占1个字节的存储空间。2）Nchar：定义形式为Nchar(n)。它与Char类型相似，不同的是，Nchar数据类型n的取值为1到4000。3）Varchar：定义形式为Varchar(n)。它与Char类型相似，n的取值为1到8000。4）Nvarchar：定义形式为Nvarchar(n)。它与Varchar类型相似。,6.1T-SQL语言基础,6文本和图形数据类型这类数据类型用于存储大量字符或二进制数据。1）Text：用于存储大量文本数据，容量理论上为1到231-1个字节。2）Ntext：与Text类型相似。3）Image：用于存储大量的二进制数据BinaryData，其理论容量为231-1个字节。,6.1T-SQL语言基础,7日期和时间数据类型1）Datetime：用于存储日期和时间的结合体，可以存储从公元1753年1月1日0时起到公元9999年12月31日23时59分59秒之间的所有日期和时间。2）Smalldatetime：与Datetime数据类型相似，但其日期时间范围较小，为从1900年1月1日到2079年6月6日，精度较低，只能精确到分钟，长度为4个字节。,6.1T-SQL语言基础,8货币数据类型货币数据类型用于存储货币值。在使用货币数据类型时应在数据前加上货币符号，系统才能辨识其为哪国的货币，如果不加货币符号则默认为“￥”。9特定数据类型SQLServer中包含了一些用于数据存储的特殊数据类型。1）Timestamp2）Uniqueidentifier10用户自定义数据类型Sysname数据类型是系统提供给用户的便于用户自定义数据类型。,6.1T-SQL语言基础,6.1.2变量T-SQL中可以使用两种变量：-局部变量（LocalVariable）-全局变量（GlobalVariable）。,6.1T-SQL语言基础,1局部变量局部变量是用户可自定义的变量，它的作用范围仅在程序内部，在程序中通常用来储存从表中查询到的数据或当作程序执行过程中暂存变量使用。局部变量必须以开头，而且必须用Declare命令声明后才可使用，其说明形式如下：Declare变量名变量类型,变量名变量类型,6.1T-SQL语言基础,给变量赋值，必须使用Select或Set命令来设定变量的值。其语法如下。Select局部变量=变量值Set局部变量=变量值【例6-3】声明一个长度为10个字符的变量ID并赋值。DeclareIDchar（10）SelectID=10010001,6.1T-SQL语言基础,2全局变量全局变量是SQLServer系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。全局变量通常存储一些SQLServer的配置设定值和效能统计数据，用户可在程序中用全局变量来测试系统的设定值或T-SQL命令执行后的状态值。系统定义的全局变量以两个开头,6.1T-SQL语言基础,6.1.3运算符1算术运算符算术运算符用于数字列或变量间的算术运算，包括+（加）、（减）、（乘）、/（除）、%（取余）2比较运算符比较运算符用来比较两个表达式之间的大小，包括大于（）、小于（=）、小于等于（）。,6.1T-SQL语言基础,3逻辑运算符逻辑运算符用于对表达式或操作数进行比较或测试，其运算结果返回的是布尔类型的值，即True或False。True表示条件成立，False则表示条件不成立。可使用的逻辑运算符主要包括And（与）、Or（或）、Not（非）。逻辑运算符如表6-3所示。,6.1T-SQL语言基础,6.1T-SQL语言基础,4位运算符包括&（按位与）、|（按位或）、（按位非）、（按位异或）5连接运算符连接运算符“+”用于连接两个或两个以上的字符、二进制串、列名或者串和列的混合体，将一个串加入到另一个串的末尾。其语法如下：+,6.1T-SQL语言基础,【例6-6】查询分数最高的学生学号以及分数。UseRtvustuSelect分数最高的学生学号是+S_ID+分数为+convert（varchar（10）,Score）FromResultsWherescore=(Selectmax(score)FromResults),6.1T-SQL语言基础,6.1T-SQL语言基础,6.1.4批处理批处理是包含一个或多个T-SQL语句的组，从应用程序一次性地发送到SQLServer执行。SQLServer将批处理语句编译成一个可执行单元，此单元称为执行计划。一个批处理语句以Go结束。,6.1T-SQL语言基础,6.1.5注释注释通常用于记录程序的设计者、评审者、评审日期、修改日期、对修改的描述，帮助人们更好地理解程序。注释的作用大致有两类：（1）序言性注释，写在开头，对程序进行整体上的描述；（2）功能性注释，写在程序内，主要对某一行或某几行进行注释。,6.1T-SQL语言基础,SQLServer2000中的注释分为行内注释和块注释两种。1行内注释-注释文本表示以“-”开始直到该行结束都是注释部分。2块注释/*注释文本*/或/*注释文本*/表示从“/*”符号开始到遇到第一个“*/”符号结束之间的部分为注释。,6.1T-SQL语言基础,6.1.6控制流程语句T-SQL语言使用的流程控制命令与常见的程序设计语言类似，主要有以下几种。1IfElse其语法如下：IfElse条件表达式,6.1T-SQL语言基础,【例6-9】下面的SQL语句是一个IfElse的例子。Declarexint,yint,zintSelectx=1,y=2,z=3Ifxyprintxy-打印字符串xyElseifyzprintyzElseprintzy运行结果如图6-7所示,6.1T-SQL语言基础,图6-7T-SQL中的IFELSE语句,6.1T-SQL语言基础,2BeginEndBeginEnd用于将多个T-SQL语句组合为一个程序块（相当于复合语句），位于Begin和End之间的所有语句视为一个单元执行。当控制语句中必须执行两条以上T-SQL语句块时，需要用BeginEnd将它们组合起来。其语法格式如下：BeginEnd,6.1T-SQL语言基础,【例6-10】下面的SQL语句是一个BeginEnd的例子。BeginDeclaremyvarfloatSetmyvar=456.256BeginPrint变量myvar的值为：PrintmyvarEndEnd运行结果如图6-8所示。,6.1T-SQL语言基础,图6-8T-SQL中的BeginEnd语句,6.1T-SQL语言基础,3CaseCase表达式用来简化SQL表达式，它可以用在任何允许使用表达式的地方，并根据条件的不同返回不同的值。Case表达式不同于一个T-SQL语句，它不能够单独执行，而只能作为一个可以单独执行的语句的一部分来使用。,6.1T-SQL语言基础,Case命令有两种格式。（1）简单Case表达式其语法格式如下：CaseWhenThenWhenThenElseEnd,6.1T-SQL语言基础,（2）搜索Case表达式其语法格式如下：CaseWhenThenWhenThenElseEnd,6.1T-SQL语言基础,4WhileWhile语句通过逻辑表达式来设置一个条件，当条件成立时，重复执行一个语句或语句块（重复执行的部分称为循环体），条件不成立时退出循环，继续执行后面的语句，其语法格式如下：WhileBeginContinueBreakEnd,6.1T-SQL语言基础,【例6-13】执行下面的SQL语句。Declarexint,yint,cintSelectx=1,y=1Whilex3BeginPrintx-打印变量x的值Whiley=、=、!3【例6-45】在“Course”表中找出学分大于3或者小于5的课程。Select*FromCourseWherecredit3orcredit=1985oryear(birth)1980,6.2数据操纵语言,3排序查询结果的顺序是按它们在表中的顺序排列的，可以使用Orderby子句对查询结果重新排序，方法是使用关键字Asc（升序）或Desc（降序）。Orderby子句要写在Where子句后面，语法格式如下：Orderby列名AsclDesc,6.2数据操纵语言,【例6-53】查询“Student”表中的所有数据，要求检索结果按照年龄大小降序排列，如果年龄相同，则按姓名降序排列。Select*FromStudentorderbybirth,snamedesc执行结果如图6-23所示。,6.2数据操纵语言,图6-23查询分析器执行查询排序语句,6.2数据操纵语言,4聚合函数有时候需要对表中的数据做出统计，这时可以使用一些统计函数来返回数据，其语法格式如下：Select标题=统计函数名（列名）From表名,6.2数据操纵语言,1）Avg（All|Distinct表达式）：计算平均值函数，用于数值数据类型。2）Min（表达式）：查找所提供表达式中的最小值函数3）Max（表达式）：查找所提供表达式中的最大值函数4）Sum（All|Distinct表达式）：计算所有数据值的和函数，用于数值数据类型。5）Count（All|Distinct表达式）：计算表达式值的数目统计函数。,6.2数据操纵语言,【例6-54】在“Course”表中求出所有课程的总学分和平均学分。在查询分析器中输入如下语句：UseRtvuStuSelect总学分=sum(credit),平均学分=avg(credit)FromCourse运行结果如图6-24所示。,6.2数据操纵语言,图6-24查询分析器执行函数语句,6.2数据操纵语言,5Groupby子句Groupby子句在被定义的数据的基础上建立分组它产生每一组的总体信息。使用了Groupby子句的选择列表中只能包含常量值、组合列或表达式，每个表达式为每组返回一个值。其语法格式如下：Groupby列名Having条件表达式其中，“Having条件表达式”选项是对生成的组进行筛选。,6.2数据操纵语言,【例6-55】在“Student”表中求出每个系别的人数。在查询分析器中输入以下语句：UseRtvuStuSelectdepartas系名,count(*)as人数FromStudentgroupbydepart运行结果如图6-25所示。,6.2数据操纵语言,图6-25查询分析器里执行Count()函数,6.2数据操纵语言,【例6-56】求系里人数少于7人的系名和人数。UseRtvuStuSelectdepartas系名,count(*)as人数FromStudentgroupbydepartHavingcount(*)7,6.2数据操纵语言,【例6-57】统计“RtvuStu”库中“Student”表中男、女生的人数。UseRtvuStuGoSelect人数=count(*),sexas性别FromStudentGroupbysex运行结果如图6-26所示,6.2数据操纵语言,图6-26查询分析器执行分组函数,6.2数据操纵语言,6Computeby子句Computeby子句可以在结果集内生成控制中断和统计，得到更详细的记录或总记录。它把数据分成较小的组，然后为每组建立详细记录结果数据集，也可为每组产生总记录（如Groupby）。其语法格式如下：Compute集合函数by列名,6.2数据操纵语言,【例6-58】将每个系学生数按系别进行统计，结果按照系别进行排序，并显示统计的明细。UseRtvuStuSelect*FromStudentorderbydepartcomputecount(s_id)bydepart运行结果如图6-27所示。,6.2数据操纵语言,图6-27查询分析器里执行集合Computeby子句,6.2数据操纵语言,7子查询子查询是指在Select语句的Where或Having子句中嵌套另一条Select语句。子查询是一个Select查询，它返回单个值且嵌套在Select、Insert、Update、Delete语句或其他子查询中。,6.2数据操纵语言,创建子查询可用三种语法来实现。Where表达式comparison_oeratorany|some(sqlstatement)Where表达式notIn(sqlstatement)WherenotExists(sqlstatement)各参数说明如下：1）Comparison_oerator指一个比较运算符。2）Sqlstatement指Select语句，遵从与其他Select语句相同的格式及规则，它必须括在括号内。,6.2数据操纵语言,【例6-59】在学生信息表中，找出出生最早和出生最晚的学生记录，并按时间排序。UseRtvustuSelect*Fromstudentwhere(Birth=(Selectmin(Birth)FromStudent)or(Birth=(Selectmax(Birth)FromStudent)orderbyBirth运行结果如图6-28所示。,6.2数据操纵语言,图6-28查询分析器里执行子查询,6.2数据操纵语言,【例6-60】查询选修课程的信息。Select*FromCourseWhereC_IDin(SelectC_IDFromResultsWhereResults.C_ID=Course.C_ID),6.2数据操纵语言,8使用Into子句创建表Into子句是创建表的又一种方法，它不仅可以复制表结构，还可以把Select子句查询的数据插入到新表中，其语法格式如下：Select选取的列Into新表名From表的列表Where查询条件,6.2数据操纵语言,【例6-61】创建一个新表“newStu”，它的结构和“Student”表相同，但该表不包含任何记录。Select*IntonewStuFromStudentWhereS_IDisnull,6.2数据操纵语言,9合并结果集合并查询是将两个或两个以上的查询结果合并，形成一个具有综合信息的查询结果。使用Union子句可以把两个或两个以上的查询结果集合并为一个结果集，其语法格式如下：查询语句UnionAll查询语句,6.2数据操纵语言,【例6-62】查询所有学生和教师的姓名和性别。（假设有一个Teacher表，具有Tname和Sex列）Selectsname,sexFromStudentUnionSelecttname,sexFromTeacher,6.2数据操纵语言,10数据查询综述【例6-63】查询语句举例。1）查询姓“陈”的男生的资料。Select*FromStudentWhereleft(sname,2)=陈andsex=男2）求学号位于前面3位的学生。Selecttop3*FromStudentorderbyS_ID,6.2数据操纵语言,3）生成一个相同结构的“Student2”表，同时复制表中的数据。Select*intoStudent2FromStudent4）查找与陈一凡在同一个系别的学生学号、姓名、性别和系别。SelectS_ID,Sname,sex,departFromStudentWhereDepart=(SelectdepartFromStudentWheresname=陈一凡),6.2数据操纵语言,5）显示每个学生的平均分数，并按分数总和由大到小输出。SelectS_ID,avg(score)FromResultsGroupbyS_IDOrderbyavg(score)desc6）选取分数界于80和100之间的学生学号、课程号和成绩。Select*FromResultsWherescorebetween80and100,6.2数据操纵语言,7）查找出成绩小于60分的学生信息。Select*FromstudentWhereS_IDin(SelectS_IDFromresultsWherescore60),6.2数据操纵语言,6.2.2Insert语句Insert语句用来向表中添加数据，其基本格式如下：Insertinto表名列名values(数据)各参数说明如下：1）“表名”是将要添加数据的表。2）“列名”是用逗号分开的表中的部