试验一熟悉OracleSQL1309剖析

1、实验一 熟悉 Oracle SQL*PLUS 环境上机目的1 熟悉 Oracle的命令操作环境 SQL*PLUS 。2 熟悉并掌握一些 SQL*PLUS 命令。熟悉 Oracle 的基本知识预备知识SQL*PlusSQL 是一种能够访问关系数据库（包括 Oracle 数据库）的语言。它能够 用在每一个 Oracle 工具中。SQL*Plus 是一个 Oracle 的产品，有自己的命令语言。SQL*Plus 有许多命令，表 1-1 只是列举了一部分常用命令。表 1-1 常用 SQL*Plus 命令SQL*Plus 命令缩写意义APPEND textA text把字符串增加到当前行的末尾CHANG

2、E /old/new/C/old/new/把当前行的旧字符串替换成新字符串CHANGE /text/C/text/把当前行中字符串删除CLEAR BUFFERCL BUFF从 SQL 缓冲区中删除所有行CONNECT userid/passwordCONN userid/passwor d在当前的登录下，击活其它的 Oracle 用户DEL删除当前行DESCRIBE tablenameDESC tablename显示任何数据库表的数据结构ED filename用默认的编辑器编辑保存的文件内容EXIT退出 SQL*PlusGET filename把以 filename 为名字的文件内容调入 SQ

3、L 缓冲区中INPUTI插入许多行INPUT textI text插入一个包含 text 字符串的行HELP击活 Oracle 内部的帮助部件HOST command在 SQL*Plus 中击活一个操作系统命 令LISTL显示 SQL 缓冲区的所有行LIST nL n显示 SQL 缓冲区中的一行到 n行LIST m nL m nSQL 缓冲区中的从第 m 行显示到第 n行PROMPT text当运行一个命令文件时，显示文本RUNR显示并运行在缓冲区中的当前 SQL 命令RUNFORM filename从 SQL*Plus 中 运 行 一 个 Oracle Forms 应用程序SAVE file

4、name把 SQL 缓冲区中的内容保存到以 filename 为名字的文件中，默认路径 为 orawinbinSPOOL filename写所有的后面的命令或者输出到一 个已经命名的文件中。 假脱机输入输 出文件的后缀为 .LISSPOOL OFF|OUTOFF 关闭假 脱机输 入输出文件；OUT 改变假脱机输入输出，送文件 到打印机上START filename filename运行以前保存的命令文件三上机练习1 SQL*Plus 的启动 当登录到操作系统后，有三种方法启动 SQL*Plus1) SQLPLUS(或 SQLPLUSW ，下同) 将看到如下的信息： SQL*Plus: Rele

5、ase (c) Copyright . All rights reserved. 请输入用户名： system 输入你的用户名字并按回车。SQL*Plus 将提示：请输入口令： oracle 输入你的密码并按回车。那么，将看到 SQL*Plus 的提示符：SQL2) SQLPLUS Username 再提示你输入密码。 例如： SQLPLUS scott Enter Password: tigerSQL3) SQLPLUS username/password 例如：SQLPLUS scott/tigerSQL4）断开与数据库的连接SQLDISCONNECT5）退出 SQL*PlusSQLEXI

6、T或：SQLQuit 2建表练习用 ORACLE SQL*PLUS 创建学生选课数据库、创建必要的完整性约束、插 入数据、体会完整性约束的意义，进行简单的数据查询。1) 、在桌面直接双击 SQL Plus图标，进入 ORACLE SQL Plus。2) 、输入用户名 /密码，为 system/oracle或者 scott/tiger。主机字符串不用写。3) 、创建学生表 S，如下所示：CREATE TABLE S(S# CHAR(4) NOT NULL,SNAME CHAR(8) NOT NULL,AGE SMALLINT,SEX CHAR(1),PRIMARY KEY(S#);结果如图所示：

7、4) 、然后依次创建课程表 C 和选课关系表，如下所示： CREATE TABLE C(C# CHAR(4),CNAME CHAR(10) NOT NULL,TEACHER CHAR(8),PRIMARY KEY(C#);CREATE TABLE SC(S# CHAR(4),C# CHAR(4), GRADE SMALLINT,PRIMARY KEY(S#,C#),FOREIGN KEY(S#) REFERENCES S(S#),然后按 Ctrl+C 复制到记FOREIGN KEY(C#) REFERENCES C(C#), CHECK(GRADE BETWEEN 0 AND 100);提示：

8、如果输入错误， 可以把原先输入的内容选中， 事本中，修改后直接 Ctrl+V 粘贴到 SQL Plus 中。5) 、插入记录：(1) 试着往 S 表中插入几条记录： INSERT INTO S(S#,SNAME,AGE,SEX) VALUES(S1, LI ,19, M);INSERT INTO S(S#,SNAME,AGE,SEX) VALUES( S2 , YANG ,19, F );INSERT INTO S(S#,SNAME,AGE,SEX) VALUES(S3, ZHANG ,18, F );如果插入的记录的主键已存在，则违反了唯一性约束： SQLINSERT INTO S(S#,S

9、NAME,AGE,SEX)VALUES(S2,ZHOU,19,F);INSERT INTO S(S#,SNAME,AGE,SEX)ERROR 位于第 1 行:ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C003007)SQL Plus 不支持连续插入记录，如输入： INSERT INTO S(S#,SNAME,AGE,SEX) VALUES(S5,SUN,19,F) VALUES(S6,QIAN,20,M); 则会报错，大家可以试试。仿照上面的格式，自己再插入三行。(2) 向 C 表中插入记录： 和上面一样，插入几条记录： INSERT INTO C(C#,CNAME,TEA

10、CHER) VALUES(C1, MATHS , WANG ); INSERT INTO C(C#,CNAME,TEACHER) VALUES( C2 , ENGLISH , ZHAO ); INSERT INTO C(C#,CNAME,TEACHER) VALUES( C3 , COMPUTER, DING );(3) 往 SC 表中插入记录： 先插入一条记录：INSERT INTO SC(S#,C#,GRADE)VALUES(S2, C3 ,90); 再插入：INSERT INTO SC(S#,C#,GRADE) VALUES(S99, C33,90);则会报错，由于在 S表中不存在 S#

11、为 S99的记录，或者说 C 表中不存在 C#为 C33 的记录，因此违反了完整性约束，如上图所示。6）、查询记录查找 C 表中所有的记录：SELECT * FROM C;查找 S 表中所有的女生的学号和姓名：SELECT S#,SNAME FROM SWHERE SEX=F;练习：查找 S表中所有年龄小于 20 岁的男生的学号。7）、修改记录把学号为 “S2的”学生的年龄改为 18。UPDATE SSET AGE=18WHERE S#= S2;练习：把 C2 课程的任课教师改名为 “ZHU”8）、删除记录把课程名为 “MATH”S的记录从 C 表中删除。 DELETE FROM CWHERE

12、 CNAME=MATHS ;现在试着做如下的删除： DELETE FROM C WHERE C#= C3; 会出现什么结果？想一想原因9）、撤消基本表SQL drop table C； drop table CERROR 位于第 1 行 :ORA-02449: 表中的唯一 /主键被外部关键字引用由于 C 表的主键被外部关键字（ SC 表的关键字）引用，所以不允许删除 C 表。 可采用如下方式在未删除 SC 表的情况下删除 C 表：drop table C cascade constraints也可以先删除 SC表，再删除 C 表。参考保存 SQL 语句1、使用 save 方法打开 SQL Pl

13、us，以 scott/tiger 登录。 随便输入一条语句，注意要是正确的语句，如 select * from S; 再输入：save d:1.sql; 可保存到指定路径。用文本编辑工具打开该文件，可以看到 SQL 语句 再插入一条记录，此时可以有两种选择：save d:1.sql append;save d:1.sql replace; 第一种是在原来的文件后面追加记录，第二种是覆盖原文件。 也可以直接在界面中操作，如图所示：推荐大家每写一条 SQL 语句，追加保存一次，下次要执行该脚本时，直接 输入：start d:1.sql ; 或者 d:1.sql;即可。2、使用 spool 语句刚进

14、入 SQL Plus 时输入：spool d:a.txt;随便写几个语句，然后输入：spool off;然后打开 d:a.txt，你会发现刚才写的语句和运行结果都保存在文件d:a.txt中。同学们可以在 system用户下输入下面命令， 解锁 scott 用户，再以 scott 用户登录： SQLalter user scott account unlock;/ 解锁 scott 用户SQLconn scott/oracle;/ 以 scott 用户登录四上机作业1 用 SQL Plus 连接数据库。2 用 Show 命令显示当前用户 （scott/oracle） 。3 练习 SQL命令：对

15、Oracle数据库基本表 EMP和 DEPT操作：1）检索 EMP 中所有的记录。2）列出工资在 1000到 2000之间的所有员工的 ENAME ，DEPTNO，SAL。3）显示 DEPT 表中的部门号和部门名称，并按部门名称排序。4）显示所有不同的工作类型。5）列出部门号在 10到 20之间的所有员工，并按名字的字母排序。6）列出部门号是 20，工作是职员的员工。7）显示名字中包含 TH 和 LL 的员工名字。8）显示所有员工的名字（ Ename）和报酬（ Remuneration）。9）显示在 1983 年中雇佣的员工。10）查询每个部门的平均工资。11）查询出每个部门中工资最高的职工。

16、12）查询出每个部门比平均工资高的职工人数。4.生成一个数据表 PROJECTS，其字段定义如下，其中 PROJID 是主键并且要求P_END_DATE 不能比 P_START_DATE 早。字段名称数据类型 长度PROJIDNUMBER 4P_DESCVARCHAR2 20P_START_DATEDATEP_END_DATEDATEBUDGET_AMOUNTNUMBER 7,2MAX_NO_STAFFNUMBER 25.生成一个数据表 ASSIGNMENTS ，其字段定义如下， 其中 PROJID 是外键引自 PROJECTS数据表，EMPNO 是数据表 EMP 的外键，并且要求 PROJI

17、D 和 EMPNO不能为 NULL 。字段名称数据类型长度PROJIDNUMBER4EMPNONUMBER4A_START_DATEDATEA_END_DATEDATEBILL_RATENUMBER4,2ASSIGN_TYPEVARCHAR226.用 DESCRIBE命令查看 4和 5题定义的字段。7.给 4 题中的 PROJECTS数据表增加一个 COMMENTS 字段，其类型为 LONG 给 5 题中的 ASSIGNMENTS 数据表增加一个 HOURS 字段，其类型为 NUMBER8.在 PROJECTS数据库表中增加下列记录：PROJID12P DESCWRITE C030 COURSEPROOF READ NOTESP START DATE02-JAN-8801-JAN-89P END DATE07-JAN-8810-JAN-89BUDGET AMOUNT500600MAX NO STAFF11COMMENTSBR CREATIVEYOUR CHOICE9.在 ASSIGNMENT