数据库练习3

emp员工表（empno 员工编号,ename 员工姓名,job 工种,mgr 上级编号,hiredate 入职日期,sal 工资,comm 奖金,deptno 部门编号）

查找出2019、2020年6、7、8月份入职的员工

select *from emp  where to_char(hiredate,'yyyy')in ('2019','2020') and
 to_char(hiredate,'mm') in ('06','07','08') ;

正则表达式模糊查询
通配符
. 任意一个字符
*任意个
.*任意个字符
^ 开头
$ 结尾
{m}匹配最近的字符m次
(A|B)A或者B

分组查询如果多个字段进行分组，优先根据第一个字段分组，第一个字段相同在根据第二个字段进行分组，依次类推。

查询姓名第三个是A的员工

select * from emp where regexp_like(ename,'^..A.*');
select * from emp where regexp_like(ename,'^.{2}A.*');

找出电话号码135，136，186，188开头的销售员，显示销售员姓名和电话

select salername,tel from saler where 
tel like '135%' or tel like'136%' or tel like'186%' or tel like'188%';

select salername,tel from saler where regexp_like(tel,'^(135|136|186|188).*');

select salername,tel from saler where regexp_like(tel,'^1(3(5|6)|8(6|8))\d{8}$');

学生表：student(sno,sname,age,sex)    sno学号,sname学生姓名,age 年龄，sex性别
教师表：teacher(tno,tname)    tno教师编号，tname教师名字
课程表：course(cno,cname,tno) cno课程编号,cname课程名称,tno教师编号
成绩表：sc(sno,cno,score)     sno学号，cno选修课程编号，score 成绩

查询选修的课程不少于3门的学生的编号，学生姓名

select s.sno,sname from student s,sc where s.sno=sc.sno 
group by s.sno,sname having count(*)>=3;

select sno,sname from student where sno in(select sno 
from sc group by sno having count(*)>=3);

查询选修了'数学'并且及格的姓名，课程名，成绩。

select sname,cname,score from student s,sc,course c
where s.sno=sc.sno and sc.cno=c.cno
and s.sno in (select sno from sc,course c
where sc.cno=c.cno and cname='数学' and score>=60) ;


select sname,cname,score from student s,sc,course c
 where s.sno=sc.sno and sc.cno=c.cno
and s.sno in (select sno from sc where 
cno=(select cno from course where cname='数学') and score>=60) ;

查询至少选修了2门课程名带有'语'的学生姓名。

select sname from student where sno in 
(select sno from sc where cno in 
(select cno from course where cname like '%语%')
group by sno having count(*)>=2);

查询不及格的课程超过2门的学生编号和姓名

select sc.sno,s.sname,count(sc.sno) from student1 s, sc
where s.sno=sc.sno and score<60
group by sc.sno,s.sname
having count(sc.sno)>2;

select sname from student1 where sno
in (select sno from sc where score <60 group by sno having count(*)>2);

查询"语文"的成绩比"数学"的成绩高的学生编号和姓名。

select s.sno,s.sname from student1 s,
(select sno,score from course c,sc where c.cno=sc.cno and c.cname='语文') a,
(select sno,score from course c,sc where c.cno=sc.cno and c.cname='数学') b
where s.sno=a.sno and a.sno=b.sno and a.score>b.score

 select sno,sname from student1 s where
(select score from sc where cno =(select cno from course where cname='语文')
and sc.sno=s.sno)
 >(select score from sc where cno =(select cno from course where cname='数学')
and sc.sno=s.sno)

下面那个查询 ，先从student1中选取一个学生编号（会依次选取下来，从第一行开始），sc.sno=s.sno，sc.sno等于选取的编号，然后开始比较，为空或者不满足语文成绩比数学成绩高，就说明该选取的学生编号不满足条件。

查询没有学习张老师课程的学生编号和姓名。

select s.sno,s.sname from student1 s where sno not in 
((select s.sno from student1 s, teacher t,course c, sc
where t.tno=c.tno and c.cno=sc.cno and sc.sno=s.sno
and t.tno  in (select tno from teacher t where tname like '张%'))) ;

select *from student1 where sno not in 
(select sno from sc where cno in 
(select cno from course where 
tno=(select tno from teacher where tname like '张%')))

标准连接 三个表连接用标准写法，节省笛卡尔积的个数

select * from emp e inner join dept d on e.deptno=d.deptno 
inner join salgrade on sal between losal and hisal;

外连接
左连接：左表所有记录都会找出来，右表只有跟左表有关联的记录才会找出来

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

右连接：右表所有记录都会找出来，左表只有跟右表有关联的记录才会找出来

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

