数据库系统概论考试重点

第一章 绪论

1、数据库的四个基本概念

1）数据

• 描述事物的符号记录

2）数据库

• 概括地讲，数据库数据具有永久存储、有组织和可共享的三个基本特点。
• 严格地讲：数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按⼀定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

3）数据库管理系统

• 位于用户和操作系统之间的一层数据管理软件，和操作系统⼀样是计算机的基础软件。
• 主要功能：
  1、数据定义功能
  2、数据组织、存储和管理
  3、数据操纵功能
  4、数据库的事务管理和运行管理
  5、数据库的建立和维护功能
  6、其他功能（通信功能，数据转换功能，互访和互操作功能等）

4）数据库系统

• 由数据库、数据库管理系统（及其应用开发工具）、应用程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统。
  

2、数据模型

1）概念模型

• 概念模型也称信息模型，它是按⽤户观点来对数据和信息建模，主要⽤于数据库设计。

  名词	描述	举例
  实体	客观存在并可相互区别的事物	人
  属性	实体所具有的某⼀特性	人的鼻子、嘴巴
  码	唯⼀标识实体的属性	学号、员工号、部门号
  实体型	用实体名及其属性集合来抽象和刻画同类实体	含鼻子和嘴巴的人
  实体集	同⼀类型实体的集合	学校
  联系	实体之间的联系通常是指不同实体集之间的联系	⼀对⼀、⼀对多和多对多

2）关系模型

• 关系模型中数据的逻辑结构是一张二维表，它由行和列组成。

  学号	姓名	年龄	性别	系名	年级
  2013004	王小美	19	女	社会学	2013
  2013006	黄大鹏	20	男	商品学	2013
  2013008	张文斌	18	男	法律	2013
  ……	……	……	……	……	……

• 相关术语

  名词	描述
  元组	表中的一行
  属性	表中的一列
  码	也称码键，表中的某个属性组
  域	是一组具有相同数据类型的值的集合
  分量	元组中的一个属性

第二章 关系数据库

1、关系代数语言

• 关系代数是一种抽象的查询语言，它用对关系的运算来表达查询
• 三大要素：运算对象（关系）、运算符（集合运算符和专门的关系运算符）和运算结果（关系）
• 关系代数运算：并，差，笛卡儿积分，选择，投影及交，连接，除。
  

1）并运算

2）交运算

3）差运算

4）笛卡尔积

5）选择（selection）

• 选择也称之为限制，选择是针对的元组进行选择，选择出满足条件的元组
• 格式：σ_选择条件(所选择的表格)
  
  例题1：查询信息（IS）系所有学生
  σ_{Sdept=‘IS’}(Student)
  查询结果为
  
  例题2：查询年龄小于20岁的学生
  σ_Sage<20(Student)
  查询结果为：
  

6）投影

• 投影运算是针对属性进行选择的运算，即从表中选择出符合条件的一列，同时会自动去除某些重复行
• 格式：π_{需投影的列}(所选择的表格)
  例题1：查询学生的姓名和所在系
  π_Sname,Sdept(Student)
  查询结果为：
  
  例题2：查询Student表中有哪些系
  π_Sdept(Student)
  查询结果为：
  

7）连接

• 悬浮元组（Dangling tuple）
  两个关系R和S在做自然连接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，从而造成R中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组

①自然连接

• 把共同属性进行等值连接，去掉重复的列
  

②外连接

• 如果把悬浮元组也保存在结果关系中，而在其他属性上填空值(Null)，就叫做外连接
  

③左外连接

• 只保留左边关系R中的悬浮元组
  

④右外连接

• 只保留右边关系S中的悬浮元组
  

8）除

• R÷S表示保留R中满足S的，而且R中要去掉S的列。
  

第三章 SQL语言

1、数据定义

1）模式定义和删除

• 模式定义

  CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
  

  例题：为用户WANG定义一个学生-课程模式S-T

  CREATE SCHEMA "S-T" AUTHORIZATION WANG;
  

• 模式定义+创建表/视图/授权

  CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
  

  例题：为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1

  CREATE SCHEMA TEST AUTHORIZATION ZHANG
  CREATE TABLE TAB1(
  	COL1 SMALLINT,
  	COL2 INT,
  	COL3 CHAR(20),
  	COL4 NUMERIC(10,3),
  	COL5 DECIMAL(5,2)
  );
  

• 模式删除

  DROP SCHEMA <模式名> <CASCADE|RESTRICT>
  

• 删除模式，其中CASCADE和RESTRICT必须二选一

  • CASCADE（级联），删除模式的同时也把该模式的所有数据库对象删除
  • RESTRICT（限制），如果该模式下有下属对象，比如表、视图，就拒绝这个删除语句的执行

2）表的定义、删除，修改

