oracle建表总结.doc

《oracle建表总结.doc》由会员分享，可在线阅读，更多相关《oracle建表总结.doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、oracle建表总结oracle建表总结Oracle建表总结(一)对象命名原则：必须由字母开始，长度在130个字符之间。名字中只能包含AZ,az,09,_(下划线)，$和#（但尽量不要使用$和#）。同一个Oracle服务器用户所拥有的对象名字不能重复。名字不能为Oracle的保留字。1、2、3、4、5、名字是大小写不敏感。(二)数据类型：（10种，主要varchar2,date,number3种）说明：（1）（2）date的使用尽量用到to_date(1990-01-01,YYYY-MM-DD)；blob一般用来存放声音，视频等to_date，如：(三)约束条件（6种）default设置默认值

2、说明：（1）列级约束：只能作用于一个列上；定义时跟在列后面（只有notnull,default纯列级约束，其它都即可表级约束，又可列级约束）；表级约束：可作用于多个列上，也可只约束一个列；要单独定义。（2）可以为其他4种约束起名，而不能给NOTNULL和DEFAULT约束起名；别名起法，如：constraintpk_tidprimarykey；（3）各种列级约束的写法（以学生表为例）；Createtablestudent(S_idvarchar(12)constraintpk_tidprimarykey,-起别名pk_tidS_namevarchar(12)notnull,S_sexchar(

3、2)default男check(s_sex=男ors_sex=女),-注意default应放在其它约束前面S_phonevarchar(11)unique,classidvarchar(12)referencesclassinfo(classid)-为表classinfo相对classid的外键（名称可不一致）);（4）表级约束简单举例（主要注意外键）Createtablestudent(S_idvarchar(12),classidvarchar(12)referencesclassinfo(classid),primarykey(s_id),-或取别名constraintpk_tidpri

4、marykey（s_id）-联合主键只能用表级约束如：primarykey（1,2）constraintstu_cla_fkforeignkey(classid)referencesclassinfo(classid)-不用别名foreignkey(classid)referencesclassinfo(classid)-两个外键，分开写，写法同上);(四)约束的操作增加约束altertable表名称addconstraint约束名称约束类型（字段）;删除约束altertabledeptdropunique(dname,loc)-指定约束的定义内容altertabledeptdropconst

