Oracle 技能强化 Part 1 单表查询

最新推荐文章于 2023-12-22 11:50:11 发布

数据架构

最新推荐文章于 2023-12-22 11:50:11 发布

阅读量395

点赞数

分类专栏： Oracle Administration 文章标签： oracle 数据库 database

本文链接：https://blog.csdn.net/u011868279/article/details/122471854

版权

Oracle Administration 专栏收录该内容

66 篇文章

订阅专栏

1、查询表中所有的行与列

(1). 查询之前我们先看一下表结构

SQL> desc emp;
Name Null? Type
----------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>

(2) 使用select * 就可以返回目标表中的所有的列。如下所示：

SQL> set line 200
SQL> select * from emp;

2、从表中检索部分行

SQL> select * from emp where deptno = 10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL>

3、查找满足多个条件的行

SQL> select * from emp where deptno = 10 or comm is not null or (sal <= 2000 and deptno = 20);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

9 rows selected.

SQL>

4、从表中检索部分列

SQL> select ename,deptno,sal from emp;

ENAME DEPTNO SAL
---------- ---------- ----------
SMITH 20 800
ALLEN 30 1600
WARD 30 1250
JONES 20 2975
MARTIN 30 1250
BLAKE 30 2850
CLARK 10 2450
SCOTT 20 3000
KING 10 5000
TURNER 30 1500
ADAMS 20 1100
JAMES 30 950
FORD 20 3000
MILLER 10 1300

14 rows selected.

SQL>

5、为列取有意义的名称

SQL> select ename as 姓名，deptno as 部门编号, sal as 工资,comm as 提成 from emp;

姓名部门编号工资提成
---------- ---------- ---------- ----------
SMITH 20 800
ALLEN 30 1600 300
WARD 30 1250 500
JONES 20 2975
MARTIN 30 1250 1400
BLAKE 30 2850
CLARK 10 2450
SCOTT 20 3000
KING 10 5000
TURNER 30 1500 0
ADAMS 20 1100
JAMES 30 950
FORD 20 3000
MILLER 10 1300

14 rows selected.

SQL>

6、在where子句中引用取别名的列

SQL> SELECT * FROM (SELECT SAL AS 工资,comm AS 提成 FROM emp) x where 工资 < 5000;

工资提成
---------- ----------
800
1600 300
1250 500
2975
1250 1400
2850
2450
3000
1500 0
1100
950
3000
1300

13 rows selected.

SQL>

7、拼接列

SQL> select ename || ' 的职位是 ' || job as msg from emp where deptno = 10;

MSG
---------------------------------
CLARK 的职位是 MANAGER
KING 的职位是 PRESIDENT
MILLER 的职位是 CLERK

SQL> select 'TRUNCATE TABLE ' || owner || '.' || table_name || ';' as 清空表 FROM ALL_TABLES WHERE OWNER = 'SYSTEM';

清空表
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE SYSTEM.LOGMNR_SESSION_EVOLVE$;
TRUNCATE TABLE SYSTEM.LOGMNR_GLOBAL$;
TRUNCATE TABLE SYSTEM.LOGMNR_PDB_INFO$;
TRUNCATE TABLE SYSTEM.LOGMNR_DID$;
TRUNCATE TABLE SYSTEM.LOGMNR_UID$;

138 rows selected.

SQL>

8、在SELECT语句中使用条件逻辑,

当用条件逻辑判断、以及行专列时候，多数会用到case when 语句。

SQL> SELECT ename,
2 sal,
3 CASE
4 WHEN sal <= 2000 THEN
'过低'
6 WHEN sal >= 4000 THEN
'过高'
8 ELSE
9 'OK'
10 END AS status
11 FROM emp;

ENAME SAL STATUS
---------- ---------- ------
SMITH 800 过低
ALLEN 1600 过低
WARD 1250 过低
JONES 2975 OK
MARTIN 1250 过低
BLAKE 2850 OK
CLARK 2450 OK
SCOTT 3000 OK
KING 5000 过高
TURNER 1500 过低
ADAMS 1100 过低
JAMES 950 过低
FORD 3000 OK
MILLER 1300 过低

14 rows selected.

SQL>

9、限制返回的行数

通过使用伪劣rownum进行行数返回值的限定。

SQL> select * from emp where rownum <= 2;

SQL>

10、从表中随机返回n条记录

提醒一个小知识： oracle分页语句需要先排序后分页。另外可以用dbms_random来对数据随机排序，然后取其中几行。

SQL> select *

SQL> select *
2 from (select ename,job from emp order by dbms_random.value())
3 where rownum <= 5;

ENAME JOB
---------- ---------
BLAKE MANAGER
MARTIN SALESMAN
ADAMS CLERK
TURNER SALESMAN
ALLEN SALESMAN

SQL>

SQL> select *
2 from (select ename,job from emp order by dbms_random.value())
3 where rownum <= 5;

ENAME JOB
---------- ---------
BLAKE MANAGER
MARTIN SALESMAN
ADAMS CLERK
TURNER SALESMAN
ALLEN SALESMAN

SQL>

2 from (select ename,job from emp order by dbms_random.value())
3 where rownum <= 5;

ENAME JOB
---------- ---------
BLAKE MANAGER
MARTIN SALESMAN
ADAMS CLERK
TURNER SALESMAN
ALLEN SALESMAN

SQL>

11、查找空值

NULL 不支持加、减、乘、除、大小比较、相等比较，

12、将空值转换为实际值，nvl比coalesce更好用，返回多个值中第一个不为空的值。

SQL> select coalesce(comm,0) from emp;

SQL> select coalesce(NULL,NULL,NULL,1,NULL,NULL,2) AS V FROM dual;

V
----------
1

SQL>

13、模糊查询

SQL> CREATE OR REPLACE VIEW v as
2 SELECT 'ABCEDF' AS vname from dual
3 union all
4 SELECT '_BCEFG' AS vname from dual
5 union all
6 SELECT '_BCEDF' AS vname from dual
7 union all
8 SELECT '_\BCEDF' AS vname from dual
9 union all
10 SELECT 'XYCEG' AS vname From dual;

View created.

SQL> SELECT * FROM V WHERE vname LIKE '%CED%';

VNAME
-------
ABCEDF
_BCEDF
_\BCEDF

SQL> SELECT * FROM V WHERE vname Like '_BCE%';

VNAME
-------
ABCEDF
_BCEFG
_BCEDF

SQL>

% : 替代一个或多个字符

—：仅替代一个字符

原来“_”被当作通配符了，怎么办呢？莫急，用转义字符

SQL> SELECT * FROM v WHERE vname LIKE '\_BCE%' ESCAPE '\';

VNAME
-------
_BCEFG
_BCEDF

SQL>