数据库第2天

--补充：
--排重 distinct
  select distinct * from emp;
--公司查询所有的职位（job）
  select distinct job from emp;

--日期类型 Date
--TimeStamp (时间戳) 精确到ns(纳秒)
--7或11个字节保存时间
--精确秒小数点后0-9位，默认6位
--1-7字节和 Date一致
--8-11字节，纳秒
--SYSTIMESTAMP
select systimestamp from dual;
select systimestamp, to_char(systimestamp,'SS.FF') from dual;
drop table user_log;
CREATE TABLE user_log(login_time timestamp(9));
insert into user_log values(systimestamp);
select * from user_log;
--------------------------
-- where 字句

--查询薪水高于1500的员工的信息
select * from emp where sal>1500;
--SQL执行顺序：
--FROM  emp
--WHERE
--SELECT
--在where字句中不能使用列别名
select ename,sal salary
from emp
where salary>1500;
--查找年薪少于20000的员工姓名和年薪（year_sal）
select ename,sal*12 year_sal
from emp
where sal*12<=20000;

--1.比较过滤条件：> < >= <= = !=(<>)
--查询薪资不等于800的员工的信息
select * from emp where sal!=800;
select * from emp where sal<>800;
--2.区间过滤条件：between and
--查询薪资在[2000,3000]之间的员工的信息
select * from emp where sal between 2000 and 3000;
--3.空值过滤条件：
--查询所有绩效为NULL的员工信息
select * from emp where comm is null;
--查询所有绩效不为NULL的员工信息
select * from emp where comm is not null;
--4.模糊查询过滤条件：
--%:代表0-n任意字符
--_:代表1个任意字符
--查询姓名是S/s开头的员工信息
select * from emp where lower(ename) like 's%';
--查询姓名第二个字母是M的员工的信息
select * from emp where ename like '_M%';
--查询第三个字母是I的员工的信息
select * from emp where ename like '__I%';
--查询最后一个字母是n/N的员工信息
select * from emp where lower(ename) like '%n';

insert into emp (empno,ename)values(4444,'s_Allen');
insert into emp (empno,ename)values(1414,'s_Scott');
--查询所有姓名带下划线的员工信息 s_
select * from emp where ename like 'sss_%' escape 's';
--escape 逃避，定义转义字符

--查询姓名不是S/s开头的员工信息
select * from emp where upper(ename) not like 'S%';

--5.逻辑连接过滤条件 AND OR NOT
--查询薪资是(2000,3000)的员工信息
select * from emp where sal>2000 and sal<3000;
--查询部门编号(deptno)是20或30的员工信息
select * from emp where deptno =20 or deptno =30;

--6.集合过滤条件: IN ANY ALL
IN(list) 判断给定的内容等于列表中任意一项即可保留
--查询部门编号(deptno)是20或30的员工信息
select * from emp where deptno in(20,30);
--查询部门编号(deptno)不是20或30的员工信息
select * from emp where deptno not in(20,30);
--ANY ALL 和> >= < <=
>ANY(list):大于列表中的最小即可
select * from emp where sal >any(1000,2000,3000);
--相当于 select * from emp where sal>1000;
<ANY(list):小于列表中的最大即可
select * from emp where sal <any(1000,2000,3000);
--相当于 select * from emp where sal<3000;
>ALL(list):大于列表中的最大即可
<ALL(list):小于列表中的最小即可
--**IN 与NULL
--IN 中添加NULL,相当于没有添加NULL
select * from emp where sal in(2000,3000,NULL);
--相当于IN(2000,3000)
--NOT IN 中添加NULL,查不到任何结果
select * from emp where sal not in(2000,3000,NULL);
--总结：where字句
--1）比较条件: > < >= <= = !=(<>)
--2）区间条件:between and 表示闭区间
--3）空值条件:is null / is not null
--4）模糊查询: like '_\%%' escape '\'
--5）逻辑条件: and or not
--6）集合条件: in any all  / not in （集合包含NULL，结果什么都没有）

--order by 子句
--排序，默认按照升序排序,降序需要在字段后边加 DESC
--可以根据多个字段进行排序，当第一个字段值有相同的记录时
--再第二个字段进行排序，以此类推。每个字段后可单独指定升降序
--查询所有员工信息，按照薪资进行排序
select * from emp order by sal;
--查询所有员工信息，按照薪资进行倒序排序
select * from emp order by sal desc;
select * from emp order by job,sal desc;
--SQL执行顺序
--FROM
--WHERE
--SELECT
--ORDER BY

