第9章Transactsql.docx
《第9章Transactsql.docx》由会员分享，可在线阅读，更多相关《第9章Transactsql.docx（82页珍藏版）》请在冰豆网上搜索。
第9章Transactsql
第9章Transact-SQL113
SQL（StructuredQueryLanguage，结构化查询语言）
T-SQL是MicrosoftSQLServer提供的一种结构化查询语言
本章主要介绍：
Transact-SQL程序设计基础
9.1SQL语言113
9.1.1概述113
SQL有ANSISQL-92标准与产业界的标准之分
Transact-SQL是在ANSISQL-92标准的基础上扩充而来的
SQL语言是应用于数据库的语言，本身是不能独立存在的。
它是一种非过程性语言。
9.1.2SQL语言的分类114
SQL语言按照用途可以分为如下4类：
DQL（DataQueryLanguage）数据查询语言
DDL（DataDefinitionLanguage）数据定义语言
DML（DataManipulationLanguage）数据操纵语言
DCL（DataControlLanguage）数据控制语言
1．数据查询语言
2．数据定义语言
3．数据操纵语言
4．数据控制语言
9.2Transact-SQL基础115
主要介绍：
查询分析器的使用；如何使用查询分析器来执行相关的SQL语句
SQL查询分析器是一个图形用户界面，用以交互地设计和测试Transact-SQL语句、批处理和脚本。
9.2.1查询分析器115
SQL查询分析器提供的功能：
用于输入Transact-SQL语句的自由格式文本编辑器。
在Transact-SQL语句中使用不同的颜色，以提高复杂语句的易读性。
对象浏览和对象搜索工具，可以轻松查找数据库中的对象和对象结构。
模板，可用于加快创建SQLServer对象的Transact-SQL语句的开发速度。
模板是包含创建数据库对象所需的Transact-SQL语句基本结构的文件。
用于分析存储过程的交互式调试工具。
以网格或自由格式文本窗口的形式显示结果。
显示计划信息的图形关系图，用以说明内置在Transact-SQL语句执行计划中的逻辑步骤。
这使程序员得以确定在性能差的查询中，具体是哪一部分使用了大量资源。
之后，程序员可以试着用不同的方法更改查询，使查询使用的资源减到最小的同时仍能返回正确的数据。
使用索引优化向导分析Transact-SQL语句以及它所引用的表，以了解通过添加其他索引是否可以提高查询的性能。
启动方法：
一、开始——程序——MicrosoftSQLServer——查询分析器
二、企业管理器——工具——SQL查询分析器
三、命令提示符下输入：
isqlw
其文件位置：
C:
\ProgramFiles\MicrosoftSQLServer\80\Tools\Binn\isqlw.exe
SQL查询分析器分为两个窗口：
左侧：
对象浏览器：
对象和模板两个选项卡
右侧：
SQL语句的输入窗口：
执行或出错结果显示在下面
9.2.2数据库的操作语句117
1．创建数据库
CREATEDATABASEdatabase_name
[
[ON[filespec]]
[LOGON[filespec]]
]
filespec定义为
（[NAME=logical_file_name,]
FILENAME=’os_file_name’
[,SIZE=size]
[,MAXSIZE={max_size|unlimited}]
[,FILEGROWTH=growth_increment]）
例9.1建立一个名称为test的数据库
CREATEDATABASEtest
按F5后显示
CREATEDATABASE进程正在磁盘'test'上分配0.75MB的空间。
CREATEDATABASE进程正在磁盘'test_log'上分配0.49MB的空间。
例9.2创建一个test1数据库，并设定数据文件为“E:
\SQLServer\测试数据1.MDF”，大小为10MB，最大为50MB，每次增长5MB。
事务日志文件为“E:
\SQLServer\测试数据1日志.LDF”，大小为10MB，最大为20MB，每次增长为5MB。
createdatabasetest1
on（
NAME=测试数据1,
FILENAME='E:
\SQLServer\测试数据1.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
）
LOGON（
NAME=测试数据1日志,
FILENAME='E:
\SQLServer\测试数据1日志.LDF'
）
按F5后显示
CREATEDATABASE进程正在磁盘'测试数据1'上分配10.00MB的空间。
CREATEDATABASE进程正在磁盘'测试数据1日志'上分配1.00MB的空间。
2．修改数据库
ALTERDATABASEdatabase
{ADDFILEfilespec
|ADDLOGFILElogical_file_name
|REMOVEFILElogical_file_name
|MODIFYFILEfilespec
|MODIFYNAME=new_dbname
）
filespec定义同建立数据库
例：
为test1数据库新增加一个逻辑名为“测试数据2”的数据文件，其大小及其最大值分别为10MB和50MB。
ALTERDATABASEtest1
ADDFILE（
NAME=测试数据2,
FILENAME='D:
\ProgramFiles\MicrosoftSQLServer\测试数据2.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
）
按F5结果：
以10.00MB为单位在磁盘'测试数据2'上扩展数据库。
例：
数据库改名：
ALTERDATABASE原数据库名
modifyname=新数据库名
ALTERDATABASEb
modifyname=a
sp_renamedb原数据库名,新数据库名
3．使用和删除数据库
USEDATABASEdatabase_name
DROPDATABASEdatabase_name
例9.3删除test1数据库
DROPDATABASETEST1
按F5运行后显示：
正在删除数据库文件'D:
\ProgramFiles\MicrosoftSQLServer\测试数据2.MDF'。
正在删除数据库文件'D:
\ProgramFiles\MicrosoftSQLServer\测试数据1日志.LDF'。
正在删除数据库文件'D:
\ProgramFiles\MicrosoftSQLServer\测试数据1.MDF'。
9.2.3表的操作语句122
可以使用SQL语言创建表、修改表、删除表。
1．表的创建
CREATETABLEtable_name
（
column_name1data_type[NULL|NOTNULL][PRIMARY|UNIQUE]
[FOREIGNKEY[（column_name）]]
REFERENCESref_table[（ref_column）]
[column_name2data_type…]
……
）
CREATETABEL<表名>
（<列名><数据类型>[列级完整性约束定义]
{,<列名><数据类型>[列级完整性约束定义]…
[,表级完整性约束定义]）
[列级完整性约束定义]包括：
NOTNULL：
限制列取值非空
DEFAULT：
给定列的默认值，形式为：
DEFAULT常量
UNIQUE：
限制列取值不能重复
CHECK：
限制列的取值范围，形式为：
CHECK（约束表达式）
PRIMARYKEY：
指定主码
FOREIGNKEY<列名>REFERENCES<外表名>（<外表列名>）：
定义本列为引用其他它表的外码。
NOTNULL和DEFAULT只能在列级完整性约束处定义，其他的既可是列级也可是表级完整性约束。
若主码由多个列组成，则只能在表级定义。
例：
usetest
go
createtablest1
（
snochar（5）PRIMARYKEY,
snamechar（8）NOTNULL,
ssexchar
（2）CHECK（ssex='男'ORssex='女'）,
sagetinyintCHECK（sage>=15ANDsage<=45）,
sdeptchar（20）default'计算机系'
）
例：
usetest
go
createtablest2
（
cnochar（10）primarykey,
cnamechar（20）notnull
）
例：
usetest
go
createtablest3
（
snochar（5）,
cnochar（10）,
gragdtinyint,
primarykey（sno,cno）,
foreignkey（sno）referencesst1（sno）,
foreignkey（cno）referencesst2（cno）
）
（1）基本用法
例9．4在test数据库中创建一个clients表。
USETEST
CREATETABLEclients（
cidint,
cnamechar（8）,
addresschar（50）
）
（2）段属性参数
常用的属性参数：
NULL和NOTNULL限制字段可以为空，或不能为空
PRIMARYKEY设置字段为主键
UNIQUE指定字段具有惟一性
例9.5在test数据库中建立一个book表，并指定bid为主键，而bname为非空：
CREATETABLEbook（
BidintNOTNULLPRIMARYKEY,
Bnamechar（8）NOTNULL,
Authoredchar（10）
）
（3）与其他表建立关联
FOREIGNKEYREFERENCEref_table（ref_column）
例9.6将book表中的authorid字段关联到authors表的authorid字段。
CREATETABLEauthors（
AuthoredintNOTNULLPRIMARYKEY,
Authornamechar（20）,
Addresschar（30）
）
CREATETABLEbook（
BidintNOTNULLPRIMARYKEY,
Bnamechar（8）NOTNULL,
AuthoredintFOREIGNKEYREFERENCESauthors（authored）
）
SELECTINTO语句：
创建一个新表，并用SELECT的结果集填充该表，新表的结构由选择列表中表达式的特性定义。
SELECT[select_list]INTO新表名from表名
复制表结构：
（从a表到b表）注：
b应该是不存在的表
Select*intobfromawhere1<>1
复制表数据：
（从a表到b表）注：
b应该是已存在的表
insertintob（列名列表）select列名列表fromawhere子句
复制表结构的同时复制数据（从a表到b表）注：
b表是尚不存在的表
select列名列表intobfromawhere子句
例9．7给出以下SQL语句的功能。
SELECTsno,sname,classintostudent1FROMstudent
2．修改表（结构）
ALTERTABLEtable_name
ADD[column_namedata_type]
[PRIMARYKEY|CONSTRAIN]
[FOREIGNKEY（column_name）
REFERENCESref_table（ref_column）]
DROP[CONSTRAINT]constraint_name|COLUMNcolumn_name
ADD子句：
增加字段
DROP子句：
删除限制或字段。
CONSTRAINT表示删除限制；COLUMN表示删除字段。
例9．8给出以下SQL语句的功能。
USEtest
ALTERTABLEBOOKADDpriceint
3．删除表
DROPTABLEtable_name
例9．9给出删除book表的SQL语句。
DROPTABLEbook
9.2.4数据查询127
“查询”的含义就是用来描述从数据库中获取数据和操纵数据的过程。
SQL语言中最主要、最核心的部分就是它的查询功能。
查询的基本格式：
由SELECT子句、FROM子句、WHERE子句组成部分
SELECT列名表
FROM表或视图名
WHERE查询限定条件
完整的SELECT语句格式：
SELECT列名表
[INTO新表名]
FROM表或视图名
[WHERE查询限定条件]
[GROUPBY分组表达式]
[HAVING分组条件]
[ORDERBY次序表达式[ASC|DESC]]
1．投影查询P125
例9．10查询student表中所有记录的sname、ssex和class列。
SELECTsname,ssex,classFROMstudent
例9．11查询老师所有的单位即不重复的depart列。
SELECTDISTINCTdepartFROMteacher
AS标题名
SELECTDISTINCTdepartas部门FROMteacher
例9．12查询student表的所有记录。
SELECTsnoAS'学号',snameAS'姓名',ssexAS'性别',
SbirthdayAS'出生日期',classas'班号'
FROMstudent
例：
selectsno学号,year（getdate（））-year（sbirthday）as年龄fromstudent
2．选择查询P126
例9．13查询score表中成绩在60~80之间的所有记录。
SELECT*
FROMscore
WHEREdegreeBETWEEN60AND80
BETWEENmANDn表示在指定的范围m~n内。
例9．14查询score表中成绩为85、86或88的记录。
SELECT*
FROMscore
WHEREdegreeIN（85,86,88）
IN…表示在指定的列表中。
例9．15查询student表中“95031”班或性别为“女”的学生记录。
SELECT*
FROMstudent
WHEREclass='95031'orssex='女'
3．排序查询P128
ORDERBY子句用来控制选择行的显示顺序。
ORDERBY子句必须是SQL命令中的最后一个子句。
（除指定目的地子句外）
升序（默认）：
ASC
降序：
DESC
例9．16以class降序显示studetn表的所有记录。
SELECT*
FROMstudent
ORDERBYclassDESC
例9．17以cno升序、degree降序显示score表的所有记录
SELECT*
FROMscore
ORDERBYcno,degreeDESC
4．使用聚合函数）P129
聚合函数用于实现数据统计等功能。
常用聚合函数：
AVG、COUNT、MIN、MAX、SUM、STDEV、VAR
例9．18查询“95031”班的学生人数
SELECTcount（*）as'95031班人数'
FROMstudent
WHEREclass='95031'
例9．19查询score表中的最高分的学生学号和课程号
SELECTcnoAS'课程名',MAX（degree）as'最高分'
FROMscore
GROUPBYcno
查询结果：
课程名最高分
------------------------------
3-10592
3-24586
6-16685
（所影响的行数为3行）
或
SELECTsno'学号',cno'课程号',degree'最高分'
fromscore
wheredegree=
（selectmax（degree）
fromscore）
（课本此例将出错，出错信息如下：
服务器:
消息8118，级别16，状态1，行1
列'score.sno'在选择列表中无效，因为该列未包含在聚合函数中，并且没有GROUPBY子句。
服务器:
消息8118，级别16，状态1，行1
列'o'在选择列表中无效，因为该列未包含在聚合函数中，并且没有GROUPBY子句。
）
例9．20查询编号为“3-105”的课程的平均分
SELECTAVG（degree）as'课程平均分'
FROMscore
WHEREcno='3-105'
GROPUBY子句
HAVING子句
当WHERE子句、GROUPBY子句和HAVING子句同时出现在一个查询中时，SQL的执行顺序如下：
WHERE子句——GROUPBY子句——聚合函数——HAVING子句
例9．21查询score表中至少有5名学生选修的并以3开头的课程号的平均分数。
SELECTcno,avg（degree）as'平均分'
FROMscore
WHEREcnoLIKE'3%'
GROUPBYcno
HAVINGCOUNT（*）>5
例9．22查询最低分大于70，最高分小于90的SNO列。
SELECTsno
FROMscore
GROUPBYsno
HAVINGMIN（degree）>70andMAX（degree）<90
5．表的连接查询P130
表的连接查询可以实现若干个表数据的联合查询。
WHERE子句中给出连接条件
FROM子句指定要连接的表
SELECT列名1，列名2，……
FROM表1，表2，……
WHERE连接条件
公共列（同名的列）的区分：
表名前缀，其格式为：
表名.列名
例9．23查询所有学生的sname、cno和degree列
SELECTstudent.sname,o,score.degree
FROMstudent,score
WHEREstudent.sno=score.sno
别名：
SQL为了简化输入，允许在查询中使用表的别名，以缩写表名，可以在FROM子句中为表定义一个临时别名，然后在查询中引用。
连接条件分类：
等值连接和非等值连接。
例9．24查询所有学生的sno、cname和degree列。
SELECTx.sno,ame,x.degree
FROMscorex,coursey
WHEREo=o
例9．25查询“95033”班所选课程的平均分
SELECTo,avg（y.degree）as'平均分'
FROMstudentx,scorey
WHEREx.sno=y.snoandx.class='95033'
GROUPBYo
INSERT[INTO]<表名>[（<列名列表>）]values（值列表）
例9．26假设使用如下命令建立了一个grade的表：
CREATETABLEgrade（lowint,uppint,rankchar
（1））
INSERTINTOgradevalues（90,100,'A'）
INSERTINTOgradevalues（80,89,'B'）
INSERTINTOgradevalues（70,79,'C'）
INSERTINTOgradevalues（60,69,'D'）
INSERTINTOgradevalues（0,59,'E'）
查询所有学生的sno、cno和rank列：
SELECTsno,cno,rank
FROMscore,grade
WHEREdegreebetweenlowandupp
ORDERBYrank
自连接：
将同一个表进行连接称自连接，进行自连接就如同两个独立的表一样，可以把一个表的某行与同一表中的另一行连接起来。
例9．27查询选学“3-105”课程的成绩高于“109”号学生成绩的所学生记录，并按成绩从高到低排列。
SELECTo,x.sno,x.degree
FROMscorex,scorey
WHEREo='3-105'andx.degree>y.degreeandy.sno='109'
ando='3-105'
ORDERBYx.degreeDESC
例9．28查询成绩高于学号“109”的课程号“3-105”的成绩的所有记录，并按成绩从低到高排列。
SELECTo,x.sno,x.degree
FROMscorex,scorey
WHEREx.degree>y.degreeandy.sno='109'ando='3-105'
ORDERBYx.degree
6．子查询P134
当一个查询是另一个查询的条件时，称之为子查询。
子查询最常用于SQL的WHERE子句中，我们按子查询返回单个值还是一组值（此时了查询前应接关键字IN、ANY或ALL等）、查询一个表还是多个表进行分类。
例9．29查询和学号为105的学生同年出生的所有学生的sno、sname和b
sbirthday列。
SELECTsno,sname,sbirthday
FROMstudent
WHEREyear（sbirthday）=
（SELECTyear（sbirthday）
FROMstudent
WHEREsno='105'）
先执行子查询，再执行主查询。
例9．30查询“张旭”老师任课的学生成绩，并按成绩递增排列。
SELECTcno,sno,degree
FROMscore
WHEREcno=
（SELECTo
FROMcoursex,teachery
WHEREx.tno=y.tnoandy.tname='张旭'）
ORDERBYdegreeDESC
如果返回多值，则要使用ANY、ALL、IN和NOTIN等，它们与查询条件一起构造返回一组值的子查询。
例9．31查询选修某课程的学生人数多于5人的老师姓名。
SE
升级会员 