oracle查询语句、分页查询

查询语句

1.       基本语法：select 【distinct】* {column1，列名2..} from  表名 【where （条件）】

（1）       select指定要查询哪些列的数据

（2）       column指定列名

（3）       *代表查询所有列

（4）       from指定查询哪张表

（5）       distinct可选，只显示结果是，是否删除重复数据

2.       查询所有列和查询指定列

（1）       查询指定列：select 列名1，列名2  from 表名

3.       取消重复行：

select distinct  列1，列2  from  表名：返回的数据一样才是重复行

4.       sql语句本身不区分大小写，但是查询的内容区分大小写

5.        nvl函数是oracle提供的，用于处理数据null的问题，基本语法：nvl（comm，0）；如果comm为空null，则返回0，如果不为空，则返回本身的值

6.       ||的使用：在查询的时候，如果希望把多列拼接起来，作为一列返回，则可以使用||

具体用法：select  列名 ||  （可以加内容）|| 列名 from  表名

        

 

         面试题：

                   希望删除用户，同时保留该用户的数据对象，怎么处理：

（1）       锁定该用户：alter  user  用户名  account  lock；（解锁用户，把lock改为unlock）

（2）       该用户已经不能登录到数据库，但是system用户依然可以使用他的书局对象

7.       where子句的用法

（1）       如何查找1995-2-7后入职的员工：

select * from 表名 where  to_char(员工列，‘yyyy-mm-dd’)>’1995-2-7’

                   (2)  如何查询1980入职的员工：

                              select * from 表名 where  to_char(员工列，‘yyyy’)=‘1980‘；

（2）       如何显示工资在2000到5000的员工

第一种：select * from  表名  where  工资列名>=2000  and  工资列<=5000;

第二种：select  * from  表名  where  工资列名  between  2000  and  5000；

8.       使用like查询（模糊查询）

%表示0到多个字符

_表示1个任意字符

如何显示首字母为s的员工姓名和工资：

    select  姓名列，工资列  from  表名  where  姓名列  like ‘s%’;

如何显示第三个字符为大写O的所有员工姓名和工资：

    select  姓名列，工资列  from  表名  where  姓名列  like’_ _O%

9.       where条件中，使用n

如何显示enpno为123.456.789..的雇员情况：

第一种：select * from  表名 where enpno=123 or enpno=456 or enpno=789；

第二种：select * from  表名 where enpno in（123,456,789）；

10.   is null的用法：

如何显示没有上级的雇员情况：

    select * from  表名 where  上级列  is null

11.   oracle中使用逻辑运算符

查询工资高于500或是岗位为上级的雇员，同时还要满足他们的姓名收字母为大写的J：

      select * from 表名  where  (工资列>500 or工作岗位列=‘上级’) and （姓名列 like ‘J%’）；

12.   order by子句的使用

order  by主要用处是对结果进行排序显示

如何按照工资的从低到高的顺序显示雇员的信息：

             select * from 表名  order  by  工资列 asc(asc可写可不写)

             在默认情况下，order by 是按升序排列：asc

             如果希望降序排列，则：

             select * from  表名  order by  工资列  desc

13.   oracle支持使用别名排序：

14.   查出最高工资与最低工资：

select  max（工资列）from 表名

select  min（工资列） from  表名

15.   查出平均工资与工资总和

select  avg（工资列），sun（工资列）from  表名

注意：avg不会把null进行统计，如果你希望为空值也需要计算，则应该泽阳做：

select sun（工资列）/count（*）from表名

count（*）也可以对字段进行统计cout（字段名）

16.   查询工资最高的人的姓名以及工作岗位：

select  ename,job  from  emp where sal=(select max(sal) from emp);

17.   group  by 和having子句

 查询每个部门的平均工资和最高工资：

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

18.   查询每个部门每种岗位的平均工资和最高工资并且分组

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

19.   查询出平均工资低于2000的部门号和平均工资：

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

  

 

   对分组函数的总结：

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

（2）       如果select语句中同时包含有group by、having、order  by，那么他们的顺序是group  by，having，order  by

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

 

 

   多表查询

1.       显示雇员名，雇员工资，及所在部门的名字

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

2.       在多表查询如果不带任何条件，则会出现笛卡尔集

避免出现笛卡尔集：

多表查询的条件是至少不能少于表的个数-1

3.       查询出部门号为10的员工姓名，员工工资，部门的名字

SQL> select dept.deptno,dept.dname,emp.ename,emp.sal from dept,emp where emp.deptno=dept.deptno and dept.deptno=10;

4.       查询出员工的姓名，员工的工资，员工的工资级别

SQL> select emp.ename,emp.sal,grade from emp,salgrade where emp.sal between salg

