韩顺平Oracle记录

韩顺平Oracle记录DataBase相关2009-11-2512:42阅读9评论0字号：大大中中小小常用命令：connscott/tiger@ORCLassysdba连接//ORCL为想要登录的实例(可选)，as后面的为可选项passwyhef修改username=yhef的密码@aa.sql执行一个文件中的所有sqlspool将屏幕数据导出showtablesshowuser//查看自己是谁setlinesize设置显示行的宽带，默认80个字符setpagesize创建用户：createuseryhefidentifiedbyyhefpwd删除用户：dropuseryhef如果欲删除的用户下有表，则需要加cascade参数，级联删除赋权限：grantconnecttoyhef//connect是一种权限，权限包括系统权限(对环境的操作)和对象权限(对别人的资源的操作)常见角色：(connect/dba/resource)grantresourcetoyhefgrantdbatoyhef授权：grantselectonemptoyhef//只授select权限//select、insert、delete、update、allselect*fromscott.emp//继续传递对象权限grantselectonemptoyhefwithgrantoption//让yhef有权利把这个emp表的select权限再授权给别人.//继续传递系统权限grantconnecttoyhefwithadminoption撤销授权：revokeselectonempformyhef//收回select权限profile用户口令管理alteruseryhefprofilemyProfilealteruseryhefaccountunlock查看表结构：descemp数据类型char定长，类型最大2000个字符。char的效率比较高varchar2,最大4000个字符。clob(characterlargeobject)字符型大对象，最大4Gnumber(5)一个5位整数number(5,2)一个5位，其中小数有2位datetimestampblob二进制4G建表：createtableemp(namevarchar2(20),agenumber(3))增加字段：altertableempadd(sexchar(2))修改字段的长度：altertableempmodify(namevarchar2(30))删除字段：altertableempdropcolumnsex;修改表名：renameemptoemployer删除表：droptableempOracle中date类型的默认格式dd-mm-yy修改默认时间格式altersessionsetnls_date_format=’yyyy-mm-dd’updateempsetage=30wherename=’xx’;savapointaa;deletefromemprollbacktoaa;truncatetableemp;只删除数据，表结构仍在表查询selectdistinctdeptnofromemp使用算术表达式Selectsal*12+nvl(comm,0)*12salfromemp;_代表一个任意字符%0-多个Selectsal*12+nvl(comm,0)*12“年薪”fromemporderby“年薪”desc;//用别名排序maxminavgsumcountall/any利用rownum分页Select*fromempSelectt1.*,rownumrnfrom(select*fromemp)t1whererownum=10;Select*from(selectt1.*,rownumrnfrom(select*fromemp)t1whererownum=10)wherern=5;利用查询结果来创建新表createtableMYTABLEasselect*fromemp;createtablemyemp(deptno,name)asselectdeptno,enamefromemp;union(重复的只取一次)/unionall(直接两个结果相加)/intersect(取交集，左右重复的数据)/minus(取差集,大表减小表后剩下的数据)Oracle函数：to_date(‘yyyy-mm-dd’,’2009-11-22’);lower(char)upper(char)length(char);substr(char,m,n)replace(char1,search_str,replace_str)///////round(n,[m])四舍五入trunk(n,[m])mod(m,n)floor(n)返回小于或等于n的最大整数ceil(n)返回大于或等于n的最小整数///////sysdateadd_months(m,n)m加上n个月select*fromempwheresysdateadd_months(hiredate,8);last_day(d)返回指定日期所在月的最后一天///////to_char(m,partten)selectename,to_char(hiredate,’yyyy-mm-ddhh24:mi:ss’)to_char(sal,’L99,999.99’)fromemp;//////////////////环境信息sys_context：db_namelanguagesession_usercurrent_schemanls_date_formatterminalhostselectsys_context(‘USERENV’,’db_name’)fromdual;//USERENV为固定字符串//用一条insert语句，插入多条数据insertintomyemp(deptno,ename)selectdeptno,enamefromempwheredeptno=10;//将scott得job,sal,comm改为跟simth一样updateempset(job,sal,comm)=(selectjob,sal,comm.Fromempwhereename=’SMITH’)whereename=‘SCOTT’;//设置为只读事务,我设置了只读事务以后，其他人的操作我将看不到。settransactionreadonly//////////////////////////////导表expuserid=scott/tiger@orcltables=(emp,dept)file=d:\scott.emp.dmp导出表expuserid=scott/tiger@orcltables=(emp,dept)file=d:\scott.emp.dmprows=n只导表结构，不导数据expuserid=scott/tiger@orcltables=(emp,dept)file=d:\scott.emp.dmpdirect=y处理大数据量导sechemaexpscott/tiger@orclowner=(scott,yhef)file=d:\scott.dmp导数据库expuserid=system/manager@orclfull=yinctype=completefile=d:\db.dmp///////////////////导入impimpuserid=scott/tiger@orcltables=(emp)file=d:\emp.dmp导入sechemaimpuserid=scott/tigerfile=scott.dmpimpuserid=system/managerfile=d:\system.dmpfromuser=systemtouser=scott导数据库impuserid=system/managerfull=yfile=d:\system.dmp//////////////数据字典user_xxxxxall_xxxxdba_xxxxxuser_tablesselecttable_namefromuser_tablesall_tablesselecttable_namefromall_tables;//当前用户所有能访问的表dba_users/dba_sys_privs/dba_tab_privs/dba_role_privs/dba_col_privsselect*fromdba_roles////////createtablespacetsp01datafile‘d:\tsp01.dbf’size20muniformsize128kcreatetablemyemp(enamevarchar2(10))tablespacetsp01;altertablespacetsp01offline;altertablespacetsp01online;altertablespacetsp01readonlydroptablespacetsp01includingcontentsanddatafiles;//扩展表空间方法1:altertablespacetsp01adddatafile‘d:\data2.dbf’size20m方法2:altertablespacetsp01‘d:\tsp01.dbf’resize20m//datafile方法3:altertablespacetsp01‘d:\tsp01.dbf’autoextendonnext10mmaxsize500m//重新指向altertablespacetsp01renamedatafile‘d:\tsp01.dbf’to‘c:\tsp01.dbf’约束：notnull/unique/primarykey/foreignkey/checkcreatetableitems(itemIdchar(10)primarykey,itemNamevarchar2(200),pricenumber(5,2)check(price0));createtablecustomer(custIdchar(5)primarykey,custNamevarchar2(20)notnull,emailvarchar2(40)uniquesexchar(2)default‘男’check(sexin(‘男’,’女’)));createtablepurchase(custIdchar(8)referencescustomer(custId),itemIdchar(8)referencesitem(itemId),numsnumber(5)check(numsbetween1and50)));AltertableitemmodifyitemNamenotnull;AltertablecustomeraddconstraintcustIdConstunique(custId);AltertablecustomerdropconstraintcustIdConstAlerttablecustomerdropprimarykeycascade;索引:CreateindexnameIndexoncustomer(custName);CreateindexmyIndexoncustomer(custId,custName);//角色:Connect/resource/dba//建立角色，（不验证）CreaterolemyRoleNamenotidentifiedCreaterolemyRoleNamenotidentifiedbyyhef;GrantcreatesessiontomyRoleNamewithadminoptionGrantmyRoleNametoyhef;Select*fromdba_roles;//过程createorreplaceproceduremy_first_spisbegin--执行部分inse