当前位置:首页 >> 其它课程 >> 数据库选修课

数据库选修课


数据库原理及应用

§2 关系数据库

姚普选
计教中心
Page 1

内 容
? 关系模型 关系及关系模式 关系模型的数据操作 数据完整性约束 ? 关系代数 关系运算 关系代数的运算种类 三种关系运算 ? SQL语言
Page 2

§ 2.1 关系模型

关系模型由三部份组成 数据结构、数据操作、完整性约束 1.关系模型的数据结构:数据的逻辑结构_二维表

学生

m

选修

n

课程

概念模型

关系模式 表示为 关系名(属性名1,属性名2,…,属性名n)

例:学生(学号,姓名,性别,年龄,班级,学院)
注意: ? 关系模式是型(type),描述一个关系的结构 ? 关系是值,是元组的集合 描述某一时刻关系模式的内容 ? 故关系模式是相对稳定的,静态的; 而关系是随时间变化的,动态的 ? 关系可用来表示实体(如学生,课程等) 也可用来表示实体间的联系(如选课)

关系的性质
?

? ? ? ?

每列都是不可再分的基本字段(原子属性) 一列中各个分量具有相同的性质和意义; 列名是唯一的(不同不二名) 行的次序可任意交换,不会改变关系的意义 列的次序可任意交换,不会改变关系的意义 元组代表了一个实体 因此表中不允许出现相同的行

故,判别两个关系是否相等,与属性次序、元组次 序、关系名均无关 若仅这三点有别,其余完全相同, 可认为两个关系相等

2.关系模型的数据操作
?

数据操作——包括对表、行、列的操作 ? 主要操作包括 查询、添加(或插入)、删除、修改 例如:查找来自于江苏的02级学生的信息
关系模型的数据操作的特点: ? 关系模型的操作对象是集合 操作对象和操作结果都是关系(表) ? 用户只要指明干什么,而不必考虑怎么干 细节由DBMS处理 ? SQL语言实现关系数据库操作 RDBMS支持SQL语言

?

3.关系模型的数据完整性约束 完整性约束:规定了特定数据模型中 数据必须满足的条件 ? 无论对数据进行什么操作, 其结果都必须满足完整性约束的条件 ? 完整性约束的目的: ? 使对数据的操作能够正确进行 ? 使数据有意义 例如: 学号既不能为空也不能重复 年龄不大于60岁 学生所属班级名称不能写成根本不存在的班级 考试成绩是0-100之间的整数

四类完整性约束

实体完整性,引用完整性,值域完整性, 用户定义的完整性
实体完整性(EI,Entity Integrity) 强制保证表的主码的完整性 通过主码实现: 主码的任一属性都不能取空值
?

?空?(null)指无值(或不知道)
0不是null。null≠”null”

∵主码是区分各个实体和联系的惟一标识 为空则意味着可存在不以主码为标识的实体, 与主码定义矛盾

引用(参照)完整性(RI,Referential Integrity) 插入、修改或删除数据时,维护各表间数据一致性 主要通过主码与外码实现 若二表通过主码与外码建立了联系,则: ? 外码所在表(子表)不能引用主码所在表(主表) 中不存在的数据 ? 主表中数据变化时,子表中相关数据相应变化。反 之也一样 ? 有时外码可取空值。如?学生?关系的?宿舍号? 例:“学生?、?专业?二实体表示为关系 学生(学号,姓名,性别,专业号,出生日期) 专业(专业号,专业名)

引用完整性

值域完整性(DI,Domain Integrity ) 限制属性值的范围。例如: 性别只能是?男?或?女?,年龄不大于30等 Access中的?有效性规则?: “男? or “女? <=30 SQL语句的Where子句中写: 性别=“男? or “女? 年龄<=30 属性值可为空,例如,学生管理信息系统中, 新生报到后要记录其住宿房号, 若该生不在学校住宿,则宿舍号可为空

用户定义的完整性 实现应用系统的业务规则,由用户自行定义 如:项目书的名称必须以?XM”开头 折扣额不能大于销售额,等等 DBMS提供了用户自定义完整性的手段: ? 约束(constraint) ? 标识列(identity column) ? 默认值(default) ? 规则(rule) ? 触发器(trigger) ? 数据类型验证(data type) ? 索引(index) ? 存储过程(stored procedure)

§2.2 关系代数 1. 关系代数简介 关系模型:提供一组完备的关系运算, 支持关系数据库检索和修改(插入、更新、删除)操作 关系运算方法分为两类: 关系代数:关系数据操纵的传统表达方式, 以集合代数运算方法对关系进行数据操作 以若干个关系为运算对象,运算结果产生新关系 关系演算:以谓词表达式描述关系操作的 条件和要求
Page 13

运算符 传统的集合 ∪ 运算符 - ∩ × 专门的关系 ? 运算符 ?
比较 运算符 ? > ≥ < ≤ = ≠ ? ∧ ∨

