oracle数据库基础知识

最新推荐文章于 2019-06-25 22:33:18 发布

avj65874

最新推荐文章于 2019-06-25 22:33:18 发布

阅读量226

点赞数

文章标签：数据库操作系统运维

原文链接：http://www.cnblogs.com/zhabayi/p/5316170.html

版权

SQL*PLUS
SQL*PLUS简介

　　(或称sqlplus)里除了我们经常使用的sql语句外，还有另外一些命令，它们通过对标题、列标题、页宽、页长以及时间等参数的格式化操作来控制sql语句的输出的内容和格式，常见的比如"set timing on”,"set heading off”等。

基本的SqlPlus命令

remark 注释,set headsep 标题换行,ttitle 设置输出页的头标题,btitle 设置输出页的尾标题,column 对sql语句中的列进行格式化处理,break on 通知sqlplus在输出结果中插入空格,compute sum 通知sqlplus计算小计,set linesize 设置sqlplus输出的最大行宽,set pagesize 设置页面的最大行数,set newpage 设置页面之间的空行数,spool sqlplus屏幕的文件输入输出命令,/**/ 注释，同remark,-- 双杠，注释，同remark,set pause sqlplus屏幕输出结果时在页面之间停顿,save 保存当前session最近的sql语句至指定的文件中,host 返回到操作系统环境，类似!,start或@,执行文件中的命令,edit 使用自定义的编辑器编辑指定文件,define_editor 自定义sqlplus里的编辑器,exit或quit 退出sqlplus

操作系统和sqlplus命名行切换：Sqlplus usename/password

　　SQL>! --输入’!’号表示切换到操作系统

　　[oracle@webhost]exit --输入exit表示切换到sqlplus命令行

　　SQL>spool /home/oreacle/tem/tem.txt --表示下面操作输出到文件tem.txt

　　SQL>spool off --关闭输出定向

命令实例举证

1. 执行一个sql脚本（test.sql）：start g:/ks.sql或者@g:/ks.sql，重新运行上一次命令输入’/’。
set pagesize n: 分页参数，最大值可以为50000。0表示不分页。
set feedback off: 不显示本次sql命令处理的记录条数。
set echo off 则只显示命令执行的结果，而不显示出命令本身。
set termout off 显示脚本中的命令的执行结果，缺省为on
set colsep '| '; 域输出分隔符(每个字段分隔符以’|’分隔)。
create syno NOTICE_PUBLISH for fund.NOTICE_PUBLISH;--创建同义词
drop synonym NOTICE_PUBLISH; --删除同义词
desc accmsg.NOTICE_PUBLISH--查看表结构
show user--显示当前用户

Ø sql查询效率

按索引查询：例如索引name为：IDX_TB_INT_TXN_LOG

select /*+ index(a IDX_TB_INT_TXN_LOG) */ * from cpab.tb_int_txn_log_hist where 条件。

左连接和右连接：“（+）”链接符在哪个表的字段上就链接哪个表。例如有a和b两张表。

Setlect * from a,b where a.column1 = b. column1(+).表示链接b表

Setlect * from a,b where a.column1(+) = b. column1(+).表示链接a表

from table where tran_dt between ‘20151209’ and ‘20151211’;(包括20151209和20151211这两天的数据)

Ø 格式转换相关语句to

1. 日期格式转换：to_date(‘20151116182211’,’yyyymmddhh24mis’).to_char(sysdate,’ yyyymmddhh24mis’).
2. 将金额tran_at设置为15位长度和两位小数点,不足15用空格补齐

Select lpad(to_char(tran_at,’fm99999999990.00’),15,’ ’) from tb_table;

3. 将字符串转换为数字：前提条件字符串不能包含数字以外的其他字符：to_number(str_cloum)
4.

Ø 查询排序和分组: group by、order by、having，intersect

升序排列：Order by tran_dt，降序排列：Order by tran_dt desc;
select tran_dt,tran_cd,count(!),sum(tran_at) from table where tran_dt > ‘20151201’ group by tran_dt having count(1) > 1 and sum(tran_at) > 100;
intersect: 取交集

Ø 查询筛选:decode,case

decode(tran_dt,’811015’,’银行缴费’,’831011’,’电力扣费’,’其他’);
case when tran_cd = ‘811015’ then ‘银行缴费’ else ‘其他’

Ø ORACLE使用函数对二进制、十进制、十六进制数互相转换

十进制转换为十六进制：select to_char(53435,'XXXXXX') from dual;
十六进制转换成十进制：select to_number('D0BB','xxxxxx') from dual;
将ASCII码（十进制的）转换成汉字：select chr(53435) from dual;
将汉字转换为十进制的ASCII码：select ascii('谢') from dual;

表空间信息查询

--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本　
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;

--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
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