河南建设信息港网站查询,百度点击软件名风,poedit2 汉化wordpress,手机网站幻灯片代码4多表关联查询
4.1表的别名
①在多表关联查询时#xff0c;如果多个表之间存在同名的列#xff0c;则必须用表名限定列的引用如dept.deptno,emp.deptno
②为使语句简洁#xff0c;使用表别名#xff0c;表别名在from子句中定义如 emp e
③表别名一经定义#xff0c;在整…4多表关联查询
4.1表的别名
①在多表关联查询时如果多个表之间存在同名的列则必须用表名限定列的引用如dept.deptno,emp.deptno
②为使语句简洁使用表别名表别名在from子句中定义如 emp e
③表别名一经定义在整个查询语句中就只能用表别名而不能用表名 emp.ename × e.ename √
④表别名只在所定义的查询语句中有效 4.2内连接直连-只显示关联到的结果会遗失部分数据
####
select d.dormno, s.name
from dorm d
[inner]join student s--默认是inner内查询不用写
on d.ids.dormid;
--用相同的ID来进行表之间连接####
select b.buildname,d.dormno,s.namefrom building bjoin dorm don b.id d.buildidjoin student son d.id s.dormid;
--三个及以上连接####
select d.dormno, s.namefrom dorm djoin student son d.id s.dormidwhere s.gender ;--条件语句放在后面####
内连接的另一种写法了解即可千万别用
select e.ename,d.dname,e.salfrom emp e,dept d--省去了join on 语句where sal3000;--忘记写where子句会导致笛卡尔积任意关联会导致非常严重的数据库崩溃--笛卡尔积两个表做任意关联无关联条件导致数据暴增
4.3外连接可以返回不相关的数据行不必须有条件语句
1左连接以左边的为主表显示所有行的结果以右边的为从表只显示关联的结果
select e.ename,d.dname,e.comm
from dept d
left join emp e
on e.deptnod.deptno;--此处应该在建表时用id而不用no####
select s.name,d.dormno
from student s, dorm d
where s.dormidd.id();
--左连接另种写法仅作了解千万别用会造成笛卡尔积数据库崩溃或数据丢失
####
▲如果左连接的从表添加where条件则左连接失效
例查询所有人的姓名如果其他宿舍在2楼显示宿舍号
select s.name,d.dormno
from student s
left join dorm d
on d.ids.dormid
where d.floor2;--此处where应改为and把选择条件变成关联条件
2右连接
right用法和左连接一样
3全连接特殊情况才用一般不用显示左右全部数据如果关联则在同一行
full同上
4.4自然连接(几乎不用)
自然连接指在检索多个表时
oracle会将个表中的列和第二个表中具有相同名称的列进行自动连接
语法 natural join
select empno,ename,job,dname
from emp
natural join dept
where sal2000;
--由于自然连接要求表之间必须有相同名称的列这样容易在出现不可预知的错故几乎不用 4.5自连接一表两用
####
select em2.ename 上层管理者,em1.ename 下属员工
from emp em1
join emp em2
on em1.mgrem2.empno--关键在于找到对应关系
order by em1.mgr;####
如果左连接后使用自连接则自连接会失效
例
select d.dormno,s.name,s1.name
from student s
left join dorm d
on d.ids.dormid
left join student s1
on d.headnos1.stuno;
--仅作语法示例
4.6交叉连接(几乎不用会造成笛卡尔积)
语法 cross join
select count(*) --计算查询结果的行数
from dept
cross join emp;
--执行结果是一个笛卡尔积等同于select count(*)
from dept,emp;
--执行结果是一个笛卡尔积
5子查询
又称嵌套查询在执行数据操作时
某个操作要依赖另一个select语句的查询结果
可以select语句嵌入该语句中注意
①子查询子句必须用括号括起来
②子查询不能包括order by
③子查询允许多层不超过255行嵌套
④子查询比多表关联查询更灵活、功能更强大、更容易理解但效率更低 5.1单行子查询
指子查询语句的返回结果只有一行
当在where子句中引用单行子查询时可以用单行比较运算符 ①关联子查询在select语句查询结果中
select s.name,s.gender,s.groupno,(select avg(t.age)from student t)--此处的平均年龄只作为常量不是聚合函数
from student s;
--查询每个人的姓名、年龄和班级平均年龄②关联子查询在条件语句中
select empno, ename, sal--外查询from empwhere sal (select min(sal) from emp)--括号内是内查询and sal (select max(sal) from emp);
--查询emp表中不是高或低工资的员工编号、姓名、工资
5.2多行子查询
指子查询语句的返回结果不止一行
当在where子句中引用多行子查询时必须用多行比较符in any all
1.1in运算符
在多行子查询中使用in运算符时
外查询会尝试与子查询结果中任何一个结果进行匹配
只要有一个匹配成功则外查询会返回当前的检索记录
select empno,ename,job
from emp
where deptno in
(select deptno from dept where dnameSALES);
--查询emp表中不是销售部门的员工信息
1.2exists运算符
exists存在前面可以加not--查询有预备党员的小组
select s.groupnofrom student swhere s.groupno in select t.groupnofrom student twhere t.political 预备党员);
select distinct s.groupnofrom student swhere exists (select t.groupnofrom student twhere t.political 预备党员and s.groupno t.groupno);
--查询没有预备党员的小组
select distinct s.groupnofrom student swhere not exists (select t.groupnofrom student twhere t.political 预备党员and s.groupno t.groupno);工作中常用 exists代替 in速度更快in关联子查询先子查询全部再主查询速度慢但容易想exists关联子查询先主查询一条一条查询再子查询子查询不需要查询全部速度更快
①将原 SQL中 in改为 exists
②在子查询中添加条件原子查询的结果原主查询中 in的前面一致 2any运算符
any运算符必须与单行操作符结合使用
并且返回行只要匹配子查询的任何一个结果即可
select deptno,ename,sal
from emp
where
salany (select sal from emp where deptno30)
and deptno30;--and与sal语句是并列的
--查询emp表中工资大于30号部门的任意一个员工工资的其他部门的员工信息
--实质上查询emp表中工资大于30号部门的低的一个员工工资的其他部门的员工信息
3all运算符
all运算符必须与单行操作符结合使用
并且返回行必须匹配子查询的所有结果
select deptno,ename,sal
from emp
where
salall (select sal from emp where deptno30);
----查询emp表中工资大于30号部门的所有员工工资的其他部门的员工信息
--与any相比较
5.3多列子查询
select *
from student s
where (s.age,s.gender)(select t.age,t.gender from student t where name张简简);
--查询年龄和性别都和张简简相同的人
--注意s.age,s.gender和t.age,t.gender前后顺序要一致 5.4关联子查询
在单行或多行子查询中内查询和外查询是分开执行的
外查询仅仅使用内查询的终结果在一些特殊需求的子查询中内查询和外查询相互关联
被称为关联子查询①关联子查询在select语句查询结果中
select s.name,s.gender,s.groupno,(select avg(t.age)from student t where t.groupnos.groupno)
from student s;
--使用了两个表别名
--不需要使用分组函数
--例 select avg(t.age) from student t where t.groupno*1即可查询1组的平均年龄②关联子查询在条件语句中
select s.name,s.age
from student s
where s.age(select avg(t.age) from student t where t.groupnos.groupno);
--查询比小组平均年龄大的人
5.5子查询难点用法
--查询A型血人数比B型血人数多的宿舍
select ssaxx.dormno
from (select d.dormno,count(1) ssaxxrs from student s join dorm d on s.dormidd.id where s.xoA group by d.dormno) ssaxx--把A血型的寝室和人数看作一个表
join (select d.dormno,count(1) ssbxxrs from student s join dorm d on s.dormidd.id where s.xoB group by d.dormno) ssbxx--把B血型的寝室和人数看作一个表
on ssaxx.dormnossbxx.dormno
where ssaxx.ssaxxrsssbxx.ssbxxrs;
--当问题不清晰的时候拆解成多个表来解决问题即可 6 开窗函数
也称分析函数窗就是范围在over子句所限定的范围内进行查询速度优于子查询。 6.1 partition by
select s.name,s.groupno,s.age,max(s.age) over(partition by s.groupno) age1
from student s;
--查询所有同学姓名、组号、年龄和其最大年龄
--partition by 类似于 group by具有分组的作用
--【max(s.age) over(partition by s.groupno) age1】是一个整体使用多个聚合函数时每个要单独加over语句
--由于受开窗范围的影响别名此处为age1要放在 order by后
6.2 order by
select s.name,s.groupno,s.age,max(s.age)over(partition by s.groupno order by s.age)
from student s;
--查询所有同学姓名、组号年龄和窗口内大年龄
--order by不仅仅具有排序的作用只有 order by 没有 partition by 则仅有排序功能 会使窗口发生变化窗口变化为从 partition by选定的窗口的行数据开始到与被查询主体 order by后的列名此处为s.age相同值的所有数据行为止不明白就运行代码试试
6.3 排序类开窗函数 row_number()、 rank()、dense_rank()
在窗口范围内对 order by 后指定的数据进行排序select s.name,s.groupno,s.age,row_number() over(partition by s.groupno order by s.age)
from student s;
--相同年龄也分先后顺序如排序 1 2 3 4 5年龄 18 19 19 19 20
select s.name,s.groupno,s.age,rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第五如排序 1 2 2 2 5年龄 18 19 19 19 20
select s.name,s.groupno,s.age,dense_rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第三如排序 1 2 2 2 3年龄 18 19 19 19 20
6.4 偏移类开窗函数 lag() lead()
select s.name,s.groupno,s.age,lag(s.age) over(partition by s.groupno order by s.age)
from student s;
--lad(参数1参数2参数3)参数1列名所处位置的数据往上偏移参数2个位置如果偏移后数据为空则用参数3的数据填充
--lead(参数1参数2参数3)往下偏移
6.5 了解部分
1first_value() last_value()
--不可和聚合函数同用
select s.name,s.groupno,s.age,first_value(s.age) over(partition by s.groupno order by s.age),last_value(s.name) over(partition by s.groupno order by s.age)
from student s;
--注意要写逗号
--first_value(col_name)返回该窗口中某列的个值
--last_value(col_name)返回该窗口中某列的后一个值 2影响开窗范围的参数(range between 参数1 and 参数2)可以有聚合函数必须有order by
range between 参数1 and 参数2
在原来的窗口范围内再进行选定select q.realname,q.groupno,q.age,max(q.age) over(partition by q.groupno order by q.agerange between 参数1 and 参数2)
from qqinfo q;参数可以替换为以下
unbounded preceding 组内首行
current row 当前行
unbounded following 组内末行
1 preceding 组内当前行前面1行
1 following 组内当前行后面1行
range 值比较--不了解
rows 行比较--不了解
7 其他
7.1 查询中的集合操作
两个集合间 交 并 差
1并 union 自带去重效果去重必带排序oracle中去重和排序都非常慢union all 不去除重复内容执行速度更快
例select s.name from student sunionselect e.name from emp e
此外要注意多列同时运算的情况对应列如s.age和e.age的属性和类型要一致select s.names.age from student sunionselect e.name,e.age from emp e
2交 intersect
3差 minus
7.2 case when
查询结果根据类别不同查询方式随之不同
select s.name--逗号不能少case--开始when s.gender1 then 男生when s.gender then 女生--也可以只有一个 when then语句 else 其他end--结束
from student s;需要注意的是 case when 是顺序执行的
如果前面的条件包含了后面的条件
则后面的条件
如select s.name,casewhen s.score60 then 及格when s.score80 then else 不及格endfrom student s;--则60分以上的都是及格即使是100也是及格而不是当 case when 中的条件是确定值即用等号时可等价于decode如select s.name,--逗号不能少decode(s.gendere,1,男生,,女生,其他)from student s;
7.3 行列相互转换
1行转列【常面】
将同一列内容分成多列
① group by
② case when
③ 聚合函数例查询班内全部人数男生数量女生数量select count(1),count(case when s.gender1 then 是 end) nans,count(case when s.gender then 是 end) nvsfrom student s;
例查询各组全部人数男生数量女生数量select s.groupnocount(1),count(case when s.gender1 then 是 end) nans,count(case when s.gender then 是 end) nvsfrom student sgroup by s.groupno; 2列转行【常面】
将不同列的内容汇总到同一列
用 union[all]并-操作select name,男 gender from stul where nans1
union
select name,女 from stu1 where nvs1;
