JAVA入门到精通-第61讲-复杂查询

--笛卡尔积：

不带任何条件会出现笛卡尔积；

SQL是从右向左执行；

从右挑选一个条件和左边的表的每一条记录进行匹配；

 

这时，只会保留你需要的记录；其他记录会被删除；

 

找到关联，会再次拆选，再次筛选： emp.deptno=dept.deptno

否则，会出现笛卡尔集；

 

 

--如果两张表都有相同名字的字段，则需要带表名（别名）

 

from 后面取表的别名；

 

select  ?, ?, ?

     from  emp, dept 

     where 

 

 

--自连接（同一张表上的连接查询）

 

把自个儿的名字显示出来？

 

ford 和他上级 jones在同一张表，共用一个ename字段；

 

所有的信息在同一张表中；

找的信息又在自身的表当中；

 

--每个员工---上级的名字--

把emp表看成两张表，认为有两张表取别名：

emp  worker

emp  boss

对这两张表进行联合查询；

 

select worker.ename , boss.ename

from  emp  worker, emp  boss

 

--普通查询是内连接

--左外连接/右外连接

 

--子查询

 

--单行子查询：返回的结果是单行；

 

--多行子查询:返回多行数据的子查询-in

 

--from子句中使用子查询

 

表的复杂查询--多表查询

说明：

    多表查询是指基于两个或两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足你的需求，(如显示sales部门位置和其员工的姓名)，这种情况下需要使用到(dept表和emp表)

 

--多表查询

--如果多张表都有相同名字的字段，则需要带表名(别名)

--显示sales部门位置和其员工的姓名

select emp.ename"员工姓名",dept.dname"部门名称",dept.loc"部门所在地" from emp,dept where dept.dname='sales' and emp.deptno=dept.deptno

 

--显示雇员名，雇员工资及所在部门的名字

select emp.ename"雇员名",emp.sal"雇员工资",dept.dname"部门名称" from emp,dept where emp.deptno=dept.deptno

 

--如何显示部门号为10的部门名、员工名和工资

select emp.deptno"部门号",dept.dname"部门名称",emp.ename"员工名称",emp.sal"工资" from emp,dept where emp.deptno=dept.deptno and emp.deptno=10

 

--显示雇员名、雇员工资及所在部门的名字并按部门排序

select e.ename"雇员名字",e.sal"工资",d.dname"部门名称" from emp e,dept d where e.deptno=d.deptno order by d.dname

 

表的复杂查询--多表查询

自连接

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

例：

--自连接

--显示某个员工的上级领导的姓名，比如显示"ford"的上级

select (select ename from emp where ename='ford')"员工姓名",ename"上级领导" from emp where empno=(select mgr from emp where ename='ford')

 

--显示公司每个员工姓名和他的上级的名字

--分析，把emp表看成两张表分别是worker/boss

--外连接(左外连接、右外连接)

select worker.ename"员工名字",boss.ename"领导名字" from emp worker,emp boss where worker.mgr=boss.empno

 

表的复杂查询--子查询

什么是子查询

子查询是批嵌入在其它sql语句中的select语句，也叫嵌套查询

 

单行子查询

单行子查询是指只返回一行数据的子查询语句

--如何显示与smith同一部门的所有员工？

select deptno"部门号",ename"员工名字" from emp where deptno=(select deptno from emp where ename='smith')

 

多行子查询

多行子查询指返回多行数据的子查询

--如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

select * from emp where job in(select distinct job from emp where deptno=10)

在from子句中使用子查询

--如何显示高于部门平均工资的员工名字、薪水、部门的平均工资

--分析：1、首先要知道各个部门的平均工资

select avg(sal)"部门平均工资",deptno from emp group by deptno

--2、把上面的查询结果当作一个临时表对待

select e.ename"员工名字",e.sal"薪水",temp.myavg"部门平均工资",e.deptno from emp e,(select avg(sal) myavg,deptno from emp group by deptno) temp where e.deptno=temp.deptno and e.sal>temp.myavg

 

在from子句中使用子查询

这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from子句中使用子查询时，必需给子查询指定别名

 

分面查询

按雇员的id号升序取出

--请显示第5个到第10个入职的雇员信息(按照入职的时间先后顺序查找)

--分析：1、显示第1个到第4个入职的雇员

select top 4 * from emp order by hiredate

--top后的数表示要取出几条记录

select top 6 * from emp where empno not in(select top 4 empno from emp order by hiredate) order by hiredate

 

--请显示第11个到13个入职的雇员信息

select top 3 * from emp where empno not in(select top 10 empno from emp order by hiredate) order by hiredate

 

--请显示第5个到9个入职的雇员信息(按薪水高低排序)

select top 5 * from emp where empno not in(select top 4 empno from emp order by sal desc) order by sal desc

 

用查询结果创建新表

这个命令是一种快捷的建表方法

select *(这里可以选择字段) into 另一个表名 from 表

 

--如何删除掉一张表重复记录

create table cat(

catId int,

catName varchar(40)

)

 

insert into cat values(1,'aa')

 

select * from cat

 

--1、把cat表的记录distinct后的结果，放到临时表中

select distinct * into #temp from cat

--2、把cat表的记录清空

delete from cat

--3、把临时表中的数据信息加入到cat表中

insert into cat select * from #temp

--4、删除临时表

drop table #temp