音效素材网提供各类素材，打造精品素材网站！

站内导航 站长工具 投稿中心 手机访问

音效素材

热门搜索： 狗叫声 打雷 钟声 wordpress模板 图标素材
网页制作|编程语言|产品运营|平面设计|操作系统|数据库|服务器|
当前位置：首页 > 教程 > 数据库 > oracle > 45个非常有用的 Oracle 查询语句小结

45个非常有用的 Oracle 查询语句小结
日期：2014-04-23 18:19:24   来源：脚本之家

日期/时间 相关查询

1.获取当前月份的第一天

运行这个命令能快速返回当前月份的第一天。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
    FROM DUAL;

2.获取当前月份的最后一天

这个查询语句类似于上面那个语句，而且充分照顾到了闰年，所以当二月份有 29 号，那么就会返回 29/2 。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
    FROM DUAL;


3.获取当前年份的第一天
每年的第一天都是1 月1日，这个查询语句可以使用在存储过程中，需要对当前年份第一天做一些计算的时候。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:

SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

4.获取当前年份的最后一天

类似于上面的查询语句。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

5.获取当前月份的天数

这个语句非常有用，可以计算出当前月份的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days  FROM DUAL;

6.获取当前月份剩下的天数
下面的语句用来计算当前月份剩下的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:

 SELECT SYSDATE, 
        LAST_DAY (SYSDATE) "Last", 
        LAST_DAY (SYSDATE) - SYSDATE "Days left"
   FROM DUAL;

7.获取两个日期之间的天数
使用这个语句来获取两个不同日期自检的天数。

复制代码 代码如下:

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0) num_of_days FROM DUAL; 
OR
SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

如果你需要查询一些特定日期的天数，可以使用第二个查询语句。这个例子是计算员工入职的天数。

8.显示当前年份截止到上个月每个月份开始和结束的日期

这个是个很聪明的查询语句，用来显示当前年份每个月的开始和结束的日期，你可以使用这个进行一些类型的计算。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。

复制代码 代码如下:

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
       TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
  FROM XMLTABLE (
          'for $i in 0 to xs:int(D) return $i'
          PASSING XMLELEMENT (
                     d,
                     FLOOR (
                        MONTHS_BETWEEN (
                           ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
                           SYSDATE)))
          COLUMNS i INTEGER PATH '.');

9.获取直到目前为止今天过去的秒数（从 00：00 开始算）

复制代码 代码如下:
SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning FROM DUAL;

10.获取今天剩下的秒数（直到 23：59：59 结束）

复制代码 代码如下:
SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left FROM DUAL;

数据字典查询

11.检查在当前数据库模式下是否存在指定的表

这是一个简单的查询语句，用来检查当前数据库是否有你想要创建的表，允许你重新运行创建表脚本，这个也可以检查当前用户是否已经创建了指定的表（根据这个查询语句在什么环境下运行来查询）。

复制代码 代码如下:

SELECT table_name
  FROM user_tables
 WHERE table_name = 'TABLE_NAME';

12.检查在当前表中是否存在指定的列

这是个简单的查询语句来检查表里是否有指定的列，在你尝试使用 ALTER TABLE 来添加新的列新到表中的时候非常有用，它会提示你是否已经存在这个列。

复制代码 代码如下:

SELECT column_name AS FOUND
  FROM user_tab_cols
 WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

13.显示表结构

这 个查询语句会显示任何表的 DDL 状态信息。请注意我们已经将‘TABLE'作为第一个信息提交了。这个查询语句也可以用来获取任何数据库对象的 DDL 状态信息。举例说明，只需要把第一个参数替换成‘VIEW'，第二个修改成视图的名字，就可以查询视图的 DDL 信息了。

复制代码 代码如下:

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

14.获取当前模式
这是另一个可以获得当前模式的名字的查询语句。

复制代码 代码如下:

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

15.修改当前模式
这是另一个可以修改当前模式的查询语句，当你希望你的脚本可以在指定的用户下运行的时候非常有用，而且这是非常安全的一个方式。

复制代码 代码如下:

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

数据库管理查询

16.数据库版本信息

返回 Oracle 数据库版本

复制代码 代码如下:

SELECT * FROM v$version;

17.数据库默认信息

返回一些系统默认的信息

复制代码 代码如下:

SELECT username,
       profile,
       default_tablespace,
       temporary_tablespace
  FROM dba_users;

