大型数据库应用复习

大型数据库应用基础知识点

1.数据库系统分为客户-服务器模式 (C/S) 和三层客户-服务器 (B/S) 模式。
2.基于Web的数据库应用采用三层客户-服务器模式( B/S)，第一层为浏览器，第二层为Web服务器，第三层为数据库服务器。
3.模型(Model)是现实世界特征的模拟和抽象，数据模型(Data Model)是一种模型，它能实现对现实世界数据特征的抽象。
4.现实世界中的客观事物及其联系，按计算机系统的观点对数据进行建模，在机器世界中用逻辑模型(Logic Model)来描述，主要的逻辑模型有层次模型、网状模型和关系模型。
5.数据模型(Data Model)是现实世界数据特征的抽象，一般由数据结构、数据操作、数据完整性约束三部分组成。
6.数据模型是现实世界的模拟，它是按计算机的观点对数据建立模型，包含数据结构、数据操作和数据完整性三要素，数据模型有层次模型、网状模型、关系模型。
7.关系模型建立在严格的数学概念基础上，数据结构简单清晰，用户易懂易用，关系数据库是目前应用最为广泛、最为重要的一种数学模型。
8.选择(Selection)指选出满足给定条件的记录，它是从行的角度进行的单目运算，运算对象是一个表，运算结果形成一个新表。
9.投影(Projection)是选择表中满足条件的列，它是从列的角度进行的单目运算。
10.数据库系统的标准结构是三级模式结构，它包括外模式、模式和内模式。
11.外模式(External Schema)又称子模式或用户模式，位于三级模式的最外层，对应于用户级，它是某个或某几个用户所看到的数据视图，是与某一应用有关的数据的逻辑表示。
12.模式(Schema)又称概念模式，也称逻辑模式，位于三级模式的中间层，对应于概念级，它是由数据库设计者综合所有用户的数据，按照统一观点构造的全局逻辑结构，是所有用户的公共数据视图(全局视图)。一个数据库只有一个模式，它是由模式描述语言(模式DDL)来描述和定义的。
13.内模式(Internal Schema)又称存储模式，位于三级模式的底层，对应于物理级，它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。一个数据库只有一个内模式,它是由内模式描述语言(内模式DDL)来描述和定义的。
14.为了能够在内部实现这三个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两级映象：外模式/模式映象，模式/内模式映象。
15.概念设计阶段的目标是形成整体数据库的概念结构，它独立于数据库逻辑结构和具体的DBMS，描述概念结构的工具是E-R模型。
16.实体之间的联系，可分为一对一的联系、一对多的联系、多对多的联系。
17.Oracle数据库的体系结构包括逻辑结构、物理结构和总体结构，其中，逻辑结构为Oracle引入的结构，物理结构为操作系统所拥有的结构。
18.表空间(TableSpace)是Oracle数据库中数据的逻辑组织单位，通过表空间来组织数据库中的数据，数据库逻辑上由一个或多个表空间组成，表空间物理上是由一个或多个数据文件组成
19.表空间划分为若干段，段由若干个盘区组成，盘区由连续分配的相邻数据块组成。
20.每一个Oracle数据库都有一个或多个数据文件，每一个数据文件只能属于一个表空间，数据文件一旦加入到表空间，就不能从这个表空间中移走，也不能和其它表空间发生联系。
日志文件(Log File)用于记录对数据库进行的修改操作和事务操作，文件后缀名为”.LOG”。
21.控制文件(Control File)用于记录和维护整个数据库的全局物理结构，它是一个二进制文件，文件后缀名为”.CTL”。
22.表空间是存储数据文件的容器，表空间由数据文件组成，数据库的所有系统数据和用户数据都必须放在数据文件中。每一个数据库创建时，系统会默认地为它创建一个SYSTEM表空间
23.

SQL语言的功能	动词
数据定义	CREATE,ALTER,DROP
数据操纵	INSERT,UPDATE,DELETE
数据查询	SELECT
数据控制	GRANT,REVOKE

24.数据操纵语言（Data Manipulation Language, DML）
用于对数据库中的数据进行插入、修改、删除等操作，DML包括INSERT、UPDATE、DELETE等语句。
25.数据定义语言（Data Definition Language, DDL）
用于定义数据库对象，对数据库、数据库中的表、视图等数据库对象进行建立和删除,DDL包括CREATE、ALTER、DROP等语句。
26.数据查询语言（Data Query Language, DQL）
用于对数据库中的数据进行查询操作，例如用 SELECT语句进行查询操作。
27.数据控制语言（Data Control Language, DCL）
用于控制用户对数据库的操作权限，DCL包括GRANT、REVOKE等语句。
28.为了改变查询结果中显示的列标题，可以在列名后使用AS <列别名>。
29.使用SELECT子句对列进行查询时，可以对数字类型的列进行计算
30.逻辑运算符包括.AND(与)、OR(或)、 NOT(非)，NOT、AND和OR的使用是有优先级的，三者之中，NOT优先级最高，AND次之，OR优先级最低。
31.当有多个表达式时, 先处理算术运算、关系运算, 接着才处理逻辑运算符。
32.BETWEEN、NOT BETWEEN、IN是用于指定范围的三个关键字，用于查找字段值在（或不在）指定范围的行。
33.模式匹配使用LIKE谓词，LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar2和date类型的数据，返回逻辑值TRUE或FALSE。
34.COUNT函数用于计算组中满足条件的行数或总行数。
35.MAX函数用于求出一组数据的最大值，MIN函数用于求出一组数据的最小值，这两个函数都可以适用于任意类型数据。
36.GROUP BY子句用于指定需要分组的列。
37.SUM函数用于求出一组数据的总和，AVG函数用于求出一组数据的平均值，这两个函数只能针对数值类型的数据。
38.HAVING子句用于对分组按指定条件进一步进行筛选，过滤出满足指定条件的分组。

