ORACLE数据库-增删改查语句大全

最近在用C#写数据库的内容，开始学习数据库语句，买了几本数据库的书，并且看了很多数据库的视频讲解，发现要么是很难读懂概念，要么是拖拉，废话一大堆，生涩难懂。于是做笔记样写了此文。此文把oracle数据库语句使用频率95%以上的列出来，基本上覆盖了我上位机工程师的全部的sql语句。  适用于：求职；初学者；学过后忘记某些语句的人；以增删改查的顺序写完下文。如果需要数据库原表及建表数据，请移步我主页查看另一篇oracle文档。如果刚好帮到你帮忙点个赞和收藏

sql语句的关键字是不管大小写的，select可以写成SELECT或SELect都行；并且和其他编程语言一样，可以跨行编写。

 

 

基本关键字：

SELECT:选择表或列；

FROM:指定数据来源；

WHERE:筛选，可以理解为加上限制条件；

INTO:将原表的结构和数据插入至新的表中；

ORDER BY:对结果进行排序（升序、降序）;

GROUP BY:对检索进行分组显示；

HAVING:对group by分组后的结果进行筛选行；

VALUES:值；

增insert 删delete 改update 查select  

一、增加语句 INSERT

1、单独增加某条记录，常用 

在dept表中加一条记录，对deptno、dname、loc、hiredate（日期）字段赋值。

INSERT into dept(deptno,dname,loc,hiredate)
values(88,'design','beijing',TO_DATE('2000-05-23', 'YYYY-MM-DD'));

2、增加记录，字段使用默认值

insert into dept values(60,'MARKET',DEFAULT)

3、筛选后批量插入

从jobs表中筛选出salary工资大于10000的记录，将这些记录插入在jobs_temp表中 

INSERT into jobs_temp
select * from jobs
where jbos.salary>10000;

4、DESC语句，查询表的结构

插入陌生表之前，可以先查看一下表的结构，避免插入非法内容。例如字段要求非空、ID编号唯一等，如果是用sql plus，可以输入desc jobs;查看jobs表的结构；如果用navicat，右键表→设计表，可以查看表的结构。

5、脚本化批量输入，类似c语言的循环输入。txt记事本写下此语句，保存为.sql格式

然后打开sql plus，输入@c:loademp     即可运行

二、删除 DELETE

1、从jobs表中删除job_id为kim的单条记录 常用

DELETE from jobs where job_id = "kim";

2、删除jobs表中所有的数据，如果误删除，可以ROLLBACK;进行回滚，恢复数据

DELETE from jobs;

3、快速删除整个jobs，不能回滚 

truncate table jobs;

三、改 UPDATE

1、将emp表中职位是销售的工资乘1.2倍（多列） 常用

update emp set sal = sal*1.2 where job = 'salesman';

2、将员工编号7788的入职时间调整

update emp set hiredate = TO_DATE('2000/05/23', 'YYYY/MM/DD') where empno=7788;

3、用修改该列为默认值

update emp set job = DEFAULT where ename = 'ufo';

4、用子查询更新。

把emp表工资低于2000的工资调整为管理者的平均工资

update emp 
set sal = (select avg(sal)
from emp where job='MANAGER')
where sal < 2000;

四、查询  SELECT

1、查询dept表中所有列  常用

select * from dept;

2、查询部分列，从dept表中查jib,name,saleraly字段的记录，别的字段不显示

select job,name ,saleraly from dept;

3、特殊语句的查询，将emp表中所有人的sal*1.1

select sal*(1+0.1) ,name from emp;

4、显示指定名称，将emp表中查询empno、ename、job列名显示为员工编号、姓名、职务，并显示这3列所有记录。

select empno"员工编号",ename"姓名",job"职务" from emp;

5、显示不重复的记录，distinct关键字

select distinct job from emp;

6、处理记录中NULL   nvl(comm,0)如果comm字段有值，就用comm的值，没有用0来代替

select ename,sal,sal+nvl(comm,0) from emp;

7、条件筛选  常用

查找emp表中工资大于1500的记录

select empno,ename,sal from emp where asl >1500;

查找emp表中工资不等于3000，5000，10000的记录

select empno,ename,sal from emp where sal <>all(3000,5000,10000);

8、模糊查询  常用   

（1）LIKE关键字

%:代表0个或多个字符

_:一个字符

如    “A%”   代表以字母A开头的任意长度字符串；   “%Z%”  代表  字符串含Z；  “A_”代表A开头后面只能跟一个字符，例如A6

