第三章关系数据库标准语言 SQL

第三章关系数据库标准语言SQL 3.1 SQL的特征 3.2 SQL的数据定义 3.3 SQL的数据查询 3.4 SQL的数据更新本章小结

第三章关系数据库标准语言SQL 3.5 SQL的数据控制 3.6 嵌入式 SQL 3.7 动态 SQL 3.8 ODBC与JDBC 本章小结

3.1 SQL的特征 1. SQL的主要标准 SQL-86 SQL-89 SQL-92(SQL2) SQL-99(SQL3) 第三章关系数据库语言SQL

SQL-86。SQL的第一个标准是1986年10月由美国国家标准化组织(ANSI)公布的。SQL-86。SQL的第一个标准是1986年10月由美国国家标准化组织(ANSI)公布的。 • SQL-89。ANSI以后通过对SQL-86的不断修改和完善，于1989年第二次公布了SQL标准，即SQL-89，该标准增强了完整性的语言特征。 • SQL-92(SQL2)。1992年又公布了SQL-92标准，该标准增加了支持对远程数据库的访问，扩充了数据类型、操作类型、动态SQL等许多新的特征。 • SQL-99(SQL3)。完成于1999年的SQL-99修订本具有更高级的特征。引入了支持对象-关系DBMS模型的SQL，扩展了对象、递归、触发等许多新的特征，支持用户自定义函数、自定义数据类型。

2. SQL的功能特点 • 功能： • 数据定义 • 数据查询 • 数据操纵 • 数据控制

第三章关系数据库语言SQL 特点： • 综合统一非关系模型的数据语言不同模式有不同的数据定义语言及数据操纵语言。SQL语言则集数据定义、操纵和控制功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。

第三章关系数据库语言SQL • 高度非过程化非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。

第三章关系数据库语言SQL • 灵活的使用方式 • SQL语言既是自含式语言，又是嵌入式语言。 • 作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。

第三章关系数据库语言SQL • 简洁、通用、功能强 SQL语言功能极强，设计巧妙，语言简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词，如表3-1所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。

3.1 SQL的特征 SQL 视图1 视图2 基本表1 基本表2 基本表3 存储文件1 存储文件2 3. SQL的基本组成（1）SQL数据库层次结构术语对照：一般关系模型 SQL • 外模式-----视图（VIEW) • 模式-----基本表(TABLE) • 内模式-----存储文件（索引） • 元组--------行(ROW) • 属性--------列(COLUMN)

3.1 SQL的特征 • 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。　　 • 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 • 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。

（2）SQL语言的组成 数据定义语言（DDL）数据操纵语言（DML）数据控制语言（DCL）嵌入与会话规则（3）SQL的语句类型 SQL模式语句 SQL数据语句 SQL事务与控制语句 SQL连接、会话及诊断语句 3.1 SQL的特征 • 4. SQL的数据类型 • 预定义数据类型 • 构造数据类型 • 用户定义数据类型（UDT，User Defined Type）

3.1 SQL的特征 5.SQL环境（1）SQL模式与目录 SQL模式：基本表的集合。好处：允许在不同的SQL模式中出现同名的基表名或视图名。目录：SQL环境中所有模式的集合。定位基表的方式： <目录名>．<模式名>．<表名> （2）SQL环境 • 设置默认的目录和模式 • 设置用户身份

3.1 SQL的特征 （3）存储过程存储过程是存储在SQL服务器上的预编译好的一组为了完成特定功能的SQL语句集。通过指定存储过程的名字并给出参数来执行它。可分为两类： ◆ 系统存储过程：由系统自动创建，完成的功能主要是从系统表中获取信息。 ◆ 用户定义的存储过程：由用户为完成某一特定功能而编写的存储过程。使用存储过程的好处： ◆ 可减少网络流量。 ◆ 增强代码的重用性和共享性。 ◆ 加快系统运行速度。 ◆ 保证数据安全性。

3.2 SQL的数据定义 1. SQL模式的定义与撤销（1）SQL模式的定义 CREATE SCHEMA ＜模式名＞ AUTHRIZATION ＜用户名＞ [＜CREATE DOMAIN子句＞|＜CREATE TABLE子句＞|＜CREATE VIEW ＞|…… ] 例；CREATE SCHEMA Teaching_db AUTHRIZATION Hang；（2）数据库模式的删除 DROP SCHEMA ＜模式名＞ {CASCADE | RESTRICT} CASCADE(级联式) ： RESTRICT（约束式)：

