SQL Server 2000数据库技术教案下载

SQL Server 2000数据库技术 教案 中职数学基础模块教案 下载北师大版¥1.2次方程的根与系数的关系的教案关于坚持的教案初中数学教案下载电子教案下载 下载 SQL Server 2000数据库技术教案下载-样章.doc 第3章 SQL语言 结构化查询语言SQL(Structured Query Language)是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 语言。 SQL语言的版本包括:SQL-89，SQL-92，SQL99。 3.1 基本概念 SQL语言之所以能够为用户和业界所接受，成为国际标准，是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。SQL语言集数据查询(data query)、数据操纵(data manipulation)、数据定义(data definition)和数据控制(data control)功能于一体，充分体现了关系数据语言的特点和优点。其主要特点包括: 1.一体化 SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。 2.高度非过程化 非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。 3.简洁 SQL语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词:CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE，如表3-1所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。 4.能以多种方式使用 , SQL语言既是自含式语言，又是嵌入式语言。 , 作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直 接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言 (例如C、PB)程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL 语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的 作法，为用户提供了极大的灵活性与方便性。 5.面向集合的操作方式(补充) , SQL语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、 更新操作的对象也可以是元组的集合。 , 非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。 例如查询所有平均成绩在80分以上的学生姓名，用户必须说明完成该请求的具体处理 过程，即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。 3.1.3 SQL语言功能概述 SQL的功能分成四部分:数据定义、数据控制、数据操纵、数据查询 SQL语言的动词 SQL功能 动词 数据查询 SELECT 数据定义 CREATE，DROP，ALTER 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE 3(2 SQL的数据类型 每种数据库产品所支持的数据类型并不完全相同，而且与标准的sql也有差异，我们以microsoft SQL Server支持的常用数据类型进行介绍。 1(数值型 , 准确型 准确型是指在计算机中能够精确存储的数据 , Bigint 8字节 , Int 4字节 , Smallint 2字节 , Tinyint 1字节 , Bit 1位 , Numeric(P,Q) P:指小数点左边位数+小数点+小数点右边位数 Decimal(P,Q) Q:指小数位数 , 近似型 用一表示浮点型数据的近似数据类型 , Float 8字节 , Real 4字节 2(字符串型 Char(N)、Varchar(N)、Text、Nchar(N)、Nvarchar(N)、Ntext、Binary(N)、 Varbinary(N)、Image 3(日期时间型 Datetime 8字节 Smalldatetime 4字节 4(货币型 Money 8字节 Smallmoney 4字节 3(3 数据定义功能 表是数据库中非常重要的对象，它用于存储用户的数据。创建表就是定义表所包含的列的结构，其中包括列的名称、约束等。 3.3.1 基本表的定义和删除 1(定义基本表 般格式如下: Create Table <表名>(<列名> <数据类型> [列级完整性约束条件] [，<列名> <数据类型> [列级完整性约束条件]...] [,<表级完整性约束条件>]); 其中: <表名>是所要定义的基本表的名字，最好能表达表的应用语义 <列名>是由一个或多个属性(列)组成。 建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由dbms自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。 下面我们以一个“学生,课程”数据库为例说明create语句的各种用法。 “学生,课程”数据库中包括三个表: (1) “学生”表student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，可记为: Student(Sno,Sname,Ssex,Sage,Sdept) (2) “课程”表course由课程号(Cno)、课程名(Cname)、学期(Semster)、学分(Ccredit)、学时(Period)五个属性组成，可记为: Course(Cno,Cname,Semster,Ccredit,Period) (3) “学生选课”表sc由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，可记为: Sc(Sno,Cno,Grade) 例1 建立一个“学生”表student，它由学号sno、姓名sname、性别ssex、年龄sage、所在系sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。 Create Table Student ( Sno Char(5) Primary Key, Sname Char(20) Not Null, Ssex Char(1) Check(Ssex=‘男’Or Ssex=‘女’), Sage Tinyint Check(Sage>=15 And Sage<=45), Sdept Char(20) Defalut ‘计算机系’ ); 例2:建立课程表 Create Table Course ( Cno Char(10) Not Null, Cname Char(20) Not Null, Ccredit Tinyint Check(Ccredit>0) Semester Tinyint Check(Semester>0), Period Int Check(Period>0), Primary Key(Cno) ); 例3:建立选课表，注意参照完整性 Create Table Sc ( Sno Char(7) Not Null, Cno Char(10) Not Null, Grade Tinyint Check (Grade>=10 And Grade<=100), Primary Key (Sno,Cno), Foreign Key (Sno) References Student (Sno), Foreign Key (Cno) Referenced Course (Cno) ); 2. 删除表 删除表时会将与表有关的所有对象一起删掉。基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心。 一般格式为:Drop Table <表名> 删除student表 Drop Table Student 3.3.2 修改表结构 一般格式为: Alter Table <表名> [Alter <列名> <新数据类型> ] [Add <新列名> <数据类型> [完整性约束]] [Drop <完整性约束名> <完整性约束名>] [Modify <列名> <数据类型> <数据类型>] [Add Primary Key(列名)] [Add Preign Key(列名) Referneces 表名(列名)]; 其中<表名>指定需要修改的基本表，Add子句用于增加新列和新的完整性约束条件，Drop子句用于删除指定的完整性约束条件，Modify子句用于修改原有的列定义。 例4 向student表增加“入学时间”列，其数据类型为日期型 Alter Table Student Add Scome Date; 不论基本表中原来是否已有数据，新增加的列一律为空值。 例5 将年龄的数据类型改为半字长整数 Alter Table Student Modify Sage Smallint; 修改原有的列定义有可能会破坏已有数据。 例6 删除关于学号必须取唯一值的约束 Alter Table Student Drop Unique(Sno); Sql没有提供删除属性列的语句，用户只能间接实现这一功能，即先原表中要保留的列及其内容复制到一个新表中，然后删除原表，并将新表重命名为原表名。 3.4 数据查询功能 查询功能是SQL语言的核心功能,是数据库中使用得最多的操作,也是最复杂的一个语句。 3.4.1查询语句的基本结构 一般格式为: SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]... FROM <表名或视图名>[,<表名或视图名>] ... [WHERE <条件表达式>] [GROUP BY <分组依据列>] [HAVING <条件表达式>] [ORDER BY <排序依据列> [ASC|DESC]]; 整个SELECT语句的含义是:根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按<分组依据列>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集 函数 excel方差函数excelsd函数已知函数     2 f x m x mx m      2 1 4 2拉格朗日函数pdf函数公式下载 。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按<排序依据列>的值的升序或降序排序。 3.4.2 简单查询 1. 选择表中的若干列 (1)查询指定列 例5 查询全体学生的学号与姓名 SELECT Sno,Sname FROM Student; <目标列表达式> 中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。 例6查询全体学生的姓名、学号、所在系 SELECT Sname, Sno, Sdept FROM Student; 结果表中的列的顺序与基表中不同，是按查询要求，先列出姓名属性，然后再列学号属性和所在系属性。 (2)查询全部列 例7查询全体学生的详细记录 SELECT * FROM Student; 该SELECT语句实际上是无条件地把Student表的全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。 (3)查询经过计算的值 SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 例8查询全体学生的姓名及其出生年份 SELECT Sname, 2004-Sage FROM Student; 本例中，<目标列表达式>中第二项不是通常的列名，而是一个计算表达式，是用当前的年份(假设为2004年)减去学生的年龄，这样，所得的即是学生的出身年份。输出的结果为: Sname ------ ---------- 李勇 1976 刘晨 1977 王名 1978 张立 1978 不仅可以是算术表达式，还可以是字符串常量、函数等。 例9查询全体学生的姓名、出生年份和所有系 SELECT Sname, 'Year of Birth:', 2004-Sage 出生年份 FROM Student; 结果为: Sname 出生年份 ------ ------------------------- 李勇 Year of Birth: 1976 刘晨 Year of Birth: 1977 王名 Year of Birth: 1978 张立 Year of Birth: 1978 用户可以通过指定别名来改变查询结果的列标 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 ，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。 2.选择表中的若干元组 (1)消除取值重复的行 例10查询所有选修过课的学生的学号 SELECT Sno FROM SC; 假设SC表中有下列数据 Sno Cno Grade ------- ------- ------- 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80 执行上面的SELECT语句后，结果为: Sno ------- 95001 95001 95001 95002 95002 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定DISTINCT短语: SELECT DISTINCT Sno FROM SC; 执行结果为: Sno ------- 95001 95002 (2)查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。 常用的查询条件 查询条件 谓 词 ??????? ??????????????? 比较(比较运算符) = 、>、>=、<、<=、<>(!=)、NOT 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件 AND, OR ?比较大小 例11查询计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept = '计算机系'; 例12查询所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage <20; 或 SELECT Sname, Sage FROM Student WHERE NOT Sage>= 20; 例13查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM Course WHERE Grade <60; 这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。 ? 确定范围 例14查询年龄在20至23岁之间的学生的姓名、系别、和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23; 与BETWEEN...AND...相对的谓词是NOT BETWEEN...AND...。 例15查询年龄不在20至23岁之间的学生姓名、系别和年龄 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23; ?确定集合 例16查询信息系、数学系和计算机系的学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept IN ('信息系', '数学系', '计算机系'); 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。 例17查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('信息系', '数学系', '计算机系'); ?字符匹配 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下: [NOT] LIKE '<匹配串>'其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有以下通配符。 %(百分号) 代表任意长度(长度可以为0)的字符串。 _(下横线) 代表任意单个字符。 [ ]:匹配[ ]中的任意一个字符。 [^]:不匹配[ ]中的任意一个字符。 例18查所有姓刘的学生的姓名、学号和性别 SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%'; 例19查名字中第二字为“大”或“小”的学生的姓名和学号 SELECT Sname, Sno FROM Student WHERE Sname LIKE '__[大小]%'; 例20查询所不姓刘的学生姓名 SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%'; 例21查询学号中最后一位不是2、3、5的学生情况 SELECT * FROM Student WHERE Sno LIKE ‘%[^235]’; ? 涉及空值的查询 例23某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NULL; 注意这里的'IS'不能用等号('=') 代替。 例24查所有有成绩的记录的学生学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL; ?多重条件查询 逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。 例25查询计算机系年龄在20岁以下的学生姓名 SELECT Sname FROM Student WHERE Sdept='计算机系' AND Sage<20; 例12 中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如下等价形式: SELECT Sname, Ssex FROM Student WHERE Sdept='信息系' OR Sdept='数学系' OR Sdept='计算机系'; 3(对查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序(通常是元组在表中的先后顺序)输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序(ASC)或降序(DESC)重新排列查询结果，其中升序ASC为缺省值。 例26查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列 SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC; 前面已经提到，可能有些学生选修了3号课程后没有参加考试，即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。 例27查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列 SELECT * FROM Student ORDER BY Sdept, Sage DESC; 4(使用集函数 为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括: COUNT([DISTINCT|ALL] *) 统计元组个数 COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数 SUM([DISTINCT|ALL] <列名>) 计算一列值的总和(此列必须是数值型) AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值(此列必须是数值型) MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值 MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值 如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为缺省值)，则表示不取消重复值。 例29 查询学生总人数 SELECT COUNT(*) FROM Student; 例30查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。 例31计算1号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno='1'; 例32查询选修1号课程的学生最高分数 SELECT MAX(Grade) FROM SC WHERE Cno='1'; 例33查询选修1号课程的学生最高分、最低分及平均分 SELECT MAX(Grade)，MIN(Grade)，AVG(Grade) FROM SC WHERE Cno='1'; 5(对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。 对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 例34查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; 该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为: Cno COUNT(Sno) ------ ---------- 1 22 2 34 3 44 4 33 5 48 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。 例35查询信息系选修了3门以上课程的学生的学号 SELECT Sno FROM SC WHERE Sdept=‘信息系’ GROUP BY Sno HAVING COUNT(*)>3; 选修课程超过3门的信息系学生的学号，首先需要通过WHERE子句从基本表中求出信息系的学生。然后求其中每个学生选修了几门课，为此需要用GROUP BY子句按Sno进行分组，再用集函数COUNT对每一组计数。如果某一组的元组数目大于3，则表示此学生选修的课超过3门，应将他的学生号选出来。HAVING短语指定选择组的条件，只有满足条件(即元组个数>3)的组才会被选出来。 WHERE子句与HAVING短语的根本区别在于:作用对象不同，WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组 3(4(3 多表连接查询 一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括内连接、外连接和交叉连接。 1(内连接 内连接是一种最常用的连接类型，使用内连接是，如果两个表的相关字段满足连接条件则从这两个表中提取数据并组合成新的记录。 当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。例如，可以都是字符型，或都是日期型;也可以一个是整型，另一个是实型，整型和实型都是数值型，因此是可比的。但若一个是字符型，另一个是整数型就不允许了，因为它们是不可比的类型。 从概念上讲DBMS执行连接操作的过程是，首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组, 就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部扫描完毕后，再到表1中找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一 个元组, 就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操 作，直到表1全部元组都处理完毕为止。 例38查询每个学生及其选修课程的情况 学生情况存放在Student表中，学生选课情况存放在SC表中，所以本查询实际上同时涉及 Student与SC两个表中的数据。这两个表之间的联系是通过两个表都具有的属性Sno实现的。 要查询学生及其选修课程的情况，就必须将这两个表中学号相同的元组连接起来。这是一个 等值连接。完成本查询的SQL语句为: SELECT * FROM Student JOIN SC ON Student.Sno=SC.Sno; 如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了 所有不重复的属性列，则称之为自然连接 例39自然连接Student和SC表 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student INNER JOIN SC ON Student.Sno=SC.Sno; 在本查询中，由于Sname、 Ssex、 Sage、Sdept、Cno和Grade属性列在Student与SC表中 是唯一的，因此引用时可以去掉表名前缀。而Sno在两个表都出现了，因此引用时必须加上 表名前缀。该查询的执行结果不再出现SC.Sno列。 例40 查询选修2号课程且成绩在90分以上的所有学生 SELECT Student.Sno, Sname FROM Student INNER JOIN SC ON Student.Sno=SC.Sno WHERE SC.Cno='2' AND SC.Grade>90; 例41 查询每个学生及其选修的课程名其及成绩 SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student S INNER JOIN ON S.Sno=SC.Sno JOIN Course C ON SC.Cno=C.Cno; 2.自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表 的自身连接。 例42查询与刘晨在同一个系学习的学生的姓名和所在系 为清楚起见，我们可以为Student表取两个别名，一个是S1，另一个是S2，也可以在考虑问 题时就把Course表想成是两个完全一样表，一个是S1表，另一个是S2表。如下所示: 完成该查询的SQL语句为: SELECT S2.Sname, S2.Sdept FROM Student as S1 JOIN Student as S2 ON S1.Sdept=S2.Sdept WHERE S1.Sname=‘刘晨’AND S2.Sname!=‘刘晨’; 3(外连接 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例35和例36的结 果表中没有关于95003和95004两个学生的信息，原因在于他们没有选课，在SC表中没有相 应的元组。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，若 某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外 连接(Outer Join)。 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno=SC.Sno; Student.Sno Sname Ssex Sage Sdept Cno Grade ----------- ------- ------ ------ ------- ------ ------- 95002 刘晨 女 19 信息系 3 80 95003 王名 女 18 数学系 NULL NULL 95004 张立 男 18 信息系 NULL NULL 右连接同理 3(4(4 子查询 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个 查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。 例如: SELECT Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno='2'); 在这个例子中，下层查询块 SELECT Sno FROM SC WHERE Cno='2'是嵌套在上层查询块 SELECT Sname FROM Student WHERE Sno IN 的WHERE条件中的。 上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。SQL 语言允许多层嵌套查询。即一个子查询中还可以嵌套其它子查询。需要特别指出的是，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句永远只能对最终查询结果排序。 嵌套查询的求解 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是 SQL(Structurred Query Language)中“结构化”的含义所在。 1(带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。 例43查询与“刘晨”在同一个系学习的学生 ?确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname='刘晨'; ?查找所有在该系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept='IS'; 结果为: Sno Sname Sdept ------- ------- ------- 95001 刘晨 计算机 95004 张立 计算机 分步写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN SELECT Sdept FROM Student WHERE Sname='刘晨'; 例44查询选修了课程名为'数据库基础'的学生学号和姓名 SELECT Sno, Sname FROM Student WHERE Sno IN SELECT Sno FROM SC WHERE Cno IN SELECT Cno FROM Course WHERE Cname='数据库基础' Sno Sname ------- -------- 95001 李勇 95002 刘晨 本查询同样可以用连接查询实现: SELECT Sno, Sname FROM Student JOIN SC ON Student.Sno=SC.Sno JOIN Course ON SC.Cno=Course.Cno WHERE Course.Cname='数据库基础'; 2.带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、 <、 =、 >=、<=、!=或<>等比较运算符。 前例中由于一个学生只可能在一个系学习，也就是说内查询刘晨所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其SQL语句如下: SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = SELECT Sdept FROM Student WHERE Sname='刘晨'; 需要注意的是，子查询一定要跟在比较符之后，下列写法是错误的: SELECT Sno, Sname, Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname='刘晨') = Sdept; 例48 见p50页 3(带有EXISTS谓词的子查询 EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“true"或逻辑假值“false"。 例49查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名亦无实际意义。 这类查询与我们前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值(在本例中是依赖于Student表的Sno值)，我们称这类查询为相关子查询(Correlated Subquery)。求解相关子查询不能象求解不相关子查询那样，一次将子查询求解出来，然后求解父查询。相关子查询的内层查询由于与外层查询有关，因此必须反复求值。从概念上讲，相关子查询的一般处理过程是: 首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值(即Sno值)处理内层查询，若WHERE子句返回值为真(即内层查询结果非空)，则取此元组放入结果表;然后再检查Student表的下一个元组;重复这一过程，直至Student表全部检查完毕为止。 例50查询所有未选修1号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨'; 3.5 数据数据更改功能 3.5.1数据插入 1. 插入单个元组 插入单个元组的INSERT语句的格式为: INSERT INTO <表名> [(<属性列1>[,<属性列2>...]) VALUES (<常量1> [,<常量2>]...) (1) 如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值。否则会出错。 (2) 如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。 例1将一个新学生记录(学号:9521105;姓名:陈冬;性别:男;所在系:信息系;年龄:18岁)插入Student表中 INSERT INTO Student VALUES ('95020', '陈冬', '男', '信息系', 18); 例2插入一条选课记录('95020'，'1') INSERT INTO SC (Sno, Cno) VALUES ('95020', '1'); 新插入的记录在Grade列上取空值。 补充:2.插入子查询结果 子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件，也可以嵌套在INSERT语句中，用以生成要插入的数据。 插入子查询结果的INSERT语句的格式为: INSERT INTO <表名> [(<属性列1> [,<属性列2>...]) 其功能是以批量插入，一次将子查询的结果全部插入指定表中。 例3对每一个系，求学生的平均年龄，并把结果存入数据库 对于这道题，首先要在数据库中建立一个有两个属性列的新表，其中一列存放系名，另一列存放相应系的学生平均年龄。 CREATE TABLE Deptage (Sdept CHAR(15) Avgage SMALLINT); 然后对数据库的Student表按系分组求平均年龄，再把系名和平均年龄存入新表中。 INSERT INTO Deptage(Sdept, Avgage) SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept; 3.5.2 更新数据 其语句的一般格式为: UPDATE <表名> SET <列名>=<表达式> [,<列名>=<表达式>]... [WHERE <条件>]; 其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。 1.有条件更新 例1将学生95001的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno='95001'; 2.无条件更新 例2将所有学生的年龄增加1岁 UPDATE Student SET Sage=Sage+1; 3.带子查询的修改语句 子查询也可以嵌套在UPDATE语句中，用以构造执行修改操作的条件。 例3将计算机系全体学生的成绩加5分 UPDATE SC SET GRADE=GRADE+5 WHERE SNO IN (SELECT SNO FROM Student WHERE Sdept=’计算机系’); 请同学们用多表连接来实现上述操作 3.5.3 删除数据 删除语句的一般格式为: DELETE FROM <表名> [WHERE <条件>]; DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句， 表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数 据，而不是关于表的定义。 1.有条件删除 例1 删除学号为95019的学生记录 DELETE FROM Student WHERE Sno='95019'; DELETE操作也是一次只能操作一个表 2.无条件删除 例2 删除所有的学生选课记录 DELETE FROM SC; 这条DELETE语句将使SC成为空表，它删除了SC的所有元组。 3.带子查询的删除语句 子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。 例3 删除计算机系所有学生的选课记录 DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno); 同样也请同学们用多表连接来实现上述操作