Oracle学习笔记五 --- 表查询



下面使用scott用户,用到的表见底部

1、查看表结构

desc [表名]

    desc emp

2、distinct命令，去除重复行

3、练习

查找SMITH的工作，薪水等

select deptno,job,sal from emp where ename ='SMITH';

4、函数nvl

如果值为null，即以0代替

查找员工年工资

select sal*13+comm*13 "年工资" from emp;

在comm没有值时，结果将没有值，所以使用函数nvl

select sal*13+nvl(comm,0)*13 "年工资" from emp;

5、条件并列：and

select sal, empno, ename from emp where sal>=2000 and sal<2500;

6、like操作符

    %    表示多个任意字符

    _     表示单个任意字符

    首字母为S的员工姓名和工资

    select ename, sal where ename like 'S%';

    第三个字符为大写O得所有员工姓名和工资

    select ename,sal from emp where ename like '__O%';

7、在where条件使用in

    select * from emp where empno in(7844,7998,3222);

8、逻辑操作符

    工资高于500或岗位为MANAGER  同事还要满足姓名首字母为大写J

    select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

9、order by [字段] [规则:asc、desc]

    工资从第低到高排序

    select * from emp order by sal;            //asc默认

    select * from emp order by sal desc;        //从高到低

    按照部门号升序而雇员工资降序排列

    select * from emp order by deptno , sal desc;

    

10、使用列的别名排序 as

    select ename, (sal+nvl(comm,0))*13 as "年薪" from emp order by "年薪";

    select ename, (sal+nvl(comm,0))*13 as nianxin from emp order by nianxin;

    select ename, (sal+nvl(comm,0))*13  "年薪" from emp order by "年薪";

    select ename, (sal+nvl(comm,0))*13  nianxin from emp order by nianxin;

    as可以省略，中文别名需要加双引号

    注意：给表起别名不能加as，见下面 19

------8<---------------8<---------------8<---------------8<---------------8<---------------8<---------

复杂查询

------8<---------------8<---------------8<---------------8<---------------8<---------------8<---------

11、数据分组

    max        min        sum        count    avg

    显示所有员工中最高工资和最低工资

    select  max(sal), min(sal) from emp;

    显示最高工资的员工姓名及其工资

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

    注意： 这样写是错误的：select ename,sal from emp where sal = max(sal);ORA-00934: 此处不允许使用分组函数

    显示比平均工资高的员工

    select * from emp where sal > (select avg(sal) from emp);

   显示所有员工平均工资和工资总和

    计算总共有多少员工

12、group by

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

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

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

13、筛选having 

分组             select avg(sal), max(sal), deptno from emp where  group by deptno

分组后筛选     select avg(sal), max(sal), deptno from emp where  group by deptno having avg(sal) > 2000;

14、数据分组小结

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

14.2   如果select 语句中同时包含group by,     having,    order by    那么他们的顺序是

          从左至右  group by ,  having , order by

--------8<-----------------8<-----------------8<-----------------8<-----------------8<---------

多表查询

--------8<-----------------8<-----------------8<-----------------8<-----------------8<---------

15、笛卡尔集 多表查询

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

    显示雇员名字、工资、及其部门名字

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

    这里两个表 dept和emp，一个条件  emp.deptno = dept .deptno;

    

    显示雇员名称，工资，部门名字，按部门排序

     select a.deptno, a.ename,a.sal, b.dname  from emp a, dept b where a.deptno = b.deptno order by a.deptno

 

16、自连接

     select   a.ename "小弟", b.ename BOSS from emp a, emp b  where a.mgr = b.empno and a.ename = 'FORD'

            

17、子查询

17.1、单行子查询 ‘=’ 等于号

题目：查找和SMITH同一部门的员工

查找出SMITH的部门号（结果仅一行数据）

    select deptno from emp where ename='SMITH';

显示

select * from emp where deptno = 

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

17.2、单列多行子查询 in 、 all、any

题目：查找和部门10的工作相同的雇员的名字

查找出10号部门所有的岗位（结果是多行数据）

select distinct job from emp where deptno=10;

显示

select  * from emp where job in

    (select distinct job from emp where deptno=10)

显示工资比部门30的所有员工的工资高的员工姓名工资和部门号

select ename,sal,deptno from emp where sal > 

    all (select sal from emp where deptno=30);

根据oracle的执行顺序，下面这个单行子查询效率更高

select ename,sal,deptno from emp where sal > 

    (select max(sal) from emp where deptno=30);

显示工资比部门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);

18、多行多列

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

19、练习

显示各部门高于该部门平均工资的员工姓名、工资、部门号

select a2.ename, a2.sal, a2.deptno, a1.mysal 

from emp a2, (select deptno, avg(sal) mysal from emp group by deptno) a1

where a2.deptno=a1.deptno and a2.sal > a1.mysal;

上面子查询得到的表a1叫内嵌视图

from子查询必须指定别名，这里指定为a1，另外给表取别名时不能加as   

20、分页查询    

        一共三种

20.1、根据rownum来分，效率还不错，可读性也行

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

    这里a1.* a1表的所有列  

    select a1.* , rownum rn from (select * from emp) a1 where rownum < 8

        

  

   select * from (select a1.* , rownum rn from (select * from emp) a1 where rownum < 8) a2 where rn > 5;

     

    这里，rn不能修改为rownum，子查询的rownum也不能修改为rn

20.2、根据ROWID来分，效率最高，但可读性差

20.3、根据分析函数来分，效率很差，

21、用查询结果创建新表

    create table mytable(id,name,sal,job,deptno) as select empno, ename,sal,job,deptno from emp;

    表将创建，数据可以拷贝过来了

22、合并查询

为了合并多个select语句的结果，可以使用集合操作符：union、union all、intersect、minus

22.1、union 【并集】会去掉重复的记录

    select ename,sal,job from emp where job='MANAGER' union 

        select ename,sal,job from emp where sal>2500;

    

22.2、union all 【并集】不会去掉重复的记录

22.3、intersect 【交集】

22.4、minus    【差集】

23、创建新的数据库

23.1、通过oracle提供的向导工具 dbca :数据库配置助手   Database Configuration Assistant

emp表

dept表