3.2 SQL的数据定义 2. 表的建立和删除（1）表的建立命令格式： CREATE TABLE <表名>(<列名><数据类型> [列级完整性约束条件] [，<列名> <数据类型> [列级完整性约束条件]...) [,<表级完整性约束条件>];例：CREATE TABLE Student ( sno CHAR(5) NOT NULLUNIQUE， sname CHAR(20) NOT NULL， sex CHAR(1)， age INT ， dept CHAR(15) )；主码

3.2 SQL的数据定义 • 完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可定义在列级，也可以定义在表级。注意：例：定义P.76的三个表结构，并指定相应的数据完整性约束条件。分析 Student表：主码：sno 姓名：非空性别：男、女两值 Course表：课程名：非空外码：pcno 主码：cno SC表：主码：(sno,cno) 成绩：0—100 外码：sno,cno

3.2 SQL的数据定义 CREATE TABLE Student (sno CHAR (5), sname CHAR (8) NOT NULL, sex CHAR (2), age SMALLINT, dept CHAR (20), PRIMARY KEY(sno), CHECK sex IN (‘男’,‘女’) ); 列级完整性约束条件实体完整性约束条件用户自定义完整性约束条件

3.2 SQL的数据定义 CREATE TABLE Course (cno CHAR (4), cname CHAR (10) NOT NULL, pcno CHAR (4), credit SMALLINT, PRIMARY KEY (cno), FOREIGN KEY (pcno) REFERENCES Course(cno) ); 参照完整性约束条件

3.2 SQL的数据定义 CREATE TABLE SC (sno CHAR (5), cno CHAR (4) , grade SMALLINT, PRIMARY KEY (sno,cno), FOREIGN KEY (sno)REFERENCES Student(sno), FOREIGN KEY (cno)REFERENCES Course(cno), CHECK ((grade IS NULL) OR (grade BETWEEN 0 AND 100)) );

3.2 SQL的数据定义 （2）表的删除格式：DROP TABLE <表名> [CASCADE | RESTRICT] 基本表定义一旦删除，表中的数据、在此表上建立的视图、索引、触发器、断言都将自动被删除掉。RESTRICT确保只有不具有相关对象的表才能被撤销。例：DROP TABLE Student CASCADE；

3.2 SQL的数据定义 3.基本表的扩充和修改一般格式为： ALTER TABLE <表名>[ADD <新列名><数据类型>[完整性约束]][DROP<完整性约束名><完整性约束名>] [MODIFY<列名> <数据类型><数据类型>]; 其中<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。（1）在现存表中增加新列格式：ALTER TABLE <表名> ADD (<列名> <数据类型>，… )

3.2 SQL的数据定义 （2）删除已存在的某个列格式：ALTER TABLE <表名> DROP <列名> [CASCADE | RESTRICT] 例：ALTER TABLE Student DROP addr；（3）修改原有列的类型格式：ALTER TABLE <表名> MODIFY <列名> <类型>；例：ALTER TABLE Student MODIFY place CHAR(8)；

3.2 SQL的数据定义 （4）补充定义主码格式：ALTER TABLE <表名> ADD PRIMARY KEY (<列名表> ) （5）删除主码格式：ALTER TABLE <表名> DROP PRIMARY KEY

3.2 SQL的数据定义 • 修改基本表实例例向Student表增加“入学时间”列，其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 例将年龄的数据类型改为半字长整数 ALTER TABLE Student MODIFY Sage SMALLINT; 例删除关于学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno);

3.2 SQL的数据定义 4.索引的建立与删除建立索引的目的：基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。命令格式： CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名><次序>[ [，<列名><次序>]]…)；次序：升序（ASC，缺省）降序（DESC） UNIQUE: 每一个索引值只对应惟一的数据记录。 CLUSTER: 建立聚簇索引，即索引项的顺序与表中记录的物理顺序一致。

