计算机关系数据库与sql第3章

第3章关系数据库标准语言——SQL北京林业大学软件教研室23.1SQL语言的基本概念与特点3.2了解SQLServer20003.3创建与使用数据库3.4创建与使用数据表3.5创建与使用索引3.6数据查询3.7数据更新3.8视图3.9数据控制北京林业大学软件教研室3结构化查询语言StructuredQueryLanguage数据查询数据定义数据操纵数据控制北京林业大学软件教研室43.1SQL语言的基本概念与特点3.1.1SQL语言的发展及标准化SQL语言的发展ChamberlinSEQUELSQL大型数据库

SybaseINFORMIXSQLServerOracleDB2INGRES----------------小型数据库

FoxProAccess北京林业大学软件教研室53.1.2SQL语言的基本概念基本表（BaseTable）一个关系对应一个基本表一个或多个基本表对应一个存储文件视图（View）视图是从一个或几个基本表导出的表，是一个虚拟的表S(SNo，SN，Sex，Age，Dept)S_Male(SNo，SN，Age，Dept)无数据，只有定义Sex='男'在数据库中只存有S_Male的定义，数据仍在S表中北京林业大学软件教研室6

SQL语言支持的关系数据库的三级模式结构

北京林业大学软件教研室73.1.3SQL语言的主要特点SQL语言是类似于英语的自然语言，简洁易用

SQL语言是一种非过程语言SQL语言是一种面向集合的语言SQL语言既是自含式语言，又是嵌入式语言SQL语言具有数据查询、数据定义、数据操纵和数据控制四种功能北京林业大学软件教研室83.2了解SQLServer2000SQLServer是一个关系数据库管理系统

企业版（EnterpriseEdition）

标准版（StandardEdition）

个人版（PersonalEdition）

开发者版（DeveloperEdition）北京林业大学软件教研室93.2.1SQLServer2000的主要组件组件功能企业管理器管理所有的数据库系统工作和服务器工作查询分析器执行Transact-SQL命令等SQL脚本程序服务管理器启动、暂停或停止SQLServer的四种服务客户端网络实用工具配置客户端的连接、测定网络库的版本信息以及设定本地数据库的相关选项服务器网络实用工具配置服务器端的连接、测定网络库的版本信导入和导出数据在OLEDB数据源之间复制数据在IIS中配置SQLXML支持在运行IIS的计算机上定义、注册虚拟目录，并在虚拟目录和SQLServer实例之间创建关联事件探查器监视SQLServer数据库系统引擎事件联机丛书查询信息北京林业大学软件教研室103.2.2企业管理器由EnterpriseManager产生的SQL脚本是一个后缀名为.sql的文件企业管理器的管理工作文本文件管理数据库

管理数据库对象管理备份

管理复制

管理登录和许可管理SQLServerAgent

管理SQLServerMail

北京林业大学软件教研室113.2.3查询分析器使用查询分析器的熟练程度是衡量一个SQLServer用户水平的标准。北京林业大学软件教研室123.3创建与使用数据库数据文件1事务日志文件

数据库数据文件n…存放数据库数据和数据库对象的文件主要数据文件(.mdf)+次要数据文件(.ndf)

只有一个可有多个记录数据库更新情况，扩展名为.ldf

当数据库破坏时可以用事务日志还原数据库内容北京林业大学软件教研室13文件组文件组（FileGroup）是将多个数据文件集合起来形成的一个整体主要文件组+次要文件组一个数据文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用日志文件不分组，它不能属于任何文件组北京林业大学软件教研室143.3.1SQLServer的系统数据库ModelMsdbTempdb系统默认数据库系统信息：磁盘空间；文件分配和使用；系统级的配置参数；登录账号信息；SQLServer初始化信息；系统中其他系统数据库和用户数据库的相关信息Model数据库存储了所有用户数据库和Tempdb数据库的创建模板通过更改Model数据库的设置可以大大简化数据库及其对象的创建设置工作存储计划信息以及与备份和还原相关的信息Tempdb数据库用作系统的临时存储空间