18.数据库字符设置信息
显示数据库的字符设置信息

复制代码 代码如下:

SELECT * FROM nls_database_parameters;

19.获取 Oracle 版本

复制代码 代码如下:

SELECT VALUE
  FROM v$system_parameter
 WHERE name = 'compatible';

20.存储区分大小写的数据，但是索引不区分大小写

某些时候你可能想在数据库中查询一些独立的数据，可能会用 UPPER(..) = UPPER(..) 来进行不区分大小写的查询，所以就想让索引不区分大小写，不占用那么多的空间，这个语句恰好能解决你的需求 。

复制代码 代码如下:

CREATE TABLE tab (col1 VARCHAR2 (10));
CREATE INDEX idx1
   ON tab (UPPER (col1));
ANALYZE TABLE a COMPUTE STATISTICS;

21.调整没有添加数据文件的表空间

另一个 DDL 查询来调整表空间大小

复制代码 代码如下:

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

22.检查表空间的自动扩展开关

在给定的表空间中查询是否打开了自动扩展开关

复制代码 代码如下:

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;
(OR)
SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

23.在表空间添加数据文件

在表空间中添加数据文件

复制代码 代码如下:

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'
    SIZE 1000M AUTOEXTEND OFF;

24.增加数据文件的大小

给指定的表空间增加大小

复制代码 代码如下:

ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;

25.查询数据库的实际大小

给出以 GB 为单位的数据库的实际大小

复制代码 代码如下:

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

26.查询数据库中数据占用的大小或者是数据库使用细节
给出在数据库中数据占据的空间大小

复制代码 代码如下:

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

27.查询模式或者用户的大小
以 MB 为单位给出用户的空间大小

复制代码 代码如下:

SELECT SUM (bytes / 1024 / 1024) "size"
  FROM dba_segments
 WHERE owner = '&owner';

28.查询数据库中每个用户最后使用的 SQL 查询
此查询语句会显示当前数据库中每个用户最后使用的 SQL 语句。

复制代码 代码如下:

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
         s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
         s.sid || '/' || s.serial# sid,
         s.status "Status",
         p.spid,
         sql_text sqltext
    FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
   WHERE     t.address = s.sql_address
         AND p.addr = s.paddr(+)
         AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;

性能相关查询
29.查询用户 CPU 的使用率

这个语句是用来显示每个用户的 CPU 使用率，有助于用户理解数据库负载情况

复制代码 代码如下:

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

30.查询数据库长查询进展情况
显示运行中的长查询的进展情况

复制代码 代码如下:

SELECT a.sid,
         a.serial#,
         b.username,
         opname OPERATION,
         target OBJECT,
         TRUNC (elapsed_seconds, 5) "ET (s)",
         TO_CHAR (start_time, 'HH24:MI:SS') start_time,
         ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
    FROM v$session_longops a, v$session b
   WHERE     a.sid = b.sid
         AND b.username NOT IN ('SYS', 'SYSTEM')
         AND totalwork > 0
ORDER BY elapsed_seconds;

31.获取当前会话 ID，进程 ID，客户端 ID 等
这个专门提供给想使用进程 ID 和 会话 ID 做些 voodoo magic 的用户。

复制代码 代码如下:

SELECT b.sid,
       b.serial#,
       a.spid processid,
       b.process clientpid
  FROM v$process a, v$session b
 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

•V$SESSION.SID AND V$SESSION.SERIAL# 是数据库进程 ID
•V$PROCESS.SPID 是数据库服务器后台进程 ID
•V$SESSION.PROCESS 是客户端 PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

32.查询特定的模式或者表中执行的最后一个 SQL 语句

复制代码 代码如下:

SELECT CREATED, TIMESTAMP, last_ddl_time
  FROM all_objects
 WHERE     OWNER = 'MYSCHEMA'
       AND OBJECT_TYPE = 'TABLE'
       AND OBJECT_NAME = 'EMPLOYEE_TABLE';

33.查询每个执行读取的前十个 SQL

复制代码 代码如下:

SELECT *
  FROM (  SELECT ROWNUM,
                 SUBSTR (a.sql_text, 1, 200) sql_text,
                 TRUNC (
                    a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
                    reads_per_execution,
                 a.buffer_gets,
                 a.disk_reads,
                 a.executions,
                 a.sorts,
                 a.address
            FROM v$sqlarea a
        ORDER BY 3 DESC)
 WHERE ROWNUM < 10;

34.在视图中查询并显示实际的 Oracle 连接

复制代码 代码如下:

SELECT osuser,
         username,
         machine,
         program
    FROM v$session
ORDER BY osuser;

35.查询并显示通过打开连接程序打开连接的组

复制代码 代码如下:

SELECT program application, COUNT (program) Numero_Sesiones
    FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC;

36.查询并显示连接 Oracle 的用户和用户的会话数量

复制代码 代码如下:

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
    FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;

37.获取拥有者的对象数量

复制代码 代码如下:

SELECT owner, COUNT (owner) number_of_objects
    FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;

38.把数值转换成文字
更多信息可以查看： Converting number into words in Oracle

复制代码 代码如下:

SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

输出:

复制代码 代码如下:

one thousand five hundred twenty-six


39.在包的源代码中查询字符串

这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING' ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用。

复制代码 代码如下:

--search a string foo_something in package source code
SELECT *
  FROM dba_source
 WHERE UPPER (text) LIKE '%FOO_SOMETHING%'
AND owner = 'USER_NAME';

40.把用逗号分隔的数据插入的表中

当 你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 IN 或者是 NOT IN 。这里我们把‘AA,BB,CC,DD,EE,FF'转换成包含 AA,BB，CC 等作为一行的表，这样你就很容易把这些字符串插入到其他表中，并快速的做一些相关的操作。

复制代码 代码如下:

WITH csv
     AS (SELECT 'AA,BB,CC,DD,EE,FF'
                   AS csvdata
           FROM DUAL)
    SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
      FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

41.查询表中的最后一个记录

这个查询语句很直接，表中没有主键，或者是用户不确定记录最大主键是否是最新的那个记录时，就可以使用这个语句来查询表中最后一个记录。

复制代码 代码如下:

SELECT *
  FROM employees
 WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees
MINUS
SELECT *
  FROM employees
 WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

42.在 Oracle 中做行数据乘法

这个查询语句使用一些复杂的数学函数来做每个行的数值乘法。更多内容请查阅： Row Data Multiplication In Oracle

复制代码 代码如下:

WITH tbl
     AS (SELECT -2 num FROM DUAL
         UNION
         SELECT -3 num FROM DUAL
         UNION
         SELECT -4 num FROM DUAL),
     sign_val
     AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
           FROM tbl
          WHERE num < 0)
  SELECT EXP (SUM (LN (ABS (num)))) * val
    FROM tbl, sign_val
GROUP BY val;

43.在 Oracle 生成随机数据

每个开发者都想能轻松生成一堆随机数据来测试数据库多好，下面这条查询语句就可以满足你，它可以在 Oracle 中生成随机的数据插入到表中。详细信息可以查看 Random Data in Oracle

复制代码 代码如下:

SELECT LEVEL empl_id,
           MOD (ROWNUM, 50000) dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,
           TO_DATE (
                 ROUND (DBMS_RANDOM.VALUE (1, 28))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1, 12))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
              'DD-MM-YYYY')
              dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
      FROM DUAL
CONNECT BY LEVEL < 10000;

44.在 Oracle 中生成随机数值

这是 Oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。

复制代码 代码如下:

--generate random number between 0 and 100
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;


45.检查表中是否含有任何的数据

这个可以有很多中写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。

复制代码 代码如下:

SELECT 1
FROM TABLE_NAME
WHERE ROWNUM = 1;