--练习题：
--1）查询emp表中的入职日期列，并按照入职先后顺序列出
select hiredate from emp order by hiredate;
--2）查询工资大于1600的员工姓名和工资
select ename,sal from emp where sal > 1600;
--3）查询工号为7369的员工的姓名和部门编码（deptno）
select ename,deptno from emp where empno=7369;
--4）查询工资不再[4000,5000]之间的员工姓名和工资
select ename,sal
from emp
where sal>5000 or sal<4000;
--where sal not between 4000 and 5000;

--5）查询那些尚未分配部门的员工的姓名
select ename
from emp
where deptno is null;
--6）已知员工每月薪水=薪资+绩效*0.8,
--查询员工姓名和每月薪水(列别名 money)，并按月收入降序排列
select ename,nvl(sal,0)+nvl(comm,0)*0.8 money
from emp
order by money desc;
--7）查询emp表中在1981年之后入职的员工的信息
select *
from emp
where hiredate>to_date('1981-01-01','yyyy-mm-dd');
--8）查询薪资大于1000且职位是‘CLERK’的员工信息
select *
from emp
where sal>1000 and job ='CLERK';
--9）查询姓名中第二个字符是‘A’的员工的薪水
select ename,sal
from emp
where ename like '_A%';
--10）查询不是10，20部门的员工信息
select *
from emp
where deptno not in(10,20);

--组函数（聚合函数）
--是对多行多条记录进行统计，然后得到一个结果
--COUNT 统计一共有多少条结果
select count(*) from emp;
--MAX 统计结果中最大值
select max(sal) from emp;
--MIN 统计结果中最小值
select min(sal) from emp;
--SUM 统计结果之和
select sum(sal) from emp;
--AVG 统计结果平均数
select avg(sal) from emp;
--组函数是忽略NULL值
select count(comm) from emp;
select avg(nvl(comm,0)) from emp;

--GROUP BY 字句
--分组字句，配合组函数使用，实现分组统计
--查询每个部门的平均工资
select deptno d,round(avg(sal),2)
from emp
group by deptno;
--SQL 执行顺序
--FROM
--WHERE
--GROUP BY
--SELECT
--ORDER BY

--HAVING 字句
--对分组的结果进行过滤，就需要使用HAVING
--(原因是where在group by之前执行，分组过滤条件不能写在where中)
--部门平均薪资在1600之上的部门编号和平均薪资
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>1600;
--SQL 执行顺序
--FROM
--WHERE
--GROUP BY
--HAVING
--SELECT
--ORDER BY

--SELECT 和 HAVING 中只能写group by 之后声明的字段，
--或用组函数修饰的字段。
select deptno,avg(sal),avg(comm)
from emp
group by deptno
having avg(sal)>1500 and avg(comm)>100;

--练习题：
--1）查询各职位（job） 平均薪资，最大值，最小值和总和
select max(sal),avg(sal),min(sal),sum(sal)
from emp
group by job;
--2）查询各职位的员工总数，并根据总数按降序排序
select job,count(*) totle
from emp
group by job
order by totle desc;
--3）查询工资最高和工资最低员工工资之差，并显示列名为DIFF
select max(sal)-min(sal) diff
from emp;
--4）查询每位管理者（MGR）属下，最低工资，且最低工资不能小于800
--且没有管理者（MGR=NULL）不进行统计
select min(sal)
from emp
where mgr is not null
group by mgr
having min(sal)>=800;
--5）查询各部门中工资大于1500的员工人数
select deptno,count(*)
from emp
where sal>1500
group by deptno;
--6）查询各部门的平均绩效，如果绩效为null，则按0计算
select deptno,avg(nvl(comm,0))
from emp
group by deptno;
--7）查询入职时间最晚的员工的姓名和部门编号
select ename,deptno,hiredate
from emp
where hiredate=(
select max(hiredate)
from emp
);
--作业
--1）查询平均薪资最高的部门中所有员工的信息
select *
from emp
where deptno in(select deptno from emp group by deptno
having avg(sal)=(select max(avg(sal))
from emp
group by deptno)
)
--2）查询平均薪资大于800的部门的员工的信息
select * from emp
where deptno in(select deptno from emp group by deptno
having avg(sal)>800
);
--3）查询平均绩效为0的职位,及其所有员工信息
select * from emp
where job in(select job from emp group by job
having avg(nvl(comm,0))=0
);
--4）查询所有领导的基本信息
select * from emp
where empno in(
select distinct mgr from emp
);
--5）查询所有不是领导的员工的基本信息
select * from emp
where empno not in(
select distinct mgr from emp where mgr is not null
)