Oracle数据库期末复习知识点整理

基础知识表3.2 Oracle数据类型表3.3 XSB的表结构表3.5 CJB的表结构操作表创建表CREATE TABLE [schema.] table_name(column_name datatype [DEFAULT expression] [column_constraint][,…n]<table_constraint>[,…n])[PCTFREE integer][PCTUSED integer][INITRANS integer][MAXTRANS integer][TABLESPACE tablespace_name][STORGE storage_clause][CLUSTER cluster_name(cluster_column,…n)][ENABLE | DISABLE ][AS subquery]【例】使用CRETE TABLE命令为XSCJ数据库建立表XSB，表结构参照表3.3。

打开SQL*Plus工具，以system方案连接数据库，输入以下语句：CREATE TABLE XSB(XH char(6) NOT NULL PRIMARY KEY,XM char(8) NOT NULL,XB char(2) DEFAULT '1' NOT NULL,CSSJ date NOT NULL,ZY char(12) NULL,ZXF number(2) NULL,BZ varchar2(200) NULL);修改表ALTER TABLE [schema.] table_name[ ADD(column_name datatype [DEFAULT expression][column_constraint],…n) ]/*增加新列*/ [ MODIFY([ datatype ] [ DEFAULT expression ][column_constraint],…n) ] /*修改已有列的属性*/ [ STORAGE storage_clause ]*修改存储特征*/[ DROP drop_clause ]/*删除列或约束条件*/【例】使用ALTER TABLE语句修改XSCJ数据库中的表。

（1）在表XSB中增加两列：JXJ（奖学金等级）、DJSM（奖学金等级说明）。

ALTER TABLE XSBADD ( JXJ number(1),DJSM varchar2(40) DEFAULT '奖金1000元');（2）在表XSB中修改名为DJSM的列的默认值。

ALTER TABLE XSBMODIFY ( DJSM DEFAULT '奖金800元' );（3）在表XSB中删除名为JXJ和DJSM的列。

ALTER TABLE XSBDROP COLUMN JXJ;ALTER TABLE XSBDROP COLUMN DJSM;（4）修改KCB表的存储特征。

ALTER TABLE KCBPCTFREE 20 MAXTRANS 200;（5）为表XS_JSJ添加主键。

ALTER TABLE XS_JSJADD (CONSTRAINT "PK_JSJ" PRIMARY KEY(XH) );删除表DROP TABLE [schema.] table_name操作表数据插入记录INSERT INTO table_name[(column_list)]V ALUES(constant1,constant2,…)【例】向XSCJ数据库的表XSB中插入如下的一行：101101 王林计算机男19900210 50可以使用如下的PL/SQL语句：INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF)V ALUES('101101', '王林', '男',TO_DA TE('19900210','YYYYMMDD'), '计算机', 50);删除记录DELETE FROM table_name∣view_name[WHERE condition]【例】将XSCJ数据库的XSB表中总学分小于50的行删除，使用如下的PL/SQL语句。

DELETE FROM XSBWHERE ZXF<50;修改记录UPDATE table_name∣view_nameSET column_name=expression,[…n][WHERE condition]【例】将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为“101241”。

UPDATE XSSET ZY='通信工程',BZ='转专业学习',XH='101241'WHERE XM= '罗林琳';查询基本语法SELECT select_list /*指定要选择的列及其限定*/FROM table_source /*FROM子句，指定表或视图*/[ WHERE search_condition ] /*WHERE子句，指定查询条件*/[ GROUP BY group_by_expression ] /*GROUP BY子句，指定分组表达式*/[ HA VING search_condition ] /*HA VING子句，指定分组统计条件*/[ ORDER BY order_expression [ ASC | DESC ]] /*ORDER子句，指定排序表达式顺序*/选择指定列SELECT column_name [ , column_name [,…n] ]FROM table_name[WHERE search_condition]【例】查询XSB表中ZXF大于50同学的XH、XM和ZXF。

SELECT XH, XM, ZXFFROM XSBWHERE ZXF>50;修改查询结果中的列标题【例】查询XSB表中计算机系同学的XH、XM和ZXF，结果中各列的标题分别指定为学号、姓名和总学分。

SELECT XH AS 学号,XM AS 姓名,ZXF AS 总学分FROM XSBWHERE ZY= '计算机';模式匹配string_expression [ NOT ] LIKE string_expression [ ESCAPE escape_character ]【例】查询CP表中产品名含有“冰箱”的产品情况。

SELECT *FROM CPWHERE CPMCLIKE '%冰箱%';【例】查询XSB表中姓“王”且单名的学生情况。

SELECT *FROM XSBWHERE XM LIKE '王_';范围比较expression [ NOT ] BETWEEN expression1 AND expression2【例】①查询CP表中价格在2000元与4000元之间的产品情况。

SELECT *FROM CPWHERE JG BETWEEN 2000 AND 4000;执行结果如右图所示。

②查询XSB表中不在1989年出生的学生情况。

SELECT *FROM XSBWHERE CSSJ NOT BETWEEN TO_DA TE('19890101', 'YYYYMMDD')AND TO_DA TE('19891231', 'YYYYMMDD');使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回TRUE，否则返回FALSE。

使用IN关键字指定值表的格式为：expression IN ( expression [,…n])【例】查询CP表中库存量为“200”“300”和“500”的情况。

SELECT *FROM CPWHERE KCL IN (200,300,500);空值比较【例】查询XSCJ数据库中总学分尚不定的学生情况。

SELECT *FROM XSBWHERE ZXF IS NULL;子查询IN子查询。

IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：( subquery )【例】查找未选修离散数学的学生的情况。

SELECT XH, XM, ZY, ZXFFROM XSBWHERE XH NOT IN( SELECT XHFROM CJBWHERE KCH IN( SELECT KCHFROM KCBWHERE KCM = '离散数学'));比较子查询。

} { ALL | SOME | ANY } ( subquery )【例】查找比所有计算机系学生年龄都大的学生。

SELECT *FROM XSBWHERE CSSJ <ALL( SELECT CSSJFROM XSBWHERE ZY= '计算机');EXISTS子查询。

EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。

EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。

格式为：[ NOT ] EXISTS ( subquery )【例】查找选修了全部课程的同学姓名。

SELECT XMFROM XSBWHERE NOT EXISTS( SELECT *FROM KCBWHERE NOT EXISTS( SELECT *FROM CJBWHERE XH=XSB.XH AND KCH=KCB.KCH));查询对象【例】查找与101102号同学所选修课程一致的同学的学号。

本例即要查找这样的学号y，对所有的课程号x，若101102号同学选修了该课，那么y也选修了该课。

SELECT DISTINCT XHFROM CJB CJ1WHERE NOT EXISTS( SELECT *FROM CJB CJ2WHERE CJ2.XH ='101102' AND NOT EXISTS( SELECT *FROM CJB CJ3WHERE CJ3.XH= CJ1.XHAND CJ3.KCH = CJ2. KCH));【例】在XSB表中查找1990年1月1日以前出生的学生的姓名和专业。

SELECT XM, ZYFROM (SELECT * FROM XSBWHERE CSSJ<TO_DA TE('19900101', 'YYYYMMDD'));自然连接【例】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。