oracle数据库之sql查询语句笔记

数据类型：
--字符型：
----char：定长查询速度快；浪费空间，全局匹配
----varchar2：节省空间；推荐使用的---
----clob：character large object
--数字型：
----number：
--日期类型：
--blob：二进制数据，可以存放图片；
对于图像，声音，视频等容量较大的数据，如果对其没有特殊
的安全性要求，则我们一般都是在数据库存储其硬盘路径，
真正实体是放在某个文件夹下的；

oracle语句不区分大小写，但是涉及到查询到的数据内容是区分大小写的；
select distinct classid，className from student；
这是去除重复行的语句；
SELECT sal*13+ nvl(comm,0)*13,ename from emp;
nvl：如果值为空，则以0代替；
select ename,sal from emp where ename like 'S%';
查询所有员工姓名的首字母是S的姓名和工资
select ename,sal from emp where sal between 1000 and 2000;
查询工资在1000到2000之间所有员工的姓名和工资；

select ename,empno from emp where empno in (7654,7369) ;
查询empno为7654或者7369的所有员工的姓名和员工编号；
这样的查询语句比较快；

select ename,empno from emp where mgr is not NULL;
查询mgr不为空的ename和empno；

select * from emp order by sal ;默认升序偏序；
如果要降序的话，加上desc；
select count(empno) as "人数" from emp; 查询共有多少个empno；
select ename,sal from emp where sal=(select max(sal) from emp);这是一个子查询，即查询中还嵌套着查询，且子查询的结果作为父查询的判断条件内容；

select ename,sal from emp where sal>(select avg(sal) from emp);查询工资高于平均工资的员工信息；

update emp set sal= sal*1.1 where sal<(select avg(sal) from emp);将工资低于平均工资的人的工资统统涨个10%；

update emp set sal=sal*1.1 where sal<(select avg(sal) from emp) and hiredate<'31-12月-1980';
将工资低于平均工资的人而且入职时间早于1980年底的员工工资统统涨个10%；
select avg(sal),deptno from emp group by deptno;
注意分组查询需要将分组字段显示出来；
同时，使用分组函数时sum，avg，max，min等，不得出现不相关字段，即只能出现分组字段；

select avg(sal),max(sal),deptno,job from emp GROUP by deptno, job;求一个部门中各职位的平均工资和最高工资；

select trunc(avg(sal),2),deptno from emp  GROUP by deptno having avg(sal)>2000 ;求平均工资低于2000的部门号和平均工资；
数据分组的总结：
1：分组函数只能出现在选择列表，having，order by子句中；
2：如果在select语句中同时包含有group by，having ，order by
那么他们的顺序是：group by，having，order by；
3：在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错；
select trunc(avg(sal),2),deptno from emp  GROUP by deptno having avg(sal)>2000 ORDER by deptno;注意三个语句出现的顺序；

笛卡尔积：多表查询的条件至少要有表的个数-1；
select e.ename, d.dname from emp e, dept d where e.deptno= d.deptno;多表查询存在着笛卡尔积的问题，需要添加条件去去除；

select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal BETWEEN s.losal and s.hisal;

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

自连接：同样的一张表当做两张表来用，个字段之间互相引用；
select worker.ename,boss.ename from emp worker, emp boss where worker.mgr= boss.empno;
子查询：
select ename,sal, deptno from emp where deptno=(select deptno from emp where ename='SMITH');
单行子查询：返回结果只有一行；
多行子查询：返回结果有多行；
select  ename,job,sal,deptno from emp where job in(select DISTINCT job from emp where deptno=10);需要使用in关键字；不能用=

select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);----->A

select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30 );---->B
A和B相比，B的效率更高；
因为oracle数据库的执行是从右向左的；

select * from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH'); 多列子查询---->>C

select * from emp where (deptno, job)=(SELECT deptno, job FROM emp where ename='SMITH');----->>D
C和D两个等价；

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

select * from (select avg(sal) mysal, deptno from emp group by deptno)  ;把多行多列查询出来的结果当做一个表，然后从查询结果里面查表；

select a2.ename, a2.sal, a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) as mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
内嵌视图：要取别名；

oracle分页查询有三种方式：
常用的一种：
select * from (select a1.*, rownum as rn from (select * from emp) a1 where rownum<=10) a2 where a2.rn>6;
第一步：oracle数据库提供的rownum，只能使用一次；
select a1.* ,rownum from (select * from emp) a1;
;
select * from (select a1.*,rownum as rn from (select * from emp)  a1 where rownum>6) a2 where a2.rn<12;

生成表查询：
create table mytable (id,name,age) as select id,name,age from
users ;从users表中查询出id，name，age字段，并把这些消息存入到新创建的mytable表中；