含义 并 差 交 广义笛卡尔积 选择 投影 连接 除 大于 大于等于 小于 小于等于 等于 不等于 非 与 或

说明
按 行 运 算

按行 按列 二元 二元
辅 助 专 门 的 关 系 运 算

关 系 代 数 运 算 的 种 类 :

逻辑 运算符

Page 14

2. 关系运算
⑴集合运算(按元组) 设关系R、S度(属性个数) 为n,相应属性值取 自同一个域
并运算:R∪S 元组属于R或S 差运算:R-S 元组属于R 交运算:R∩S 元组既属R 又属于S
Page 15

⑵三种关系运算:选择、投影、连接

例7-4:“学生?关系 Access数据库中的?学生?表

Page 16

选择运算: 按条件从一个或多个关系中 抽取若干个元组组成新关系, 新关系是原关系的子集。 例如,条件:性别=“女?

Page 17

投影运算: 性 别 、 班 级 四 个 属 性 上 投 影

例 : 学 生 关 系 在 学 号 、 姓 名 、

新 关 系 是 原 关 系 子 集

选 择 一 些 属 性 组 成 新 关 系 ,

从 一 个 或 多 个 关 系 中

Page 18

连接运算: 连接运算:将两个关系中满足条件的元组连接起来 组成新关系 等连接:取两关系笛卡尔积中A、B属性值相等的元组

R.C=S.T R S
Page 19

§2.3 SQL语言介绍 SQL(Structured Query Language, ? 结构化查询语言) ISO命名为国际标准数据库语言 提供了数据定义、数据查询 、数据操纵和数据控制 语句,是一种综合性的数据库语言,可独立完成 数据库生命周期中的全部活动 用户可直接键入SQL命令来操纵数据库, 也可将其 嵌入 高级语言( C 、 Pascal 、 Java 等) 程序中使用 各种 RDBMS 一般都支持 SQL 或提供 SQL 接口。其 影响已超出数据库领域,扩展到了其他领域
Page 20

1. SQL对关系数据库的支持
从DBMS的角度看,数据库系统有一个严谨的体系 结构,从而保证其功能得以实现 SQL语言支持三级模式结构
SQL

视图1

视图2

外模式

基表1

基表2

基表3

基表4

模式

存储文件1

存储文件2

内模式
Page 21

说明:
由基表构成关系数据库的?模式? 基表是实际的表,一个基对应一个关系; 一个或多个表对应一个存储文件; 存储文件的逻辑结构组成关系数据库内模式; 视图是从一个或多个表中导出的表,为虚表, 数据库只存放其定义而数据仍在原表中 视图和基表都是关系 用户使用SQL语言对基表和视图进行查询、 更新等各种操作

Page 22

SQL语言功能概要 四类功能: 数据定义:CREATE、DROP、ALTER

创建表、索引、查询 数据操纵:INSERT、UPDATE、DELETE 插入、更新、删除 数据查询:SELECT 查询 数据控制:GRANT、REVOTE 授予、收回权限

阅读:SQL语言的发展 SQL:Structured Query Language ? 1974年 IBM公司Boyce 和Chamberlin提出 ? 1975-1979年 IBM在System R关系数据库系统原型 中实现 ? 1986年10月 成为ANSI标准:SQL-86 ? 1987年6月 接纳为ISO标准 ? 1989年4月 ISO较为完整的SQL-89标准 ? 1992年8月 ISO推出更完善的SQL-92标准 ? 1999年 ISO推出新的SQL-99标准

阅读:SQL语言的特点
?

一体化
集数据定义语言DDL、数据操纵语言DML和数据 控制语言DCL于一体,综合管理功能于一身

?

高度非过程化
用户只需提出?做什么?,而不需指明?怎么做?

?

面向集合的操作方式
操作的数据不是一条记录,而是记录的集合

?

同样语法提供两种使用方式
用户可直接键盘交互方式使用SQL命令,也可将 SQL语句嵌入其他高级语言(VB、Delphi、VC、 Java等)

?

语言简洁,容易掌握
命令(语句)只有9条,语法接近于自然语言

2. SQL语言的数据类型

不同数据库产品所支持的数据类型稍有差别
(以SQL Server 2000为例) 两大类: ? 系统数据类型 系统内含的

? 用户自定义数据类型
在系统数据类型的基础上建立 使用系统存储过程sp_addtype定义 SQL Server 2000的系统数据类型如下:

1)数值型
数据类型 Bigint Int 整数型 Smallint 精 Tinyint 确 Decimal(p,q) 精确数值型 或 Numeric(p,q) Bit 位型 Float 近 浮点型 似 Real 说明 8字节, -263~263-1 4字节, -231~231-1 2字节, -215~215-1 1字节, 0~255 -10^38+1 ~1038-1 p为总位数,q为小数位数 存储二进制0或1 -1.79E+308 ~ 1.79E+308 -3.4E+38 ~ 3.4E+38

