SQL基础语法(增删改查)和基本函数

select distinct ename, sal from emp;--没有除去因为多了不相同的ename
--排序 order by  升序 asc（默认） desc降序
select * from emp order by sal，empno desc;
--多字段排序 分为主和次
select ename,sal,empno from emp order by sal asc,empno desc,ename;
--where 条件查询  大小写敏感 字符串需要’‘引起来
--查询叫SCOTT的员工信息
select * from emp where  ename = 'SCOTT';
--查询工资等于1250的员工信息
select * from emp where  sal = 1250;
--查询入职时间为1981年4月22日的员工信息
select * from emp where hiredate='22-4月-1981';
--查询工资大于1250的员工信息
select * from emp where sal>=1250;
--查询工资不等于800的员工信息
select * from emp where sal<>800;
select * from emp where sal!=800;
--between A and B 在AB之间（包含AB）
select * from emp where sal between 800 and 1250;
select * from emp where sal>=800 and sal<=1250;
--in(集合)属于集合中的一个
--查询工资为800 900 1250 的员工信息
select * from emp where sal in (800,900,1250);
select * from emp where sal=800 or sal =900 or sal=1250;
--查询奖金为空的员工
select * from emp where comm is null or comm='';
select * from emp where comm='';
--查询奖金不为空的员工
select * from emp where comm is not null;
select * from emp where comm !='';
--like 模糊查询 %和 _ ，_代表必须有一个字符
--查询以S开头的员工名字
select * from emp where ename like 'S%'; 
--查询以A开头并且至少有2个字符的员工名字
select * from emp where ename like 'A_%'; 
--查询以A开头N结尾的员工姓名 一共五个字符
select * from emp where ename like 'A___N';
--特殊符号的 特别说明用escape
select * from emp where ename like '%/%' escape '/';
-- and or not 逻辑 运算符
--查询工资大于900并且名字以S开头
select * from emp where sal>900 and ename like 'S%';
--查询工资不是800,900,1500的员工信息
select * from emp where sal not in (800,900,1250);
--查询名字不以S开头的员工信息

select * from emp where ename not like '%S';

-- 时间比较问题
select * from emp where hiredate>'1-1月-81';
-- 1981-1-1
select * from emp where hiredate>to_date('1981-1-1','yyyy-mm-dd');
select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1981-1-1';
-- nvl 空转数
select ename,nvl(comm,0) from emp;
-- nvl2(e,n,m) e为空转m 不为空转n
select nvl2(comm,'有','太惨了') from emp;
-- decode 
select decode(sal,1250,'有钱',5000,'真有钱','穷鬼') from emp;
-- case when then else end (if else)
select (case
         when sal >= 5000 then
          '有钱'
         when sal >= 4000 then
          '1有钱'
         when sal >= 3000 then
          '2有钱'
         when sal >= 2000 then
          '3有钱'
         else
          '穷'
       end)
       -- switch case
  from emp;
  select (case sex when 0 then '男' when 1 then '女' end) from dual;
  --sum 求和 max 最大 min 最小 avg 平均 count 统计
  -- 查询最大工资
  select max(sal) from emp;
  -- 查询最小工资
  select min(sal) from emp;
  -- 查询平均工资
  select avg(sal) from emp;
  -- 查询所有工资的和
  select sum(sal) from emp;
  -- 查询一共有几个员工
  select count(*) from emp;
-- 聚合函数可以写在一行上
select max(sal),min(sal),avg(sal),sum(sal),count(*) from emp;
-- 聚合函数不可以和单行数据同时展示
select ename,max(sal) from emp;
-- count(*) 统计所有 
select sum(sal)/count(*),avg(sal) from emp;
--count(exp)统计非空的exp的个数
--count(distinct exp)统计非空不重复的exp的个数
select count(sal),count(distinct sal) from emp;

