sql语句基础

select的用法

--每个员工的所有信息
select * from emp
--每个人的部门编号，姓名，薪水
select deptno,ename,sal from emp;
--每个人的年薪
select ename,sal*12 from emp;
--计算2*3的值
select 2*3 from emp;
--计算2*3的值(dual)
select 2*3 from dual;
select * from dual;
--得到当前时间
select sysdate from dual
--可以给列起别名,比如求每个人的年薪
select ename,sal*12 salperyear from emp;
--如果别名中有空格，需要用双引号
select ename,sal*12 "sal per year" from emp;
--如果没有内容，则为空
select comm from emp;
--当空字段参与计算，则结果是null
--例如：计算每个人的全年的收入包括月薪和年终奖
select ename,sal*12+comm from emp;
--可以将多个字符串拼在一起。比如：求每个人的薪水，格式为smith-sal-123
select ename||'-sal-'||sal from emp;
--如果字符串中有单引号，需要用另外一个单引号转义，比如：这样一个字符串： he's friend
select ename||'''s sal is'||sal from emp;


--------------------------------------------------------
--distinct 关键词的用法
--求有哪些个部门
select distinct deptno from emp
--可以用来修饰多个字段。比如：求有哪些个部门和job的组合
select distinct deptno,job from emp

--------------------------------------------------------
where关键词的用法
--可以是数值类型的等值判断。比如：求10这个部门的所有员工
select * from emp where deptno=20
--可以是字符串类型的等值判断。比如：求叫KING的这个人的信息
select * from emp where ename = 'KING'
--也可以是不等值判断。比如：求薪水小于2000的员工信息
select * from emp where sal<2000;
--字符串也可以做不等值判断，比如：求所有ename大于'CBA'的员工信息。
select * from emp where ename>'CBA';
--求部门不是10的员工
select * from emp where deptno <> 10;
--求薪水在800和1500之间的员工信息
select * from emp where sal >=800 and sal <=1500;
--也可以写成
select * from emp where sal between 800 and 1500
--这样写则不可以
-----------------------------select * from emp where 800<=sal<=1500
--where...in..的用法。比如：求薪水是800或者1500或正2000的员工信息
select * from emp where sal=800 or sal=1500 or sal=2000
--相当于写成这样
select * from emp where sal in(1500,800,2000,1500,1500,1500,1500);
--再比如求姓名是KING,SMITH,AA的员工信息
select * from emp where ename in ('KING','SMITH','AA')
--求入职时间在20-2月-81之后的员工信息
select * from emp where hiredate < '23-5月 -87';

--------------------------------------------------------
--and or not的用法
--求薪水大于1000或者部门在20这个部门的员工信息
select * from emp where sal>1000 and deptno=20
--求薪水不是800或者不是1500或者不是3000的员工信息
select * from emp where sal not in (800,1500,3000)
--也可以这样来写
select * from emp where sal <>800 and sal <> 1500 and sal<>3000

--------------------------------------------------------
--like的用法
--求名字中包含ALL这三个字符的员工信息
select * from emp where ename like '%E%';
--求名字中的第二个字母是A的员工
select * from emp where ename like '_A%';
--特殊字符需要转义。比如：求员工中包含特殊字符%的员工信息
select * from emp where ename like '%/%%' escape '/'

--------------------------------------------------------
--null的用法
--求没有年终奖的员工
select * from emp where comm is null
--求有年终奖的员工
select * from emp where comm is not null


--------------------------------------------------------
--order by的用法
--员工信息按照姓名正序排列
select * from emp order by ename asc;
--员工信息按照倒叙排列
select * from emp order by ename desc;
--也可以是多个字段组合排列。例如：员工信息按照部门正序排列，并且按照姓名倒叙排列
select * from emp order by deptno asc,ename desc

--------------------------------------------------------
--function的用法
--把所有姓名变成小写
select lower(ename) from emp;
--把所有姓名变成大写
select upper(ename) from emp;
--求所有人名中包含'a'的员工信息不区分大小写
select * from emp where lower(ename) like '%a%'
--截取子字符串，比如求Hello的一部分
select substr('hello',2,2) from dual;
select substr(ename,2,2) from emp;
--求Hello的一部分，并指明长度

--求ascii码对应的字符
select chr(65) from dual
--求字符对应的ascii码
select ascii('中')from dual
--四舍五入
select round(12.456,2) from dual
select round(12.456,-1) from dual
--四舍五入小数点后面多少位

--四舍五入小数点前面多少位


--------------------------------------------------------
--important!日期转换函数
--------------------------------------------------------
--将当前日期转换成1981-03-12 12:00:00这种形式的字符串
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

--将1981-03-12 12:00:00字符串转换成日期
select to_date('1981-03-12 12:00:00','YYYY-MM-DD HH24:MI:SS') from dual;

--将每个人的薪水转换成固定格式的字符串
select to_char(sal,'$999,999,999.99') from emp;
--将固定格式的字符串转换成数值
select to_number('$8,000.00','$999,999,999.99') from dual;

--当null参与计算时候，可以用nvl这个函数。比如求每个人一年总共的收入
select ename,sal*12+comm from emp

--------------------------------------------------------
--group function组函数
--求所有人的薪水的总和，平均值，最大值，最小值
select sum(sal),avg(sal),max(sal) ,min(sal) from emp;
--求总的行数
select count(*) from emp;
--求总的行树，（可以指定具体的字段）但如果字段有null值的时候需要小心使用
select count(comm) from emp;

--也可以过滤掉重复的行之后统计行数

select count(distinct deptno) from emp
--可以指明按照哪个字段进行分组.比如；分部门统计最高薪水
select deptno,max(sal) from emp where deptno is not null group by deptno
--也可以按照多个字段来分组统计，比如：分部门和岗位，统计最高薪水和行数
select deptno,job,max(sal),count(*) from emp group by deptno,job

--------------------------------------------------------
--重要：出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。
--------------------------------------------------------
select ename,deptno,job,max(sal),count(*) from emp group by deptno,job

--求薪水最高的员工姓名
select * from emp where sal=(select max(sal) from emp);
delete from emp where ename='TEST2'
update emp set deptno=10 where deptno=99
select * from dept
insert into dept (deptno,dname,loc) values('10','ACCOUNTING','NEW YORK');
--having从句的用法
--求平均薪水是2000以上的部门
select deptno,avg(sal) as avg_sal from emp group by deptno
having avg(sal) >2000
--------------------------------------------------------
--总结一下select语法
select
from
where
group by
having
order by
--------------------------------------------------------
-- 执行顺序very important!
-- 首先执行where语句将原有记录过滤；
-- 第二执行group by 进行分组；
-- 第三执行having过滤分组；
-- 然后将select 中的字段值选出来；
-- 最后执行order by 进行排序；

--------------------------------------------------------
/*
按照部门分组统计，求最高薪水，平均薪水
只有薪水是1200以上的才参与统计
并且分组结果中只包括平均薪水在1500以上的部门
而且按照平均薪水倒叙排列
*/
select max(sal),avg(sal) from emp
where sal>1200
group by deptno
having avg(sal) >1500
order by avg(sal) desc

--------------------------------------------------------
/*
把雇员按部门分组，
求最高薪水， 部门号，
过滤掉名字中第二个字母是'A'的，
要求分组后的平均薪水>1500，
按照部门编号倒序排列
*/
select max(sal) ,deptno from emp where ename not like '_A%'group by deptno
having avg(sal) >1500
order by deptno desc

/* very very important! */
select ename, deptno from emp;
select deptno, dname from dept;
--------------------------------------------------------------------------------------
--老语法：----------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--等值连接：求员工姓名以及员工所在部门的名字同时显示出来
select ename,emp.deptno,dname,dept.deptno from emp,dept
where emp.deptno = dept.deptno

select ename,e.deptno,dname,d.deptno from emp e,dept d
where e.deptno = d.deptno

--非等值连接：要求每位雇员的薪水等级
select * from salgrade
select ename,sal,grade,losal,hisal from emp,salgrade
where sal >=losal and sal <=hisal
--跨3个表：求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时
select ename,dname,grade from emp,dept,salgrade
where emp.deptno = dept.deptno
and sal >=losal and sal <=hisal
and job ='PRESIDENT'


--也可以同一个表做跨表连接：求每位员工的姓名，及其上级经理的姓名

select e1.ename,e2.ename from emp e1,emp e2
where e1.mgr = e2.empno

--------------------------------------------------------------------------------------
--新语法------------------------------------------------------------------------------
--在SQL1992的语法规则中，语句过滤的条件和表连接的条件都被放在了where子句中，当条件过多时，容易造成混淆，
--SQL1999修正了这个缺点，将连接条件和数据过滤条件区分开来，
--------------------------------------------------------------------------------------

--交叉连接
--结果会产生这两张表的笛卡尔乘积

select * from emp cross join dept
--要用deptno作为等值连接条件，我们可以这样写
select * from emp join dept using (deptno)
select ename, dname from emp join dept using(deptno);
--相当于
select ename, dname from emp join dept on emp.deptno = dept.deptno
--也可以写成这样


--也可以用于非等值连接
--求每位雇员的薪水等级
select * from emp join salgrade on (sal >=losal and sal<= hisal)


--多个join,where组合使用
--(求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时)

select * from emp join dept on emp.deptno = dept.deptno
join salgrade on (sal >=losal and sal<= hisal)
where job = 'PRESIDENT'


--外连接--取出表中连接不到一起的多余的数据
--没有全内连接，没有右内连接

--其中outer也可以省略，简写为left join , right join , full join
--left inner join可以缩写成inner join 也可以缩写成join，意思是左内。
--update emp set deptno=20 where ename='SMITH';
--commit;
select * from emp;
select * from dept;
delete from dept where deptno=99;
--左内,从左往右找，匹配不上的记录不显示
select ename,emp.deptno from emp join dept on emp.deptno = dept.deptno;
select ename,emp.deptno from emp inner join dept on emp.deptno = dept.deptno;
--没有这种语法：select ename,emp.deptno from emp left inner join dept on emp.deptno = dept.deptno;
--左外连接,从左往右找，匹配不上的记录也显示一行
select ename,dept.deptno from emp left /*outer*/ join dept on emp.deptno = dept.deptno;

--右外连接，从右往左找，匹配不上的记录，也显示一行
select ename,dept.deptno from emp right /*outer*/ join dept on emp.deptno = dept.deptno;
--没有右内连接：select ename,dept.deptno from emp right inner join dept on emp.deptno = dept.deptno;

--全外连接
select ename,dept.deptno from emp full /*outer*/ join dept on emp.deptno = dept.deptno;

--左外,右外的区别






--什么时候用外连接呢?比如领导向你要所有学生的列表,顺便把所属的班级也列出来,就需要外连接

--在Where语句中使用子查询
-----------------------------------------------------------------
--雇员中最高薪水的人员名称
--1,先求出最高薪水
--2,再求雇员中最高薪水的人员名称
select ename from emp where sal=(select max(sal) from emp)



--有哪些人的薪水是在整个雇员的平均薪水之上的
select ename,sal from emp where sal >(select avg(sal) from emp)

-----------------------------------------------------------------
--雇员中哪些人是经理人
--1,首先查询mgr中有哪些号码
--2,再看有哪些人员的号码在此出现
select distinct mgr from emp where mgr is not null order by mgr

select ename
from emp
where empno in (select distinct mgr from emp where mgr is not null )
--where in 中不让写orderby
select ename
from emp
where empno in (select distinct mgr from emp where mgr is not null order by mgr)

-----------------------------------------------------------------
--在From子句中使用子查询
------------------------------------------------------------------

--部门平均薪水的等级
--1,首先将每个部门的平均薪水求出来
--2,然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级
select deptno,avg(sal) from emp group by deptno
select * from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade
on avg_sal between losal and hisal;


-----------------------------------------------------------------
--每个部门最高薪水的人员名称
--1,首先将每个部门的最高薪水求出来
--2,然后把结果当成一张表，再用emp和这张结果表做连接，以此求得每个部门最高薪水的人员名称

select deptno,max(sal) from emp where deptno is not null group by deptno

select ename from emp e join
(select deptno,max(sal) max_sal from emp where deptno is not null group by deptno ) t
on sal = max_sal and e.deptno = t.deptno

-----------------------------------------------------------------
--哪些人的薪水在部门的平均薪水之上
--1,首先将每个部门的平均薪水求出来
--2,然后把结果当成一张表，再用emp和这张结果表做连接，以此求得哪些人的薪水在部门的平均薪水之上
select deptno,avg(sal) avg_sal from emp group by deptno

select * from emp join (select deptno,avg(sal) avg_sal from emp group by deptno)t
on (sal>avg_sal and emp.deptno=t.deptno)
-----------------------------------------------------------------
--求部门中(所有人的)平均的薪水等级,形式如：
-- deptno avg_grade
-- 10 3.67
-- 20 2.8
-- 30 2.5
--1,先求每个人的薪水等级
--2,再按照部门分组,求平均数
select deptno,sal,grade from emp join salgrade on sal between losal and hisal
select deptno,avg(grade) from (select deptno,sal,grade from emp join salgrade on sal between losal and hisal)t group by deptno




------------------------------------------------------------------------------------------
--使用伪字段:rownum，----------------------
------------------------------------------------------------------------------------------
--用来标识每条记录的行号，行号从1开始，每次递增1
select rownum,emp.* from emp;
--oracle下rownum只能使用 < <=， 不能使用 = > >= 等比较操作符，
select rownum,emp.* from emp where rownum<5;
--当rownum和order by 一起使用时，会首先选出符合rownum条件的记录，然后再排序
--(错误的写法)例如，当我们要求薪水最高的前5个人时，最直接的想法可以这样写：
select * from emp where rownum<5 order by sal desc
--(正确的写法)可以这样写

select * from
(select * from emp order by sal desc) t
where rownum<=5




--------------------------------------------------------
--不准用组函数(即MAX()),求薪水的最高值(面试题)
--第一种解决办法:
--1,先把所有薪水按照倒序排列
--2,再取第一行
select * from
(select sal from emp order by sal desc) t
where rownum=1



--第二种解决办法:
--1,先跨表查询自己,先求出的结果中,e1.sal不可能出现最大数
--2,然后再not in
select e2.sal from emp e1,emp e2 where e1.sal>e2.sal
select sal from emp where sal not in(select e2.sal from emp e1,emp e2 where e1.sal>e2.sal)


-----------------------------------------------------------------
--求平均薪水最高的部门的部门编号
--第一种解决办法:
--1,先求出每个部门的平均薪水,
select deptno,avg(sal) avg_sal from emp group by deptno
--2,再求每个部门的平均薪水的最高值,
select max(avg_sal) from (1111111111111111111111111)
--3,最后再求第一步结果中avg_sal = 最高薪水的记录.

select deptno from (111111111111) where avg_sal = (22222222)


select deptno
from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal)
from (select deptno,avg(sal) avg_sal from emp group by deptno))


--没法考虑并列第一的情况
select deptno from
(select deptno,avg(sal) avg_sal from emp group by deptno order by avg(sal) desc)
where rownum<=1


--第二种解决办法:
--1,将上面的第一步第二步合并,先求最高平均薪水,用max(avg(sal))的办法
--不能写成select deptno,max(avg(sal)) from emp group by deptno
select max(avg(sal)) from emp group by deptno
--2,求出每个部门的平均薪水
select deptno,avg(sal) avg_sal from emp group by deptno
--3,最后再求第二步结果中(即每个部门的平均薪水),avg_sal = (第一步结果)的记录.即avg_sal =最高薪水的记录.
select deptno from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =(select max(avg(sal)) from emp group by deptno)


--第三种解决办法:
--1,先求出每个部门的平均薪水,
select avg(sal) avg_sal from emp group by deptno
--2,求最高平均薪水,用max(avg(sal))的办法
select max(avg(sal)) from emp group by deptno
--3,再使用having语句, avg(sal) = 第二步的结果
注意：为组函数起的别名在having中不能用

select deptno from emp group by deptno
having avg(sal) = (select max(avg(sal)) from emp group by deptno)



-----------------------------------------------------------------
--求平均薪水最高的部门的部门名称
--1,部门平均最高薪水
--2,得到部门编号列表,注意用group by deptno
--3,再应用having子句, having avg(sal) = (第一步的结果)
--4,得到平均最高薪水的那个部门的编号
--5,再得到部门名称



select dname from dept where deptno in

(
select deptno
from (select deptno,avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal)
from (select deptno,avg(sal) avg_sal from emp group by deptno))

)

-----------------------------------------------------------------
--求平均薪水的等级最低的部门的部门名称
--第一步：部门平均薪水的等级,分成两个小步骤,第一小步是求部门平均薪水

select * from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal

--第二步：最低的等级值

select min(grade) from (1111111111111111111111111)

--第三步：等于最低值的部门编号
------------有错误,应该是grade=
select deptno from (111111111111) where grade = (22222222222222)
--第四步：求名称
select dname from dept where deptno in(33333333333)




select dname
from dept
where deptno in
(

select deptno
from (select *
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) t
join salgrade on avg_sal between losal and hisal)
where grade =
(select min(grade)
from (select *
from (select deptno, avg(sal) avg_sal
from emp
group by deptno) t
join salgrade on avg_sal between losal and hisal)))









--也可以用视图的方式来解决
--conn sys/bjsxt as sysdba
--grant create table, create view, create sequence to scott
--根据第一步的结果,建立一个view

create or replace view v1 as
--必须明确定义列
select deptno, avg_sal, grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade on avg_sal between losal and hisal


--查看一下
select * from v1
--查询一下

--带入view

select dname from dept where deptno in
(select deptno from (v1) where grade = (select min(grade) from v1))








-------------------------------------------------------------
--为什么in的后面不能order by ？







---------------------------------------------------------------
--求部门经理人中平均薪水最低的部门名称 (思考题)
第一步,求部门经理的雇员编号
select distinct mgr from emp where mgr is not null
第二步，按部门统计，求部门经理的平均薪水
select deptno,avg(sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null)group by deptno
第三步，求最低值
select min(avg(sal)) from emp where empno in (select distinct mgr from emp where mgr is not null)group by deptno
第四步，求部门经理人中平均薪水最低的部门名称
select deptno from (2222222222222) where avg_sal =(333333333333333333333333)


select dname from dept where deptno in (select deptno from (select deptno,avg(sal) avg_sal from emp where empno in (select distinct mgr from emp where mgr is not null)group by deptno) where avg_sal =(select min(avg(sal)) from emp where empno in (select distinct mgr from emp where mgr is not null)group by deptno))
----------------------------------------------------------------------------
--求比普通员工的最高薪水还要高的经理人名称
--1,求所有经理的编号
create or replace view v1 as
select distinct mgr from emp where mgr is not null
select * from v1
--2,普通员工的最高薪水
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null)
--3,

select ename from emp where empno in (select * from v1)
and sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null))

--即:


select ename from emp where empno in (select distinct mgr from emp where mgr is not null)
and sal > (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null))








------------------------------------------------------------------------------
--求薪水最高的前5名雇员
--1,先观察一下

--2,看看rownum的作用

--3,不是我们想要的结果
select ename,sal from emp where rownum<=5 order by sal desc
--4,先order by,再rownum
select * from
(select ename,sal from emp order by sal desc ) t
where rownum<=5

--------------------------------------------------------------------------------
--求薪水最高的第6到第10名雇员(重点掌握)
--这种没法实现,oracle下rownum只能使用 < <=， 不能使用 = > >= 等比较操作符
--注意里面的rownum和外面的rownum的区别，外面要想访问里面的rownum，必须取得一个别名。

select * from
(select ename,sal from emp order by sal desc ) t
where rownum>=5
and rownum<=10

--所以再套一层select
select * from
(select t.*,rownum r from
(select ename,sal from emp order by sal desc ) t
)
where r>=5
and r<=10

--还有一种排序方式
select * from
(select * from emp order by sal desc)where rownum<=10
minus
select * from
(select * from emp order by sal desc)where rownum<=5
--------------------------------------------------------------------
--练习: 求最后入职的5名员工
--1,每个人的入职时间

--2,取前5行





-----------------------------------------------------------------
--求每个部门中薪水最高的前两名雇员

--1,每个员工的姓名，部门，工资,按部门和工资(倒序)排列
select ename,deptno,sal from emp order by deptno,sal desc
--2,套一层,加上个r
select ename,deptno,sal,rownum r from
(select ename,deptno,sal from emp order by deptno,sal desc) t
--3,创建试图
create or replace view v1
as
select ename,deptno,sal,rownum r from
(select ename,deptno,sal from emp order by deptno,sal desc) t
--观察一下
select * from v1

--每个部门中,薪水最高的第一行,并创建试图
create or replace view v2 as
select deptno,min(r) min_r from v1 group by deptno

--两个view跨表连接,大于薪水最高的行数,小于最高的行数+1,并且部门编号要匹配
select ename from v1 join v2
on ( v1.deptno = v2.deptno and v1.r >=v2.min_r and v1.r<=v2.min_r+1)


-------------------------------------------------------------------------------
--面试题: 比较效率
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;

---------------------------------------------------------
--使用union、minus

--使用union、minus可以用来实现结果集的合并和去除（可以理解为加和减），例如：
select * from emp where deptno=10
union
select * from emp where deptno=20;

--相当于
select * from emp where deptno=10 or deptno=20

--而下面的语句
select * from emp where deptno in (10,20)
minus
select * from emp where sal < 1500;
--相当于
select * from emp where deptno in(10,20) and sal>=1500

--求分段显示薪水的个数



如：
scale total
<800 0
801-1000 2
1001-2000 3
2001-5000 6
>5000 8

select '<800' as scale ,count(*) as total from emp where sal<800
union
select '<801-1000' as scale ,count(*) as total from emp where sal<=1000 and sal>=801



--或者显示成为
--注意：使用between .. and .. 的时候，包含了最大和最小值。
800-1000 1001-2000 2001-5000
2 3 6
select * from
(select count(*) as "800-1000" from emp where sal >=800 and sal <= 1000),
(select count(*) as "1001-2000" from emp where sal >=1001 and sal <= 2000),
(select count(*) as "2001-5000" from emp where sal >=2001 and sal <= 5000)



--或显示成为
DEPTNO 800-2000 2001-5000
------ ---------- ----------
30 5 1
20 2 3
10 1 2
select t.deptno,"800-2000","2001-5000" from
(select deptno,count(*) as "800-2000" from emp where sal between 800 and 2000 group by deptno) t
join
(select deptno,count(*) as "2001-5000" from emp where sal between 2001 and 5000 group by deptno) t1
on t.deptno = t1.deptno









-----------------------------------------------------------------------------------
--每个薪水等级有多少名雇员 ？
--1,先求出每个雇员的薪水等级

--2,再group一下