Oracle 数据库语法总结

Oracle数据库语法总结沈小明雷运石整理长沙牛耳软件学院2008年7月12日编写前的话：学好数据库的基本就是掌握基本的语法和记好相关的单词，本人初学ORACLE，觉得很无聊，于是就开始制作本小册子，由于时间仓促，若有不足之处，请批评并指正，谢谢！【表空间以及数据表创建】--------创建表空间createtablespacenewerdatafile'F:\Oracle基础\work\newer.dbf'size10mautoextendon;--------创建临时表空间createtemporarytablespacenewer1tempfile'F:\Oracle基础\work\newer1.dbf'size10mautoextendon;--------删除表空间droptablespacenewerincludingcontentsanddatafilescascadeconstraints;--可选,表空间如果有对象一起删,并把数据文件一起删除和约束一起删--------创建用户createuserACCP(用户名)identifiedbyACCP（密码）defaulttablespacenewertemporarytablespacenewer1--------授权grantconnecttoACCP--授予连接数据库的权限grantresourcetoACCP--角色（上面2个权限是最基本的，是创建用户必须给予的两个权限）grantcreatetabletoACCP--授予创建表的权利grantcreateviewtoACCP--授予创建视图的权利grantselectonscott.emptoACCP--将scott.emp表的查询权限给AccpgrantAllonscott.emptoACCP---将scott.emp表的增删改查权限给Accp--------创建表数据类型：（下面详述）字符类型：char（1-2000）vatchat2（1-4000）long（1-2gb）数值类型：number（p，s）日期类型：创建测试用表Createtableempasselect*fromscott.empOr-----创建emp表---createtableEMP(EMPNONUMBER(4)notnull,ENAMEVARCHAR2(10),JOBVARCHAR2(9),MGRNUMBER(4),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,2),DEPTNONUMBER(2))；----------------设置主建约束------------altertableEMPaddconstraintPK_EMPprimarykey(EMPNO)----设置外建约束----altertableEMPaddconstraintFK_DEPTNOforeignkey(DEPTNO)referencesDEPT(DEPTNO);altertableempaddconstraintfk_deptnoforeignkey(deptno)referencesdept(deptno);--创建dept表----createtableDEPT(DEPTNONUMBER(2)notnull,DNAMEVARCHAR2(14),LOCVARCHAR2(13))【数据类型详解】字符数据类型char2000字节varchar4000字节char与varchar的区别是当char与varchar2都是存储10个字节时。如下：char(10).varchar2(10);当存储如下所示时：汗水真爽在char里面就是10个字节但在varchar2中只有8个字节;long2GB表中只有一列不能是唯一与主键约束。不能建立索引，不能作为存储或过程的参数类型number(p,s);p代表精度1至38位，S表示小数点右边数字的位数，他在-84到127之间日期类型date认识的普通格式：21-4月-2008date'1989-02-23'to_date('1990-03-23','yyyy-mm-dd')timestamp精确到小数点后6位systimestamp系统时间row和longrow存储二进制数据row2000字节longrow2GB用于存储可变二进制数据LOB大对象数据，存储4GB非结构化数据子类型有：bfileclobblob【sqlsqlsqlsql语句，函数】数据定义语言DDLcreatedropaltertruncate--利用现有的表创建新表createtabletempasselect*fromemp;createtabletest(new_namevarchar2(400),new_scodetimestamp,new_datenumber(4,5)--default(systimestamp)--默认不是约束，只是一种附加条件而)--建立一些常见的约束---注：建立约束的前提是表里面没数据的情况下进行主键约束：altertabletestaddconstraint这里随便取个别名就好，便于删除时用primarykey要设置的主键字段比如：altertabletestaddconstraintnew_testprimarykey(new_name);唯一键约束：altertabletestaddconstraintnew_scodeingunique(new_scode);check约束：altertabletestaddconstraintnew_scodeingcheck(new_idbetween1and1999);外键:altertabletestaddconstraintnew_empnoforeignkey(deptno)referencesdept(deptno);altertableempaddconstraintnew_enmpnoforeignkey(deptno)referencesdept(deptno);删除约束：altertabletestdropconstraint约束别名添加列：altertabletestaddnew_lievarchar2(2222);修改列的结构类型，前提：没有数据的条件下进行：altertabletestmodify(new_lienumber);删除列：altertabletestdropcolumnnew_lie;在有数据的情况下也可以使用--修改现有表的数据类型，条件是没数据插入的情况下可以用使用--altertabletestmodify(现有字段，修改的类型结构);---通过ADD添加表的字段----altertabletestadd(new_addressvarchar2(2233),new_agenumber);----删除表中的列----altertabletestdropcolumnnew_age;---修改表的列名与修改表名rename关键字--altertabletemprenamecolumnsaltoxunlei;--此处的temp是用createtabletempasselect*fromemp;altertabletemprenametoxunlei;数据操纵语言DMLselectinsertupdatetruncate--查询空间下的所有表select*fromuser_all_tables去掉重复的值selectdistinct字段from表名两种连接方法如下：selectcount(*)as人数,b.deptnoas部门编号fromempa,deptbwherea.deptno=b.deptnoandsal=1000groupbyb.deptnoorderbycount(*);selectcount(a.empno)as工资,b.deptnoas部门编号fromempainnerjoindeptbona.deptno=b.deptnowheresal1000groupbyb.deptnohavingb.deptno=10orderbyb.deptno;--右连接----selectename,dnamefromemparightjoindeptbona.deptno=b.deptno或者：selectenameas人数，b.deptnoas部门编号fromempa,deptbwherea.deptno(+)=b.deptno;selectcount(a.empno)as工资,b.deptnoas部门编号fromempa,deptbwherea.deptno=b.deptnoandsal1000groupbyb.deptno--出现groupby时必须有聚合函数的出现才能使用having---分组后的条件不能单独出现，只能配合groupby联合使用orderbycount(a.empno)desc;selectto_char(sysdate,'mm')fromdual;--转换字符复制另外表的数据insertintoemp1selectemp.empnofromemp;createtableemp1(bnumber(4));deletefrom表名where--隐藏事务，可以回滚truncatetable表名---截断表，但结构存在。不能回滚。没隐藏事务droptable表名---删除表及所有数据,没隐藏事务，不能回滚----事务控制语言----commit提交；savepoint保存点（标记）--用它更方便我们控制事务处理rollback--回滚rollbacktosavepoint标记点--回滚到标记点例题：updateempsetename='aaa'whereempno=7369;savepointsavename;deleteempwhereempno=7369;savepointsavename1;rollbacktosavepointsavename;commit;createtablespaceleidatafile'f:\lei.dbf'size10Mautoextendon;createusertestingidentifiedbya123ddefaulttablespacenew_scott;---授权的withgrantoption怎么用的---步骤如下：首先在其他空间下比如将：grantallonscott.empto另外用户withgrantoption;然后进入另外用户下进行如：insertintoscott.empvalues()操作。--收回权限--revokeconnectfromusername---删除用户连接权限revokeselectonempfromusername----删除对具体表的操作权限---集合操作符union---去掉重复的selectdeptonfromempunionselectdeptnofromdept;unionall---联合所有不去掉重复的intersect--交集---取出相同的字段对应的值minus---减集selectdeptnofromdeptminusselectdeptnofromemp;---根据前面的表字段值跟后面表匹配，打印出第二张表中不含有的字段值优先级算术连接比较not非and与or或---SQL函数--单行函数---日期函数------在指定时间日期月加上指定的数值add_months例如selectadd_months(sysdate,2)fromdual;----months_between返回两个日期之间的日期差selectmonths_between(systimestamp,'12-4月-2008')fromdual;---last_day返回当月的最后一天selectlast_day(sysdate)fromdual;----next_day返回下个星期几的日期0-selec