第3章 关系数据库标准语言—SQL.ppt

第3章 SQL语言,学习目标 了解SQL的基本构成 掌握SQL的数据定义语言 掌握SQL语言的各种查询的实现 掌握SQL语言的数据更新语言 掌握视图的定义及其应用,3.1 SQL概述,SQL(Structured Query Language，结构化查询语言)，是定义和管理关系数据库的国际标准的编程语言。 SQL于1974年由Boyce和Chamberlin提出的，并于19741979年由IBM公司的San Jose Research Laboratory研制了关系数据库管理系统System R上实现了这种功能。,3.1.1 SQL的发展,SQL语言是定义和管理关系数据库的国际标准的编程语言。所经历的主要阶段如下： 1986年，ANSI的数据库委员会发布了文件：X.135-1986数据库语言(简称SQL-86) 。 1987年，ISO也通过了这一标准。 1989年10月，ANSI的数据库委员会发布了文件：X3.135-1989数据库语言-完整和增强的SQL(简称SQL-89) 发布，SQL89成为DBMS遵循的新标准。 1992年8月， ANSI和ISO共同发布了SQL-92 (简称SQL-2) 标准。,1999年， ISO发布了SQL-99 (简称SQL-3) 标准。 2003年， ISO发布了SQL-2003 (简称SQL-4) 标准。,3.1.2 SQL的特点,SQL是一个集数据查询、数据操纵、数据定义和数据控制于一体数据库管理系统语言。主要特点有： 综合统一。利用SQL可以独立完成数据库生命周期中的全部活动； 高度非过程化。利用SQL实现对关系的操作，用户只需提出“干什么”， 无需规定“如何干”；即不需要指定操作过程，也不需要了解存取路径； 面向集合的操作方式。 一种(语法)结构两种使用方式，表现在： 联机使用方式：即自主式语言；,嵌入式使用方式：即SQL语句嵌入到某种高级语言的程序中使用。 语言简洁，易学易用。SQL功能强大且具有两种不同的使用方式，但语言十分简洁，完成核心功能的语句仅仅只有9个，如下表3-1所示。,表3-1 SQL语言的动词,3.1.3 SQL的组成,SQL语言主要由以下几部分构成： 1 数据定义功能 即SQL的模式语句，主要有： 模式的定义、修改、删除功能； 索引的定义、修改、删除功能； 视图的定义、删除功能； 存储过程、触发器等的定义、修改、删除功能； 各种完整性约束条件的定义及核查机制。,2 数据操纵功能 即SQL的数据操作语句，主要有： 基于关系代数和元组演算的数据查询和数据更新(插入、修改和删除)语句； 嵌入式SQL的数据操作语句； 事务及其控制语句； 3 数据控制功能 关系、视图的访问权限的设置、取消及核查； SQL的连接语句； 其它的控制功能语句。,3.1.4 SQL查询的基本概念,SQL支持关系数据库的三级模式结构，如下图3-1所示。, 基本表(Table)：是独立存在的表，一个关系对应一个基本表，表中保存同一实体集中各实体的数据。一个或多个基本表对应一个存储文件。一个基本表可以定义若干索引，索引也存储在存储文件中。 视图(View)：是由基本表或视图导出的虚表，本身不存储实际的数据。在数据库中存储的只是视图的定义。从用户的角度，视图和基本表在概念上是等价的。 除此之外，SQL中还有导出表，即查询结果集。是根据基本表或视图查询得到的结果集。 约束和断言(Constraint And Assertion)：是标识有效数据集合的命名规则。它们为表、行或域(列)限定了相容的数据；并定义了两个检查特性：延迟模式和约束核查时间。,断言是与一些模式有关；常以核查约束的形式出现； 约束是与一些表或域有关。在实际的DBMS产品中常以核查约束或规则的形式出现。 触发器(Trigger)：是与单一基本表相关联的命名规则。每个触发器定义一个触发器事件，规定了对表进行更新操作时在表中产生的一个触发动作；触发时间规定了被触发动作发生的时间是在触发事件之前或之后。 SQL调用例程(SQL Invoked Routine)：SQL调用例程是有一些模式或模块组成。 由系统定义：称为调用例程或过程； 由用户定义：称为自定义函数或存储过程。,SQL中要定义的数据对象有许多，基本的有：基本表、视图、索引等，这些都由SQL的数据定义语句来实现如下表3-2。先讨论基本表和索引的相关定义语句。,3.2 SQL的数据定义语言,表3-2 SQL的数据定义语句,根据实验将要使用的具体的DBMS产品是SQL Server，详见补充1。,3.2.1 数据库的定义、删除与修改,1 数据库的创建 使用Transact-SQL语句 CREATE DATABASE ON , n , n LOG ON , n COLLATE 其中：, := PRIMARY ( NAME = , FILENAME = OS文件的路径及名字 , SIZE = 文件初始大小 , MAXSIZE = 最大值 | UNLIMITED , FILEGROWTH = 文件大小增量值 ) := FILEGROUP ,例1： Create Database sale_management_data On Primary ( Name=sale_file1, Filename=e:sale_systemdatasale_file1.mdf, Size= 100MB, MaxSize=2000MB, FileGrowth = 10MB ), ( Name=sale_file2, Filename =e:sale_systemdatasale_file2.ndf, Size = 10MB, MaxSize = 1000MB, FileGrowth = 10),Log on ( Name =sale_log, Filename =f:sale_systemdatasale_log.ldf, Size = 10MB, MaxSize = 1000MB, FileGrowth = 10MB ) 说明： Database_name：要建立的数据库的逻辑名称。要求：整个SQL Server服务器唯一；长度不超过128个字符。 ON与LOG ON ：指定数据库的数据文件、文件组和日志文件。当有多个数据文件或日志文件是，彼此之间要用“,”分隔。对于关键字PRIMARY，若有，指定其后的的数据文件属于主文件组；若没有，则第一个数据文件是主文件。, SIZE参数：指定数据文件或日志文件的初始大小。单位可以是KB、MB、GB、TB。对主数据文件，其最小值应等于model数据库中主数据文件的大小。若省略时，默认数据文件和日志文件大小为1MB(主数据文件除外)。 MAXSIZE参数：指定数据文件或日志文件可以增加到的最大容量。若省略时，默认到磁盘空间用尽为止(相当于MAXSIZE = UNLIMITED ) 。 FILEGROWTH参数：指定数据文件或日志文件的空间的每次增长量。有绝对增长量(不能小于64KB)和相对增长量两种方式。,2 数据库的修改 数据库创建完毕后，可能根据需要须修改数据库本身的各项设置，主要包括： 更改数据库名称 扩充数据文件或日志文件空间； 收缩数据文件或日志文件空间； 添加或删除数据文件或日志文件； 创建文件组； 改变默认文件组； 更改数据库的配置设置； 更改数据库的所有者。,使用Transact-SQL语句 ALTER DATABASE ADD FILE , n TO FILEGROUP | ADD FILEGROUP | ADD LOG FILE , n | REMOVE FILE | REMOVE FILEGROUP | MODIFY FILE | MODIFY NAME = | MODIFY FILEGROUP filegroup_property |NAME= ,其中： := ( NAME = , NEWNAME = , FILENAME = OS文件的路径及名字 , SIZE =文件的初始大小 , MAXSIZE =最大的文件容量 , FILEGROWTH = 文件大小增量 ) 说明： 注意各功能子句的作用; 与“创建数据库”基本相同，仅多了一个“NEWNAME”项。, MODIFY FILE子句：修改指定的数据文件。每次修改时，只能修改FILENAME、SIZE、FILEGROUP、MAXSIZE属性中的一项。 使用该命令改变数据文件容量时，只能增加不能减少。,3 数据库的删除 使用Transact-SQL语句 DROP DATABASE , 在SQL Server中，只有数据库所有者和sysadmin、dbcreator固定服务器角色才有权限删除数据库。 当删除数据库时，其数据文件和事务日志文件也会同时被删除。,建立数据库后最重要的一类定义就是定义基本表，首先介绍SQL-3中的数据类型，如表3-3所示。,3.2.2 基本表的定义、删除与修改,表3-3 SQL-3中的数据类型,1 基本表的定义 语法格式： CREATE TABLE ( ， ) = , , = (,) DEFAULT NOT NULL UNIQUE CONSTRAINT () =PRIMARY KEY (),FOREIGN KEY () REFERENCES () CHECK (expL) ,说明： DEFAULT ：默认值设定。当有该选项时，在进行数据插入时若没有指定该列的值时，就自动取为默认值； NOT NULL：是否允许空值设定。当有该选项时，在进行数据插入时必须为该列指定具体值； UNIQUE：值的唯一性设定。当有该选项时，整个表中该列值唯一； CONSTRAINT ()：列级完整性设定。列级完整性是一个条件表达式。 若有该选项时，则在对该列数据进行更新时其值必须满足相应的条件。,若在该列上定义了几个约束，则该列的取值必须是几个条件表达式所限定值集的交集。 表级完整性约束主要有三类： PRIMARY KEY ()：主码约束设定。用来定义表中所有的记录必须满足的实体完整性；一个表只能有一个主码。 FOREIGN KEY () REFERENCES (：外码约束设定。用来定义表中的外码以及外码引自于哪个表；一个表可以有多个外码。 CHECK (expL)：核查约束设定。用来定义表中同一记录内不同的属性之间必须满足的依赖(函数)关系。,基本表定义举例 例：学生-选课数据库中的四个表的定义。 专业设置表(专业编号,系名,专业名) ； 学生情况表(学号,姓名,性别,出生日期,入学年份,专业编号) ； 课程设置表(课程编号,课程名称,学时数,学分,先修课程号) ； 学生选课(学号,课程编号,成绩) 。,CREATE TABLE Major( major_no CHAR(3) NOT NULL CONSTRAINT major_const(major_no like 1-90-90-9 and major_no not like 100), department VARCHAR(30) NOT NULL, speciality VARCHAR(30) NOT NULL, PRIMARY KEY (major_no) ) CREATE TABLE Student( sno CHAR(10) NOT NULL CONSTRAINT sno_const(sno like 1-90-90-90-91-90-90-91-90-90-9 and substring(sno, 9, 2) not like 00 ),sname CHAR(10) NOT NULL, sex CHAR(2) NOT NULL CONSTRAINT sex_const(sex IN (“男”,“女”), birthday DATE NOT NULL CONSTRAINT bith_const(birthdaygetdate(), enter_year DATE NOT NULL, major_no CHAR(3), PRIMARY KEY (sno) , FOREIGN KEY (major_no) REFERENCES Major(major_no),CREATE TABLE Course( cno CHAR(6) NOT NULL CONSTRAINT cno_const(cno like a-z0-90-9-0-90-9), cname VARCHAR(30) NOT NULL, class_hourse SMALLINT NOT NULL, study_num NUMERIC(3,1) NOU NULL, Pre_cno CHAR(6), PRIMARY KEY(cno), FOREIGN KEY(pre_cno) REFERENCES Course(cno),CREATE TABLE Select_Course( sno CHAR(10) NOT NULL , cno CHAR(6) NOT NULL , score NUMERIC(5,1) CONSTRAINT score_const(score ISNULL or (score=0 and score=100), PRIMARY KEY(sno,cno), FOREIGN KEY(sno) REFERENCES Student(sno) , FOREIGN KEY(cno) REFERENCES Course(cno) ),2 基本表的修改 语法格式： ALTER TABLE ADD ALTER COLUMN SET DEFAULT / DROP DEFAULT/ DROP CONSTRAINT DROP RESTRICT/CASCADE ADD / DROP CONSTRAINT RESTRICT/CASCADE ,说明： ADD ：增加新的列。增加的新列由指定(见表的定义)；所增加的列必须允许为空值(不允许出现关键字NOT NULL)；具体作用有： 表的度(目)加1。新增加的列在表的所有列定义的最后； 表中已有行在列上的值自动取为空或默认值(当有默认值子句时)； 列被添加到对表作用的触发器的每个UPDATE触发事件的清单中； 新增加的列对任何涉及到的视图的定义或约束没有作用。, ALTER COLUMN子句：列的修改。设置或删除指定列的默认值；或删除指定列的指定名字的约束； DROP RESTRICT/CASCADE：删除指定的列。若未指定RESTRICT/CASCADE，相当于CASCADE，具体作用有： 表的度(目)减1。表的所有其它列的顺序也适当调整； 表中该列上的所有数据全被删除。 关键字的作用是： 选用RESTRICT：只有当列不在任何相关的对象(视图、SQL例程、触发器定义或断言)中出现时才能删除；,选用CASCADE：删除该列及所有包含有该列的任何相关的对象(视图、SQL例程、触发器定义或断言)。 ADD ：增加表级完整性约束。 DROP CONSTRAINT RESTRICT/CASCADE：删除指定的表级约束。若未指定RESTRICT/CASCADE，相当于CASCADE，关键字的作用是： 选用RESTRICT：只有当约束不再被任何SQL例程使用或不再与其它约束或视图有关； 选用CASCADE：删除该约束及所有与该约束相关的对象； 一般地，不要试图使用该子句删除主码的属性。,例：在学生选课表中增加一个“选修时间”的列。 ALTER TABLE Select_Course ADD select_date DATE CONSTRAINT sd_const(select_dategetdate(),3 基本表的删除 语法格式： DROP TABLE RESTRICT/CASCADE 说明： DROP TABLE RESTRICT：删除命名的表及其数据，只有当该表满足以下条件，否则出错。 没有子表； 不在任何视图、SQL例程、触发器定义或断言中被引用； 不在任何不是该表所有的表约束被引用； 不在任何其它表或SQL参数范围内。, DROP TABLE CASCADE：删除指定的表、表中数据、所有建立在该表之上的相关对象。 成功删除一个表实际上包括以下几个方面的动作： 该表的定义及表中所有数据被删除； 以该表为基础的所有子表以级联方式(CASCADE)删除； 该表的超(父)表，该表行的超行被删除；该表也将从所有直接子表的超表清单中删除； 任何拥有对该表的所有操作权限也被全部撤消； 所有与该表有关的其它相关对象也被级联撤消。,索引是提高查询效率的有效机制。SQL允许对一个基本表建立多个索引，以提供多种存取路径。建立和删除索引一般是由DBA或表的属主(即表的主人)根据需要建立。 有些DBMS自动建立下列的索引： PRIMARY KEY UNIQUE 维护索引： DBMS自动完成； 使用索引：DBMS自动选择是否使用索引以及使用哪些索引。,3.2.3 索引的建立与删除,1 索引的建立 语法格式： CREATE UNIQUE/CLUSTER INDEX ON (ASC/DESC ,ASC/DESC ) 说明： ：指定要建立索引的基本表； 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔； ASC/DESC：指定索引值的排列次序，升序：ASC，降序：DESC。缺省时相当于ASC；,UNIQUE/CLUSTER：指定要建立的索引的类型。若没有该项，则建立的是一般的索引； 选用UNIQUE：建立唯一性索引。则该索引的每一个索引值只对应唯一的数据记录； 选用CLUSTER：建立聚簇索引。聚簇索引指的是表中记录的物理存放顺序就是索引项的顺序。 若在表定义时指定了主码，系统就自动以主码为索引字段创建了一个聚簇索引； 若在表定义时没有指定了主码而创建了聚簇索引，系统就自动以聚簇索引字段作为主码； 一个表必须有唯一的一个聚簇索引。 索引一经建立，就由系统使用和维护。,2 索引的删除 语法格式： DROP INDEX 功能：将索引的定义从数据字典(DD)中删除 。,3.3 SQL的数据查询,1 查询的关系表达式 查询要求用关系表达式可以表示为： A1,A2, An F(R1R2Rm) 在该表达式中有三种基本参数，分别是： 查询目标列： A1,A2, An； 查询对象： R1，R2 ， ，Rm； 查询条件(逻辑)：F。 这三类不同的参数在SQL查询语句中分别用不同的子句表示，分别是：select子句，from子句，where子句。,2 查询语句的语法格式 SELECT ALL/DISTINCT FROM WHERE GROUP BY HAVING ORDER BY ASC/DESC ， =*/ =， =/./ AS =， ，,=/ AS SELECT子句：对应于关系表达式中的投影运算，指定查询目标列； FROM子句：对应于笛卡尔积中的各个关系，即列出查询时所涉及的所有查询对象； WHERE子句：对应于查询条件(逻辑)。 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用集函数。 HAVING短语：筛选出只有满足指定条件的元组； ORDER BY子句：对查询结果表按指定列值排序。,功能： 先构造from子句中关系的笛卡尔积，然后根据where子句的条件对元组进行选择，最后再投影到select子句指定的列。,所谓单表查询指的是查询仅涉及一个表(from子句中仅有一个查询对象)，是一种最简单的查询操作。具体有以下几种情况： 选择表中的若干列； 选择表中的若干元组； 对查询结果排序； 使用集函数； 对查询结果分组。,3.3.1 单表查询,1 查询指定的列 例1：查询全体学生的学号、姓名、性别。 SELECT sno，sname, sex FROM Student 2 查询全部列 例2：查询全体学生的详细记录。 SELECT * FROM Student 3 查询经过计算的列 SELECT子句的是通过表达式计算的结果,这样的表达式通常有：算术表达式、各种常量、函数、列别名。,例3：查询全体学生的学号、姓名、性别、年龄。 SELECT sno, sname, sex, year(getdate()-year(birthday) AS sage FROM Student 使用列别名改变查询结果的列标题。 4 查询表中的若干元组 消除取值重复的行 在查询时可能得到的结果集中存在相同重复的元组(经过选择和投影)，可以通过在SELECT子句中选用不同的关键字决定重复的元组是否保留？ ALL/DISTINCT：没有或选用ALL，保留重复的元组；选用DISTINCT：重复的元组只保留一个。,例4： 查询选修了课程的学生学号。 SELECT sno FROM Select_Course 按上述方法得到的查询结果集中，一个学生选修了多少门课程，就有多少行相同重复的学号，实际上只要一个即可，多余重复的应该去掉。则查询语句是： SELECT DISTINCT sno FROM Select_Course DISTINCT短语的作用范围是所有目标列。 查询满足条件的元组 查询满足条件的元组，是SELECT查询语句使用最灵活和最有效的体现之一，通过选用where子句并构造各种条件表达式来实现，常用查询条件如表3-4所示。, 比较大小 在WHERE子句的中使用比较运算符： =，=，!，! ； 逻辑运算符NOT + 比较运算符。,表3-4 常用的查询条件,例5：查询所有年龄在20岁以下的学生的学号、姓名及其年龄。 SELECT sno, sname, sex, year(getdate()-year(birthday) AS sage FROM Student WHERE sage=20,例6：查询有考试成绩不及格的学生的学号。 由于某些学生可能有多门课程不及格，实际上只要一个即可，因此需要使用DISTINCT短语。 SELECT DISTINCT sno FROM Select_Course WHERE score中使用谓词： BETWEEN AND NOT BETWEEN AND BETWEEN后的是范围的下限， AND后的是范围的上限。,例7：查询所有年龄在2023岁之间的学生的学号、姓名及其年龄。 SELECT sno, sname, sex, year(getdate()-year(birthday) AS sage FROM Student WHERE sage BETWEEN 20 AND 23 确定集合 在WHERE子句的中使用谓词： NOT IN () ：用逗号分隔的一组取值。, 字符串模式匹配 在WHERE子句的中使用谓词LIKE，其语法格式是： NOT LIKE ESCAPE 含义是：查找指定的列与相匹配的元组。 ：可以是固定的字符串或含通配符(%或_)的字符串。当是固定字符串时，可以用=运算符取代 LIKE 谓词；用!=或运算符取代NOT LIKE谓词。 %(百分号) ：代表任意长度(长度可以为0)的字符串。 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串。,_(下横线)：代表任意单个字符。 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。 当用户要查询的字符串本身就含有%或 _ 时，要使用ESCAPE 短语对通配符进行转义。 例8：查询专业编号是414的所有女生的学号、姓名。 SELECT sno,sname FROM Student WHERE major=414 AND sex LIKE 女 例9：查询DB_Design课程的课程号、学时和学分。 SELECT cno， class_hourse, study_num FROM Course WHERE cname LIKE DB_DesignESCAPE ,使用换码字符将通配符转义为普通字符。 例10：查询以“DB_“开头，且倒数第3个字符为 i的课程的详细情况。 SELECT * FROM Course WHERE cname LIKE DB_%i_ _ ESCAPE 涉及空值的查询 在WHERE子句的中使用谓词： 使用谓词 IS NULL 或 IS NOT NULL； “IS NULL” 不能用 “= NULL” 代替。,例11：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT sno，cno FROM Select_Course WHERE score IS NULL 例12：查询所有有成绩的学生学号和课程号。 SELECT sno，cno FROM Select_Course WHERE score IS NOT NULL 说明：在空值处理是用IS不能用=来代替。, 多重条件查询 在WHERE子句的中用逻辑运算符AND和 OR来联结多个查询条件。其中：AND的优先级高于OR，可以用括号改变优先级。 可用来实现多种其他谓词： NOT IN NOT BETWEEN AND 例13：查询所有年龄在2023岁之间的学生的学号、姓名及其年龄。 SELECT sno, sname, sex, year(getdate()-year(birthday) AS sage FROM Student WHERE sage=20 AND sage=23,5 对查询结果集排序 使用ORDER BY子句对结果集按指定的列排序。 例14：查询所有选修了课程号为C02-01的学生学号和成绩，并按成绩降序排列。 SELECT sno，score FROM Select_Course WHERE cno=C02-01 ORDER BY score DESC 对于空值：若按升序排列，含空值的元组排在最后；若按降序排列，含空值的元组排在最前； 在整个查询语句中，若有ORDER BY子句，只能是最后一个子句；,6 使用集函数实现统计 使用SQL提供的集函数，使用列名作为函数的自变量来实现各种统计功能，集函数如表3-5 所示。,表3-5 SQL提供的集函数,集函数使用的说明： DISTINCT/ALL： 缺省(没有)时相当选ALL：只对非空值进行处理； 选用DISTINCT：对于非空值中若有相同的值，只取其中的一个进行统计。 SUM，AVG，MAX，MIN函数只能对数值型的列进行统计； 当目标列是表达式或集函数时，一般要为其取一个别名。,例15：查询选修了课程的学生人数。 学生每选修了一门课程，在Select_Course表中就有一条记录。一个学生会选修多门课程，为避免重复计算人数，要使用DISTINCT关键字。 SELECT COUNT(DISTINCT sno) as study_num FROM Select_Course 例16：查询选修了课程号为C02-01的学生人数、最高成绩、最低成绩和平均成绩。 SELECT COUNT(DISTINCT sno) as study_num, MAX(score) as max_score, MIN(score) as min_score, AVG(score) as avg_score FROM Select_Course WHERE cno=C02-01,7 对查询结果集分组 使用GROUP BY子句对查询的中间结果集进行分组，同一组(分组字段的值相同)的所有元组在结果集中是一个元组。分组的目的是为了细化统计。 例17：查询每一门课程选修的学生人数、最高成绩、最低成绩和平均成绩。 SELECT COUNT(DISTINCT sno) as study_num, MAX(score) as max_score, MIN(score) as min_score, AVG(score) as avg_score FROM Select_Course GROUP BY cno,若分组后还需要对结果集中的元组按一定条件进行筛选，得到满足条件的组，则可以使用HAVING短语。 例18：查询平均成绩在70分以上(含70分) 的学生学号、选课门数、最高成绩、最低成绩和平均成绩。 SELECT sno, COUNT(DISTINCT cno) as select_num, MAX(score) as max_score, MIN(score) as min_score, AVG(score) as avg_score FROM Select_Course GROUP BY sno HAVING avg_score=70 WHERE子句与HAVING短语的区别：WHERE子句作用于表或视图；HAVING短语作用于组，且只能出现在GROUP BY子句中。,凡是在一个查询的FROM子句中有两个或两个以上的查询对象的，都是连接查询。连接查询是关系数据库中最主要的查询形式。具体有：等值连接、自然连接、非等值连接、自身连接、外连接和复合条件查询等。 连接条件 WHERE子句中表示两个表的条件称为连接条件或连接谓词。决定了两个关系中哪些元组相互匹配、连接结果中出现那些属性(列)。 在连接条件或连接谓词中的列名称为连接字段。在连接条件各连接字段不一定同名，但必须是可比的。,3.3.2 连接查询,连接谓词的一般格式： /. /. =、=、和是同一列名：称为自然连接查询， 前必须有前缀， 这种查询是最常用的一种； /. BETWEEN /. AND /. 若查询语句的FROM子句中有多个查询对象而又没有WHERE子句：称为笛卡尔积。, 若一个查询语句的WHERE子句有多个连接谓词：称为复合连接查询，这种查询是最传统的查询形式。 无论是哪一种连接谓词，若查询对象是同一个表(必须取不同的别名，连接谓词中使用别名)：称为自身连接查询。 连接查询的机制 嵌套循环法(NESTED-LOOP) 和笛卡尔积的构造方式完全一样。 排序合并法(SORT-MERGE)(常用于=连接) 首先按连接属性对表1和表2排序；,对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一个大于表1连接字段值的元组时，对表2的查询不再继续； 找到表1的第二个元组，然后从刚才的中断点处继续顺序扫描表2，重复和上一步相似的操作； 重复上述操作，直到表1中全部元组都处理完。 索引连接(INDEX-JOIN) 对表2按连接字段建立索引； 对表1中的每个元组，依次根据连接字段值查询表2的索引，当找到满足条件的元组时，就将两个表中的元组拼接起来，形成一个结果表中元组。, 连接类型 连接类型决定了如何处理连接条件不满足(不匹配)的元组。SQL-3中允许的连接类型和连接条件如下： 连接类型：INNER JOIN(内连接)、 LEFT OUTER JOIN(左外连接)、 RIGHT OUTER JOIN(右外连接)、 FULL OUTER JOIN(完全外连接)； 连接条件：NATURAL、ON , 复合条件查询,所谓复合条件查询指的是WHERE子句中含多个连接条件。 例1：查询选修了课程号是S11-01且成绩在80分(含80分)以上的学生的学号、姓名、成绩。 SELECT S.sno, sname, cname, score FROM student as S, select_course as SC WHERE S.sno=SC.sno and SC.cno=S11-01 and score=80 例2：查询平均成绩在75分以上(含75分) 的学生学号、姓名、选课门数、最高成绩、最低成绩和平均成绩。,SELECT S.sno as 学号, sname as 姓名， COUNT(DISTINCT cno) as 选课门数, MAX(score) as 最高成绩, MIN(score) as 最低成绩, AVG(score) as 平均成绩 FROM Student as S , Select_Course as SC WHERE S.sno=SC.sno GROUP BY S.sno HAVING 平均成绩=75 例3：查询学生学号、姓名、选修的课程名及成绩。 SELECT S.sno as 学号, sname as 姓名,cname as 课程名, score as 成绩 FROM Student as S, Select_Course as SC, Course WHERE S.sno = SC.sno and SC.cno = Co,例4：查询所有课程都及格的学生的学号、姓名、最高成绩、最低成绩和平均成绩。 SELECT S.sno as 学号, sname as 姓名，MAX(score) as 最高成绩, MIN(score) as 最低成绩, AVG(score) as 平均成绩 FROM Student as S ,Select_Course as SC WHERE S.sno=SC.sno GROUP BY S.sno HAVING 最低成绩=60,例5：查询选修了课程号是C02-01所有同学的学号、姓名、性别、出生日期和级别。 SELECT S.sno, sname, sex, birthday_date, enter_date FROM Student as S, select_course as SC WHERE S.sno=SC.sno and cno=C02-01 例6：查询选修了高等数学所有同学的学号、姓名、性别、系名、专业、出生日期和级别。 SELECT S.sno, sname, sex, department, speciality, birthday_date, enter_date FROM Student as S, select_course as SC, Course as C, Major as M WHERE S.sno=SC.sno and SC.cno=C.Cno and S.major_no=M.major_no and cname like 高等数学,例7：查询成绩至少比选修了课程号C02-01的某一个学生的成绩要高的学生的学号、姓名、性别和级别。 SELECT DISTINCT S. sno, sname, sex, enter_date FROM Student as S, Select_Course as SCX, Select_Course as SCY WHERE S.sno=SCX.sno and SCX.scoreSCY.score and SCY.cno=C02-01, 连接查询,所谓连接查询指的是表之间的关系通过各表中字段的值相等的连接关系来实现。 连接关系一般SELECT命令的FROM子句中表示，而WHERE子句则进一步对要查询的记录加以限制 ； 这种查询的FROM子句的一般形式是： FROM AS INNER/LEFT OUTER/RIGHT OUTER JOIN AS ON .=. 和应该是相同的字段名(推荐使用方式)，若是不同的名字，则必须是数据类型和值域相同。,1 内连接 内连接实际上是等值连接，关键字是INNER JOIN.ON。 连接的字段通常名字相同； 查询结果集中仅包括两个表中连接字段相等(相匹配)的情况； 若将关键字是INNER换成NATURAL就变成了自然连接。,例1：查询平均成绩在75分以上(含75分) 的学生学号、姓名、选课门数、最高成绩、最低成绩和平均成绩。 SELECT S.sno as 学号, sname as 姓名， COUNT(DISTINCT cno) as 选课门数, MAX(score) as 最高成绩, MIN(score) as 最低成绩, AVG(score) as 平均成绩 FROM Student as S INNER JOIN Select_Course as SC ON S.sno=SC.sno GROUP BY S.sno HAVING 平均成绩=75,例2：查询选修了课程号是S11-01且成绩在80分(含80分)以上的学生的学号、姓名、性别、课程名、年龄、成绩。 SELECT S.sno, sname, sex, cname, year(getdate()-year(birthday_date) as sage, score FROM student as S INNER JOIN Select_Course as SC ON S.sno=SC.sno INNER JOIN Course as C ON SC.cno=C.cno WHER SC.cno=S11-01 and score=80,例3：查询所有选修了高等数学的学生的学号、姓名、性别、系名、专业、出生日期和级别。 SELECT S.sno, sname, sex, department, speciality, birthday_date, enter_date FROM Student as S INNER JOIN select_course as SC ON S.sno=SC.sno INNER JOIN Course as C ON SC.cno=C.cno INNER JOIN Major as M ON S.major_no=M.major_no WHERE cname like 高等数学,2 外连接 关键字是：LEFT/RIGHT/FULL OUTER JOIN.ON 。 查询结果集中不仅包括两个表中连接字段相等(相匹配)的情况，而且包括两个表中连接字段不匹配的情况。 参与连接的两个表有左，右的次序。 左外连接 以左表为主，关键字是LEFT OUTER JOIN.ON 。其处理过程是： 先内连接。处理两个表中连接字段相匹配的记录；,然后处理不匹配的记录。对于左边表中每一条在右边表中不匹配的记录t：结果集中有一条记录r，r从左边表中得到的字段就是记录t，而其它来自右边表中的字段值全被赋为空(NULL) 。 例1：查询学生的学号、姓名、级别、选课编号。 SELET S.sno, sname, sex, enter_year, cno FROM Student as S LEFT OUTER JOIN Select_Course as SC ON S.sno=SC.sno 在上例中，左表是学生情况表(Student)，右表是学生选课表(Select_Course) 。查询结果表中不仅有已选课的学生及其选课情况，而且有未选课的学生情况。,例2：查询未选修任何课程的同学的学号、姓名、性别、级别、专业编号。 SELET S.sno, sname, sex, enter_year, major_no FROM Student as S LEFT OUTER JOIN Select_Course as SC ON S.sno=SC.sno WHER cno IS NULL, 右外连接 以右表为主，关键字是RIGHT OUTER JOIN.ON 。其处理过程是： 先内连接。处理两个表中连接字段相匹配的记录； 然后处理不匹配的记录。对于右边表中每一条在左边表中不匹配的记录t：结果集中有一条记录r，r从右边表中得到的字段就是记录t，而其它来自左边表中的字段值全被赋为空(NULL) 。 例3：查询学生的学号、姓名、选课的课程号、成绩。 SELET S.sno, sname, cno, score FROM Student as S RIGHT OUTER JOIN Select_Course as SC ON S.sno=SC.sno,在上例中，左表是学生情况表(Student)，右表是学生选课表(Select_Course) 。查询结果表中不仅有已被学生选修的课程情况，而且有未被学生选修的课程情况。 例4：查询没有任何同学选修的课程的课程编号、课程名称、学时数、学分。 SELET C.cno, cname, class_hourse, study_num FROM Select_Course as SC RIGHT OUTER JOIN Course as C ON SC.cno=C.cno WHER SC.sno IS NULL, 全外连接 全外连接是左外连接和右外连接的组合，关键字是FULL OUTER JOIN.ON 。其处理过程是： 先内连接。处理两个表中连接字段相匹配的记录； 然后处理与左边表的记录不匹配的情况。对于左边表中每一条在右边表中不匹配的记录t：结果集中有一条记录r，r从左边表中得到的字段就是记录t，而其它来自右边表中的字段值全被赋为空(NULL) 。 最后处理与右边表的记录不匹配的情况。对于右边表中每一条在左边表中不匹配的记录t：结果集中有一条记录r，r从右边表中得到的字段就是记录t，而其它来自左边表中的字段值全被赋为空(NULL) 。,3.3.3 嵌套查询,一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。 子查询的限制：不能使用ORDER BY子句。 层层嵌套方式反映了 SQL语言的结构化； 有些嵌套查询可以用连接运算替代； 不相关子查询(嵌套子查询)：子查询的查询条件不依赖于父查询； 相关子查询(相关查询)：子查询的查询条件依赖于父查询。, 嵌套子查询,是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套子查询的WHERE子句的一般形式是： WHERE ( SELECT FROM .) :=NOT IN,ANY/SOME/ALL =,=,!= 其中： =SOME(或=ANY)与IN等价： ALL与 NOT IN 等价。,1 集合成员资格测