-- 92连表查询
-- 查询员工信息和部门信息
select * from emp e,dept d
where e.deptno = d.deptno
-- 查询员工姓名，部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno
-- 查询员工所有信息和部门名称
select e.*,d.dname from emp e,dept d where e.deptno = d.deptno
-- 查询工资大于3000的员工姓名，工资和部门名称 
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno
and e.sal>3000
-- 非等值查询
-- 查询员工的工资等级
select * from emp e,salgrade s
where e.sal<=s.hisal and e.sal>s.losal
update emp set deptno=null where empno=1
-- 外连接
-- 左外连接 
select * from emp e,dept d
where e.deptno = d.deptno(+);
-- 右外连接
select * from emp e,dept d
where e.deptno(+) = d.deptno;
-- 自连接 自己连自己
-- 查询员工姓名和员工的经理姓名
select e1.ename,e2.ename from emp e1 ,emp e2
where e1.mgr = e2.empno;
-- 查询员工姓名和员工的经理姓名和其经理的姓名
select e1.ename,e2.ename,e3.ename from emp e1 ,emp e2,emp e3
where e1.mgr = e2.empno and e2.mgr = e3.empno;
-- cross join交叉连接(笛卡尔积)
select * from emp e cross join dept d;
-- natural join 自然连接
select * from emp natural join dept; 
-- using
select e.ename,deptno from emp e join dept d 
using(deptno);
-- on 自定义连接条件
select * from emp e join dept d 
on e.deptno = d.deptno ;
select * from emp e1 join emp e2 on e1.mgr=e2.empno;
-- 查询员工的姓名和经理的姓名和其经理的姓名
select e1.ename, e2.ename, e3.ename
  from emp e1
  join emp e2
    on e1.mgr = e2.empno
  join emp e3
    on e2.mgr = e3.empno
-- inner join 两边都合法的数据
select * from emp e inner join dept d on e.deptno = d.deptno;
-- left join 以左表为主
select * from emp e left join dept d on e.deptno = d.deptno;
-- right join 以右表为主
select * from emp e right join dept d on e.deptno = d.deptno;
-- full join 全连 取两个表的所有数据
select * from emp e full join dept d on e.deptno = d.deptno;
-- 查询员工姓名和部门姓名和其经理的名称和经理的部门名称
select e1.ename, d1.dname, e2.ename, d2.dname
  from emp e1
  left join dept d1
    on e1.deptno = d1.deptno
  left join emp e2
    on e1.mgr = e2.empno
  left join dept d2
    on e2.deptno = d2.deptno;


select SAL from emp where ename='CLARK';
select * from emp where sal>2450;
-- 子查询
select * from emp where sal>(select SAL from emp where ename='CLARK');
-- 思考：查询工资高于平均工资的雇员名字和工资。
select ename,sal from emp where sal>(select avg(sal) from emp);
-- 思考：查询和SCOTT同一部门且比他工资低的雇员名字和工资。
select ename,sal from emp where 
deptno=(select deptno from emp where ename='SCOTT')
and sal<(select sal from emp where ename='SCOTT');
-- 多行子查询 查询和s,s,c在同一个部门的员工信息并不包含s,s,c;
select * from emp where
deptno in(select distinct deptno from emp where ename in('SCOTT','SMITH','CLARK'))
and ename not in('SCOTT','SMITH','CLARK');
--思考：查询工资最高的雇员名字和工资。
select ename,sal from emp where sal=(select max(sal) from emp);
--查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
select * from emp where job = (select job from emp where ename='SCOTT')
and hiredate<(select hiredate from emp where ename='SCOTT');
--查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字
select empno,ename from emp where job = (select job from emp where ename='SCOTT')
or hiredate<(select hiredate from emp where ename='SCOTT');
-- 多行查询
--查询工资低于任何一个“CLERK”的工资的雇员信息。
select * from emp where sal< any(select sal from emp where job='CLERK');
--查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
select empno,ename,sal from emp where sal>all(select sal from emp where job='SALESMAN');
--查询部门20中职务同部门10的雇员一样的雇员信息。
select * from emp where job in(select job from emp where deptno=10) and deptno =20;
--查询在雇员中有哪些人是经理人
select * from emp where empno in (select distinct mgr from emp where mgr is not null or mgr !='');
--找出部门编号为20的所有员工中收入最高的职员
select ename from emp where  sal=(select max(sal) from emp where deptno=20) and deptno =20
--查询每个部门平均薪水的等级
select a.deptno, grade
  from salgrade, (select deptno, avg(sal) b from emp group by deptno) a
 where losal < a.b
   and hisal >= a.b