数据库第3天

--SQL语句书写顺序
SELECT  字段列表,列别名,组函数,单行函数,运算式
FROM 表名
WHERE 过滤条件(比较/区间/逻辑/集合/模糊/空值)
GROUP BY 分组字段
HAVING 分组过滤
ORDER BY 排序字段 [DESC]
--SQL语句执行顺序
FROM  
WHERE     不能有组函数/列别名
GROUP BY
HAVING    不能有列别名/只能有组函数/GROUP BY中分组字段
SELECT    只能有组函数/GROUP BY中分组字段
ORDER BY
--子查询
--where 子查询
--查找与SCOTT员工同职位的其他员工的信息
select * from emp
where  job in(select job from emp where ename='SCOTT')
and ename <>'SCOTT';
--查找薪水比平均薪水高的员工
select * from emp
where sal>(select avg(sal) from emp );
--查找部门中有SALESMAN,但不是SALESMAN的员工
select * from emp
where deptno in(
select distinct deptno
from emp where job ='SALESMAN')
and job <>'SALESMAN';
--HAVING子查询
--查询最低薪水高于30部门的最低薪水的部门信息
select * from dept
where deptno in (select deptno from emp group by deptno
having min(sal)>(select min(sal) from emp where deptno=30)
);
--FROM 子查询
select *
from ( select loc,deptno from dept);

select *
from(
select ename,sal*12 year_sal
from emp)
where year_sal>1000;

--关联查询
--使用的两个或两个以上的表进行查询相关的信息，就称为关联查询
--查找员工（emp）及其所在部门(dept)的信息
--笛卡尔积
select count(*) from dept; --4
select count(*) from emp; --19
select count(*) from dept,emp;--4*19=76
select emp.*,dept.* from dept,emp
where dept.deptno=emp.deptno;
--oracle 数据库解析from子句时，发现需要查找多张表
--会将多张表进行笛卡尔积运算，对于我们来说，笛卡尔积中
--只有部分结果是我们所需要的，因此需要进行过滤，过滤条件
--根据不同的业务逻辑，是不同的
select * from emp,dept where emp.deptno=dept.deptno;

select distinct s_id from fruits;

create table supplier(
  s_id number(4),
  s_name varchar2(50)
);
insert into supplier values(1001,'开心农场');
insert into supplier values(1002,'人人农场');
insert into supplier values(1003,'密密农场');
select * from supplier;
commit;

--查找'榴莲'f_name供应商的信息
select * from supplier
where s_id=(select s_id from fruits where f_name='榴莲');
--关联查询
select s.* from fruits f,supplier s
where f.s_id=s.s_id
and f_name='榴莲';
--关联查询写法2 ...JOIN... ON...
select s.* from fruits f join supplier s
on f.s_id=s.s_id --关联条件
where f_name='榴莲';
select * from emp e join dept d on e.deptno=d.deptno;

--关联查询分类
--内连接
--内连接查询返回两个关联表中所有满足关联条件的记录
  --等值连接：关联条件是等号
  --非等值连接：关联条件不是等号
  select * from salgrade ;
  --查询员工(emp)姓名薪水等级(salgrade)
  select ename,grade from emp join salgrade
  on sal between losal and hisal;
--外连接
--关联查询中返回结果包含不满足关联条件的记录
--左外连接：以左表为驱动表，左表中的数据全部显示，右表中不匹配的
--关联数据用NULL代替
--右外连接：和上述类似，左右颠倒即可。
--全外连接: 左外连接+右外连接-重复的数据 full join
--查询员工及其所在部门的信息，同时显示没有分配部门的员工信息
select * from dept d right join emp e on e.deptno=d.deptno;
select * from dept;
--查询所有没有员工的部门信息
insert into dept values(50,'Test','DALIAN');

select d.* from dept d left join emp e
on e.deptno=d.deptno
where job is null;

select * from dept d  full join emp e
on e.deptno=d.deptno ;