39.在连接谓词表示形式中，连接条件由比较运算符在WHERE子句中给出，将这种表示形式称为连接谓词表示形式，连接谓词又称为连接条件。
40.经常用到的连接有等值连接、自然连接和自连接等
41.在以JOIN关键字指定连接的表示方式中,在FROM子句中用JOIN关键字指定连接的多个表的表名,用ON子句指定连接条件。
42.内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。内连接是系统默认的，可省略INNER关键字。
43.集合查询将两个或多个SQL语句的查询结果集合并起来，利用集合进行查询处理以完成特定的任务，使用四个集合操作符(Set Operator) UNION、UNION ALL、INTERSECT和MINUS，将两个或多个SQL查询语句结合成一个单独SQL查询语句。
44.在构成复合查询的各个单独的查询中，列数和列的顺序必须匹配，数据类型必须兼容。
45.UNION语句将第一个查询中的所有行与第二个查询的所有行相加，消除重复行并且返回结果。
46.UNION ALL语句与标准的UNION语句工作方式基本相同，唯一不同的是UNION ALL不会从列表中滤除重复行。
47.INTERSECT操作会获取两个查询，对值进行汇总，并且返回同时存在于两个结果集中的行。只由第一个查询、或者第二个查询返回的那些行不包含在结果集中。
48.MINUS集合操作会返回所有从第一个查询中有但是第二个查询中没有的那些行。
49.在IN子查询中，首先执行括号内的子查询，再执行父查询，子查询的结果作为父查询的查询条件。
50.关键字ALL、SOME和ANY用于对比较运算的限制，ALL指定表达式要与子查询结果集中每个值都进行比较，当表达式与子查询结果集中每个值都满足比较关系时，才返回TRUE，否则返回FALSE；SOME和ANY指定表达式要只与子查询结果集中某个值满足比较关系时，就返回TRUE，否则返回FALSE。
51.在EXISTS子查询中，EXISTS谓词只用于测试子查询是否返回行，若子查询返回一个或多个行，则EXISTS返回TRUE，否则返回FALSE，如果为NOT EXISTS，其返回值与EXIST相反。
52.

运算符	含义
IN	与子查询返回结果中任何一个值相等
NOT IN	与子查询返回结果中任何一个结果都不相等
>ANY	比子查询返回结果中某一个值大
=ANY	与子查询返回结果中某一个值相等
<ANY	比子查询返回结果中某一个值小
>ALL	比子查询返回结果中所有值都大
<ALL	比子查询返回结果中任何一个值都小
EXISTS	子查询至少返回一行时条件为TRUE
NOT EXISTS	子查询不返回任何一行时条件为TRUE