3.2 SQL的数据定义 注意：在一个基本表上最多只能建立一个聚簇索引。经常更新的列不宜建立聚簇索引。 • 所建索引放何处？ • 例: ①为Student表按学号升序建惟一聚簇索引。 ② 为SC表按学号升序和课程号降序建惟一索引。 ①CREATE UNIQUE CLUSTER INDEX Stno ON Student(Sno)； ②CREATE UNIQUE INDEX Scno ON SC(Sno ，Cno DESC)； • 删除索引一般格式为： DROP INDEX<索引名>； • 例： DROP INDEX Stno； • 删除索引时，系统会同时从数据字典中删去有关该索引的描述。

一般形式： SELECT <A> FROM <R> WHERE <F> GROUP BY <列名1> HAVING <条件表达式> ORDER BY <列名2> [ASC|DESC]; 基本语句含义: 根据WHERE中的F，从基表或视图R中找出满足条件的元组，再从中选出目标属性值形成结果表。 3.3 SQL的数据查询查询目标数据来源选择元组的条件将结果按<列名1>的值进行分组满足条件的组才予输出按<列名2>排序查询结果

3.3 SQL的数据查询 • 如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常在成组后再使用集函数。 • 如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。 • 如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。

3.3 SQL的数据查询 • 其中：[…]：表示其中的成分为任选项。 <…>：表示其中的成分由用户具体给定。 |：表示其中并列的成分只能择一。 • 查询目标： ALL：表示保留满足条件的所有元组（缺省）。 DISTINCT：表示去掉重复元组。目标列：可以为属性名、表达式、通配符‘*’（表示所有属性列）。

学生-课程数据库: Student(sno，sname，sex，age，dept) Course(cno，cname，pcno，credit) SC(sno，cno，grade) 3.3.1 单表查询一、选择表中的列例：查询所有学生的姓名、学号、所在系。 SELECT sname，sno，dept FROM Student （次序不同）; 3.3 SQL的数据查询－单表查询目标A 来源R 条件F sname sno dept ——————————— 王萧虎 200101 信息院 ……

3.3 SQL的数据查询－单表查询 • 查询经过计算的值 • SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 • 例4 查全体学生的姓名及其出生年份 SELECT Sname, 2006-Sage FROM Student; • 例5 查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, 'Year of Birth:', 1996-Sage, ISLOWER(Sdept) FROM Student;

3.3 SQL的数据查询－单表查询 来源R 目标A 条件F • 例：查全体学生的姓名及其出生年份。 SELECT sname，2007-age AS FROM Student ； Birthday 别名二、选择表中的行 1 消除取值重复的行 • 例：查考试成绩不及格的学号。 sname 2005-age ——————————— 王萧虎1987 …… Birthday SELECT sno FROM SC WHERE grade<60 ； DISTINCT

3.3 SQL的数据查询－单表查询 • 2. 查询满足条件的元组 • 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如下所示。常用的查询条件：查询条件谓词 • 比较比较运算符 • 确定范围 BETWEEN AND, NOT BETWEEN AND • 确定集合 IN, NOT IN • 字符匹配 LIKE, NOT LIKE • 空值 IS NULL, IS NOT NULL • 多重条件 AND, OR

3.3 SQL的数据查询－单表查询 • (1) 比较（=, <, >…）例查计算机系全体学生的姓名 SELECT Sname FROM Student WHERE Sdept = 'CS'; 例查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname, Sage FROM Student WHERE Sage <20; 或 WHERE NOT Sage>= 20;

3.3 SQL的数据查询－单表查询 (2)确定范围 (BETWEEN … AND … ) • 例:查询选002号课程且成绩在80--90的学生号。 SELECT sno FROM SC WHERE sno=‘002’ AND grade BETWEEN 80 AND 90；关系运算？ (3)确定集合(IN) • 例：查询信息院、数学系和计算机学院学生的姓名和性别。 SELECT sname，sex FROM Student WHERE dept IN ('信息院'，'数学系'，'计算机学院') • ? 查询不是这三个系的学生的姓名和性别。其他表示？ NOT

3.3 SQL的数据查询－单表查询 (4)字符匹配---近似查询，模糊查询格式：[NOT] LIKE ’<匹配串>’ 含义：是查找指定的属性列值与<匹配串>相匹配的元组。 • 其中匹配串可含：％：代表任意长度(可为0)的字符串。 _：代表任意单个字符。例：查所有姓刘或姓王的学生姓名、学号和性别。 SELECT sname，sno，sex FROM Student WHERE sname LIKE ‘刘％’ OR sname LIKE ‘王％’ ； ? 查询所有非姓刘或非姓王的学生姓名、学号和性别。 NOT NOT

