oracle-sql语句

一、oraclesql相关记录1：表数据重复查询语句select*fromT_SPEC_VIEWwhereidin(selectidfromT_SPEC_VIEWgroupbyidhavingcount(*)1)orderbyid;select*from表where重复字段in（select重复字段from表groupby重复字段havingcount（*））；2：单表的复制createtablet_welcome_finance_series50905asselect*fromt_welcome_finance_series53：多表更新语法表A:A1，A2，表B：B1，B2；Ｂ１＝A1；updatet_jbxx_jbxxfy118setmzdm=(selectmzdmfromt_zxbz_mzwheret_zxbz_mz.mzmc=t_jbxx_jbxxfy118.mzdm)whereexists(select*fromt_zxbz_mzwheret_zxbz_mz.mzmc=t_jbxx_jbxxfy118.mzdm)方法二：updateasetvalue=(selectvaluefrombwhereid=a.id)whereexists(select1frombwhereid=a.id);3.1:多表联合查询，4：截取字段语句updateT_URP_DWfysetdwdm=substr(dwdm,0,3);replace（'将要更改的字符串','被替换掉的字符串','替换字符串'）例：selectreplace('111222333444','222','888')fromdual;selectbh,replace(xf,',')asxffromfy_cw后六位数据截取：substr(shzjh,length(shzjh)-6+1,6)ASpassword明文；5：修改字段名称，表名，字段类型，删除列createtableTEST(IDNUMBERnotnull,NAMEVARCHAR2(20))ALTERTABLETESTRENAMETOTEST1--修改表名ALTERTABLET_BKS_ZYFXRENAMECOLUMNyxTOpx--修改表列名ALTERTABLETESTMODIFYNAME1NUMBER(20)--修改字段类型ALTERTABLET_BKS_ZYFXADDJLNYVARCHAR2(12)--添加表列ALTERTABLETESTDROPCOLUMNADDRESS--删除表列6：多表查询多表查询：表连接分类：内连接、外连接、交叉连接1、内连接：[inner]joinonSQL语法格式：语法1：select*from表1[inner]join表2on表1.字段1=表2.字段1;语法2：select*from表1,表2where表1.字段1=表2.字段1;说明：内连接中的innerjoin和join是等价的！但是建议为了程序的可读性尽量不要省略inner!2、外连接：分类：左外连接、右外连接、全连接！1、左外连接：leftouterjoin连接效果：左侧的表中的全部数据都会被显示出来，但是右侧表的数据，只有和左侧匹配上的字段才会被查询出来！否则都会显示null！SQL语法格式：语法1：select*from表1leftouterjoin表2on表1.字段1=表2.字段1;语法2：select*from表1leftouterjoin表2where表1.字段1=表2.字段1(+);2、右外连接：rightouterjoin连接效果：右侧的表中的全部数据都会被显示出来，但是左侧表的数据，只有和右侧匹配上的字段才会被查询出来！否则都会显示null！SQL语法格式：语法1：select*from表1rightouterjoin表2on表1.字段1=表2.字段1;语法2：select*from表1leftouterjoin表2where表1.字段1(+)=表2.字段1;3、全外连接：full/allouterjoinSQL语法格式：select*from表1fullouterjoin表2on表1.字段1=表2.字段1;2、交叉连接：表与表之间做笛卡尔积查询！SQL语法格式：(无条件查询)select*from表1crossjoin表2;或者select*from表1,表2;二、oracle数据库备份相关7：数据库导出语句（备份）1：将数据库TEST完全导出,用户名system密码manager导出到D:\daochu.dmp中expsystem/manager@TESTfile=d:\daochu.dmpfull=y2：将数据库中system用户与sys用户的表导出expsystem/manager@TESTfile=d:\daochu.dmpowner=(system,sys)3：将数据库中的表inner_notify、notify_staff_relat导出expaichannel/aichannel@TESTDB2file=d:\data\newsmgnt.dmptables=（inner_notify,notify_staff_relat)4：将数据库中的表table1中的字段filed1以00打头的数据导出expsystem/manager@TESTfile=d:\daochu.dmptables=(table1)query=\wherefiled1like'00%'\5.1：备份单表或多表数据expuser/password@serverfile=filefullpathnamelog=logfullpathnametables=(tablespacename.table1,tablespacename.table2,...)8：数据库导入语句1：单表或多表数据恢复：如果被恢复的表已经存在，应该先将其删除（droptable），不然报“IMP-0015”错误，导入失败。单表或多表数据恢复命令如下：导入前需要先设置本地环境：setNLS_LANG=AMERICAN_AMERICA.AL32UTF81：impuser/password@serverfile=filefullpathnamelog=logfullpathnamefull=y2：impusername/pwd[@sid（服务名）]file=pathname+filenametables=(tablename):3：impusr_xg/ydxg2015@ydfile=D:\程序安装包\xg_new02.dmptables=(T_GGGL_BBWH)命令样例：impusr_xg/wiscom@ywk_172.18.0.1fromuser=usr_xgtouser=usr_xgfile=xg1.0.dmplog=imp.log三、oracle数据库系统sql语句9：查询一个数据库中，每个表的记录数selectTABLE_NAME,NUM_ROWSfromUSER_TABLESorderbyNUM_ROWSdesc10：查看数据库用户名和密码1：查看数据库用户名称和密码；selectusername,passwordfromdba_users；2：//修改用户口令格式alteruser用户名identifiedby新密码;SQLalterusersystemidentifiedbymanager;3：账号解锁：alteruser用户名accountunlock;4：锁定：alterusertestaccountlock;11：系统sql语句查询1：查看所有表空间语句：selecttablespace_name,sum(bytes)/1024/1024fromdba_data_filesgroupbytablespace_name;2:查看当前用户的缺省表空间SQLselectusername,default_tablespacefromuser_users;3.查看所有用户：select*fromdba_users;select*fromall_users;select*fromuser_users;4.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：select*fromdba_sys_privs;select*fromuser_sys_privs;5.查看角色(只能查看登陆用户拥有的角色)所包含的权限sqlselect*fromrole_sys_privs;6.查看用户对象权限：select*fromdba_tab_privs;select*fromall_tab_privs;select*fromuser_tab_privs;7.查看所有角色：select*fromdba_roles;8.查看用户或角色所拥有的角色：select*fromdba_role_privs;select*fromuser_role_privs;9.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)select*fromV$PWFILE_USERS注意：1、以下语句可以查看Oracle提供的系统权限selectnamefromsys.system_privilege_map10：查看：sidselect*fromv$instanceselect*fromv$active_instances11：查看当前用户的角色SQLselect*fromuser_role_privs;:12：查看当前用户的系统权限和表级权限SQLselect*fromuser_sys_privs;SQLselect*fromuser_tab_privs;:13：查看当前用户表空间：SELECTTABLE_NAME,TABLESPACE_NAMEFROMUSER_TABLES:14：查看那些用户有DBA权限：select*fromsys.dba_role_privswheregranted_role='DBA'12：创建表空间1.表空间给角色授权：2.alteruser用户名quotaunlimitedon表空间;3.回收用户dba的角色:revokedbafromuser4.查看表空间数据文件位置语句：select*fromdba_data_files5.删除用户：//删除用户以及用户所有的对象dropuserzfmicascade;//删除表空间前提：删除表空间之前要确认该表空间没有被其他用户使用之后再做删除DROPTABLESPACETS_XG_DINCLUDINGCONTENTSANDDATAFILESCASCADECONSTRAINTS;13：创建表空间语句1：创建表空间语句：--查询表空间所在路径select*fromV$datafile;--创建临时表空间createtemporarytablespaceTB_BD_TEMPtempfile'/u01/oradata/ZHFWDB/TB_BD_TEMP.dbf'size1000mautoextendonnext50mmaxsize2048mextentmanagementlocal;--创建表空间createtablespaceTB_BD_Dloggingdatafile'/u01/oradata/ZHFWDB/TB_BD_D.dbf'size2040Mautoextendonnext50mmaxsize2048mextentmanagementlocal;--创建用户createuserUSR_BDSCidentifiedby123456defaulttablespaceTB_BD_DtemporarytablespaceTB_BD_TEMP;--授权用户grantconnect,resource,dbatoUSR_BDSC;14：用户授权语句1：oracle中创建一个用户，只能查看指定的视图，如何授权grantselecton视图名字to用户名；2：grantconnect