--=================
--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
取差集,