2) 字符型
数据类型 Char(n) 定长字符串 Varchar(n) 变长字符串 Text 文本 Nchar(n) 定长Unicode Nvarchar(n) 变长Unicode Ntext Unicode文本 定长二进制字符 Binary(n) 变长二进制字符 Varbinary(n) Image 任意二进制数据 说明 n=1~8000 n=1~8000 最多231-1个字符 n=1~4000 n=1~4000 最多230-1个字符 n=1~8000 n=1~8000 最多231-1个字节,约2GB

3) 日期时间型
说明 8字节,1753年1月1日~9999年12月31日 Datetime 精度百分之三秒 4字节,1900年1月1日~2079年6月6日, Smalldatetime 精度1分钟 数据类型

日期:英文数字 数字加分隔符 或 纯数字 时间:24小时制 12小时制

Oct 25 2005 2005-10-25 2005/12/25 20051025 15:28:56 3:28:56 PM

4) 货币型
说明 8字节, -263 ~ 263-1, Money 精确度为4位小数, 最多19位数字 4字节, -214748.3648 ~ 214748.3647, Smallmoney 精确度为4位小数 数据类型

§2.3 SQL语言数据定义功能
SQL语言的数据定义语句 操作对象 创建 删除 修改 表 CREATE TABLE DROP TABLE ALTER TABLE 视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX 触发器 CREATE TRIGGER 存储过程 CREATE PROCEDURE

1. 表的定义与删除

1) 建立表结构 _基本语法:
CREATE TABLE 表名 (列名 类型 约束,…),[表级约束 ]
列名——表中列的名字 类型——该列数据类型 约束——该列完整性约束条件: NOT NULL 列值不能为空(不能用于表级约束) DEFAULT <常量> 指定列的默认值(不能用于表级约束)

UNIQUE
PRIMARY KEY

列值不能重复
指定本列为主码

CHECK <表达式> 限定列的取值范围

FOREIGN KEY

本列为引用其他表的外码,用法如下

FOREIGN KEY [列名] REFERENCES 外表名 (外表列名)

例:创建student表
列名 Sno Sname Ssex Sage Sdept 说明 学号 姓名 性别 年龄 所在系 数据类型 字符串,长度为7 字符串,长度为10 字符串,长度为2 整数 字符串,长度为20 约束 主码 非空 ‘男’ 或 ‘女’ 15~45 默认为 ‘计算机系’

CREATE TABLE student ( Sno Ssex Sage Sdept char(7) char(2) tinyint PRIMARY KEY, CHECK (Ssex = '男' OR Ssex = '女' ), CHECK (Sage >= 15 AND Sage <= 45), Sname char(10) NOT NULL,

char(20) DEFAULT '计算机系' )

例:创建course表
列名
Cno Cname Ccredit Cemester Period

说明
课程号

数据类型 字符串,长度为10 字符串,长度为20 整数 整数 整数

约束

课程名 学分 学期 学时

主码 非空 取值大于0 取值大于0 取值大于0

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) )

例:创建SC表
列名 说明 数据类型 约束

Sno
Cno Grade

学号
课程号 成绩

字符串,长度为7
字符串,长度为10 整数

主码,引用student的外码
主码,引用course的外码 取值0~100

CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint CHECK (Grade>=0 AND Grade<=100), PRIMARY KEY(Sno, Cno), FOREIGN KEY(Sno) REFERENCES student (Sno), FOREIGN KEY(Cno) REFERENCES course (Cno) )

2) 删除表(DROP TABLE)

基本语法: DROP TABLE 表名
说明:

删除表时会将表结构和表中的数据一起删除 例:删除test表 DROP TABLE test

3) 修改表结构 基本语法:

ALTER TABLE 表名 [ ALTER COLUMN 列名 新类型 ] 修改列定义 | [ ADD [COLUMN] 列名 类型 约束 ] 增加新列 | [ DROP COLUMN 列名 ] 删除列 | [ ADD PRIMARY KEY (列名列表 ) ] 增加主码约束 | [ ADD FOREIGN KEY (列名 ) REFERENCES 外表名 (列名 ) ] 增加外码约束

例1:为SC表添加?选课类别?列:XKLB char(4)

ALTER TABLE SC ADD XKLB char(4) 例2:将新添加的XKLB列的类型改为char(6) ALTER TABLE SC ALTER COLUMN XKLB char(6)

§2.4 SQL语言数据定义功能
更新:插入、删除、修改
?

表中插入一行数据:
insert into student (…) VALUES (…) 修改(更新)表中数据: UPDATE student SET Sage=Sage+1 删除数据:

?

?

DELETE SC WHERE Grade < 60
?

清空整个表:

TRUNCATE TABLE SC

1. 插入数据 基本语法: INSERT [INTO] 表名 [(列名表)] VALUES (值列表)
列名表——表中的列名的列表,列名之间用逗号分隔 未指定列名表时,值列表中值的顺序须与表中定义列的顺序 一致,且每列都要有值(或为空) 值列表——相应列的值,值之间用逗号分隔 值列表中的值要与列名表中的列按位置顺序对应,且应满足 约束条件 例:学生记录(9512101,李勇,男,19,计算机系)插入student表