53.视图(View)通过SELECT查询语句定义，它是从一个或多个表(或视图)导出的，用来导出视图的表称为基表(Base Table)，导出的视图称为虚表。在数据库中，只存储视图的定义，不存放视图对应的数据，这些数据仍然存放在原来的基表中。
54.视图可以由一个基表中选取的某些行和列组成，也可以由多个表中满足一定条件的数据组成，视图就像是基表的窗口，它反映了一个或多个基表的局部数据。
55.视图(View)的优点：
★ 方便用户的查询和处理，简化数据操作。
★ 简化用户的权限管理，增加安全性。
★ 方便数据共享。
★ 屏蔽数据库的复杂性。
★ 可以重新组织数据。
56.更新视图是指通过试图进行插入、删除和修改数据，由于视图是不存储数据的虚表，对视图的更新最终转化为对基表的更新。通过更新视图数据可更新基表的数据，但只有满足条件的视图才能更新。
57.数据完整性一般包括域完整性、实体完整性、参照完整性，用户定义完整性
58.域完整性指列数据输入的有效性，又称列完整性，通过CHECK约束、DEFAULT约束、NOT NULL约束、数据类型和规则等实现域完整性。
59.实体完整性要求表中有一个主键，其值不能为空且能唯一地标识对应的记录，又称为行完整性，通过PRIMARY KEY约束、UNIQUE约束、索引或 IDENTITY 属性等实现数据的实体完整性。
60.参照完整性保证主表中的数据与从表中数据的一致性，又称为引用完整性，参照完整性确保键值在所有表中一致，通过定义主键(PRIMARY KEY)与外键(FOREIGN KEY)之间的对应关系实现参照完整性。
61.如果定义了两个表之间的参照完整性，则要求：
● 从表不能引用不存在的键值。
● 如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改。
● 如果要删除主表中的某一记录，应先删除从表中与该记录匹配的相关记录。
62.总括起来， Oracle数据库中的数据完整性包括域完整性、实体完整性、参照完整性，和实现上述完整性的约束，其中：
● CHECK约束，检查约束，实现域完整性。
● NOT NULL约束，非空约束，实现域完整性。
● PRIMARY KEY约束，主键约束，实现实体完整性。
● UNIQUE KEY约束，唯一性约束，实现实体完整性。
● FOREIGN KEY约束，外键约束，实现参照完整性。
63.通过PRIMARY KEY约束定义主键，一个表只能有一个PRIMARY KEY约束，且PRIMARY KEY约束不能取空值，Oracle为主键自动创建唯一性索引，实现数据的唯一性。
64.通过UNIQUE约束定义唯一性约束，为了保证一个表非主键列不输入重复值，应在该列定义UNIQUE约束。
65.PRIMARY KEY约束与UNIQUE约束主要区别如下：
● 一个表只能创建一个PRIMARY KEY约束，但可创建多个UNIQUE约束。
● PRIMARY KEY约束的列值不允许为NULL，UNIQUE约束的列值可取NULL。
● 创建PRIMARY KEY约束时，系统自动创建聚集索引，创建UNIQUE约束时，系统自动创建非聚集索引。PRIMARY KEY约束与UNIQUE约束都不允许对应列存在重复值
66.常量(Constant)的值在定义时被指定，不能改变，常量的使用格式取决于值的数据类型。
67.变量(Variable)和常量都用于存储数据，但变量的值可以根据程序运行的需要随时改变，而常量的值在程序运行中是不能改变的。
68.%TYPE属性提供了变量和数据库列的数据类型，在声明一个包含数据库值的变量时非常有用。
69.%ROWTYPE属性声明描述表的行数据的记录。
70.PL/SQL的基本逻辑结构包括顺序结构、条件结构和循环结构。
71.PL/SQL不是普通的程序语言，而是面向过程语言和SQL语言的结合，可使用SELECT-INTO语句将SQL语言查询结果存入变量。
72.简单CASE语句设定一个变量的值，然后顺序比较WHEN关键字后给定值，如果遇到第1个相等的给定值，则执行THEN关键字后的赋值语句，并结束CASE语句。
73.搜索CASE语句在WHEN关键字后设置布尔表达式，选择第一个为TRUE的布尔表达式，执行THEN关键字后的语句，并结束CASE语句。
74.循环结构的功能是重复执行循环体中的语句，直至满足退出条件退出循环，下面分别介绍LOOP-EXIT-END循环、LOOP-EXIT-WHEN-END循环、WHILE-LOOP-END循环和FOR-IN-LOOP-END循环。
75.<循环体>中包含需要重复执行的语句，IF后条件表达式值为TRUE，执行EXIT退出循环；否则继续循环，直到满足条件表达式的条件退出循环。
76.异常是在Oracle数据库中运行时出现的错误，使语句不能正常运行，并可能造成更大的错误甚至整个系统崩溃。
77.异常处理代码在EXCEPTION部分实现，当遇到预先定义的错误时，错误被相应的WHEN-THEN语句捕捉，THEN后的语句代码将执行，对错误进行处理。
78.用户可以通过自定义异常来处理错误的发生，调用异常处理需要使用RAISE语句。
79.在Oracle中，创建用户定义函数使用CREATE FUNCTION语句。
80.参数类型：参数类型有IN、OUT、IN OUT三种模式，默认为IN模式。
■ IN模式：表示传递给IN模式的形参，只能将实参的值传递给形参，对应IN模式的实参可以是常量或变量。
■ OUT模式：表示OUT模式的形参将在函数中被赋值，可以将形参的值传给实参，对应OUT模式的实参必须是变量。
■ IN OUT模式：IN OUT模式的形参既可以传值也可以被赋值，对应IN OUT模式的实参必须是变量。
81.游标包括显式游标(Explicit Cursor)和隐式游标(Implicit Cursor)，显式游标的操作要遵循声明游标、打开游标、读取数据和关闭游标等步骤，而使用隐式游标不需执行以上步骤，只需让PL/SQL处理游标并简单地编写SELECT语句。
82.存储过程是一种命名PL/SQL程序块，它将一些相关的SQL语句，流程控制语句组合在一起，用于执行某些特定的操作或者任务。将经常需要执行的特定的操作写成过程，通过过程名，就可以多次调用过程，从而实现程序的模块化设计，这种方式提高了程序的效率，节省了用户的时间。
83.存储过程具有以下特点：
● 存储过程在服务器端运行，执行速度快。
● 存储过程增强了数据库的安全性。
● 存储过程允许模块化程序设计。
● 存储过程可以提高系统性能。
84.自定义函数和存储过程区别

存储过程	函数
实现在数据库中完成某种特定的操作和任务	通常是对数据的操作（选择或计算）
声明用PROCEDURE	声明用FUNCTION
声明时用不需要描述返回值类型	声明时用需要描述返回值类型，要用return语句
作为一个独立PL/SQL语句执行	不能独立执行，必须作为表达式的一部分调用，或用select语句
通过out、in out可以有零个或多个返回值，没有return语句	通过return语句只有一个返回值，也可通过out带回返回值

85.触发器是一种特殊的存储过程，与表的关系密切，其特殊性主要体现在不需要用户调用，而是在对特定表（或列）进行特定类型的数据修改时激发。
86.触发器与存储过程的差别如下：
● 触发器是自动执行，而存储过程需要显式调用才能执行。
● 触发器是建立在表或视图之上的，而存储过程是建立在数据库之上的。
87.触发器用于实现数据库的完整性，触发器具有以下优点：
● 可以提供比CHECK 约束、FOREIGN KEY约束更灵活、更复杂、更强大的约束。
● 可对数据库中的相关表实现级联更改。
● 可以评估数据修改前后表的状态，并根据该差异采取措施。
● 强制表的修改要合乎业务规则。
88.Oracle的触发器有三类：DML 触发器、INSTEAD OF触发器、系统触发器。
89.DML 触发器是当发生数据操纵语言 (DML) 事件时要执行的操作。DML 触发器用于在数据被修改时强制执行业务规则，以及扩展 CHECK 约束、FOREIGN KEY约束的完整性检查逻辑。
90.由于DML语句可能作用于多行，因此触发器的PL/SQL语句可能为作用的每一行运行一次，这样的触发器称为行级触发器(row-level trigger)；也可能为所有行只运行一次，这样的触发器称为语句级触发器(statement-level trigger)。如果未使用FOR EACH ROW子句，指定为语句级触发器，触发器激活后只执行一次。如果使用FOR EACH ROW子句，指定为行级触发器，触发器将针对每一行执行一次。WHEN子句用于指定触发条件。
91.在行级触发器执行过程中，PL/SQL语句可以访问受触发器语句影响的每行的列值。“:OLD.列名”表示变化前的值，“:NEW.列名”表示变化后的值。
92.创建DML 触发器的语法结构包括触发器定义和触发体两部分。触发器定义包含指定触发器名称、指定触发时间、指定触发事件、指定触发对象、指定触发级别等。触发体由PL/SQL语句块组成，它是触发器的执行部分。
93.INSTEAD OF触发器(替代触发器)，一般用于对视图的DML触发。当视图由多个基表连接而成，则该视图不允许进行INSERT、UPDATE和DELETE等 DML操作。在视图上编写INSTEAD OF触发器后，INSTEAD OF触发器只执行触发体中的PL/SQL语句，而不执行DML语句，这样就可以通过在INSTEAD OF触发器中编写适当的代码，进行对组成视图的各个基表的操作。
94.Oracle提供的系统触发器可以被数据定义语句DDL事件或数据库系统事件触发。
95.Oracle数据库系统采用用户、角色、权限、概要文件、审计等安全管理策略来实行数据的安全性。
96.户是数据库的使用者和管理者，用户管理是Oracle数据库安全管理的核心和基础。每个连接到数据库的用户都必须是系统的合法用户，用户要使用Oracle的系统资源，必须拥有相应的权限。
97.权限是预先定义好的执行某种SQL语句或访问其他用户模式对象的能力。权限分为系统权限和数据库对象权限两类。
98.系统权限是指在系统级控制数据库的存取和使用的机制，即执行某种SQL语句的能力。例如，启动、停止数据库，修改数据库参数，连接到数据库，以及创建、删除、更改模式对象（如表、视图、过程等）等权限。
99.对象权限是指在对象级控制数据库的存取和使用的机制，即访问其他用户模式对象的能力。例如，用户可以存取哪个用户模式中的哪个对象，能对该对象进行查询、插入、更新操作等。
100.系统权限的授予使用GRANT语句。
101.用WITH ADMIN OPTION，则允许被授予者进一步为其他用户或角色授予权限，此即系统权限的传递性。
102.数据库管理员或者具有向其他用户授权的用户可以使用REVOKE语句将已经授予的系统权限收回。
103.Oracle对象有下列9种权限。
（1）SELECT：读取表、视图、序列中的行。
（2）UPDATE：更新表、视图和序列中的行。
（3）DELETE：删除表、视图中的数据。
（4）INSERT：向表和视图中插入数据。
（5）EXECUTE：执行类型、函数、包和过程。
（6）READ：读取数据字典中的数据。
（7）INDEX：生成索引。
（8）REFERENCES：生成外键。
（9）ALTER：修改表、序列、同义词中的结构。
104.角色(Role)是一组权限，可以授予用户和其它角色，也可以从用户和其它角色中收回。
105.角色、用户及权限是一组有密切关系的对象，既然角色是一组权限的集合，那么他被授予某个用户时才能有意义。
106.角色所对应的权限集合中可以包含系统权限和对象权限。角色可以授予另外一个角色，但需要避免将角色授予它本身，也不能循环授予。
107.DBA可以为数据库用户组创建用户自定义的角色，赋予一般的权限需要。
108.当角色被建立后，没有任何权限，可以使用GRANT语句给角色授予权限，同时可以使用REVOKE语句取消角色的权限。
109.使用SET ROLE语句为数据库用户的会话设置角色的启用和禁用。

