关系数据库标准语言SQL.ppt

第3章关系数据库标准语言SQL,3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.7小结,3.1SQL概述,3.1.1SQL的产生与发展3.1.2SQL的特点3.1.3SQl的基本概念,3.1SQL概述,SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言几乎所有的关系数据库管理系统软件都支持SQL，许多软件厂商对SQL基本命令集进行了不同程度的扩充和修改SQL已成为数据库领域中的主流语言,3.1.1SQL的产生于发展,SQL语言最早称为Sequel，是Boyce和Chamberlin1974年提出的。Sequel不断发展，并更名为SQL由于SQL，功能丰富，语言简洁，使用灵活，倍受用户和业界欢迎，被众多计算机公司和软件公司所采用。1974年由Boyce和Chamberlin提出，在IBM公司的SystemR上实现。1986年被美国国家标准局（ANSI）批准为关系数据库语言的美国标准。1987年国际标准化组织（ISO）通过这一标准自SQL-92以来，SQL标准的规模开始变大（SQL-89标准大约120页，SQL-92标准超过620页，而SQL-99标准多达1700页）。目前，大多数商品化DBMS支持SQL-92主要部分（初级标准和部分中、高级的标准），并在其他方面有一些扩展。SQL-99扩充太快，过于庞大，DBMS开发商对实现SQL-99似乎不太积极。本书关于SQL的介绍主要基于SQL-92,3.1.1SQL的产生与发展（续）,标准大致页数发布日期SQL/861986.10SQL/89(FIPS127-1)120页1989年SQL/92622页1992年SQL991700页1999年SQL20033600页2003年从内容上看，页数逐渐增多，从单个文档扩充到多个文档从功能上看，从SQL92开始，已经扩展了一系列开放部分，如SQL调用接口，SQL永久存储模块；从SQL99开始扩展为框架、SQL基础部分、SQL调用接口、SQL永久存储模块，SQL宿主语言绑定，SQL外部数据的管理和SQL对象语言的绑定等,3.1.2SQL的特点,一、综合统一二、高度非过程化三、面向集合的操作方式四、以同一种语法结构提供多种使用方式五、语言简洁，易学易用,一、综合统一,数据库系统的主要功能是通过数据库支持的数据语言来实现的非关系模型（层次模型、网状模型）的数据语言一般分为：模式数据定义语言（SchemaDataDefinitionLanguage，模式DDL）外模式数据定义语言（SubschemaDataDefinitionLanguage，外模式DDL或者子模式DDL）数据存储有关的描述语言（DataManipulationLanguage，DML）数据操纵语言（DataManipulationLanguage，DML）定义模式、外模式、内模式和进行数据的存取与处置用户数据库投入运行后，如果需要修改模式，必须停止现有数据库的运行，转储数据，修改模式并编译后再重装数据库，十分麻烦,一、综合统一（续）,SQL级数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言的风格统一，可以独立完成数据库生命周期中的全部活动定义关系模式，插入数据，建立数据库对数据库中的数据进行查询和更新数据库重构和维护数据库安全性、完整性控制为数据库应用系统的开发提供良好的环境用户数据库投入使用后可以进行相应的修改模式，具有良好的可扩展性关系模型中实体和实体间的联系都为关系，这种数据结构的单一性带来了数据操作符的统一性SQL中插入仅用INSERT即可DBTG中插入分为STORE和CONNECT两种,二、高度非过程化,非关系数据模型的数据操纵语言是“面向过程”的语言，用“过程化”语言完成某项请求，必须指定存取路径SQL进行数据操作，只需要提出“做什么”即可，无需指明“如何做”，即无需了解存取路径，具体的存取路径由系统自动完成，从而极大地减轻了用户的负担，而且有利于提高数据的独立性,三、面向集合的操作方式,非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录例如：要查找平均成绩在80分以上的学生姓名，用户必须一条一套地把满足条件的学生记录找出来（需要说明具体的处理过程，即按照哪条路径，如何循环等）SQL采用集合的操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元祖的集合,四、以同一种语法结构提供多种实用方式,SQL是独立的语言能够独立地用于联机交互的实用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作SQL是嵌入式语言能够嵌入到高级语言（C,C+，Java）程序中，供程序员设计程序时实用提供了极大地灵活性与方便性,五、语言简洁，易学易用,SQL功能强大，设计巧妙，完成核心功能仅用9个动词,表3.1SQL的动词,3.1.3SQL的基本概念,SQL支持关系数据库的三级模式基本表本身独立存在的表。一个关系就对应一个基本表。（模式）存储文件一个（或多个）基本表对应一个存储文件，一个表可带若干索引。索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构是任意的，对用户是透明的。（内模式）视图从一个或几个基本表导出的表。是一个虚表，数据库中仅存视图定义，不存放视图对应的数据。并且用户可以在视图上再定义视图。（外模式）,外模式,模式,内模式,存储文件1,存储文件2,基本表1,基本表2,基本表3,基本表4,视图1,视图2,SQL,图3.1SQL对关系数据库模式的支持,3.2学生课程数据库,3.3数据定义,符号约定表示X是需要进一步定义或说明语言成分。X表示X可以缺省或出现一次。X表示X可以出现一次。X|Y表示或者X出现，或者Y出现，但二者不能同时出现。SQL语言的保留字（如CREATE）不区分大小写。为醒目起见，对于SQL语句中的SQL的保留字，我们使用大写。SQL语句用分号结束。一个SQL语句可以写在一行或多行中，各种空白符号用于分隔不同的词。良好的语句的书写风格使得程序赏心悦目、易于阅读。,3.3数据定义（续）,3.3数据定义（续）,3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除,3.3.1模式的定义与删除,一、定义模式CREATESCHEMAAUTHORIZATION如果没有指定，那么隐含为要创建模式，调用该命令的应用必须拥有DBA权限，或者获得了DBA授予的创建模式的权限,3.3.1模式的定义与删除（续）,例1定义一个学生课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户WANG定义了一个模式S-T例2CREATESCHEMAAUTHORIZATIONWANG;该语句没有指定，所以隐含为用户名WANG说明：定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等,3.3.1模式的定义与删除（续）,CREATESCHEMAAUTHORIZATION|例3CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT,COL2INT,COL3CHAR(20),COL4NUMERIC(10,3),COL5DECIMAL(5,2);该语句为用户ZHANG创建了一个模式TEST，并且在其中定义了一个表TAB1,3.3.1模式的定义与删除（续）,二、删除模式DROPSCHEMA其中CASCADE和RESTRICT，两者必选其一CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部一起删除RESTRICT（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行，只有当该模式中没有任何下属的对象时才能执行DROPSCHEMA语句例4：DROPSCHEMATESTCASCADE;该语句删除了模式TEST。同时，该模式中已经定义的表TAB1也被删除了,3.3数据定（续),3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除,3.3.2基本表的定义、删除与修改,创建一个基本表要对基本表命名，定义表的每个列，并定义表的完整性约束条件。一、定义基本表CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件,一、定义基本表,定义每个属性（列）的名称、类型、缺省值和列上的约束条件，格式如下：DEFAULT,其中，是标识符，对定义的列命名；定义列的取值类型，它可以是之后介绍的任意类型，也可以是用户定义的域类型；可选短语“DEFAULT”定义列上的缺省值，是中的一个特定值或NULL（空值）；每个列上可以定义零个或或多个约束条件，约束列的取值,一、定义基本表（续）,列约束定义格式如下CONSTRAINT其中可选短语“CONSTRAINT”为列约束命名常用的列约束包括：NOTNULL：不允许该列取空值不加NOTNULL限制时，该列可以取空值。PRIMARYKEY：指明该列是主码，其值非空、惟一。UNIQUE：该列上的值必须惟一相当于说明该列为候选码。CHECK()：指明该列的值必须满足的条件，其中是一个涉及该列的布尔表达式,一、定义基本表（续）,一个表可以包含零个或多个，用于定义主码、其他候选码、外码和表上的其它约束。表约束定义定义形式如下：CONSTRAINT其中可选短语“CONSTRAINT”为表约束命名PRIMARYKEY(A1,Ak)：说明属性列A1,Ak构成该关系的主码。当主码只包含一个属性时，也可以用列约束定义主码。UNIQUE(A1,Ak)：说明属性列A1,Ak上的值必须惟一相当于说明A1,Ak构成该关系的候选码当候选码只包含一个属性时，也可以用列约束定义候选码。CHECK()：说明该表上的一个完整性约束条件。通常，是一个涉及该表一个或多个列的布尔表达式,一、定义基本表（续）,外码比较复杂，它具有下形式：FOREIGNKEY(A1,Ak)REFERENCES()属性A1,Ak是关系（表）的外码给出被参照关系的表名给出被参照关系的主码说明违反参照完整性时需要采取的措施,一、定义基本表（续）,例下面的语句创建教师表TeachersCREATETABLETeachers(TnoCHAR(7)PRIMARYKEY,TnameCHAR(10)NOTNULL,SexCHAR(2)CHECK(Sex=男ORSex=女),BirthdayDATE,TitleCHAR(6),DnoCHAR(4),FOREIGNKEY(Dno)REFERENCESDepartments(Dno);,一、定义基本表（续）,例5建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，且为主码，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)；系统执行上面的语句后，在数据库中建立一个新的空的“学生”表Student，并将有关“学生”表的定义及有关约束条件存放在数据字典中,一、定义基本表（续）,例6建立一个“课程”表Course。CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEYCpnoREFERENCESCourse(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/);说明：参照表和被参照表可以是同一个表,一、定义基本表（续）,例7建立学生选课表SC。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT),PRIMARYKEY(Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY（Sno）REFERENCESStudent(Sno)/*表级完整性约束条件，Sno是外码，被参照表是Student*/FOREIGNKEY（Cno）REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);,二、数据类型,SQL支持的数据类型SQL支持许多内置的数据类型允许用户定义新的域（数据）类型。SQL支持的数据类型包括CHARACTER(n)：长度为n的定长字符串CHARACTERVARYING(n)：最大长度为n的变长字符串BIT（n）：长度为n的二进制位串BITVARYING(n)：最大长度为n的变长二进制位串INTEGER：长度为4BSMALLINT:长度为2B,二、数据类型（续）,SQL支持的数据类型NUMERIC(p,d):定点数，p位数字，小数点后d位DECIMAL:（同上）FLOAT(n):浮点数，精度至少n位数字REAL：浮点数DOUBLEPRECISION：双精度浮点数DATE：日期YYYY-MM-DDTIME：时间HH：MM：SSTIMESTAMP：时标，即日期时间INTERVAL：时间间隔,二、数据类型（续）,CHAR(n)：定长字符串，长度n由用户指定。省略(n)时，长度为1。CHAR的全称是CHARACTER。VARCHAR(n)：变长字符串，最大长度n由用户指定。VARCHAR的全称是CHARACTERVARYING定长和变长字符串的差别主要表现在前者需要固定长度的空间，而后者占用的空间在最大长度范围内是可改变的。BIT(n)：定长二进位串，长度n由用户指定。省略(n)时，长度为1。BITVARYING(n)：变长二进位串，最大长度n由用户指定。INT：整数，其值域依赖于具体实现。INT的全称是INTEGER。SMALLINT：小整数，其值域依赖于具体实现，但小于INT的值域,二、数据类型（续）,NUMERIC(p,d)：p位有效数字的定点数，其中小数点右边占d位。DEC(p,d)：p位有效数字的定点数，其中小数点右边占d位。DEC的全称是DECIMAL。FLOAT(n)：精度至少为n位数字的浮点数，其值域依赖于实现。REAL：实数，精度依赖于实现。DOUBLEPRECISION：双精度实数，精度依赖于实现，但精度比REAL高。DATE：日期，包括年、月、日，格式为YYYY-MM-DD。TIME：时间，包括时、分、秒，格式为HH:MM:SS。TIME(n)可以表示比秒更小的单位，秒后取n位。TIMESTAMP：时间戳，是DATE和TIME的结合INTERVAL：时间间隔。SQL允许对DATE、TIME和INTERVAL类型的值进行计算。,二、数据类型（续）,SQL提供ETRACT(fieldFROMVar)，从DATE、TIME和TIMESTAMP类型变量Var从提取字段field。对于DATE类型的变量，field可以是YEAR、MONTH和DAY；对于TIME类型的变量，field可以是HOUR、MINUTE和SECOND；而对于TIMESTAMP类型field可以是YEAR、MONTH、DAY、HOUR、MINUTE和SECOND例如，如果d是DATE类型，则ETRACT(YEARFROMd)返回d中的年份。,三、模式与表,每一个基本表都属于某一个模式，一个模式包含多个基本表，定义模式的方法有三种：方法一：在表名中明显地给出模式名Createtable“S-T”.Student();Createtable“S-T”.Course();Createtable“S-T”.SC();方法二：在创建模式语句中同时创建表方法三：设置所属的模式，这样在创建表时表名中不必给出模式名,三、模式与表（续）,当用户创建基本表时若没有指定模式，系统根据搜索路径来确定该对象所属的模式。搜索路径包含一组模式列表，RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出错误。使用下面的语句可以显示当前的搜索路径，例如：SHOWsearch_path搜索路径的当前默认值是：$user,PUBLIC其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式,四、修改基本表,基本表创建好以后，在某些情况下需要修改它的结构。SQL允许添加列定义修改或删除列的缺省值删除列添加表约束删除表约束。使用ALTERTABLE语句修改基本表,四、修改基本表（续）,1.向基本表添加新的列ALTERTABLEADDCOLUMNCOLUMN可以省略（下同）和创建基本表相同，但是新添加的列一般不允许用NOTNULL说明。2.对于已经存在的列，SQL-92只允许修改或删除列的缺省值，语句形式为：ALTERTABLEALTERCOLUMNSETDEFAULT|DROPDEFAULTSETDEFAULT以新的缺省值替换原来的缺省值DROPDEFAULT删除缺省值,四、修改基本表（续）,3.删除已存在的列ALTERTABLEDROPCOLUMNCASCADE|RESTRICTCASCADE表示级联，删除将成功，并且依赖于该列的数据库对象（如涉及该列的视图、约束）也一并删除RESTRICT表示受限，仅当没有依赖于该列的数据库对象时删除才能成功4.添加表约束ALTERTABLEADD其中与创建基本表相同,四、修改基本表（续）,5.删除表约束ALTERTABLEDROPCONSTRAINTCASCADE|RESTRICT其中被删除的约束一定是命名的约束，给出约束名CASCADE导致删除约束并且同时删除依赖于该约束的数据库对象RESTRICT仅当不存在依赖于该约束的数据库对象时才删除该约束。,四、修改基本表（续）,例8向Student表增加“入学时间”列，其数据类型为日期型ALTERTABLEStudentADDScomeDATE;注：不论基本表中原来是否已有数据，新增加的列一律为空值例9将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYCOLUMNSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据例10删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；,五、删除基本表,DROPTABLERESTRICT|CASCADE;基本表删除，表中的数据、表上的索引都删除表上的视图往往仍然保留，但无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述RESTRICT：表的删除是有限制条件，如果基于该表定义有视图，或者有其他表引用该表（如CHECK、FOREIGNKEY等约束），或者该表有触发器、存储过程或函数等，则不能删除CASCADE：表的删除没有限制条件缺省情况是RESTRICT,五、删除基本表（续）,11删除Student表DROPTABLEStudentCASCADE；基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、视图、触发器等有关对象一般也都将被删除,五、删除基本表（续）,例12若表上建有视图，选择RESTRICT时表不能删除；CASCADE时可以删除表，视图也会自动被删除。CREATEVIEWIS_StudentASSELECTSnow，Sname,SageFromStudentWHERESdept=IS;DROPTABLEStudentRESTRICT;/*删除Student表*/-ERROR:cannotdroptableStudentbecauseotherobjectsdependonit/*系统返回错误信息，存在依赖该表的对象，此表不能被删除*/DROPTABLEStudentCASCADE;/*删除表*/-NORICE:dropcascadestoviewIS_Student/*系统返回提示，此表上的视图也被删除*/SELECT*FROMIS_Student;-ERROR:relationIS_Studentdoesnotexist,3.3数据定义（续),3.3.1模式的定义与删除3.3.2基本表的定义、删除与修改3.3.3索引的建立与删除,3.3.3索引的建立与删除,建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引索引一经建立，就由系统使用和维护它，不需用户干预,一、建立索引,语句格式CREATEUNIQUECLUSTERINDEXON(,)；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引,指索引项的顺序与表中记录的物理顺序一致的索引组织,一、建立索引,例13CREATECLUSTERINDEXStusnameONStudent(Sname);将在Student标的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname的升序存放用户可以在最经常查询的列上建立聚簇索引以提高查询效率一个基本表上最多建立一个聚簇索引建立聚簇索引后，更新该索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不易建立聚簇索引,一、建立索引（续）,例14为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；,一、建立索引（续）,唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致,一、建立索引（续）,建立索引原则记录有一定规模某列在where子句中频繁使用先装数据，后建索引,二、删除索引,索引旦建立，就由系统来选择和维护，无需用户干预，但当删除一些不必要的索引时，可用下列语句来实现：DROPINDEX删除索引时，系统将删除索引结构，并同时从数据字典中删去有关该索引的定义。例4.5删除Student表的Stusname索引。DROPINDEXStusname；注：索引是关系数据库的内部实现技术，属于内模式范畴,3.1SQL概述3.2学生课程数据库3.3数据定义3.4数据查询3.5数据更新3.7小结,3.4数据查询,查询是数据库的最重要的操作在SQL中，所有查询都用SELECT语句实现查询在一个或多个关系（基本表或视图）上进行，其结果是一个关系,3.4数据查询（续）,语句格式：SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；其中：SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序,3.4数据查询（续）,3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询,3.4.1单表查询,查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、聚集函数五、对查询结果分组,一、选择表中的若干列,选择表中的若干列或者部分列，是关系代数的投影运算1.查询指定列例1查询全体学生的学号与姓名SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系SELECTSname，Sno，SdeptFROMStudent；注：中各个列的先后顺序可以与表中的顺序不一致，用户可以根据应用的需要改变列的显示顺序,一、选择表中的若干列（续）,2.查询全部列例3查询全体学生的详细记录SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；注：使用*查询的全部列的结果中列的显示顺序与其在基本表中的顺序是一致的,一、选择表中的若干列（续）,3.查询经过计算的值SELECT子句的:表中的属性列表达式算术表达式、字符串常量、函数、列别名等例4查全体学生的姓名及其出生年份SELECTSname，2014-SageFROMStudent；输出结果：Sname2014-Sage-李勇1976刘晨1977王名1978张立1978,一、选择表中的若干列（续）,例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,YearofBirth:,2014-Sage,LOWER(Sdept)FROMStudent；输出结果为：SnameYearofBirth:2014-SageLOWER(Sdept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is,字符串常量,函数,一、选择表中的若干列（续）,例5.1使用列别名改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2014-SageBIRTHDAY，LOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is,二、选择表中的若干元组,消除取值重复的行查询满足条件的元组,1、消除取值重复的行,在SELECT子句中使用DISTINCT短语取消重复的元组（行）假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380,1、消除取值重复的行（续）,ALL与DISTINCT的使用,例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或(默认ALL)SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002,(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002,注：若没有指定DISTINCT，则缺省为ALLDISTINCT短语的作用范围是所有目标列，从SELECT语句的一般形式中即可看出例：查询选修课程号，及成绩错误：SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确：SELECTDISTINCTCno，GradeFROMSC;,2.查询满足条件的元组,查询满足指定条件的元组是通过WHERE语句实现的,（1）比较大小,在WHERE子句的中使用比较运算符=，=，)，!，!逻辑运算符NOT+比较运算符例7:查询计算机科学系全体学生的名单SELECTSnameFROMStudentWHERESdept=CS查询过程：对Student表进行全表扫描，查询每行的元组在Sdept列的值是否等于CS，将符合条件抽取出其Sname列的值形成一个新的元组,（1）比较大小,例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；,（1）比较大小,例9查询考试成绩有不及格的学生的学号SELECTDISTINCTSNOFROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;,五、对查询结果进行分组（续）,使用HAVING短语筛选最终输出结果HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。WHERE子句中的谓词在形成分组前起作用HAVING短语作用于组，从中选择满足条件的元组。HAVING子句中的谓词在形成分组后才起作用,3.4数据查询（续）,3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询,3.4.2连接查询,同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词一般格式：.比较运算符：=、=、.BETWEEN.AND.连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的,3.4.2连接查询（续）,SQL中连接查询的主要类型广义笛卡尔积等值连接(含自然连接)非等值连接查询自身连接查询外连接查询复合条件连接查询,一、广义笛卡尔积,不带连接谓词的连接很少使用例：SELECTStudent.*,SC.*FROMStudent,SC注：将Student中的每一个元组分别于SC中的每一个元组进行串接操作，结果没有实际意义,二、等值与非等值连接查询,等值连接、自然连接非等值连接等值连接连接运算符为“=”的连接操作.=.任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。例32查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；/*将Student与SC中同一学生的元组连接起来*/,Student表,SC表,等值连接后查询结果为：,自然连接,是等值连接的一种特殊情况，把目标列中重复的属性列去掉例33对例32用自然连接完成。SELECTStudent.Sno，Sname，Ssex，Sage,Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；,非等值连接查询,连接运算符不是=的连接操作.比较运算符：、=、.BETWEEN.AND.,自身连接,一个表与其自己进行连接，称为表的自身连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀,自身连接(续),例34查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；,SECOND表（Course表）,自身连接结果为：,1,7,3,5,4,5,6,7,FIRST表（Course表）,四、外连接,外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出外连接分类：左外连接LEFTOUTJOIN:外连接符出现在连接条件的左边右外连接RIGHTOUTJOIN:外连接符出现在连接条件的右边全外连接FULLOUTJOIN,四、外连接（续）,例35查询每个学生及其选修课程的情况包括没有选修课程的学生-用外连接操作SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno);/*也可以作用USING来去掉结果中的重复值：FROMStudentLEFTOUTJOINSCUSING(Sno);*/,五、复合条件连接,WHERE子句中含多个连接条件时，称为复合条件连接例37查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno=2ANDSC.Grade90；/*逻辑运算符其他限定条件*/,五、复合条件连接（续）,例38查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno；结果：,3.4数据查询（续）,3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询,3.4.3嵌套查询,一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno内层查询/子查询FROMSCWHERECno=2）；子查询的限制不能使用ORDERBY子句，ORDERBY子句只能对最终结果排序层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代,嵌套查询分类及求解方法,分类：不相关子查询：子查询的查询条件不依赖于父查询相关子查询：子查询的查询条件依赖于父查询嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止,3.4.3嵌套查询（续）,引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询,一、带有IN谓词的子查询,例39查询与“刘晨”在同一个系学习的学生。(此查询要求可以分步来完成，然后再构造嵌套查询)确定“刘晨”所在系名SELECTSdeptFROMStudentWHERESname=刘晨；结果为：SdeptIS,查找所有在IS系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdept=IS；结果为：SnoSnameSdept95001刘晨IS95004张立IS,一、带有IN谓词的子查询,构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)；判断此查询是相关还是不相关子查询？是不相关子查询并且DBMS求解该查询时也是分步去做的,一、带有IN谓词的子查询（续）,接上，此查询也可以用自身连接完成SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨；父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=刘晨)；,注：s1，s2都是Student的别名，称为元组变量，表示Student的一个元组,一、带有IN谓词的子查询（续）,例40查询选修了课程名为“信息系统”的学生学号和姓名。SELECTSno，Sname最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno首先在Course关系中找出“信FROMCourse息系统”的课程号，结果为3号WHERECname=信息系统);,课程名,学生姓名,Student,课程号,学生号,course,SC,一、带有IN谓词的子查询（续）,接上，用连接查询SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系统;,二、带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接当能确切知道内层查询返回单值时，可用比较运算符（，=，）。与ANY或ALL谓词配合使用,二、带有比较运算符的子查询（续）,例：例37可以用=代替IN：SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=刘晨)；,子查询一定要跟在比较符之后错误的例子：SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=刘晨)=Sdept；,二、带有比较运算符的子查询（续）,例41找出每个学生超过他选修课程平均成绩的课程号SELECTSno,CnoFROMSCxWHEREGrade=(SELECTAVG(Gra