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相关内容感兴趣的读者可查看本站专题:《Oracle常用函数汇总》、《Oracle日期与时间操作技巧总结》及《php+Oracle数据库程序设计技巧总结》

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

(0)

相关推荐

  • 一个oracle+PHP的查询的例子

    http://qdlover.yeah.net   一个oracle+PHP的查询的例子   本来我不用php了,可是还是有很多朋友问我,我就做了一个例子,具体函数请查手册   其实oracle的函数和别的也差不多,只不过它这里多了一个定义输出变量的过程,   如果学过pl/sql的人都知道的(不过听说只有清华和西安交大用它做网站).   就是OCIDefineByName($id,"ROWNUM",&$rownum);对应了查询中的rownum,一个对应一个,   而且最应该

  • Oracle 11GR2的递归WITH子查询方法

    下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法,具体内容如下所示: SQL> with emp_data(ename,empno,mgr,l) as (select ename, empno, mgr, 1 lvl from emp where mgr is null union all select emp.ename, emp.empno, emp.mgr, ed.l+1 from emp, emp_data ed where emp.mgr = ed.empno ) S

  • Oracle基础学习之子查询

    首先使用子查询的时候注意事项包括,子查询可以嵌套多层和子查询需要圆括号()括起来,下面我们来看看详细的介绍. 基础介绍 1,wherer:子查询一般会返回单行单列 单行多列 多行单列 : 2,having:子查询会返回单行单列,同时表示要使用统计函数: 3,from:子查询返回多行多列数据(表结构): 4,select:返回单行单列 (一般不使用): 示例详解 where(进行数据行的筛选操作): a:查询出低于公司平均工资的雇员信息. select * from emp where sal<(

  • oracle常用sql查询语句部分集合(图文)

    Oracle查询语句 select * from scott.emp ; 1.--dense_rank()分析函数(查找每个部门工资最高前三名员工信息) select * from (select deptno,ename,sal,dense_rank() over(partition by deptno order by sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ; 结果: --rank()分析函数(

  • Oracle通过递归查询父子兄弟节点方法示例

    前言 说到Oracle中的递归查询语法,我觉得有一些数据库基础的童鞋应该都知道,做项目的时候应该也会用到,下面本文就来介绍下关于Oracle通过递归查询父子兄弟节点的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 方法如下: 1.查询某节点下所有后代节点(包括各级父节点) // 查询id为101的所有后代节点,包含101在内的各级父节点 select t.* from SYS_ORG t start with id = '101' connect by parent_

  • ORACLE查询删除重复记录三种方法

    比如现在有一人员表 (表名:peosons) 若想将姓名.身份证号.住址这三个字段完全相同的记录查询出来 复制代码 代码如下: select p1.*   from persons  p1,persons  p2   where p1.id<>p2.id   and  p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address 可以实现上述效果. 几个删除重复记录的SQL语句 1.用rowid方法 2.用g

  • oracle查询语句大全(oracle 基本命令大全一)

    1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username; 3.connect username/password//进入. 4.select table_name,column_name from user_tab_columns

  • oracle基本查询操作子查询用法实例分析

    本文实例讲述了oracle基本查询操作子查询用法.分享给大家供大家参考,具体如下: 一.子查询语法 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); 子查询在主查询之前一次执行完成. 子查询的结果被主查询使用. select ename from emp where sal > (select sal from emp where ename='SCOTT'); (*注意:子查询要

  • Oracle数据库中基本的查询优化与子查询优化讲解

    1. 查询条件合理排序 Oracle采用自下而上的顺序解析WHERE字据,从优化性能角度考虑,建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾,而将表 之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率. 例如: SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno

  • oracle 查询表名以及表的列名

    1.查询表名: 复制代码 代码如下: select table_name,tablespace_name,temporary from user_tables [where table_name=表名] 其中:table_name:表名(varchar2(30)); tablespace_name:存储表名的表空间(varchar2(30)): temporary:是否为临时表(varchar2(1)). eg: select table_name,tablespace_name,tempora

  • oracle数据库常用的99条查询语句

    1. select * from emp; 2. select empno, ename, job from emp; 3. select empno 编号, ename 姓名, job 工作 from emp; 4. select job from emp; 5. select distinct job from emp; 6. select distinct empno, job from emp;说明:因为雇员编号不重复, 所以此时证明所有的列没有重复,所以不能消除掉重复的列. 7. 查询

随机推荐