查找出每个工资等级的人数 （没有人数的显示为0），显示工资等级和人数 

select grade,count(sal) from salgrade s left join emp e 
on sal between losal and hisal
group by grade

查找出每个部门每个工资等级的人数，（没有人数的显示为0），显示部门名称，工资等级和人数 

select d.dname,grade, count(sal) from emp e  right join salgrade s 
 on sal between losal and hisal
right join dept d on  e.deptno=d.deptno
group by d.dname,grade
order by dname;

select d.deptno,dname,grade,count(empno) from dept d
 inner join salgrade on 1=1 left join emp e on d.deptno=e.deptno 
and sal between losal and hisal
group by d.deptno,dname,grade;

列出无雇员的部门的部门号，部门名称 

select * from emp e right join dept d on e.deptno=d.deptno 
and empno is null

select * from emp e right join dept d on e.deptno=d.deptno 
where empno is null

如果用and，因为右连接，右表的所有记录都会找出来，笛卡尔积，
--每条记录都会与之相连接再加上null的条件，就会出现左表是null右表是所有部门
--故应使用where过滤连接后的表

显示高于自己部门平均工资的员工信息

select *from emp,
(select deptno,avg(sal) avgsal from emp group by deptno) avgT
where emp.deptno=avgT.deptno and sal>avgsal;

select*from emp e where sal>(select avg(sal) 
from emp where deptno=e.deptno);

复制emp表

create table emp1 as select * from emp;

复制空表

create table emp2 as select * from emp where 1=2;

一个表的数据插入另外一个表

insert into emp2 select *from emp1;

rowid：属于表中的列，rowid 是一个用来唯一标记表中行的伪列,它是物理表中行数据的内部地址.（查询语句可以根据rowid到数据文件中唯一定位目标行）

        因此，根据rowid查询的效率是最高的，数据在入库落地时(即成功插入数据)，Oracle

数据库就给这条数据分配一个唯一的rowid。

--根据rowid查询

select rowid,emp.* from emp;

select rowid,emp.* from emp where emp.rowid = 'AAAMgzAAEAAAAAgAAA';

rownum:行号 

是给结果集增加的一个序号，不属于任何的表，先有结果集，才能有rownum，始终

是从1开始编号(不能跨过1而存在)。

找出emp表的前面5行数据

select rownum,emp.* from emp where rownum <=5;

找出emp表的第5到第8行数据

select rownum,emp.* from emp where rownum >=5 and rownum<=8;

rownum跨过1，无法查询

select *from 
(select rownum rn,emp.* from emp)
where rn between 5 and 8;

分页查询，查询表中第2-5行的数据

--方法一：首先用子查询查出所有rownum形成一个临时表，然后在选择临时表的rownum列的2-5行数据

select *

from (select rownum rn, e.* from emp e) ee

where ee.rn between 2 and 5;

或者方法二：首先用子查询查出前5行数据形成临时表，然后在查rownum>2的数据

select *

from (select rownum rn,em.* from emp em where rownum <= 5) t

where t.rn >= 2;

总结分页查询2点： 1.对rownum取别名； 2.利用子查询。

视图：是数据的对象，它的数据来源于select查询语句的结果,用来保护隐私的数据

视图就是一条select语句，只在执行时候才有数据，不能往视图中插入、修改、删除数据，只能查询数据。（在查询时，视图返回的数据就是一张临时表）

--创建视图

create or replace view emp_vw as

select e.empno,e.ename from emp e;

--视图名等价于select语句

emp_vw=(select e.empno,e.ename from emp e);

--查询视图

select * from emp_vw;

select * from (select e.empno,e.ename from emp e);

--对视图重命名

rename emp_vw to vw_emp;

--删除视图

drop view vw_emp;

给scott用户授予创建视图的权限：(cmd命令行黑窗口登录,需要分号)

第一步：sqlplus  /  as sysdba;    登录工具

第二步：grant create view to scott;

--学生表：sno ,sname ,age sex )

--老师表：tno ,tname 

--课程表：cno,cname ,tno 

--成绩表：sno ,cno ,score 
 

查询学过“谌燕”老师所教的所有课的同学的学号、姓名

select sno,sname from student where sno in(
select sno from sc where cno in (select cno from course
 where tno in (select tno from teacher where tname='谌燕'))
group by sno having count(*)=(select count(*) from course 
where tno in (select tno from teacher where tname='谌燕')));

先查询‘谌燕’老师所教课程的所有课程编号，找出所有学习过老师课程的学生编号，然后根据学生编号分组

