第3章TransactSQL语言课件

第3章Transact-SQL语言(1)2．1Transact-SQL概论

SQL语言是一种用于存取和查询数据，更新并管理关系数据库系统的数据库查询和编程语言。Transact-SQL是对标准SQL程序语言的增强，是用于应用程序和SQLServer之间通信的主要语言。Transact-SQL的语法结构类似于英语，易学易用，书写随意。SQL语言是一种说明性的语言。SQL语言特别适合于Client/Server体系结构。SQL语言是由数据库定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）组成。数据库类型主数据文件：数据库的起点，指向数据库中的其他文件。*.mdf次数据文件:*.ndf日志文件:包含用于恢复数据的所有日志信息。*.ldf文件名与文件大小逻辑文件名：是在T-SQL中引用物理文件时使用的名称。物理文件名：符合操作系统命名规则文件大小：一般由初始容量不断增大，可以制定一个最大容量。数据库对象表：由行和列组成，是具体组织和存储数据的对象。记录：表中每一行数据即为一条记录主键：表中一列或多列的组合，能唯一确定一条记录。外键：一列或多列的组合中，存在A表中，但不是A的主键，同时存在B表中，且为B表的主键。索引：表中一列或若干列值的集合和相应的指向表中物理标识的数据页的逻辑指针清单。约束：是SQL实施数据一致性和完整性的方法。数据库对象默认值：插入数据时，未指定具体值的字段，系统自动提供默认的数据规则：用来限制表中字段的有限范围，确保数据完整性的方式存储过程：一组经过编译的而已重复使用的T-SQL代码的组合触发器：一组特殊的存储过程，与表格或某些操作相关联。系统数据库Master:记录SQL实例的所有系统级信息，同时还记录其他数据库是否存在，以及这些数据库存放的位置。不能添加文件或文件组不能更改排序规则，更改数据库所有者不能创建全文目录或索引不能在系统表上创建触发器不能删除数据库，上传主文件组不能将数据库设置为OFFLINE和ReadOnly系统数据库Model:用于SQL实例上创建的所有数据库的模板，对model数据库进行的修改将应用于以后的所有数据库Msdb:用于计划警报和作业以及与备份和回复相关的信息，由SQLServer代理Tempdb:所有用户都可用的全局资源，保存所有临时表和临时存储过程显示创建的临时对象所有版本的更新记录创建或重新生成索引时，临时排序的结果系统数据库Resource:是只读数据库，包含SQLServer2005中的所有系统对象。创建数据库注意事项：只有系统管理员和数据库拥有者才可创建创建过程就是为数据库设计名称、设计所占用的存储空间和存储文件位置的过程所有新数据库都是系统数据库model的副本单个数据库可存储在单个文件上，也可跨文件存储数据库的大小可增大或收缩最多可存储32767个数据库自动生成sysdatabases系统表CREATDATABASE命令建数据库Creatdatabase_nameON[[<>[,…n]][,<>[,…n]]][LOGON{<>[,…n]}][COLLATEcollation_name]<>定义[PRIMARY]([NAME=logical_,]‘OS_’[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,=growth_increment])[,…n][]：表示可选语法项，省略时各参数取默认值[,…n]：表示前面的内容可重复多次{}：必选项<>：表示实际的语句要用相应的内容代替文字大写：T-SQL的关键字文字小写：用户提供的T-SQL语法的参数Database_name:用户创建的数据库名ON：指定存放数据库的数据文件信息LOGON：指定日志文件的明确定义COLLATE：指定数据库默认排序规则<>：指定文件属性NAMElogical_:定义文件的逻辑名称os_：定义文件的物理名称SIZE:文件属性的初始值MAXSIZE:定义文件可以增长到的最大值:定义文件的每次增长的大小：定义对文件组的控制注意事项1、创建用户数据库后，要备份master数据库2、所有数据库都至少包含一个主文件组，所有系统表都分配在主文件组中。3、每个数据库都有一个所有者，可在数据库中执行某些特殊的操作。4、创建数据库的权限默认的授予sysadmin和dbcreator固定服务器角色的成员。举例创建一个xsgl数据库、其中主数据库文件为10MB，最大容量不受限制，每次增长1MB，事务日志文件容量为1MB，最大容量不受限制，每次增长100KB。答案CREATEDATABASExsglONPRIMARY(NAME=‘xsgl’,‘C:\programFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\xsgl.mdf’,SIZE=10MB,MAXSIZE=UNLIMITED,)LOGON(NAME=‘xsgl_log’,‘C:\programFiles\MicrosoftSQL,Server\MSSQL.1\MSSQL\Data\xsgl.ldf’SIZE=1MB,MAXSIZE=UNLIMITED,)练习:创建一个xsb数据库、其中主数据库文件为10MB，最大容量为50MB，每次增长0.1MB，事务日志文件容量为1MB，最大容量不受限制，每次增长5%事务日志事务日志主要是存放恢复数据库所需的信息，是数据库中已发生的所有修改和执行每次修改的事务的一连串记录，后缀名为“*.ldf”，日志文件最小为512KB日志文件有点1、可以单独备份2、可从服务器失效的时间中将服务恢复到最近的状态3、可以很容易监测事务日志文件的空间，不会抢占数据库空间4、向数据库和事务日志写入数据时会产生较少的冲突，有利于提高SQL的完整性。工作过程1、应用程序发出一个修改数据库命令2、事务开始时，事务日志会记录事务开始标志，并将可能影响的数据库页从磁盘读入到缓冲区3、事务中每个更改语句都被记录在日志文件中4、在缓冲去中修改相应数据打开数据库使用语句：USEdatabase_name查看数据库1、使用SQLServerManagementStudio查看2、使用T-SQL语句查看[EXECUTE]sp_helpdb[数据库名]修改数据库1、使用SQLServerManagementStudio修改数据库配置2、使用T-SQL语句修改数据库配置ALTERDATABASEdatabase_name{ADDFILE<>[,…n][TO]|ADDLOGFILE<>[,…n]|REMOVE|ADD|REMOVEMODIFYFILE<>MODIFYNAME=new_database_nameMODIFY{}}例题用T-SQL命令将xsgl重命名为“学生管理系统”？ALTERDATABASExsglMODIFYNAME=学生管理系统练习为“学生管理系统”增加一个数据库文件，文件名为“成绩表.ndf”删除数据库1、使用SQLServerManagementStudio删除数据库2、使用T-SQL语句删除数据库语法：DROPDATABASEdatabase_name例题 删除已创建的“学生管理系统”？DROPDATABASE学生管理系统GO2．2操作数据属于数据操作语言（DML）的功能：SELECT、INSERT、UPDATE、DELETE共4个语句。最重要的是SELECT，希望大家熟练掌握。2．2．1打开一个数据库

在对数据库中的数据进行操作之前，必须先使用USE命令打开数据库，并使用GO语句作为结束行。例如：USEXKGO2．2．2SELECT语句

SELECT语句主要用于检索数据，也可以用来向局部变量赋值或者调用一个函数。常用的语法为：SELECT选择列表FROM表名WHERE检索条件GROUPBY分类列HAVING分类后的筛选条件ORDERBY输出排序列COMPUTER[BY]在查询结果后附加统计查询。Computerby前必有orderby排序。下面详细介绍SELECT语句的各种使用方法。一、在列名中使用*在列名中使用星号*，则从FROM子句中指定的表或视图中检索并返回所有列二、在列名之前使用DISTINCT消除重复行在列名之前使用DISTINCT，则会消除输出列的值都相同的那些行。如有重复行，则只显示第一行。与DISTINCT相反的是ALL，表示显示全部满足条件的那些行

三、在列名之前使用TOPn[PERCENT]返回前n[%]行使用TOP，可以从结果集中仅返回前n行。如果指定了PERCENT，则仅返回前n%行，此时n必须介于0-100之间。如果语句中有ORDERBY，则先执行ORDERBY排序后返回前n[%]行。四、修改检索结果中列的标题方法一：新标题=列名方法二：列名新标题方法三：列名AS新标题列名不能加引号，当新标题中含有空格时必须加引号。单引号和双引号都可以。五在检索结果中显示字符串（必须加单引号）在SELECT的列名中，将要增加的字符串用单引号括起来，显示结果的该列将全部显示指定的字符串。六、在WHERE子句中给出检索的条件条件结果为逻辑值，可以包含比较运算符（等于=、不等于<>或!=、大于>、大于等于>=或!<、小于<、小于等于<=或!>）和逻辑运算符（与AND、或OR、非NOT）。七、列为表达式在列名中可以使用表达式（列名、函数、常数、表达式等）。八、使用ORDERBY子句重新排序检索结果可以使用ORDERBY子句对检索结果重新进行排序，可以规定升序或降序，方法是使用关键字ASC或DESC。如果省略ASC或DESC，系统则默认为升序。可以在ORDERBY子句中指定多个列，检索结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2列排序……依此类推。九、在WHERE中使用IN关键字使用IN关键字进行检索比使用多个OR运算符进行检索更为简单，并且易于阅读和理解。也可以使用NOTIN，功能与IN相反。十、在WHERE中使用LIKE关键字LIKE关键字是一个匹配运算符，它与字符串表达式相匹配，字符串表达式被定义为字符串和通配符。字符串和通配符必须括在单引号中。SQL的4个通配符为：%——百分号，匹配包含0个或多个字符的字符串。_——下划线，匹配任何单个的字符。[]——排列通配符，匹配任何在范围或集合中的单个字符，例如[m-p]匹配的是mnop单个字符。[^]——不在范围之内的字符，匹配任何不在范围或集合之内的单个字符。要查找通配符本身，需将它们用方括号括起来。十一、在WHERE中使用ISNULL关键字在WHERE中使用ISNULL关键字检索列中没有赋值的行。十二、在WHERE中使用比较运算符和逻辑运算符实现检索某一范围内的信息在WHERE中使用比较运算符和逻辑运算符实现检索某一范围内的信息。也可以单独使用比较运算符。十三、在WHERE中使用BETWEEN关键字实现检索某一范围内的信息BETWEEN关键字总是和AND一起使用，用于检索某一范围内的信息。也可以使用NOTBETWEEN。使用格式是：BETWEEN下限AND上限。相当于>=下限AND<=上限十四、在WHERE之后使用COMPUTE子句COMPUTE用于计算总计或进行分组小计，总计值或小计值将作为附加新行出现在检索结果中。该子句用在WHERE子句之后。第3章Transact-SQL语言(2)十五、在WHERE之后使用COMPUTEBY子句使用COMPUTEBY子句对BY后面给出的列进行分组显示，并计算该列的分组小计。使用COMPUTEBY子句时必须使用ORDERBY对COMPUTEBY中BY指定的列进行排序。使用格式是：COMPUTE函数BY分类列名。十六、在WHERE之后使用GROUPBY子句将检索结果按照GROUPBY后面指定的列进行分组该子句写在WHERE子句的后面当在SELECT子句中包含有聚合函数时，最适合使用GROUPBY子句。SELECT子句中的选项列表中出现的列包含在聚合函数中或者包含在GROUPBY子句中。十七、在GROUPBY之后使用HAVING子句HAVING子句用于限定组或聚合函数的检索条件该子句常常用在GROUPBY之后十八、在WHERE中使用子查询子查询是在查询中包含另一个查询的查询可以使用子查询代替表达式子查询只能返回一列数据，有时只有返回一行数据。子查询要用圆括号括起来。十九、使用UNION运算符UNION运算符用于将两个或多个检索结果合并成一个结果，当使用UNION时，需遵循两个规则：1．所有查询中的列数和列的顺序必须相同。2．所有查询中按顺序对应列的数据类型必须兼容。如果希望重新排序多个检索结果的合并结果，则在最后的SELECT语句中使用ORDERBY子句。二十、对多个表或视图进行检索

