数据库复习(3)
来源:互联网 发布:苏霍伊 知乎 编辑:程序博客网 时间:2024/04/19 12:12
第三章 关系数据库标准语言SQL
3.1 SQL概述
SQL
功能SELECT
数据定义 CREATE
, DROP
, ALTER
数据操纵 INSERT
, UPDATE
, DELETE
数据控制 GRANT
, REVOKE
SQL
支持关系数据库三级模式结构
<作图>
- 基本表
- 存储文件
- 视图
3.2 学生-课程数据库
学生表 | Student(Sno,Sname,Ssex,Sage,Sdept)课程表 | Course(Cno,Cname,Cpno,Ccredit)学生选课表 | SC(Sno,Cno,Grade)
3.3 数据定义
CREATE SCHEMA
DROP SCHEMA
表 CREATE TABLE
DROP TABLE
ALTER TABLE
视图 CREATE VIEW
DROP VIEW
索引 CREATE INDEX
DROP INDEX
3.3.1 模式的定义与删除
定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;create schema <模式名> authorization <用户名>;
如果没有指定 <模式名>,那么 <模式名> 隐含为 <用户名>
定义模式实际上定义了一个命名空间。在这个空间中可以定义该模式包含的数据库对象,例如基本表、视图、索引等。
删除模式
DROP SCHEMA <模式名> <CASCADE|RESTRICT>;
CASCADE
(级联)- 删除模式的同时把该模式中所有的数据库对象全部删除
RESTRICT
(限制)- 如果该模式中定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。当该模式中没有任何下属的对象时 才能执行。
3.3.2 基本表的定义、删除与修改
定义基本表
CREATE TABLE <表名>( <列名> <数据类型> <列级完整性约束条件>, <列名> <数据类型> <列级完整性约束条件>, <列名> <数据类型> <列级完整性约束条件> <表级完整性约束条件> );
建立学生表Student
,学号是主码,姓名取值唯一。
CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/ Sname CHAR(20) UNIQUE, /* Sname取唯一值*/ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20));
建立一个“学生选课”表SC
CREATE TABLE SC( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno, Cno), /* 作为表级完整性进行定义*/ FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件,Sno是外码,被参照表是Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级完整性约束条件, Cno是外码,被参照表是Course*/);
数据类型
CHAR(n)
VARCHAR(n)
INT
SMALLINT
NUMERIC(p, d)
REAL
Double Precision
FLOAT(n)
DATE
TIME
模式与表
- 每一个基本表都属于某一个模式
- 一个模式包含多个基本表
- 定义基本表所属模式
DBA
用户可以设置搜索路径,然后定义基本表
SET search_path TO “S-T”,PUBLIC;Create table Student(......);
修改基本表
ALTER TABLE <表名> ADD <新列名> <数据类型> 完整性约束 DROP <完整性约束名> ALTER COLUMN<列名> <数据类型>;ALTER TABLE Student ADD S_entrance DATE;ALTER TABLE Student ALTER COLUMN Sage INT;ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
DROP TABLE <表名> [RESTRICT| CASCADE];
3.3.3 索引的建立与删除
一、建立索引
语句格式
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> ( <列名> [<次序>], <列名> [<次序>], <列名> [<次序>], );CREATE CLUSTER INDEX Stusname ON Student(Sname);CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC)
CLUSTER
为聚簇索引(指索引项的顺序与表中记录的物理顺序一致的索引组织)。一个表只有一个。UNIQUE
表示唯一索引。
二、删除索引
DROP INDEX <索引名>;
3.4 数据查询
3.4.1 单表查询
一、 选择表中的若干列
-- 小写字母SELECT Sname,'Year of Birth:', 2004-Sage, LOWER(Sdept)FROM Student;-- 列别名SELECT Sname NAME, 'Year of Birth:' BIRTH, 2000-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENTFROM Student;
二、选择表中的若干元组
1.消除取值重复的行,如果没有指定DISTINCT
关键词,则缺省为ALL
SELECT DISTINCT SnoFROM SC;
WHERE Sage BETWEEN 20 AND 23; WHERE Sage NOT BETWEEN 20 AND 23; WHERE Sdept IN ( 'IS','MA','CS' );WHERE Sdept NOT IN ( 'IS','MA','CS' );-- 匹配串为固定字符串WHERE Sno LIKE '200215121';-- 匹配串为含通配符的字符串 (% 、_)WHERE Sname LIKE '刘%';WHERE Sname LIKE '欧阳_ _';-- 使用换码字符将通配符转义为普通字符WHERE Cname LIKE 'DB\_Design' ESCAPE '\';-- ESCAPE '\' 表示“ \” 为换码字符
三、ORDER BY子句
ASC
:排序列为空值的元组最后显示DESC
:排序列为空值的元组最先显示
四、聚集函数
COUNT
SUM
AVG
MAX
MIN
SELECT COUNT(*)SELECT COUNT(DISTINCT Sno)
五、GROUP BY子句
SELECT Cno, COUNT(Sno)FROM SCGROUP BY Cno;
SELECT SnoFROM SCGROUP BY SnoHAVING COUNT(*) >3;
HAVING
短语与WHERE
子句的区别:
- 作用对象不同
WHERE
子句作用于基表或视图,从中选择满足条件的元组HAVING
短语作用于组,从中选择满足条件的组
3.4.2 连接查询
连接查询:同时涉及多个表的查询
嵌套循环法(NESTED-LOOP
)
首先在表1中找到第一个元组,然后从头开始扫描表2,逐一查找满足连接件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。
表2全部查找完后,再找表1中第二个元组,然后再从头开始扫描表2,逐一查找满足连接条件的元组,找到后就将表1中的第二个元组与该元组拼接起来,形成结果表中一个元组。
- 重复上述操作,直到表1中的全部元组都处理完毕
一、等值与非等值连接查询
等值连接:连接运算符为=
SELECT Student.*,SC.*FROM Student,SCWHERE Student.Sno = SC.Sno;
自然连接:等值连接中把目标列中重复的属性列去掉
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student,SCWHERE Student.Sno = SC.Sno;
二、自身连接
- 自身连接
- 一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性,因此必须使用别名前缀
SELECT FIRST.Cno,SECOND.CpnoFROM Course FIRST,Course SECONDWHERE FIRST.Cpno = SECOND.Cno;
三、外连接
外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM StudentLEFT OUT JOIN SC ON (Student.Sno=SC.Sno);
四、复合条件连接
复合条件连接:
WHERE
子句中含多个连接条件
3.4.3 嵌套查询
嵌套查询概述
- 一个
SELECT-FROM-WHERE
语句称为一个查询块 - 将一个查询块嵌套在另一个查询块的
WHERE
子句或HAVING
短语的条件中的查询称为嵌套查询
SELECT Sname /*外层查询/父查询*/FROM StudentWHERE Sno IN ( SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= ' 2 ');
子查询的限制
- 不能使用
ORDER BY
子句 - 层层嵌套方式反映了
SQL
语言的结构化 - 有些嵌套查询可以用连接运算替代
不相关子查询
- 子查询的查询条件不依赖于父查询
- 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。
相关子查询
- 子查询的查询条件依赖于父查询
- 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表
- 然后再取外层表的下一个元组
- 重复这一过程,直至外层表全部检查完为止
嵌套查询的形式
一、带有IN
谓词的子查询
-- 将第一步查询嵌入到第二步查询的条件中 | -- 用自身连接完成: | SELECT Sno, Sname, Sdept | SELECT S1.Sno, S1.Sname, S1.SdeptFROM Student | FROM Student S1, Student S2WHERE Sdept IN ( | WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨'; SELECT Sdept | FROM Student | WHERE Sname= '刘晨' | ); | | -- 此查询为不相关子查询。 |
二、带有比较运算符的子查询
[例41]找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno, CnoFROM SC xWHERE Grade >= ( SELECT AVG(Grade) FROM SC y WHERE y.Sno = x.Sno );此查询为相关子查询。
三、带有ANY
(SOME
)或ALL
谓词的子查询
[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄SELECT Sname, SageFROM StudentWHERE Sage < ANY ( SELECT Sage FROM Student WHERE Sdept= ' CS' ) AND Sdept <> 'CS ' ; /*父查询块中的条件 */
四、带有EXISTS
谓词的子查询
1.EXISTS
谓词
- 存在量词
∃ - 带有
EXISTS
谓词的子查询不返回任何数据,只产生逻辑真值true
或逻辑假值false
。 - 若内层查询结果非空,则外层的
WHERE
子句返回真值 - 若内层查询结果为空,则外层的
WHERE
子句返回假值 - 由
EXISTS
引出的子查询,其目标列表达式通常都用*
,因为带EXISTS
的子查询只返回真值或假值,给出列名无实际意义
[例44]查询所有选修了1号课程的学生姓名。
思路分析:
本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值,用此值去检查SC关系
若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= ‘1’,则取此Student.Sname送入结果关系
用嵌套查询 | 用连接运算SELECT Sname | SELECT SnameFROM Student | FROM Student, SCWHERE EXISTS ( | WHERE Student.Sno=SC.Sno AND SC.Cno= '1'; SELECT * | FROM SC | WHERE Sno=Student.Sno AND Cno= '1' | ); |
不同形式的查询间的替换
- 一些带
EXISTS
或NOT EXISTS
谓词的子查询不能被其他形式的子查询等价替换 - 所有带
IN
谓词、比较运算符、ANY
和ALL
谓词的子查询都能用带EXISTS
谓词的子查询等价替换
用EXISTS
/NOT EXISTS
实现全称量词(难点)
SQL
语言中没有全称量词(For all)- 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词: (\forall x)P ≡\neg (\exists x(\neg P))
[例39]查询与“刘晨”在同一个系学习的学生。-- 可以用带EXISTS谓词的子查询替换:SELECT Sno,Sname,SdeptFROM Student S1WHERE EXISTS ( SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨' );[例46] 查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ));
3.4.4 集合查询
集合操作的种类
- 并操作
UNION
- 交操作
INTERSECT
- 差操作
EXCEPT
参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。SELECT *FROM StudentWHERE Sdept= 'CS'UNIONSELECT *FROM StudentWHERE Sage<=19;
UNION
:将多个查询结果合并起来时,系统自动去掉重复元组。 UNION ALL
:将多个查询结果合并起来时,保留重复元组
3.4.5 Select语句的一般形式
SELECT [ALL|DISTINCT] FROM WHEREGROUP BYHAVING ORDER BY
3.5 数据更新
3.5.1 插入数据
两种插入数据方式
- 插入元组
- 插入子查询结果
一、插入元组
INSERTINTO Student (Sno, Sname, Ssex, Sdept, Sage)VALUES ('200215128', '陈冬', '男', 'IS', 18);INSERTINTO StudentVALUES ('200215126', '张成民', '男', 18, 'CS');
二、插入子查询结果
INSERTINTO Dept_age(Sdept,Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;
3.5.2 修改数据
UPDATE StudentSET Sage = 22WHERE Sno= '200215121';
[例7] 将计算机科学系全体学生的成绩置零。UPDATE SCSET Grade=0WHERE 'CS'= ( SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno );
3.5.3 删除数据
[例10] 删除计算机科学系所有学生的选课记录。DELETEFROM SCWHERE 'CS' = ( SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno );
3.6 视图
3.6.1 定义视图
- 组成视图的属性列名:全部省略或全部指定
- 子查询不允许含有
ORDER BY
子句和DISTINCT
短语 WITH CHECK OPTION
表示对视图进行UPDATE
,INSERT
和DELETE
操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
CREATE VIEW IS_StudentAS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS';
- 行列子集视图
- 若一个视图是从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,我们称这类视图为行列子集视图。
-- 基于多个基表的视图[例3] 建立计算机系选修了1号课程的学生视图。CREATE VIEW CS_S1(Sno, Sname, Grade)AS SELECT Student.Sno, Sname, Grade FROM Student, SC WHERE Sdept= 'CS' AND Student.Sno=SC.Sno AND SC.Cno= '1';
- 基于视图的视图
- 带表达式的视图
- 分组视图
二、删除视图
DROP VIEW <视图名>;
3.6.2 查询视图
RDBMS
实现视图查询的方法–视图消解法(View Resolution)
- 进行有效性检查
- 转换成等价的对基本表的查询
- 执行修正后的查询
3.6.3 更新视图
- 更新视图是指通过视图来插入(
INSERT
)、删除(DELETE
)和修改(UPDATE
)数据。由于视图是不实际存储数据的虚表,
因此对视图的更新, 最终要转换为对基本表的更新。 - 更新视图的限制:一些视图是不可更新的, 因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新
3.6.4 视图的作用
3.7 小结
- 数据库复习(3)
- 数据库复习3
- 数据库复习3
- 数据库复习(一)
- 数据库复习(1)
- 数据库复习(2)
- sql(数据库语言) 复习
- 数据库的复习(二)
- 数据库复习笔记(一)
- 数据库期末复习-(1)
- 数据库复习3——数据库完整性
- 数据库复习
- 数据库复习
- 数据库复习
- 数据库复习
- 数据库复习
- 数据库复习
- 数据库复习
- sql多表查询
- 海量数据处理算法—Bloom Filter
- Hibernate连接池配置
- redis 的基本操作
- Webview 和js之间安全交互
- 数据库复习(3)
- 使用Auto Layout中的VFL(Visual format language)--代码实现自动布局
- JAVA中的继承和实现的区别以及接口和抽象类的区别
- C++枚举
- Android端 WebP图片压缩与传输的一点探索
- 【微信开发】上传下载多媒体文件
- java 的transient关键字的作用
- 海量数据处理 算法总结
- 汉诺塔VII