• 表的定义

  CREATE TABLE <表名> (
  	<列名> <数据类型> [列级完整性约束条件]
  	[,<列名> <数据类型> [列级完整性约束条件]]
  	…
  	[,<表级完整性的约束条件>]	
  );
  

  例题：建立一个“学生”表Student

  CREATE TABLE Student(
  	Sno CHAR(9) PRIMARY KEY,  /* 列级完整性约束条件，Sno是主码 */
  	Sname CHAR(20) UNIQUE,    /* Sname 取唯一值 */
  	Ssex CHAR(2),
  	Sage SMALLINT,
  	Sdept CHAR(20)
  );
  

• 表的删除

  DROP TABLE <表名> [RESTRICT|CASCADE];
  

  • RESTRICT：如果删除的基本表被其他表的约束所引用、有视图、有触发器、有存储过程或函数，则此表不能被删除
  • CASCADE：删除基本表的同时，相关的依赖对象也会一起被删除

  例题：删除Student表

  DROP TABLE Student CASCADE;
  

• 表的修改

  ALTER TABLE <表名>
  [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
  [ADD <表级完整性约束>]
  [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
  [DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT]]
  [ALTER COLUMN <列名> <数据类型>];
  

  例题：向Student 表增加“入学时间”列，其数据类型为日期型

  ALTER TABLE Student ADD S_entrance DATE;
  

  例题：将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数

  ALTER TABLE Student ALTER COLUMN Sage INT;
  

  例题：增加课程名称必须取唯一值的约束条件

  ALTER TABLE Course ADD UNIQUE(Cname);
  

3）索引的建立、修改与删除

数据量比较大的时候，查询耗时间长，建立索引可以有效减少消耗时间
索引可以建立在一列或者多列上

• 建立索引

  CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
  ON <表名> (<列名> [<次序>] [,<列名> [<次序>]] …);
  

  • UNIQUE：此索引的每一个索引值只对应唯一的数据记录
  • CLUSTER：要建立的索引是聚簇索引，物理顺序与索引的逻辑顺序相同

  例题：为学生-课程数据库中的Student、Course和SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

  CREATE UNIQUE INDEX Stusno ON Student(Sno); 
  CREATE UNIQUE INDEX Coucno ON Course(Cno); 
  CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
  

• 修改索引

  ALTER INDEX <旧索引名> RENAME TO <新索引名>;
  

  例题：将SC表的SCno索引名改为SCSno

  ALTER INDEX SCno RENAME TO SCSno;
  

• 删除索引

  DROP INDEX <索引名>;
  

  例题：删除Student表的Stusname索引

  DROP INDEX Stusname;
  

2、数据查询

• 数据查询是数据库的核心操作。SQL 提供了SELECT 语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。
• 格式：

  SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]…
  FROM <表名或视图名> [,<表名或视图名>]|(<SELECT 语句>) [AS] <别名>
  [WHERE <条件表达式>]
  [GROUP BY <列名1> [HAVING <条件表达式>]]
  [ORDER BY <列名2> [ASC|DESC]];
  

1)单表查询

①、选择表中的若干列

例题：查询全体学生的学号与姓名

SELECT Sno,Sname
FROM Student;

例题：查询全体学生的姓名、学号和所在系

SELECT Sname,Sno,Sdept 
FROM Student;

例题：查询全体学生的详细记录

SELECT * 
FROM Student;

例题：查询全体学生的姓名及其出生年份

SELECT Sname,2022-Sage 
FROM Student;

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

SELECT Sname,'Year of Birth:',2022-Sage,LOWER(Sdept) 
FROM Student;

②、选择表中的若干元组

例题：查询选修了课程的学生学号

SELECT DISTINCT Sno 
FROM SC;

例题：查询计算机科学系全体学生的名单

SELECT Sname
FROM Student 
WHERE Sdept='cs';

例题：查询所有年龄在20岁以下的学生姓名及其年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<20;

例题：查询考试成绩不及格的学生的学号

SELECT DISTINCT Sno 
FROM SC 
WHERE Grade<60;

查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage 
FROM Student 
WHERE Sage BETWEEN 20 AND 23;

查询年龄不在 23 岁之间的学生姓名、系别和年龄。

SELECT Sname,Sdept,Sage 
FROM Student 
WHERE Sage NOT BETWEEN 20 AND 23;

查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。

// 方式一
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN('CS','MA','IS');
// 方式二
SELECT Sname,Ssex 
FROM Student 
WHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';

查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别

SELECT Sname,Ssex 
FROM Student 
WHERE Sdept NOT IN('CS','MA','IS');

查询学号为 201215121 的学生的详细情况

SELECT * 
FROM Student 
WHERE Sno LIKE '201215121';

查询所有姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Ssex 
FROM Student 
WHERE Sname LIKE '刘%';

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

SELECT Sname 
FROM Student 
WHERE Sname LIKE '欧阳_';

查询名字中第二个字为“阳”的学生的姓名和学号

SELECT Sname,Sno 
FROM Student 
WHERE Sname LIKE '_阳%'；

