sql查询示例笔记(多表查询）

emp(empid,empname,sex,salary,mgr,job,comm,deptid)  员工表（员工ID，员工姓名，性别，工资，上级员工编号mgr，职位，补助，部门ID）

grade(grade,lowsal,hisal)    工资等级表（等级，最低工资，最高工资）

dept(deptid,deptname)    部门表（部门ID，部门名称）

--e.g.查询员工的姓名，工资，工资等级

select a1.empname,a1.salary,a2.grade from emp a1,salary a2 where a1.salary between a2.lowsal and a2.hisal;

--e.g.查询雇员名，雇员工资，及所在部门名称，并按部门排序

select a1.empname,a1.salary,a2.deptname from emp a1,dept a2 where a1.deptid=a2.deptid order by a2.deptid;

--order by 排序

--group by 分组 

自连接实例：

e.g.查询员工姓名，员工领导姓名

select worker.empname,boss.empname from emp worker,emp boss where worker.mgr=boss.empid;

e.g.显示员工”Ford“的领导姓名

select worker.empname,boss.empname from emp worker,emp boss where empname='Ford' and worker.mgr=boss.empid;

单选子查询示例：

e.g.显示与”Smith"同一部门的所有员工信息

select *  from emp where deptid=(select deptid from emp where empname='smith');

e.g.查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

select empname,job,salary,deptid from emp where job in (select distinct job from emp where deptid='10');

distinct 关键字取唯一不同的值(过滤掉重复记录）

e.g.显示工资比部门30所有员工工资高的员工的姓名、工资、部门名

select  a1.empname,a1.salary,a2.deptname  from emp a1,dept a2 where a1.deptid=a2.deptid and a1.salary>all (select a1.salary from a1 where a1.deptid=30);

select  a1.empname,a1.salary,a2.deptname  from emp a1,dept a2 where a1.deptid=a2.deptid and a1.salary> (select max(salary) from emp where deptid=30);  --效率更高

e.g.显示工资比部门30任一员工工资高的员工的姓名、工资、部门名

select  a1.empname,a1.salary,a2.deptname  from emp a1,dept a2 where a1.deptid=a2.deptid and a1.salary>any(select a1.salary from a1 where a1.deptid=30);

select  a1.empname,a1.salary,a2.deptname  from emp a1,dept a2 where a1.deptid=a2.deptid and a1.salary> (select min(salary) from emp where deptid=30);  --效率更高

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

select empname from emp where (job,deptid)=(select job,deptid from emp where empname='smith');

e.g. 查询工资比部门平均工资高的员工姓名、工资、部门号、部门平均工资

select a1.empname,a1.salary,a1.deptid,a2.mysal from emp a1,(select deptid,AVG(salary) mysal from emp group by deptid) a2 where a1.deptid=a2.deptid and a1.salary>a2.mysal;

--(select deptid,AVG(salary) mysal from emp group by deptid) a2  将子查询当作一张子表

--给表取别名不能加as 给列取别名可以加as,也可以不加

oracle分页

1.使用rownum分页

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

--rownum (oracle分配的行号）

查询6~10行记录

1）查询行号<=10的记录

select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;

--rownum只能出现一次，出现2次无效

2）在上面的基础上查询行号>=4的记录

select * from (select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6; （效率高）

如果要指定要查询列那么只需修改最里层的查询

上面查询只需要员工的名字和薪水，如下：

select * from (select * from (select a1.*,rownum rn from (select empname,salary from emp) a1 where rownum<=10) where rn>=6;

排序也只需要修改最里层的查询

select * from (select * from (select a1.*,rownum rn from (select empname,salary from emp order by salary desc) a1 where rownum<=10) where rn>=6;

--order by salary desc,按工资降序

1.按rowid来分（效率最高）

2.按分析函数来分（效率低）

3.按rownum来分（效率中等）

查询表emp的总记录数:

select count(*) from emp;

在原表的基础上创建一张新表：

create table myemp (id,empname,sal) as select empid,empname,salary from emp;

合并查询：(并集）

select empname,salary,job from emp where sal>2500 union select empname,salary,job from emp where job="MANAGER";

union all （不清除重复记录）

minus（差集）

intersect(交集）

create table emp1(eid number(4),myname varchar2(50),myDept number(5));

insert into emp1(eid,myname,myDept) select empid,empname,deptid from emp where deptid=10;

更新员工scott的岗位、工资、补助与smith员工一样

update emp set (job,salary,comm) =（select job,salary,comm from emp where empname='SMITH') where empname='SCOTT';