ORACLE-常用基础命令总结

简介:

博文说明【前言】:

    本文将通过个人口吻说明记录oracle常用基础命令,在目前时间点【2017年5月16号】下,所掌握的技术水平有限,可能会存在不少知识理解不够深入或全面,望大家指出问题共同交流,在后续工作及学习中如发现本文内容与实际情况有所偏差,将会完善该博文内容。


关于表空间及用户创建相关命令,欢迎看我的另一篇博文:“ORACLE-用户及表空间创建命令总结”

博文链接地址:http://watchmen.blog.51cto.com/6091957/1926409



正文:


    Oracle中的增删改查指的是:insert、delete、update、select

    Oracle中最精华的部分:对用户的管理,对权限的控制


一:增【插入数据及添加命令】


1、往表中插入日期格式数据

插入全部字段:

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');  注意,要写中文月字】

插入部分字段:

1
sql>insert into student(xh,xm,sex,birthday) values( 'a222' , 'john' , '女' ,null);


2、修改插入的默认日期格式为年月日

1
sql>alter session setnls_date_format= 'yyyy-mm-dd' ;


3、往表中添加一个字段

1
SQL> alter table student add classId number(2);


【注意】oracle在增加字段的时候只会增加到表的最后,不能自定义该字段的位置,不能使用befor或者after,不同于MySQL


二:删【删除数据命令】


1、删除用户

1
sql>drop user scott [cascade];


如果要删除的用户,已经创建了表,那么就需要在删除时带上cascade参数

2、删除字段

1
sql>alter table studentdrop column sal;


3、删除表中的数据【表还在】

1
sql>  delete  from student;--删除数据,表结构还在,在删除过程中会写日志,后期还可以进行恢复
1
2
3
sql> truncate table student;
--删除表中的数据,表结构还在,在删除过程中不写日志,无法找回删除的记录,因此删除的速度较快
--但后期无法对数据进行恢复


4、删除表【删除表的数据和结构】

1
sql> drop table student;


三:改【修改数据命令】


1、修改字段的类型或长度【注意:此时该字段不能有数据】

1
sql>alter table studentmodify xm  char (30);


2、修改字段的名字

1
sql>alter table student renamecolumn xm to newxm;


3、修改表中某个字段的指

1
sql> update student setsex= '女'  wherexh= 'A0001' ;


4、修改表中某个字段的值的一部分

1
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、修改表的名字

1
sql> rename  student tostu;


6、修改用户密码

1
Sql> alter user “ system ” identified by “cxh123456”;


四:查【查询数据命令】


1、查询某一个字段的值为空值或不为空值

1
SQL> select * fromstudent where birthday is null[is not null];


2、查询结果取消重复记录

1
sql> select distinct deptno,job from emp;


3、查询date日期类型字段

1
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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、查看表空间的数据文件对应的物理文件名称及路径

1
2
3
4
5
select file_id,
file_name,
round(bytes / (1024 * 1024), 0)total_space
FROM dba_data_files         
ORDER BY tablespace_name;


3、查看表空间的详细信息

1
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、查看当前数据库的字符集

1
2
select userenv( 'language' ) from dual;
select * from nls_database_parameters where parameter= 'NLS_CHARACTERSET' ;


6、查看数据库版本

1
select *from v$version;


7、查看oracle数据库的service_name

1
select name from v$database;


8、查看当前连接数据库的SID (Instance)

1
select instance_name from v$instance;


9、查看oracle全局数据库名

1
2
select * from global_name;
输出类似:ORACLE10.REGRESS.RDBMS.DEV.US.ORACLE.COM   【name.domain】


10、查看数据库中有哪些用户

1
2
SQL> selectusername,password from dba_users;
SQL> select * fromall_users;


11、查看oracle下所有的表空间,包括临时表空间

1
2
SQL> select tablespace_name from dba_tablespaces;  --这张表只存放表空间信息,不包含用户信息什么的
select *  from dba_tablespaces; --可以看到表空间的block_size等信息


12、查看数据库中所有用户的默认表空间和临时表空间

1
selectusername,default_tablespace,temporary_tablespace from dba_users


