sql使⽤汇总（PQSQL）
--sql structured query language
--DML--Data Manipulation Language--数据操作语⾔
query information (SELECT),
add new rows (INSERT),
modify existing rows (UPDATE),
delete existing rows (DELETE),
perform a conditional update or insert operation (MERGE),
see an execution plan of SQL (EXPLAIN PLAN),
and lock a table to restrict access (LOCK TABLE).
--DDL--Data Definition Language--数据定义语⾔
create, modify,drop, or rename objects (CREATE,ALTER,DROP,RENAME),
remove all rows from a database object without dropping the structure (TRUNCATE),
manage access privileges (GRANT,REVOKE),
audit database use (AUDIT,NOAUDIT)
and add a description about an object to the dictionary (COMMENT).
--Transaction Control事务控制语句
save the changes(COMMIT)
or discard the changes (ROLLBACK) made by DML statements.
Also included in the transaction-control statements are statements to set a point or marker in the transaction for possible rollback (SAVEPOINT) and to define the properties for the transaction (SET TRANSACTION).
Used to manage the properties of the database.
There isonly one statement in this category (ALTER SYSTEM).
--DCL--Data Contro Language--与开发关系不是很密切,⽤于权限的分配与回收
grant,revoke,data control
--Session Control
control the session properties (ALTER SESSION)
and to enable/disable roles (SET ROLE).
--System Control
--------------------------------------------------------
select的⽤法
--每个员⼯的所有信息
select*from emp;
-
-每个⼈的部门编号，姓名，薪⽔
select empno, ename, sal from emp;
--每个⼈的年薪
select ename, sal*12from emp;
--计算2*3的值
select2*3from emp;
--计算2*3的值(dual)
select2*3from dual;
--得到当前时间
select sysdate from dual;
--可以给列起别名,⽐如求每个⼈的年薪
select ename, sal*12 annual_sal from emp;
--如果别名中有空格，需要⽤双引号
select ename, sal*12 "annual sal" from emp;
--如果没有内容，则为空
select ename, sal, comm from emp; /*null*/
--当空字段参与计算，则结果是null
--例如：计算每个⼈的全年的收⼊包括⽉薪和年终奖
select ename, sal *12+ comm from emp;
--可以将多个字符串拼在⼀起。⽐如：求每个⼈的薪⽔，格式为smith-sal-123
select ename ||'-'|| sal ||'-'|| comm from emp;
--如果字符串中有单引号，需要⽤另外⼀个单引号转义，⽐如：这样⼀个字符串： he's friend
select ename ||'he''s friend'from emp;
--------------------------------------------------------
--distinct 关键词的⽤法
--求有哪些个部门
select deptno from emp;
select distinct deptno from emp;
--可以⽤来修饰多个字段
--求有哪些个部门和job的组合
select deptno , job from emp;
select distinct deptno , job from emp;
--------------------------------------------------------
where关键词的⽤法
--可以是数值类型的等值判断。⽐如：求10这个部门的所有员⼯
select*from emp where deptno =10;
--可以是字符串类型的等值判断。⽐如：求叫KING的这个⼈的信息
select*from emp where ename ='KING';
--也可以是不等值判断。⽐如：求薪⽔⼩于2000的员⼯信息
select*from emp where sal >2000;
--字符串也可以做不等值判断，⽐如：求所有ename⼤于'CBA'的员⼯信息。
select ename from emp where ename >'CBA';
--求部门不是10的部门
select*from emp where deptno <>10;
-
-求薪⽔在800和1500之间的员⼯信息
select*from emp where sal between800and1500;
--也可以写成
select*from emp where sal >=800and sal <=1500;
/*这样写则不可以
--select * from emp where 800 <= sal <= 1500;
*/
--in..的⽤法。⽐如：求薪⽔是800或者1500或正2000的员⼯信息
select ename, empno, sal from emp where sal in (800, 1500, 2000);
--相当于写成这样
select ename, empno , sal from emp where sal =800or sal =1500or sal =2000;
-
-再⽐如求姓名是KING,SMITH,AA的员⼯信息
select ename, empno , sal from emp where ename in ('KING', 'SMITH', 'AA');
--求⼊职时间在20-2⽉-81之后的员⼯信息
select ename, hiredate from emp where hiredate >'20-2⽉-81';
--------------------------------------------------------
--and or not的⽤法
--求薪⽔⼤于1000或者部门在10这个部门的员⼯信息
select*from emp where sal >1000or deptno =10;
--求薪⽔不是800或者不是1500或者不是3000的员⼯信息
select*from emp where sal <>800and sal <>1500and sal <>3000;
--也可以这样来写
select*from emp where sal not in (800, 1500, 3000);
--------------------------------------------------------
--like的⽤法
--求名字中包含ALL这三个字符的员⼯信息
select ename from emp where ename like'%ALL%';
--求名字中的第⼆个字母是A的员⼯
select ename from emp where ename like'_A%';
--特殊字符需要转义。⽐如：求员⼯中包含特殊字符%的员⼯信息
select ename from emp where ename like'%\%%'escape'\';
--------------------------------------------------------
--null的⽤法
-
-求没有年终奖的员⼯
select ename from emp where comm is null;
--求有年终奖的员⼯
select ename from emp where comm is not null;
--------------------------------------------------------
--order by的⽤法
--员⼯信息按照姓名正序排列
select ename, sal from emp order by ename asc; --ascent
--员⼯信息按照倒叙排列
select ename, sal from emp order by ename desc; --descent
--也可以是多个字段组合排列。例如：员⼯信息按照部门正序排列，并且按照姓名倒叙排列select ename, sal, deptno from emp order by deptno asc, ename desc;
--------------------------------------------------------
--function的⽤法
--把所有姓名变成⼩写
select lower(ename) from emp;
--把所有姓名变成⼤写
select upper(ename) from emp;
--求所有⼈名中包含'a'的员⼯信息不区分⼤⼩写
select ename from emp where lower(ename) like'%a%';
--截取⼦字符串，⽐如求Hello的⼀部分
select substr('Hello', 2) from dual;
--求Hello的⼀部分，并指明长度
select substr('Hello', 2, 3) from dual;
--求ascii码对应的字符
select chr(65) from dual;
--求字符对应的ascii码
select ascii('中') from dual;
--四舍五⼊
select round(23.652) from dual;
--四舍五⼊⼩数点后⾯多少位
select round(23.652, 1) from dual;
--四舍五⼊⼩数点前⾯多少位
select round(23.652, -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, 'L00,000.9999') from emp;
--将固定格式的字符串转换成数值
select to_number('$1,250.00', '$9,999.99') from dual;
-
-null当null参与计算时候，需要要nvl这个函数
select ename, sal*12+comm from emp;
select ename, sal*12+ nvl(comm, 0) 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 group by deptno;
--也可以按照多个字段来分组统计，⽐如：分部门和岗位，统计最⾼薪⽔和⾏数
select deptno, job , max(sal), count(*) from emp group by deptno, job;
--------------------------------------------------------
--重要：出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by ⼦句中。--------------------------------------------------------
--select ename, deptno, max(sal) from emp group by deptno;
--select ename, max(sal) from emp;
--求薪⽔最⾼的员⼯姓名
select max(sal) from emp;
select ename, sal from emp where sal =5000;
select ename from emp where sal = (select max(sal) from emp);
--having从句的⽤法
--求平均薪⽔是2000以上的部门
select avg(sal), deptno 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), deptno
from emp
where sal >1200
group by deptno
having avg(sal) >1500
order by avg(sal) desc;
--------------------------------------------------------
/*
把雇员按部门分组，
求最⾼薪⽔，部门号，
过滤掉名字中第⼆个字母是'A'的，
要求分组后的平均薪⽔>1500，
按照部门编号倒序排列
*/
select deptno, max(sal)
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, dname from emp , dept;
select ename, dname from emp , dept where emp.deptno = dept.deptno;
--要求每位雇员的薪⽔等级
select中distinct
select ename, sal, grade from emp, salgrade where emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal; select ename, sal, grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
--求⼯作职位是’PRESIDENT’的雇员姓名，部门名称和薪⽔等级时
select ename, dname, grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
and job ='PRESIDENT';
--求每位员⼯的姓名，及其上级经理的姓名
select empno, ename, mgr from emp;
ame, e2.ename from emp e1, emp e2 = e2.empno;
--新语法
--在SQL1992的语法规则中，语句过滤的条件和表连接的条件都被放在了where⼦句中，当条件过多时，容易造成混淆，--SQL1999修正了这个缺点，将连接条件和数据过滤条件区分开来，
--交叉连接
--结果会产⽣这两张表的笛卡尔乘积
select ename , dname from emp cross join dept; --inner join
--要⽤deptno作为等值连接条件，我们可以这样写
select ename, dname from emp join dept using(deptno);
--相当于
select ename, dname from emp join dept on emp.deptno = dept.deptno;
--也可以写成这样
select ename, dname from emp join dept on (emp.deptno = dept..deptno);
--也可以⽤于⾮等值连接
--求每位雇员的薪⽔等级
select ename, sal, grade from emp
join salgrade on (emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal);
--多个join,where组合使⽤
--(求⼯作职位是’PRESIDENT’的雇员姓名，部门名称和薪⽔等级时)
select ename, dname , grade from emp e
join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where job ='PRESIDENT';
--外连接--取出表中连接不到⼀起的多余的数据
--没有全内连接，没有右内连接
-
-其中outer也可以省略，简写为left join , right join , full join
--left inner join可以缩写成inner join 也可以缩写成join，意思是左内。
--update emp set deptno=null where ename='SMITH';
--commit;
--左内
select dname,ename from emp left inner join dept using(deptno)
--左外连接
select ename,dname from emp left outer join dept using(deptno)
--右外连接
select ename,dname from emp right outer join dept using(deptno)
--全外连接
select ename,dname from emp full outer join dept using (deptno);
--左外，右外的区别
ame from emp e1 join emp e2 = e2.empno);
ame from emp e1 left outer join emp e2 = e2.empno);
ame from emp e1 right outer join emp e2 = e2.empno);
ame from emp e1 full outer join emp e2 = e2.empno);
--什么时候⽤外连接呢:
----⽐如领导向你要所有学⽣的列表，顺便把所属的班级也列出来，就需要外连接
--在Where语句中使⽤⼦查询
-----------------------------------------------------------------
--雇员中最⾼薪⽔的⼈员名称
-
-1，先求出最⾼薪⽔
--2，再求雇员中最⾼薪⽔的⼈员名称
select ename from emp where sal = (
select max(sal) from emp
);
--有哪些⼈的薪⽔是在整个雇员的平均薪⽔之上的
select empno, ename from emp where sal > (select avg(sal) from emp);
-----------------------------------------------------------------
--雇员中哪些⼈是经理⼈
--1,⾸先查询mgr中有哪些号码
--2，再看有哪些⼈员的号码在此出现
select ename from emp where empno in (
select distinct mgr from emp where mgr is not null
);
-----------------------------------------------------------------
--在From⼦句中使⽤⼦查询
------------------------------------------------------------------
--部门平均薪⽔的等级
--1,⾸先将每个部门的平均薪⽔求出来
--2，然后把结果当成⼀张表，再⽤这张结果表和salgrade表做连接，以此求得薪⽔等级
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
-----------------------------------------------------------------
--每个部门最⾼薪⽔的⼈员名称
--1,⾸先将每个部门的最⾼薪⽔求出来
--2，然后把结果当成⼀张表，再⽤emp和这张结果表做连接，以此求得每个部门最⾼薪⽔的⼈员名称select ename, sal, emp.deptno from emp join
(select deptno, max(sal) max_sal from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
-----------------------------------------------------------------
--哪些⼈的薪⽔在部门的平均薪⽔之上
--1,⾸先将每个部门的平均薪⽔求出来
-
-2，然后把结果当成⼀张表，再⽤emp和这张结果表做连接，以此求得哪些⼈的薪⽔在部门的平均薪⽔之上select ename, sal from emp join
(select deptno, avg(sal) avg_sal from emp group by deptno) t
on (emp.sal > t.avg_sal and emp.deptno = t.deptno);
-----------------------------------------------------------------
--求部门中(所有⼈的)平均的薪⽔等级，形式如：
--  deptno  avg_grade
--  10      3.67
--  20      2.8
--  30      2.5
--1，先求每个⼈的薪⽔等级
--2，再按照部门分组，求平均数
select deptno , avg(grade) from
(select ename, deptno, grade from emp e
join salgrade s on (e.sal between s.losal and s.hisal)
)
group by deptno;
------------------------------------------------------------------------------------------
--使⽤伪字段:rownum，----------------------
------------------------------------------------------------------------------------------
--⽤来标识每条记录的⾏号，⾏号从1开始，每次递增1
select ename from emp where rownum <=5;
--oracle下rownum只能使⽤ < <=，不能使⽤ = > >= 等⽐较操作符，
select ename from emp where rownum >5;
--当rownum和order by ⼀起使⽤时，会⾸先选出符合rownum条件的记录，然后再排序
--(错误的写法)例如，当我们要求薪⽔最⾼的前5个⼈时，最直接的想法可以这样写：
select ename, sal from emp where rownum <=5order by sal desc;
--(正确的写法)可以这样写
select ename, sal
from
(select ename, sal from emp order by sal desc)
where rownum <=5;
--------------------------------------------------------
--不准⽤组函数(即MAX())，求薪⽔的最⾼值(⾯试题)
-
-第⼀种解决办法:
--1，先把所有薪⽔按照倒序排列
--2，再取第⼀⾏
select sal from (
select sal from emp order by sal desc
)
where rownum <=1;
--第⼆种解决办法:
--1，先跨表查询⾃⼰，先求出的结果中,e1.sal不可能出现最⼤数
--2，然后再not in
select distinct sal from emp where sal not in (
select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal)
);
-----------------------------------------------------------------
--求平均薪⽔最⾼的部门的部门编号
--第⼀种解决办法: