《数据库概论》实验
四. 退出Oracle
当想停止工作并离开SQL *PLUS，则可以在SQL *PLUS命令提示符下输入EXIT或QUIT命令。
SQL> EXIT [RETURN] 或 SQL> QUIT [RETURN]
五. 提示信息
1. 建议使用方法二登录ORACLE。在此方式下，SQL *PLUS有一种缓冲区编辑器，可以用来在SQL >提示符下交互式地输入命令和修改命令。
(1) 在SQL>提示符下，可直接进行拷贝和粘贴操作。 (2) 在SQL>提示符下，可直接对命令缓冲区内容进行编辑。 SQL> ED[RETURN]
出现缓冲区编辑器窗口，自动显示刚刚执行的命令语句。修改编辑，保存退出，返回SQL> 命令提示符状态。
SQL>/[RETURN]
自动执行缓冲区编辑器中的命令语句。
2. 一个事务从执行一条SQL更新语句(INSERT、UPDATE、DELETE)开始，当出现如下任一情况时，就结束该事务：
? 执行COMMIT或ROLLBACK命令(这是一种显式划分事务的方式)； ? 发出DDL命令(如执行CREATE TABLE 命令等)； ? LOGOFF； ? 程序异常结束。
如果在SQL *PLUS中没有上述四种情况，当退出系统时，系统会自动提交最后一个事务。 3. 查询本用户账号下的所有数据库表的名称： SELECT * FROM TAB； 4. 查看一个数据库表的表结构： DESC 表名；
5. ORACLE常用的数据类型： 数据类型 数字型 表示方式 NUMBER(n) NUMBER(n,d) CHAR(n) 说 明 可包括数字0-9、正负号(+、-)和十进制小数点 n: 总有效位数(范围1-38) s: 小数点后面的位数 可包含字母、数字和特殊字符(+、-、%、$) n <= 255； n1 <= 2000 日期和时间。存储方式为： 字符型 VARCHAR(n) VARCHAR2(n1) 日期型 DATE 世纪、年、月、日、时、分、秒。缺省日期格式： 中文版为 09-10月-99，英文版为09-OCT-99。 利用ORACLE提供的日期掩码格式，可按需要显示日期与时间。例如显示系统日期或显示系统日期、时间：
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL；
或 SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MM:SS') FROM DUAL；
36/42
《数据库概论》实验
6. 几个常用的ORACLE函数
(1) LTRIM()、RTRIM()、TRIM()：去除前导、后缀的空格或字符。 (2) UPPER()、LOWER()：字符大小写转换。
(3) SUBSTR(str，m，n)：截取子串。其中，str：字符串；m：开始位置；n：长度。
7. 在Oracle中，可以使用SET AUTOCOMMIT命令来设置事务自动提交环境。一旦设置了自动提交，则在每个SQL操作INSERT、UPDATE或DELETE语句之后，系统就自动进行了提交。一般情况下，最好不要使用自动提交方式。
设置自动提交的语句格式： SET AUTOCOMMIT IMMEDIATE; SET AUTOCOMMIT ON; 设置非自动提交的语句格式： SET AUTOCOMMIT OFF;
Oracle以非自动提交方式为缺省方式。
8. 查询其他用户的数据（必须为授权的用户），应在表名前增加用户名作为限定词。格式为：
用户名.表名
9. 查询本用户所有表上的完整性约束名称。
SELECT Constraint_name, Constraint_type, Table_name, R_constraint_name
FROM User_constraints;
Constraint Type PRIMARY KEY UNIQUE KEY FOREIGN KEY CHECK, NOT NULL Character P U R C
10. 查询本用户所有表上的非空约束和CHECK约束条件。 SELECT Constraint_name, Search_condition FROM User_constraints;
11．查询本用户所有表上定义的完整性约束的约束列名
SELECT Constraint_name, Table_name, Column_name FROM User_cons_columns;
37/42
《数据库概论》实验
六. 教学管理数据库设计 各表的数据结构和数据如下：
1． 学生情况表 Student
SNO(C,5) SNAME(C,10) SDEPT(C,2) SCLASS(C,2) SSEX(C,2) SAGE(N,2) 学号 姓名 系 班级 性别 年龄 96001 马小燕 96002 黎明 96003 刘东明 96004 赵志勇 97001 马蓉 97002 李成功 97003 黎明 97004 李丽 CS 01 CS MA IS CS IS CS 01 01 02 01 03 02 02 女 男 男 女 男 女 女 男 21 18 20 19 20 19 19 18 CS：计算机系 MA：数学系 IS：信息系
男 18 MA 02 96005 司马志明 CS 主关键字：SNO 非空字段：SNAME，SDEPT，SCLASS
2． 课程名称表 Course 3． 教师授课表 Teach
CNO(C,3) CNAME(C,16) CTIME(N,3)
学时数 课程号 课程名称 TNAME(C,8) TSEX(C,2) CNO(C,3) TDATE 教师姓名 性别 课程号 授课日期 TDEPT(C,2) 系 001 数学分析 144 002 普通物理 144 003 微机原理 80 004 数据结构 72 005 操作系统 80 006 数据库原理 80 007 编译原理 60 008 程序设计 40 王成刚 李正科 严敏 赵高 李正科 刘玉兰 王成刚 马悦 男 男 女 男 男 女 男 女 004 003 001 004 003 006 004 008 1999.9.5 CS 1999.9.5 CS 1999.9.5 MA 1999.9.5 IS 2000.2.23 MA 2000.2.23 CS 2000.2.23 IS 2000.9.6 CS 主关键字：CNO 候选关键字：CNAME 主关键字：TNAME，CNO，TDEPT 参照关系：Course(CNO)
4． 成绩表 Score
SNO(C,5) CNO(C,3) SCORE(N,5,2) SNO(C,5) CNO(C,3) SCORE(N,5,2) SNO(C,5) CNO(C,3) SCORE(N,5,2) 学号 课程号 分数 学号 课程号 分数 学号 课程号 分数 96001 001 77.5 96001 003 89 96001 004 86 96001 005 82 96002 001 88 96002 006 90 96005 004 92 96005 005 90 96005 006 89 96005 007 76 96003 001 69 97001 008 95 96004 001 87 96003 003 91 97002 003 91 97002 004 97002 006 92 97004 005 90 97004 006 85 96002 003 92.5 97001 001 96 主关键字：SNO，CNO 参照关系：Student(SNO)，Course(CNO)
38/42
《数据库概论》实验
七. SQL语句
1． SQL数据定义语句：
例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。
例1-2： (修改数据库表) 在Student表中增加SSEX(C,2) 字段。
例1-3： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。 例1-4： (建立索引) 为Score表按学号升序和课程号降序分别建立索引,索引名分别为SC_NDXSNO和SC_NDXCNO。为Score表按分数降序建立索引，索引名为GRADE_NDX。 例1-5： (删除索引) 删除索引SC_NDX_SNO。
例1-6： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。 例1-7： (修改数据库表) 删除成绩表Score的参照完整性约束关系。 例1-8： (修改数据库表) 添加成绩表Score的参照完整性约束关系。
2． SQL数据操纵语句：
例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。 例2-2：(多行插入) 将表Student中在计算机系(‘CS’)的学生数据插入到表S1中。
例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表
Student_Gr中。
例2-4： (修改数据) 将S1表中所有学生的年龄加2。
例2-5： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。 例2-6： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。 例2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。 例2-8： (删除数据) 删除Score表中学号为‘96001’的成绩信息，根据返回信息解释其原因。 例2-9： (删除数据) 删除Score表中课程号为‘003’ 的成绩信息，根据返回信息解释其原因。 例2-10： (删除数据) 删除学生表S1中学号以96打头的学生信息。 例2-11： (删除数据) 删除数据库表S1中所有学生的数据。 例2-12：(删除表) 删除数据库表S1和Student_Gr。
3． SQL数据查询语句：
例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。 例3-2： (不选择重复行) 求选修了课程的学生学号。 例3-3： (选择表中的所有列) 求全体学生的详细信息。 例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。
39/42
《数据库概论》实验
例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。 例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。
例3-7： (比较大小条件) 求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。 例3-8： (确定范围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。 例3-9： (确定范围条件) 求年龄不在19岁与22岁之间的学生的学号和年龄。 例3-10：(确定集合条件) 求在下列各系的学生信息：数学系、计算机系。 例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。 例3-12：(匹配查询) 求姓名是以“李”打头的学生。 例3-13：(匹配查询) 求姓名中含有“志”的学生。
例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。 例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。
例3-16：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。
例3-17：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数。 例3-18：(组函数) 求学生总人数。
例3-19：(组函数) 求选修了课程的学生人数。 例3-20：(组函数) 求计算机系学生的平均年龄。
例3-21：(组函数) 求选修了课程001的最高、最低与平均成绩。 例3-22：(分组查询) 求各门课程的平均成绩与总成绩。 例3-23：(分组查询) 求各系、各班级的人数和平均年龄。
例3-24：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。
SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT WHERE SDEPT=’CS’ GROUP BY SDEPT;
例3-25：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。 SELECT SAGE FROM STUDENT GROUP BY SNO;
例3-26：(分组查询) 求学生人数不足3人的系及其相应的学生数。 例3-27：(分组查询) 求各系中除01班之外的各班的学生人数。
例3-28：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。 例3-29：(自然连接查询) 求学生号以及其选修课程的课程号和成绩，但查询结果中只能有一个SNO字段。
例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。
例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。 例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。
例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩。
40/42
数据库实验习题