insert into student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9512101', '李勇', '男', 19, '计算机系')

2. 更新数据

基本语法: UPDATE 表名 SET 列名=表达式 [ , … ] [ WHERE 条件 ]
SET子句——指定要改的列及改后的值,可一次修改多列 WHERE子句——指定要修改表中哪些记录,如果省略, 则所有记录的指定列都被修改

例1:所有学生年龄加1 UPDATE student SET Sage=Sage+1 例2:‘9512101?学生年龄加1 UPDATE student SET Sage=Sage+1 WHERE Sno = '9512101'

3. 删除数据 基本语法: DELETE [FROM] 表名 [ WHERE 条件 ] WHERE子句——指定要删除表中哪些记录,如果省略,
则所有记录都被删除,仅保留表的结构

例1:删除所有学生的选课记录

DELETE FROM SC
例2:删除所有不及格学生的选课记录

DELETE SC WHERE Grade < 60

4. 清空整个表 基本语法: TRUNCATE TABLE 表名 说明:
? ?

表中所有记录都被删除,仅保留表的结构。 要删除所有数据,TRUNCATE比DELETE速度快

例:删除所有学生的选课记录
TRUNCATE TABLE SC

§2.5 SQL语言数据查询功能
查询表中记录: SELECT语句, 可完成 ? 单表查询、多表联合查询、视图查询 ? 查询结果可0行、1行或多行,是一个记录集
基本语法:

SELECT [ALL/DISTINCT] 目标列名列表 FROM 表名列表 WHERE 条件 GROUP BY 列名列表 HAVING 组的筛选条件 ORDER BY 列名列表

SELECT语句的说明
参 数 描 述 满足条件的所有记录都将被返回,包括重复的记 录(默认,可以省略) 返回记录中不出现重复的记录。这个子句在每个 SELECT子句中只使用一次 指定数据从哪几个表中获取 从表中选取记录的条件

ALL
DISTINCT

FROM
WHERE

GROUP BY
HAVING ORDER BY

返回的记录将按照某一个或几个列的值分组
结果集中每个组必须满足的条件。该子句只和 GROUP BY 子句联合使用

查询结果按某列或几列的值升序(ASC)或降序 (DESC)排列

2.5.1 简单查询
简单查询:单表查询 1. 查询指定的列

SELECT子句中指定要查询的列:
例1:查询全体学生的学号与姓名

SELECT Sno, Sname
FROM student 例2:查询全体学生的姓名、学号与所在系 SELECT Sname, Sno, Sdept FROM student

查询全部列 法1:目标列名列表中列出所有的列 例:查询全体学生的记录 SELECT Sno, Sname, Ssex, Sage, Sdept FROM student 法2:星号‘*’代替目标列名列表 同上例:

SELECT *
FROM student

查询经过计算的列

目标列名列表中可包含表达式、常量或函数
例1:查询全体学生的出生年份

SELECT Sname, 2006 - Sage FROM student
例2:列出每个学生的学号、所选课程号、课程综合 成绩:综合成绩=(成绩×0.8)+20 SELECT Sno, Cno, Grade * 0.8 + 20 FROM SC 例3:查询全体学生出生年份,Sname列标题显示为 ?姓名?,出生年份列标题显示为?出生年份? SELECT Sname AS '姓名', 2006 - Sage '出生年份' FROM student

避免查询结果中的重复行 使用DISTINCT子句可避免重复行 例:查询student表中的学生都属于哪些系
?

不使用DISTINCT: SELECT Sdept FROM student 使用DISTINCT: SELECT DISTINCT Sdept FROM student

?

查询结果按列的值排序 使用ORDER BY子句,可将查询结果按某列或某几列 的值升序(ASC)或降序(DESC)排列 例:查询学生学号、姓名、年龄,按年龄升序排列 SELECT Sno 学号, Sname 姓名, Sage 年龄

FROM student
ORDER BY Sage ASC

注:默认情况下为升序,故ASC可以省略 降序排序时DESC不能省略

2. 选择表中若干元组(行) 查询过程中,可以使用筛选条件,使得查询结果中 只包含满足条件的元组(记录行) 查询满足条件的元组是通过WHERE子句实现的。 WHERE子句中常用的查询条件如下:
查询条件
比较 确定范围

表达式谓词
=、>、>=、<、<=、<>(或!=)、NOT <比较运算符> BETWEEN…AND、NOT BETWEEN…AND

确定集合

IN、NOT IN

相似字符串匹配 LIKE、NOT LIKE 空值 多重条件 IS NULL、IS NOT NULL AND、OR

带条件的查询-比较大小 例1:查询计算机系全体学生的姓名 SELECT Sname

FROM student
WHERE Sdept = '计算机系' 例2:查询所有不小于20岁的学生的姓名及年龄 SELECT Sname, Sage FROM student WHERE Sage >= 20

