接触 PL/SQL 第三天

今天的章节主要介绍了基础SQL的使用以及一些细节

1.先来说说查询语言（DQL）吧

这是在数据库中使用最多，也是最灵活的一个

结构：

select-- 字段名/函数/表达式 等等from--表/视图等where--跟条件group by--分组字段order by--排序字段

下面写一些具体的查询语句

select * from dept;--查询部门表所有信息select ename,sal from emp;--查询员工表所有员工的姓名，工资select ename,hiredate from emp;--查询所有员工的姓名，入职如期，日期按默认格式显示如(20-2月-81 表示 81年2月20日)select ename,to_char(hiredate,'yyyy-mm-dd') from emp;--查询所有员工的姓名，入职日期，to_char()函数 日期按指定格式显示 如（1981-2-20）select distinct deptno,job from emp;--显示所有部门号及岗位，去掉重复记录 distinct关键字的作用就是去重复记录select ename,sal*12 as "年薪" from emp;--查询所有员工的姓名，月薪的12倍显示字段名为 年薪select ename,sal,comm,sal+nvl(comm,0) from emp;--查询所有员工的姓名，薪资，奖金，月收入 nvl(comm,0) comm为null是返回0 否则返回comm 注：任何值与null相加结果都为nullselect ename||' is a '||job as "任职" from emp;--使用||进行字符串拼接

使用条件判断的查询

select ename,sal from emp where sal>2000;--工资大于2000的员工姓名，工资select sal,job from emp where lower(ename)='scott';--名字叫scott的工资，职位。 lower()函数将字符串转为小写 对应的upper()函数将字符串转为大写select ename,sal from emp where sal between 2000 and 3000;--查询工资在2000-3000之间的员工姓名，工资select ename,sal from emp where ename like '_a%';--查询名字第二个字母为a的员工姓名，工资，like关键字用来模糊匹配,_表示单个任意字符，%表示多个任意字符select ename,sal from emp where sal in (1200,2500);--查询工资在指定集合中的员工姓名，工资select ename,sal,comm from emp where comm is not null;--查询有奖金的员工姓名，工资，奖金select ename,sal,job,deptno from emp where deptno=20 and job='clerk';--查询20号部门岗位为clerk的员工资料 注意：and/or/可以作为条件连接关键字not指定非意向，优先级顺序 比较符号>not>all>or 如果想改变优先级可以使用()包围起来

查询并排序

select ename,sal from emp where deptno=30 order by sal asc;--查询30号部门员工的姓名，工资，按工资降序排列。asc为升序排列，desc为降序排列，默认情况下就是升序排列，asc可以不写，排序时null值为最大select ename,sal,deptno from emp order by deptno asc,sal desc;--可以对多个字段进行排序，先对部门升序排序，再对同部门的工资降序排序select ename,sal*12 as "年薪" from emp order by "年薪" desc;--按年薪降序排列select ename,sal,job from emp order by 2;--按第二个字段排序;

分组查询后面再讲

2.增，删，改（DML语言）

1)增加 需要注意的地方

字段为数字时可以直接使用数字，字段为字符或日期时需要加单引号

插入数据时必须满足约束条件，如not null 等

插入数据时 数据必须与列的顺序，个数保持一致

insert into dept values(50,'TRAIN','北京');--往部门表插入一条记录，部门号为50，部门名为TRAIN，部门所在地为北京insert into emp (empno,ename,sal,job) values(2258,'JOHN',3000,'clerk');--往员工表插入一条记录，包含员工号，名字，工资，岗位，其他没写字段为null值--使用子查询插入 先建立emp_dept20表，共empno,ename,sal,deptno四个字段insert into emp_dept20select empno,ename,sal,deptno from emp where deptno=20;--查询员工表20号部门的员工数据，插入到20号部门表--下面是直接装载的写法insert into emp_dept20select /*append*/ empno,ename,sal,deptno from emp where deptno=20;--操作大批量数据时效率非常高，缺点每次操作必须提交(会对表生成6级枷锁)，否则报错.--多表插入  ，同样先建好表 deptno10,deptno20,deptno30,deptno50insert allwhen deptno=10 then into deptno10when deptno=20 then into deptno20when deptno=30 then into deptno30else into deptno50;select * from emp;--all关键字：记录可能会被重复使用, 用first关键字：记录将不会被重复使用

2)修改 需要注意的地方

字段为数字时可以直接使用数字，字段为字符或日期时需要加单引号