存储临时表,临时存储过程和全局变量值，创建临时表，存储用户利用游标说明所筛选出来的数据Master北京林业大学软件教研室153.3.2SQLServer的实例数据库重建实例数据库安装目录\MSSQL\Install中：Instpubs.sqlInstnwnd.sql实例数据库

pubs

Northwind

虚构的图书出版公司的基本情况

包含了一个公司的销售数据

北京林业大学软件教研室163.3.3创建用户数据库用EnterpriseManager创建数据库用SQL命令创建数据库

CREATEDATABASEdatabase_name[ON

[<filespec>[,...n]][,<filegroup>[,...n]]][LOGON{<filespec>[,...n]}][COLLATEcollation_name][FORLOAD|FORATTACH]北京林业大学软件教研室17[例3-1]用SQL命令创建一个教学数据库Teach，数据文件的逻辑名称为Teach_Data，数据文件物理地存放在D：盘的根目录下，文件名为TeachData.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为Teach_Log，日志文件物理地存放在D：盘的根目录下，文件名为TeachLog.ldf，初始存储空间大小为10MB，最大存储空间为25MB，存储空间自动增长量为5MB。CREATEDATABASETeachON( NAME=Teach_Data, FILENAME='D:\TeachData.mdf', SIZE=10, MAXSIZE=50, FILEGROWTH=5)LOGON( NAME=Teach_Log, FILENAME='D:\TeachLog.ldf', SIZE=5, MAXSIZE=25, FILEGROWTH=5)北京林业大学软件教研室183.3.4修改用户数据库用EnterpriseManager修改数据库用SQL命令修改数据库ALTERDATABASEdatabase_name{ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name[WITHDELETE]|ADDFILEGROUPfilegroup_name |REMOVEFILEGROUPfilegroup_name |MODIFYFILE<filespec> |MODIFYNAME=new_dbname |MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name} |SET<optionspec>[,...n][WITH<termination>] |COLLATE<collation_name>}北京林业大学软件教研室19[例3-2]修改Northwind数据库中的Northwind文件增容方式为一次增加2MB。ALTERDATABASENorthwindMODIFYFILE( NAME=Northwind, FILEGROWTH=2mb)北京林业大学软件教研室203.3.5删除用户数据库用EnterpriseManager删除数据库用SQL命令删除数据库DROPDATABASEdatabase_name[,...n] [例3-3]删除数据库Teach。 DROPDATABASETeach 北京林业大学软件教研室213.3.6查看数据库信息用EnterpriseManager查看数据库信息用系统存储过程显示数据库信息

用系统存储过程显示数据库结构用系统存储过程显示文件信息

用系统存储过程显示文件组信息Sp_helpdb[[@dbname=]'name']Sp_helpfile[[@filename=]'name']

Sp_helpfilegroup[[@filegroupname=]'name']北京林业大学软件教研室22EXECSp_helpdbNorthwindEXECSp_helpfileNorthwindEXECSp_helpfilegroup北京林业大学软件教研室233.4创建与使用数据表3.4.1数据类型整数数据

精确数值近似浮点数值日期时间数据bigint，int，smallint，tinyintnumeric和decimalfloat和realdatetime与smalldatetime北京林业大学软件教研室24字符串数据Unicode字符串数据二进制数据货币数据char、varchar、textnchar、nvarchar与ntextbinary、varbinary、imagemoney与smallmoney标记数据timestamp和uniqueidentifier北京林业大学软件教研室253.4.2创建数据表用EnterpriseManager创建数据表相关属性定义“字段名”“数据类型”字段的“长度”、“精度”和“小数位数”“允许空”“默认值”同一表中不许有重名字段系统默认为NULL北京林业大学软件教研室26用SQL命令创建数据表 CREATETABLE<表名> (<列定义>[{，<列定义>|<表约束>}])[例3-4]用SQL命令建立一个学生表S。 CREATETABLES ( SNoCHAR(6), SNVARCHAR(8), SexCHAR(2)DEFAULT'男', AgeINT, DeptVARCHAR(20))<列名><数据类型>[DEFAULT][{<列约束>}]