带条件的查询-确定范围 条件:列名|表达式

[NOT] BETWEEN 下限 AND 上限
例:查询年龄20~25岁间的学生的姓名及年龄 SELECT Sname, Sage FROM student WHERE Sage BETWEEN 20 AND 25 等价于: SELECT Sname, Sage FROM student WHERE Sage >= 20 AND Sage >= 25

带条件的查询-确定集合
? ?

用于查找列值属于指定集合的元组 条件格式: 列名 [NOT] IN (常量1, 常量2, …, 常量n) 例:查询信息系和计算机系学生的姓名和性别 SELECT Sname, Ssex

FROM student
WHERE Sdept IN ('信息系', '计算机系')

带条件的查询-相似字符串匹配

用于查询指定列中与匹配模板相似的元组
条件: 列名 [ NOT ] LIKE 匹配字符串模板 模板中可包含如下四种通配符: _ % [] [^ ] 代表任意单个字 代表任意长度的字符串(包括0个) 匹配[ ]中的任意一个字符 如[xyz]表示匹配x、y、z中的任意一个 不匹配[ ]中任意一个字符

相似字符串匹配(Cont.) 例1:查询姓?李?的学生的信息

SELECT * FROM student
WHERE Sname LIKE '李%' 例2:查询姓?王?、姓?贾?的学生的信息 SELECT * FROM student WHERE Sname LIKE '[王贾]%' 例3:查询学号第2、3位为52的学生的信息 SELECT * FROM student

WHERE Sno LIKE '_52%'

带条件的查询-空值

空值(NULL):不确定的值
比较一个值是否为空,不能用普通比较运算符 必须使用?IS [ NOT ] NULL”来判断 例:查询无考试成绩的学生的学号和相应课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NULL

带条件的查询-多重条件 多条件查询: 使用逻辑运算符AND、OR构成条件表达式 例:查询计算机系年龄在20岁以下的

学生的学号、姓名和性别
SELECT Sno, Sname, Ssex

FROM student
WHERE Sdept = '计算机系' AND Sage < 20

3. 使用计算函数汇总数据
计算函数:也称为聚合函数或聚集函数

可对一组值进行计算并返回计算值
? ? ? ?

COUNT(*) SUM(列名) AVG(列名)