查询例题

在course表中查询没有任课教师的课程名称

1 2	select cname from course where tno is null

在score表中统计平均成绩在80以上的学生的平均分和课程数

select sno,avg(grade) ,count(*)
  from score
  group by sno
  having avg(grade)>=80

在score表中统计每门课程的人数和平均分

1
2
3

select cno,count(*),avg(grade) 
  from score
  group by cno

在teacher表中统计各职称的人数

1
2
3

select title,count(*) 
  from teacher
  group by title

查询计算机专业学生的最高、最低和平均学分

1
2
3

select max(tc),min(tc),avg(tc)
 from student
 where speciality='计算机'

查询计算机专业及选修了英语的学生

SELECT sno, sname, ssex
  FROM student
  WHERE speciality='计算机'
UNION
SELECT a.sno, a.sname, a.ssex 
  FROM student a, score b,course c
  WHERE a.sno=b.sno AND b.cno= c.cno and c.cname='英语';

查找选修了8001课程且为计算机专业学生的姓名课程名称及成绩（用JION关键字实现连接）

1
2
3

SELECT a.sname AS 姓名, b.cname AS 课程名, c.grade AS 成绩
  FROM student a join score c on a.sno=c.sno join course b on b.cno=c.cno
  WHERE a.speciality='计算机'and b.cno='8001';

查询既选修了英语又选修了数据库系统的学生的学号、姓名、出生日期和专业

SELECT a.sno AS 学号, a.sname AS 姓名, a.sbirthday AS 出生日期, a.speciality AS 专业
  FROM student a, course b, score c
  WHERE a.sno=c.sno AND b.cno=c.cno AND b.cname='英语'
INTERSECT 
SELECT a.sno AS 学号, a.sname AS 姓名, a.sbirthday AS 出生日期, a.speciality AS 专业
  FROM student a, course b, score c
  WHERE a.sno=c.sno AND b.cno=c.cno AND b.cname='数据库系统';

查询既选修了’1201’又未选修’1004’的学生的学号、姓名、出生日期和专业。

SELECT a.sno AS 学号, a.sname AS 姓名, a.sbirthday AS 出生日期, a.speciality AS 专业
  FROM student a,  score c
  WHERE a.sno=c.sno AND b.cno='1201'
MINUS
SELECT a.sno AS 学号, a.sname AS 姓名, a.sbirthday AS 出生日期, a.speciality AS 专业
 FROM student a,  score c
  WHERE a.sno=c.sno AND b.cno='1004';

查询了选修英语的学生的姓名和成绩

1
2
3

select a.sname, c.grade 
from student a,course b,score C
where a.sno=c.sno and b.cno=c.cno and b.cname='英语'

选修了高等数学且成绩在80分以上的学生的情况

1
2
3

select a.*
from student a,course b,score C
where a.sno=c.sno and b.cno=c.cno and b.cname='高等数学'　and c.grade>=80

查询数字电路任课教师的姓名和性别

1
2
3

select d.tname, d.tsex 
from course b, teacher d
where b.tno= d.tno and b.cname='数字电路'

查询选修计算机学院教师讲授课程的学生姓名和成绩

1
2
3

select a.sname,c.grade
from student a,course b,score C，teacher d
where a.sno=c.sno and b.cno=c.cno and　b.tno= d.tno and d.school='计算机学院'

查询选修了通信学院教师每门课程的平均成绩

select b.cno,   avg(c.grade)
from course b,score C，teacher d
where  b.cno=c.cno and　b.tno= d.tno and d.school='通信学院'
group by b.cno

查询student表中女生学分在50以上的学生学号、姓名、专业

1
2
3

SELECT sno,sname, speciality 
FROM student
where ssex='女'and tc>50

在course表中学分是4的课程编号和课程名称

1
2
3

SELECT cno,cname
FROM course
where credit=4

在teacher表中职称在副教授以上的教师信息

1
2
3

SELECT *
FROM teacher
where title in ('副教授','教授');

在score表中查询成绩不在60-70之间的学生信息

1
2
3

SELECT *
FROM score
where grade not between 60 and 70

在score表中统计平均成绩在80以上的学生的平均分和课程数

select sno,avg(grade) ,count(*)
  from score
  group by sno
  having avg(grade)>=80

查询了选修高等数学的学生的姓名和成绩

1
2
3

select a.sname, c.grade 
from student a,course b,score C
where a.sno=c.sno and b.cno=c.cno and b.cname='高等数学'

查询计算机网络任课教师的姓名和性别

1
2
3

select d.tname, d.tsex 
from course b, teacher d
where b.tno= d.tno and b.cname='计算机网络'

用子查询实现,查询数字电路任课教师的姓名和性别

1
2
3

select  tname,  tsex 
from    teacher  
where  tno in(select tno from course where cname='数字电路')

创建一个只读视图,实现选修了英语且成绩在80分以上的学生的情况

create or replace  view view1
as
select a.*
from student a,course b,score C
where a.sno=c.sno and b.cno=c.cno and b.cname='英语'　and c.grade>=80
with read only;

查询通信专业学生的最高、最低和平均学分

1
2
3

select max(tc),min(tc),avg(tc)
 from student
 where speciality='通信'

存储过程例题

创建一个带参数的存储过程，查询指定学号学生的总学分,并写出调用过程;把实现该功能的存储过程改写成函数的定义和完整定义形式。

create or replace procedure spTc(p_sno in char)
as
credit number;
begin
select tc into credit from student
where sno=p_sno;
dbms_output.put_line(credit);
end;

credit or replace function spTcS(p_sno in char)
return number
as
credit number;
begin 
select tc into credit from student
where sno=p_sno;
return credit;
end;

创建一个带输出参数的存储过程spNumber，查找指定专业的学生人数（包含存储过程调用）

create or replace spNumber(p_speciality in char,p_num out number)
as
begin
select count(*) into p_num from student
where speciality=p_speciality;
end
declare
v_num number;
begin
spnumber('计算机',v_num);
dbms_output.put_line(v_num);
end;

编写函数实现，输入学生的姓名和课程名查询学生的成绩（包含函数调用过程）

CREATE OR REPLACE FUNCTION funGrade(p_sname IN char,p_cname IN char)
RETURN number
AS
result number;
BEGIN
select grade INTO result
from student a,course b,score c
where a.sno=c.sno and b.cno-c.cno and sname=p_sname and cname=p_cname;
RETURN(result);
END funGrade;
DECLARE
v_gd number;
BEGIN
v_gd:=funGrade('小明';'英语');
DBMS_OUTPUT.PUT_LINE('小明的英语成绩是: '||v_gd);
END;

创建一个存储过程spNumberAvg，输入学号后，将该生所选课程数和平均分存人输出参数内（包含存储过程调用）。

CREATE OR REPLACE PROCEDURE spNumberAvg(p_sno IN CHAR,p_num OUT NUMBER,p_avg OUT NUMBER)
AS
BEGIN
select count(cno) into p_num
from score
where sno=p_sno;
select avg(grade) into p_avg
from score
where sno=p_sno;
END;
DECLARE
v_num number;
v_avg number;
BEGIN
spNumberAvg('0111101',v_num,v_avg);
DBMS_OUTPUT.PUT_LINE('学号0111101的学生的选课数是: '||v_num||', 平均分是: '||avg);
END;

创建一个存储过程spSpecialityCnameAvg,求指定专业和课程的平均分。
解：

(1)
CREAT OR REPLACE PROCEDORE spSpecialityCnameAvg(p_spec IN student. Speciality%TYPE, p_cname IN course.cname%TYPE, p_avg OUT number )
  AS
BEGIN
  SELECT AVG（grade）TNTO p_avg
FROM student a, course b, score c
WHERE a.sno=c.sno AND b.cno=c.cno AND a.Speciality=p_spec AND b.cname=p_cname;
END;
(2)
DECLARE 
v_avg number(4,2);
BEGIN
spSpecialityCnameAvg(‘计算机’，’数据库系统’, v_avg )
  DBMS_OUTPUT.PUT_LINE(‘计算机专业数据库系统的平均分是：’||v_avg);
END;

创建一个存储过程spCnameMax,求指定课程号的课程名和最高分。
解：

（1）
CREAT OR REPLACE PROCEDORE spCnameMax(p_cno IN course. cno%TYPE, p_cname OUT course.cname%TYPE, p_max OUT number )
AS
BEGIN
  SELECT cname TNTO p_cname
     FROM course 
     WHERE cno=p_cno;
  SELECT MAX(grade) TNTO p_max
     FROM course a, score b
     WHERE a.cno=b.cno AND a.cno=p_cno
 END;
(2)
DECLARE 
v_cnamecourse.cname%TYPE;
v_max number
BEGIN
spCnameMax(‘1012’, v_cname, v_max);
DBMS_OUTPUT.PUT_LINE(‘课程号1012的课程名是:’||v_cname ||’最高分是:’||v_max)
END;

创建一个存储过程spNameSchoolTitle,求指定教室编号的姓名、学院和职称。
解：

(1)
CREAT OR REPLACE PROCEDORE spNameSchoolTitle(p_tno IN teacher.tno%TYPE,p_tname OUT teacher.tname%TYPE,p_school OUT teacher.school%TYPE,p_title OUT teacher.title%TYPE)
AS
BEGIN
  SELECT tname INTO p_tname
     FROM teacher
     WHERE tno=p_tno;
  SELECT school INTO p_school
FROM teacher
     WHERE tno=p_tno;