查询所有不姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Ssex 
FROM Student 
WHERE Sname NOT LIKE '刘%'；

查询DB_Design课程的课程号和学分

SELECT Cno,Ccredit 
FROM Course 
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

SELECT * 
FROM Course 
WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';

某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;

查所有有成绩的学生学号和课程号

SELECT Sno Cno 
FROM SC 
WHERE Grade IS NOT NULL;

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

SELECT Sname 
FROM Student 
WHERE Sdept='CS' AND Sage<20;

③、ORDER BY子句

用ORDER BY子句对查询结果按照一个或多个属性列的升序（ASC ）或降序（DESC）排列，默认值为升序
查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

SELECT Sno,Grade 
FROM SC 
WHERE Cno='3'
ORDER BY Grade DESC;

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

SELECT * 
FROM Student 
ORDER BY Sdept,Sage DESC;

④、聚集函数

聚集函数	作用
COUNT(*)	统计元组个数
COUNT([DISTINCT|ALL] <列名>)	统计一列中值的个数
SUM([DISTINCT|ALL] <列名>)	计算一列值的总和（此列必须是数值型）
AVG([DISTINCT|ALL] <列名>)	计算一列值的平均值（此列必须是数值型）
MAX([DISTINCT|ALL] <列名>)	求一列值中的最大值
MIN([DISTINCT|ALL] <列名>)	求一列值中的最小值

• 如果指DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL 为默认值），则表示不取消重复值。
• 当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。COUNT (*) 是对元组进行计数，某个元组的一个或部分列取空值不影响 COUNT 的统计结果
• 聚集函数只能用于 SELECT 子句和 GROUP BY 中的HAVING子句，WHERE子句中是不能用聚集函数作为条件表达式的

例题1：查询学生总人数

SELECT COUNT(*)
FROM Student;

例题2：查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno) 
FROM SC;

例题3：计算选修1号课程的学生平均成绩

SELECT AVG(Grade) 
FROM SC 
WHERE Cno='1';

例题4：查询选修1号课程的学生最高分数

SELECT MAX(Grade) 
FROM SC 
WHERE Cno='I';

例题5：查询学生201215012选修课程的总学分数

SELECT SUM(Ccredit) 
FROM SC,Course 
WHERE Sno='201215012' AND SC.Cno=Course.Cno;

⑤、GROUPBY 子句

GROUP BY 子句将查询结果按某一列或多列的值分组，值相等的为一组
例题1：求各个课程号及相应的选课人数

SELECT Cno,COUNT(Sno) 
FROM SC 
GROUP BY Cno;

例题2：查询选修了三门以上课程的学生学号

SELECT Sno 
FROM SC 
GROUP BY Sno 
HAVING COUNT(*)>3;

例题3：查询平均成绩大于等于90分的学生学号和平均成绩

SELECT Sno,AVG(Grade) 
FROM SC 
GROUP BY Sno 
HAVING AVG(Grade)>=90;

2）连接查询

①、等值与非等值连接查询

[<表名1>.]<列名> <比较运算符> [<表名2>.]<列名2>

• 当连接运算符为＝时，称为等值连接。使用其他运算符称为非等值连接。
• 在等值连接中把目标列中重复的属性列去掉则为自然连接
  查询每个学生及其选修课程的情况。

SELECT Student.*,SC.*
FROM Student,SC 
WHERE Student.Sno=SC.Sno;

查询每个学生及其选修课程的情况（自然连接）

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade 
FROM Student,SC 
WHERE Student.Sno=SC.Sno;

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

SELECT Student.Sno,Sname 
FROM Student SC 
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

②、自身连接
查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno 
FROM Course FIRST,Course SECOND 
WHERE FIRST.Cpno=SECOND.Cno;

③、外连接
左外连接列出左边关系中所有的元组， 右外连接列出右边关系中所有的元组。
④、多表连接
两个以上的表进行连接，称为多表连接
查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade 
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

3）嵌套查询

①、带有IN谓词的子查询
查询与“刘晨”在同一个系学习的学生

SELECT Sno,Sname,Sdept 
FROM Student 
WHERE Sdept IN (
	SELECT Sdept 
	FROM Student 
	WHERE Sname='刘晨'
);

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

SELECT Sno,Sname 
FROM Student 
WHERE Sno IN(
	SELECT Sno 
	FROM SC
	WHERE Cno IN(
		SELECT Cno
		FROM Course
		WHERE Cname='信息系统'
	)
);

②、带有比较运算符的子查询
找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno Cno 
FROM SC x
WHERE Grade >=(
	SELECT AVG(Grade) 
	FROM SC y 
	WHERE y.Sno=x.Sno
);

③、带有 ANY (SOME）或 ALL 谓词的子查询
查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和
年龄。

SELECT Sname,Sage 
FROM Student 
WHERE Sage<ANY(
	SELECT Sage 
	FROM Student 
	WHERE Sdept='CS'
	) 
AND Sdept  'CS';