更新数据时必须满足约束条件

更新数据时 数据必须与列的数据类型相匹配

注意：更新数据时, 通常需要加条件判断(切记)

update emp set sal=2550 where ename='scott';--将scott的工资改为2550update emp set sal=sal*1.1 where deptno=10;--给10号部门的员工涨10%工资update emp set hiredate=to_date('1991-3-20','yyyy-mm-dd') where empno=7788;--将员工号为7788的员工入职日期修改 to_date()函数将指定的字符串按指定的格式转换为日期--子查询更新update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='smith')where ename='scott';--将scott的职位，工资，奖金改成跟smith一样

3)删除

删除要注意的也是记得要跟上条件判断，如果不加则删除全表数据

delete from emp where ename='smith';--开除指定员工delete from emp where sal<1000;--开除工资少于1000的员工delete from emp;--删除员工表的所有数据truncate table emp;--删除表的所有数据，效率更高，不支持事务，delete支持事务

3.事务操作

commit; 提交事务

rollback; 回滚事务

DML操作都需要配合事务操作一起使用

commit 就不多说了

rollback 可以配合保存点实现回滚事务到指定点

写个例子

savepoint a;--保存点adelete from emp where deptno=10;--正常操作savepoint b;--保存点bdelete from emp;--误删数据rollback to b;--回滚到b点

如果使用rollback则全部回滚

现在有个需求：不统计当天16点后的销售信息

可以设置只读事务

例子：

set transaction read only;--事务开始第一句select sal from emp where ename='scott';--会话A 假设此时查询结果为2000update emp set sal=2500 where ename='scott';--会话B 修改为2500commit;select sal from emp where ename='scott';--会话A 查询结果依然为2000

设置只读事务时，本会话不能进行DML操作

顺序事务：在支持只读事务的同时，也支持本会话进行DML操作

set transaction isolation level serializable;--事务开始第一句select sal from emp where ename='scott';--会话A 假设此时查询结果为2000update emp set sal=2500 where ename='scott';--会话B 修改为2500commit;select sal from emp where ename='scott';--会话A 查询结果依然为2000update emp set sal=3000 where ename='scott';--会话A 可以DML操作commit;

4.数据分组

说到分组就得说分组函数了

1）常用分组函数：MAX（最大值），MIN（最小值），AVG（平均值），SUM（求和），COUNT（记录数）

select max(sal),min(sal),avg(sal),sum(sal) from emp;--员工表的最大最小平均工资及工资总和select avg(sal),count(deptno),deptno from emp group by deptno;--每个部分的平均工资，部门人数，部门号select count(*) from emp;--员工表记录数;

count(*),count(1),count(字段) 说明：

这三者基本上没什么差别,效率也差不多 需要注意到是count(*)在遇到null值时也会记录数量，而另外两个不会

统计表记录数时使用count(*)比合适,默认count(*)会按照表主键进行统计

分组函数一般配合having(不能使用where)进行条件过滤

select avg(sal),deptno from emp group by deptno having avg(sal)>2000;--查询部门平均工资大于2000的部门平均工资及部门号

2）分组函数要注意到地方：

分组函数只能出现在字段，having子句，order by子句中

如果select语句中包含group by，having以及order by子句，则order by子句必须放在最后

如果选择字段包含字段，表达式和分组函数，那么这些字段及表达式必须出现在group by子句中

3）高级分组函数

          岗位

部门号

CLERK

ANALYST

MANAGER

PRESIDENT

SALESMAN

10

1300

 

2450

5000

 

20

2050

3500

2975

 

 

30

950

 

2850

 

1400

rollup() 生成横向统计结果：

select deptno,job,avg(sal) from emp group by rollup(deptno,job);

          岗位

部门号

CLERK

ANALYST

MANAGER

PRESIDENT

SALESMAN

横向统计

10

1300

 

2450

5000

 

2916

20

2050

3500

2975

 

 

2815

30

950

 

2850

 

1400

1566

合计

 

 

 

 

 

2301

cube() 生成横向+纵向结果

select deptno,job,avg(sal) from emp group by cube(deptno,job);

          岗位

部门号

CLERK

ANALYST

MANAGER

PRESIDENT

SALESMAN

横向统计

10

1300

 

2450

5000

 

2916

20

2050

3500

2975

 

 

2815

30

950

 

2850

 

1400

1566

纵向统计

1587

3500

2758

5000

1400

 

合计

 

 

 

 

 

2301

grouping()