缺省值为“男”北京林业大学软件教研室273.4.3定义数据表的约束正确性有效性相容性数据的完整性约束（Constraint）默认（Default）规则（Rule）触发器（Trigger）存储过程（StoredProcedure）SQLServer的数据完整性机制北京林业大学软件教研室28完整性约束的基本语法格式[CONSTRAINT<约束名>]<约束类型>

NULL/NOTNULL

UNIQUE

PRIMARYKEY

FOREIGNKEY

CHECK北京林业大学软件教研室29NULL/NOTNULL约束NULL表示“不知道”、“不确定”或“没有数据”的意思主键列不允许出现空值 [CONSTRAINT<约束名>][NULL|NOTNULL][例3-5]建立一个S表，对SNo字段进行NOTNULL约束。 CREATETABLES ( SNoCHAR(6)CONSTRAINTS_ConsNOTNULL, SNVARCHAR(8), SexCHAR(2), AgeINT, DeptVARCHAR(20))可省略约束名称:SNoCHAR(6)NOTNULL北京林业大学软件教研室30UNIQUE约束（惟一约束）指明基本表在某一列或多个列的组合上的取值必须惟一在建立UNIQUE约束时，需要考虑以下几个因素：使用UNIQUE约束的字段允许为NULL值。一个表中可以允许有多个UNIQUE约束。可以把UNIQUE约束定义在多个字段上。UNIQUE约束用于强制在指定字段上创建一个UNIQUE索引，缺省为非聚集索引。UNIQUE用于定义列约束[CONSTRAINT<约束名>]UNIQUE

UNIQUE用于定义表约束[CONSTRAINT<约束名>]UNIQUE（<列名>[{,<列名>}]）北京林业大学软件教研室31[例3-6]建立一个S表，定义SN为惟一键。CREATETABLES( SNoCHAR(6),SNCHAR(8)CONSTRAINTSN_UniqUNIQUE,SexCHAR(2),AgeINT,DeptVARCHAR(20))[例3-7]建立一个S表，定义SN+SEX为惟一键，此约束为表约束。CREATETABLES( SNoCHAR(6),SNCHAR(8)UNIQUE,SexCHAR(2),AgeINT,DeptVARCHAR(20),CONSTRAINTS_UNIQUNIQUE(SN,Sex))SN_Uniq可以省略SNCHAR(8)UNIQUE北京林业大学软件教研室32

PRIMARYKEY约束（主键约束）用于定义基本表的主键，起惟一标识作用PRIMARYKEY与UNIQUE的区别：一个基本表中只能有一个PRIMARYKEY，但可多个UNIQUE对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL不能为NULL不能重复北京林业大学软件教研室33PRIMARYKEY用于定义列约束CONSTRAINT<约束名>PRIMARYKEYPRIMARYKEY用于定义表约束[CONSTRAINT<约束名>]PRIMARYKEY(<列名>[{,<列名>}])[例3-8]建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键。CREATETABLES( SNoCHAR(6)CONSTRAINTS_PrimPRIMARYKEY,SNCHAR(8),SexCHAR(2),AgeINT,DeptVARCHAR(20))CREATETABLEC( CNoCHAR(5)CONSTRAINTC_PrimPRIMARYKEY,CNCHAR(20),CTINT)北京林业大学软件教研室34[例3-9]建立一个SC表，定义SNo+CNo为SC的主键。CREATETABLESC( SNoCHAR(5)NOTNULL,CNoCHAR(5)NOTNULL,ScoreNUMERIC(4,1),CONSTRAINTSC_PrimPRIMARYKEY(SNo,CNo))北京林业大学软件教研室35

FOREIGNKEY约束（外键约束）[CONSTRAINT<约束名>]FOREIGNKEYREFERENCES<主表名>(<列名>[{,<列名>}])外部键从表主键主表引用

北京林业大学软件教研室36[例3-10]建立一个SC表，定义SNo,CNo为SC的外部键。CREATETABLESC( SNoCHAR(5)NOTNULLCONSTRAINTS_ForeFOREIGNKEYREFERENCESS(SNo),CNoCHAR(5)NOTNULLCONSTRAINTC_ForeFOREIGNKEYREFERENCESC(CNo),ScoreNUMERIC(4,1),CONSTRAINTS_C_PrimPRIMARYKEY(SNo,CNo));北京林业大学软件教研室37CHECK约束CHECK约束用来检查字段值所允许的范围在建立CHECK约束时，需要考虑以下几个因素：一个表中可以定义多个CHECK约束。每个字段只能定义一个CHECK约束。在多个字段上定义的CHECK约束必须为表约束。当执行INSERT、UNDATE语句时CHECK约束将验证数据。