联合查询：求几个查询结果的并集；
select * from emp where empno=7654 UNION SELECT * from emp where ename='SMITH';但是需要保证列要相等，即两个查询的结果集各列要相同；

并集，交集，补集的操作比and or等关键字的查询效率高；驱动驱动类：oracle.jdbc.driver.OracleDriver
url：jdbc:oracle:thin:@127.0.0.1:1521:oral

日期格式转换函数
to_date('1980-11-12','yyyy-mm-dd');

update emp set (job, sal, comm)=(SELECT job, sal, comm FROM emp where ename='SMITH') where ename='SCOTT';
子查询语句；
insert into emp(empno, ename, job) select empno+1, ename, job from emp;

select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

select mod(10,3) from dual;//dual虚拟的一张表；

select * from emp;
select ename,sal from emp;这条语句执行效率更高；

select sal*12 as 年工资 ,ename from emp;从emp表中查出所有员工的姓名和
年工资；

select sal*12+nvl(comm,0),ename from emp;从emp表中查出所有员工的姓名和
年工资；年工资包括奖金，nvl函数的意思是如果comm字段有值，则取出，如果没有值就用0代替；

select ename,sal,hiredate from emp where hiredate>'1-1月-1980';
从emp表中查询入职日期大于1980-1-1的员工的姓名和工资；
记住日期格式：'dd-mm月-yyyy';

模糊查询：
select ename,sal from emp where ename like 'S%';
从emp表中取出姓名首字母为S的员工的姓名和工资；
%：代表任意个任意字符；
_:代表一个任意字符；

select ename,sal from emp where ename like '__O%';
从emp表中取出员工姓名的第三个字面为O的员工的姓名和工资；

使用in：
select ename,job,mgr from emp where deptno in(7369,7544,1265);
从emp表中查询deptno号为 7369,7544,1265,的员工的姓名，工作，上级；
使用in语句比多个并列的or更加高效；

使用is null判断某个字段是否为空；
select ename,job from emp where mgr is null;
从emp表中选出mgr字段为空的员工的 姓名和工作字段；
注意判断一个字段为空使用的是is null，判断一个字段不为空使用的是 is not null；

 

 

select语句的评判顺序：很重要；
1 from ，第一个判断，表示从哪个表中选什么内容；
2 如果有where子句，在from的结果将应用与where子句中；
  如果没有where子句，那么from的结果将用于下一个指定的子句，比如 group by 或 having子句
   表示对选出的表数据进行筛选，或者分组
3 评判玩最后一个子句后则将结果供 select子句进行进一步的筛选；
4 select语句选出来的结果供order by子句进行进一步的分组排序或者筛选；
这符合我们日常生活中的习惯；
比如我们要在江西所有的大学生中，选出年龄小于20岁，学生的姓名和年龄和成绩；并按照年龄降序排列；

我们的思路是，首先将目标锁定在大学生中，即from University Student 表中；
然后这些大学生中那些事符合条件的，年龄小于20岁 where age<20;

这些学生选出来后，我们要这些学生的那些信息呢？ 即select name,age;

那么这些学生的的年龄和姓名信息被选出来后，你怎么组织呢？是按什么顺序排列呢？
即  order by  age；

SELECT ENAME,SAL FROM EMP WHERE (SAL>500 OR JOB='MANAGER') AND ENAME LIKE 'J%';
从emp表中选出 姓名首字母为J的并且工资高于500或者工作岗位是manager的员工的姓名和工资信息；

SELECT * FROM EMP ORDER BY SAL DESC;从emp表中选出所有员工的所有信息；按工资字段
的降序排列；

SELECT * FROM EMP ORDER BY DEPTNO,SAL DESC;desc是降序，asc是降序；
从emp表中选出所有员工的所有信息，并按照deptno升序，然后按照工资降序排列
也就是说排序有两级排序，先按部门号deptno升序，即部门号是第一级，优先；
后按照工资降序排列，即工资是第二级，次优先；

 

SELECT ENAME,SAL+NVL(COMM,0) AS "年薪" FROM EMP ORDER BY "年薪";
从emp表中选择员工的的 姓名，年薪（月工资*12+奖金）,并按年薪升序排序；

 

select ename,sal from emp where sal=(select max(sal) from emp   );
从emp表中取出最高工资的员工的姓名和工资；
注意这里用到了子查询，所谓的子查询也就是潜逃查询，一个查询的结果集作为另一个查询的
条件判断依据；

select ename,sal from emp where sal>(select avg(sal) from emp);
从emp表中选出工资高于平均工资的员工的姓名和工资；这里也用到了子查询；

update emp set sal=sal*1.1 where sal<(select avg(sal) from emp);
将工资低于平均工资的员工的工资提高10%；

