oracle查询

最新推荐文章于 2023-08-25 20:02:54 发布

最新推荐文章于 2023-08-25 20:02:54 发布 · 126 阅读

文章标签：

本文介绍Oracle数据库中的SQL查询技巧，包括基本查询、复杂查询、分页查询等，并讲解了如何利用SQL提高查询效率。

--=================

--author:yeeXun

--date:2010-11-25　16:46:00

--address:17-304

--=================

oracle简单查询

查看表结构

SQL>desctable_name;

查询所有列

SQL>select*fromtable_name;

❤ 少用此查询，因为查询速度很慢

❤settimingon 命令：将查询耗时现实在查询结果后面

settimingoff命令：关闭现实查询耗时关闭

·疯狂复制数据法：

insertintotableA(column1,column2,...)select*fromtableB;

取消重复行

SQL>select distinct column1,column2,...fromtable_name;

❤ 大小写区分

数据区分大小写，而SQL 语句不区分大小写，即表的表名字段名都不区分大小写

字段别名

Oracle： select 字段名 " 别名 "fromtable_name;

使用算术表达式

+：当“ + ”左右的字段有一个为 NULL 值，那么整个字段的结果将为 NULL 值

、- 、 * 、 /

❤ 处理nul 值

使用nvl 函数处理

nvl（ column,0 ）：如果 column 为空，则用 0 代替；如果不为空，则为其原值

like操作符

%：表示任意 0 到多个字符

_：表示任意单个字符

❤ 常用单引号：''

在where 条件中使用 in

selectcolumn1,column2,...fromtable_namewherecolumnMin(data1,data2,...)

使用isnull 的操作符

判断字段是否为空

使用逻辑操作符

andor：在 where 子句中

使用orderby 操作符

排序：默认为升序（asc ）排列，可以在字段后面加上 desc （降序）

多个字段排序，直接将要排序的字段加载orderby 后面，用逗号（ , ）分隔开

使用列的别名排序

selectcolumn1'aa',column2 as 'bb'fromtable_name;

可以使用as 关键字，以可以使用

--------------------------------------------------------------------

oracle复杂查询

1.单表查询

数组分组——max,min,avg,sum,count

❤ 若查询列中出现分组函数，那么必须全部都是分组函数

groupby和 having 子句

groupby：用于对查询的结果分组统计

分组字段一定要出现在查询字段里面

having：用于限制分组现实结果，这个子句可以使用分组函数（ sqlserver 中叫聚合函数），而 where 子句里面不能用

❤ 对分组数据的小结

1. 分组函数只能出现在选择列表、having 、 orderby 子句中

2. 如果在select 语句中同时含有 groupby 、 having 、 orderby （这也是他们在查询语句中的出现顺序），那么他们的排序是 groupby 、 having 、 orderby

3. 在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须有一个出现在groupby 子句中，否则出错

多表查询

不同的表

自连接

相同的表

❤ 数据库在执行sql 语句时，是从右到左

selectdeptnofromempwhereename='SMITH'andjob='MANAGE';

这条语句是先执行where 部分，再执行前面的 select 部分

❤ 先写子查询，在写主查询

单行子查询：指只返回一行数据的子查询

select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH');

多行子查询：指返回多行数据的子查询

select*fromempwherejobin(selectjobfromempwheredeptno=10);

in运算符

在多行子查询中使用any 运算符

多列子查询

select*fromempwhere (deptno,job) =(s electdeptno,jobfromempwhereename='SMITH');

在from子句中使用子查询

？如何查找高于自己部门平均工资的员工信息

s elect a.ename,a.sal,a.deptno,b.avgsal

fromempa,

(selectdeptno,avg(sal)avgsalfromempgroupbydeptno) b

wherea.deptno=b.deptnoanda.sal>b.avgsal;

❤当在 from子查询中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图；当在 from 子句中使用子查询时，必须给该子查询指定别名，建议不要用 as

❤给表取别名，不要加as ，在给列取别名时，加 as

衡量程序员水平：

1. 本身对网络处理怎么样

2. 对数据库方面的知识

3. 程序的优化，代码效率高

分页查询

Oracle的分页共有三种

rownum分页

selecta.*,rownumr n from(select*fromemp) a;

这个rn是oracle给每一条数据分配的行号，变化的

2.显示ruwnum[oracle分配]

Ruwnum只能用一次，也就是不能使用and连接，betweenand

4. 几个变化

A. 如果要指定查询列，只需要修改最里层的子查询即可

select*from

(

selecta.*,rownumrufrom

(select ename,sal fromemp)a

whererownum<=10

)

whereru>=6;

B.如何排序，只需要修改最里层的子查询即可

select*from

(

selecta.*,rownumrufrom

(selectename,salfromemp orderbysal )a

whererownum<=10

)

whereru>=6;

Oracle分页

老师给的例子，上网找了一下，居然有！

1.根据 ROWID 来分

select*fromt_xiaoxi

whererowidin

(

selectridfrom

(selectrownumrn,ridfrom

(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)

whererownum<10000

)

wherern>9980

)

orderbyciddesc;

执行时间0.03 秒

2.按分析函数来分

select*from

(

selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit

)

whererk<10000andrk>9980;

执行时间1.01 秒

3.按 ROWNUM 来分

select*from

(

selectt.*,rownumrnfrom(select*fromt_xiaoxiorderbyciddesc

whererownum<10000)

Wherern>9980; 执行时间0.1 秒

其中t_xiaoxi 为表名称， cid 为表的关键字段，取按 CID 降序排序后的第 9981-9999 条记录， t_xiaoxi 表有 70000 多条记录

个人感觉1 的效率最好， 3 次之， 2 最差

用查询结果创建新表

这个命令是一种快捷的建表方法

createtablemyemp(id,ename,sal)

asselectempno,ename,salfromemp;

合并查询

有时候在实际应用中，为了合并多个 select语句的结果，可以使用集合操作符号， union ， unionall ， intersect ， minus

集合操作的速度很高，比and ， all 这些语句快

1. Union

该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行

2. unionall

相似于union 操作符，但是不取消重复行，而且不会排序

3. Minus

取差集，