[CONSTRAINT<约束名>]CHECK(<条件>)北京林业大学软件教研室38[例3-11]建立一个SC表，定义Score的取值范围为0～100之间。 CREATETABLESC ( SNoCHAR(5), CNoCHAR(5), ScoreNUMERIC(4,1)CONSTRAINTScore_ChkCHECK(Score>=0ANDScore<=100))[例3-12]建立包含完整性定义的学生表。 CREATETABLES ( SNoCHAR(6)CONSTRAINTS_PrimPRIMARYKEY, SNCHAR(8)CONSTRAINTSN_ConsNOTNULL, SexCHAR(2)DEFAULT'男', AgeINTCONSTRAINTAge_ConsNOTNULL CONSTRAINTAge_ChkCHECK(AgeBETWEEN15AND50), DeptCHAR(10)CONSTRAINTDept_ConsNOTNULL)北京林业大学软件教研室393.4.4修改数据表用EnterpriseManager修改数据表的结构用SQL命令修改数据表ALTERTABLE<表名>ADD<列定义>|<完整性约束定义>ALTERTABLE<表名>ALTERCOLUMN<列名><数据类型>[NULL|NOTNULL]ALTERTABLE<表名>DROPCONSTRAINT<约束名>北京林业大学软件教研室40[例3-13]在S表中增加一个班号列和住址列。ALTERTABLESADDClass_NoCHAR(6),AddressCHAR(40)使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOTNULL约束。[例3-14]在SC表中增加完整性约束定义，使Score在0～100之间。ALTERTABLESCADDCONSTRAINTScore_ChkCHECK(ScoreBETWEEN0AND100)北京林业大学软件教研室41[例3-15]把S表中的SN列加宽到10个字符。 ALTERTABLES ALTERCOLUMN SNCHAR(10)不能改变列名；不能将含有空值的列的定义修改为NOTNULL约束；若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型；只能修改NULL/NOTNULL约束，其他类型的约束在修改之前必须先将约束删除，然后再重新添加修改过的约束定义。[例3-16]删除S表中的主键。 ALTERTABLES DROPCONSTRAINTS_Prim

北京林业大学软件教研室423.4.5删除基本表用EnterpriseManager删除数据表用SQL命令删除数据表DROPTABLE<表名>只能删除自己建立的表，不能删除其他用户所建的表北京林业大学软件教研室433.4.6查看数据表查看数据表的属性 属性包括：数据表的名称，所有者，创建日期，文件组，记录的行数，数据表中的字段名称、结构和类型等。查看数据表中的数据 在EnterpriseManager中，用右键单击要查看数据的表，从快捷菜单中选择“打开表”，再选择其子菜单中的“返回所有行”。北京林业大学软件教研室443.5创建与使用索引3.5.1索引的作用3.5.2索引的分类加快查询速度保证行的惟一性聚集索引与非聚集索引唯一索引复合索引聚集索引：查询速度快非聚集索引：更新速度快排列的结果存储在表中只有一个排列的结果不存储在表中可以有多个有UNIQUE，自动建立非聚集的惟一索引有PRIMARYKEY，自动建立聚集索引将两个或多个字段组合起来建立的索引，单独的字段允许有重复的值北京林业大学软件教研室453.5.3创建索引用EnterpriseManager创建索引用索引创建向导创建索引直接创建索引用SQL命令创建索引 CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][{,<列名>}][次序]…)建立惟一索引建立聚集索引ASC或DESC，默认为ASC北京林业大学软件教研室46[例3-18]为表SC在SNo和CNo上建立惟一索引。CREATEUNIQUEINDEXSCIONSC(SNo,CNo)[例3-19]为教师表T在TN上建立聚集索引。CREATECLUSTERINDEXTIONT(TN)注意：（1）改变表中的数据（如增加或删除记录）时，索引将自动更新。（2）索引建立后，在查询使用该列时，系统将自动使用索引进行查询。（3）索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。北京林业大学软件教研室473.5.4查看与修改索引用EnterpriseManager查看和修改索引用Sp_helpindex存储过程查看索引 Sp_helpindex[@objname=]'name'[例3-20]查看表SC的索引。 EXECSp_helpindexSC表的名称北京林业大学软件教研室48用Sp_rename存储过程更改索引名称 Sp_rename'数据表名.原索引名','原索引名'[例3-21]更改T表中的索引TI名称为T_Index。EXECSp_rename'T.TI','T_Index','index'北京林业大学软件教研室493.5.5删除索引用EnterpriseManager删除索引用DROPINDEX命令删除索引 DROPINDEX数据表名.索引名 [例3-22]删除表SC的索引SCI。 DROPINDEXSC.SCI不能删除由CREATE或ALTER命令创建的索引，也不能删除系统表中的索引北京林业大学软件教研室503.6数据查询3.6.1SELECT命令的格式与基本使用