1．笛卡儿积使用格式：SELECT*FROM表1，表2，……。结果的行数是各表行数的积，结果的列数是各表列数的和。2．在WHERE中使用连接条件（1）连接条件为避免产生笛卡儿积，并且得到所需要的检索结果，必须使用WHERE子句给出连接条件。一般来说，对N个表（或视图）的检索要有N-1个连接条件。多表检索中的表或视图可以在同一个数据库中，也可以来自不同的数据库。在FROM子句中最多可使用16个表或视图。（2）连接类型连接的类型有内连接、外连接和交叉连接。内连接就是只包含满足连接条件的数据行，它主要有自然连接和相等连接等形式。外连接是连接运算的扩展，可以处理缺失信息，它又分为左外连接、右外连接和全连接三种形式。交叉连接是一种很少使用的连接，两个表的交叉连接的结果集的总行数等于所连接的两个表的行数的积，交叉连接通常没有实际的意义。3．相等连接（内连接）相等连接是将要连接的列作相等比较后所作的连接，相等连接总会产生冗余，因为连接的列要显示两次。内连接需在FROM子句中指明：FROM左表名INNERJOIN右表名ON连接条件4．自然连接自然连接是将要连接的列作相等比较后的连接，但是连接的列只显示一次。自然连接消除了相等连接产生的冗余。5．带有选择条件的连接6．比较连接在进行连接时不必使用等号进行连接，而采用比较运算符。7．自连接自连接就是一个表和它自身进行连接。表名在FROM子句中出现两次，可以对表定义一个别名，在SELECT子句中引用的列名也要使用表的别名进行限定。左连接：左连接首先计算两个表的自然连接，再取出左表中与右表任一数据行都不匹配的那些行，用空值填充所有来自右表的那些列，再把增加的这些行增加到自然连接的结果集中。左连接需在FROM子句中指明：FROM左表名LEFTJOIN右表名ON连接条件（2）右连接右连接与左连接相对称：右连接首先计算两个表的自然连接，再取出右表中与左表任一数据行都不匹配的那些行，用空值填充所有来自左表的那些列，再把增加的这些行增加到自然连接的结果集中。右连接需在FROM子句中指明：FROM左表名RIGHTJOIN右表名ON连接条件（3）完全连接为了包含两个表中都不匹配的数据行，可以使用完全连接，它完成左连接和右连接的操作，包括了左表和右表中所有不满足条件的行。完全连接需在FROM子句中指明：FROM左表名FULLJOIN右表名ON连接条件8．外连接