用于统计结果是否用到了特定的字段，如果用到了返回0，否则返回1

select deptno,job,avg(sal),grouping(deptno),grouping(sal) from emp group by rollup(deptno,job);

grouping sets()

将几个单字段的分组结果显示在一张表中

select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);

5.连接查询 多张表或视图连接

select e.ename,e.job,d.deptno,d.dname from emp e,dept dwhere e.deptno=d.deptno;--指定连接条件

内连接，外连接

select e.ename,e.job,d.deptno,d.dname from emp e inner join dept d--内连接(两边的表如何一边匹配到null则忽略该记录)on e.deptno=d.deptno;--指定连接条件select e.ename,e.job,d.deptno,d.dname from emp e left join dept d--左外连接(左边匹配到null则忽略该记录)on e.deptno=d.deptno;--指定连接条件select e.ename,e.job,d.deptno,d.dname from emp e right join dept d--又外连接(右边匹配到null则忽略该记录)on e.deptno=d.deptno;--指定连接条件select e.ename,e.job,d.deptno,d.dname from emp e full join dept d--全连接(不管是否配到null都显示该记录)on e.deptno=d.deptno;--指定连接条件select ename,job,d.deptno,dname from emp natural join dept d;--两张表关联字段名一样时，可直接使用这种模式，进行内连接，不用写连接条件

6.子查询

在查询中嵌套查询

select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10);

all(集合) 大于集合中所有值

select ename,job,sal,deptno from emp where sal>all (select distinct sal from emp where deptno=10);

any(集合) 大于集合中任一一个数即可

select ename,job,sal,deptno from emp where sal>any (select distinct sal from emp where deptno=10);

多列子查询

select ename,job,sal,deptno from emp where (deptno ,job)=(select deptno,job from emp where ename='smith');--查询和smith同部门同职位的员工

关联子查询

select ename,job,sal,deptno from emp e where exists (select 1 from dept d where e.deptno=d.deptno and d.loc='NEW YORK');--查询在纽约工作的员工信息

from 子句中也可以使用子查询

select ename,job,sal from emp e,(select deptno,avg(sal) as avgsal from emp group by deptno) dwhere e.deptno=d.deptno and e.sal>d.avgsal;--查询工资大于本部门平均工资的员工信息

再说下视图view

create or replace view emp_dept20 asselect e.*,d.name,d.loc from emp e,dept d where d.deptno=e.deptno and e.deptno=20;--将20号部门的员工信息与部门信息整合到一张视图中

这种普通视图是虚假的，实际查询的时候还是会从emp，dept这两张基础表中查询数据

另外一种实体视图则真实拥有存储空间，是真实存在的表，定时从基表更新数据，查询时直接从实体视图的表中查询数据

create materialized view emp_dept20 asselect e.*,d.name,d.loc from emp e,dept d where d.deptno=e.deptno and e.deptno=20;--将20号部门的员工信息与部门信息整合到一张视图中

7.合并查询

select ename,sal,job from emp where sal>2000;unionselect ename,sal,job from emp where job='manager';

语法都一样，合并条件不同

union：并集合并，将两个集合中重复的记录保留一份，其余不变

union all：全集合并，合并两个集合，保留重复字段

intersect：交集合并，将两个集合中重复的记录保留一份，其余删除

minus：差集合并，将两个集合重复的记录全部删除，其余保留不变

8.其他复杂查询

1)with子句重用查询

with sumary as (select dname,sum(sal) as sumsal from emp e,dept d where e.deptno=d.deptno group by deptno)select dname,sumsal from sumary where sumsal>(select sum(sumsal)/3 from sumary);--查询部门工资总和大于全部工资总和三分之一的部门名及部门工资总和

2）case表达式

select ename,sal,case when sal>3000 then 3     when sal>2000 then 2     else 1 end as gradefrom emp where deptno=10;

3) 层次结构查询

start with 指定开始记录行

connect by 指定父子记录行之间的关系
frior 引用父行记录

select lpad(' ',3*(level-1))||ename ename,       lpad(' ',3*(level-1))||job job from emp--左缩进体现嵌套关系where job<>'clerk' start with mgr is null--起始行mgr字段为空connect by mgr=prior empno;--指定子行mgr字段等于父行empno字段

查询后的效果图：

今天的内容有点多，主要是sql语句的各种应用了，记不住也没关系，知道就行，忘记了查一查就可以

这两天工作体检的事情有点累，6点半写到现在，吃泡面去