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

计1301班数据库复习资料1/18基础知识表3.2Oracle数据类型数据类型格式描述charCHAR[(size[BYTE|CHAR])]固定长度字符域，size规定字符的最大长度，最大可为2000个字节。BYTE和CHAR关键字表示长度单位是字节还是字符，默认为BYTEncharNCHAR[(size)]多字节字符集的固定长度字符域，长度随字符集而定，最多为2000个字符或2000个字节varchar2VARCHAR2(size[BYTE|CHAR])可变长度字符域，最大长度可达4000个字符nvarchar2NVARCHAR2[(size)]多字节字符集的可变长度字符域，长度随字符集而定，最多为4000个字符或4000个字节dateDATE用于存储全部日期的固定长度(7个字节)字符域，时间作为日期的一部分存储其中。除非通过设置NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以DD-MON-RR格式表示，如13-APR-99表示1999年4月13日timestampTIMESTAMP[(precision)]用亚秒的粒度存储一个日期和时间。precision是亚秒粒度的位数，默认为6，范围为0～9timestampTIMESTAMP[(precision)]WITHTIMEZONE通过另外存储一个时区偏差来扩展timestamp数据类型，这个时区偏差定义本地时区与UTC之间的差值timestampTIMESTAMP[(precision)]WITHLOCALTIMEZONE通过另外存储一个时区偏差来扩展timestamp数据类型，该类型不存储时区偏差，但存储时间作为数据库时区的标准形式，时间信息将从本地时区转换到数据库时区intervalyeartomonthINTERVALYEAR[(precision)]TOMONTH用年和月的形式存储一段时间，precision指定的是年的位数，默认为2intervaldaytosecondINTERVALDAY[(precision)]TOSECOND[(s_precision)]用天、时、分和秒的形式存储一段时间，precision是这个时间的天部分所要求的最大位数，默认为2。s_precision是这个时间的秒部分所要求的小数点右边的位数，默认为6numberNUMBER[(p[,s])]可变长度数值列，允许值为0、正数和负数。p指总位数，默认为38，s指小数点右边的位数，默认为0floatFLOAT[(p)]浮点型数值列，p表示数值位数longLONG可变长度字符域，最大长度可到2GBrawRAW(size)表示二进制数据的可变长度字符域，size为最大长度，最长为2000个字节longrawLONGRAW表示二进制数据的可变长度字符域，最长为2GBblobBLOB二进制大对象，最大长度为4GB计1301班数据库复习资料2/18clobCLOB字符大对象，最大长度为4GBnclobNCLOB多字节字符集的CLOB数据类型，最大长度为4GBbfileBFILE外部二进制文件，大小由操作系统决定rowidROWID表示RowID的二进制数据，Oracle8RowID的数值为10个字节urowidUROWID[(size)]用于数据寻址的二进制数据，size规定最大长度，默认为4000个字节binary_floatBINARY_FLOAT表示浮点类型，比number效率更高，32位binary_doubleBINARY_DOUBLE表示双精度数字类型，64位表3.3XSB的表结构列名数据类型是否可空默认值说明列名含义XHchar(6)×无主键，前2位年级，中间2位班级号，后2位序号学号XMchar(8)×无姓名XBchar(2)ד男”性别CSSJdate×无出生时间ZYchar(12)√无专业ZXFnumber(2)√00≤总学分160总学分BZvarchar2(200)√无备注表3.4KCB的表结构列名数据类型是否可空默认值说明列名含义KCHchar(3)×无主键课程号KCMchar(16)×无课程名KKXQnumber(1)√1只能为1～8开课学期XSnumber(2)√0学时XFnumber(1)×0学分表3.5CJB的表结构列名数据类型是否可空默认值说明列名含义XHchar(6)×无主键学号KCHchar(3)×无主键课程号CJnumber(2)√无成绩计1301班数据库复习资料3/18操作表创建表CREATETABLE[schema.]table_name(column_namedatatype[DEFAULTexpression][column_constraint][,…n]table_constraint[,…n])[PCTFREEinteger][PCTUSEDinteger][INITRANSinteger][MAXTRANSinteger][TABLESPACEtablespace_name][STORGEstorage_clause][CLUSTERcluster_name(cluster_column,…n)][ENABLE|DISABLE][ASsubquery]【例】使用CRETETABLE命令为XSCJ数据库建立表XSB，表结构参照表3.3。打开SQL*Plus工具，以system方案连接数据库，输入以下语句：CREATETABLEXSB(XHchar(6)NOTNULLPRIMARYKEY,XMchar(8)NOTNULL,XBchar(2)DEFAULT'1'NOTNULL,CSSJdateNOTNULL,ZYchar(12)NULL,ZXFnumber(2)NULL,BZvarchar2(200)NULL);修改表ALTERTABLE[schema.]table_name[ADD(column_namedatatype[DEFAULTexpression][column_constraint],…n)]/*增加新列*/[MODIFY([datatype][DEFAULTexpression][column_constraint],…n)]/*修改已有列的属性*/[STORAGEstorage_clause]*修改存储特征*/[DROPdrop_clause]/*删除列或约束条件*/【例】使用ALTERTABLE语句修改XSCJ数据库中的表。计1301班数据库复习资料4/18（1）在表XSB中增加两列：JXJ（奖学金等级）、DJSM（奖学金等级说明）。ALTERTABLEXSBADD(JXJnumber(1),DJSMvarchar2(40)DEFAULT'奖金1000元');（2）在表XSB中修改名为DJSM的列的默认值。ALTERTABLEXSBMODIFY(DJSMDEFAULT'奖金800元');（3）在表XSB中删除名为JXJ和DJSM的列。ALTERTABLEXSBDROPCOLUMNJXJ;ALTERTABLEXSBDROPCOLUMNDJSM;（4）修改KCB表的存储特征。ALTERTABLEKCBPCTFREE20MAXTRANS200;（5）为表XS_JSJ添加主键。ALTERTABLEXS_JSJADD(CONSTRAINTPK_JSJPRIMARYKEY(XH));删除表DROPTABLE[schema.]table_name操作表数据插入记录INSERTINTOtable_name[(column_list)]VALUES(constant1,constant2,…)【例】向XSCJ数据库的表XSB中插入如下的一行：101101王林计算机男1990021050可以使用如下的PL/SQL语句：INSERTINTOXSB(XH,XM,XB,CSSJ,ZY,ZXF)VALUES('101101','王林','男',TO_DATE('19900210','YYYYMMDD'),'计算机',50);删除记录DELETEFROMtable_name∣view_name[WHEREcondition]【例】将XSCJ数据库的XSB表中总学分小于50的行删除，使用如下的PL/SQL语句。计1301班数据库复习资料5/18DELETEFROMXSBWHEREZXF50;修改记录UPDATEtable_name∣view_nameSETcolumn_name=expression,[…n][WHEREcondition]【例】将姓名为“罗林琳”的同学的专业改为“通信工程”，备注改为“转专业学习”，学号改为“101241”。UPDATEXSSETZY='通信工程',BZ='转专业学习',XH='101241'WHEREXM='罗林琳';查询基本语法SELECTselect_list/*指定要选择的列及其限定*/FROMtable_source/*FROM子句，指定表或视图*/[WHEREsearch_condition]/*WHERE子句，指定查询条件*/[GROUPBYgroup_by_expression]/*GROUPBY子句，指定分组表达式*/[HAVINGsearch_condition]/*HAVING子句，指定分组统计条件*/[ORDERBYorder_expression[ASC|DESC]]/*ORDER子句，指定排序表达式顺序*/选择指定列SELECTcolumn_name[,column_name[,…n]]FROMtable_name[WHEREsearch_condition]【例】查询XSB表中ZXF大于50同学的XH、XM和ZXF。SELECTXH,XM,ZXFFROMXSBWHEREZXF50;修改查询结果中的列标题【例】查询XSB表中计算机系同学的XH、XM和ZXF，结果中各列的标题分别指定为学计1301班数据库复习资料6/18号、姓名和总学分。SELECTXHAS学号,XMAS姓名,ZXFAS总学分FROMXSBWHEREZY='计算机';模式匹配string_expression[NOT]LIKEstring_expression[ESCAPEescape_character]【例】查询CP表中产品名含有“冰箱”的产品情况。SELECT*FROMCPWHERECPMCLIKE'%冰箱%';【例】查询XSB表中姓“王”且单名的学生情况。SELECT*FROMXSBWHEREXMLIKE'王_';范围比较expression[NOT]BETWEENexpression1ANDexpression2【例】①查询CP表中价格在2000元与4000元之间的产品情况。SELECT*FROMCPWHEREJGBETWEEN2000AND4000;执行结果如右图所示。②查询XSB表中不在1989年出生的学生情况。SELECT*FROMXSBWHERECSSJNOTBETWEENTO_DATE('19890101','YYYYMMDD')ANDTO_DATE('19891231','YYYYMMDD');使用IN关键字可以指定一个值表，值表中列出所有可能的值，当表达式与值表中的任意一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expressionIN(expression[,…n])【例】查询CP表中库存量为“200”“300”和“500”的情况。SELECT*FROMCPWHEREKCLIN(200,300,500);空值比较【例】查询XSCJ数据库中总学分尚不定的学生情况。计1301班数据库复习资料7/18SELECT*FROMXSBWHEREZXFISNULL;子查询IN子查询。IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression[NOT]IN(subquery)【例】查找未选修离散数学的学生的情况。SELECTXH,XM,ZY,ZXFFROMXSBWHEREXHNOTIN(SELECTXHFROMCJB