END;
(2)
DECLARE 
v_tnameteacher.tname%TYPE;
v_schoolteacher.school%TYPE;
v_titleteacher.title%TYPE;
BEGIN
spNameSchoolTitle(‘100001’,v_tname,v_school,v_title);
DBMS_OUTPUT.PUT_LINE(‘教师编号100001的教师姓名是：’||v_tname ||’学院是：’|| v_school||’职称是:’||v_title);
END;

上机题目

第一次上机题目

1、创建商品表（goods），表结构如下：

列名	数据类型	允许NULL值	是否主键	说明
gid	char(6)		√	商品号
gname	char(20)			商品名称
gclass	char(6)			商品类型代码
price	number			价格
stockqt	number			库存量
gnotarr	number	√		未到货商品数量

CREATE TABLE goods
{
gid char(6) not null primary key,
gname char(20) not null,
gclass char(6) not null,
price number not null,
stockqt number not null,
gnotarr number null
};

2.在score表的grade列添加CHECK约束，限制grade列的值在0到100之间。

1 2	alter table score add constraint ck_grade check(grade>=0 and grade<=100);

3.删除student表的sno列的PRIMARY KEY约束，然后在该列添加PRIMARY KEY约束。

alter table student
drop constraint pk_sno;
alter table student
add constraint pk_sno primary key(sno);

4.在score表的sno列添加FOREIGN KEY约束，与student表中的主键列创建表间参照关系。

1 2	alter table score add constraint fk_sno foreign key(sno) references student(sno);

5.在course表中tno列创建外键，与teacher表中主键列创建表间参照关系。

1 2	alter table course add constraint fk_tno foreign key(tno) references teacher(tno);

第二次上机题目

1、查询score表中学号为121004，课程号为1201的学生成绩。

1	select grade as 成绩 from sroce where sno=’121004’ and cno=’1201’;

2、列出goods表的商品名称、商品价格和打7折后的商品价格。

1	select gname as 商品名称,price as 商品价格,price*0.7 as 打7折后的商品价格 from goods;

3、查询student表中姓周的学生情况。

1	select * from student where sname like '周%';

4、查询通信专业的最高学分的学生情况。

1	select a.sno,a.sname,b.cname,max(c.grade) from student a,course b,score c where a.sno=c.sno and b.cno=c.cno and a.speciality='通信';

5.查询1004课程的最高分、最低分和平均成绩。

1	select max(grade) as 1004课程最高分,min(grade) as 1004课程最低分,avg(grade) as 1004课程平均成绩 from score where cno=’1004’;

6.查询至少有3名学生选修且以4开头的课程号和平均分数。

select avg(grade),cno from score where cno in(
select cno from score where sno in(
select sno from score GROUP BY sno having count(*)>=3
) and cno like '4%'
) GROUP BY cno;

7.将计算机专业的学生按出生时间升序排列。

1	select * from student where speciality=’计算机’ order by sbirthday;

8.查询各门课程最高分的课程号和分数，并按分数降序排列。

1	select sno,max(grade) from score group by cno order by max(grade) desc;

第三次上机题目

1.查找选修了“英语”的学生姓名及成绩。

1
2
3

select a.sname, c.grade 
from student a,course b,score C
where a.sno=c.sno and b.cno=c.cno and b.cname=’英语’

2.查询选修了“高等数学”且成绩在80分以上的学生情况。

1
2
3

select a.*
from student a,course b,score c
where a.sno=c.sno and b.cno=c.cno and b.cname=’高等数学’　and c.grade>=80

3.查询选修某课程的平均成绩高于85分的教师姓名。

1
2
3

Select d.tname from coures b, score c, teacher d
Where b.cno=c.cno and b.tno=d.tno
Having avg(c.grade)>=85

4.查询既选学过‘1201’号课程，又未选学‘1004’号课程的学生姓名、性别和总学分。

SELECT a.sname AS 姓名, a.ssex AS 性别, a.tc AS 总学分
FROM student a, coures b, score c
WHERE a.sno=c.sno AND b.cno=’1201’
MINUS
SELECT a.sname AS 姓名, a.ssex AS 性别, a.tc AS 总学分
FROM student a, course b,score c
WHERE a.sno=c.sno AND b.cno=’1004’

5.查询每个专业最高分的课程名和分数。

Select b.cname, max(c.grade)
From student a,course b,score c
Where a.sno=c.sno and c.cno=b.cno
Group by a.speciality

6.查询通信专业的最高分。

1
2
3

Select max(c.grade)
From student a,score c
Where a.sno=c.sno and a.speciality=’通信’

7.查询数据库系统课程的任课教师。

1
2
3

Select d.tname 
From course b, teacher d
Where b.tno=d.tno and b.cname=’数据库系统’

8.查询成绩高于平均分的成绩记录。

1
2
3

Select sno,cno,grade, avg(grade)
From score
Having grade>avg(grade)

第四次上机题目

1.创建一个视图vwClassStudentCourseScore,包含学号、姓名、性别、班级、课程号、课程名和成绩等列，班级为201236，并查询视图的所有记录。

Create or replace view vwClassStudentCourseScore
As
Select a.sno,a.sname,a.ssex,a.sclass,b.cno,b.cname,c.grade
From student a,course b,score c
Where a.sno=c.sno and b.cno=c.cno and a.sclass=’201236’;
Select * from vwClassStudentCourseScore;