统计表中元组个数 计算列值总和(列须数值型 计算列值平均值(列须数值型

COUNT(列名) 统计本列列值个数

?
?

MAX(列名)
MIN(列名)

求列值中最大值
求列值中最小值

注:除COUNT(*)外,其他函数忽略NULL值

使用计算函数汇总数据

例1:统计学生表中学生的人数 SELECT COUNT(*) FROM student
例2:统计选修了课程的学生的人数 SELECT COUNT(DISTINCT Sno) FROM SC 例3:计算C01课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno = 'C01' 例4:查询选修C01课程的学生的最高分和最低分 SELECT MIN(Grade), MAX(Grade) FROM SC WHERE Cno = 'C01'

4. 对查询结果进行分组计算 GROUP子句: 依据某几列的值对结果分组 注意:若用GROUP子句,则目标列名列表中每个列 为分组依据列或计算函数。即非聚集字段全部出现 在GROUP子句中,否则将出错 格式:GROUP BY 分组依据列名列表 [ HAVING 组筛选条件 ] 例1:统计每门课程选课人数,列出课程号和人数 SELECT Cno AS 课程号, COUNT(Sno) AS 选课人数 FROM SC GROUP BY Cno

例2:查询每名学生的选课门数和平均成绩 SELECT Sno AS 学号, COUNT(*) AS 选课数, AVG(Grade) AS 平均成绩 FROM SC GROUP BY Sno 例3:要求同例2,以下语句错误! SELECT Sno 学号, Cno 课程号, COUNT(*) 选课数, AVG(Grade) 平均成绩 FROM SC GROUP BY Sno 因:每组只有一条记录, 该记录中Cno值不确定(有多个值)

结果分组(Cont.) 用HAVING子句可以对组的结果进行筛选 例:查询选修了3门以上课程的学生的学号 SELECT Sno AS 学号 FROM SC GROUP BY Sno HAVING COUNT(*) > 3

2.5.2 多表连接查询
多表连接查询: 多个相关联表中获取信息 ? 实际上是计算多个表的笛卡尔积(一种关系运算) 连接查询主要包括内连接、自连接和外连接 1. 内连接 使用内连接: 两个表的相关字段满足连接条件,则从 两个表中提取数据并拼接成新记录 内连接格式:FROM 表1 [INNER] JOIN 表2 ON 连接条 连接条件的一般格式: 表名1.列名1 <比较运算符> 表名2.列名2

注意:参与比较的两个列在语义上必须相同

多表连接查询-内连接(Cont.) 例如,两表根据条件 number = scale 进行内连接
number name 1 2 张山 李斯 scale Address

1 1
2 2

北京 上海
西安 成都

结果

number name scale 1 1 2 2 张山 张山 李斯 李斯 1 1 2 2

Address 北京 上海 西安 成都

例1:查询每名学生的情况及其选课情况。

SELECT *
FROM student JOIN SC ON student.Sno = SC.Sno

注意: 结果中有重复的Sno列
例2:查询计算机系学生选课情况, 列出学生的姓名、课程号和成绩

SELECT Sname, Cno, Grade
FROM student JOIN SC ON student.Sno = SC.Sno WHERE Sdept = '计算机系'

可为表指定别名,以简化书写。但一旦指定别名,用 到该表名的地方都必须使用别名 例3:查询信息系选修VB的课程的学生的成绩

列出学号、姓名、课程名和成绩
SELECT a.Sno, a.Sname, c.Cname, b.Grade FROM student a JOIN SC b ON a.Sno = b.Sno JOIN course c ON c.Cno = b.Cno WHERE a.Sdept = '信息系'

AND c.Cname = 'VB编程语言'

2. 多表连接查询-自连接

自连接: 特殊的内连接,把一个表自己与自己做内连接
即物理上一个表,逻辑上是两个表

使用自连接时必须为自连接的表起两个别名,使之在逻 辑上成为两个表
自连接的格式:

SELECT <别名1|别名2>.列名1, <别名1|别名2>.列名2
FROM 表名 [AS] 别名1 JOIN 表名 [AS] 别名2 ON 连接条件 …(其它子句)

2. 多表连接查询-自连接(Cont.) 例:查询与李勇在同一个系的学生的姓名和系名 SELECT S2.Sname, S2.Sdept FROM student S1 JOIN student S2 ON S1.Sdept = S2.Sdept

WHERE S1.Sname = '李勇'
AND S2.Sname != '李勇'

3. 多表连接查询-外连接 在内连接中,只有满足连接条件的元组才在结果中。 结果中需要不满足连接条件的元组时:外连接

外连接只限制一个表的数据必须满足连接条件,而另 一个表中的数据可以不满足连接条件
外连接格式: FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON 连接条件 LEFT [OUTER] JOIN称为左外连接,它限制表2中 的数据必须满足连接条件,表1不限

RIGHT [OUTER] JOIN称为右外连接,它限制表1中 的数据必须满足连接条件,表2不限

多表连接查询-外连接 例:查询学生选课情况,包括选课及未选课的学生 SELECT a.Sno, Sname, Cno, Grade FROM student a LEFT JOIN SC ON a.Sno = SC.Sno 可知: LEFT JOIN:左表(表1)数据全在结果中 RIGHT JOIN:右表(表2)数据全在结果中

不满足条件的那个表的列在结果中的值均为NULL

2.5.4 子查询
子查询:嵌入某个SELECT、INSERT、UPDATE 或 DELETE语句中的SELECT语句
子查询可以嵌套多层 子查询允许出现在任何能够使用表达式的地方, 一般位于外层语句的WHERE子句或HAVING子句 中,与比较运算符或逻辑运算符一起构成查询条件 嵌套查询的一般处理方法: 先处理子查询,其结果作为父查询的条件 涉及多表的查询,可连接查询,也可嵌套查询 若查询结果来自一个关系,适用于嵌套查询

1. 使用子查询进行基于集合的测试
子查询返回的结果是一个集合 用IN(或NOT IN)操作符比较集合中的数据 例1:查询与宋江在同一个系的学生 SELECT Sno, Sname, Sdept

FROM student
WHERE Sdept IN ( SELECT Sdept FROM student WHERE Sname = '宋江? ) AND Sname != '宋江'

例2:查询成绩小于60分的学生及其课程
SELECT a.Sno, Sname, Sdept, b.Cname, Grade FROM student a JOIN SC ON a.Sno = SC.Sno JOIN course b

ON b.Cno = SC.Cno
WHERE a.Sno IN

( SELECT Sno
FROM SC WHERE Grade < 60) AND Grade < 60

2. 使用子查询进行比较测试
?
?

要求子查询返回的结果是单一值,不能是集合
用比较运算符与子查询返回的值进行比较

例:查询选修了c02课程且成绩大于此课程平均成绩的 学生,列出学号和成绩。
SELECT Sno, Grade FROM SC

WHERE Cno = 'c02'
AND Grade >

( SELECT AVG(Grade)
FROM SC WHERE Cno = 'c02? )

3. 使用子查询进行存在性测试
先执行外层查询,再执行子查询 子查询返回的是一个逻辑值(真或假) 用EXISTS(或NOT EXISTS)确定外层查询的数 据是否是满足要求的结果

子查询不需指定列名,一般用星号(*)代替。
例:查询选择了c01课程的学生姓名 SELECT Sname FROM student a WHERE EXISTS ( SELECT * FROM SC WHERE Sno = a.Sno AND Cno = 'c01? )

?

小结 SQL语言的发展

?
? ? ?

常用数据类型
数据定义语句:表的建立、修改、删除

数据的增、删、改操作
数据查询操作
?

单表查询
? 无条件、条件、分组、排序、聚集函数计算

?

多表连接查询
? 内连接、自连接、左外连接、右外连接

?

子查询

练习题 1. 利用本章提供的三个表(Student、Course和SC) 实现如下操作,写出相应的SQL语句,并上机验证 结果 a)查询SC表中的全部记录 b)查询计算机系学生的姓名、年龄 c)查询成绩小于60分的学生的学号、姓名、课程名和 分数 d)统计每门课程的选课人数和考试最高分 e)查询哪些课程没有学生选修,列出课程号和课程名