SELECT[ALL|DISTINCT][TOPN[PERCENT][WITHTIES]] 〈列名〉[AS别名1][{，〈列名〉[AS别名2]}] [INTO新表名]

FROM〈表名1或视图名1〉[[AS]表1别名][{，〈表名2或视图名2〉[[AS]表2别名]}] [WHERE〈检索条件〉] [GROUPBY<列名1>[HAVING<条件表达式>]] [ORDERBY<列名2>[ASC|DESC]]投影选取北京林业大学软件教研室51[例3-23]查询全体学生的学号、姓名和年龄。SELECTSNo,SN,AgeFROMS[例3-24]查询学生的全部信息。SELECT*FROMS[例3-25]查询选修了课程的学生号。SELECTDISTINCTSNoFROMSC[例3-26]查询全体学生的姓名、学号和年龄。SELECTSNName,SNo,AgeFROMSSELECTSNASName,SNo,Age北京林业大学软件教研室523.6.2条件查询运算符含义=,>,<,>=,<=,!=,<>比较大小AND,OR,NOT多重条件BETWEENAND确定范围IN确定集合LIKE字符匹配ISNULL空值常用的比较运算符：

北京林业大学软件教研室53比较大小[例3-27]查询选修课程号为‘C1’的学生的学号和成绩SELECTSNo,ScoreFROMSCWHERECNo='C1'[例3-28]查询成绩高于85分的学生的学号、课程号和成绩。SELECTSNo,CNo,ScoreFROMSCWHEREScore>85北京林业大学软件教研室54多重条件查询 NOT、AND、OR 用户可以使用括号改变优先级 [例3-29]查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。SELECTSNo,CNo,ScoreFROMSCWHERE(CNo='C1'ORCNo='C2')AND(Score>=85)

高低北京林业大学软件教研室55确定范围[例3-30]查询工资在1000至1500元之间的教师的教师号、姓名及职称。SELECTTNo,TN,ProfFROMTWHERESalBETWEEN1000AND1500[例3-31]查询工资不在1000至1500之间的教师的教师号、姓名及职称。SELECTTNo,TN,ProfFROMTWHERESalNOTBETWEEN1000AND1500WHERESal>=1000ANDSal<=1500北京林业大学软件教研室56确定集合利用“IN”操作可以查询属性值属于指定集合的元组。[例3-32]查询选修C1或C2的学生的学号、课程号和成绩。 SELECTSNo,CNo,Score FROMSC WHERECNoIN('C1'，'C2')利用“NOTIN”可以查询指定集合外的元组。[例3-33]查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。 SELECTSNo,CNo,Score FROMSC WHERECNoNOTIN('C1'，'C2')北京林业大学软件教研室57部分匹配查询当不知道完全精确的值时，用户可以使用LIKE或NOTLIKE进行部分匹配查询（也称模糊查询）<属性名>LIKE<字符串常量>