2.创建一个视图vwCourseScore,包含学生学号、课程名和成绩等列，然后查询该视图的所有记录。

Create or replace view vwCourseScore
As
Select c.sno,b.cno,c.grade
From course b, score c
Where b.cno=c.cno;
Select * from vwCourseScore;

3.创建一个视图vwAvgGradeStudentScore,包含学生学号、姓名和平均分等列，按平均分降序排列，再查询该视图的所有记录。

Create or replace view vwAvgGradeStudentScore
As
Select a.sno,a.sname,c.avg_grade
From student a,(select sno, avg(grade) as avg_grade from score Group by sno ORDER by avg(grade) DESC) c
Where a.sno=c.sno;
Select * from vwAvgGradeStudentScore;

4.计算1~100的偶数和。

DECLARE
  v_n number:=2;
  v_s number:=0;
  LOOP
v_s:=v_s+v_n;
v_n:=v_n+2;
EXIT WHEN v_n=101;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(‘1~100的偶数和为：’||v_s);
END;

5.打印1~100各个整数的平方，每10个打印一行。

DBCLARE
v_ n number := 1;
BEGIN
LOOP
DBMS OUTPUT. PUT(v_ n*v_ n||‘’);
IF MOD(v_ n,10) = 0 THEN
DBMS_OUTPUT. PUT_LINE(‘’);
END IF;
v_ n ;=v_n+1;
EXIT WHEN v_ n> 100;
END LOOP;
END;

第五次上机题目

1.查询每个学生的平均分，保留整数，丢弃小数部分。

1 2	select sno,trunc(avg(grade)) from score group by sno

2.使用用户定义函数查询不同班级的课程平均成绩。

创建函数：
CREATE OR REPLACE FUNCTION funAverage(v_sclass IN char,v_cno IN char)
  RETURN number
AS
  RESULT number;
BEGIN
  SELECT avg(grade) INTO result
FROM student a,score b
WHERE a.sno=b.sno AND sclass = v_sclass AND cno=v_cno;
RETURN(result);
 END funAverage;
调用函数
DECLARE 
v_avg number;
BEGIN
v_avg:=funAverage(‘201205’,’1004’);
  DBMS_OUTPUT.PUT_LINE(’201205班1004课程的平均成绩是:’||v_avg);
END;

第六次上机题目

1、创建一个存储过程spSpecialityCnameAvg,求指定专业和课程的平均分。
解：

(1)
CREAT OR REPLACE PROCEDORE spSpecialityCnameAvg(p_spec IN student. Speciality%TYPE, p_cname IN course.cname%TYPE, p_avg OUT number )
  AS
BEGIN
  SELECT AVG（grade）TNTO p_avg
FROM student a, course b, score c
WHERE a.sno=c.sno AND b.cno=c.cno AND a.Speciality=p_spec AND b.cname=p_cname;
END;
(2)
DECLARE 
v_avg number(4,2);
BEGIN
spSpecialityCnameAvg(‘计算机’，’数据库系统’, v_avg )
  DBMS_OUTPUT.PUT_LINE(‘计算机专业数据库系统的平均分是：’||v_avg);
END;

2、创建一个存储过程spCnameMax,求指定课程号的课程名和最高分。
解：

（1）
CREAT OR REPLACE PROCEDORE spCnameMax(p_cno IN course. cno%TYPE, p_cname OUT course.cname%TYPE, p_max OUT number )
AS
BEGIN
  SELECT cname TNTO p_cname
     FROM course 
     WHERE cno=p_cno;
  SELECT MAX(grade) TNTO p_max
     FROM course a, score b
     WHERE a.cno=b.cno AND a.cno=p_cno
 END;
(2)
DECLARE 
v_cnamecourse.cname%TYPE;
v_max number
BEGIN
spCnameMax(‘1012’, v_cname, v_max);
DBMS_OUTPUT.PUT_LINE(‘课程号1012的课程名是:’||v_cname ||’最高分是:’||v_max)
END;

3、创建一个存储过程spNameSchoolTitle,求指定教室编号的姓名、学院和职称。
解：

(1)
CREAT OR REPLACE PROCEDORE spNameSchoolTitle(p_tno IN teacher.tno%TYPE,p_tname OUT teacher.tname%TYPE,p_school OUT teacher.school%TYPE,p_title OUT teacher.title%TYPE)
AS
BEGIN
  SELECT tname INTO p_tname
     FROM teacher
     WHERE tno=p_tno;
  SELECT school INTO p_school
FROM teacher
     WHERE tno=p_tno;
END;
(2)
DECLARE 
v_tnameteacher.tname%TYPE;
v_schoolteacher.school%TYPE;
v_titleteacher.title%TYPE;
BEGIN
spNameSchoolTitle(‘100001’,v_tname,v_school,v_title);
DBMS_OUTPUT.PUT_LINE(‘教师编号100001的教师姓名是：’||v_tname ||’学院是：’|| v_school||’职称是:’||v_title);
END;