SELECT MAX(SAL),MIN(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;
按照部门分组，求个部门的工资的最大值和最小值，需要注意的是，这里的分组字段一定要显示
否则会报错；

 

SELECT AVG(SAL),MAX(SAL),DEPTNO,JOB FROM EMP GROUP BY DEPTNO,JOB
ORDER BY DEPTNO;
从emp表中按部门，职位二级分组，求出每个部门的每种职位的平均工资和最高工资
并按部门编号，升序排列；

SELECT * FROM ( SELECT DEPTNO,AVG(SAL) AS ASAL FROM EMP GROUP BY DEPTNO) SUB WHERE
 SUB.ASAL<2000;
这句是查找出部门平均工资低于2000的部门和该部门的平均工资；
这要分成两个地方来看:
1 首先：SELECT DEPTNO,AVG(SAL) AS ASAL FROM EMP;这是查出各个部门的平均工资；然后将查询后的结果有当成是一个表，取名为 SUB表；他有两个字段，部门号DEPTNO,和平均工资ASAL；
2 然后就是对这个SUB进行操作，取出平均工资低于2000的部门号；
SELECT SUB.DEPTNO,SUB.ASAL FROM SUB WHERE SUB.ASAL<2000;----------------A

同时这个要求还可以由having语句来实现；having语句是用来限制分组显示结果，也就是对分组结果进行进一步的过滤；

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)<2000;-------------B
从emp表中按部门分组，求出各个部门的平均工资，同时对结果进行过滤，使用having子句，
将平均工资低于2000的部门号和平均工资显示出来；
问题也随之来了，A和B哪个语句更加高效呢？？？

多表查询：条件判断必须要是外键的作为条件判断，否则会出现笛卡尔积；要避免出现；
多表查询的条件至少要是表的个数-1；
dept表：deptno，dname；
emp表： deptno,ename....
他们的共有字段是deptno，即emp的外键，dept的主键；
SELECT E.ENAME,E.SAL,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=E.DEPTNO;
首先给多个表取个别名；用表名去标识各个字段名属于哪个表，然后加上相应的条件；
这个语句的意思是给emp表取e，dept取d这个别名，从这两个表中选出，
员工的姓名，工资和所在的部门号，条件是d表的部门号等于e表的部门号；

SELECT E.ENAME,E.DNAME,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND
DEPTNO=10;
在上一个基础上继续添加条件；

SELECT E.ENAME,E.SAL,E.GRADE FROM EMP E,SALGRADE G WHERE E.SAL BETWEEN G.LOSAL
AND G.HISAL;
也是一个子查询；

SELECT E.ENAME,E.SAL,E.DNAME FROM EMP E,DEPT D WHERE D.DEPTNO=E.DEPTNO ORDER BY D.DNAME;
从emp表和dept表中选出员工的姓名，工资，以及所在的部门名称 并按部门排序；

 

查出FORD的上级的信息：
SELECT * FROM EMP WHERE EMPTNO=(SELECT MGR FROM EMP WHERE ENAME='FORD');
这是子查询的做法；--------------A

SELECT BOSS.ENAME,BOSS.EMPNO,BOSS.SAL FROM EMP WORKER,EMP BOSS WHERE WORKER.MGR=
BOSS.EMPNO AND WORKER.ENAME='FORD';
这是自连接的做法--------------B
A和B哪种方式最高效呢？

选出和SMITH同一个部门的员工信息；
第一步：选出SMITH的部门：
SELECT DEPTNO FROM EMP WHERE ENAME='SMITH';
第二步：将第一步的结果作为条件；
SELECT * FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');

选出工资比30号部门所有员工都高的员工姓名和工资和部门号；
第一步：选出30部门的最高工资：
SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30;

第二步：将第一步的结果作为条件选出；
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL>(SELECT MAX(SAL) FROM EMP WEHRE DEPTNO=30);

第二种方式：
SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL> ALL (SELECT SAL FROM EMP);
all关键字是用于集体比较的；

SELECT ENAME,SAL,DEPTNO,JOB FORM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SMITH') AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');
选出部门号和职位和SMITH相同的职员姓名，工资，部门号，工作；
上面这个sql语句可以简化成：
SELECT ENAME,SAL,DEPTNO,JOB FORM EMP WHERE (DEPTNO,JOB)=(SELECT DEPTNO,JOB FROM EMP WHERE ENAME='SMITH');