13、查询当前登录用户的默认表空间和临时表空间

1
2
selectusername,default_tablespace,temporary_tablespace from user_users;
select * fromuser_users;   可以查看当前登录用户的一些信息


14、查询当前登录用户下的所有表

1
select * from user_tables;


15、查询当前登录用户下表的数量

1
select count(*) fromuser_tables;


16、查询RAC集群的公网信息,随便一台就能看

1
select utl_inaddr.get_host_address(host_name),host_name from gv$instance;


17、查询数据库参数信息

1
show PARAMETERS name;


18、查询数据库下各用户的表的数量

1
select count (*) , owner from dba_tables group by owner order by owner;


19、查询RAC集群各实例的连接数

1
select inst_id,count(*) from gv$session group by inst_id;


20、查询数据库最大连接数

1
select value from v$parameter where name = 'processes' ;


21、查询AIX系统配置信息

1
2
3
4
1、prtconf
2、bootinfo -r
3、vmstat    查看CPU核心数量(虚拟CPU个数)和内存
注意:登录AIX系统之后,注意TERM变量的设置,要设置成vt100



六:授权命令


1、系统授权,授权系统权限

1
2
grant connect,resource toxiaoming 【一般为新建用户初始化授权使用】
Resource:可以让普通用户在任何一个表空间建表


2、用户授权,授权数据对象权限

1
2
3
grant select on emp toxiaoming [with grant option];
【只授权查询权限给xiaoming用户,[]内参数为该权限可以被传递再次进行权限】
grant all on emp toxiaoming;【授权所有权限给xiaoming用户】


3、将用户所有表的查询权限授权给另一个用户【不使用dba权限用户】

1
2
3
登录cklp用户,将所有表的权限分配给query用户  
select  'GRANT SELECT ON ' ||table_name|| ' to query;'   from user_tables; 
将执行结果复制出来,写成一个sql文件  然后再执行,里面都是授权语句。


七:权限回收命令


1、用户授权回收:

1
2
revoke select on emp fromxiaoming;
【如果xiaoming用户把相应的权限做了再次授权给其他用户,那么,其他用户的权限也会被回收,即彻底回收】


八:排序命令


1、查询结果进行多次排序并且升降序不同

1
2
3
按照部门号升序而雇员的工资降序排列
sql>select * from emp orderby deptno [asc],sal desc;           默认asc
注意:有多个排序同时要求的情况使用,进行隔开注意order by只有一个  后面按顺序写上字段和规则


2、使用列的别名进行排序

1
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、对分组显示的结果进行限制显示

1
select deptno,avg(sal),max(sal) from emp group by deptno havingavg(sal)<2000;


10:like操作符


显示首字符为S的员工姓名和工资

SQL> select ename,sal from emp where ename like 'S%';   注意要有''单引号


11:设置主键


1、在创建表的时候就进行创建

1
create table categoryinfo(categoryId varchar2(10),categoryName varchar2(30),primarykey(categoryId));


2、创建表之后再进行修改

1
alter table cott.categoryinfo add constraints pk_category primary key(categoryId);


3、删除主键

1
alter table scott.categoryinfo drop constraint pk_category;


12:设置外键


1、在创建表的时候进行外键的创建:

1
2
3
create table  productinfo(productidvarchar2(10),productname varchar(20),
category varchar2(10),constraint fk_produce foreign  key(category) 
references categoryinfo(categoryID) on  delete  cascade);


2、创建表之后再进行修改:

1
2
alter table productinfo add constraint fk_produce foregin key(category) 
references categoryinfo(categoryId) on  delete  cascade);


3、删除外键:

1
alter table productifo drop constraint fk_product;


13:自连接


自连接是指在同一张表上的连接查询

1
2
select worker.ename,boss.ename from emp worker,emp boss 
where worker.mgr=boss.empno;


14:其他常用命令


1、解锁用户

1
alter user scott account unlock;


2、用查询结果来创建新表【常用于对表做备份操作】

1
create table emp_bak as select * from emp;




结尾:


     感谢阅读,祝有收获的一天!






      本文转自1清风揽月1  51CTO博客,原文链接:http://blog.51cto.com/watchmen/1926416
