数据库原理及实验指导书922Word下载.docx
《数据库原理及实验指导书922Word下载.docx》由会员分享,可在线阅读,更多相关《数据库原理及实验指导书922Word下载.docx(40页珍藏版)》请在冰点文库上搜索。
![数据库原理及实验指导书922Word下载.docx](https://file1.bingdoc.com/fileroot1/2023-5/1/cb686669-2527-4284-8a80-4eb7242ee4c0/cb686669-2527-4284-8a80-4eb7242ee4c01.gif)
3.了解并掌握Oracle中的用Alter命令和Drop命令对表的修改和删除。
二、实验类型
验证性
三.预备知识
DDL是SQL命令的子集,用来创建、修改、删除Oracle数据库结构。
这些命令能立即影响数据库和数据字典字段信息。
1.表名命名规则
所用的表名必须满足下面的条件:
a)名字必须以A-Z或a-z的字母开始;
b)名字可以包括字母、数字和特殊字母(_)。
字符$和#也是合法的,但是这种用法不提倡;
c)名字大小写是一样的;
例如EMP、emp和eMp是表示同一个表;
d)名字最长不超过30个字符;
e)表名不能和其它的对象重名;
f)表名不能是SQL保留字。
表2-1表名命名举例
名字
合法
EMP85
YES
85EMP
NO(开始不是字母)
FIXED_ASSETS
FIXEDASSETS
NO(包含空格)
UPDATE
NO(SQL保留字)
2.字段类型
表2-2字段类型
数据类型
描述
VARCHAR2(w)
变长字符长度为w。
最长为2000个字符。
CHAR(w)
定长字符长度为w。
默认为1个字符;
最长为255个字符
NUMBER
38位有效数字的浮点数
NUMBER(w)
W位精确度的整数,范围从1至38
NUMBER(w,s)
W是精度,或总的数字书,范围从1至38。
S是比例,或是小数点右边的数字位。
比例的范围从-84至127
DATE
日期值,范围从公元前14712年1月到公元314712年12月
LONG
变长字符串,其最大长度为2G(或231-1个字节)
RAW和LONGRAW
面向字节数据,可存储字符串、浮点数,二进制数据等
3.Create命令
1)、CREATETABLEtable_name(column_nametype(size),column_nametype(size),…);
例如:
CREATETABLEDEPT
(DEPTNONUMBER
(2),
DNAMEVARCHAR2(12),
LOCVARCHAR2(12));
2)、CREATETABLEtable_name[(column_name,…)]ASSELECTstatement;
CREATETABLEDEPTNO10(NAME,LOCATION)
ASSELECTDNAME,LOCFROMDEPT
WHEREDEPTNO=10;
4.完整性约束
Oracle允许用户为表和列定义完整性约束来增强一定的规则。
可分为:
表约束和字段约束
四.上机内容
1.创建表EMP
CREATETABLEEMP
(EMPNONUMBER(4)NOTNULL,
ENAMEVARCHAR2(10),
JOBVARCHAR2(10),
MGRNUMBER(4),
HIREDATEDATE,
SALNUMBER(7,2),
COMMNUMBER(7,2),
DEPTNONUMBER
(2)NOTNULL);
Tablecreated.
用SQL*PLUS命令DESCRIBE来看生成的EMP表的列明细清单:
输入命令:
DESCRIBEEMP
EMP生成的数据表结构显示如下:
Name
Null?
Type
EMPNO
NOTNULL
NUMBER(4)
ENAME
VARCHAR2(10)
JOB
MGR
HIREDATE
SAL
NUMBER(7,2)
COMM
DEPTNO
NUMBER
(2)
2.从其他表中抽取字段生成数据表
CREATETABLEEMP_PARTAS
SELECTEMPNO,ENAME,JOB,SAL,COMMFROMEMP;
TableCreated.
DESCRIBEEMP_PART
EMP_Part生成的数据表结构结果显示如下:
3.DROP命令删除数据表
DROPTABLEEMP_PART;
Tabledropped.
4.给数据表EMP增加一个字段SPOUSES_NAME
ALTERTABLEEMP
ADD(SPOUSES_NAMECHAR(10));
SPOUSES_NAME
CHAR(10)
5.用ALTER的MODIFY命令修改已存在的字段的定义
MODIFY(ENAMEVARCHAR2(12));
TableAltered.
VARCHAR2(12)
6.用ALTER的DROP命令删除数据表中已存在的约束
ALTERTABLEEMPDROPPRIMARYKEY;
TableAltered.
7.创建表CUSTOMER
createtablecustomer(
last_namevarchar2(30)notnull,
state_cdvarchar
(2),
salesnumber);
8.创建表STATE
createtablestate(
state_cdvarchar
(2)notnull,
sate_namevarchar2(30));
五.上机作业
1.创建如下三个基表:
S(S#,SNAME,AGE,SEX)对应的中文为:
[学生(学号,姓名,年龄,性别)]
SC(S#,C#,GRADE)对应的中文为:
[学习(学号,课程号,成绩)]
C(C#,CNAME,TEACHER)对应的中文为:
[课程(课程号,课程名,任课教师)]
注:
本书以后要用到这三个基本表。
2.生成一个数据表PROJECTS,其字段定义如下,其中PROJID是主键并且要求P_END_DATE不能比P_START_DATE早。
字段名称
长度
PROJID
4
P_DESC
VARCHAR2
20
P_START_DATE
P_END_DATE
BUDGET_AMOUNT
7,2
MAX_NO_STAFF
2
3.生成一个数据表ASSIGNMENTS,其字段定义如下,其中PROJID是外键引自PROJECTS数据表,EMPNO是数据表EMP的外键,并且要求PROJID和EMPNO不能为NULL。
A_START_DATE
A_END_DATE
BILL_RATE
4,2
ASSIGN_TYPE
4.用DESCRIBE命令查看1和2题定义的字段。
5.给1题中的PROJECTS数据表增加一个COMMENTS字段,其类型为LONG。
给2题中的ASSIGNMENTS数据表增加一个HOURS字段,其类型为NUMBER。
实验二数据操纵
1.在数据表中用Insert增加记录。
2.用Update修改数据表中的数据。
3.用Delete删除表中的数据。
4.掌握视图(VIEW)在Oracle的应用。
1.Insert命令
1)用来在数据表中增加记录,格式如下:
INSERTINTOtablename[(column,column,….)]
VALUES(value,value,….);
命令中[(column,column,….)]是可选的。
一般情况下,为了编程的方便,最好指定字段列表。
该命令每次只能增加一条记录。
注意,CHARACTER和DATE必须用单引号括起来。
INSERTINTODEPT(DEPTNO,DNAME,LOC)
VALUES(50,’市场部’,’上海’);
在DEPT中增加一个新部门,忽略部门名称,这时字段列表必须指定否则出错,可用如下命令:
INSERTINTODEPT(DEPTNO,LOC)
VALUES(50,’上海’);
另外,如果部门名称不能确定,可用NULL代替,如下:
VALUES(50,NULL,’上海’);
增加DATE类型的数值,常用格式是DD-MON-YY。
默认的世纪是20世纪,ORCALE已克服了Y2K(千年虫)问题。
如下:
INSERTINTOEMP(EMPNO,NAM,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7568,’MASON’,
’ANALYST’,7566,
TO_DATE(‘24/06/20849:
30’,’DD/MM/YYYYHH:
MI’),
3000,NULL,20);
2)增加从其他数据表查询出的数据
命令格式:
INSERTINTOtable[(column,column,….)]
SELECTselect-list
FROMtable(s);
2.Update命令
在需要修改表中数据时,可使用update命令如下:
UPDATEtable[alias]
SETcolumn[,column….]={expression,subquery}
[WHEREcondition];
命令由三部分组成:
●update后跟一个或多个要修改的表,这部分是必不可少的。
●set后跟一个或多个要修改的表列,这也是必不可少的。
●where后跟查询条件,这是选项;
如果WHERE子句忽略,UPDATE命令将修改数据表中所有记录。
修改EMP表中SCOTT的记录数据,把他调到销售部,并且工资提高10%,具体命令如下:
UPDATEEMP
SETJOB=’SALESMAN’,
HIREDATE=SYSDATE,
SAL=SAL*1.1
WHEREENAME=‘SCOTT’;
1recordupdated.
3.Delete命令
Delete命令用来从表中删除一行或多行记录。
命令格式如下:
DELETEFROMtable[WHEREcondition];
该命令由两部分组成:
●关键字deletefrom后跟准备要从中删除数据的表名,这是必不可少的。
●关键字where后跟删除条件,是可选项;
如果不用WHERE子句,数据表中的所有记录将被删除。
例如删除EMP表中部门号是10的记录:
DELETEFROMEMPWHEREDEPTNO=10;
4.视图(VIEW)
视图是一个“窗口”,通过它可以看或修改数据库表中的数据。
视图来源于表或其他视图。
视图只存为SELECT语句。
它只是一个虚表而不是在物理存储器上的真正存在的数据表。
视图没有自己的数据,它的数据来自基表。
视图的优点:
⏹限制对数据库的访问。
⏹允许用户对复杂的查询进行简单的查询。
⏹对特别的用户和应用程序提供数据独立性。
视图对从多个数据库表中检索数据是透明的。
视图也允许不同的用户对同一数据表有不同的数据窗口。
生成视图的命令格式:
CREATE[ORREPLACE][FORCE]VIEWview-name
[(column1,column2,……)]
ASSELECTstatement
[WITHCHECKOPTION[CONSTRAINTconstraint_name]];
1.用Insert在基本表customer中插入数据
SQL>
insertintocustomervalues(‘Nicholson’,’CA’,6989.99);
1rowcreated.
insertintocustomervalues(‘Martin’,’CA’,2345.45);
insertintocustomervalues(‘Laursen’,’CA’,34.34);
insertintocustomervalues(‘Bambi’,’CA’,1234.55);
insertintocustomervalues(‘McGraw’,’NJ’,123.45);
2.在表STATE中插入指定的字段
insertintostate(state_name,state_cd)
2values(‘Massachusetttes’,’MA’);
2values(‘California’,’CA’);
2values(‘NewJersey’,’NJ’);
1created.
2values(‘NewYork’,’NY’);
3.修改数据
把state表中NewYork改为Florida,NY改为FD:
UPDATEstateSETstate_name=‘Florida’,state_cd=‘FD’
wherestate_name=‘NewYork’andstate_cd=‘NY’;
4.删除数据
从STATE表删除state_name为Florida和state_cd为FD的记录:
DELETEFROMSTATEWHEREstate_name=‘Florida’ANDstate_cd=‘FD’;
5.创建视图
生成一个部门号是10的视图:
CREATEVIEWD10EMP
AS
SELECTEMPNO,ENAME,SAL
FROMEMP
WHEREDEPTNO=10;
Viewcreated。
6.视图应用
从视图D10EMP中查询出全部信息:
SELECT*FROMD10EMP
ORDERBYENAME;
7.删除视图
DROPVIEWD10EMP;
1.用INSERT命令输入数据
表3-1基本表S的数据
S1
WANG
M
S2
LIU
19
S3
CHEN
22
S4
WU
S5
LOU
21
F
S8
DONG
18
表3-2基表C的数据
C2
MATHS
MA
C4
PHYSICS
SHI
C3
CHEMISTRY
ZHOU
C1
DB
LI
C5
OS
WEN
表3-3基本表SC的数据(空格为未选修)
C#S#
80
85
90
75
70
NULL
60
95
65
2.对S、C、SC表进行操作:
1)、把C2课程的非空成绩提高10%。
2)、在SC表中删除课程名为PHYSICS的成绩的元组。
3)、在S和SC表中删除学号为S8的所有数据。
3.在PROJECTS数据库表中增加下列记录:
1
WRITEC030COURSE
PROOFREADNOTES
02-JAN-88
01-JAN-89
07-JAN-88
10-JAN-89
500
600
COMMENTS
BRCREATIVE
YOURCHOICE
4.在ASSIGNMENTS数据库表中增加下列记录:
7369
7902
7844
01-JAN-88
04-JAN-88
03-JAN-88
50.00
55.00
45.50
WR
PF
HOURS
15
30
5.把ASSIGMENTS表中ASSIGNMENTTYPE的WR改为WT,其他的值不变。
6.在PROJECTS和ASSIGNMENTS插入更多的记录。
7.删除自己随意插入的记录。
实验三数据查询
1.掌握Select语句的运用。
2.掌握一些函数的应用。
3.掌握子查询的运用。
4.连接和分组的应用。
1.Select语句
Select命令用于从Oracle数据库中检索数据。
读者利用select命令告诉数据库要检索什么样的信息。
Select是读者看到的最常用的SQL语句,select命令(如下)有六个基本部分构成:
SELECT[DISTINCT]{*,COLUMN[ALIAS],……}
FROMtable
WHEREcondition(s)
ORDERBY{column,exper}[ASC|DESC]
GROUPBY{column,exper}
HAVINGhaving_condtions;
i.Select后跟用户需要检索的信息(如下一部分将要提到的表中表列的名字)。
这是select命令必不可少的部分。
ii.From后跟检索对象(如存放数据的一个或多个表的名称),from部分也是必不可少的。
iii.Where后跟检索条件(如限制检索内容的条件),where部分是可选的。
iv.Orderby后跟分类准则(如取自空值数据如何给出的第一部分的表列名称表),orderby部分是可选的。
v.Groupby后跟分组的字段或准则。
vi.Having后跟分组的查询条件。
2.选择指定的列
我们可以在select关键字后跟一个或多个表列。
星号指示Oracle显示表中的所有字段。
我们使用同样的select语句,但指定了需要查看的一个表的某些字段:
selecttable_namefromuser_tables
TABLE_NAME
CUSTOMER
STATE
2rowsselected
3.条件查询
到目前为止,我们看到了select命令可用来查看表中所有表列(select*)或部分表列(selectcolumn1,column3)。
如果读者只想看特定的数据行,怎么办呢?
这就需要用wherer子句来解决了。
我们想要查看state_cd值为MA的所有客户,可以用命令:
selectlast_name,state_cd,salesfromcustomerwherestate_cd=‘MA’;
结果如下:
LAST_NAMESTSALES
TeplowMA23445.67
1.显示EMP表中所有的部门号、职工名称和管理者号码:
SELECTDEPTNO,ENAME,MGR
FROMEMP;
----------------
-----------------
--------------
SMI