[例3-34]查询所有姓张的教师的教师号和姓名。SELECTTNo,TNFROMTWHERETNLIKE'张%'[例3-35]查询姓名中第二个汉字是“力”的教师号和姓名。SELECTTNo,TNFROMTWHERETNLIKE'_力%'北京林业大学软件教研室58空值查询某个字段没有值称之为具有空值（NULL）空值不同于零和空格，它不占任何存储空间[例3-36]查询没有考试成绩的学生的学号和相应的课程号。SELECTSNo,CNoFROMSCWHEREScoreISNULL北京林业大学软件教研室593.6.3常用库函数及统计汇总查询函数名称功能AVG按列计算平均值SUM按列计算值的总和MAX求一列中的最大值MIN求一列中的最小值COUNT按列值计个数北京林业大学软件教研室60[例3-37]求学号为S1学生的总分和平均分。 SELECTSUM(Score)ASTotalScore,AVG(Score)ASAveScore FROMSC WHERE(SNo='S1')[例3-38]求选修C1号课程的最高分、最低分及之间相差的分数。 SELECTMAX(Score)ASMaxScore,MIN(Score)ASMinScore,MAX(Score)－MIN(Score)ASDiff FROMSC WHERE(CNo='C1')[例3-40]求学校中共有多少个系。 SELECTCOUNT(DISTINCTDept)ASDeptNum FROMSDISTINCT消去重复行北京林业大学软件教研室61[例3-41]统计有成绩同学的人数。 SELECTCOUNT(Score) FROMSC成绩为零的同学他计算在内，没有成绩（即为空值）的不计算。[例3-42]利用特殊函数COUNT(*)求计算机系学生的总数。 SELECTCOUNT(*)FROMS WHEREDept='计算机'COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字。北京林业大学软件教研室623.6.4分组查询GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。[例3-43]查询各个教师的教师号及其任课的门数。SELECTTNo,COUNT(*)ASC_NumFROMTCGROUPBYTNoGROUPBY子句按TNo的值分组，所有具有相同TNo的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。北京林业大学软件教研室63若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句[例3-44]查询选修两门以上课程的学生的学号和选课门数。SELECTSNo,COUNT(*)ASSC_NumFROMSCGROUPBYSNoHAVING(COUNT(*)>=2)GROUPBY子句按SNo的值分组，所有具有相同SNo的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。HAVING子句去掉不满足COUNT（*）>=2的组北京林业大学软件教研室643.3.5查询的排序当需要对查询结果排序时，应该使用ORDERBY子句，ORDERBY子句必须出现在其他子句之后。排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。[例3-45]查询选修C1的学生学号和成绩，并按成绩降序排列。SELECTSNo,ScoreFROMSCWHERE(CNo='C1')ORDERBYScoreDESC北京林业大学软件教研室65[例3-46]查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。SELECTSNo,CNo,ScoreFROMSCWHERE(CNoIN('C2','C3','C4','C5'))ORDERBYSNo,ScoreDESC北京林业大学软件教研室66[例3-47]求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。SELECTSNo,SUM(Score)ASTotalScoreFROMSCWHERE(Score>=60)GROUPBYSNoHAVING(COUNT(*)>=3)ORDERBYSUM(Score)DESC取出整个SC筛选Score>=60的元组将选出的元组按SNo分组筛选选课三门以上的分组将选取结果排序在剩下的组中提取学号和总成绩ORDERBY2DESC;“2”代表查询结果的第二列北京林业大学软件教研室673.6.6数据表连接及连接查询连接查询：一个查询需要对多个表进行操作表之间的连接：连接查询的结果集或结果表连接字段：数据表之间的联系是通过表的字段值来体现的连接操作的目的：从多个表中查询数据表的连接方法:

表之间满足一定条件的行进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件利用关键字JOIN进行连接：当将JOIN关键词放于FROM子句中时，应有关键词ON与之对应，以表明连接的条件北京林业大学软件教研室68INNERJOIN显示符合条件的记录，此为默认值LEFT（OUTER）JOIN为左（外）连接，用于显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示RIGHT（OUTER）JOIN右（外）连接，用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示FULL（OUTER）JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示CROSSJOIN将一个表的每一个记录和另一表的每个记录匹配成新的数据行JION的分类北京林业大学软件教研室69等值连接与非等值连接[例3-48]查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。方法1：SELECTT.TNo,TN,CNoFROMT,TCWHERE(T.TNo=TC.TNo)AND(TN='刘伟')方法2：SELECTT.TNo,TN,CNoFROMTINNERJOINTCONT.TNo=TC.TNoWHERE(TN='刘伟')连接条件，当比较运算符为“＝”时，称为等值连接。其他情况为非等值连接。引用列名TNo时要加上表名前缀，这是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。北京林业大学软件教研室70[例3-49]查询所有选课学生的学号、姓名、选课名称及成绩。SELECTS.SNo,SN,CN,ScoreFROMS,C,SCWHERES.SNo=SC.SNoANDSC.CNo=C.CNo[例3-50]查询每门课程的课程名、任课教师姓名及其职务、选课人数。SELECTCN,TN,Prof,COUNT(SC.SNo)FROMC,T,TC,SCWHERET.TNo=TC.TNoANDC.CNo=TC.CNoANDSC.CNo=C.CNoGROUPBYSC.CNo北京林业大学软件教研室71自身连接[例3-51]查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。方法1：SELECTX.TN,X.SalASSal_a,Y.SalASSal_bFROMTASX,TASYWHEREX.Sal>Y.SalANDY.TN='刘伟'方法2：SELECTX.TN,X.Sal,Y.SalFROMTASXINNERJOINTASYONX.Sal>Y.SalANDY.TN='刘伟'方法3：SELECTR1.TN,R1.Sal,R2.SalFROM(SELECTTN,SalFROMS)ASR1INNERJOIN(SELECTSalFROMTWHERETN='刘伟')ASR2ONR1.Sal>R2.Sal北京林业大学软件教研室72[例3-52]检索所有学生姓名，年龄和选课名称。方法1：SELECTSN,Age,CNFROMS,C,SCWHERES.SNo=SC.SNoANDSC.CNo=C.CNo方法2：SELECTR3.SNo,R3.SN,R3.Age,R4.CNFROM(SELECTSNo,SN,AgeFROMS)ASR3INNERJOIN(SELECTR2.SNo,R1.CNFROM(SELECTCNo,CNFROMC)ASR1INNERJOIN(SELECTSNo,CNoFROMSC)ASR2ONR1.CNo=R2.CNo)ASR4ONR3.SNo=R4.SNo北京林业大学软件教研室73外连接 而在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。[例3-53]查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。SELECTS.SNo,SN,CN,ScoreFROMSLEFTOUTERJOINSCONS.SNo=SC.SNoLEFTOUTERJOINCONC.CNo=SC.CNo

左外部连接右外部连接北京林业大学软件教研室743.6.7子查询在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询。返回一个值的子查询[例3-54]查询与“刘伟”老师职称相同的教师号、姓名 SELECTTNo,TN FROMT WHEREProf=( SELECTProf FROMT WHERETN='刘伟')