练习题 2. 写出创建下表的SQL语句,表名为test, 表结构如下: COL1:字符型,主码

COL2: 整型,允许空值
COL3:字符型,长度为10,不允许空值

3. 用ALTER语句修改上题创建的表结构:
a) 增加一个新列COL4,货币类型,允许空值 b) 将COL3的长度改为20

3.2 视图
?

内容
视图概念 ? 定义视图 ? 删除视图
?

§2.6 视图
视图:数据库基本表中取出的数据组成的逻辑窗口, 其它表中导出的虚表 数据库中只存放视图的定义,不存放其包含的数据, 这些数据仍存放在原来的基本表中

视图对应于数据库结构中的外模式
视图可以建立在基本表上,也可以建立在其他的视图 上,即可以在一个视图之上再定义视图 一个基本表上可以建立多个视图。一个视图也可以建 立在多个基本表上

从视图中查询与从表中查询的语句基本相同

视图的示意图
视图

基本表1

基本表2

视图有很多优点:

简化操作
多表间查询时,通过视图实现,将复杂查询封装在 视图内部,用户不必学习复杂的数据库查询技术

增加了数据安全性
授权用户才能使用视图;而且只能查询授权的列

分割数据
通过列名表纵向分割,通过WHERE横向分割

提供逻辑独立性
对视图进行修改时,不必修改使用视图的应用程序

定义视图的CREATE VIEW语句的一般格式: CREATE VIEW 视图名 [(视图列名表)] AS 子查询语句 其中子查询可是任意SELECT语句,但要注意: 子查询中通常不含ORDER BY和DISTINCT子句 定义视图时要么指定全部视图列,要么全部省略。若 省略视图的属性列名,则其列名同子查询列名 如下三种情况须明确指定组成视图的所有列名: ? 某个目标列是计算函数或列表达式; ? 多表连接时选出了几个同名列作为视图的字段; ? 需要在视图中为某个列选用新的更合适的列名。

1. 定义单源表视图 单源表视图:视图来自一个基本表的部分行、列 可查询和修改数据操作 例:建立信息系学生的视图 CREATE VIEW V_IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept = '信息系? CREATE VIEW语句的结果仅保存视图定义

对视图查询时,才按其定义从相应基本表中查询数 如:SELECT * FROM IS_Student

2. 定义多源表视图

多源表视图:视图来自于多个表的数据。
多源表视图一般只用于查询,不用于修改数据

例:建立信息系选了‘c01?号课程的学生的视图 CREATE VIEW V_IS_S1(Sno, Sname, Grade) AS SELECT Student.Sno, Sname, Grade
FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = '信息系' AND SC.Cno = 'c01'

3. 在已有视图上定义新视图

视图也可以建立在已存在的视图上。
例:建立信息系选修了‘c01?号课程且 成绩在90分以上的学生的视图 CREATE VIEW V_IS_S2 AS

SELECT Sno, Sname, Grade
FROM V_IS_S1

WHERE Grade >= 90

4. 定义带表达式的视图

定义视图时,可根据需要设置一些从基本表中的列 派生出来属性列,在这些派生属性列中保存经过 计算的值 例:定义一个反映学生出生年份的视图
CREATE VIEW BT_S(Sno, Sname, Sbirth)

AS
SELECT Sno, Sname, 2006-Sage FROM Student

5. 含分组统计的视图 视图的子查询中允许含有GROUP BY子句,但这样的 视图只能用于查询,不能用于修改数据 例:定义存放每个学生学号及平均成绩的视图

CREATE VIEW S_G(Sno, AverageGrade)
AS

SELECT Sno, AVG(Grade)
FROM SC GROUP BY Sno

注:如果子查询的选择列表包含表达式或统计函数, 且在查询中也没有为这样的列指定列标题,则定义 视图的语句中必须要指定视图属性列的名字

删除视图 删除视图的SQL语句的格式为: DROP VIEW 视图名 例:删除IS_Student视图。 DROP VIEW IS_Student

注意:如果被删除的视图是作为其他视图的数据源, 则导出视图将无法再使用。同样,如果作为视图的 基本表被删除了,则视图也将无法使用

SQL语言的数据定义 例7-5 :创建SC表, 包括学号、课程号和成绩三个属性, 属性组(学号,课程号)为主键 CREATE TABLE SC (
?

?
?

学号 CHAR(8) NOT NULL, 课程号 CHAR(6) NOT NULL, 成绩 SMALLINT, PRIMARY KEY(学号,课程号)

);
Page 90