5、raintdept_dname_loc_uk-指定约束名查询约束条件Select*fromuser_constraints;查询被约束的列Select*fromuser_cons_columns;(五)表的修改，删除，重命名表增加altertable表名add列名类型修改altertable表名modify列名【（新）类型（新）约束】删除altertable表名drop列名重命名renameold_nametonew_name;(六)创建序列可单独修改里面的改扩展阅读：oracle数据库创建表设计与修改(个人总结超有用)CREATETABLEcd_study_detail(row_idVARC

6、HAR2(20)UNIQUE,id_cardVARCHAR2(20)PRIMARYKEY,study_dateDATECHECK(study_dateLIKE%-%-%),study_descVARCHAR2(50),study_scoNUMBER(10,1),CONSTRAINTdtudy_role_fkFOREIGNKEY(id_card)REFERENCESneu_study(id_card);CREATETABLEneu_study(row_idVARCHAR2(20)UNIQUE,id_cardVARCHAR2(20)PRIMARYKEY,birth_dayDATECHECK(bir

7、th_dayLIKE%-%-%),role_idVARCHAR2(20),per_remarkVARCHAR2(255),base_salryNUMBER(10,4)CHECK(base_salryBETWEEN500AND2022),fact_salryNUMBER(10,4),create_dateDATEDEFAULTSYSDATECHECK(create_dateLIKE%-%-%),last_up_dateDATECHECK(last_up_dateLIKE%-%-%),CONSTRAINTdtudy_role_fkFOREIGNKEY(字段)REFERENCES表名(字段)/字段后

8、直接加references表名(字段名)创建外键更好)DROPTABLEneu_study;CREATETABLEROLE(cd_roleVARCHAR2(20)PRIMARYKEY,role_nameVARCHAR2(20)UNIQUE)SELECT*FROMneu_studySELECT*FROMROLEINSERTINTOROLEVALUES(&请输入部门编号,&请输入部门姓名);CREATESEQUENCEseq_studySTARTWITH1INCREMENTBY1;DROPSEQUENCEseq_study;INSERTINTOneu_studyVALUES(seq_study.N

9、EXTVAL,&请输入身份证号,to_date(&请输入生日,yyyy-mm-dd),&请输入部门编号,&备注,&请输入基本工资,&请输入当前实际工资,to_date(&请输入创建工资的日期,yyyy-mm-dd),to_date(&请输入最后更新工资的日期,yyyy-mm-dd);INSERTINTOneu_studyVALUES(seq_study.NEXTVAL,2100902930,to_date(1986-02-03,yyyy-mm-dd),1102,无,2022,600,to_date(2022/11/10,yyyy-mm-dd),to_date(2022/11/16,yyyy-m

10、m-dd);COMMIT;SELECT*FROMneu_study;/Oracle的基本操作Oracle字段类型（zz）在Oracle关于时间属性的建表Example:createtablecourses(cidvarchar(20)notnullprimarykey,cnamevarchar(20)notnull,ctypeinteger,ctimedateDEFAULTSYSDATE,cscorefloatnotnull)insertintocoursesvalues(ss01,java,0,TO_DATE(2022-8-28,yyyy-mm-dd),94)insertintocourse

11、svalues(ss02,music,1,TO_DATE(2022-8-27,yyyy-mm-dd),88)insertintocoursesvalues(ss03,c+,0,TO_DATE(2022-8-29,yyyy-mm-dd),78)在PL/SQL建表以后，记得commit，才能在pl/sql上查询到数据。对于日期类型将字符串转换成日期：InsertIntotable（col_date）Values(to_date(2022-01-16,yyyy-mm-dd);将日期转换成字符串：InsertIntotable（col_char）Values(to_char(Date,format);

12、select*fromcourseswherectimeto_date(2022-8-28,yyyy-mm-dd)创建表1.ORACLE常用的字段类型ORACLE常用的字段类型有VARCHAR2(size)可变长度的字符串,必须规定长度CHAR(size)固定长度的字符串,不规定长度默认值为NUMBER(p,s)数字型p是位数总长度,s是小数的长度,可存负数最长38位.不够位时会四舍五入.DATE日期和时间类型LOB超长字符,最大可达4GCLOB超长文本字符串BLOB超长二进制字符串BFILE超长二进制字符串,保存在数据库外的文件里是只读的.数字字段类型位数及其四舍五入的结果原始数值12345

13、67.89数字字段类型位数存储的值Number1234567.89Number(8)12345678Number(6)错Number(9,1)1234567.9Number(9,3)错Number(7,2)错Number(5,-2)1234600Number(5,-4)1230000Number(*,1)1234567.92.创建表时给字段加默认值和约束条件创建表时可以给字段加上默认值例如:日期字段DEFAULTSYSDATE这样每次插入和修改时,不用程序操作这个字段都能得到动作的时间创建表时可以给字段加上约束条件例如:非空NOTNULL不允许重复UNIQUE关键字PRIMARYKEY按条件检

14、查CHECK(条件)外键REFERENCES表名(字段名)3.创建表的例子CREATETABLEDEPT(EPTNONUMBER(2)CONSTRAINTPK_DEPTPRIMARYKEY,DNAMEVARCHAR2(14),LOCVARCHAR2(13);CREATETABLEregion(IDnumber(2)NOTNULLPRIMARYKEY,postcodenumber(6)default0NOTNULL,areanamevarchar2(30)defaultNOTNULL);4.创建表时的命名规则和注意事项1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,

15、_,$,#2)大小写不区分3)不用SQL里的保留字,一定要用时可用双引号把字符串括起来4)用和实体或属性相关的英文符号长度有一定的限制注意事项:1)建表时可以用中文的字段名,但最好还是用英文的字段名2)创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引4)一个表的最多字段个数也是有限制的，254个.5.约束名的命名规则和语法约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)约束名字符串的命名规则同于表和字段名的命名规则6.使用约束时的注意事项约束里不能用系统函数,如SYSDATE和别

16、的表的字段比较可以用本表内字段的比较想在事务处理后,做约束的检查SQLaltersessionsetconstraintsdeferred.7.由实体关系图到创建表的例子s_dept前提条件:已有region表且含唯一关键字的字段idSQLCREATETABLEs_dept(idNUMBER(7)CONSTRAINTs_dept_id_pkPRIMARYKEY,nameVARCHAR2(25)CONSTRAINTs_dept_name_nnNOTNULL,region_idNUMBER(7)CONSTRAINTs_dept_region_id_fkREFERENCESregion(id),CO

17、NSTRAINTs_dept_name_region_id_ukUNIQUE(name,region_id);8.较复杂的创建表例子SQLCREATETABLEs_emp(idNUMBER(7)CONSTRAINTs_emp_id_pkPRIMARYKEY,last_nameVARCHAR2(25)CONSTRAINTs_emp_last_name_nnNOTNULL,first_nameVARCHAR2(25),useridVARCHAR2(8)CONSTRAINTs_emp_userid_nnNOTNULLCONSTRAINTs_emp_userid_ukUNIQUE,start_date

18、DATEDEFAULTSYSDATE,commentsVARCHAR2(25),manager_idNUMBER(7),titleVARCHAR2(25),dept_idNUMBER(7)CONSTRAINTs_emp_dept_id_fkREFERENCESs_dept(id),salaryNUMBER(11,2),commission_pctNUMBER(4,2)CONSTRAINTs_emp_commission_pct_ckCHECK(commission_pctIN(10,12.5,15,17.5,20);8.通过子查询建表通过子查询建表的例子SQLCREATETABLEemp_41

19、ASSELECTid,last_name,userid,start_dateFROMs_empWHEREdept_id=41;SQLCREATETABLEAasselect*fromBwhere1=2;只要表的结构.10.用子查询建表的注意事项1)可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。2)用子查询方式建立的表，只有非空NOTNULL的约束条件能继承过来,其它的约束条件和默认值都没有继承过来.3)根据需要，可以用altertableaddconstraint再建立其它的约束条件，如primarykey等.11.ForeignKey的可选参数OND

20、ELETECASCADE在创建ForeignKey时可以加可选参数:ONDELETECASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.如果没有ONDELETECASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.12.如果数据库表里有不满足的记录存在，建立约束条件将不会成功.13.给表创建和删除同义词的例子SQLCREATESYNONYMd_sum2FORdept_sum_vu;SQLCREATEPUBLICSYNONYMs_dept2FORalice.s_dept;SQLDROPSYNONYMs_dept;Oracle添加修改删除表字段1、添加字段

21、altertabletable_nameaddcolumn1typeaddcolumn2typeaddcolumn3type.;2、删除字段altertabletable_namedropcolumncolumn_name;3、修改字段altertabletable_namemodify(column_nametype);14.修改字段名方法：ALTERTABLE表名MODIFY(字段名notnull)15.请问如何修改一张表的主键?altertableaaadropconstraintaaa_key;altertableaaaaddconstraintaaa_keyprimarykey(a1,b1);16.如果该字段不是外键中的字段，你可以通过：altertabletab_namedropCOLUMNcol_name;删除1.修改主键ALTERTABLEtable_nameDROPPRIMARYKEYCASCADE;ALTERTABLEtable_nameADDPRIMARYKEY(column_names);2.ALTERTABLEtable_nameDROPCOLUMN(col1,col2,col3);-fororacle8iorlaterALTERTABLEtable_nameADD(col1datatype,clo2datatype);第 9 页 共 9 页