3.3 SQL的数据查询－单表查询 • 例查姓“欧阳”且全名为三个汉字的学生的姓名 • SELECT Sname FROM Student WHERE Sname LIKE ‘欧阳__'; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟２个_。 • 例查名字中第二字为“阳”字的学生的姓名和学号 • SELECT Sname, Sno FROM Student WHERE Sname LIKE '__阳%'; • 注意：一个汉字要占两个字符的位置

3.3 SQL的数据查询－单表查询 格式2： LIKE ’<匹配串>’ ESCAPE ’<换码字符>’ • 若要查的串本身就含有％或_ ，则用ESCAPE ‘<换码字符>’对通配符进行转义。ESCAPE ’\’短语表示\为换码字符，这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。 • 例：查“DB_”开头且倒数第2个字符为 i 的课程情况。 SELECT * FROM Course WHERE cname LIKE ‘DB\_％i_’ ESCAPE ’ \’；

3.3 SQL的数据查询－单表查询 • 查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT * FROM Course WHERE Cname LIKE ’DB_%i__’ ESCAPE ’&’; 注意这里的匹配字符串’DB\_%i__’。第一个_前面有换码字符\，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符\，所以它们仍作为通配符。执行结果为： • Cno Cname Ccredit 8 DB_Design 4 10 DB_Programming 2 13 DB_DBMS Design 4 &

3.3 SQL的数据查询－单表查询 • (5) 涉及空值的查询 • 例某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号 SELECT Sno, Cno FROM SCWHERE Grade IS NULL; 注意这里的‘IS’不能用等号（‘=’）代替。 • 例查所有有成绩的记录的学生学号和课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;

3.3 SQL的数据查询－单表查询 条件 F 目标 A 来源 R 三、对查询结果排序 • 例:查询全体男学生的学号、系、年龄结果按所在的系升序排列，同一系中的学生按年龄降序排列。 Student表排序一排序二 SELECT sno, dept, age FROM Student WHERE sex=‘男’ ORDER BY dept，age DESC； Orderby的排序键应该是查询清单中的列名

3.3 SQL的数据查询－单表查询 例:查询女学生的总人数和平均年龄。 SELECT COUNT(sno),AVG(age) FROM Student WHERE sex=‘女’ 例:查询选修001号课程并及格的学生的最高分数、最低分及总分。 SELECT MAX(grade),MIN(grade),SUM(grade) FROM SC WHERE cno=‘001’ and grade>=60

3.3 SQL的数据查询－单表查询 五、对查询结果分组： GROUP BY 子句 • 将查询结果表按某一(多)列值分组，值相等的为一组。 • 目的：细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。例查询各个课程号与相应的选课人数 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

3.3 SQL的数据查询－单表查询 • 该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为： CnoCOUNT(Sno) ------ ---------- 1 22 2 34 3 44 4 33 5 48

3.3 SQL的数据查询－单表查询 • 如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。例：求每个学生（号）的平均成绩，并将其超过88分的输出。 SELECT sno，AVG( grade) FROM SC GROUP BY sno； WHERE ? HAVING AVG( grade) >88； • HAVING短语：在各组中选择满足条件的小组。 • WHERE子句：在表中选择满足条件的元组。

3.3 SQL的数据查询－单表查询 集函数在成组之前不计算，因此不能用于WHERE子句, 一般将简单条件写入WHERE。 HAVING子句的条件运算数之一是一个集函数。若HAVING子句无前导GROUPBY，选择清单中不能含有非集函数列。

3.3 SQL的数据查询－单表查询 练习：查询至少选修4门课程的学生学号。例：找出选课学生超过30人的课程的平均成绩及选课人数。 SELECT cno,AVG(grade),COUNT(*) AS st_number FROM SC GROUP BY sno HAVING st_number>=30

3.3 SQL的数据查询－单表查询 例：求学生关系中女生的每一年龄组（超过20人）有多少，要求查询结果按人数升序排列，人数相同时按年龄降序排列。 SELECT age,COUNT(sno) AS number FROM Student WHERE sex=‘女’ GROUP BY age HAVING number>20 ORDER BY number,age DESC

第三章 关系数据库标准语言 SQL