从ename表中查性别不为男性的人，非

select empno,ename from emp where sexy not like "男";

从ename表中查姓名为梁*的人

如果要查的语句内同时有%和_，则用转义字符。例如从dept表中，查找姓名包含ZHANG_ *****的人，其中/是转义字符，sql规定转义字符可以是任何符号。以下两个语句都是合法的。

select * from dept
where ename like 'ZHANG/_%' escape '/';

select * from dept
where ename like 'ZHANG=_%' escape '=';

（2）介于 、不介于  between A and B   和   not between ...and   大于等于A，且小于等于B

select empno,sal from emp where sal not between 1000 and 5000;

（3）非空  is null；查找sal不是空字段的人

select empon ename,sal from emp where sal is null;

（4）IN关键字，按值查找;排除名字为kobe，james，curry的人

select  * from emp where ename not in('kobe','james','curry');

（5）逻辑判断

查找工资不在1000至10000之间的人；查找工资在小于1000或大于15000的人

select * from emp where not sal > 1000 and sal <10000;

select * from emp where sal <1000 or sal >15000;

9、分组查 GROUP BY   

一次性列出所有工作的种类;

select * from emp group by job

分组查询的时候，可以带上统计函数，常用的统计函数有：

AVG:平均值 ；COUNT：数量；MAX：最大值；MIN:最小值；SUM：总和

select job,avl(sal),sum(sal),max(sal),count(job)
from emp   group by job;

注意：（1）group up排序是升序排序；

（2）想要从emp表中查看每种职位的平均工资，语句为：

select job,avl(sal) from emp group by job;

其中，select后面有job，则group by后面也要是job

10、having语句限制分组效果

having一般和group by一起用。如果不用gruop by，having就和where语句类似，都是限制筛选的语句。但having可以用函数，如上面第九点的avg、count、max等，而where不能用函数。

如果在 SELECT语句中使用了GROUP BY子句,那么HAVING子句将被应用于GROUP BY子句创建的那些组中;如果在SELECT语句中指定了WHERE子句，而没有指定GROUPBY子句，那么HAVING 子句将被应用于 WHERE 子句的输出，并且整个输出被看作一个组;如果在 SELECT 语句中既没有指定 WHERE 子句，也没有指定GROUPBY子句，那么HAVING 子句将被应用于 FROM 子句的输出，并且将其看作一个组。

针对理解HAVING子句的作用，最好的办法就是记住 SELECT 语句中的子句处理顺序。在SELECT语句中，首先由FROM子句找到数据表，WHERE子句则接收FROM子句输出的数据而HAVING子句则接收来自GROUPBY、WHERE或FROM子句的输出。

WHERE 子句，也没有指定GROUPBY子句，那么HAVING 子句将被应用于 FROM 子句的输出，

先分组计算每个部门的平均工资，再用having过滤平均工资高于2000 

select deptno as 部门编号,avg(sal) as 平均工资 from emp 
group by deptno having avg(sal)>2000;

11、排序 order by语句

order by必须放在所有的句子后面，也就是整个语句的最后面。order默认升序排序，desc是降序

根据emp表，按照部门编号升序，工资降序的排序方式，显示员工名字、部门、工资

select ename ,deptno,sal from emp
order by deptno,sal desc;

select sal from emp where sal > 1000

12、子查询  IN、ANY、ALL

• IN：外查询匹配子查询结果中的任意一个值（只要有一个匹配就返回）。
• ANY：和（比如 >“大于”、<“小于” 等）结合，满足子查询任意一个结果即可返回。
• ALL：和（比如 >“大于”、<“小于” 等）结合，必须满足子查询所有结果才返回。

例子：查找不是销售部门（SALES）的员工信息