9．交叉连接交叉连接就是将连接的两个表的所有行进行组合，形成一个结果集，该结果集的列数等于两个表的列数和，行数等于两个表的行数积。结果与笛卡儿积相同。交叉连接需在FROM子句中指明：FROM表1CROSSJOIN表2二十一、使用表别名可以给表定义别名，以方便检索时对列的引用和简化连接条件的书写。定义别名的方法：在FROM子句中将要定义别名的表名用空格间隔，然后紧随所定义的别名。即表名在前别名在后，中间中空格隔开。二十二、在WHERE中使用EXISTS关键字EXISTS子句用于测试跟随的子查询中的行是否存在，如果存在，则返回TRUE（真）。2．2．3向表中输入数据

一、使用INSERT语句INSERT语句用来向表中或视图或输入新的数据行。输入数据的方法有两种：使用INSERT语句和使用企业管理器。INSERT语句的简单语法：INSERT[INTO]表名{[(列名表)]{VALUES({DEFAULT|NULL|表达式}[,……n])|执行过程}列的个数必须与VALUES子句中给出的值的个数相同；数据类型必须和列的数据类型相对应。当省略列名表时，其值必须是表的所有列，并且顺序一致。二、使用企业管理器输入数据打开表返回所有行。输入数据后关闭窗口即可。三、在INSERT语句中使用SELECT子句插入数据INSERT语句的特点是每一次只能输入一行数据。在INSERT语句中使用SELECT子句可以将表中数据行插入到表中，并且可以一次输入多行数据。使用SELECT子句插入数据的语法格式：INSERT表名[(列名表)]SELECT列表FROM源表WHERE条件注意：1．INSERT语句中和FROM子句中所使用的表可以相同也可以不相同，但必须是已存在的表。2．INSERT中指定的表和SELECT子句得到的结果集一定要兼容，即列的数量和顺序必须相同，列的数据类型和长度要相同，或者进行转换。3．如果省略列名表，则SELECT中的列表必须与表名中的所有列保持一致。使用SELECTINTO语句允许用户定义一个新表，并且把SELECT的数据插入到新表中。前面的3种方法的共同点就是在数据插入之前表已经存在。而使用SELEC