oracle简单查询

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

--author:yeeXun

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

--address:17-304

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

 

oracle简单查询

查看表结构

SQL>desc  table_name;

 

查询所有列

SQL>select * from table_name;

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

 

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

  set  timing  off 命令：关闭现实查询耗时关闭

 

·疯狂复制数据法：

insert  into  tableA(column1,column2,...) select * from tableB;

 

取消重复行

SQL>select distinct column1,column2 ,... from  table_name ;

 

❤大小写区分

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

 

字段别名

Oracle：select  字段名  "别名"  from  table_name ;

 

使用算术表达式

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

、-、*、/

❤处理nul值

使用nvl函数处理

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

 

like操作符 

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

_：表示任意单个字符

❤常用单引号：' '

 

在where条件中使用in

select  column1,column2,... from  table_name  where columnM  in (data1,data2,... )

 

使用is  null 的操作符

判断字段是否为空

 

使用逻辑操作符

and   or    ：在where子句中

 

使用order  by  操作符

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

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

 

使用列的别名排序

select  column1  'aa' ,column2  as  'bb'  from  table_name ;

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

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

oracle复杂查询

1.单表查询

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

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

 

group by 和having 子句

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

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

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

 

❤对分组数据的小结

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

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

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

 

多表查询

不同的表

自连接

相同的表

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

select  deptno  from emp  where  ename='SMITH'  and  job='MANAGE' ;

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

 

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

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

 select * from emp where deptno=(select deptno from emp where ename='SMITH');

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

select * from emp where job in(select job from emp where deptno=10);

in运算符 

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

 

多列子查询

select * from emp where (deptno,job)=(s elect deptno,job from emp where ename='SMITH');

 

在from子句中使用子查询

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

select a.ename,a.sal,a.deptno,b.avgsal 

from emp a,

(select deptno,avg(sal) avgsal from emp group by deptno) b 

where a.deptno=b.deptno and a.sal>b.avgsal;

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

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

 

衡量程序员水平：

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

2.对数据库方面的知识

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

 

分页查询

Oracle的分页共有三种

rownum 分页

1.

select a.*,rownum rn from (select * from emp) a;

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

2.显示ruwnum[oracle分配]

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

3. 

4.几个变化

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

select * from 

(

select a.*,rownum ru from 

(select ename,sal from emp)a 

where rownum<=10

) 

where ru>=6;

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

select * from 

(

select a.*,rownum ru from 

(select ename,sal from emp order by sal)a 

where rownum<=10

) 

where ru>=6;

Oracle分页

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

1.根据ROWID来分

select * from t_xiaoxi 

where rowid in

(

select rid from 

(select rownum rn,rid from

(select rowid rid,cid from t_xiaoxi  order by cid desc) 

where rownum<10000

) 

where rn>9980

) 

order by cid desc;

执行时间0.03秒

2.按分析函数来分

select * from 

(

select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t

) 

where rk<10000 and rk>9980;

执行时间1.01秒

3.按ROWNUM来分

select * from

(

select t.*,rownum rn from(select * from t_xiaoxi order by cid desc

) t 

where rownum<10000) 

Where rn>9980;执行时间0.1秒

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

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

 

用查询结果创建新表

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

create table myemp(id ,ename,sal) 

as select empno,ename,sal from emp;

 

合并查询

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

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

1.Union

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

2.union all

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

3.Minus

取差集，