select empno,ename,job
from emp where deptno in
(select deptno from dept where dname<>'SALES");

1. 子查询部分：select deptno from dept where dname<>'SALES'。从dept表（部门表）中，筛选出 ** 部门名称（dname）不是 'SALES'** 的所有部门编号（deptno）。

2. 主查询部分：select empno,ename,job from emp where deptno in (...)从emp表（员工表）中，筛选出部门编号（deptno）在子查询结果中的员工，返回这些员工的员工号（empno）、姓名（ename）、职位（job）。

简单说：先找出 “非销售部门” 的编号，再匹配这些部门下的员工信息。

例子：在emp表中，查工资高于10号部门的任意一个员工工资的其他部门的员工信息

select deptno,ename,sal from emp where sal > any
(select sal from emp where deptno = 10) and deptno <> 10;

1. 子查询部分：select sal from emp where deptno = 10。从emp表中，筛选出10 号部门所有员工的工资（sal）。

2. 主查询部分：select deptno,ename,sal from emp where sal > any (...) and deptno <> 10

   最终返回这些员工的部门编号（deptno）、姓名（ename）、工资（sal）。

   • sal > any (...)：员工工资只要 ** 大于子查询返回的 “10 号部门任意一个员工的工资”** 即可（只要比 10 号部门里工资最低的人高，就符合条件）。
   • deptno <> 10：同时要求员工不属于 10 号部门。

简单说：找 “非 10 号部门” 中，工资比 10 号部门里至少某一个人高的员工。

例子：在emp表中，查询工资高于 30 号部门所有员工工资的员工信息，返回这些员工的部门编号（deptno）、姓名（ename）和工资（sal）。

select deptno,ename,sal from emp where sal > all
(select sal from emp where deptno = 30);

• 子查询 select sal from emp where deptno = 30：先找出 30 号部门所有员工的工资。
• 主查询 where sal > all (...)：要求员工工资必须比子查询中 30 号部门的每一个员工工资都高，再结合前面的select，最终返回符合条件的员工的部门编号、姓名、工资。

13、关联查询

输出每个工资高于同职位的平均工资的员工信息

select empno,ename,sal from emp f
where sal > (select avg(sal) from emp where job = f.job)
order by job;

from emp f 中，f 是给 emp 表起的别名。

步骤	外查询行为	内查询行为	判断逻辑
1	取出第 1 行员工（比如职位是 CLERK）	执行 select avg(sal) from emp where job = f.job，即计算所有 CLERK 职位的平均工资	判断当前员工工资是否 > 「CLERK 平均工资」
2	取出第 2 行员工（比如职位是 MANAGER）	执行 select avg(sal) from emp where job = f.job，即计算所有 MANAGER 职位的平均工资	判断当前员工工资是否 > 「MANAGER 平均工资」
...	重复上述过程，直到遍历完 emp 所有行	每次都根据 “当前行的职位” 动态计算平均工资	只保留 “工资> 所属职位平均工资” 的行

 

14、表的关联查询  常用

（1）表的简称

给emp表叫e，dept表叫d 。非常常用，特别在业务稍微复杂的系统中非常常用

select e.empno as 员工编号,e.name as 员工名称,d.dname as 部门名称
from emp e,dept d
where e.deptno=d.deptno
and e.job='MANAGER';

（2）内连接：把多张表中符合连接条件的数据行合并查询，只会显示符合条件的结果，不符合条件的一个都不显示  INNER JOIN....ON

通过deptno将emp表和dept表进行连接

select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门
from emp e inner join dept d
on e.deptno=d.deptno;

（3）外连接

分为左外连接left join、右外连接right join、完全外连接full join。

左外连接：查询结果（右表）中包含了满足条件的记录，并包含了左表中不满足条件的记录。

右外连接：查询结果（左表）中包含了满足条件的记录，并包含了右表中不满足条件的记录。

 

什么意思？是不是一头雾水，这是我看别的视频说的，一脸懵逼。我就举个最简单的例子，左外连接：左表是全班同学名单，右表是获得三好学生同学名单，如此查询，会显示全班图像名单，并且多一列该同学是否获得三好学生。右外连接相反，右表显示全班同学名单，左表显示三好学生名单。

全班同学姓名	是否获得三好学生
张三	是
李四	NULL

 

总结：左表的所有行都保留，右表匹配不上的补NULL；

           右表的所有行都保留，左表匹配不上的补NULL；

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e left join dept d
on e.deptno=d.deptno;

完全外连接：左右表所有行都保留，有空的地方补NULL

select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e full join dept d
on e.deptno=d.deptno;

15、自然连接 natural join

系统会自动将第一张表的列和第二张表列中相同名称的列进行自动连接;

从emp表中，选出工资高于2000的人，将这些人在dept表中的信息显示出来

select empno,ename,job,dname
from emp natural join dept
where sal > 2000;

16、自连接

 用户自定义建立两表之间的关系。

例如查询所有管理者管理下属的信息。

select em2.ename 管理者,em1.ename as 下属员工
from emp em1 left join emp em2
on em1.mgr=em2.empno
order by em1.mgr;