ORACLE-常用基础命令总结

zidong

　　博文说明【前言】：
　　本文将通过个人口吻说明记录oracle常用基础命令，在目前时间点【2017年5月16号】下，所掌握的技术水平有限，可能会存在不少知识理解不够深入或全面，望大家指出问题共同交流，在后续工作及学习中如发现本文内容与实际情况有所偏差，将会完善该博文内容。
　　关于表空间及用户创建相关命令，欢迎看我的另一篇博文：“ORACLE-用户及表空间创建命令总结”
　　博文链接地址：http://watchmen.blog.51cto.com/6091957/1926409
　　正文：
　　Oracle中的增删改查指的是：insert、delete、update、select
　　Oracle中最精华的部分：对用户的管理，对权限的控制
　　一：增【插入数据及添加命令】
　　1、往表中插入日期格式数据
　　插入全部字段：
SQL> insert into student values('mark1',to_date('2003-08-21','YYYY-MM-DD'));　　【oracle默认的日期格式是DD-MON-YY 日-月-年，如果要用默认的方式插入数据化就是这种形式：insert into stu values('2011','zhangsan','男','50','12-11月-1990');  注意，要写中文月字】
　　插入部分字段：
sql>insert into student(xh,xm,sex,birthday) values('a222','john','女',null);　　2、修改插入的默认日期格式为年月日
sql>alter session setnls_date_format='yyyy-mm-dd';　　3、往表中添加一个字段
SQL> alter table student add classId number(2);　　【注意】oracle在增加字段的时候只会增加到表的最后，不能自定义该字段的位置，不能使用befor或者after，不同于MySQL
　　二：删【删除数据命令】
　　1、删除用户
sql>drop user scott [cascade];　　如果要删除的用户，已经创建了表，那么就需要在删除时带上cascade参数
　　2、删除字段
sql>alter table studentdrop column sal;　　3、删除表中的数据【表还在】
sql> delete from student;--删除数据，表结构还在，在删除过程中会写日志，后期还可以进行恢复sql> truncate table student;　　
--删除表中的数据，表结构还在，在删除过程中不写日志，无法找回删除的记录，因此删除的速度较快
　　
--但后期无法对数据进行恢复
　　4、删除表【删除表的数据和结构】
sql> drop table student;　　
　　三：改【修改数据命令】
　　1、修改字段的类型或长度【注意：此时该字段不能有数据】
sql>alter table studentmodify xm char(30);　　2、修改字段的名字
sql>alter table student renamecolumn xm to newxm;　　3、修改表中某个字段的指
sql> update student setsex='女' wherexh='A0001';　　4、修改表中某个字段的值的一部分
sql> update tablename set configvalue=replace(configvalue,'10.10.68.172:5003','30.1.32.73:8052')　　
　　命令解释：将10.10.68.172:5003替换成30.1.32.73:8052
　　5、修改表的名字
sql>rename student tostu;　　6、修改用户密码
Sql> alter user “system” identified by “cxh123456”;　　四：查【查询数据命令】
　　1、查询某一个字段的值为空值或不为空值
SQL> select * fromstudent where birthday is null[is not null];　　2、查询结果取消重复记录
sql> select distinct deptno,job from emp;　　3、查询date日期类型字段
select * from stu whereto_char(LOGINDATE,'yyyy-MM-DD hh24: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,0,'客服人员',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)
set lines 300 pages 1000　　
col tablespace_name for a50
　　
select a.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 (select d.tablespace_name,
　　
               sum(bytes / 1024 / 1024) s,
　　
               sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms
　　
          from dba_data_files d
　　
         group by d.tablespace_name) a,
　　
       (select f.tablespace_name, sum(f.bytes / 1024 / 1024) s
　　
          from dba_free_space f
　　
         group by f.tablespace_name) f,
　　
       (select distinct tablespace_name, autoextensible
　　
          from DBA_DATA_FILES
　　
         where autoextensible = 'YES'
　　
        union
　　
        select distinct tablespace_name, autoextensible
　　
          from DBA_DATA_FILES
　　
         where autoextensible = 'NO'
　　
           and tablespace_name not in
　　
               (select distinct tablespace_name
　　
                  from DBA_DATA_FILES
　　
                 where autoextensible = 'YES')) g
　　
where a.tablespace_name = f.tablespace_name
　　
   and g.tablespace_name = f.tablespace_name order by "FREE%";
　　2、查看表空间的数据文件对应的物理文件名称及路径
select file_id,　　
file_name,
　　
round(bytes / (1024 * 1024), 0)total_space
　　
FROM dba_data_files
　　
ORDER BY tablespace_name;
　　3、查看表空间的详细信息
select * from dba_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、查看当前数据库的字符集
select userenv('language') from dual;　　
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
　　6、查看数据库版本
select *from v$version;　　7、查看oracle数据库的service_name
select name from v$database;　　8、查看当前连接数据库的SID (Instance)
select instance_name from v$instance;　　9、查看oracle全局数据库名
select * from global_name;　　
输出类似：ORACLE10.REGRESS.RDBMS.DEV.US.ORACLE.COM   【name.domain】
　　10、查看数据库中有哪些用户
SQL> selectusername,password from dba_users;　　
SQL> select * fromall_users;
　　11、查看oracle下所有的表空间，包括临时表空间
SQL> select tablespace_name from dba_tablespaces;  --这张表只存放表空间信息，不包含用户信息什么的　　
select *  from dba_tablespaces; --可以看到表空间的block_size等信息
　　12、查看数据库中所有用户的默认表空间和临时表空间
selectusername,default_tablespace,temporary_tablespace from dba_users　　13、查询当前登录用户的默认表空间和临时表空间
selectusername,default_tablespace,temporary_tablespace from user_users;　　
select * fromuser_users;   可以查看当前登录用户的一些信息
　　14、查询当前登录用户下的所有表
select * from user_tables;　　15、查询当前登录用户下表的数量
select count(*) fromuser_tables;　　16、查询RAC集群的公网信息，随便一台就能看
select utl_inaddr.get_host_address(host_name),host_name from gv$instance;　　17、查询数据库参数信息
show PARAMETERS name;　　18、查询数据库下各用户的表的数量
select count (*) , owner from dba_tables group by owner order by owner;　　19、查询RAC集群各实例的连接数
select inst_id,count(*) from gv$session group by inst_id;　　20、查询数据库最大连接数
select value from v$parameter where name ='processes';　　21、查询AIX系统配置信息
1、prtconf　　
2、bootinfo -r
　　
3、vmstat    查看CPU核心数量（虚拟CPU个数）和内存
　　
注意：登录AIX系统之后，注意TERM变量的设置，要设置成vt100
　　
　　
　　六：授权命令
　　1、系统授权，授权系统权限
grant connect,resource toxiaoming 【一般为新建用户初始化授权使用】　　
Resource：可以让普通用户在任何一个表空间建表
　　2、用户授权，授权数据对象权限
grant select on emp toxiaoming [with grant option];　　
【只授权查询权限给xiaoming用户,[]内参数为该权限可以被传递再次进行权限】
　　
grant all on emp toxiaoming;【授权所有权限给xiaoming用户】
　　3、将用户所有表的查询权限授权给另一个用户【不使用dba权限用户】
登录cklp用户，将所有表的权限分配给query用户　　
select 'GRANT SELECT ON '||table_name||' to query;'  from user_tables;
　　
将执行结果复制出来，写成一个sql文件  然后再执行，里面都是授权语句。
　　
　　七：权限回收命令
　　1、用户授权回收：
revoke select on emp fromxiaoming;　　
【如果xiaoming用户把相应的权限做了再次授权给其他用户，那么，其他用户的权限也会被回收，即彻底回收】
　　八：排序命令
　　1、查询结果进行多次排序并且升降序不同
按照部门号升序而雇员的工资降序排列　　
sql>select * from emp orderby deptno [asc],sal desc;           默认asc
　　
注意：有多个排序同时要求的情况使用,进行隔开注意order by只有一个  后面按顺序写上字段和规则
　　2、使用列的别名进行排序
select ename,sal*12 "年薪" from emp order by"年薪"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的后面的
　　SQL> select ename,deptno,sal from emp group by deptno,ename,sal order by sal desc;
　　1、对分组显示的结果进行限制显示
select deptno,avg(sal),max(sal) from emp group by deptno havingavg(sal) select ename,sal from emp where ename like 'S%';   注意要有''单引号
　　11：设置主键
　　1、在创建表的时候就进行创建
create table categoryinfo(categoryId varchar2(10),categoryName varchar2(30),primarykey(categoryId));　　2、创建表之后再进行修改
alter table cott.categoryinfo add constraints pk_category primary key(categoryId);　　3、删除主键
alter table scott.categoryinfo drop constraint pk_category;　　12：设置外键
　　1、在创建表的时候进行外键的创建：
create table  productinfo(productidvarchar2(10),productname varchar(20),　　
category varchar2(10),constraint fk_produce foreign  key(category)
　　
references categoryinfo(categoryID) on delete cascade);
　　2、创建表之后再进行修改：
alter table productinfo add constraint fk_produce foregin key(category)　　
references categoryinfo(categoryId) on delete cascade);
　　3、删除外键：
alter table productifo drop constraint fk_product;　　13：自连接
　　自连接是指在同一张表上的连接查询
select worker.ename,boss.ename from emp worker,emp boss　　
where worker.mgr=boss.empno;
　　14：其他常用命令
　　1、解锁用户
alter user scott account unlock;　　2、用查询结果来创建新表【常用于对表做备份操作】
create table emp_bak as select * from emp;　　结尾：
　　
　　感谢阅读，祝有收获的一天！