SQL --...

第四章 SQL语言--本章内容

SQL语言的基本特征

SQL数据定义功能

索引的建立和删除

SQL的查询功能

SQL数据操纵功能

嵌入式SQL动态SQL访问数据库

SQL语言的基本特征

一体化的特点

两种使用方式，统一的语法格式

高度非过程化

语言简洁、易学易用

SQL语言也支持关系数据库三级模式体系结构

外模式：视图+一些基本表

模式：基本表

内模式：存储文件(逻辑结构）

第四章 SQL语言—组成部分

数据定义语言DDL(Data Definition Language)– 定义关系模式、删除/修改关系模式

交互式数据操纵语言DML(Data Manipulation Language)– 查询、插入、删除、修改

索引与视图定义功能

嵌入式SQL事务控制功能 --第8章权限管理 – 第9章完整性 – 第10章

一体化的特点

数据库三级模式

外模式1 外模式2 外模式n

模式

内模式

数据库

应用A 应用B 应用C 应用D 应用E

视图

表

索引

模式/外模式映射

内模式/模式映射

样板模式

Sailors(sid: integer, sname: string, rating: integer, age: real)Boats(bid: integer, bname: string, color: string)Reserves(sid: integer, bid: integer, day: date)

样板数据库

Sid sname rating age sid bid day22 Dustin 7 45 22 101 10/10/9829 Brutus 1 33 22 102 10/10/9831 Lubber 8 55 22 103 10/8/9832 Andy 8 25 22 104 10/7/9858 Rusty 10 35 31 102 11/10/9864 Horatio 7 35 31 103 11/6/9871 Zorba 10 16 31 104 11/12/9874 Horatio 9 40 64 101 9/5/9885 Art 3 25 64 102 9/8/9895 Bob 3 63 74 103 9/8/98

Bid bname color101 Interlake blue102 Interlake red103 Clipper green104 Marine redSailors

Boats

Reserves


创建表SailorsCreate table Sailors Create table Sailors( sid char(11), ( sid char(11) primary key，sname char(20), sname char(20),rating int, rating int,age int, age int）primary key ( sid ) )

表级约束

列级约束

sid sname rating ageSailors


创建表BoatsCreate table Boats

( bid char(10),bname char(20),color char(10),primary key ( bid ) )

表级约束

bid bname colorBoats


创建表ReservesCreate table Reserves

( sid char(11),bid char(10),day datetime,primary key ( sid, bid ),foreign key(sid) references Sailors,foreign key(bid) references Boats )

数据类型（教材p89）

表级约束

sid bid dayReserves

基本表的修改与删除

修改

Alter table Sailors add sex char(3)

删除

Drop table Sailors把一个基本表的定义连同表上的所有记录、

索引以及由此基本表导出的所有视图都删除，并释放相应的存储空间

索引

当关系很大时，通过扫描所有关系中的所有元组来找出符合条件的元组代价太高

– 例如：选择IS系年龄大于22岁的学生

– Student关系中可能存在5000个元组，只有200个元组是大于22岁的。

– 代价大的方法：逐个评价5000个元组

– 改进的方法：直接评价200个元组

外模式外模式外模式

模式

内模式

数据库

应用应用应用应用

表

索引

视图索引

首先：Students记录按Sage来排序

简单的改进方法：二分法查找文件中的记录

问题：如果文件很大，二分法查找仍会带来巨大的执行代价。如何改善？

建立一个索引文件

索引

索引文件: 索引页的格式

一级索引结构

P0 K1 P1 K2 P2 … Kn Pn

P0 K1 P1 K2 P2 … Kn Pn

Page 1 Page 2 … Page n 数据文件

索引文件

索引

一级索引结构

P0 16 P1 22 P2 … 25 Pn

Page 1 Page 2 Page 3 … Page n 数据文件

索引文件

age

因为索引文件比数据文件小得多在索引文件上进行二分法搜索比在数据文件上搜索要快得多

索引的优点

关系属性A上的索引可以提高查找在A上具有某个特性值的

元组的效率索引--index

索引可以加快查找

索引的代价（时间、空间）

索引的维护和使用（系统）

SQL支持用户建立索引

索引建立策略

如果一个（组）属性经常在查询条件中出现

如果一个属性经常作为min或max等聚集函数的参数

在连接属性上

索引

唯一索引：unique每个索引值只对应一个唯一的数据记录

聚簇索引：cluster索引项的顺序和表中数据记录的物理顺序一致

学号

95001950029500395004

学生

95002 王 18 CS95004 李 17 IS95001 张 17 MA95003 赵 18 CS

年龄

17

18

学生

95001 张 17 MA95004 李 17 IS95002 王 18 CS95003 赵 18 CS

创建索引

给关系Students的Sage属性创建一个索引

CREATE INDEX ageIndex ON Students(Sage);

DBMS允许对多个属性创建一个索引

CREATE INDEX keyIndex ON SC(sno, cno);

索引的建立

在表Sailors的属性sid上建立一个唯一索引

create unique index in_u_sid on Sailors( sid ) 在表Boats的属性bid上建立一个聚簇索引

create cluster index in_c_bid on Boats( bid )

创建索引

在表Sailors的属性sname上建立一个索引

create index xsname on Sailors( sname ) 在表Boats的属性bname上建立一个索引

create index xbname on Boats( bname ) 在表Reverse的属性(bid,sid)上建立一个索引(组合索引）

create index xbidsid on Reverse( bid asc, sid desc )

asc为升序,desc为降序, 缺省为升序

索引的删除

索引的删除: Drop index 索引名

Drop index xsname索引可以加快查找，但不是索引越多越好

对某个属性使用索引能极大地提高对该属性上值的检索效率，使用到该属性时，可以加快连接操作。

对某个属性使用索引会使得对关系的插入、删除、修改变得复杂和费时。

访问和更新索引结构本身也需要磁盘操作。

SQL的查询功能

SQL查询语句的基本结构

SELECT [DISTINCT] 目标列

FROM 基本表 (视图) 范围变量名,….[WHERE 条件表达式][Group by 列名1 [having 分组表达式] ][Order By 列名2 asc | desc ];


模式

内模式

数据库


表

索引

视图

基本结构

三个子句

– Select: 对应关系代数的投影运算，列出查询结果中的属性

– From: 对应关系代数的笛卡尔集运算，列出表达式求值中

需扫描的关系

– Where:对应关系代数的选择谓词，包括一个作用在from子句中关系属性上的谓词。

Select A1, A2, …, An

From r1, r2, …, rm

[Where P]

属性

关系

谓词

πA1,A2,..,An(σP(r1 × r2 × … × rm))

样板模式

Students ( Sno, Sname, Ssex, Sage, Sdept)Courses ( Cno, Cname, Cpno, Credit)SC ( Sno, Cno, Grade)用SQL创建以上三个表

单表查询

查询内容仅涉及一个表

例子：查询全体学生的学号和姓名

select Sno, Sname 顺序无关性

from Students;

Students ( Sno, Sname, Ssex, Sage, Sdept)Courses ( Cno, Cname, Cpno, Credit)SC ( Sno, Cno, Grade)

单表查询

例子：查询全体学生的基本情况

select *from Students;例子：查询全体学生的姓名及出生年份

select Sname, 2009-Sagefrom Students;


单表查询

例子：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名

select Sname, ‘Year of birth: ’, 2009-Sage,islower(Sdept)

from Students;

Sname ‘Year of birth: ’ 2009-Sage islower(Sdept)

李勇 Year of birth: 1976 cs刘辰 Year of birth: 1977 is王敏 Year of birth: 1978 ma张立 Year of birth: 1978 is


示意答案

单表查询

例子：查询全体学生的姓名、出生年份


单表查询

例子：查询全体学生的姓名、出生年份

Students ( Sno, Sname, Ssex, Sage, Sdept)Courses ( Cno, Cname, Cpno, Credit)SC ( Sno, Cno, Grade) 单表查询

用户可以通过指定别名来改变查询结果的列标题

select Sname NAME, ‘Year of birth’ BIRTH,2009-Sage BIRTHDAY, islower(Sdept) DEPARTMENT

from Students;

NAME BIRTH BIRTHDAY DEPARTMENT

李勇 Year of birth: 1976 cs刘辰 Year of birth: 1977 is王敏 Year of birth: 1978 ma张立 Year of birth: 1978 is


单表查询

消除取值重复的行

查询选修了课程的学生学号

select Snofrom SC;消除重复行

select distinct Snofrom SC;

Sno95001 95001950019500295002

Sno95001 95002


查询满足条件的元组

常用查询条件

查询条件谓词比较 =, >, <, >=, <=, !=, <>, !>, !<

not + 上述比较运算符（例如not=）确定范围 between and, not between and确定集合 in, not in字符匹配 like, not like空值 is null, is not null多重条件 and, or

单表查询

一般查询查询年龄在20岁以下的学生姓名及其年龄select Sname, Sagefrom Studentswhere Sage < 20;查询年龄在20~23岁之间的学生姓名、所在系，年龄select Sname, Sdept, Sagefrom Studentswhere Sage between 20 and 23;


查询信息系、数学系和计算机系学生的姓名和性别

select Sname, Ssexfrom Studentswhere Sdept in (‘IS’, ‘MA’, ‘CS’);查询所有姓刘的学生的姓名、学号和性别

select Sname, Sno, Ssexfrom Studentswhere Sname like ‘刘%’；


单表查询

查询姓欧阳且全名为三个汉字的学生姓名

select Snamefrom Studentswhere Sname like ‘欧阳__’;查询DB_Design课程号和学分

select Cno, Ccreditfrom Courseswhere Cname like ‘DB\_Design’;


查询以C开始和结束，并且有至少3个字母组成

的系的学生的学号

select Snofrom Studentswhere Sdept like ‘C_%C’;查询缺考学生的学号和课号

select Sno, Cnofrom SCwhere Grade is null;


单表查询

查询计算机系年龄在20岁以下的学生的姓名

select Snamefrom Studentswhere Sdept=‘CS’ and Sage<20;查询信息系、数学系和计算机系学生的姓名和性别

select Sname, Ssexfrom Studentswhere Sdept=‘CS’ or Sdept=‘IS’ or Sdept=‘MA’


对查询结果排序

例子：查询选修了3号课程的学生学号和成

绩，要求查询结果按成绩降序排列

select Sno, Gradefrom SCwhere Cno=‘3’order by Grade desc;


单表查询

例子：查询全体学生的情况，查询结果按系号升序排列，同一系的学生按年龄降序排列

select *from SCorder by Sdept asc, Sage desc; 使用集函数

例子: 查询学生总数

select count(*)from Students;


例子: 查询选修了课程的学生人数

select count (distinct Sno)from SC;求选修1号课程的学生的平均成绩

select avg(Grade)from SCwhere Cno=‘1’;


单表查询

SQL提供的主要集函数(聚合函数)count( [distinct|all] *)count( [distinct|all] <列名>)sum( [distinct|all] <列名>)avg( [distinct|all] <列名>)max ( [distinct|all] <列名>)min ( [distinct|all] <列名>)

单表查询

对查询结果分组: 将查询结果按一列或多列值分组, 目的是将集函数作用到组上(例如: 小计)例子: 求每门课的课号及其选课人数select Cno, count(Sno)from SCgroup by Cno;例子: 求选课人数超过10人的课程号及其人数select Cno, count(Sno)from SCgroup by Cnohaving count(Sno)>10;

组内条件


多个关系上的查询

集合操作

多个SQL的查询结果可以进行集合操作

多关系的连接操作


集合操作主要有: union、intersect和except例子：查询计算机系的学生以及年龄不大于19岁的学生

select *from Studentswhere Sdept=‘CS’unionselect *from Studentswhere Sage <= 19;不用union，如何实现该查询？


例子：查询计算机系的学生与年龄不大于19岁的学生的

交集

select *from Studentswhere Sdept=‘CS’intersectselect *from Studentswhere Sage<=19; 不用intersect，如何实现该查询？


例子：查询计算机系的学生与年龄不大于19岁的

学生的差集

select *from Studentswhere Sdept=‘CS’exceptselect *from Studentswhere Sage<=19; 不用except，如何实现该查询？


关于消除重复元组的讨论

在select中缺省情况下：保留重复元组

使用distinct消除重复元组

在union, intersect, except中缺省情况下：消除重复元组

在union, intersect, 或except后加上all，保留重

复元组

多表连接查询

等值与非等值连接查询

查询每个学生及其选修课的情况

select Students.*, SC.*from Students, SCwhere Students.Sno=SC.Sno;

Students.Sno Sname … SC.Sno Cno …95001 李勇 … 95001 1 ……


多表连接查询

自然连接select Students.*, Cno, Gradefrom Students, SCwhere Students.Sno=SC.Sno;自身连接查询每门课的课号及其间接先修课的课号C1: Courses ( Cno, Cname, Cpno, Credit)C2: Courses ( Cno, Cname, Cpno, Credit)select C1.Cno, C2.Cpnofrom Courses C1, Courses C2where C1.Cpno=C2.Cno;

Students ( Sno, Sname, Ssex, Sage, Sdept)Courses ( Cno, Cname, Cpno, Credit)SC ( Sno, Cno, Grade) 多表连接查询

自身连接:查询每门课的课号及其间接先修课的课号

多表连接查询

自身连接查询每门课的课号及其间接先修课的课号(间接距离为1)

C1: Courses ( Cno, Cname, Cpno, Credit)C2: Courses ( Cno, Cname, Cpno, Credit)

select C1.Cno, C2.Cpnofrom Courses C1, Courses C2where C1.Cpno=C2.Cno;


连接结果

C1.Cpno=C2.Cno

C1 C2

多表连接查询外连接: 在一般连接中,只有满足条件的元组才可以作为结果输出, 外连接则不同(左、右外连接)查询学校内学生及雇员的情况。

(内)连接

既是学生，又是雇员。

左外连接

是学生，可以不是雇员。

右外连接

可以不是学生，但，是雇员。

全外连接

可以不是学生，可以不是雇员。

Students(name, dept)Employee(name, salary)

多表连接查询

左外连接:是学生，可以不是雇员。


CS王敏

IS李勇

deptname

王一

李勇

name

500200salary

From子句的写法

Students LEFT OUTER JOIN Employee

null200salary

CSISdept

王敏

李勇

name

多表连接查询

全外连接: 可以不是学生，可以不是雇员。


CS王敏

IS李勇

deptname

王一

李勇

name

500200salary

nullCS王敏

500

200salary

null

ISdept

王一

李勇

name

From子句的写法

Students FULL OUTER JOIN Employee

多表连接查询

左外连接

多表连接查询

思考：Student和SC之间可以做哪种外连接？

Students ( Sno, Sname, Ssex, Sage, Sdept)SC ( Sno, Cno, Grade)

多表连接查询

复合条件连接

查询选修2号课程且成绩在90分以上的所有学生

select Students.*from Students, SCwhere Students.Sno=SC.Sno and

Cno=‘2’ and Grade>90;


多表连接查询

复合条件连接

查询每个学生的学号、姓名、选修的课程名和成绩

select Students.Sno, Sname, Cname, Gradefrom Students, Courses, SCwhere Students.Sno=SC.Sno and

SC.Cno=Courses.Cno;


嵌套查询

查询块(SELECT-FROM-WHERE)一个查询块可以放在另一个查询块的where子句或having短语的条件中

父查询和子查询

子查询中不可以使用order by 子句，order by只能对终结果排序

多个简单查询构造一个复杂的查询

相关子查询和不相关子查询

不相关子查询

带有in的子查询：子查询结果为多值

查询与刘晨在一个系学习的学生

要求：不使用连接查询

Students ( Sno, Sname, Ssex, Sage, Sdept)

select *from Studentswhere Sdept in (select Sdept

from Studentswhere Sname=‘刘晨’);

不相关子查询求解方法由里向外

不相关子查询



要求：使用连接查询


不相关子查询



要求：使用连接查询

select S2.*from Students S1, Students S2,where S1.Sname = ‘刘晨’ and

S1.Sdept = S2.Sdept;

有些嵌套查询可以用连接查询代替


不相关子查询

查询选修了课程名为信息系统的学生学号和姓名

select Sno, Snamefrom Studentswhere Sno in (select Sno

from SCwhere Cno in (select Cno

from Courseswhere Cname=‘信息系统’));

不相关子查询

带有比较运算符的子查询：子查询结果为单值

的情况

例子：查询与刘晨在一个系学习的学生

select *from Studentswhere Sdept = (select Sdept

from Studentswhere Sname=‘刘晨’);

注意：不能用=表示值和表间的等值判断

不相关子查询

带有any或all的子查询：必须同比较运算符同用

θ any θ all查询比信息系某一学生年龄小的其他系学生姓名和年龄

select Sname, Sagefrom Studentswhere Sage <any (select Sage

from Studentswhere Sdept=‘IS’)

and Sdept != ‘IS’ ;

不相关子查询

用集函数实现上述查询(效率比any和all高)select Sname, Sagefrom Studentswhere Sage < (select max(Sage)

from Studentswhere Sdept=‘IS’)

and Sdept!=‘IS’ ; 查询比信息系所有学生年龄都小的其他系的学生姓名和年龄(思考题)Any和all和集函数的对应关系参见教材

多表连接查询

自身连接

查询每门课的课号及其间接先修课的课号

C1: Courses ( Cno, Cname, Cpno, Credit)C2: Courses ( Cno, Cname, Cpno, Credit)select C1.Cno, C2.Cpnofrom Courses C1, Courses C2where C1.Cpno=C2.Cno; 查询每门课的课号及其直接先修课的课号

Select Cno, Cpnofrom Courses

Courses ( Cno, Cname, Cpno, Credit)

嵌套查询

查询块

一个查询块可以放在另一个查询块的where子句或having短语的条件中

父查询和子查询

子查询中不可以使用order by 子句，order by只能对终结果排序

多个简单查询构造一个复杂的查询

不相关子查询

相关子查询

相关子查询

带有exists的子查询：子查询结果为true或false查询所有选修了1号课程的学生姓名

select Snamefrom Studentswhere exists (select *

from SCwhere Sno=Students.Snoand Cno=‘1’);

相关子查询求解方法由外向里(样板数据库)

思考：查询没有选修1号课程的学生姓名

相关子查询

带有exists的子查询：子查询结果为true或false查询没有选修了1号课程的学生姓名

select Snamefrom Studentswhere not exists (select *

from SCwhere Sno=Students.Sno andCno=‘1’);

相关子查询求解方法由外向里(样板数据库)

相关子查询(思考题)带有exists的子查询：子查询结果为true或false查询没有选修了1号课程的学生姓名

select Snamefrom Studentswhere exists (select *

from SCwhere Sno=Students.Sno andCno != ‘1’);

上述查询是否正确?

相关子查询SQL中没有全称量词,但是 ∀x(P) ≡ ¬( ∃x (¬P) )例子: 查询选修了全部课程的学生姓名select Snamefrom Studentswhere not exists (select *

from Coursewhere not exists (select *

from SCwhere Sno=Students.Sno and

Cno=Course.Cno));

分析: 求学生x, 对于任意一门课y, P: 学生x选修了y, 则: ∀y(P)≡¬(∃y(¬P))

x: Students.Snoy: Courses.Cno

90C29500290C295001

80C195001gradeCnoSno

李二95002王一95001(x)SnameSno

O.S.C2DBMSC1(y)CnameCno

当x=95001时,找不到一个课程,使P不成立

相关子查询

SQL中没有逻辑蕴含运算,但是 (p→q) ≡ ¬p∨q例子: 查询至少选修了学生95002选修的全部课程

的学生学号

分析: 查询学号u, 对于任意一门课v, 只要95002选修, 则u必然选修, 如果:

p: 95002选修vq: u选修v则: ∀v(p → q) ≡¬(∃v(¬(p → q)))

≡¬(∃v(¬(¬p∨q)))≡¬(∃v(¬(¬p∨q)))≡¬∃v(p∧ ¬ q)

相关子查询

例子: 查询至少选修了学生95002选修的全部课程的学生学号p: 95002选修v, q: u 选修v∀v(p → q) ≡ ¬∃v(p∧ ¬ q)select Sno

from SC Xwhere not exists (select *

from SC Ywhere Sno=‘95002’ and not exists

(select *from SC Zwhere Sno=X.Sno and

Cno=Y.Cno));

u: X.Snov: Y.Cno

90C2 (v)9500290C295001

80C195001(u)gradeCnoSno

Y X

Z

习题

查询租用过103号船的船员姓名

习题

查找租用过船只的船员编号

问题1：是否需要在Select子句中添加DISTINCT?问题2：如果将Select子句中的S.sid换成S.sname,是否需要添加DISTINCT?

Sid sname rating age Sid bid day22 Dustin 7 45 22 101 10/10/9829 Brutus 1 33 22 102 10/10/9858 Rusty 10 35 64 101 9/5/9864 Horatio 7 35

Sailors Reserves

Sailors.Sid sname rating age Reserves.Sid bid day22 Dustin 7 45 22 101 10/10/9822 Dustin 7 45 22 102 10/10/9864 Horatio 7 35 64 101 9/5/98

Sailors ReservesSailors.Sid θ Reserves.Sid

问题1：若不加DISTINCT,则结果中存在重复元组，结果中的原则个数等于Reserves的元组个数

问题2：与问题1的答案相同习题（嵌套查询）

查找租用过103号船只的水手的名字

不相关子查询

问题3：不使用嵌套查询该怎麽做？

嵌套查询

查找租用过103号船只的水手的名字

相关子查询嵌套查询

查找比Harotio级别高的水手

查找rating>7且年龄>25的水手编号

复合条件

使用Intersect来代替and

Select S.sidfrom Sailors Swhere S.rating>7 and S.age>25

Select S.sid from Sailors S where s.rating>7intersectselect S2.sid from Sailors S2 where S2.age>25

习题使用In查询来改写Intersect查询

查找rating>7且年龄>25的水手编号

不相关子查询

Select S.sidfrom Sailors S where S.rating > 7And S.sid in ( select S2.sid

from Sailors S2 where S2.age > 25 )

可以使用Union来代替or运算

查找租用过红船或绿船的水手编号

查找租用过红船和绿船的水手编号

错误的写法

正确的写法

Select S.snamefrom Sailors S, Reverses R, Boats Bwhere S.sid=R.sid and R.bid=B.bid and

B.color=‘red’ and B.color=‘green’

Select S.snamefrom Sailors S, Reverses R1, Boats B1, Reverses R2, Boats B2where S.sid=R1.sid and R1.bid=B1.bid and S.sid=R2.sid and

R2.bid=B2.bid and B1.color=‘red’ and B2.color=‘green’

习题

查找租用过红船和绿船的水手编号Intersect查询

Select S.sidfrom Sailors S, Reverses, R Boats Bwhere S.sid=R.bid and R.bid=B.bid and B.color=‘red’intersectselect S2.sidfrom Sailors S2, Reverses R2, Boats B2where S2.sid=R2.bid and R2.bid=B2.bid

and B2.color=‘green’

思考：第二部分的from子句和第一部分的from子句是否可以相同？

可以相同

使用In查询来改写Intersect查询查找租用过红船和绿船的水手编号

In查询（不相关子查询）

思考：父子两块查询中的from子句是否可以相同？

使用In查询来改写Intersect查询

查找年长的水手的年龄和名字在18岁以上水手中,对于每个rating级别中少有

两个水手以上的组中年轻水手的年龄

在18岁以上水手中,对于每个rating级别中少有

两个水手以上的组中年轻水手的年龄

在18岁以上水手中,对于每个rating级别中少有两个水手以上的组中年轻水手的年龄(子查询)

Having子句中也可以包含子查询

Having子句可以为Count(*) > 1

问题4:如果去掉颜色选择条件,查询结果如何?问题5:如果去掉Sailors和与S.sid相关的条件,情况会怎样?

查找每条红色船只被租用的次数

在三个关系连接之上的一个分组操作

问题4:查询每条船被租用的次数

问题5:如果Reserves引用Sailors，则去掉Sailors和与S.sid相关的条件不会影响查询结果。关于group by的练习

求选修课程记录中，成绩好和差的学生的姓名，性别和成绩

用嵌套查询可以解决此问题

关于group by的练习

求选修课程记录中，成绩好和

差的学生的姓名，性别和成绩

嵌套查询


求选修课程记录中，男生里成绩好和差的学生的性别和成绩，以及女生里成绩好和

差的学生的性别和成绩


求选修课程记录中，男生里成绩好和差的学生的性别和成绩，以及女生里成绩好和差的学生的性别和成绩


求选修课程记录中，男生里成绩好和差的学生的性别和成绩，以及女生里成绩好和差的学生的性别和成绩


求选修课程记录中，男生里成绩好和差的学生的姓名，性别和成绩，以及女生里成绩好和差的学生的姓名，性别和成绩

SQL数据操纵功能(更新)

数据插入

数据删除

数据修改

数据插入

单记录插入

insert into Students values(‘95020’, ‘陈东’, ‘男’, 18,‘IS’);insert into SC(Sno, Cno) values(’95020', ’1');多记录插入(插入子查询结果)假定数据库中有表:deptage(Sdept char(15), Avgage smallint)则可以将Students中的部分数据导入到deptageinsert into deptage ( sdept, avgage )

select Sdept, avg( Sage ) from Students group by Sdept;

数据删除

delete from Students where Sno = '95019’;delete from SC带有子查询的删除

delete from SC where 'CS' =

( select Sdeptfrom Studentswhere Students.Sno = SC.Sno );

思考: 王立同学因病休学,请将王立的选课记录删去

delete from SCwhere ‘王立’=

(select Snamefrom Studentswhere Students.Sno=SC.Sno)

数据修改

update Students set Sage = 22 where Sno= ‘95001’;update Students set Sage = Sage + 1;带有子查询的修改

update SC set Grade = 0 where 'MA' =

( select Sdeptfrom Students where Sno=SC.Sno );

更新操作与数据库的一致性

更新操作只对一个表操作, 但实际中可能:例如: 请删除学号为95019的学生, 隐含将其所有

选课记录删去

delete from students where Sno=‘95019’;delete from SC where Sno=‘95019’;如果建表时, 说明SC参照Students存在, 则此

删除失败

更新操作与数据库的一致性

数据库提供事务概念处理这类问题

如果建表时, 说明SC参照Students存在, 且说明on delete cascade, 则只要

delete from students where Sno=‘95019’;便可将95019的选课记录全部删去

视图

视图是用户可以看见的(虚)关系，它不是逻辑模型的一部分。视图是从一个或几个基本表（视图）导出的表

视图是虚表：数据库中只存放视图的定义(存放于数据字典中)，不存放视图对应的数据视图也称动态窗口：视图可以和基本表一样被查询，被删除视图的更新是有一定限制的可以基于视图，定义新的视图


模式

内模式

数据库


表

索引

视图

视图定义

基于关系建立视图

例子：建立信息系学生的视图

create view IS_S asselect * from Studentswhere Sdept=‘IS’;

create view IS_Students(No, Name, Sex) asselect Sno, Sname, Ssex from Studentswhere Sdept=‘IS’;

行列子集视图：从单个基本表导出，去掉了一些行和列，但保留了码

?视图的属性名

视图定义

视图的属性或者全部指定，或者全部不指定

换名

某个目标列是集函数或列表达式

多表连接时有几个同名列需要区分

例子：将学生的学号及平均成绩定义为一个视

图

create view avgGrade(Sno, avgG) asselect Sno, avg(Grade)from SCgroup by Sno;

虚拟列

视图定义

基于视图建立视图

例子：建立信息系选修了1号课程且成绩在90分以上

的学生的视图

Create view IS_Top_Students(Sno, Sname, Grade)

as select IS_Students.Sno, Sname, Gradefrom IS_Students, SCwhere IS_Students.Sno=SC.Sno and

Cno=‘1’and Grade>90;

视图定义

with check option子句

对视图进行UPDATE、INSERT、DELETE时要保证更新、插入、删除的行满足视图定义的谓词条件。

create view IS_S asselect * from Studentswhere Sdept=‘IS’with check option;

删除视图

语句格式：DROP VIEW <视图名>；将视图定义从数据字典中删除，且由该视图导出的视图均失效，要从数据字典中将失效的视图都删除，以免用户使用错误

例如：

DROP VIEW IS_Student;思考：若删除了基本表，由基本表导出的视图又如何？

查询视图

视图查询执行过程: 把定义中的子查询和用户查询

结合起来, 转化成等价的对基本表的查询

例子: 在信息系的学生中找到年龄小于20岁的学生

select * from IS_Swhere Sage<20;转换后的查询为(系统完成):select * from Studentswhere Sdept=‘IS’ and Sage<20;

查询视图

查询信息系选修了1号课程的学生姓名和成绩

select Sname, Grade from IS_Students, SCwhere IS_Students.Sno=SC.Sno and Cno=‘1’;

查询视图

对视图的查询有些是不能直接进行转换的

例子: 在avgGrade视图中, 查询平均成绩在90分以上

的学生学号和平均成绩

select * from avgGradewhere avgG>=90;

错误转换:select Sno, avg(Grade)from SCwhere avg(Grade)>=90group by Sno; create view avgGrade(Sno, avgG) as

select Sno, avg(Grade)from SCgroup by Sno

查询视图

对视图的查询有些是不能直接进行转换的

例子: 在avgGrade视图中, 查询平均成绩在90分以上的学生学号和平均成绩

select * from avgGradewhere avgG>=90;

正确转换:select Sno, avg(Grade)from SCgroup by Snohaving avg(Grade)>=90;

create view avgGrade(Sno, avgG) asselect Sno, avg(Grade)from SCgroup by Sno

视图的作用

简化用户的操作

视图使用户能以多种角度看待同一数据

视图可以对机密数据提供一定的安全保护

视图的作用

视图对数据库重构提供了一定程度的逻辑独立性

例如: Students(Sno, Sname, Ssex, Sage, Sdept)分为: S1(Sno, Sname, Sage)

S2(Sno, Ssex, Sdept)但在Students上已有开发了多个应用, 为了达到不改变应用程序的目的, 可以建视图Students:create view Students(Sno, Sname, Ssex, Sage, Sdept) as

select S1.Sno, S1.Sname, S2.Ssex, S1.Sage, S2.Sdept

from S1, S2where S1.Sno=S2.Sno;

视图更新

对视图更新终转换成对基本表的更新

将信息系学生学号为95002的学生姓名改为“刘辰”update IS_Students set Sname=‘刘辰’where Sno=‘95002’;通过视图IS_Students插入一信息系的学生记录

insert into IS_Students values(‘95029’, ‘赵新’, ‘女’);insert into IS_S values(‘95029’, ‘赵新’, ‘女’,20, ‘MA’)with check option子句

视图更新

删除信息系学号为95029的记录

delete from IS_Studentswhere Sno=‘95029’;行列子集视图是可以更新的, DB2例子参见教材

视图更新

由两个以上基本表导出的视图不允许更新；

来自于字段表达式或常数的视图字段不允许进行insert和update操作，但允许delete操作；

不允许更新集函数视图字段；

不允许更新含有group by或distinct视图；

嵌套查询内层的表也是导出该视图的基本表时，不允许更新；

不允许更新的视图上定义的视图也不允许更新。

嵌套查询内层的表也是导出该视图的基本表

例如：CREATE VIEW GOOD_SCAS

SELECT Sno,GradeFROM SCWHERE Grade >

(SELECT AVG(Grade)FROM SC);

嵌入式SQL

DBMS DBMS

程序

嵌入式SQL

实现复杂应用：高级语言计算完备性+SQL语言的高效处理

主语言(宿主语言), 嵌入式SQL几点需要注意的问题

在主语言中，SQL语句必须被清楚地标识

如何在两种语句间传递数据类型

SQL语句的查询结果是一个集合,而高级语

言通常不是为集合而定义的

嵌入式SQL嵌入式SQL的处理过

程：数据库厂商提供

预编译器

修改和扩充主语言编译器

宿主语言 + 嵌套SQL

预编译器

宿主语言 + SQL函数调用

宿主语言编译器

目标代码程序

SQL库链接

可执行代码

关系DBMS一般提供一批用宿主语言编写的SQL函数，组成SQL函数库, 供应用程序调用DBMS的各种功能

嵌入式SQL在主语言中，SQL语句必须被清楚地标识

嵌入式SQL 的一般格式

所有SQL语句必须加前 EXEC SQL和语句结束标志(C语言中一般是“;” 号)

EXEC SQL select Sno from SC where Cno=‘1’;

嵌入式SQL语句可分为: 可执行语句和说明

性语句

嵌入式SQL

SQL函数库实际上是DBMS向应用程序提供的一种接口, 称为调用级接口(CLI)预编译器将嵌入SQL语句编译成宿主语言对SQL函数的调用

95年ISO公布了一个CLI标准, 作为SQL-92的一个附件,称为SQL-92/CLI或CLI95, 新的RDBMS都支持该标准, 标准中只规定了函数的定义

嵌入式SQL如何在两种语句间传递数据类型，实现嵌入式SQL语句与主语言语句之间的通信

公共数据结构-- SQL通信区(SQLCA)SQL通信区(SQLCA): 每个SQL语句执行后, 系统要反馈给应用程序若干信息, 这些信息存于SQLCA中

应用程序从SQLCA中取出这些状态信息, 并据此决

定应用程序的进一步执行

SQLCA是一个数据结构, 由系统定义, 程序员只需

在程序中加入

“EXEC SQL INCLUDE SQLCA;” 便可以引用其中

的分量

嵌入式SQL如何在两种语句间传递数据类型，实现嵌入式SQL语句与主语言语句之间的通信

公共数据结构--分量SQLCODE分量SQLCODE是一整型变量, 系统将每个SQL语句

执行成功与否的结果赋给该变量

0: 成功

正数: 已执行但有异常(其中100表示没取到数) 负数: 表示SQL语句因某些错误而未被执行, 负数的值表示错误的类别

嵌入式SQL主变量(宿主变量)

嵌入式SQL使用主语言的变量输入/输出数据, 主变量的说明(可以和属性同名)

例子: EXEC SQL BEGIN DECLARE SECTION;char Sno[7],char givenSno[7],char Cno char[2],float Grade,short GradeIn;EXEC SQL END DECLARE SECTION;

嵌入式SQL主变量的使用

在SQL中使用主变量时, 主变量前一定加“:”例子: EXEC SQL select Sno, Cno, Grade

into :Sno, :Cno, :Gradefrom SCwhere Sno=:givenSno;

嵌入式SQL指示变量(主变量的一种)

一个主变量可以附带一个指示变量, 指示变量也必须说明, 使用时放在主变量之后

例子:EXEC SQL select Sno, Cno, Grade

into :Sno, :Cno, :Grade :GradeInfrom SCwhere Sno=:givenSno;

指示主变量Grade的取值情况(是否为空值), 当GradeIn<0时, 表明成绩为空值

指示变量不允许用在where子句中

嵌入式SQLSQL语句的查询结果是一个集合,而高级语言通常

不是为集合而定义的

游标

SQL与主语言具有不同的处理方式, 用游标协调

游标区是系统为用户开设的一个数据缓冲区, 存放SQL语句的执行结果, 每个游标区有一个名字

游标的说明和使用

嵌入式SQLEXEC SQL DECLARE cursor1 CURSOR FOR

select Sno, Cno, Gradeinto :Sno, :Cno, :Gradefrom SC;

EXEC SQL OPEN cursor1;while(1) {

EXEC SQL FETCH cursor1;if(sqlca.sqlcode)<>0 // 没有取到结果

break; //跳出循环printf(“Sno:%s, Cno:%s, Grade:%d”, Sno,

Cno, Grade);} EXEC SQL CLOSE cursor1;

不使用游标的SQL语句

不使用游标的sql语句Insert语句update(不带current子句)语句delete(不带current子句)语句

查询结果为单记录的语句例子: 例子1: 查询某个学生选修某门课程的成绩, 要求输出学号, 课号和成绩exec sql include sqlca;exec sql begin declare section;

char Sno[7],char givenSno[7],

特点


char Cno char[3],givenCno char[3],float Grade,short GradeIn,

exec sql end declare section;main() {

printf(“\n请输入学号:”)scanf(“%s”, Sno);printf(“\n请输入课号:”)scanf(“%s”, Cno);


exec sql select Sno, Cno, Gradeinto :Sno, :Cno, :Grade: GradeInwhere Sno=:givenSno and Cno=:givenCno;

if(sqlca.sqlcode==0) // 执行正确

if (GradeIn<0) // 成绩为空

printf(“\n%s, %s, %d”, Sno, Cno);else

printf(“\n%s, %s, %d”, Sno, Cno, Grade);else // 执行不正确

printf(“\n没有查到所需数据\n”); }


例子2: 将学生的年龄增加1岁exec sql update Students

set Sage=Sage+1;例子3: 将学生的1号课成绩增加指定的分数, 要求从键盘上

输入增加的值

exec sql update SCset Grade=Grade+:Raisewhere Cno=‘1’;

例子4:将学生的1号课成绩置空

exec sql update SCset Grade = Grade + :Raise :GradeInwhere Cno=‘1’;


例子5: 某学生退学了, 将其所有选课记录删除, 要求输入学生的学号

exec sql delete from SCwhere Sno=:givenSno;

例子6: 某个学生选修了某门课程, 将有关记录插入SC表中, 要求从键盘输入学生的学号, 成绩为空

分析: 说明主变量, 将指示变量赋初值-1exec sql insert into SC

values(:Sno, :Cno, :Grade :GradeIn)

使用游标的SQL语句


查询结果为多条记录的select语句

带有current的update语句

带有current的delete语句

例子1: 查询某个系全体学生的信息, 要求系名由键盘输入

exec sql include sqlca;exec sql begin declare section;

char Sno[7], Sname[11], Ssex[3];char givenSdept[20];short Sage;

exec sql end declare section;


main() {EXEC SQL DECLARE cursor2 CURSOR FOR

select Sno, Sname, Ssex, Sageinto :Sno, :Sname, :Ssex, :Sagefrom Studentswhere Sdept=:givenSdept;

printf(“\n请输入要查询的系:”);scanf(“%s”, givenSdept);EXEC SQL OPEN cursor2;while(1) {

EXEC SQL FETCH cursor2;if(sqlca.sqlcode)<>0

break; printf(“\n%s, %s, %s, %d”, Sno, Sname, Ssex, Sage);

} EXEC SQL CLOSE cursor2; }


例子2: 查询某个系全体学生的信息, 要求系名由键盘输入, 并根据用户需要修改某些记录的年龄字段

exec sql include sqlca;exec sql begin declare section;

char Sno[7], Sname[11], Ssex[3];char givenSdept[20];short Sage;

exec sql end declare section;


main() {EXEC SQL DECLARE cursor3 CURSOR FOR

select Sno, Sname, Ssex, Sageinto :Sno, :Sname, :Ssex, :Sagefrom Studentswhere Sdept=:givenSdeptfor update of Sage;

printf(“\n请输入要查询的系:”);scanf(“%s”, gevenSdept);EXEC SQL OPEN cursor3;while(1) {

使用游标的SQL语句EXEC SQL FETCH cursor3;if(sqlca.sqlcode)<>0

break; printf(“\n%s, %s, %s, %d”, Sno, Sname, Ssex, Sage);printf(“\n需要修改吗?”);scanf(“%c” &yn);if(yn=‘y’ or yn=‘Y’){

printf(“\n输入要修改的值:”);Scanf(“%d”, givenSage);exec sql update Students

set Sage=:givenSagewhere current of cursor3; }

} EXEC SQL CLOSE cursor3;

}


带有current的删除语句处理同上

当游标中的select带有union或order by 子句时, 不能使用带有current的update和delete当游标中的select定义了一个不可更新的视图时, 不能使用带有current的update和delete

动态SQL静态SQL: 编写程序时, SQL语句是已知的, 只有一些主变量的值需要输入的

动态SQL: 如果想查询的列不固定, 查询的条件不固定, 这样通用的查询程序必须通过动态SQL实现

动态SQL允许在程序运行过程中, 临时组装SQL语句, 主要有三种形式:

语句可变

条件可变

数据库对象和查询条件都可变

例子:例子1: 删除表Students中的某些学生记录

动态SQLexec sql begin declare section;char sqlString[200];exec sql end declare section;char conditons[150];main() {

strcpy(sqlString, “delete from Students where ”);printf(“enter search condition:”);scanf(“%s”, conditions);strcat(sqlString, conditons);exec sql execute immediate :sqlString; …

}思考题: 删除表中的一些记录

以分号结尾

动态SQL例子2: 删除某年以前出生的某些学生记录, 要求由键盘输入年份(仅给出程序段)exec sql begin declare section;char sqlString[200];int birthYear;exec sql end declare section;….strcpy(sqlString, “delete from Students where 2009-

Sage<=:y;”);printf(“Enter birthYear for deleting:”);Scanf(“%d”, &birthYear);exec sql prepare q1 from :sqlString;

SQL中带有

参数

不在任何地方说明, 仅占位

动态SQLexec sql execute q1 using :birthYear;

…. 例子3: 求选修某门课程号的学生和成绩, 按什么排序由查询者指定(仅给出程序段)

…. //主变量说明

char orderBy[150];strcpy(sqlString, “select Sno, Grade from SC where Cno=:C”);

printf(“输入orderby子句:”);scanf(“%s”, orderBy);strcat(sqlString, orderBy);printf(“输入课程号:”);

查询需要返回结果,但是否是多个元组, 在编程时不能确定

动态SQLscanf(“%s”, givenCno);exec sql prepare query1 from :sqlString;exec sql declare cursor4 cursor for query; exec sql open cursor4 using :givenCno;while(1) {

exec sql fetch cursor4 into :Sno, :Grade :GradeIn;if(sqlca.sqlcode==100)

break;if(sqlca.sqlcode<0)

break;…

}

访问数据库

DBMS DBMS

程序

DBMS

程序

API

ODBC和JDBC

ODBC：Open DataBase ConnectivityJDBC：Java DataBase Connectivity特点：

– 程序不需要重新编译，就可以在多个不同的DBMS上进行操作

– 可以同时访问多个DBMS– 在源代码级和代码执行级独立于DBMS

…

ODBC / JDBC程序

ODBC和JDBC

体系结构：四个主要组件

– 应用

– 驱动器管理

– 多个数据源指定的驱动器

– 数据源

…

ODBC / JDBC程序

ODBC和JDBC

应用

– 初始化、终止与数据源的连接

– 通过定义好的ODBC/JDBC API，提交SQL语句、获得结果

驱动器管理

– 加载ODBC/JDBC 驱动器

– 从应用层传递ODBC/JDBC函数到正确的驱动器

– 处理从应用层提交的ODBC/JDBC初始化和信息调用，并纪录所用的功能调用。

– 执行初步的错误检测

ODBC和JDBC

驱动器

– 建立与数据源的连接

– 提交请求、返回请求结果

– 转换数据源与ODBC/JDBC间的数据格式、

错误格式和错误代码

数据源

– 处理从驱动器发来的命令

– 返回结果

ODBC和JDBC

不同的体系结构

JDBC体系结构：通常有4种类型

– 桥将JDBC函数调用直接翻译到另一种API，但不是到DBMS的本地API。例如：ODBC-JDBC桥。

应用只加载一个驱动器。

– 直接翻译到本地的( 初的)API将JDBC函数调用直接翻译到某一个指定的数据源之上的API的方法调用。驱动器动态链接到指定的数据源上。

ODBC和JDBC

不同的体系结构

JDBC体系结构：通常有4种类型

– 网络桥驱动器与网络中的中间件通讯，将JDBC函数调用翻译为DBMS指定的方法调用。

注意，客户方的驱动器(在网络桥场地的驱动器)通常不是面向DBMS的。

– 直接翻译到socket(套接字)不直接调用DBMS API，驱动器通过JAVA socket与DBMS通讯。

注意，客户方的驱动器是面向DBMS的

使用JDBC的实例

JDBC是一组JAVA和接口的集合，用户可以用JAVA编程语言来直接访问数据库。

java.sql包中包括类和接口

使用JDBC的实例

数据源驱动器由Drivermanager类管理，维护当前加载的驱动器列表。

– 动态加入和删除驱动器

registerDriver, deregisterDriver, getDrivers

例子：动态加载一个数据源并建立连结Class.forName(“oracle/jdbc.driver.OracleDriver”);Connection connection=DriverManager.getConnection(url, uid,

pid);

使用JDBC的实例

例子：向Sailors关系中插入一条元组connection.setAutoCommit(false);PreparedStatement pstmt =

connection.prepareStatement(“INSERT INTO Sailors VALUES ?,?,?,?”);

pstmt.setString(1, j_id); pstmt.setString(2, j_name);pstmt.setInt(3, j_rate);pstmt.setInt(4, j_age);pstmt.execute();pstmt.close();connection.commit();

Sid sname rating ageSailors

使用JDBC的实例

例子：执行查询请求Statement stmt = connection.createStatement();ResultSet res =

stmt.executeQuery(“SELECT S.sname, S.age FROM Sailors S”);While (result.next()) {

String name = res.getString(1); int age = res.getInt(2);// process result row

}stmt.close();connection.commit();

Sid sname rating ageSailors

SQL --...

Documents

Transcript of SQL --...