ORACLE-常用基础命令总结
博文说明【前言】: 本文将通过个人口吻说明记录oracle常用基础命令,在目前时间点【2017年5月16号】下,所掌握的技术水平有限,可能会存在不少知识理解不够深入或全面,望大家指出问题共同交流,在后续工作及学习中如发现本文内容与实际情况有所偏差,将会完善该博文内容。 关于表空间及用户创建相关命令,欢迎看我的另一篇博文:“ORACLE-用户及表空间创建命令总结” 博文链接地址:http://watchmen.blog.51cto.com/6091957/1926409 正文: Oracle中的增删改查指的是:insert、delete、update、select Oracle中最精华的部分:对用户的管理,对权限的控制 一:增【插入数据及添加命令】 1、往表中插入日期格式数据 插入全部字段: SQL>insertintostudentvalues('mark1',to_date('2003-08-21','YYYY-MM-DD')); 【oracle默认的日期格式是DD-MON-YY 日-月-年,如果要用默认的方式插入数据化就是这种形式:insert into stu values('2011','zhangsan','男','50','12-11月-1990'); 注意,要写中文月字】 插入部分字段: sql>insertintostudent(xh,xm,sex,birthday)values('a222','john','女',null); 2、修改插入的默认日期格式为年月日 sql>altersessionsetnls_date_format='yyyy-mm-dd'; 3、往表中添加一个字段 SQL>altertablestudentaddclassIdnumber(2); 【注意】oracle在增加字段的时候只会增加到表的最后,不能自定义该字段的位置,不能使用befor或者after,不同于MySQL 二:删【删除数据命令】 1、删除用户 sql>dropuserscott[cascade]; 如果要删除的用户,已经创建了表,那么就需要在删除时带上cascade参数 2、删除字段 sql>altertablestudentdropcolumnsal; 3、删除表中的数据【表还在】 sql>deletefromstudent;--删除数据,表结构还在,在删除过程中会写日志,后期还可以进行恢复 sql>truncatetablestudent; --删除表中的数据,表结构还在,在删除过程中不写日志,无法找回删除的记录,因此删除的速度较快 --但后期无法对数据进行恢复 4、删除表【删除表的数据和结构】 sql>droptablestudent; 三:改【修改数据命令】 1、修改字段的类型或长度【注意:此时该字段不能有数据】 sql>altertablestudentmodifyxmchar(30); 2、修改字段的名字 sql>altertablestudentrenamecolumnxmtonewxm; 3、修改表中某个字段的指 sql>updatestudentsetsex='女'wherexh='A0001'; 4、修改表中某个字段的值的一部分 sql>updatetablenamesetconfigvalue=replace(configvalue,'10.10.68.172:5003','30.1.32.73:8052') 命令解释:将10.10.68.172:5003替换成30.1.32.73:8052 5、修改表的名字 sql>renamestudenttostu; 6、修改用户密码 Sql>alteruser“system”identifiedby“cxh123456”; 四:查【查询数据命令】 1、查询某一个字段的值为空值或不为空值 SQL>select*fromstudentwherebirthdayisnull[isnotnull]; 2、查询结果取消重复记录 sql>selectdistinctdeptno,jobfromemp; 3、查询date日期类型字段 select*fromstuwhereto_char(LOGINDATE,'yyyy-MM-DDhh24:mi:ss')='2016-07-11'; 【进行转义,将date类型转义成char类型并制定格式,然后进行匹配】 4、查询结果计算平均值并指定输出位数 select count(distinct cust.regist_no) "每周案件总数", trunc(count(distinctcust.regist_no)/14,2) "每日平均数" 【总数除以14,指定输出2位】 from CP_CUST_REGSIT_INFOcust where xxxx….. 查询3个或3个以上条件【使用逻辑操作符号】 5、查询结果,并将字段的值指定别名: SQL> select * from empwhere (sal>500 or job='manager') and ename like 'J%'; select CUSTOMER_CODE "客户编号",SERVICE_CODE "客服编号",decode(SER_OR_CLI,'客服人员',1,'客户') "消息发送者",CONTENT "聊天内容",to_char(time,'yyyy-mm-dd hh24:mi:ss') "聊天日期" from CP_WECHAT_NEWS_HIS whereto_char(time,'yyyy-mm-dd') between '2016-09-26' and '2016-09-29' group byCUSTOMER_CODE,SERVICE_CODE,SER_OR_CLI,CONTENT,time order by CUSTOMER_CODE,time; 五:查询系统参数命令 1、查看表空间使用率(M) setlines300pages1000 coltablespace_namefora50 selecta.tablespace_name,round(a.s,2)"CURRENT_TOTAL(MB)",round((a.s-f.s),2)"USED(MB)",round(100-f.s/a.s*100,2)"USED%",f.s"FREE(MB)",round(f.s/a.s*100,2)"FREE%",g.autoextensible,round(a.ms,2)"MAX_TOTAL(MB)" from(selectd.tablespace_name,sum(bytes/1024/1024)s,sum(decode(maxbytes,bytes,maxbytes)/1024/1024)ms fromdba_data_filesd groupbyd.tablespace_name)a,(selectf.tablespace_name,sum(f.bytes/1024/1024)s fromdba_free_spacef groupbyf.tablespace_name)f,(selectdistincttablespace_name,autoextensible fromDBA_DATA_FILES whereautoextensible='YES' union selectdistincttablespace_name,autoextensible fromDBA_DATA_FILES whereautoextensible='NO' andtablespace_namenotin (selectdistincttablespace_name fromDBA_DATA_FILES whereautoextensible='YES'))g wherea.tablespace_name=f.tablespace_name andg.tablespace_name=f.tablespace_nameorderby"FREE%"; 2、查看表空间的数据文件对应的物理文件名称及路径 selectfile_id,file_name,round(bytes/(1024*1024),0)total_space FROMdba_data_files ORDERBYtablespace_name; 3、查看表空间的详细信息 select*fromdba_data_files; 4、查看详细的表空间使用情况【Byte,M等单位详细显示】 SELECT a.tablespace_name "表空间名",total "表空间大小",free "表空间剩余大小",(total - free) "表空间使用大小",total / (1024 * 1024 * 1024) "表空间大小(G)",free / (1024 * 1024 * 1024) "表空间剩余大小(G)",(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",round((total - free) / total,4) * 100 "使用率 %" FROM (SELECT tablespace_name,SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,(SELECT tablespace_name,SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name; 5、查看当前数据库的字符集 selectuserenv('language')fromdual; select*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET'; 6、查看数据库版本 select*fromv$version; 7、查看oracle数据库的service_name selectnamefromv$database; 8、查看当前连接数据库的SID (Instance) selectinstance_namefromv$instance; 9、查看oracle全局数据库名 select*fromglobal_name; 输出类似:ORACLE10.REGRESS.RDBMS.DEV.US.ORACLE.COM【name.domain】 10、查看数据库中有哪些用户 SQL>selectusername,passwordfromdba_users; SQL>select*fromall_users; 11、查看oracle下所有的表空间,包括临时表空间 SQL>selecttablespace_namefromdba_tablespaces;--这张表只存放表空间信息,不包含用户信息什么的 select*fromdba_tablespaces;--可以看到表空间的block_size等信息 12、查看数据库中所有用户的默认表空间和临时表空间 selectusername,default_tablespace,temporary_tablespacefromdba_users 13、查询当前登录用户的默认表空间和临时表空间 selectusername,temporary_tablespacefromuser_users; select*fromuser_users;可以查看当前登录用户的一些信息 14、查询当前登录用户下的所有表 select*fromuser_tables; 15、查询当前登录用户下表的数量 selectcount(*)fromuser_tables; 16、查询RAC集群的公网信息,随便一台就能看 selectutl_inaddr.get_host_address(host_name),host_namefromgv$instance; 17、查询数据库参数信息 showPARAMETERSname; 18、查询数据库下各用户的表的数量 selectcount(*),ownerfromdba_tablesgroupbyownerorderbyowner; 19、查询RAC集群各实例的连接数 selectinst_id,count(*)fromgv$sessiongroupbyinst_id; 20、查询数据库最大连接数 selectvaluefromv$parameterwherename='processes'; 六:授权命令 1、系统授权,授权系统权限 grantconnect,resourcetoxiaoming【一般为新建用户初始化授权使用】 Resource:可以让普通用户在任何一个表空间建表 2、用户授权,授权数据对象权限 grantselectonemptoxiaoming[withgrantoption]; 【只授权查询权限给xiaoming用户,[]内参数为该权限可以被传递再次进行权限】 grantallonemptoxiaoming;【授权所有权限给xiaoming用户】 3、将用户所有表的查询权限授权给另一个用户【不使用dba权限用户】 登录cklp用户,将所有表的权限分配给query用户 select'GRANTSELECTON'||table_name||'toquery;'fromuser_tables; 将执行结果复制出来,写成一个sql文件然后再执行,里面都是授权语句。 七:权限回收命令 1、用户授权回收: revokeselectonempfromxiaoming; 【如果xiaoming用户把相应的权限做了再次授权给其他用户,那么,其他用户的权限也会被回收,即彻底回收】 八:排序命令 1、查询结果进行多次排序并且升降序不同 按照部门号升序而雇员的工资降序排列 sql>select*fromemporderbydeptno[asc],saldesc;默认asc 注意:有多个排序同时要求的情况使用,进行隔开注意orderby只有一个后面按顺序写上字段和规则 2、使用列的别名进行排序 selectename,sal*12"年薪"fromemporderby"年薪"asc;【注意:别名需要使用"】 九:分组命令 在使用groupby的时候前面的select中必须包含它不能使用*代替所有 select 列表项中不存在的列可以出现在group by的列表项中,但在select列表项中出现的列必须全部出现在group by后面(聚合函数除外)。 因此在使用groupby的时候要注意后面接的一定要是前面出现的字段 SQL> select ename,deptno,sal from emp group by deptno,ename,sal; 【注意】在结合使用的时候order by是写在groupby的后面的 1、对分组显示的结果进行限制显示 selectdeptno,avg(sal),max(sal)fromempgroupbydeptnohavingavg(sal)<2000; 10:like操作符 显示首字符为S的员工姓名和工资 11:设置主键 1、在创建表的时候就进行创建 createtablecategoryinfo(categoryIdvarchar2(10),categoryNamevarchar2(30),primarykey(categoryId)); 2、创建表之后再进行修改 altertablecott.categoryinfoaddconstraintspk_categoryprimarykey(categoryId); 3、删除主键 altertablescott.categoryinfodropconstraintpk_category;
1、在创建表的时候进行外键的创建: createtableproductinfo(productidvarchar2(10),productnamevarchar(20),categoryvarchar2(10),constraintfk_produceforeignkey(category) referencescategoryinfo(categoryID)ondeletecascade); 2、创建表之后再进行修改: altertableproductinfoaddconstraintfk_produceforeginkey(category) referencescategoryinfo(categoryId)ondeletecascade); 3、删除外键: altertableproductifodropconstraintfk_product;
自连接是指在同一张表上的连接查询 selectworker.ename,boss.enamefromempworker,empboss whereworker.mgr=boss.empno;
1、解锁用户 alteruserscottaccountunlock; 2、用查询结果来创建新表【常用于对表做备份操作】 createtableemp_bakasselect*fromemp; 结尾: 感谢阅读,祝有收获的一天! (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |