oracle基本查询用法入门示例_Oracle

本文实例讲述了oracle基本查询用法。分享给大家供大家参考，具体如下：

一、基本select语句

				?

									SELECT *|{[DISTINCT] column|expression [alias], ...}

									FROM table;

例如：

				?

									--查询所有数据

									select * from emp;

									--查询指定列数据

									select empno,ename,sal from emp;

									--算数运算符(+ - * /)

									select ename,sal,sal+30 from emp;

									--使用括号

									select ename,sal,12*(sal+30) from emp;

									--定义空值

									--(空值是无效的，未指定，未知的或不可预知的值，空值不是空格或是0)

									select ename,job,sal,comm from emp;

									--空值的数学运算

									--包含空值的数学表达式的值都为空值

									select ename,12*sal+comm from emp;

									--列的别名

									--别名使用双引号，AS可以省略

									select deptno as "no",ename as "name" from emp;

									--连接符,把列与列，列与字符连接在一起

									select deptno || '--' || ename from emp;

									--字符串

									--日期和字符只能在单引号中出现

									select 'hello ' || ename from emp;

									--删除重复行

									select distinct deptno from emp;

									--显示表结构

									desc[ribe] tablename;

二、过滤和排序

				?

									SELECT *|{[DISTINCT] column|expression [alias], ...}

									FROM table

									[WHERE condition(s)];

例如：

				?

									--查询指定条件数据

									select deptno,ename from emp where deptno=10;

									--字符串和日期包含在单引号中

									--字符串大小写敏感，日期格式敏感

									select ename,job,deptno from emp where ename='King';

									--比较运算符(= > < <= >= <> !=)

									select ename,sal from emp where sal<1500;

									--其他比较运算符

									--BETWEEN ... AND ... 在两个值之间包含边界

									--IN(set) 等于值列表中的一个

									--LIKE 模糊查询

									--IS NULL 空值

									select ename,sal,deptno from emp where deptno in(10,30);

									select ename,sal,comm from emp where comm is null;

									--逻辑运算(AND OR NOT)

									select ename,sal from emp where deptno=10 and sal>1500;

排序

				?

									ORDER BY 字段 [DESC|ASC]

例如：

				?

									select ename,sal from emp order by sal desc;

									--多列排序

									--先按第一列排序，如果相同，则按第二列排序，以此类推

									select * from emp order by sal desc,hiredate desc;

三、单行函数

1、字符函数

				?

									--LOWER 转换小写

									--UPPER 转换大写

									--INITCAP 首字母大写

									select lower(ename) from emp;

									--CONCAT 接接字符串

									--SUBSTR 截取字符串

									--LENGTH 字符串长度

									--INSTR 查找字符串

									--LPAD 左边填充字符

									--RPAD 右边填充字符

									--TRIM([leading|trailing|both] 字符串1 from 字符串2) 

									--TRIM可以删除两边空格，也可删除其他字符

									--REPLACE 替换字符串

									select concat('aa','bb') from emp;

									select substr('abcdefg', 2, 3) from emp;

									select length('test...') from emp;

									select instr('hello world', 'w') from emp;

									select lpad(sal, '10', '0') from emp;

									select rpad(sal, '10', '*') from emp;

									select trim(' test ') from emp;

									--从尾部删除字符串*号

									select trim(trailing '*' from '**1212121**') from emp;

									--把字符串中的22替换成88

									select replace('11223344', '22', '88') from emp;

2、数字函数

				?

									--ROUND 四舍五入

									--TRUNC 截断

									--MOD 求余

									select round(25.533,2) from dual;

									select trunc(25.323,2) from dual;

									select mod(8, 3) from dual;

3、日期

oracle中日期型数据实际含有两个值：日期和时间。

默认格式为：DD-MON-RR

				?

									--返回系统时间

									select sysdate from dual;

									--两个日期相减，返回日期之间相差的天数

									select ename,(sysdate-hiredate) / 7 "weeks" from emp;

									--MONTHS_BETWEEN 两日期相差月数

									--ADD_MONTHS 指定日期加上若干月数

									--NEXT_DAY 指定日期的下一个日期

									--LAST_DAY 本月的最后一天

									--ROUND 日期四舍五入

									--TRUNC 日期截断

									select months_between(sysdate,hiredate) from emp;

4、显式数据类型转换

				?

									--TO_CHAR(date, 'format_model')

									--把日期转换成字符串

									select to_char(sysdate, 'YYYY MM DD HH:MI:SS') from dual;

									--TO_CHAR(number, 'format_model')

									select ename,sal,to_char(sal, '$99,999.00') from emp;

									--TO_NUMBER(char[,'format_model'])

									--TO_DATE(char[,'format_model'])

通用函数，适用于任何数据类型，也适用于空值

				?

									NVL(expr1,expr2)

									NVL2(expr1,expr2,expr3)

									NULLIF(expr1,expr2)

									COALESCE(expr1,expr2,...)

5、条件表达式

在sql语句中使用if-then-else逻辑
case表达式，sql99语法，类似basic，比较繁锁
decode函数，oracle自已语法，类似java，比较简洁

				?

									CASE expr WHEN comparison_expr1 THEN return_expr1

									     [WHEN comparison_expr2 THEN return_expr2

									      WHEN comparison_expr3 THEN return_expr3

									      ELSE else_expr]

									END

				?

									DECODE(col|expression, search1, result1

									           [,search2, result2, ..., ]

									           [,defautl]

									)

				?

									select ename,sal,decode(round(sal/1000),

									1, '一倍'，

									2, '二倍'，

									3, '三倍',

									'不知倍数'

									) from emp;

6、函数嵌套

单行函数可以嵌套，嵌套函数的执行是由内到外。

四、分组函数

分组函数作用于一组数据，并对一组数据返回一个值。
常用组函数

				?

									--AVG

									--COUNT

									--MAX

									--MIN

									--SUM

									select sum(sal) as "total" from emp;

									select max(sal) from emp;

									select count(*) from emp where deptno=10;

									select count(distinct deptno) from emp;

分组数据
通过GROUP BY可以将数据分成若干组

				?

									select deptno,avg(sal) from emp group by deptno;

(*注意：在select列表中所有未包含在组函数中的列都必须包含在group by中。)

				?

									--多列分组

									select deptno,job,avg(sal) from emp group by deptno,job;

过滤分组
通过HAVING子句对分组进行过滤

				?

									select deptno,avg(sal) from emp group by deptno having deptno in(10,20);

(*注意：不能在where子句中使用组函数，having子句中可以。)
嵌套组函数

				?

									select max(avg(sal)) from emp group by deptno;

五、多表查询

oracle的连接 sql99的连接
等值连接        cross joins
不等值连接     natural joins
外连接           using clause
自连接         full or two sided outer joins

				?

									SELECT table1.column, table2.column

									FROM table1,table2

									WHERE table1.column1=table2.column2;

				?

									--等值连接

									select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno;

									--多连接条件和and操作符

									select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno and e.deptno=10;

表的别名
1、使用表别名可简化查询
2、使用表名前缀可以提高执行效率
3、如果使用了表的别名，则不能再使用表的真名。

				?

									--不等值连接

									--查询员工部门名称和工资等级

									select d.dname, e.ename, e.sal, s.grade

									from emp e, dept d, salgrade s

									where e.deptno = d.deptno

									and e.sal >= s.losal

									and e.sal <= s.hisal;

外连接语法
外连接查询可以查询不满足连接条件的数据。
外连接的符号是(+)

				?

									SELECT table1.column, table2.column

									FROM table1, table2

									WHERE table1.column(+) = table2.column;

									SELECT table1.column, table2.column

									FROM table1, table2

									WHERE table1.column= table2.column(+) ;

				?

									--查询部门人数

									select d.dname, d.deptno, count(e.empno)

									 from emp e, dept d

									 where e.deptno(+) = d.deptno

									 group by d.deptno,d.dname;

自连接

				?

									--查询员工的上级

									select e.ename as "员工", e2.ename as "上级"

									 from emp e, emp e2

									 where e.empno = e2.mgr;

使用sql:1999语法连接

				?

									SELECT table1.column, table2.column

									FROM table1

									[CROSS JOIN table2] |

									[NATURAL JOIN table2] |

									[JOIN table2 USING (column_name)] |

									[JOIN table2 ON(table1.column_name=table2.column_name)] |

									[LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];

使用cross join连接的表产生叉集，叉集和笛卡尔集是相同的。

				?

									select e.ename,d.dname from emp e cross join dept d;

使作natural join自然连接，会以两个表中具有相同名字的列为条件创建等值连接。

				?

									select e.ename,d.dname from emp e natural join dept d;

使用using创建连接，用natural join创建等值连接时，可以使用using指定等值连接中需要用到的列。

				?

									select e.ename,d.dname from emp e join dept d USING (deptno);

使用on创建连接，可以指定额外的连接条件。

				?

									select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

使用on创建多表连接

				?

									select e.ename, d.dname, e2.ename

									from emp e

									join dept d

									on e.deptno = d.deptno

									join emp e2

									on e.empno = e2.mgr;

内连接和外连接
在sql:1999中，内连接只返回满足连接条件的数据。
两个表在连接过程中除了返回满足连接条件的行以外，还返回左(右)表中不满足条件的行，这种称为左(右)外连接。
两个表在连接过程中除了返加满足连接条件的行以外，还返回两个表中不满足条件的行，这种连接称为满外连接。

				?

									--左外连接

									select e.ename, d.dname

									from emp e

									left outer join dept d

									on e.deptno = d.deptno;

				?

									--右外连接，返回右表中不满足条件的行

									select e.ename, d.dname

									from emp e

									right outer join dept d

									on e.deptno = d.deptno;

				?

									--满外连接

									select e.ename, d.dname

									from emp e

									full outer join dept d

									on e.deptno = d.deptno;

希望本文所述对大家Oracle数据库程序设计有所帮助。

原文链接：https://www.cnblogs.com/jkko123/p/6294566.html

oracle基本查询用法入门示例

延伸 · 阅读

[Oracle] dbms_metadata.get_ddl 的使用方法总结

oracle 动态AdvStringGrid完美示例 (AdvStringGrid使用技巧/Cells)

oracle中利用关键字rownum查询前20名员工信息及rownum用法

Linux中Oracle数据库备份

oracle使用instr或like方法判断是否包含字符串

Oracle数据库由dataguard备库引起的log file sync等待问题

oracle 优化的一点体会

Oracle查看表结构命令详解

云服务器是什么，云主机干什么用的？

2020最新好用的web服务器软件推荐

服务器操作系统有哪些?

web服务器配置（图文详解）

企业如何选择阿里云服务器配置?

五大免费主机管理系统优缺点对比及推荐

2019最新三款Windows下连接Linux的ssh软件下载推荐

服务器常用管理软件盘点

Nginx服务器究竟是怎么执行PHP项目