使用比较运算符（=,>,<,>=,<=,!=）北京林业大学软件教研室75返回一组值的子查询使用ANY[例3-55]查询讲授课程号为C5的教师姓名。SELECTTNFROMTWHERE(TNo=ANY(SELECTTNo FROMTC WHERECNo='C5'))使用ANY或ALLSELECTTNFROMT,TCWHERET.TNo=TC.TNoANDTC.CNo='C5'IN北京林业大学软件教研室76返回一组值的子查询使用IN[补充例题]查询选修了001号课程和002号课程的学生的学号SELECTSNOFROMSCWHERECNO='001'ANDSNOIN(SELECTSNOFROMSCWHERECNO='002‘)关键：一列不可能同时取两个值北京林业大学软件教研室77[例3-56]查询其他系中比计算机系某一教师工资高的教师的姓名和工资。SELECTTN,SalFROMTWHERE(Sal>ANY( SELECTSal FROMT WHEREDept='计算机'))AND(Dept<>'计算机')SELECTTN,SalFROMTWHERESal>( SELECTMIN(Sal) FROMT WHEREDept='计算机')ANDDept<>'计算机'北京林业大学软件教研室78使用ALL[例3-58]查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。 SELECTTN,Sal FROMT WHERE(Sal>ALL( SELECTSal FROMT WHEREDept='计算机')) AND(Dept<>'计算机')[例3-59]查询不讲授课程号为C5的教师姓名。 SELECTDISTINCTTN FROMT WHERE('C5'<>ALL( SELECTCNo FROMTC WHERETNo=T.TNo))Sal>( SELECTMAX(Sal)NOTIN北京林业大学软件教研室79使用EXISTS带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOTEXISTS与此相反。含有IN的查询通常可用EXISTS表示，但反过来不一定。[例3-62]查询选修所有课程的学生姓名。SELECTSNFROMSWHERE(NOTEXISTS(SELECT* FROMCWHERENOTEXISTS(SELECT* FROMSC WHERESNo=S.SNoANDCNo=C.CNo)))

北京林业大学软件教研室803.6.8合并查询合并查询就是使用UNION操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。参加合并查询的各子查询的使用的表结构应该相同。[例3-63]从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。 SELECTSNoAS学分,SUM(Score)AS总分 FROMSC WHERE(SNo='S1') GROUPBYSNo UNION SELECTSNoAS学分,SUM(Score)AS总分 FROMSC WHERE(SNo='S5') GROUPBYSNo

北京林业大学软件教研室813.6.9存储查询结果到表中使用SELECT…INTO语句可以将查询结果存储到一个新建的数据库表或临时表中。[例3-64]从SC数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表cal_table中。 SELECTSNoAS学分,SUM(Score)AS总分 INTOCal_Table FROMSC GROUPBYSNo北京林业大学软件教研室823.7数据更新3.7.1添加数据用EnterpriseManager添加数据不能应付数据的大量添加用SQL命令添加数据数据更新添加数据（INSERTINTO）修改数据（UPDATE）删除数据（DELETE）

INSERTINTO北京林业大学软件教研室83用SQL命令添加数据添加一行新记录INSERTINTO<表名>[(<列名1>[,<列名2>…])]VALUES(<值>)[例3-65]在S表中添加一条学生记录（学号：S7、姓名：郑冬、性别：女、年龄：21、系别：计算机）。 INSERTINTOS(SNo,SN,Age,Sex,Dept) VALUES('S7','郑冬',21,'女','计算机')必须用逗号将各个数据分开，字符型数据要用单引号括起来。如果INTO子句中没有指定列名，则新添加的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。北京林业大学软件教研室84添加一行记录的部分数据值[例3-66]在SC表中添加一条选课记录('S7','C1')。INSERTINTOSC(SNo,CNo)VALUES('S7','C1')添加多行记录INSERTINTO<表名>[(<列名1>[,<列名2>…])]子查询北京林业大学软件教研室85[例3-67]求出各系教师的平均工资，把结果存放在新表AvgSal中。首先建立新表AvgSal，用来存放系名和各系的平均工资 CREATETABLEAvgSal ( DepartmentVARCHAR(20), AverageSMALLINT)求出T表中各系的平均工资，把结果存放在新表AvgSal中 INSERTINTOAvgSal SELECTDept,AVG(Sal) FROMT GROUPBYDept

北京林业大学软件教研室863.7.2修改数据用EnterpriseManager修改数据不能应付数据的大量修改用SQL命令修改数据 UPDATE<表名> SET<列名>=<表达式>[,<列名>=<表达式>]… [WHERE<条件>]

UPDATE北京林业大学软件教研室87[例3-68]把刘伟老师转到信息系UPDATETSETDept='信息'WHERESN='刘伟[例3-69]将所有学生的年龄增加1岁UPDATESSETAge=Age+1修改多行修改一行[例3-71]把讲授C5课程的教师的岗位津贴增加100元。UPDATETSETComm=Comm+100WHERE(TNoIN(SELECTTNo FROMT,TC WHERET.TNo=TC.TNoANDTC.CNo='C5'))用子查询选择要修改的行用子查询提供要修改的值[例3-72]把所有教师的工资提高到平均工资的1.2倍。UPDATETSETSal=(SELECT1.2*AVG(Sal) FROMT)北京林业大学软件教研室883.7.3删除数据用EnterpriseManager删除数据比较适合于少量的单