Oracle检索数据
一、Oracle用户模式与模式 对象
1.概念
模式就是数据库对象的集合，数据库对象包括表、函数、索引、视图、过程。
2.示例模式scott
SQL> select table_name from user_tables;TABLE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
二、简单查询
1.查询所有列
1.1 查询单个表中的所有列
查询当前用户模式下的表
SQL> conn scott/tiger
已连接。
SQL> select * from dept;DEPTNO DNAME LOC
---------- ---------------------------- --------------------------10 ACCOUNTING NEW YORK40 OPERATIONS BOSTON20 RESEARCH DALLAS30 SALES CHICAGO
查询其他模式下的表，需要使用模式名.表名指定
SQL> conn system/123456
已连接。
SQL> select * from emp;
select * from emp*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> select * from scott.emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-12月-80 800 207499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 307521 WARD SALESMAN 7698 22-2月 -81 1250 500 307566 JONES MANAGER 7839 02-4月 -81 2975 207654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 307698 BLAKE MANAGER 7839 01-5月 -81 2850 307782 CLARK MANAGER 7839 09-6月 -81 2450 107788 SCOTT ANALYST 7566 13-7月 -87 3000 207839 KING PRESIDENT 17-11月-81 5000 107844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30已选择 10 行。1.2 查询多个表中的所有列
表名间使用,隔开
SQL> select * from dept,salgrade;DEPTNO DNAME LOC GRADE LOSAL HISAL
---------- ---------------------------- -------------------------- ---------- ---------- ----------10 ACCOUNTING NEW YORK 2 1201 140010 ACCOUNTING NEW YORK 3 1401 200010 ACCOUNTING NEW YORK 4 2001 300040 OPERATIONS BOSTON 2 1201 140040 OPERATIONS BOSTON 3 1401 200040 OPERATIONS BOSTON 4 2001 300020 RESEARCH DALLAS 2 1201 140020 RESEARCH DALLAS 3 1401 200020 RESEARCH DALLAS 4 2001 300030 SALES CHICAGO 2 1201 140030 SALES CHICAGO 3 1401 200030 SALES CHICAGO 4 2001 3000已选择 12 行。
2.查询特定列
SQL> select losal,grade from salgrade;LOSAL GRADE
---------- ----------1201 21401 32001 4
--注：忘记表中有哪些字段，可以使用desc查询伪列
概念：伪列是一种数据类型，用于唯一标识一条记录，是基于64位编码的18个字符。不是真实存在于数据表中的列，所以被称为伪列，可以查询 但不能增删改。
作用：
①以最快的方式访问表中的一行
②能显示表的行是如何存储的
③作为表中唯一标识
常用的伪列：
rowid，数据库中每一行都有一个行地址，rowid伪列返回该行地址。可以使用rowid值来定位表中的一行。通常情况下，rowid值可以唯一标识数据库的一行。
rownum，查询结果返回的每一行，对应都有一个rownum伪列数值代表的次序。比如返回结果的第一行对应的rownum值为1，第二行为2.通过使用rownum伪列，可以限制查询返回的行数。
SQL> select rowid,rownum from emp;ROWID ROWNUM
------------------ ----------
AAAR2NAAHAAAAFrAAA 1
AAAR2NAAHAAAAFrAAB 2
AAAR2NAAHAAAAFrAAC 3
AAAR2NAAHAAAAFrAAD 4
AAAR2NAAHAAAAFrAAE 5
AAAR2NAAHAAAAFrAAF 6
AAAR2NAAHAAAAFrAAG 7
AAAR2NAAHAAAAFrAAH 8
AAAR2NAAHAAAAFrAAI 9
AAAR2NAAHAAAAFrAAJ 10已选择 10 行。SQL> desc emp名称是否为空? 类型----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------EMPNONOT NULL NUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(9)MGRNUMBER(4)HIREDATEDATESALNUMBER(7,2)COMMNUMBER(7,2)DEPTNONUMBER(2)
SQL> select * from emp where rownum<=5;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-12月-80 800 207499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 307521 WARD SALESMAN 7698 22-2月 -81 1250 500 307566 JONES MANAGER 7839 02-4月 -81 2975 207654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
3.查询日期列
日期列有多种显示格式：简体中文、美国英语、
3.1 以简体中文显示日期结果
=>=>通过设置会话的nls_date_language参数来实现
3.2 以美国英语显示日期结果
=>通过设置会话的nls_date_language参数来实现
SQL> alter session set nls_date_language='AMERICAN';会话已更改。
SQL> select * from scott.emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 13-JUL-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30已选择 10 行。
3.3 以特定格式显示日期结果
比如xxxx年xx月xx日，xxxx-xx-xx等
=>通过设置会话的nls_date_format参数来实现
①格式化为xxxx年xx月xx日
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY''年''MM''月''DD''日';②格式化为xxxx-xx-xx
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY''-''MM''-''DD''-';会话已更改。
3.4 to_char()自定义日期格式
语法：TO_CHAR(日期|数字|列，转换格式)
eg：使用to_char()将系统日期转换为YYYY-MM-DD格式
SQL> select to_char(sysdate,'YYYY-MM-DD') 转换后日期 from dual;转换后日期
--------------------
2025-03-14
4.排除重复列
distinct是通过排序来实现去重的，因此避免在数据量大时使用distinct
SQL> select job from emp;JOB
------------------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN已选择 10 行。SQL> select distinct job from emp;JOB
------------------
CLERK
SALESMAN
ANALYST
MANAGER
PRESIDENT
5.带有表达式的SELECT子句
select语句中可以使用算术运算符，优先级和一般的计算一样。
SQL> select ename,sal,sal*(1+0.1) 涨薪 from emp;ENAME SAL 涨薪
-------------------- ---------- ----------
SMITH 800 880
ALLEN 1600 1760
WARD 1250 1375
JONES 2975 3272.5
MARTIN 1250 1375
BLAKE 2850 3135
CLARK 2450 2695
SCOTT 3000 3300
KING 5000 5500
TURNER 1500 1650已选择 10 行。
6.处理NULL
使用NVL函数处理null
NVL（参数，value）表示如果参数为空，则返回value，参数和value需要是相同数据类型
使用NVL2函数处理null
NVL2（参数，value1，value2）表示如果参数为空，则返回value2，不为空则返回value2
SQL> select nvl(comm,3500) from emp;NVL(COMM,3500)
--------------35003005003500140035003500350035000已选择 10 行。SQL> select nvl2(comm,comm,3500) from emp;NVL2(COMM,COMM,3500)
--------------------35003005003500140035003500350035000已选择 10 行。
8.连接字符串
使用||操作符连接字符串
使用函数CONCAT连接字符串
SQL> select 'hello'||'im addie' from dual;'HELLO'||'IMADDIE'
--------------------------
helloim addieSQL> select concat('addie','hello,im alley') from dual;CONCAT('ADDIE','HELLO,IMALLEY')
--------------------------------------
addiehello,im alley