Oracle 高级查询

--分组函数	
	--统计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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值