select distinct/列名/表达式/单行函数/组函数/
from 表名
where 条件(子查询)1 or 条件2 and 条件3
group by 列名
having 组函数的条件
order by 列名/列别名/表达式/组函数
select deptno, count(*) c
from emp_ning
group by deptno
--order by c;
order by 2;
--每个班的学生人数?
1 4
2 2
3 73
4 65
5 48
--把班级人数>50人的班级查出来
select class_id, count(*)
from t_student_ning
group by class_id
having count(*) > 3
order by 2;
--每个班的成绩比例加起来是否=100?
--百分之百
1 100
2 100
select class_id, sum(scale)
from t_assess_rule_ning
group by class_id;
--每个学生的总分?按总分由高到低排序
3 173
2 171
1 157
1 1 90
1 2 87
1 3 75
1 4 67
1 5 92
1 6 95
select student_id, sum(test_score)
from t_performance_ning
group by student_id
order by 2 desc;
一.子查询
--谁的总成绩比1号学生的总成绩高?
select student_id, sum(test_score)
from t_performance_ning
group by student_id
having sum(test_score) > (
select sum(test_score)
from t_performance_ning
where student_id = 1
);
--谁的薪水比公司的平均薪水低?
select ename, salary
from emp_ning
where salary < (select avg(nvl(salary,0))
from emp_ning);
--谁的薪水比本部门的平均薪水低?
--关联子查询
select ename, salary, deptno
from emp_ning x
where salary < (
select avg(nvl(salary,0))
from emp_ning
where deptno = x.deptno
);
--谁的薪水比同经理的员工平均薪水低?
select ename, salary, manager
from emp_ning x
where salary < (
select avg(nvl(salary,0))
from emp_ning
where manager = x.manager
);
张三 8000 10 1001
李四 5000 20 1001
王五 15000 30 1001
赵六 10000 10 1002
5000
10 12000
20 5000
30 8000
.....
--哪些员工是别人的经理?
exists: 子查询是否有结果返回
有 true
没有 false
select empno, ename
from emp_ning x
where exists (select 1
from emp_ning
where manager = x.empno);
--哪些人不是别人的经理?
select empno, ename
from emp_ning x
where not exists (select 1 from emp_ning where manager = x.empno);
--哪些部门没有员工?
--判断依据: 部门表的编码,没有出现在职员表的部门编码列中.
--使用 not exists
select deptno, dname, location from dept_ning x
where not exists (select 1 from emp_ning where deptno = x.deptno);
集合操作:
select deptno from dept_ning minus
select distinct deptno from emp_ning;
集合的合集: union / union all
集合的交集: intersect
select ename, salary, deptno
from emp_ning
where deptno = 10
intersect
select ename, salary, deptno
from emp_ning
where salary > 8000;
二.多表联合查询
select emp_ning.*, dept_ning.*
from emp_ning join dept_ning
on emp_ning.deptno = dept_ning.deptno;
select e.*, d.*
from emp_ning e join dept_ning d
on e.deptno = d.deptno;
select e.ename, d.dname, d.location
from emp_ning e join dept_ning d
on e.deptno = d.deptno;
--主键: Primary Key = PK
--列值是唯一的,不重复的
--主表 / 父表
--外键: Foreign Key = FK
--列值参照某个主键列值
--从表 / 子表
--查询学生的名字,科目,成绩
select stu.student_name, per.subject_id,per.test_score from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id;
select stu.*, per.*, sub.*
from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id join t_subject_ning sub on per.subject_id = sub.subject_i ;
--列出学生的姓名/科目名/成绩
select stu.student_name, sub.subject_name, per.test_score
from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id join t_subject_ning sub
on per.subject_id = sub.subject_id
--1班的学生成绩
select stu.student_name, sub.subject_name, per.test_score
from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id join t_subject_ning sub on per.subject_id = sub.subject_id where stu.class_id = 1;
--1班学生的成绩的总分,并排序
select stu.student_name,sum(per.test_score) total_score from t_student_ning stu join t_performance_ning per on stu.student_id = per.student_id where stu.class_id = 1 group by stu.student_nameorder by total_score desc;
张无忌 张三丰
郭芙蓉 张无忌
--列出员工的名字和他的经理名字
--自连接
select worker.ename, manager.ename from emp_ning worker join emp_ning manager on worker.manager = manager.empno;
update emp_ning set deptno = null where ename = 'tom';
--部门是null的员工不会被查出来
--没有员工的部门,也不会被查出来.
--内连接
select e.ename, d.dname from emp_ning e join dept_ning d on e.deptno = d.deptno;
--外连接: 左外连接, 右外连接
select e.ename, d.dname from emp_ning e left outer join dept_ning d on e.deptno = d.deptno;
select e.ename, d.dname from dept_ning d right outer join emp_ning e on e.deptno = d.deptno;
-- t1-驱动表
t1 left outer join t2
t2 right outer join t1
外连接的结果集 = 内连接的结果集 +
驱动表中在匹配表中没有对应记录的记录和 空值的组合
--部门表做驱动表
select e.ename, d.dname
from emp_ning e right outer join dept_ning d
on e.deptno = d.deptno;
--等价的语句,部门表做驱动表
select e.ename, d.dname
from dept_ning d left outer join emp_ning e
on e.deptno = d.deptno;
--left / right 外连接 都可以
--关键是谁做驱动表.
--全外连接: full outer join
select e.ename, d.dname
from emp_ning e full outer join dept_ning d
on e.deptno = d.deptno;
--哪些部门没有员工?
--1.关联子查询实现
select dname, location from dept_ning x where not exists ( select 1 from emp_ning where deptno = x.deptno)
--2.集合
select deptno from dept_ning minus
select distinct deptno from emp_ning;
--3.外连接
--where 匹配表的pk is null = 驱动表中匹配不上的记录.
--相当于过滤掉内连接的结果集.
select e.empno, e.ename, d.deptno, d.dname, d.location
from emp_ning e right outer join dept_ning d
on e.deptno = d.deptno
where e.empno is null;
Top-N分析
--薪水最高的三个人?
伪列: rownum
--测试rownum的作用
select rownum, empno, ename, salary
from emp_ning;
--希望:先排序,再取前三条.
--实际:先取前三条,再排序.
select empno, ename, salary
from emp_ning
where salary is not null
and rownum < 4
order by salary desc;
--先排序,再取前三条.
select * from( select empno, ename, salary from emp_ning where salary is not null order by salary desc) where rownum < 4;
--计算学生总分: 学生表,成绩表
select *
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_ning stu
join
t_performance_ning per
on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
order by total_score desc)
where rownum < 2;
--输入:班号, 输出:该班的最高分
create or replace function maxScore_ning(p_class_id number)
return number
is
v_total_score number;
begin
--v_total_score赋值为指定班的最高分
select total_score into v_total_score
from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_ning stu
join
t_performance_ning per
on stu.student_id = per.student_id
where stu.class_id = p_class_id
group by stu.student_name
order by total_score desc)
where rownum < 2;
return v_total_score;
end;
select stu.student_name, sum(per.test_score)
from t_student_ning stu
join t_performance_ning per
on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name
having sum(per.test_score) = maxscore_ning(1);
--返回最高成绩的学生学号
create or replace function maxScore_ning1(p_class_id number)
return number
is
v_student_id number;
begin
select student_id into v_student_id
from (
select stu.student_id,
stu.student_name,
sum(per.test_score) total_score
from t_student_ning stu
join
t_performance_ning per
on stu.student_id = per.student_id
where stu.class_id = p_class_id
group by stu.student_id, stu.student_name
order by total_score desc)
where rownum < 2;
return v_student_id;
end;
全国职称计算机考试速成过关系列套装:W .. 定价:¥133 优惠价:¥133.0 更多书籍 | |
全国职称计算机考试速成过关系列套装:W .. 定价:¥124 优惠价:¥124.0 更多书籍 |