--查询每个职员自己和他的领导名字，和他们的员工编码
7369 SMITH 7902 FORD
select e.empno,e.ename,m.empno,m.ename
from  emp e left join emp m
on e.mgr=m.empno;--员工的管理者编号 = 管理者工号
select e.empno,e.ename,e.mgr,m.empno,m.ename
from  emp e join emp m
on e.mgr=m.empno;
--查询所有部门的名称，地点，员工数量和平均薪资
select d.dname,d.loc,nvl(e.num,0),nvl(e.avg_sal,0)
from dept d left join (
select deptno,count(ename) num,round(avg(sal),2) avg_sal
from emp group by deptno
) e
on e.deptno=d.deptno;
select d.dname,d.loc,count(e.ename),round(avg(nvl(e.sal,0)),2)
from dept d left join emp e
on d.deptno=e.deptno
group by d.dname,d.loc;

--SELECT 子查询
--相当于外连接的另一种表现形式
select e.ename,e.sal,e.deptno,(
select d.dname from dept d
  where d.deptno=e.deptno
) 部门名称
from emp e;

--EXISTS 关键字
--在WHERE子查询中需要引入到主查询的字段数据时，使用EXISTS关键字。
--EXISTS后边的子查询至少返回一条数据，则整个条件判断返回TRUE
--否则整个条件判断返回FALSE
--查询有员工的部门信息
select * from dept
where deptno in(select distinct deptno from emp);
select * from dept d
where exists (select * from emp e where d.deptno=e.deptno);
--查询没有部门的员工
select * from dept d
where not exists (select * from emp e where d.deptno=e.deptno);
--查询有在售水果的供应商信息
select * from supplier
where exists (select * from fruits where supplier.s_id=fruits.s_id);

--DML 子查询
--删除所有比SMITH工资高的员工
delete from emp
where sal>(select sal from emp where ename='SMITH');
select * from emp;
rollback;
--所有薪资比平均薪资少的员工的薪水加200
update emp set sal=nvl(sal,0)+200
where nvl(sal,0)<(select avg(sal) from emp);
--部门信息复制一份，重新插入到部门信息表
insert into dept (select * from dept where deptno in(10,20));
select * from dept;
rollback;

--DDL
--创建一个emp副表
CREATE TABLE t_emp
AS(
  SELECT * FROM EMP
);
SELECT *　FROM  t_emp;

CREATE TABLE tt_emp
AS(
  SELECT ename,dname from emp e,dept d
  where e.deptno=d.deptno
);
select * from tt_emp;

--分页查询
--ROWNUM 伪列
select rownum ,empno,ename from emp
where rownum<=5;
--rownum 只能从1开始计数，不能从中间截取
--可以使用别名进行查询
select * from
(select rownum rn,empno,ename from emp)
where  rn between 11 and 15;
--查询第n页 pageSize
--where  rn between （n-1）*pageSize+1 and n*pageSize;

--分页和排序
--进行3次查询： 先排序，再加别名，再分页查询
--按薪水进行降序排序，null按0处理，查询第8到第16条数据
select *
from (
  select rownum rn ,e.*
  from (
  select ename,nvl(sal,0)
  from emp order by sal desc
  )e
)
where rn between 8 and 16 ;

--作业：
--Student(S#,Sname,Sage,Ssex);学生表
--Course(C#,Cname,T#);课程表
--Teacher(T#,Tname);教师表
--SC(S#,C#,score);成绩表
--1.查询"001"课程比"002"课程成绩高的所有学生的学号
select a.s#
from (select s#,score from sc where c#='001') a,
(select s#,score from sc where c#='002') b
where a.score>b.score and a.s#=b.s#;
--2.查询平均成绩大于60的同学的学号和平均成绩
select s#,avg(score) avg_score from sc
group by s#
having avg(score)>60;
--3.查询所有同学的学号，姓名，选课数，总成绩
--s# sname, ,socre ->sc student
select s.s#,s.Sname,count(c.s#),sum(c.score)
from student s left join sc c on s.s#=c.s#
group by s.s#,s.Sname

--*请根据上述题意，自行创建表
create table student(
s# number(4),
sname varchar2(50),
sage number(3),
ssex char(1)
);
create table course(
c# char(3),
cname varchar2(50),
t# number(4)
)
create table teacher(t# number(4),tname varchar2(50));
create table sc(s# number(4),c# char(3),score number(3));


select * from emp left join dept on emp.deptno=dept.deptno;
--
select * from emp,dept
where emp.deptno(+)=dept.deptno;
--使用伪列rownum+子查询
--pagesize=5 page=3
select *
from(
select rownum rn,e.*
from (select * from emp order by deptno) e
)
where rn between 11 and 15;