表查询

第四章 表查询

 

SQL查询语句语法：

select 表名1.列1，表名2.列2… from 表名1,表名2  where 条件 order by 排序项 asc或者desc;

select 列1，列2… from表名group by 分组项 having 条件 order by 排序项 asc或者desc;

 

 1 查看表结构

 descdept;

 descemp;

  2 查询所有数据

 select* from emp;

  3查询指定列：

select 列1，列2... from 表名;

select empno, ename from emp1;

  4 取消重复查询的行：

select distinct job from emp1;

  5 查询时，给列起别名

查询emp1表中的empno,ename,sal

select empno, ename as “姓名”,sal from emp1;

select empno, ename “姓名”,sal from emp1;

  6 Oracle 中字符串的连接

  select ename || ' 担任' || job || '职务' from emp1;

  7 查询为null的数据

  select ename,sal,comm from emp1 where comm is null;

v  使用算术表达式查询

      +，-，*，/

1.查询emp表中每个员工的实际年薪（12个月工资+12个月奖金）

年薪= sal*12 + comm *12=(sal+comm)*12

select ename, (sal+nvl(comm,0))*12“年薪” from emp;

v  使用比较运算符

   =、>、<、>=、<=、!=、<> 、any（任一）、all（全部）

 1.查询emp表中薪水大于1250小于2500的员工姓名和薪水

              select ename,sal from emp wheresal > 1250 and sal < 2500;

2. 查询emp表中职位为CLERK或者SALESMAN中任意一个的姓名和职位

              select ename,job from emp wherejob = any('CLERK','SALESMAN');

 

v  使用逻辑运算符查询:

and、or、not；between…and、in

1. 查询出工资在2000--3000之间的员工信息

SQL> select * from emp wheresal between 2000 and 3000;//包含2000和3000

2查询出10号部门中的所有经理，和20号部门中不是经理但是工资高于2000的员工信息

      SQL>select * from emp where (deptno=10 and job='MANAGER') or

 (deptno=20 and job!='MANAGER' andsal>2000);

v  模糊查询 like

_: 代表一个任意字符

       %:代表0到多个任意字符

1. 查找出姓名第二个字母是L的员工信息 *L*****   _L%

SQL> select * from emp where ename like'_L%';

v  排序 order by [asc/desc]

asc： 从小到大(默认为asc)

desc：从大到小

1 查询出emp表中员工信息，要求按照工资从低到高排序

SQL> select * from emp order by salasc;//asc可以省略

 

2(多列排序)查找出员工信息，按照部门从小到大排序，在部门内按照工资从大到小排序

SQL> select * from emp order by deptnoasc,sal desc;

 

v  使用聚合函数/分组函数 max,min,avg,sum,count;

1 查询出emp表中最高工资

SQL>select max(sal) from emp;

 

v  使用group by 和 having

1. 查询出每个部门的最高，最低，平均工资

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

 

2. 查询出不同工作岗位的平均工资，只显示平均工资高于1500的信息，并且平均工资按照从低到高显示

SQL>select job,avg(sal) from emp group by job having avg(sal)>1500 order byavg(sal);

 

v  多表查询

1. 从emp和dept表中查询出每个员工姓名，工作，部门名，部门所在地

SQL> select e.ename,e.job,d.dname,d.locfrom emp e,dept d where e.deptno=d.deptno;

 

v  自连接：自连接是指在同一张表的连接查询。

1. 查询出emp表中所有员工的上级

       select e1.empno,e1.ename, e1.mgr,e2.ename"上级姓名" from emp e1,emp e2 wheree1.mgr = e2.empno;

 

v  子查询

1查询smith的上级信息

select ename from emp where empno = (selectmgr from emp where ename = 'SMITH');

1.(all)查询出比20号部门所有员工工资都要高的员工信息

方法一:使用all，all代表所有

     SQL>select  * from emp where sal>all(select sal fromemp where deptno=20);

方法二:使用max函数

     SQL>select* from emp where sal>(select max(sal) from emp where deptno=20);

 

2.查询出与SMITH同一个部门，且工作岗位相同的员工信息，不包含SMITH

//使用普通子查询

SQL> select * from emp where (job=(selectjob from emp where ename='SMITH') and deptno=(select deptno from emp whereename='SMITH')) and ename!='SMITH';

 

//使用多列子查询

SQL> select * from emp where(deptno,job)=(select deptno,job from emp where ename='SMITH') andename!='SMITH';

 

v  from子查询

1. 查询出高于自己部门平均工资的员工姓名，工资，部门编号，部门平均工资

 selecte.ename,e.sal,a.deptno,a.avg_sal from emp e,(select deptno,avg(sal) avg_salfrom emp group by deptno) a where e.deptno=a.deptno and e.sal>a.avg_sal;

 

v  外连接查询

a. 左外连接 left outer join/left join

       左边的表不加限制

       1. 所有员工及对应部门的记录，包括没有对应部门编号depno的员工记录

       select e.ename,e.deptno,d.dname from emp1e left join dept1 d one.deptno = d.deptno;

       或者：

       select e.ename,e.deptno,d.dname from emp1e, dept1 d where e.deptno = d.deptno(+);

                    

b. 右外连接

右边的表不加限制

       select * from emp e right join dept don  e.deptno = d.deptno;

       或者：select e.*,d.* from emp e,dept d where e.deptno(+) = d.deptno;

                    

c. 全外连接

   左右两表都不加限制  

selecte.ename,e.deptno "emp部门编号", d.deptno "dept部门编号", d.dname from emp1 e ful

louter join dept1 d on e.deptno = d.deptno;

 

注意：用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。

 

v  合并查询

union,unionall，intersect，minus

并      并       交              差

1. 查询出，工资>=2500 或者工作为MANAGER的员工信息

(select * from emp where sal>=2500) union (select * from emp where job='MANAGER');

 

v  Oracle分页查询

1.查询出emp表中前5名员工信息

  select emp.*,rownum from emp whererownum<=5;

2.查询出emp表中工资排在第3—5的员工信息,不考虑奖金

 select * from (select e.*,rownum rn from(select * from emp order by sal desc) e where rownum<=5) where rn>=3;