--分组函数
--统计10,20,30 部门的平均工资大于2000的最大工资,最小工资,平均工资
--1、拆分成不同的组:group by:把具有指定列相同值的指定列分为一组
select deptno , sal from scott.emp group by depton;--不可以,deptno的记录数,与sal的记录数不一样
--分组的列不能与没分组的列一起显示
select deptno from scott.emp group by depton;
--2、统计分组数据::
select deptno , max(sal) 最大工资,min(sal) 最小工资, round(avg(sal),2) 平均工资 from scott.emp group by deptno; --可以,结果数量一致,分组列可以与聚合函数一起使用
--3、过滤掉不要的数据记录
select deptno , max(sal) 最大工资,min(sal) 最小工资, round(avg(sal),2) 平均工资 from scott.emp where deptno in (10,20,30) group by deptno;
--查询结构
select column_name [,...] from table_name where column_value [and | or ...] group by column_name [...] order by column_name [...];
--having 筛选
--select deptno ,count(1) from scott.emp group by deptno where sal < 1000; --不可以 where在group by 执行之前执行
--分组后的数据不能使用where 进行过滤,使用having对分组后的数据进行筛选;
select deptno ,count(1) from scott.emp group by deptno having avg(sal) > 2000;
--select ,from, where ,group by ...[having..] ,order by 执行的先后顺序?
select rownum ,e.* from scott.emp e; --因为别名可以使用,所以from 先于select执行
select rownum rn,e.* from scott.emp e where rn < 6; --因为别名不可以使用,所以where 先于select执行
select deptno dn from scott.emp group by dn; --别名不起作用,group by 先于select执行
--from > where > group by > select
select sal s, e.* from scott.emp e order by s; --别名起作用,select先于order by 执行
--from > where > group by > select > order by
--多表查询
--连接查询: 将多张表连接成一张表操作
--内连接:表与表处于同等的位置
在scott的用户中有:emp(员工表), dept(部门表) ,salgrade(工资等级表)
--emp(员工表): EMPNO(员工编号) ENAME (员工姓名) JOB (工作) MGR (上司编号)HIREDATE (入职日期) SAL (工资) COMM (资金) DEPTNO(部门编号)
--dept(部门表):DEPTNO(部门编号) DNAME (部门名称) LOC(地址)
--salgrade(工资等级表):GRADE(工资级别) LOSAL(低) HISAL(高)
select * from emp, dept;
select * from emp, dept where emp.deptno = dept.deptno; //先产生笛卡尔积,然后再筛选数据
[inner] join...on...(子表放左边,主表放右边)
select * from emp e join dept d on e.deptno = d.deptno; --使用主表加入字表效率会好一些,查询次数会少一些。
select * from dept d join emp e on e.deptno = d.deptno;
--scott 在哪里上班
select e.ename, d.loc from emp e join dept d on e.deptno = d.deptno where e.ename = 'SCOTT';
select e.ename, d.loc from emp e join dept d on e.ename = 'SCOTT' and e.deptno = d.deptno; --在筛选时过滤,效率更高;
--scott 领导是谁
select e1.ename 员工,e2.ename 上司 from emp e1 join emp e2 on e1.ename = 'SCOTT' and e1.mgr = e2.empno;
--外连接:区分主表(dept)和从表(emp)
--含有(+)从表
select * from emp e, dept d where e.deptno = d.deptno(+); --左外连接
select d.* from emp e, dept d where e.deptno(+) = d.deptno and e.empno is NULL ; --右外连接
--左外连接:([out] left join... on ...)
--没有人的部门
select d.* from dept d left join emp e on e.deptno = d.deptno where e.empno is NULL ;
--有外连接:
select d.* from emp e right join dept d on e.deptno = d.deptno where e.empno is NULL ;
--完全外连接:
--子查询:在sql语句中嵌套一个select语句就是子查询
--返回一行一列
--scott 在哪里上班
select loc 上班地点 from dept where deptno = (select deptno from emp where ename = 'SCOTT');
--scott 领导是谁
select ename 上司 from emp where empno = (select mgr from emp where ename = 'SCOTT');
--返回多行一列
--公司有人的部门
--子查询先于主查询执行
select * from dept where deptno = (select deptno from emp); --子查询结果含有多个结果,不能用= 要用 in
select * from dept where deptno in (select deptno from emp);
--使用exists
select * from dept d where exists (select 1 from emp e where e.deptno = d.deptno);
--没有员工的部门
select * from dept where deptno not in (select deptno from emp);
--使用not exists
select * from dept d where not exists (select 1 from emp e where e.deptno = d.deptno);
--返回多行多列
--最高工资的员工
select * from emp where sal in (select max(sal) from emp);
select * from emp where rownum = 1 order by sal desc;
select * from (select * from emp order by sal desc) where rownum = 1;
--查询工资前三名的员工
select * from (select * from emp order by sal desc) where rownum in (1,2,3);
select * from (select * from emp order by sal desc) where rownum <= 3;
--查询 4~6 的员工信息;
分页的前提: 第几页: pageNum ,每页的数量: 3 pageSize
(pageNum - 1) * pageSize < < pageNum * pageSize;
一页: 1~3
一页: 4~6
一页: 7~9
select * from (select e.*, rownum rn from (select * from emp order by sal desc) e where rownum <= 6) where rn > 3;
---------------------------------
--分页语句:
select * from
(select e.*, rownum rn from
(select * from emp order by sal desc) e
where rownum <= &pageNum * &pageSize where rn > (&pageNum - 1) * &pageSize);
问题:获得Person表的表结构
creat table new_person as select * from person; --拷贝表的结构,数据和检查约束
问题:获得Person表的表结构不要数据
creat table new_person as select * from person where 1 != 1; --拷贝表的结构 1 != 1 为FALSE 不能返回数据
备份表:
1、重命名表
rename person to person_bak;
2、拷贝结构和数据
create table person as select * from person_bak;
使用子查询插入多条数据:
alter table person add address varcher2(50);
insert into person(id, name,address)
select empno, ename, '湖南衡阳' from scott.emp where depno = 10 union
select empno, ename, '湖南长沙' from scott.emp where depno = 20 union
select empno, ename, '湖南岳阳' from scott.emp where depno = 30;
Oracle 高级查询
最新推荐文章于 2022-10-28 16:08:04 发布