数据库设计__定义基本表_2

S表的结构

I表的结构

C表的结构
Page 91

数据库设计__定义基本表_3 创建表与表之间的联系

Page 92

3. 数据查询 例7-6 :选择表中若干列

⑴ 查询全体学生的详细记录 SELECT * ? FROM S; ⑵ 查询全体学生的学号与姓名 ? SELECT IDStu, NameStu ? FROM S; ⑶ 查询全体学生的姓名及其出生年份 ? SELECT 姓名, Year(出生年份) ? FROM S;
Page 93

Page 94

SQL查询语句例_1 例7-7 :选择表中若干列

⑴ 查询全体学生的详细记录 SELECT * ? FROM S; ⑵ 查询全体学生的学号与姓名 ? SELECT 学号, 姓名 ? FROM S; ⑶ 查询全体学生的姓名及其出生年份 ? SELECT 姓名, Year(出生年份) ? FROM S;
Page 95

SQL查询语句例_2 例7-8 :选择表中若干元组

⑴ 查询信息51班学生的情况 ? SELECT * ? FROM S ? WHERE 班级='信息51'; ⑵ 查询信息51班选修了课程的女生的学号 ? SELECT DISTINCT 学号 ? FROM SC ? WHERE 班级='信息51' AND 性别='女'; 其中,DISTINCT限定学号相同的记录只显示 一个,AND是逻辑与运算符

Page 96

SQL查询语句例_3 ⑶ 查询年龄在20~23岁之间的学生的姓名、 班级和年龄 ? SELECT 姓名, 班级, 年龄 ? FROM S ? WHERE 年龄 BETWEEN 20 AND 23; ⑷ 查询选修050012号课程的学生的学号和成绩, 查询结果按成绩降序排列 ? SELECT 学号, 成绩 ? FROM SC ? WHERE 课程号='050012' ? ORDER BY 成绩 DESC;
Page 97


更多相关文档:

数据库学生选课系统

数据库设计——学生选课系统设计内容: 我们组设计的是学生选课管理系统, 选课管理系统是学校教务系统中很庞大、 很复杂的一个数据库系统, 在这里我们将其简化,只...

数据库设计选课管理系统

五、 数据库实施(一) 、建立数据库 CREATE DATABASE Student ON (NAME=’学生选课管理库’, FILENAME='E:\db\学生选课管理库.mdf’, SIZE= 3, MAXSIZE= 50...

数据库 学生选课管理系统

数据库课程设计 设计题目 组长姓名 组长学号 专业班级 1 1 指导教师学生选课管理系统 XXXX 1132101102 计算机应用技术 <1>班 XXXX 信息工程院 计算机科学与技术 系...

学生选课数据库设计

北京理工大学珠海学院课程设计说明书 2011 — 2012 学年第 1 学期 题目: 学生选课数据库设计 学生选课数据库设计 学 院: 商学院 专业班级: 专业班级: 学号姓名...

数据库设计--网上选课

数据库设计--网上选课_计算机软件及应用_IT/计算机_专业资料。网上选课系统 CSE 数据库设计——网上选 课系统数据库原理与应用 目录 1 引言 3 1.1 定义 3 2...

数据库学生选课系统

数据库学生选课系统_工学_高等教育_教育专区。《SQL Server 数据库》 课程设计 题目:学生选课系统 系专班学 别: 业: 级: 号: 学生姓名: 指导教师: ×××...

数据库课程设计学生选课管理系统

3. 数据库设计 3.1. 概念结构设计根据需求分析,设计出“学生选课系统”的 E-R 图,该 E-R 图包括四个实 体:学生信息实体、教师信息实体、管理员信息实体、...

选修课管理系统文档(数据库课程设计)

数据库系统设计课程设计任务书设计题目 选修课管理系统 指导教师 班级 学生 本系统主要实现三大功能: 一、学生选课 1)学生登陆 2)进行初选,复选,补退选 3)查询...

数据库课程设计 学生选修课系统

数据库课程设计 学生选修课系统_工学_高等教育_教育专区。数据库课程设计成绩10信计2012-2013(一) 《数据库原理及应用》课程设计 设计题目 设计时间 学生姓名 学生...

数据库设计(学生选课系统)

数据库设计(学生选课系统)_工学_高等教育_教育专区。本实验将设计出一个高校网上选课数据库系统,其要求简要如下: (1) 系统用户由三类组成:教师、学生和管理员。...
更多相关标签:
防骗选修课走红 | 爆笑防骗选修课走红 | 选修课 | 超星选修课网址 | 超星选修课 | 超星尔雅选修课 | 选修课论文格式 | kfsj.bjedu.cm选修课 |
网站地图

文档资料共享网 nexoncn.com copyright ©right 2010-2020。
文档资料共享网内容来自网络,如有侵犯请联系客服。email:zhit325@126.com