如果你知道一些很好用的查询语句，可以减轻 Oracle 开发者的负担，那么在评论分享一下吧：）

    有用语句Oracle查询小结
    上一篇：oracle用户权限管理使用详解
    下一篇：Oracle 获得以百分号结尾的脚本有三种写法

    您感兴趣的教程

    在docker中安装mysql详解

    本篇文章主要介绍了在docker中安装mysql详解，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编...

    详解 安装 docker mysql

    win10中文输入法仅在桌面显示怎么办?

    win10中文输入法仅在桌面显示怎么办?

    win10系统使用搜狗，QQ输入法只有在显示桌面的时候才出来，在使用其他程序输入框里面却只能输入字母数字，win10中...

    win10 中文输入法

    一分钟掌握linux系统目录结构

    这篇文章主要介绍了linux系统目录结构，通过结构图和多张表格了解linux系统目录结构，感兴趣的小伙伴们可以参考一...

    结构 目录 系统 linux

    PHP程序员玩转Linux系列 Linux和Windows安装

    这篇文章主要为大家详细介绍了PHP程序员玩转Linux系列文章，Linux和Windows安装nginx教程，具有一定的参考价值，感兴趣...

    玩转 程序员 安装 系列 PHP

    win10怎么安装杜比音效Doby V4.1 win10安装杜

    第四代杜比®家庭影院®技术包含了一整套协同工作的技术，让PC 发出清晰的环绕声同时第四代杜比家庭影院技术...

    win10杜比音效

    纯CSS实现iOS风格打开关闭选择框功能

    这篇文章主要介绍了纯CSS实现iOS风格打开关闭选择框,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作...

    css ios c

    Win7如何给C盘扩容 Win7系统电脑C盘扩容的办法

    Win7如何给C盘扩容 Win7系统电脑C盘扩容的

    Win7给电脑C盘扩容的办法大家知道吗？当系统分区C盘空间不足时，就需要给它扩容了，如果不管，C盘没有足够的空间...

    Win7 C盘 扩容

    百度推广竞品词的投放策略

    SEM是基于关键词搜索的营销活动。作为推广人员，我们所做的工作，就是打理成千上万的关键词，关注它们的质量度...

    百度推广 竞品词

    Visual Studio Code(vscode) git的使用教程

    这篇文章主要介绍了详解Visual Studio Code(vscode) git的使用，小编觉得挺不错的，现在分享给大家，也给大家做个参考。...

    教程 Studio Visual Code git

    七牛云储存创始人分享七牛的创立故事与

    这篇文章主要介绍了七牛云储存创始人分享七牛的创立故事与对Go语言的应用,七牛选用Go语言这门新兴的编程语言进行...

    七牛 Go语言

    Win10预览版Mobile 10547即将发布 9月19日上午

    微软副总裁Gabriel Aul的Twitter透露了 Win10 Mobile预览版10536即将发布，他表示该版本已进入内部慢速版阶段，发布时间目...

    Win10 预览版

    HTML标签meta总结,HTML5 head meta 属性整理

    移动前端开发中添加一些webkit专属的HTML5头部标签，帮助浏览器更好解析HTML代码，更好地将移动web前端页面表现出来...

    移动端html5模拟长按事件的实现方法

    这篇文章主要介绍了移动端html5模拟长按事件的实现方法的相关资料,小编觉得挺不错的，现在分享给大家，也给大家...

    移动端 html5 长按

    HTML常用meta大全(推荐)

    这篇文章主要介绍了HTML常用meta大全(推荐)，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参...

    cdr怎么把图片转换成位图? cdr图片转换为位图的教程

    cdr怎么把图片转换成位图? cdr图片转换为

    cdr怎么把图片转换成位图？cdr中插入的图片想要转换成位图，该怎么转换呢？下面我们就来看看cdr图片转换为位图的...

    cdr 图片 位图

    win10系统怎么录屏?win10系统自带录屏详细教程

    win10系统怎么录屏?win10系统自带录屏详细

    当我们是使用win10系统的时候，想要录制电脑上的画面，这时候有人会想到下个第三方软件，其实可以用电脑上的自带...

    win10 系统自带录屏 详细教程

    + 更多教程 +
    精选教程
    教程标签
    到期 lsnrctl 九九 乘法表 引发 notfound 表里 补充 精确 如何将 truncatetabl 提高效率 忘记密码 从零开始 floor 端口号 侦听 最慢 次数最多 自动启动 插拔 高级 clob 中有 当天 发生了 经验教训 如何正确 大比拼 ER account BBED 隐式 多条 length 检查点 实际应用 带有 rpad 简述

    关于我们

    音效素材网整合国内外优质的资源，内容涉及js效果代码，平面设计教程，网页设计教程，cms建站，网页模版等领域，提供最新的行业资讯，设计教程和素材参考学习。

    版权声明

    声明：本站发布的资源均来自互联网，仅供交流学习之用，请勿作商业用途，所有资源版权归原作者所有。如果有侵犯到您的权益，请联系本站删除，谢谢合作！

    音效素材网(YinXiaoSuCai.CN)

    Copyright © 1999-2022 音效素材网 版权所有   京ICP备11032199号-4  关于我们 | 广告合作 | 版权声明 | 联系我们 | 友情链接 | RSS订阅 |
    MsSqlMysqloracleMariaDBSQLiteDB2