还剩19页未读，继续阅读
本资源只提供10页预览，全部文档请下载后查看！喜欢就下载吧，查找使用更方便
文本内容:
基础知识表数据类型
3.2Oracle数据类型描述格式固定长度字符域，规定字符的最大长度，sizeCHAR[size[BYTE|最大可为个字节和关键字char2000BYTE CHARCHAR]]表达长度单位是字节还是字符，默认为BYTE多字节字符集的固定长度字符域，长度随字符nchar NCHAR[size]集而定，最多为个字符或个字节20002000VARCHAR2size[BYTE|可变长度字符域，最大长度可达个字符varchar24000CHAR]多字节字符集的可变长度字符域，长度随字符nvarchar2NVARCHAR2[size]集而定，最多为个字符或个字节40004000用于存储所有日期的固定长度（个字节）字7符域，时间作为日期的一部分存储其中除非date DATE通过设置NLS_DATE_FORMAT参数来取代日期格式，否则查询时，日期以格DD-MON-RR式表达，如表达年月日13-APR-991999413用亚秒的粒度存储一种日期和时间precisiontimestamp TIMESTAMP[precision]是亚秒粒度的位数，默认为6,范围为0-9TIMESTAMP[precision]通过此外存储种时区偏差来扩展数timestamptimestamp WITH据类型，这个时区偏差定义当地时区与UTC之TIME ZONE间的差值通过此外存储一种时区偏差来扩展timestampTIMESTAMP[precision]数据类型，该类型不存储时区偏差，但存储时间timestamp WITH作为数据库时区的原则形式，时间信息将从当LOCAL TIMEZONE地时区转换到数据库时区interval yearto INTERVALYEAR用年和月的形式存储一段时间，指定precisionmonth[precision]TO MONTH的是年的位数，默认为2用天、时、分和秒的形式存储一段时间，INTERVAL DAYintervalday toprecision是这个时间的天部分所规定的最大位[precision]TOsecond数，默认为2s_precision是这个时间的秒部分所SECOND[s_precision]规定的小数点右边的位数，默认为6可变长度数值列，容许值为、正数和负数0number NUMBER[p[,s]]p指总位数，默认为38,s指小数点右边的位数，默认为0float FLOAT[p]浮点型数值列，p表达数值位数long LONG可变长度字符域，最大长度可到2GB表达二进制数据的可变长度字符域，为最sizeraw RAWsize大长度，最长为个字节2000表达二进制数据的可变长度字符域，最长为long rawLONG RAW2GBblob BLOB二进制大对象，最大长度为4GBclob CLOB字符大对象，最大长度为4GB多字节字符集的数据类型，最大长度CLOBnclob NCLOB为4GB【例】求数据库中各专业的学生数XSCJ专业,学生数SELECT ZYAS COUNT*ASFROM XSBGROUP BY ZY;子句HAVING子句的语法格式为HAVING[HAVING search_condition]【例】其中，为查询条件，与子句的查询条件类似，不过不一样的search_condition WHERE是子句可以使用记录函数，而子句不可以HAVING WHERE查找通信工程专业平均成绩在分以上的学生的学号和平均成绩85学号,平均成绩SELECT XHAS AVGCJASFROM CJBWHERE XH INSELECT XHFROM XSB二，通信工程，WHERE ZYGROUPBY XHHAVING AVGCJ=85;子句ORDER BY子句的语法格式为ORDER BY[ORDER BY{order_by_expression[ASC|DESC]}n]【例】将计算机专业学生的“计算机基础”课程成绩按降序排列姓名，课程名，成绩SELECT XMAS KCMAS CJASFROM XSB,KCB,CJBWHERE XSB.XH=CJB.XH AND CJB.KCH=KCB.KCH廿计算机基础，廿计算机，AND KCMAND ZYORDER BY CJDESC;子句UNION使用UNION子句可以将两个或多种SELECT查询的成果合并成一种成果集，其语法格式为{query specification|query expression}UNION[A LL]query specification|query expression[UNION[ALL]query specification|query expression[…n]]【例】查找学号为和学号为两位同学的信息101101101210SELECT*FROM XSB廿WHERE XH101101,UNION ALLSELECT*FROM XSBWHEREXH=101210,;视图创立视图CREATE[OR REPLACE|[FORCE|NOFORCE]VIEW[schema.]view_name[column_name[,...n]]ASselect_statement[WITH CHECK OPTION[CONSTRAINT constraint_name]][WITH READONLY]【例】查找平均成绩在分以上的学生的学号和平均成绩80本例首先创立学生平均成绩视图包括学号在视图中列名为和平均成绩在XS_KC_AVG,num视图中列名为score_avgCREATE OR REPLACE VIEWXS_KC_AVGnum,score_avg ASSELECT XH,AVGCJFROM CJBGROUP BYXH;再对视图进行查询XS_KC_AVGSELECT*FROM XS_KC_AVGWHERE scoreavg=80;更新视图【例】在数据库中使用如下语句创立可更新视图XSCJ CS_XSloCREATE OR REPLACE VIEW CS_XS1ASSELECT*FROM XSB廿通信工程WHERE ZY插入数据、修改数据、删除数据与表相似删除视图DROP VIEW[schema.]view_name修改视图跟创立视图同样【例】修改视图的定义，包括学号、姓名、选修的课程号、课程名和成绩CS_KCCREATE OR REPLACE FORCEVIEWCS_KCASSELECT XS.XH,XS.XM,XS_KC.KCH,KC.KCM,CJFROM XS,XS_KC,KCWHERE XS.XH=XSJKC.XH ANDXS_KC.KCH=KC.KCH上通信工程，AND ZYMWITHCHECKOPTION;索引创立索引/*索引类型刃CREATE[UNIQUE IBITMAP]INDEX/*索弓名称*/[schema.]index_name ION[schema.]table_namecolumn_name|column_expression[ASC IDESC]/*指定与否创立对应的日志记录*/[LOGGING|NOLOGGING]/*生成记录信息*/[COMPUTE STATISTICS]/*对复合索引进行压缩*/[COMPAESS|NOCOMPRESS]/*索引所属表空间*/[TABLESPACE tablespace_name]/*指定与否对表进行排序*/[SORT|NOSORT][REVERSE]【例】根据表的姓名列和出生时间列创立复合索引XSBCREATE INDEXXSBJndON XSBXM,CSSJ维护索引ALTER INDEX[schema.]index_name[LOGGING|NOLOGGING][TABLESPACE tablespace_name][SORT|NOSORT][REVERSE][RENAME TOnew_index_name]【例】重命名索引kc_name_idxoALTER INDEXkcb_name_idxRENAME TOkcb.idx;删除索引DROP INDEX[schema.]index_name数据完整性详见表格创立域完整性CHECK实体完整性PRIMARY KEY/UNIQUE参照完整性REFERNCES顾客自定义函数创立函数语法格式/*函数名称*/CREATE[ORREPLACE]FUNCTION function_nameparameter_name1,model datatype1,/*参数定义部分*/parameter_name2,mode2datatype2,parameter_name3,mode3datatype3,...RETURN return_datatype{IS|AS}[申/*定义返回值类型*/明变量]BEGIN/*函数体部分*/function_body;[RETURN scalar_expression;]/*返回语句*/END[function_name];下面给出一种函数，阐明其种参数的合法性3CREATE ORREPLACE FUNCTIONexplain_parameterin_pmt IN char,out_pmt OUTchar,in_out_pmt IN OUT charRETURNcharASreturn_char char;BEGIN〈函数语句序列》RETURN return_char;END[explain_parameter];函数语句序列及其也许出现的状况如下in_pmt:=hello1;该语句是错误的，由于类型的参数只能作为形参来传递值，不能在函数体中赋值INreturn_char:=in_pmt;该语句语法对的由于类型参数自身就是用来传递值，而是作为返回值变量IN return_char通过类型参数赋值给IN in_pmt return_char out_pmt:=hello;o该语句对的由于作为类型参数，在函数体内被赋值是容许的ut_pmt OUTreturn_char-out_pmt;该语句不对的由于类型参数不能传递值OUTin_out_pmt:=’world;该语句对的由于参数可以在函数体中被赋值IN OUTreturn_char:=in_out_pmt;该语句对的，由于类型参数既能传递值，也可以复制INOUT【例】计算某门课程全体学生的平均成绩CREATE ORREPLACE FUNCTIONaverage cnumIN charRETURNnumberAS/*定义返回值变量*/avger number;BEGINSELECT AVGCJINTO avgerFROM CJBWHERE KCH=cnumGROUP BYKCH;RETURNavger;END;调用函数实参variable_name:=function_name[^#1,2,...]【例】用函数登记表中有多少男同学count_num XSBSETSERVEROUTPUT ON;DECLAREman_num number;BEGIN男；man_num:=count_numDBMS_OUTPUT.PUT_LINETO_CHARman_num;END;输出成果为14删除函数语法格式DROP FUNCTION[schema.]function_name存储过程创立存储过程/*定义过程名*/CREATE[ORREPLACE]PROCEDURE[schema.]procedure_name/*定义参数[parameter parameter_mode date_type[DEFAULT expr][...n]]5类型及属性刃{IS|AS[declare_section]/*变量申明部分*/BEGINsql_statement过程体*//*PL/SQLEND fprocedure_name][;]【例】计算某专业总学分不小于的人数，存储过程使用了一种输入参数和一种输出参数40CREATE ORREPLACE PROCEDUREcount gradezyINchar,person_num OUTnumber ASBEGINSELECTCOUNTXHINTO person_numFROM XSBWHERE ZY=zy ANDZXF40;END;调用存储过程[{EXEC|EXECUTE}]procedure_name[[parameter=]value|@variable][;]【例】调用例中的存储过程
7.1procSET SERVEROUTON;EXEC proc;输出成果hello world如下命令运行的成果与之相似BEGINproc;END;删除存储过程DROP PROCEDURE[schema.]procedure_name;触发器触发器DML/*指定触发器名称*/CREATE[ORREPLACE]TRIGGER[schema.]trigger_name{BEFORE IAFTER IINSTEAD OF/*定义触发器种类*/{DELETE|INSERT|UPDATE[OF column,...n]}[OR{DELETE|INSERT|UPDATE[OF column,...n]}]/*指定操作对象*/ON[schema.]{table_name I view_name}[FOR EACHROW[WHENcondition]]块*/sql_statement[...n]/^PL/SQL【例】创立一种表其中只有一列在表上创立一种触发器，每次插入操作时，将变tablel,a量的值设为”并显示str“TRIGGER ISWORKING创立表table1CREATE TABLE table1a number;创立触发器INSERT table!.insertCREATE ORREPLACE TRIGGERtable l_insertAFTER INSERTON table1DECLAREstr charlOO-TRIGGER ISWORKING1;BEGINDBMS_OUTPUT.PUT_LINEstr;END;向中插入一行数据tabletINSERT INTOtablet VALUESIO;输出成果TRIGGER ISWORKING替代触发器【例】在数据库中创立视图包括学生学号、专业、课程号、成绩该视图依XSCJ stu.view,赖于表和是不可更新视图可以在视图上创立触发器，当向视图中插XSB CJB,INSTEAD OF入数据时分别向表和插入数据，从而实现向视图插入数据的功能首先创立视图XSB CJBCREATEVIEW stu.viewASSELECT XSB.XH,ZY,KCH,CJFROM XSB,CJBWHERE XSB.XH=CJB.XH创立触发器INSTEAD OFCREATETRIGGER InsteadTrigINSTEADOF INSERTON stu_view FOREACH ROWDECLARExmchar8;xb char2;cssj date;BEGIN佚名；xm:=男;xb:=月cssj-101-1-90;INSERT INTOXSBXH,XM,XB,CSSJ,ZYVALUES:NEW.XH,xm,xb,cssj,:NEW.ZY;INSERT INTOCJB VALUES:NEW.XH,:NEW.KCH,:NEW.CJ;END;向视图插入一行数据INSERT INTOstu_view VALUESO91102；5十算机」107，85;查看数据与否插入SELECT*FROM stu_view WHEREXH=091102;执行成果如图所示查看与视图关联的表的状况XSBSELECT*FROM XSBWHEREXH=0911021;系统触发器从开始，提供的系统触发器可以在或数据库系统上被触发指的是Oracle8i OracleDDL DDL数据定义语句，如、和等而数据库系统事件包括数据库服务器的启CREATE ALTERDROP动或关闭数据库服务器出错等STARTUP SHUTDOWN,SERVERERROR语法格式CREATE ORREPLACE TRIGGER[scache.]trigger_name{BEFORE|AFTER}{ddl_event_list Idatabse_event_list}ON{DATABASE I[schema.]SCHEMA[when_clause]tigger_body【例】创立一种顾客事件触发器，记录顾客所删除的所有对象首先以顾客身份连接SYSTEM SYSTEM数据库，创立一种存储顾客信息的表CREATE TABLEdropped_objects object__name varchar230,object_type varchar20,dropped_date date；创立触发器，在顾客删除对象之前记录到信息表中BEFORE DROP dropped_objectsCREATE ORREPLACE TRIGGERdropped_obj_triggerBEFORE DROPON SYSTEM.SCHEMABEGININSERT INTOdropped_objectsVALUESora_dict_obj_name,ora_dict_obj_type,SYSDATE;END;目前删除SYSTEM模式下的某些对象，并查询表dropped_objects DROP TABLEtable1;DROP TABLEtable2;SELECT*FROM dropped_objects;触发器删除册除触发器使用语句，语法格式IJ DROPTRIGGER DROPTRIGGER[schema.]trigger_name启用和禁用触发器ALTER TRIGGER[schema.]trigger_name DISABLE|ENABLE;其中，表达禁用触发器，表达启用触发器例如要禁用触发器使用如下语句DISABLE ENABLEdel_xs,ALTER TRIGGERdel_xs DISABLE;假如要启用或禁用一种表中的所有触发器，还可以使用如下的语法ALTER TABLEtable_name{DISABLE|ENABLE}ALL TRIGGERS;bfile BFILE外部一进制文献，大小由操作系统决定表达的二进制数据，的RowID Oracle8RowIDrowid ROWID数值为个字书10用于数据寻址的二进制数据，规定最大长sizeuro wid UROWID[size]度，默认为个字节4000表达浮点类型，比效率更高，位binary_float BINARY_FLOAT number32binary_double BINARY_DOUBLE表达双精度数字类型，64位表的表构造
3.3XSB数据类型是否可空默认值说明列名含义列名主键，前位年级，中间2无学号XH char6X位班级号，后位序号22无姓名XM char8X“男”性别XB char2X无出生时间CSSJ dateXq无专业ZY char12总学分＜总学分ZXF number2003160无备注BZ varchar2200表的表构造
3.4KCB数据类型是否可空默认值说明列名含义列名无主键课程号KCH char3X无课程名KCM char16XKKXQ numberlq1只能为1〜8开课学期XS number2q0课时学分XF numberlX0表的表构造
3.5CJB数据类型是否可空默认值说明列名含义列名无主键学号XH char6X无主键课程号KCH char3X无成绩CJ number2操作表创立表CREATE TABLE[schema.]table_namecolumn_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_namecluster_column,...n][ENABLE|DISABLE][AS subquery]【例】使用命令为数据库建立表表构造参照表CRETE TABLEXSCJ XSB,
3.3打开工具，以方案连接数据库，输入如下语句:SQL*Plus systemXHchar6NOT NULLPRIMARY KEY,XM char8NOT NULL,XB char2DEFAULT4*NOT NULL,NOT NULL,CSSJ dateNULL,ZY char12CREATE TABLE XSBZXF number2NULL,BZ varchar2200NULL）；修改表ALTER TABLE[schema.]table_name[ADDcolumn_name datatype[DEFAULT expression][column_constraint]...n]9/*增长新列*/[MODIFY[datatype][DEFAULT expression]/*修改已经有列的[column_constraint],...n]属性*/*修[STORAGE storage_clause]改存储特性*/[DROPdrop_clause]/*删除列或约束条件刃【例】使用语句修改数据库中的表ALTER TABLEXSCJ在表中增长两列奖学金等级、奖学金等级阐明1XSB JXJDJSMALTER TABLEXSBADD JXJnumberl,奖金元；DJSM varchar240DEFAULT1000在表中修更名为的列的默认值2XSB DJSMALTER TABLEXSB奖金元；MODIFYDJSM DEFAULT800在表中删除名为和的列3XSB JXJDJSMALTER TABLEXSBDROP COLUMNJXJ;ALTERTABLEXSBDROP COLUMNDJSM;修改表的存储特性4KCBALTER TABLEKCBPCTFREE20MAXTRANS200;为表添加主键5XS_JSJALTER TABLEXS_JSJADD CONSTRAINT,,PK_JSJn PRIMARYKEYXH;删除表DROPTABLE[schema.]table_name操作表数据插入记录INSERT INTOtable_name[column_list]VALUESconstant1,constant2,...【例】向数据库的表中插入如下的一行XSCJ XSB王林计算机男1011011990021050可以使用如下的语句PL/SQLINSERT INTOXSBXH,XM,XB,CSSJ,ZY,ZXFVALUESClOllOr,•I#；^^TO.DATECl9900210,；YYYYMMDD,,50;删除记录DELETE FROM table name Iviewname[WHERE condition]【例】将数据库的表中总学分不不小于的行删除，使用如下的语句XSCJ XSB50PL/SQLDELETE FROM XSBWHERE ZXF50;修改记录UPDATE table_nameIview_nameSET column_name=expression,[...n][WHERE condition]【例】将姓名为“罗林琳”的同学的专业改为“通信工程备注改为“转专业学习”，学号L改为力01241”UPDATE XSSETZY=，通信工程;BZ=，转专业学习；XH=1101241,罗林琳；WHERE XM=查询SELECT selectlist/*指定要选择的列及其限定*/基本语法子句，指定表或视图*/FROM table_source/*FROM[WHERE search_condition]/*WHERE子句，指定查询条件*/[GROUPBYgroup_by_expression]/*GROUPBY子句，指定分组体现式*/[HAVING search_condition]/HAVING子句，指定分组记录条件*/子句，指定排序体现式次[ORDERBYorder_expression[ASC|DESC]]BORDER序*/选择指定列SELECT column_name[,column_name]FROMtable_name[WHERE search_condition]【例】查询表中不小于同学的、和XSB ZXF50XH XM ZXFSELECT XH,XM,ZXFFROM XSBWHEREZXF50;修改查询成果中的列标题【例】查询表中计算机系同学的、和成果中各列的标题分别指定为学号、XSB XHXMZXF,姓名和总学分学号,姓名,总学分SELECT XHAS XMAS ZXFASFROM XSB廿计算机；WHEREZY模式匹配【例】查询表string_expression[NOT]LIKE string_expression[ESCAPE escape_character]CP中产品名具有“冰箱”的产品状况SELECT*FROM CPWHERECPMC冰箱％;LIKE%【例】查询表中姓“王”且单名的学生状况XSBSELECT*FROMXSB王二WHERE XMLIKE范围比较expression[NOT]BETWEEN expression!AND expression2【例】
①查询表中价格在元与元之间的产品状况CP4000SELECT*FROM CPWHEREJG BETWEENAND4000;执行成果如右图所示
②查询表中不在年出生的学生状况XSB1989SELECT*FROM XSBWHERECSSJ NOTBETWEEN TO.DATEf19890101；TYYYMMDDAND TO_DATE1989123YYYYMMDD;使用IN关键字可以指定一种值表,值表中列出所有也许的值，当体现式与值表中的任意一种匹配时，即返回否则返回使用关键字指定值表的格式为TRUE,FALSE INexpression INexpression【例】查询表中库存量为”和”的状况CP“200”“300“500SELECT*FROM CPWHEREKCL IN200,300,500;空值比较【例】查询数据库中总学分尚不定的学生状况XSCJSELECT*FROM XSBWHEREZXF ISNULL;子查询IN子查询IN子查询用于进行一种给定值与否在子查询成果集中的判断，格式为:expression[NOT]INsubquery【例】查找未选修离散数学的学生的状况SELECTXH,XM,ZY,ZXFFROM XSBWHEREXH NOTINSELECT XHFROM CJBWHERE KCHINSELECT KCHFROMKCB廿离散数学WHERE KCM比较子查询【例】查找比所有计expression{|=|=||=|!=|}{ALL|SOME|ANY}subquery算机系学生年龄都大的学生SELECT*FROM XSBWHERECSSJ ALLSELECTCSSJ FROMXSB廿计算机，；WHERE ZYEXISTS子查询EXISTS谓词用于测试子查询的成果与否为空表，若子查询的成果集不为空，则返回否则返回还可与结合使用，即EXISTS TRUE,FALSE EXISTSNOT NOTEXISTS,其返回值与刚好相反格式为EXIST[NOT]EXISTSsubquery【例】查找选修了所有课程的同学姓名SELECT XMFROM XSBWHERE NOT EXISTSSELECT*FROM KCBWHERE NOTEXISTSSELECT*FROM CJBWHEREXH=XSB.XH ANDKCH=KCB.KCH；查询对象【例】查找与号同学所选修课程一致的同学的学号101102本例即要查找这样的学号对所有的课程号若号同学选修了该课，那么也选修了该y,x,101102y课SELECT DISTINCTXHFROM CJBCJ1WHERENOTEXISTSSELECT*FROMCJBCJ2WHERE CJ
2.XH=4011021AND NOTEXISTSSELECT*FROMCJBCJ3WHERE CJ
3.XH=CJ
1.XHAND CJ
3.KCH=CJ
2.KCH；【例】在表中查找年月日此前出生的学生的姓名和专业XSB199011SELECT XM,ZYFROM SELECT*FROMXSBWHERECSSJTO_DATE119900101\1YYYYMMDD1;自然连接【例】查找选修了“计算机基础”课程且成绩在分以上的学生学号、姓名、课程名及成绩80SELECT XSB.XH,XM,KCM,CJFROM XSB,KCB,CJBWHERE XSB.XH=CJB.XH ANDKCB.CH=CJB.KCH廿计算机基础，AND KCMAND CJ=80;关键字指定的连接JOIN内连接内连接按照所指定的连接条件合并两个表，返回满足条件的行ON【例】用的关键字体现下列查询查找选修了“计算机基础”课程且成绩在FROM JOIN80分以上的学生学号、姓名、课程名及成绩SELECT XSB.XH,XM,KCM,CJFROM XSBJOINCJB JOINKCB ONCJB.KCH=KCB.KCHON XSB.XH=CJB.XH廿计算机基础’WHERE KCMANDCJ=80;外连接外连接的成果表不仅包括满足连接条件的行，还包括对应表中的所有行外连接包括如下三种•左外连接成果表中除了包括满足连接条件的行外，还包括左表的所有LEFT OUTERJOIN行；•右外连接成果表中除了包括满足连接条件的行外，还包括右表的所有RIGHT OUTERJOIN行；•完全外连接成果表中除了包括满足连接条件的行外，还包括两个表的FULL OUTERJOIN所有行【例】查找被选修了的课程的选修状况和所有开设的课程名SELECT CJB.*,KCMFROM CJBRIGHT JOINKCB ONCJB.KCH=KCB.KCH;交叉连接交叉连接实际上是将两个表进行笛卡尔积运算，成果表是由第个表的每一行与第个表的每12一行拼接后形成的表，因此成果表的行数等于两个表行数之积【例】列出学生所有也许的选课状况SELECTXH,XM,KCH,KCMFROM XSBCROSS JOINKCB;记录函数和函数、和函数、函数SUM AVGMAX MINCOUNT【例】求选修了课程的学生总人数选修了课程的总人数SELECT COUNTDISTINCTXH ASFROMCJB;分组记录GROUPBY子句用于对表或视图中的数据按字段分组，语法格式为:GROUP BYGROUPBY[ALL]group byexpression[...n]9。
个人认证
优秀文档
获得点赞 0