,如需转载请自行联系原作者



相关文章
|
2月前
|
Oracle 关系型数据库 分布式数据库
PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
1月前
|
SQL Oracle 关系型数据库
探索 Linux 命令 `db_archive`:Oracle 数据库归档日志的工具
探索 Linux 中的 `db_archive`,实际与 Oracle 数据库归档日志管理相关。在 Oracle 中,归档日志用于恢复,当在线重做日志满时自动归档。管理员可使用 SQL*Plus 查看归档模式,通过 `RMAN` 进行备份和恢复操作。管理归档日志需谨慎,避免数据丢失。了解归档管理对 Oracle 管理员至关重要,确保故障时能快速恢复数据库。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SET命令:你的数据库会话“调色板”
【4月更文挑战第19天】Oracle SQL*Plus的SET命令是数据库会话的“调色板”,用于设置输出格式、反馈信息和各种偏好。它能调整PAGESIZE和LINESIZE以优化显示,控制ECHO和FEEDBACK开关以定制反馈,以及统计命令执行时间(TIMING)和调试SQL(VERIFY)。更高级的选项如HEADING和COLSEP可改善输出的可读性。通过灵活运用SET命令,能提升工作效率和体验,是数据库管理员和开发者的必备工具。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的HELP命令:你的数据库“百事通”
【4月更文挑战第19天】`Oracle SQL*Plus` 的 `HELP` 命令是数据库查询的强大工具,犹如“百事通”。在遇到困惑时,`HELP` 可提供详细命令解释和用法示例,解答基础到高级的 SQL 和 PL/SQL 疑问。它还是“活字典”,揭示命令关联与区别,如 `SET` 和 `ALTER SESSION`。此外,`HELP` 解释数据库概念,如“事务”,并支持模糊查询。无论新手还是专家,`HELP` 都是数据库探索的得力助手。
|
2月前
|
SQL Oracle 关系型数据库
Oracle spool格式化数据命令
在这个示例中,通过设置不同的 `SET`命令参数,你可以控制输出的格式,包括每页行数、每行字符数、列分隔符等。你也可以使用其他的 `SET`命令参数来进一步定制输出格式。
31 0
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的TTITLE和BTITLE命令:为你的数据报告加上精美的“画框”
【4月更文挑战第19天】`SQL*Plus`的`TTITLE`和`BTITLE`命令用于为数据报告添加吸引人的标题和边框。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的COLUMN命令:数据展示的“化妆师”
【4月更文挑战第19天】Oracle SQL*Plus的COLUMN命令是数据展示的利器,能美化和格式化输出。它可定制列标题、调整显示格式(如数字的小数位数和日期格式),添加前缀和后缀(如货币符号),以及控制列宽和是否折行,使得数据呈现更直观、专业。利用COLUMN命令,能将原始数据转化为易于理解和视觉吸引力强的展示形式。
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的SPOOL命令:数据库世界的“录像机”
【4月更文挑战第19天】`SQL*Plus`的`SPOOL`命令是Oracle数据库中的“录像机”,能记录所有操作和输出。它在用户开始“SPOOL ON”时启动,记录SQL查询、输出、错误信息等。完成后,“SPOOL OFF”停止记录并生成日志文件,便于回顾和检查。日志文件可自定义保存位置和命名,支持多文件录制,方便分类管理。无论数据分析、SQL脚本编写还是日常维护,`SPOOL`都是强大的工具,值得一试!
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL*Plus的DESCRIBE命令:数据结构的“侦探”
【4月更文挑战第19天】`DESCRIBE`命令是Oracle SQL*Plus中的明星工具,用于快速揭示数据结构。它能显示表或视图的列名、数据类型、长度和空值限制,帮助用户理解数据库对象详情。此命令对数据库设计、查询优化和故障排除至关重要,且支持描述视图和同义词。通过有效利用DESCRIBE提供的信息,可以提升SQL查询效率,优化表结构,成为数据管理的得力助手。
|
10月前
|
SQL Oracle 关系型数据库
Oracle杂谈二 SQL*PLUS命令的使用大全
Oracle杂谈二 SQL*PLUS命令的使用大全
41 0