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

数据库原理与应用,第三章 关系数据库标准语言SQL,2,问答及课堂讨论,1.假如要建立一个数据库，如何建立? 2.数据库名字是什么? 3.可以不可以使用一张表来存放这些数据?为什么?好处和坏处各是什么? 4.要不要规范化,如何规范化? 5.缺省值如何设置? 6.完整性如何设置? 7.最后结果应该是怎么样的?,3,本章导读,1、有关数据表的操作，包括表的创建、修改、删除和建立索引等操作以及表中数据维护的有关操作和方法； 基本掌握在SQL Server2005中使用 T-SQL语句对表的操作和表的数据操作，包括创建表、删除表、对表中字段建立索引、向表中增加数据、修改数 据、删除数据等。 2、如何从SQL Server2005中中使用SELECT语句查询数据的方法； 基本掌握单表查询、连接查询、嵌套查询和集合查询，使用SELECT语句的条件子句、排序子句和分组子句。 3、视图的概念和优点，并结合实例介绍了在SQL Server 2005中如何实现创建、修改和管理视图； 掌握在SQL Server2005中如何创建、修改和管理视图。,4,第3章 关系数据库标准语言 SQL,3.1 SQL概述及特点 3.2 学生-课程 数据库 3.3 SQL的数据定义功能 3.4 SQL的数据查询功能 3.5 SQL的数据更新功能 3.6 视图 3.7 小结,5,3.1 SQL概述及特点 3.2 学生-课程 数据库 3.3 SQL的数据定义功能,6,3.1 SQL概述及特点,1. SQL的主要功能 (1) 数据定义功能（DDL） 定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。 (2) 数据操纵功能（DML） 包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。 (3) 数据控制功能（DCL） 通过对数据库用户的授权和收权命令来实现有关数据的存取控制，保证数据库的安全性；约束条件实现完整性。,7,2. SQL的特点,1) SQL具有自主式和嵌入式两种形式。 2) SQL具有语言简洁、易学易用的特点。 3) SQL支持三级模式结构。 全体基本表构成了数据库的模式。 视图和部分基本表构成了数据库的外模式。 数据库的存储文件和它们的索引文件构成了关系数据库的内模式。,8,3.1 SQL概述,9,3.1 SQL概述,10,3.2 学生-课程 数据库,学生-课程模式 S-T : 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade),Student表,11,Course表,12,SC表,13,3.3 SQL的数据定义功能,包括：定义基本表、定义视图、定义数据库，基本语句,14,3.3 SQL的数据定义功能,相关约定： 1、语句格式约定符号 :实际语义；:任选；或|为必须其一； ，n前面的项可以重复多次； -:行注释符 /*/：块注释符 2、一般语法规定 数据项分隔符“，”；字符串常数：“”; 3、SQL特殊语法规定 关键字大写；结束符为；采用格式化书写；,15,创建和管理数据库(T-SQL),CREATE DATABASE数据库名 ON PRIMARY(NAME =逻辑数据文件名， FILENAME= 操作数据文件路径和文件名 ，SIZE=文件长度 ，MAXSIZE=最大长度 ，FILEROWTH=文件增长率)，n LOG ON (NAME=逻辑日志文件名， FILENAME= 操作日志文件路径和文件名 ，SIZE=文件长度 )，n FOR RESTORE 子句中：PRIMARY指明主文件名；SIZE说明文件的大小，数据库文件最小为1MB，默认值为3MB；FILEROWTH说明文件的增长率，默认值为10%。FOR RESTORE子句说明重建一个数据库，该重建的数据库用于数据恢复操作。,16,示例,create database school on (name=school_dat, filename=“c:school.mdf“, size=5, maxsize=10 ) log on ( name=school_log, filename=“c:school.ldf“, size=3, maxsize=10 ),17,进入查询分析器,1.选择数据库,2.选择新建查询,18,打开查询分析器的第二种方式,直接点击,19,3.3 SQL的数据定义功能,3.3.1 基本表的定义和维护功能 1. 定义基本表 定义基本表语句的一般格式为： CREATE TABLE 库名表名( 列名数据类型列级完整性约束条件 ，列名数据类型列级完整性约束条件 ，n ，表级完整性约束条件，n )；,注意：默认情况下，SQL语言不区分大小写。,20,(1) SQL支持的数据类型,21,：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件 ：涉及一个或多个属性列的完整性约束条件,3.3 SQL的数据定义功能,22,(1) 例题,例 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。,USE SCHOOL CREATE TABLE Student ( Sno char(5) NOT NULL UNIQUE, Sname char(20) UNIQUE, Ssex char(1) default 男, Sage int default 20, Sdept char(15) ),23,例题 （续）,24,(2) 列级完整性的约束条件,针对属性值设置的限制条件。 1) NOT NULL或NULL约束。NOT NULL约束不允许字段值为空，而NULL约束允许字段值为空。 2) UNIQUE约束。UNIQUE约束是惟一性约束，即不允许列中出现重复的属性值。 3) DEFAULT约束。即将列中使用频率最高的属性值定义为default约束中的默认值，可以减少数据输入的工作量。 CONSTRAINT约束名 DEFAULT默认值 使用格式： DEFAULT 常量 如：Sage Int default 20 Sage Int Constraint DF_Sage default 20,25,4) CHECK约束。约束条件表达式直接跟在列定义后面 检查约束，check约束通过约束条件表达式设置列值应满足的条件。 格式：CONSTRAINT约束名CHECK约束条件表达式 如： CRATE TABLE table1 （ 性别 char(2) constraint c1 check (性别 in （男，女）), - 性别 char(2) check (性别 in （男，女）), 不指定约束名 ）,(2) 列级完整性的约束条件,26,(3) 表级完整性约束条件,涉及到关系中多个列的限制条件。 1) UNIQUE约束。惟一性约束。 2) PRIMARY KEY约束。定义主码，保证惟一性和非空性。 格式为： CONSTRAINT约束名PRIMARY KEY（列组） 如：constraint c2 primary key (学号，课程号),27,(3) 表级完整性约束条件,3) FOREIGN KEY约束。用于定义外码约束，定义参照完整性。 CONTRAINT约束名FOREIGN KEY(外码) REFERENCES被参照表名(与外码对应的主码名) 如： constraint c3 foreign key(课程号) REFERENCES 课程（课程号）；,28,4) CHECK约束。约束条件表达式在列定义完成之后。 检查约束，check约束通过约束条件表达式设置列值应满足的条件。 格式： CONSTRAINT 约束名CHECK约束条件表达式 如： CRATE TABLE table1 （ 性别 char(2) , 年龄 int, constraint c1 check (性别 in （男，女）) ）,(3) 表级完整性的约束条件,29,【例3-1】建立基本表： 学生(学号，姓名，年龄，性别，所在系)； 课程(课程号，课程名，先行课)； 选课(学号，课程号，成绩).,CREATE TABLE 学生(学号 CHAR(5) NOT NULL UNIQUE, 姓名 CHAR(8) NOT NULL, 年龄 SMALLINT DEFAULT 20, 性别 CHAR(2) CHECK(性别 =男 or 性别 =女), 所在系 CHAR(20) ),列级约束,30,CREATE TABLE 学生(学号 CHAR(5) NOT NULL UNIQUE, 姓名 CHAR(8) NOT NULL, 年龄 SMALLINT DEFAULT 20, 性别 CHAR(2), 所在系 CHAR(20), CONSTRAINT C2 CHECK(性别 in(男,女),或：先建立好表基本结构，然后用ALTER修改表结构，添加约束。如下： ALTER TABLE 学生 ADD CONSTRAINT C1 DEFAULT 男 FOR 性别,表级约束,31,CREATE TABLE 课程 (课程号 CHAR(5) PRIMARY KEY, 课程名 CHAR(20), 先行课 CHAR(5),CREATE TABLE 选课(学号 CHAR(5) , 课程号 CHAR(5) , 成绩 SMALLINT, CONSTRAINT C3 CHECK(成绩 BETWEEN 0 AND 100) , CONSTRAINT C4 PRIMARY KEY(学号,课程号) , CONSTRAINT C5 FOREIGN KEY(学号) REFERENCES 学生(学号), CONSTRAINT C6 FOREIGN KEY(课程号) REFERENCES 课程(课程号),32,2. 修改基本表和删除基本表,ALTER TABLE表名 ADD(新列名数据类型完整性约束 ，n ) DROP完整性约束名COLUMN 列名 ，n ALTER COLUMN(列名数据类型，n)； ：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定的完整性约束条件 ALTER COLUMN子句：用于修改列名和数据类型,33,(1) 使用ADD子句增加新列,【例3-2】向课程表Course中增加“学时”字段。 ALTER TABLE Course ADD 学时 SMALLINT； 注意：不论基本表中原来是否已有数据，新增加的列一律为空值。 (2) 使用ALTER子句修改列的原定义（如何删除列？） 【例3-3】将年龄的数据类型改为半字长整数。 ALTER TABLE Student alter column Sage smallint 注意：修改原有的列定义有可能会破坏已有数据。,34,(3) 使用DROP子句删除指定的完整性约束条件,【例3-4】删除学生表中对年龄的默认值的定义。 ALTER TABLE Student DROP C1; 【例3-5】 删除学生姓名必须取唯一值的约束。 ALTER TABLE Student DROP UQ_Student_Sname； 删除基本表的一般格式为： DROP TABLE表名； 注意：将基本表删除，数据表上的索引都删除 表上的视图往往仍然保留，但无法引用,DROP TABLE Student ；,【例3-6】 删除Student表,35,3.3.2 索引的定义和维护功能,1. 索引的作用 1) 使用索引可以明显地加快数据查询的速度。 2) 使用索引可保证数据的惟一性。 3) 使用索引可以加快连接速度。 2. 建立索引的原则 1) 索引的建立和维护由DBA和DBMS完成。 2) 大表应当建索引，小表则不必建索引。 3) 对于一个基本表，不要建立过多的索引。 4) 根据查询要求建索引。,36,3. 建立和删除索引的格式,建立索引格式为： CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX索引名 ON表名(列名次序,列名次序)； 删除索引格式为： DROP INDEX 表名|视图名.索引名； 【例3-7】为学生_课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立索引；课程表按课程号升序建聚集（簇）索引；选课表按学号升序和课程号降序建惟一索引。 CREATE UNIQUE INDEX IX_Sno ON 学生(学号)；(默认为升序) CREATE CLUSTERED INDEX IX_Cno ON 课程(课程号)； CREATE UNIQUE INDEX IX_SC ON 选课(学号 ASC，课程号 DESC)；,37,3.4 SQL的数据查询功能,38,3.4 SQL的数据查询功能,3.4.1 SELECT语句介绍 1. SELECT语句的语法 SELECT目标列组 FROM数据源 WHERE元组选择条件 GROUP BY分列组HAVING 组选择条件 ORDER BY排序列1排序要求1 ，n；,39,语法说明,(1) SELECT子句：指明目标列（字段、表达式、函数表达式、常量）。两个基本表中相同的列名表示为：表名.列名 (2) FROM子句：指明数据源。表间用“，”分割。数据源不在当前数据库中，使用“数据库名.表名”表示。一表多用，用别名标识。定义表别名：表名别名 (3) WHERE子句：元组选择条件。 (4) GROUP BY子句：结果集分组。当目标列中有统计函数，则统计为分组统计，否则为对整个结果集统计。子句后带上HAVING子句表达组选择条件（带函数的表达式）。 (5) HAVING短语：筛选出只有满足指定条件的组。 (6) ORDER BY子句：对查询结果表排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。,40,2. SELECT语句的操作符,(1) 算术操作符 +（加号）、（减号）、*（乘号）和 /（除号）。 (2) 比较操作符 =（等于）、（大于）、=（大于等于）、!=（不等于）、（小于大于）、!（不大于）和 !（不小于），共9种操作符。,41,(3) 逻辑操作符,42,(4) 组合查询操作符和其他SQL操作符,是针对传统关系运算的操作符 1) UNION：并查询，并在结果集中去掉重复行。 2) MINUS：差查询操作。 3) INTERSECT：交查询操作。,使用格式： 组合操作符 ,43,4) *：取全部字段。格式为： * 或表名.* 5) ALL：全部。保留重复值（有统计函数时要求计算重复值）。 格式为： ALL字段或 ALL字段组 6) DISTINCT：去掉重复值。在结果集中去掉重复值，或在统计函数中不计重复值。 格式为：DISTINCT字段或 DISTINCT字段组,44,3.4.2 SQL的查询实例及分析,学生课程库结构为： 学生(sno，sname，sage，ssex,sdept)； 课程(cno，cname，cpno,ccredit)； 选课(sno，cno，grade). 1. 简单查询：查询过程中只涉及到一个表的查询语句，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组,45,课程表,学生表,46,选课表,47,1 比较大小,【例3-9】求数学系学生的学号和姓名。 SELECT sno,sname FROM 学生 WHERE Sdept=数学系; 【例3-10】求选修了课程的学生学号。 SELECT DISTINCT sno FROM 选课; DISTINCT：消除取值相同的记录。,48,例子,【例3-11】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。 SELECT sno,grade FROM 选课 WHERE cno=C1 ORDER BY grade DESC,sno ASC; order by：按某种顺序显示。使用格式： order by asc|desc， , asc：按升序排序； desc：按降序排序。,49,2、确定范围,【例3-12】求选修课程C3且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.8输出。 SELECT sno,grade*0.8 FROM 选课 WHERE cno = C3 AND Grade BETWEEN 80 AND 90; between and：查找值在指定范围内的元组。,50,求全体学生的姓名及其出生年份。,SELECT Sname 姓名,2010-Sage 出生年份 FROM 学生,目标列可以是表中的属性列，也可以是表达式； 用户可以通过制定别名来改变查询结果的列标题。,51,3 确定集合 4字符匹配,【例3-13】求数学系或计算机系姓张的学生的信息。 SELECT * FROM 学生 WHERE Sdept IN (数学系,计算机系) AND Sname LIKE 张%; In: 用来查找值属于指定集合的元组。格式： 列名 in (常量1，常量2，常量n) Like：用于查找指定列中与匹配串常量匹配的元组。匹配串是一种特殊的字符串，它可以包含普通字符，还可以包含通配符。 通配符：_(下划线)：匹配任意一个字符。 %(百分号)：匹配0个或多个字符。,52,SQL Server 中四种匹配符的含义,% 代表零个或者多个任意字符 _ 代表一个任意字符 指定范围内的任意单个字符 不在指定范围内的任意单个字符 带有匹配符的字符串必须使用引号引起来，例如下面的示例: LIKE BR% 返回以“BR”开始的任意字符串。 LIKE Br% 返回以“Br”开始的任意字符串。 LIKE %een 返回以“een”结束的任意字符串。 LIKE %en% 返回包含“en”的任意字符串。 LIKE _en 返回以“en”结束的三个字符的字符串。 LIKE CK% 返回以“C”或者“K”开始的任意字符串。 LIKE S-Ving 返回长为四个字符的字符串，结尾是“ing”，开始是从S到V LIKE Mc% 返回以“M”开始且第二个字符不是“c”的任意字符串,53,1、如果用户要查找的数据中本身就包含了通配符，如SQL_Mail，就需要使用转义字符来区分通配符和实际存在的字符。其格式如下： LIKE 字符匹配串 ESCAPE 转义字符 如：查找对象名称为SQL_M开头，il结尾，中间有一个不确定字符的对象 SELECT * FROM objects WHERE object_name LIKE SQL#_M_il ESCAPE # 说明：这里使用了两个下划线符号，前一个由于有ESCAPE字符作标识，因而被认为是实际存在的下划线符号字符；后一个没有此标识，故被作为通配符。,54,2、使用来将通配符指定为普通字符： 如： SELECT * FROM object WHERE column1 LIKE %54% 它将返回所有包含54%的字符串。,55,查询名字以张_开头的3个字符的学生信息,SELECT * FROM 学生 WHERE sname LIKE 张#_ ESCAPE # SELECT * FROM 学生 WHERE sname LIKE 张_,56,5 涉及空值的查询,【例3-14】求缺少了成绩的学生的学号和课程号。 SELECT Sno,Cno FROM 选课 WHERE Grade IS NULL; Is null：判断取值为空。 is Not null：判断取值不为空。,6 多重条件查询,AND OR 【例3-13】求数学系或计算机系姓张的学生的信息。 SELECT * FROM 学生 WHERE Sdept IN (数学系,计算机系) AND Sname LIKE 张%; 等价于SELECT * FROM 学生 WHERE （Sdept=数学系 OR Sdept=计算机系） AND Sname LIKE 张%;,57,ORDER BY子句,用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC)排列，默认值为升序。 【例3-15】查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。 SELECT Sno,Grade FROM SC WHERE Cno=3 ORDER BY Grade DESC;,58,聚集函数,COUNT(*) COUNT(DISTINCT|ALL) SUM(DISTINCT|ALL) AVG(DISTINCT|ALL) MAX(DISTINCT|ALL) MIN(DISTINCT|ALL) DISTINCT，表示在计算时要取消指定列中的重复值。 不用则表示不取消重复值。,59,例3.41 查询学生总人数 SELECT COUNT（*） FROM Student; 例3.42 查询选修了课程的学生人数 SELECT COUNT（DISTINCT Sno） FROM Student; 一个学生如果选修多门课，为避免重复计算学生人数，用DISTINCT,60,例3.43 计算选修1号课程的学生平均成绩 SELECT AVG（Grade) FROM SC WHERE Cno=1; 例3.44 查询选修1号课程的学生最高分数 SELECT MAX（Grade) FROM SC WHERE Cno=1;,61,例3.45 查询学生201215012选修课程的总学分数 SELECT SUM（Ccredit) FROM SC,Course WHERE Sno=201215012 AND SC.Cno=Course.Cno 注意：WHERE不能用聚集函数作为条件表达式。,62,GROUP BY 子句,将查询结果按某一列或者多列的值分组，值相等的为一组。 分组后聚集函数将作用于每一个组，即每一个组都有一个函数值。 例3.46 求各个课程号及相应的选课人数。 SELECT Cno，COUNT（Sno) FROM SC GROUP BY Cno；,63,例3.47 查询选修了三门以上课程的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT（*）3 这里先用GROUP BY子句按Sno进行分组，在用聚集函数COUNT对每一组计数，HAVING短语给出了选择组的条件，3表示这个学生选修的课程超过3门,64,例3.48 查询平均成绩大于等于90分的学生学号和平均成绩。 SELECT Sno,AVG(Grade) FROM SC WHERE AVG(Grade)=90 GROUP BY Sno; 错误 SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)=90;,65,66,2. 连接查询,连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接。 (1) 等值连接和非等值连接 表名1.列名1比较运算符表名2.列名2 比较运算符：=、=、= 和!=；列名称为连接字,【例5-15】查询每个学生的情况以及他(她)所选修的课程。 SELECT 学生.*,选课.* FROM 学生,选课 WHERE 学生.sno=选课.sno;,67,(2) 自身连接在一个表内进行自身的连接 例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。,课程的先行关系链为：C5C4C3C2C1， 课程的间接关系链为：C5C3C1。,68,【例3-18】查询每一门课的间接先行课。 SELECT A.cno,A.cname,B.cpno FROM 课程 A,课程 B WHERE A.cpno=B.cno,A,B,结果,69,(3) 外部连接,左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。左外部连接符号为“*=”，右外部连接符号为“=*”。外部连接中不匹配的分量用NULL表示。,70,连接的结果集,71,【例3-19】用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句,内连接： SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门= 部门.部门号； 左外部连接： SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门*= 部门.部门号； 右外部连接： SELECT 职工.*，部门名称，电话 FROM 职工，部门 WHERE 职工.所在部门 =*部门.部门号；,左外连接,右外连接,等价于： SELECT 职工.*,部门名称,电话 FROM 职工 LEFT JOIN 部门 ON （职工.所在部门=部门.部门号）,72,外连接,SELECT A.cno,A.cname,B.cpno FROM 课程 A,课程 B WHERE A.cpno=*B.cno,SELECT A.cno,A.cname,B.cpno FROM 课程 A,课程 B WHERE A.cpno*=B.cno,73,3. 嵌套查询（又称子查询）,嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,书写：上层 下层 处理：下层 上层,74,例3-20：查询与刘晨在同一个系的学生。,查询过程： （1）确定刘晨所在的系，即执行子查询； SELECT Sdept FROM 学生 WHERE Sname=刘晨,（2）在子查询的结果中查找所有在此系学习的学生。 SELECT * FROM 学生 WHERE sdept IN (计算机系) SELECT * FROM 学生 WHERE sdept=计算机系,结果为：计算机系,(1) 使用In（或 not In）操作符的嵌套查询,75,SQL查询代码： SELECT * FROM 学生 WHERE sdept IN (SELECT sdept FROM 学生 WHERE sname=刘晨);,思考：如果在查询结果中不出现刘晨同学，应如何修改代码？,SELECT * FROM 学生 WHERE sdept = (SELECT sdept FROM 学生 WHERE sname=刘晨);,76,SELECT * FROM 学生 WHERE sname!=刘晨 and sdept = (SELECT sdept FROM 学生 WHERE sname=刘晨);,77,(1) 使用IN操作符的嵌套查询 【例3-20】求选修了数据结构的学生学号和姓名。 执行步骤： （1）在课程表中求出数据库课程的课程号； （2）根据找出的课程号在选课表中找出学了这门课程的学号； （3）根据学号在学生表中找出其姓名 SELECT sno,sname FROM 学生 WHERE sno IN ( SELECT sno FROM 选课 WHERE cno IN ( SELECT cno FROM 课程 WHERE cname=数据库);,找出选修了数据结构的学号,找出这些学号对应的学生信息,找出选修了数据库的课程号,可用“=”代替,不可用“=”代替,78,该题也可以使用下面的连接查询表达。 SELECT 学生.Sno,Sname FROM 学生,课程,选课 WHERE 学生.Sno=选课.Sno AND 课程.Cno =选课.Cno AND 课程.Cname=数据库; 注： 子查询的限制 不能使用ORDER BY子句 层层嵌套方式反映了 SQL语言的结构化 有些嵌套查询可以用连接运算替代,79,(2) 使用比较符的嵌套查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。 与ANY或ALL谓词配合使用,注意：使用嵌套查询进行比较测试时，要求嵌套 语句必须是返回单值的查询语句,80,【例3-21】求C1课程的成绩高于刘晨的C1课程成绩的学生学号和成绩。,分析： （1）在学生表中找出刘晨的学号； （2）在选课表中找出刘晨的C1课程的成绩； （3）最后在选课表中求出C1课程成绩大于刘晨的学生学号和成绩。,使用2层嵌套： 第一层嵌套使用（大于）操作符号； 第二层嵌套使用（等于）操作符号,81,SELECT sno,Grade FROM 选课 WHERE cno=C1 AND Grade ( SELECT Grade FROM 课程 WHERE cname=C1 AND Sno= (SELECT Sno FROM 学生 WHERE sname=刘晨),找出大于这个成绩的学生学号、成绩,找出刘晨的C1课程的成绩,找出刘晨的的学号,可用“in”代替,82,In操作符与比较操作符的区别,In操作符用于一个值与多个值的比较； 比较操作符用于一个值与另一个值之间的比较，当比较符后面的值需要通过查询才能得到时，就需要使用比较符嵌套查询。,83,(3) 使用ANY或ALL操作符的嵌套查询,格式为：字段比较符ANY|ALL子查询,84,【例3-22】求其他系中比计算机系某一学生年龄小的学生。 SELECT * FROM 学生 WHERE Sage 计算机系;,找出计算机系学生年龄,找出比其中一个年龄小的学生,【例3-23】求其他系中比计算机系学生年龄都小的学生。,SELECT * FROM 学生 WHERE Sage 计算机系;,85,(4) 使用EXISTS操作符的嵌套查询,【例3-24】求选修了C2课程的学生姓名。 SELECT Sname FROM 学生 WHERE EXISTS (SELECT * FROM 选课 WHERE 学生.Sno=Sno AND Cno=C2),SELECT Sname,Cno FROM 学生, 选课 WHERE Cno=C2 AND 学生.Sno=选课.Sno,EXISTS代表存在量词 。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。,86,【例3-25】求没有选修C2课程的学生姓名。,SELECT Sname FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 学生.Sno=Sno AND Cno=C2),87,例子,【例3-26】查询选修了全部课程的学生的姓名 SELECT Sname FROM 学生 WHERE NOT EXISTS (SELECT * FROM 课程 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 学生.Sno=Sno AND 课程.Cno=Cno),88,【例3-27】求至少选修了学号为“0102”的学生所选修的全部课程的学生学号和姓名。 SELECT sno,sname FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课 选课1 WHERE 选课1.sno=0102 AND NOT EXISTS (SELECT * FROM 选课 选课2 WHERE 学生.Sno=选课2.Sno AND 选课1.Cno=选课2.Cno);,未选“0102” 所选的课的记录,不存在,89,【例3-30】求选修了C1课程但没有选修C2课程的学生学号。,SELECT 学号 FROM 选课 WHERE 课程号=C1 MINUS SELECT 学号 FROM 选课 WHERE 课程号=C2；,本例也可以用下面的EXISTS嵌套查询表示。,SELECT sno FROM 选课 选课1 WHERE cno=C1 AND NOT EXISTS (SELECT sno FROM 选课 选课2 WHERE 选课1.sno=选课2.sno AND 选课2.cno=C2)；,SQL Server没有Minus功能，可用not in代替，还可用not exists，二者语法稍有不同。,90,课堂练习,1.假设一个数据库中有关系： 产品关系 P（pno，pn，pr，ps），属性的含义依次为产品号、品名、单价和供应商；分别用关系代数表达式和SQL语句写出下列查询： 检索供应商“希望”公司产品的产品号、产品名和单价。 2假设一个数据库中有关系： 订单关系 R（rno，cno，pno，rd，qty）其属性的含义依次为订单号、客户号、产品号、日期和数量。规定一张订单只能订购一种产品。 用SQL语句写出下列查询： 检索每种产品的订单个数和订购产品的总量。,91,1. 关系代数：pno,pn,pr(ps=希望(P) SELECT pno,pn,pr FROM P WHERE ps=希望 2.SELECT count(rno),sum(qty) FROM R GROUP BY pno,92,4. 使用分组和SQL函数查询,【例3-31】求学生的总人数。 SELECT COUNT (*) FROM 学生；,93,例子,【例3-32】求选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM 选课,【例3-33】求课程和选修该课程的人数。,【例3-34】求选修课超过3门课的学生学号。,SELECT cno,COUNT(cno) FROM 选课 GROUP BY Cno,SELECT Sno FROM 选课 GROUP BY Sno HAVING COUNT(*)3,94,【例3-35】求“C1”课程的平均成绩。 SELECT Cno,AVG(Grade) FROM 选课 WHERE Cno=C1 GROUP BY Cno; 【例3-36】求学生“0101”的总成绩。 SELECT SUM(ALL(Grade) FROM 选课 WHERE Sno=0101 【例3-37】求学生“0101”的平均成绩。 SELECT AVG(ALL(Grade) FROM 选课 WHERE Sno=0101 ?思考：不用ALL会有什么变化？若ALL换成DISTINCT呢？,95,查询语句小结,SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY ， . HAVING ORDER BY ASC|DESC ， ASC|DESC ；,96,目标列表达式,目标列表达式格式 (1) . * (2) .，. ：由属性列、作用于属性列的SQL函数和常量的任意算术运算（+，-，*，/）组成的运算公式。,97,SQL函数格式,COUNT SUM AVG (DISTINCT|ALL ) MAX MIN COUNT (DISTINCT|ALL *),98,条件表达式格式,（1） ANY|ALL (SELECT语句),99,条件表达式格式,（2） NOT BETWEEN AND (SELECT (SELECT 语句) 语句),100,条件表达式格式,（3） (， ) NOT IN (SELECT语句),101,条件表达式格式,(4) NOT LIKE (5) IS NOT NULL (6) NOT EXISTS (SELECT语句),102,条件表达式格式,（7） AND AND OR OR,103,3.5 SQL的数据更新功能,104,3.5 SQL的数据更新功能,3.5.1 SQL的数据插入功能 两种插入数据方式 插入单个元组 插入子查询结果 1. 使用常量插入单个元组 格式为： INSERT INTO表名(属性列1，属性列2) VALUES (常量1，常量2)； 功能：将新元组插入指定表中。,105,例子,【例3-38】将一个新学生记录(学号：98010，姓名：张三，年龄：20，所在系：计算机系 )插入到学生表中。,INSERT INTO 选课 (sno,cno) VALUES (98010, C10),【例3-39】插入一条选课记录(学号：98010，课程号：C10，成绩不详)。,INSERT INTO 学生 VALUES (98010,张三,20,男,计算机系);,106,插入单个元组注意：,INTO子句 指定要插入数据的表名及属性列 属性列的顺序可与表定义中的顺序不一致 没有指定属性列：表示要插入的是一条完整的元组，且属性列与表定义中的顺序一致 指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句 提供的值必须与INTO子句匹配 值的个数 值的类型,107,2. 在表中插入子查询的结果集,格式为： INSERT INTO表名(属性列1，属性列2) 子查询；,CREATE TABLE 系平均年龄 (系名称 CHAR(20), 平均年龄 SMALLINT); INSERT INTO 系平均年龄 SELECT Sdept,AVG(Sage) FROM 学生 GROUP BY Sdept,【例5-40】求每个系学生的平均年龄，并把结果存入数据库中。,108,插入,DBMS在执行插入语句时会检查所插元组是否破坏表上 已定义的完整性规则 实体完整性 参照完整性 用户定义的完整性 对于NOT NULL约束的属性列是否提供了非空值 对于UNIQUE约束的属性列是否提供了非重复值 对于值域约束的属性列所提供的属性值是否在值域范围内,109,3.一次插入多条记录,用一条INSERT语句，插入多行（用SELECT 子句代替VALUES子句） 例子： INSERT INTO 学生 (sno,Sname ) SELECT 0112,aaa UNION SELECT 0113,bbb UNION SELECT 0114,ccc,110,3.5.2 SQL的数据修改功能,UPDATE表名 SET列名=表达式，列名=表达，n WHERE条件； 三种修改方式 修改某一个元组的值 修改多个元组的值 修改带子查询的语句,111,【例3-41】将学生表中全部学生的年龄加上2岁。,【例3-42】将选课表中的数据库课程的成绩乘以0.8。,UPDATE 学生 SET Sage=Sage+2,UPDATE 选课 SET grade= Grade*0.8 WHERE Cno= (SELECT Cno FROM 课程 WHERE Cname= 数据库 );,112,【例3-43】将学号为0105的学生的年龄改为21岁。,Update 学生 Set Sage=18 Where Sno=0105,【例3-44】将计算机系全体学生的成绩加5分。,Update 选课 Set Grade=Grade+5 Where Sno in(select Sno from 学生 where Sdept=计算机系),113,SET子句 指定修改方式： 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组,114,DBMS在执行修改语句时会检查修改操作是否破坏表 上已定义的完整性规则： 实体完整性 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束,115,3.5.3 SQL的数据删除功能,格式： DELETE FROM表名 WHERE条件；,1、无条件删除； 2、有条件删除； （1）基于本表条件的删除； （2）基于其它表条件的删除；,116,无条件删除,【例3-45】删除所有系平均年龄的记录。,Delete from 系平均年龄 删除完后， 系平均年龄表是一个空表。,117,有条件删除,1、基于本表条件的删除,DELETE FROM 学生 WHERE Sdept=数学系,【例3-46】删除数学系的学生记录。,118,【例3-47】删除计算机系的学生记录及选课记录。 DELETE FROM 选课 WHERE Sno IN (SELECT Sno FROM 学生 WHERE Sdept=计算机系) DELETE FROM 学生 WHERE Sdept=计算机系,改为：删除数学系的不及格学生的选课记录呢？,119,3.6 视 图,120,3.6 视 图,基于视图的操作 查询 删除 受限更新 定义基于该视图的新视图,视图的特点 虚表，是从一个或几个基本表（或视图）导出的表 只存放视图的定义，不存放视图对应的数据 基表中的数据发生变化，从视图中查询出的数据也随之改变,121,3.6 视 图,3.6.1 定义视图 3.6.2 查询视图 3.6.3 更新视图 3.6.4 视图的作用,122,3.6.1 定义视图,建立视图 删除视图,123,视图的定义和维护功能,1. 视图的优点 1) 视图能够简化用户的操作。 2) 视图