选出高于自己部门平均工资的员工信息：
1 ：先查出各个部门的平均工资；SELECT DEPTNO,AVG(SAL) AS ASAL FROM EMP GROUP BY DEPTNO;----F
2 ：将上面的查询结果当做一个表F；然后从emp表和F表中去除笛卡尔积；增加条件即可；
SELECT E.ENAME,E.DEPTNO,F.ASAL FROM EMP E,
(SELECT DEPTNO,AVG(SAL) AS ASAL FROM EMP GROUP BY DEPTNO) F
WHERE
E.DEPTNO=F.DEPTNO
AND E.SAL>F.ASAL;

给列去别名用as，给表取别名不用as；

使用分页查询的思路：
1 首先查询出需要被分页显示的记录；SELECT * FROM EMP;
2 将1中的结果作为一个表，然后显示出行号，这个行号是系统提供的；
 SELECT E.*,ROWNUM AS RN ( SELECT * FROM EMP) E;
3 将2中的结果作为一个带行号的表，然后对这个表进行查询，查询指定的行号；
   SELECT F.* (SELECT E.*,ROWNUM AS RW(SELECT * FROM EMP) E) F WHERE F.RN>3 AND F.RN<9;
注意要改变查询列和排序的问题都是在最底层改变；

分页查询实现方式有三种：
1 rowid：效率最高，最难懂；
2 分析函数，效率最低；
3 ：rownum，最容易理解，效率第二；

生成表查询：
create table worker（id,name,sal） as select
empno,ename,sal from emp;
即将查询的结果作为创建一个新的表的内容；

联合查询：

学好了oracle数据库，其他数据库都是浮云；
学好了JavaEE，.NET就是浮云；
所以要好好学好JavaEE和Oracle数据库；

更新表查询：

insert into worker(id,name,sal) SELECT EMPNO,ENAME,SAL FORM EMP WHERE DEPTNO=10;
将emp表中的10号部门的员工的empno，ename，sal取出；
然后插入到worker表中，注意字段要匹配；
事务真的很重要。。。

字符串的合并用||;
dual的虚拟表；

<script type="text/javascript" id="wumiiRelatedItems"> </script>

转发至微博

 

转发至微博

阅读(34) | 评论(0)

|

       

用微信  “扫一扫”

将文章分享到朋友圈。

 

用易信  “扫一扫”

将文章分享到朋友圈。

 

喜欢 推荐 0人  |  转载

 

历史上的今天

最近读者

热度

在LOFTER的更多文章

关闭

玩LOFTER，免费冲印20张照片，人人有奖！          我要抢>

评论

this.p={ m:2, b:2, loftPermalink:'', id:'fks_087065092094089071084095<wbr>083074072084080068086081<wbr>080065084095', blogTitle:'oracle数据库之sql查询语句笔记', blogAbstract:'

数据类型：
--字符型：
----char：定长查询速度快；浪费空间，全局匹配
----varchar2：节省空间；推荐使用的---
----clob：character large object
--数字型：
----number：
--日期类型：
--blob：二进制数据，可以存放图片；
对于图像，声音，视频等容量较大的数据，如果对其没有特殊
的安全性要求，则我们一般都是在数据库存储其硬盘路径，
真正实体是放在某个文件夹下的；

\n

oracle语句不区分大小写，但是涉及到查询到的数据内容是区分大小写的；
select distinct classid，className from student；
这是去除重复行的语句；
SELECT sal*13+ nvl(comm,0)*13,ename from emp;

', blogTag:'', blogUrl:'blog/static/217276209201341594430781 ', isPublished:1, istop:false, type:0, modifyTime:1400160287704, publishTime:1368625470781, permalink:'blog/static/217276209201341594430781 ', commentCount:0, mainCommentCount:0, recommendCount:0, bsrk:-100, publisherId:0, recomBlogHome:false, currentRecomBlog:false, attachmentsFileIds:[], vote:{}, groupInfo:{}, friendstatus:'none', followstatus:'unFollow', pubSucc:'', visitorProvince:'', visitorCity:'', visitorNewUser:false, postAddInfo:{}, mset:'000', mcon:'', srk:-100, remindgoodnightblog:false, isBlackVisitor:false, isShowYodaoAd:false, hostIntro:'JAVA软件工程师,有扎实的Java基础,熟悉JavaEE技术,对框架的底层原理熟悉,学习能力强。', hmcon:'0', selfRecomBlogCount:'0', lofter_single:'' } {list a as x} {if !!x}

{if x.visitorName==visitor.userName} {else} {/if}

{if x.moveFrom=='wap'}   {elseif x.moveFrom=='iphone'}   {elseif x.moveFrom=='android'}   {elseif x.moveFrom=='mobile'}   {/if} ${fn(x.visitorNickname,8)|escape}

{/if} {/list} {if !!a} ${fn(a.nickname,8)|escape}

${a.selfIntro|escape}{if great260}${suplement}{/if}

 

{/if}