select sno,sname from student where sno in(
select sno from sc where cno in 
(select cno from course where tno in 
(select tno from teacher where tname='谌燕'))
group by sno having count(*)=(select count(*) 
from course where tno in (select tno from teacher where tname='谌燕')));

首先查找出学过谌燕老师课程的所有学生的学生编号，然后按照学生编号分组，如果分组的人数等于该老师教过课程的课程数，即为学习过该老师所有课程的学生。

查询学过学号为“s001”同学所有门课的其他同学学号和姓名

select sno,sname from student where sno in(
select sno from sc where cno in 
(select cno from sc where sno='s001') and sno!='s001'
group by sno having count(*)=(select count(*) from sc where sno='s001'));

与上题想法一样，先找到与s001学过的课程编号，根据课程编号找到学过相关课程的学生编号，注意要排除s001，然后根据学生编号分组，再分组之后过滤，如果分组的人数等于s001学过的课程数，即为学过学号s001所有门课的同学

查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名

select sno,sname from student s where sno in(
select sno from sc sc1 where cno 
in (select cno from sc where sno='s001') and sno!='s001'
group by sno having count(*)=(select count(*) from sc where sno='s001')
and (select count(*) from
 sc where sno=sc1.sno)=(select count(*) from sc where sno='s001'));

因为上一个题目选出了学习过s001所有课程的同学，但是没有过滤学习过所有课程并且还学习过其他课程的同学，在这基础上再筛选这些同学所学的课程数等于s001学习过的课程数，就可以筛选出不仅学习过s001所有课程还多学了其他课程的同学。

emp 员工表（empno 员工编号,ename 员工姓名,job 职位,mgr 上级编号,
hiredate 入职日期,sal 工资,comm 奖金,deptno 部门编号）
dept部门表(deptno 部门编号，dname 部门名称，loc 部门地址)
salgrade工资等级表(grade 等级,losal 最低工资,hisal 最高工资)

找出每个等级的人数，没有人数的显示为0， 格式显示如下：
        等级1   等级2    等级3  等级4  等级5  等级6
         10       2       3       4      0      0

select count(decode(grade,1,1)) 等级1, 
count(decode(grade,2,1)) 等级2, 
count(decode(grade,3,1)) 等级3, 
count(decode(grade,4,1)) 等级4, 
count(decode(grade,5,1)) 等级5,
count(decode(grade,6,1)) 等级6
from emp,salgrade where sal between losal and hisal;

满足等级时decode会返回1，count函数就会统计人数，其他不满足的就不会返回1 

decode函数很简单，相当于if....else....,只不过语法很简洁

decode()

decode(条件，值1，值2，值3……)
当decode条件，值1满足时，就输出值1，值2满足时，就输出值2 以此类推，

decode(字段值，值1，值2，值3……)
当字段值等于值1 ，则输出值2，否则输出值3

select sum(case grade when 1 then count(sal) else 0 end) as 等级1,
sum(case grade when 2 then count(sal) else 0 end) as 等级2,
sum(case grade when 3 then count(sal) else 0 end) as 等级3,
sum(case grade when 4 then count(sal) else 0 end) as 等级4,
sum(case grade when 5 then count(sal) else 0 end) as 等级5
 from salgrade s left join emp e on sal between losal and hisal
group by grade
order by grade

 

          第二种是将竖行转换为横向的

1、查找出员工的姓名，工资，工资级别(工资少于3000为"三等工资"、工资在3000-6000之间为"二等工资"、高于6000为"一等工资")
    例如：
    --姓名  工资   工资等级
    --张三, 1000  三等工资
    --李四, 5000  二等工资
    --王五  7000  一等工资

select ename,sal,(
       case
          when sal<3000 then '三等工资'
          when sal between 3000 and 6000 then '二等工资'
          else
            '一等工资'
       end

) grade
from emp;

查找员工的姓名，职位(如果职位是：MANAGER，则显示'经理'，CLERK，则显示'职员',SALESMAN 则显示'销售员',其它， 则显示'雇员')

select ename, 
case job 
  when 'MANAGER' then '经理' 
  when 'SALESMAN'then '销售员'
else
  '雇员'
  end
  from emp

select ename,decode(job,'MANAGER',
'经理','SALESMAN','销售员','CLERK','职员','雇员') 职位  from emp;

显示高于自己部门平均工资的员工信息

select *from emp,
(select deptno,avg(sal) avgsal from emp group by deptno) avgT
where avgT.deptno=emp.deptno and sal>avgsal;

首先从emp中选取部门编号（会从第一行依次向后选取）然后avgT.deptno=选取那个部门编号，然后再sal>avgsal，如果不满足则不满足条件