赞
踩
- ①在多表关联查询时,如果多个表之间存在同名的列,则必须用表名限定列的引用
- 如dept.deptno,emp.deptno
- ②为使语句简洁,使用表别名,表别名在from子句中定义如 emp e
- ③表别名一经定义,在整个查询语句中就只能用表别名,而不能用表名 emp.ename × e.ename √
- ④表别名只在所定义的查询语句中有效
- ####
- select d.dormno, s.name
- from dorm d
- [inner]join student s--默认是inner内查询,不用写
- on d.id=s.dormid;
- --用相同的ID来进行表之间连接
-
- ####
- select b.buildname,d.dormno,s.name
- from building b
- join dorm d
- on b.id = d.buildid
- join student s
- on d.id = s.dormid;
- --三个及以上连接
-
- ####
- select d.dormno, s.name
- from dorm d
- join student s
- on d.id = s.dormid
- where s.gender = ;
- --条件语句放在后面
-
- ####
- 内连接的另一种写法(了解即可,千万别用)
- select e.ename,d.dname,e.sal
- from emp e,dept d--省去了join on 语句
- where sal>=3000;--忘记写where子句会导致笛卡尔积任意关联,会导致非常严重的数据库崩溃
- --笛卡尔积:两个表做任意关联,无关联条件,导致数据暴增
(1)左连接(以左边的为主表,显示所有行的结果,以右边的为从表,只显示关联的结果)
- select e.ename,d.dname,e.comm
- from dept d
- left join emp e
- on e.deptno=d.deptno;--此处应该在建表时用id而不用no
-
- ####
- select s.name,d.dormno
- from student s, dorm d
- where s.dormid=d.id(+);
- --左连接另种写法,仅作了解,千万别用,会造成笛卡尔积数据库崩溃或数据丢失
- ####
- ▲如果左连接的从表添加where条件,则左连接失效
- 例:查询所有人的姓名,如果其他宿舍在2楼,显示宿舍号
- select s.name,d.dormno
- from student s
- left join dorm d
- on d.id=s.dormid
- where d.floor=2;--此处where应改为and,把选择条件变成关联条件
(2)右连接
right用法和左连接一样
(3)全连接(特殊情况才用,一般不用,显示左右全部数据,如果关联则在同一行)
full同上
- 自然连接指在检索多个表时
- oracle会将个表中的列和第二个表中具有相同名称的列进行自动连接
- 语法: natural join
- select empno,ename,job,dname
- from emp
- natural join dept
- where sal>2000;
- --由于自然连接要求表之间必须有相同名称的列,这样容易在出现不可预知的错,故几乎不用
- ####
- select em2.ename 上层管理者,em1.ename 下属员工
- from emp em1
- join emp em2
- on em1.mgr=em2.empno--关键在于找到对应关系
- order by em1.mgr;
-
- ####
- 如果左连接后使用自连接,则自连接会失效
- 例:
- select d.dormno,s.name,s1.name
- from student s
- left join dorm d
- on d.id=s.dormid
- left join student s1
- on d.headno=s1.stuno;
- --仅作语法示例
- 语法: cross join
- select count(*) --计算查询结果的行数
- from dept
- cross join emp;
- --执行结果是一个笛卡尔积
-
- 等同于
-
- select count(*)
- from dept,emp;
- --执行结果是一个笛卡尔积
- 又称嵌套查询
-
- 在执行数据操作时
- 某个操作要依赖另一个select语句的查询结果
- 可以select语句嵌入该语句中
-
- 注意:
- ①子查询子句必须用括号括起来
- ②子查询不能包括order by
- ③子查询允许多层(不超过255行)嵌套
- ④子查询比多表关联查询更灵活、功能更强大、更容易理解,但效率更低
- 指子查询语句的返回结果只有一行
- 当在where子句中引用单行子查询时可以用单行比较运算符(= > < >= <= <>)
-
- ①关联子查询在select语句(查询结果中)
- select s.name,s.gender,s.groupno,
- (select avg(t.age)from student t)--此处的平均年龄只作为常量,不是聚合函数
- from student s;
- --查询每个人的姓名、年龄和班级平均年龄
-
- ②关联子查询在条件语句中
- select empno, ename, sal--外查询
- from emp
- where sal > (select min(sal) from emp)--括号内是内查询
- and sal < (select max(sal) from emp);
- --查询emp表中不是高或低工资的员工编号、姓名、工资
- 指子查询语句的返回结果不止一行
- 当在where子句中引用多行子查询时必须用多行比较符(in any all)
(1.1)in运算符
- 在多行子查询中使用in运算符时
- 外查询会尝试与子查询结果中任何一个结果进行匹配
- 只要有一个匹配成功则外查询会返回当前的检索记录
- select empno,ename,job
- from emp
- where deptno in
- (select deptno from dept where dname<>'SALES');
- --查询emp表中不是销售部门的员工信息
(1.2)exists运算符
- exists存在(前面可以加not)
-
- --查询有预备党员的小组
- select s.groupno
- from student s
- where s.groupno in
- select t.groupno
- from student t
- where t.political = '预备党员');
- select distinct s.groupno
- from student s
- where exists (select t.groupno
- from student t
- where t.political = '预备党员'
- and s.groupno = t.groupno);
- --查询没有预备党员的小组
- select distinct s.groupno
- from student s
- where not exists (select t.groupno
- from student t
- where t.political = '预备党员'
- and s.groupno = t.groupno);
-
- 工作中常用 exists代替 in(速度更快)
- in关联子查询先子查询全部
- 再主查询(速度慢,但容易想)
- exists:关联子查询先主查询一条一条查询
- 再子查询(子查询不需要查询全部,速度更快)
- ①将原 SQL中 in改为 exists
- ②在子查询中添加条件
- (原子查询的结果=原主查询中 in的前面一致)
(2)any运算符
- any运算符必须与单行操作符结合使用
- 并且返回行只要匹配子查询的任何一个结果即可
- select deptno,ename,sal
- from emp
- where
- sal>any (select sal from emp where deptno=30)
- and deptno<>30;--and与sal语句是并列的
- --查询emp表中工资大于30号部门的任意一个员工工资的其他部门的员工信息
- --实质上查询emp表中工资大于30号部门的低的一个员工工资的其他部门的员工信息
(3)all运算符
- all运算符必须与单行操作符结合使用
- 并且返回行必须匹配子查询的所有结果
- select deptno,ename,sal
- from emp
- where
- sal>all (select sal from emp where deptno=30);
- ----查询emp表中工资大于30号部门的所有员工工资的其他部门的员工信息
- --与any相比较
- 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前后顺序要一致
- 在单行或多行子查询中内查询和外查询是分开执行的
- 外查询仅仅使用内查询的终结果
-
- 在一些特殊需求的子查询中内查询和外查询相互关联
- 被称为关联子查询
-
- ①关联子查询在select语句(查询结果中)
- select s.name,s.gender,s.groupno,
- (select avg(t.age)from student t where t.groupno=s.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.groupno=s.groupno);
- --查询比小组平均年龄大的人
- --查询A型血人数比B型血人数多的宿舍
- select ssaxx.dormno
- from (select d.dormno,count(1) ssaxxrs
- from student s
- join dorm d
- on s.dormid=d.id
- where s.xo='A'
- group by d.dormno) ssaxx--把A血型的寝室和人数看作一个表
- join (select d.dormno,count(1) ssbxxrs
- from student s
- join dorm d
- on s.dormid=d.id
- where s.xo='B'
- group by d.dormno) ssbxx--把B血型的寝室和人数看作一个表
- on ssaxx.dormno=ssbxx.dormno
- where ssaxx.ssaxxrs>ssbxx.ssbxxrs;
- --当问题不清晰的时候拆解成多个表来解决问题即可
也称分析函数窗,就是范围在over子句所限定的范围内进行查询,速度优于子查询。
- 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后
- 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)相同值的所有数据行为止
- (不明白就运行代码试试)
- 在窗口范围内对 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
- 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)
- 往下偏移
(1)first_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 行比较--不了解
- 两个集合间 交 并 差
- (1)并
- union (自带去重效果)
- (去重必带排序)
- (oracle中去重和排序都非常慢)
- union all (不去除重复内容)
- (执行速度更快)
- 例:
- select s.name from student s
- union
- select e.name from emp e
- 此外要注意多列同时运算的情况:
- 对应列如s.age和e.age的属性和类型要一致
- select s.name,s.age from student s
- union
- select e.name,e.age from emp e
- (2)交( intersect )
- (3)差 ( minus )
- 查询结果根据类别不同,查询方式随之不同
- select s.name,--逗号不能少
- case--开始
- when s.gender=1 then '男生'
- when s.gender= then '女生'--也可以只有一个 when then语句
- else '其他'
- end--结束
- from student s;
-
- 需要注意的是 case when 是顺序执行的
- 如果前面的条件包含了后面的条件
- 则后面的条件
- 如:
- select s.name,
- case
- when s.score>60 then '及格'
- when s.score>80 then ''
- else '不及格'
- end
- from student s;
- --则60分以上的都是及格,即使是100也是及格而不是
-
- 当 case when 中的条件是确定值(即用等号=)时
- 可等价于decode如:
- select s.name,--逗号不能少
- decode(s.gendere,1,'男生',,'女生','其他')
- from student s;
(1)行转列【常面】
- 将同一列内容分成多列
- ① group by
- ② case when
- ③ 聚合函数
-
- 例(查询班内全部人数,男生数量,女生数量):
- select count(1),
- count(case when s.gender=1 then '是' end) nans,
- count(case when s.gender= then '是' end) nvs
- from student s;
- 例(查询各组全部人数,男生数量,女生数量):
- select s.groupno,
- count(1),
- count(case when s.gender=1 then '是' end) nans,
- count(case when s.gender= then '是' end) nvs
- from student s
- group by s.groupno;
(2)列转行【常面】
- 将不同列的内容汇总到同一列
- 用 union[all](并-操作)
-
- select name,'男' gender from stul where nans=1
- union
- select name,'女' from stu1 where nvs=1;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。