and salgrade.hisal;

5.       多表查询的时候，加表名的问题

如果两个表的列同名，则需要加表名区分，否则可以不加

注意：建议在多表查询的时候，使用别名：

SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.lo

isal;

6.       显示雇员名，雇员工资以及所在部门的名字，并按照部门排序：

SQL> select ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno

pt.dname;

7.       自连接（就是把一张表看做两张表）

显示个个员工的姓名和他的上级领导姓名

解题思路：把emp表看做两张表，一张是员工表，一张是上级表：

select worker.ename,boss.mgr from emp worker,emp boss where worker.empno=boss.mgr;

如果希望把没有上级的员工显示出来，则需要外连接

8.       在子查询中使用any

如何显示工资比部门30的任意一个工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);

第二种方式：select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);

9.       多列子查询：

查询出与smith的部门和岗位完全相同的所有雇员

select  * from emp where(deptno,job)=(select deptno,job from emp where ename=’SMITH’);

10.   查询出emp表中所有部门的名称：

select distinct dept.dname  from emp,dept where emp.deptno=dept.deptno;

11.   显示所有雇员名及全年收入13月（工资+补助），并指定列别名‘年收入’

select ename,(sal+nvl(comm,0))*13 年收入 from emp;

12.   在from子中使用子查询

先查询出各个部门的平均工资：select deptno,avg(sal) from emp group by deptno;

把上面的查询结果当成一个临时表：

SQL> select emp.deptno,emp.ename from emp,(select deptno mydp,avg(sal) myavg from emp by deptno) where emp.sal>myavg and emp.deptno=mydp order by emp.deptno;

13.   查找每个部门最高工资的人的详细信息

SQL> select* from emp,(select deptno dpt,max(sal) height from emp group by deptno) where emp.sal=height and emp.deptno=dpt;

14.   显示每个部门的信息编号、名称和人员数量：

SQL>select dept.deptno,dept.dname,mycount from dept,(select deptno,count(*)mycount from emp group by deptno) mytable where dept.deptno=mytable.deptno（+）;+号表示外连

 

分页查询

   mysql：select * from where 条件 limit 从第几条取，去几条

   sqlserver：select top  4 * from 表名 where id not in（select top 4 id from 表名 where 条件）；  意思：排除前4条，再取4条，实际上是取出5-8条

   oracle：查询出第4条到第6条的记录：

  SQL> select t2.* from(select t1.*,rownum rn from(select * from emp)t1 where rownum<=6)t2 where rn>=4;

   上面这个sql语句是oracle数据库效率比较高的查询方法；oracle使用三层过滤机制

   第一层：select * from emp

   第二层：select t1.*,rownum rn from (select * from emp)t1 where rownum<=6

   第三层：整个sql语句

上面的sql语句相当于一个分页模板：6代表取到第几条；4代表从第几条开始去取

如果西需要针对不同的情况分页，请在最内层进行处理，包括多表

查看分页查询效率：

模拟10万的一个表-》测试

create table mytest as select empno,ename,sal,comm,deptno from emp;

自我复制：

insert into mytest(empno,ename,sal,comm,depto)select empno,ename,sal,comm,depot from mytest;

 

本语法：select  列名  from  表1 inner join 表2 on 条件

举例：显示出员工信息和部门名称：

select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;

等价于：

select emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno;

 

外连接

外连接分为3中：左外连，右外连，完全外连。

左外连：select stu.id,stu.name,score.grade from stu left join score on stu.id=score.id;

如何判断一张表是左边：如果在left join左边，就是左表

另外一种写法：select stu.id,stu.name,score.grade from stu ，score where stu.id=score.id（+）;

右外连：

显示所有成绩，如果没有名字与id也把成绩显示出来：

select stu.id,stu.name,score.grade from stu right join score on stu.id=score.id;

也可以这样写：

select stu.id,score.grade from stu,score where stu.id(+)=score.id;

说明：右外连，指的是右边的表如果没有和左边的表任何一条记录匹配，也要被选中；

全外连：不管有没有匹配上，都显示出来：

select stu.id,stu.name,score.grade from stu full outer join score on stu.id=score.id;

 

 

列出部门名称和这些部门的员工信息，并且同时列出那些没有员工的部门：

select dept.dname,emp.ename,emp.job,emp.mgr,emp.deptno from emp,dept where emp.deptno(+)=dept.deptno;

 

列出薪资大于1500的各种工作：

select distinct emp.job from emp,(select min(sal) mymin,job from emp group by job)where mymin>1500;

查询出与scott从事相同工作的员工信息：

select emp.ename,emp.job from emp,(select job scottjob from emp where ename='SCOTT')where emp.job=scottjob;