SQL语句基础用法大全5（DML）

Author： Master
发布时间：June 7, 2021
443 views
No comments
10228 words
Categories：数据类

###条件查询

select e.ename,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;

###统计函数

分类：

COUNT():查询表中的数据记录

AVG(): 求出平均值

SUM(): 求和

MAX(): 求出最大值

MIN(): 求出最小值

统计出公司的所有雇员每个月支付的平均工资及总工资

select count(empno),sum(sal),avg(sal) from emp;
统计雇员中的最高和最低工资

select max(sal),min(sal) from emp;
注意：

COUNT()函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，那么COUNT()也会返回数据，只是这个数据是“0”.

如：select count(ename) from bonus; #返回数字0

select sum(sal) from bonus; #返回NULL

如果使用其他函数，则有可能返回NULL值，但是COUNT()永远都会返回一个具体的数字，记住了，记住了，记住了。

###分组统计

什么情况下需要分组统计？

1、男的分一组，女的分一组

2、年龄分组，成年和未成年

3、地区分组，上海和北京，

这些信息如果都保存在数据库中，肯定在数据库的某一列上存在重复数据，例如按照性别分组的时候，有男和女，按照年龄分组，有一个范围的重复，按照地区的话有一个地区的信息重复

注意：当数据重复的时候分组才有意义，一个人也可以单独分一组，但是么有意义。

如果需要分组，可以使用GROUP BY子句，语法如下：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

按照部门编号分组，求出每个部门的人数和平均工资

select deptno,count(empno),avg(sal) from emp
group by deptno;
按照职位分组，求出每个职位的最高和最低工资

select job,max(sal),min(sal) from emp
group by job;
注意：分组函数有以下要求：

1、分组函数可以在没有分组的时候单独使用，可是不能出现其他的查询字段，如：

正确使用：select count(empno) from emp;

错误使用，出现其他字段：select empno,count(empno) from emp;

2、如果要进行分组，则select子句之后，只能出现分组的字段和统计函数，其他字段不能出现，如：

正确使用：select job,count(empno),avg(sal) from emp group by job;

错误用法：select empno,job,count(empno),avg(sal) from emp group by job;

3、分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段，如：

按照职位分组，统计平均工资最高的工资

先统计出各个职位的平均工资

select job,avg(sal)
from emp
group by job;
查询平均工资最高的工资

错误使用：select job,max(avg(sal)) from emp group by job;

正确使用：select max(avg(sal)) from emp group by job;

查询出每个部门的名称，部门的人数，平均工资

思路：

1、确定所需要的数据表

dept表：每个部门的名称

emp表： 统计出部门的人数，平均工资

2、确定已知的关联字段

emp.deptno=dept.deptno
做法：

1、将dept表和emp表的数据关联

select d.dname,e.ename,e.sal
from dept d,emp e
where d.deptno=e.deptno;
2、查看上面语句执行的结果，有重复值，就可以进行分组查询，但是此时与之前的分组不太一样，之前的分组是针对一张实体表，

但是对于以上的数据是通过查询结果显示的，所以是一张临时的虚拟表，但是不管是实体表和虚拟表，只要是有重复，那么就直接进行分组

select d.dname,count(e.empno),avg(e.sal) from dept d,emp e
where d.deptno=e.deptno
group by d.dname;
通过结果可以看到，这个分组并不合适，部门一共有四个，因为已经引入了dept表，该表存在了四个部门，所以应该通过左右连接来改变查询的结果

select d.dname,count(e.empno),avg(e.sal) from dept d,emp e
where d.deptno=e.deptno(+)
group by d.dname;
查询结果发现OPERATIONS部门的平均工资为NULL值，并不符合结果预期，所以可以通过NVL函数来进行处理，如下：

select d.dname,count(e.empno),trunc(nvl(avg(e.sal),0)) avg
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.dname;

###多字段分组

要求显示每个部门的编号，名称，位置，部门的人数和平均工资

思路：

1、确定所需要的数据表

dept表：每个部门的编号，名称，位置

emp表： 统计出部门的人数，平均工资

2、确定已知的关联字段

emp.deptno=dept.deptno
做法：

1、将emp表和dept表关联

select d.deptno,d.dname,d.loc,e.empno,e.sal
from dept d,emp e
where d.deptno=e.deptno(+);
2、查看以上语句执行的结果，发现有三个列存在重复数据(deptno,dname,loc),所以在分组上的GROUP BY子句中就可以写上三个字段：

select d.deptno,d.dname,d.loc,count(e.empno),
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc;
以上就是分组查询，不管是单字段还是多字段，一定要有一个前提就是存在了重复数据。

在上个范例的基础上，要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000

###使用where子句

select d.deptno,d.dname,d.loc,count(e.empno) mycount,trunc(nvl(avg(e.sal),0)) myavg
from dept d,emp e
where d.deptno=e.deptno(+)
and avg(e.sal)>2000
group by d.deptno,d.dname,d.loc;
该语句会报错，如下：

where d.deptno=e.deptno(+) and avg(e.sal)>2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
意思是说在where子句中不能使用统计函数，这和where子句的功能有关。

如果要对分组后的数据再次进行过滤，需要使用HAVING子句，语法格式如下：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[HAVING 分组后的过滤条件(可以使用分组函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

下面使用HAVING进行过滤

select d.deptno,d.dname,d.loc,count(e.empno) mycount,trunc(nvl(avg(e.sal),0)) myavg
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc
having avg(sal)>2000;
注意：WHERE和HAVING的区别

WHERE:在执行GROUP BY操作之前进行的过滤，表示从全部数据中进行过滤，不能使用统计函数；

HAVING: 在GROUP BY分组之后的再次过滤，可以使用统计函数。

思考练习题二：

显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于5000，输出结果按照月工资的合计升序排列。

###子查询(核心重点)

子查询=简单查询+限定查询+多表查询+统计查询的综合体

在之前说多表查询不建议使用，因为性能差，但是多表查询最有利的替代者就是子查询，在实际的开发中使用最多的就是子查询。

语法：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数,(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

FROM 表名称 [别名],[表名称 [别名],…],(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

[WHERE 条件(s)](

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

[GROUP BY 分组字段1 [,分组字段2,…]]

[HAVING 分组后的过滤条件(可以使用统计函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

WHERE:子查询一般只返回单行单列，多行单列，单行多列的数据

FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现。

要求查出工资比SMITH还要高的全部信息

思路：

1、首先要知道SMITH的工资是多少

select sal from emp where ename=’SMITH’;
2、由于此时返回的是单行单列的数据，所以这个子查询可以在WHERE中出现

select * from emp where sal>(
select sal
from emp
where ename=’SMITH’);
要求查询出高于公司平均工资的全部雇员信息

思路：

1、公司的平均工资是多少？

select avg(sal) from emp;
2、由于此时返回的是单行单列的数据，在WHERE之中出现

select * from emp where sal>(
select avg(sal)
from emp);
以上返回的是单行单列，但是在子查询中，也可以返回单行多列的数据，只是这种子查询很少出现，如：

select * from emp where(job,sal)=(
select job,sal
from emp
where ename=’ALLEN’);
如果子查询返回的是多行单列数据的话，这个时候就需要使用三种判断符来进行判断：IN,ANY,ALL

1、IN操作符：用于指定一个子查询的判断范围

select * from emp
where sal in(
select sal
from emp
where job=’MANAGER’);
在使用IN的时候还要注意NOT IN的问题，如果使用NOT IN操作，在子查询中，如果有一个内容是NULL，则不会有任何查询结果，

因为如果有NULL，则会查询所有数据，如果数据量太大就会导致有漏洞产生，所以加入限制。

select * from emp
where sal not in(
select sal
from emp
where job=’MANAGER’);
2、ANY操作符，与每一个内容相匹配，有三种匹配形式

1、=ANY:功能与IN操作符是完全一样；

select * from emp
where sal=any(
select sal
from emp
where job=’MANAGER’);
2、>ANY:比子查询中返回记录最小的还要大的数据

select * from emp
where sal>any(
select sal
from emp
where job=’MANAGER’);
3、

查询出每个部门的编号，名称，位置和部门人数，平均工资

之前使用的是多字段分组统计完成的，如：

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
这时候实际产生了笛卡儿积，共56条记录。

下面使用子查询来完成，所有的统计查询只能在GROUP BY中出现，所以在子查询之中负责统计数据，而在外部的查询之中，负责将统计数据和dept表数据相统一。

select d.deptno,d.dname,d.loc,nvl(temp.count,0),nvl(temp.avg,0)
from dept d,(
select deptno dno,count(empno) count,avg(sal) avg
from emp
group by deptno) temp
where d.deptno=temp.dno(+);
这个子查询和之前的两种方法的比较：

1、子查询中统计的记录是14条，最终统计的显示结果是3条结果

2、dept表之中一共有4条记录

3、如果现在产生笛卡儿积的话只有12条，在加上雇员的14条，一共才26条

在开发中，使用子查询可以提高效率和节省性能，大部分情况下：如果最终查询结果中出现了select语句，但是又不能直接使用统计函数时，就在子查询中统计信息。

###数据更新(重点)

增删改查

数据更新包括数据的增加、修改、删除。

为了做实验，我们将emp表复制一份，使用以下指令：

create table myemp as select * from emp;
这种语法是oracle支持的，其他的数据库不一样

数据的增加

语法：

INSERT INTO 表名称 [(字段1,字段2,字段3,…)] VALUES(值1,值2,值3,…)

注意：如果需要进行增加数据的话，则以下的几种数据类型要分别处理：

增加数字：直接编写数字，如：123；

增加字符串：字符串应该使用”’”声明；

增加DATE数据：

第一种：可以按照已有的字符串的格式编写字符串，如：‘20-6月-06’；

第二种：利用TO_DATE函数将字符串变为DATE型数据；

第三种：如果设置的时间为当前系统时间，则使用SYSDATE;

对于数据的增加有两种操作格式：

完整型：

insert into myemp (empno,ename,hiredate,sal,mgr,job,comm)
values (8888,’zhangsan’,TO_DATE(‘1960-08-17’,’yyyy-mm-dd’),8000,7369,’daza’,1000);

insert into myemp (empno,ename,hiredate,sal,mgr,job,deptno)
values (8889,’lisi’,SYSDATE,3000,7369,’daza’,30);
简便型：不写列名称

错误：

insert into myemp
values (8889,’lisi’,SYSDATE,3000,7369,’daza’,30);
正确：需要按照列名称的顺序来写，必须符合字段的要求，一般开发中不使用

insert into myemp values (8890,’wangwu’,’daza’,7369,sysdate,3000,null,30);
数据的修改

语法：

UPDATE 表名称 SET 更新字段1=更新值1,更新字段2=更新值2,…[WHERE 更新条件(s)];

示例；

更新员工编号为7369的员工的工资为5000，奖金为2000，职位升级为’MANAGER’,职位更新时间为当前时间

update myemp set sal=5000,comm=2000,job=’MANAGER’,hiredate=sysdate where empno=7369;
所有员工的工资统一改为7500

update myemp set sal=7500;
注意：如果更新的时候不加上更新条件，则意味着更新全部数据，一定要注意，当数据量达到一定程度时，这种方式不可取。

为了后面做实验，我们需要回滚数据表

SQL> ROLLBACK;
数据的删除

语法：

DELETE FROM 表名称 [WHERE 删除条件(s)];

示例:

删除1987年入职的员工的信息

delete from myemp where TO_CHAR(hiredate,’yyyy’)=1987;
注意：如果删除的时候没有相应匹配条件的时候，则更新记录为0，更新操作也一样。

删除表中的所有数据

delete from myemp;
注意：对于删除操作，尽可能少使用，因为删除操作对于查询操作要危险许多。

提示：对于删除操作，在开发时对于所有的删除操作之前先给出一个提示框，以防止误删除。

###事务处理

对于数据表的操作，查询要比更新操作更安全，因为更新操作有可能会出现错误，导致没有按照既定的要求正确的完成更新操作。

在很多时候更新可能由多条语句共同完成，如银行转账：

-判断A的账户上是否有5000W select yue+shouxufei>5000+sxf from zhanghu where id=a

-判断B的账户状态是否正常 select id,status from zhanghu where id=b

-从A的账户上移走5000W update zhanghu set yue-5000 where id=a

-向B的账户上增加5000W update zhanghu set yue+5000 where id=b

-向银行支付手续费5W update zhanghu set yue+shouxufei where id=yinhang

以上五个数据操作是一个整体，可以理解为一个完整的业务，如果其中第三点出错，其他操作该怎么办？

如果有操作出现错误，那么其他操作应该不再继续执行，并且都回归到最原始的状态，而这一个流程的操作实际上就是事务的操作。

回滚之前的操作

rollback;

再次查看myemp表，确认其中的数据情况

select * from myemp;
所有的事务处理都是针对每一个会话进行的，在oracle中，把每一个连接到数据库的用户都称为一个会话,每一个会话之间彼此独立，互不通信，每一个会话独享自己的事务控制，而事务控制之中主要使用两个命令：

事务的回滚：ROLLBACK,更新操作回到原点

事务的提交：COMMIT,真正的发出更新请求，一旦提交后无法回滚

示例：

在会话1中删除一条数据，从另一个会话来查询数据

会话1 delete from myemp where empno=7369;

会话1 select * from myemp;

会话2 select * from myemp;

在会话1中回滚之前的更新操作，再次删除数据，然后提交，再在两个会话中查询数据,在会话1中进行回滚发现已经无法回滚了。

会话1 delete from myemp where empno=7369;

会话1 commit;

会话1 select * from myemp;

会话2 select * from myemp;

注意：这种事务控制会出现一些问题，例如，某一个会话在更新数据表的时候还没有提交事务，其他会话是无法进行更新的，必须等待之前的会话提交后才可以。

示例：

死锁,会话1执行更新后，在没有提交之前，会话2进行更新会出现等待

会话1 update myemp set sal=9000 where empno=7839;

会话2 update myemp set sal=8000 where empno=7839;

这种问题从大的方面来讲可以称作死锁，但是在oracle之中死锁有很多种类.

所有的数据更新一定都会受到事务的控制。

###数据伪列

ROWNUM(重点)

ROWNUM为每一个显示的记录都会自动的随着查询生成的行号。

示例：

查询emp表的rownum，empno,ename,job,hiredate和sal

select rownum,empno,ename,job,hiredate,sal from emp;
该行号不是永久的，会随着查询的显示变化而变化

查询emp表的rownum，empno,ename,job,hiredate和sal

select rownum,empno,ename,job,hiredate,sal from emp where deptno=30;
查询前五条记录

select rownum,empno,ename,job,hiredate,sal from emp where rownum<=5;
查询6-10行记录

错误做法：

select rownum,empno,ename,job,hiredate,sal from emp where rownum between 6 and 10;

执行完成后并没有返回任何数据，因为rownum不是真实列，正确的思路是先查询前10条记录，然后再查询后5条记录，需要使用子查询

select * from (
select rownum rn,empno,ename,job,hiredate,sal from emp
where rownum<=15) temp
where temp.rn>10;

ROWID

ROWID表示的是每一行数据保存的物理地址的编号。

查看ROWID

select rowid,deptno,dname,loc from dept;
查看完成后，保存该结果，下面的案例要和这个结果做比较

每一条记录的ROWID都不会重复，所以即使所有列的内容重复，ROWID也不会重复.

格式为：AAAL+XAAEAAAAANAAA

其中： 数据对象号：AAAL+X

相对文件号：AAE

数据块号： AAAAAN

数据行号： AAA

###总结：

1、多表查询：在进行查询语句编写的时候，一定要确定所需要关联的数据表，而且只要是表的关联查询，就一定会存在笛卡儿积的问题，使用关联字段消除此问题。在使用多表查询的时候要考虑到左右连接的问题，oracle之外的数据库可以使用SQL1999语法控制左右连接。

2、所有的统计函数是用于进行数据统计操作的，而统计要在分组中进行/或者是单独使用，分组使用GROUP BY子句，是在某一列上存在重复数据的时候才会使用分组操作，而分组后的过滤使用HAVING子句完成，所有的分组函数可以嵌套，但是嵌套之后的分组函数之中不能再有其他的查询字段，包括分组字段。

3、子查询：结合限定查询、多表查询、分组统计查询完成各个复杂查询的操作，子查询一般在WHERE和FROM之后出现较多。

4、数据库的更新操作一定要受到事务的控制，事务的两个命令:COMMIT,ROLLBACK,每一个连接到数据库上的用户都用一个会话来表示。

5、数据表的分页查询显示依靠ROWNUM伪列，这个在以后的开发当中必定要使用。

>转自：http://www.uml.org.cn/sjjm/201905173.asp

Last modification：August 1, 2021

欢迎留下您的脚印

SQL语句基础用法大全5（DML）

Master • 2021 年 06 月 07 日

###条件查询

select e.ename,e.job,m.ename
from emp e,emp m
where e.mgr=m.empno;

###统计函数

分类：

COUNT():查询表中的数据记录

AVG(): 求出平均值

SUM(): 求和

MAX(): 求出最大值

MIN(): 求出最小值

统计出公司的所有雇员每个月支付的平均工资及总工资

select count(empno),sum(sal),avg(sal) from emp;
统计雇员中的最高和最低工资

select max(sal),min(sal) from emp;
注意：

COUNT()函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，那么COUNT()也会返回数据，只是这个数据是“0”.

如：select count(ename) from bonus; #返回数字0

select sum(sal) from bonus; #返回NULL

如果使用其他函数，则有可能返回NULL值，但是COUNT()永远都会返回一个具体的数字，记住了，记住了，记住了。

###分组统计

什么情况下需要分组统计？

1、男的分一组，女的分一组

2、年龄分组，成年和未成年

3、地区分组，上海和北京，

注意：当数据重复的时候分组才有意义，一个人也可以单独分一组，但是么有意义。

如果需要分组，可以使用GROUP BY子句，语法如下：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

按照部门编号分组，求出每个部门的人数和平均工资

select deptno,count(empno),avg(sal) from emp
group by deptno;
按照职位分组，求出每个职位的最高和最低工资

select job,max(sal),min(sal) from emp
group by job;
注意：分组函数有以下要求：

1、分组函数可以在没有分组的时候单独使用，可是不能出现其他的查询字段，如：

正确使用：select count(empno) from emp;

错误使用，出现其他字段：select empno,count(empno) from emp;

2、如果要进行分组，则select子句之后，只能出现分组的字段和统计函数，其他字段不能出现，如：

正确使用：select job,count(empno),avg(sal) from emp group by job;

错误用法：select empno,job,count(empno),avg(sal) from emp group by job;

3、分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段，如：

按照职位分组，统计平均工资最高的工资

先统计出各个职位的平均工资

select job,avg(sal)
from emp
group by job;
查询平均工资最高的工资

错误使用：select job,max(avg(sal)) from emp group by job;

正确使用：select max(avg(sal)) from emp group by job;

查询出每个部门的名称，部门的人数，平均工资

思路：

1、确定所需要的数据表

dept表：每个部门的名称

emp表： 统计出部门的人数，平均工资

2、确定已知的关联字段

emp.deptno=dept.deptno
做法：

1、将dept表和emp表的数据关联

但是对于以上的数据是通过查询结果显示的，所以是一张临时的虚拟表，但是不管是实体表和虚拟表，只要是有重复，那么就直接进行分组

select d.dname,count(e.empno),trunc(nvl(avg(e.sal),0)) avg
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.dname;

###多字段分组

要求显示每个部门的编号，名称，位置，部门的人数和平均工资

思路：

1、确定所需要的数据表

dept表：每个部门的编号，名称，位置

emp表： 统计出部门的人数，平均工资

2、确定已知的关联字段

emp.deptno=dept.deptno
做法：

1、将emp表和dept表关联

在上个范例的基础上，要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000

###使用where子句

如果要对分组后的数据再次进行过滤，需要使用HAVING子句，语法格式如下：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[HAVING 分组后的过滤条件(可以使用分组函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

下面使用HAVING进行过滤

WHERE:在执行GROUP BY操作之前进行的过滤，表示从全部数据中进行过滤，不能使用统计函数；

HAVING: 在GROUP BY分组之后的再次过滤，可以使用统计函数。

思考练习题二：

###子查询(核心重点)

子查询=简单查询+限定查询+多表查询+统计查询的综合体

在之前说多表查询不建议使用，因为性能差，但是多表查询最有利的替代者就是子查询，在实际的开发中使用最多的就是子查询。

语法：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数,(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

FROM 表名称 [别名],[表名称 [别名],…],(

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

[WHERE 条件(s)](

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数

FROM 表名称 [别名],[表名称 [别名],…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2,…]]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]])

[GROUP BY 分组字段1 [,分组字段2,…]]

[HAVING 分组后的过滤条件(可以使用统计函数)]

[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],…]]

WHERE:子查询一般只返回单行单列，多行单列，单行多列的数据

FROM：子查询返回的一般是多行多列的数据，当作一张临时表出现。

要求查出工资比SMITH还要高的全部信息

思路：

1、首先要知道SMITH的工资是多少

select sal from emp where ename=’SMITH’;
2、由于此时返回的是单行单列的数据，所以这个子查询可以在WHERE中出现

select * from emp where sal>(
select sal
from emp
where ename=’SMITH’);
要求查询出高于公司平均工资的全部雇员信息

思路：

1、公司的平均工资是多少？

select avg(sal) from emp;
2、由于此时返回的是单行单列的数据，在WHERE之中出现

select * from emp where sal>(
select avg(sal)
from emp);
以上返回的是单行单列，但是在子查询中，也可以返回单行多列的数据，只是这种子查询很少出现，如：

1、IN操作符：用于指定一个子查询的判断范围

因为如果有NULL，则会查询所有数据，如果数据量太大就会导致有漏洞产生，所以加入限制。

select * from emp
where sal not in(
select sal
from emp
where job=’MANAGER’);
2、ANY操作符，与每一个内容相匹配，有三种匹配形式

1、=ANY:功能与IN操作符是完全一样；

select * from emp
where sal=any(
select sal
from emp
where job=’MANAGER’);
2、>ANY:比子查询中返回记录最小的还要大的数据

select * from emp
where sal>any(
select sal
from emp
where job=’MANAGER’);
3、

查询出每个部门的编号，名称，位置和部门人数，平均工资

之前使用的是多字段分组统计完成的，如：

select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname,d.loc;
这时候实际产生了笛卡儿积，共56条记录。

1、子查询中统计的记录是14条，最终统计的显示结果是3条结果

2、dept表之中一共有4条记录

3、如果现在产生笛卡儿积的话只有12条，在加上雇员的14条，一共才26条

###数据更新(重点)

增删改查

数据更新包括数据的增加、修改、删除。

为了做实验，我们将emp表复制一份，使用以下指令：

create table myemp as select * from emp;
这种语法是oracle支持的，其他的数据库不一样

数据的增加

语法：

INSERT INTO 表名称 [(字段1,字段2,字段3,…)] VALUES(值1,值2,值3,…)

注意：如果需要进行增加数据的话，则以下的几种数据类型要分别处理：

增加数字：直接编写数字，如：123；

增加字符串：字符串应该使用”’”声明；

增加DATE数据：

第一种：可以按照已有的字符串的格式编写字符串，如：‘20-6月-06’；

第二种：利用TO_DATE函数将字符串变为DATE型数据；

第三种：如果设置的时间为当前系统时间，则使用SYSDATE;

对于数据的增加有两种操作格式：

完整型：

insert into myemp (empno,ename,hiredate,sal,mgr,job,comm)
values (8888,’zhangsan’,TO_DATE(‘1960-08-17’,’yyyy-mm-dd’),8000,7369,’daza’,1000);

insert into myemp (empno,ename,hiredate,sal,mgr,job,deptno)
values (8889,’lisi’,SYSDATE,3000,7369,’daza’,30);
简便型：不写列名称

错误：

insert into myemp
values (8889,’lisi’,SYSDATE,3000,7369,’daza’,30);
正确：需要按照列名称的顺序来写，必须符合字段的要求，一般开发中不使用

insert into myemp values (8890,’wangwu’,’daza’,7369,sysdate,3000,null,30);
数据的修改

语法：

UPDATE 表名称 SET 更新字段1=更新值1,更新字段2=更新值2,…[WHERE 更新条件(s)];

示例；

更新员工编号为7369的员工的工资为5000，奖金为2000，职位升级为’MANAGER’,职位更新时间为当前时间

update myemp set sal=5000,comm=2000,job=’MANAGER’,hiredate=sysdate where empno=7369;
所有员工的工资统一改为7500

update myemp set sal=7500;
注意：如果更新的时候不加上更新条件，则意味着更新全部数据，一定要注意，当数据量达到一定程度时，这种方式不可取。

为了后面做实验，我们需要回滚数据表

SQL> ROLLBACK;
数据的删除

语法：

DELETE FROM 表名称 [WHERE 删除条件(s)];

示例:

删除1987年入职的员工的信息

delete from myemp where TO_CHAR(hiredate,’yyyy’)=1987;
注意：如果删除的时候没有相应匹配条件的时候，则更新记录为0，更新操作也一样。

删除表中的所有数据

delete from myemp;
注意：对于删除操作，尽可能少使用，因为删除操作对于查询操作要危险许多。

提示：对于删除操作，在开发时对于所有的删除操作之前先给出一个提示框，以防止误删除。

###事务处理

对于数据表的操作，查询要比更新操作更安全，因为更新操作有可能会出现错误，导致没有按照既定的要求正确的完成更新操作。

在很多时候更新可能由多条语句共同完成，如银行转账：

-判断A的账户上是否有5000W select yue+shouxufei>5000+sxf from zhanghu where id=a

-判断B的账户状态是否正常 select id,status from zhanghu where id=b

-从A的账户上移走5000W update zhanghu set yue-5000 where id=a

-向B的账户上增加5000W update zhanghu set yue+5000 where id=b

-向银行支付手续费5W update zhanghu set yue+shouxufei where id=yinhang

以上五个数据操作是一个整体，可以理解为一个完整的业务，如果其中第三点出错，其他操作该怎么办？

如果有操作出现错误，那么其他操作应该不再继续执行，并且都回归到最原始的状态，而这一个流程的操作实际上就是事务的操作。

回滚之前的操作

rollback;

再次查看myemp表，确认其中的数据情况

事务的回滚：ROLLBACK,更新操作回到原点

事务的提交：COMMIT,真正的发出更新请求，一旦提交后无法回滚

示例：

在会话1中删除一条数据，从另一个会话来查询数据

会话1 delete from myemp where empno=7369;

会话1 select * from myemp;

会话2 select * from myemp;

在会话1中回滚之前的更新操作，再次删除数据，然后提交，再在两个会话中查询数据,在会话1中进行回滚发现已经无法回滚了。

会话1 delete from myemp where empno=7369;

会话1 commit;

会话1 select * from myemp;

会话2 select * from myemp;

示例：

死锁,会话1执行更新后，在没有提交之前，会话2进行更新会出现等待

会话1 update myemp set sal=9000 where empno=7839;

会话2 update myemp set sal=8000 where empno=7839;

这种问题从大的方面来讲可以称作死锁，但是在oracle之中死锁有很多种类.

所有的数据更新一定都会受到事务的控制。

###数据伪列

ROWNUM(重点)

ROWNUM为每一个显示的记录都会自动的随着查询生成的行号。

示例：

查询emp表的rownum，empno,ename,job,hiredate和sal

select rownum,empno,ename,job,hiredate,sal from emp;
该行号不是永久的，会随着查询的显示变化而变化

查询emp表的rownum，empno,ename,job,hiredate和sal

select rownum,empno,ename,job,hiredate,sal from emp where deptno=30;
查询前五条记录

select rownum,empno,ename,job,hiredate,sal from emp where rownum<=5;
查询6-10行记录

错误做法：

select rownum,empno,ename,job,hiredate,sal from emp where rownum between 6 and 10;

执行完成后并没有返回任何数据，因为rownum不是真实列，正确的思路是先查询前10条记录，然后再查询后5条记录，需要使用子查询

select * from (
select rownum rn,empno,ename,job,hiredate,sal from emp
where rownum<=15) temp
where temp.rn>10;

ROWID

ROWID表示的是每一行数据保存的物理地址的编号。

查看ROWID

select rowid,deptno,dname,loc from dept;
查看完成后，保存该结果，下面的案例要和这个结果做比较

每一条记录的ROWID都不会重复，所以即使所有列的内容重复，ROWID也不会重复.

格式为：AAAL+XAAEAAAAANAAA

其中： 数据对象号：AAAL+X

相对文件号：AAE

数据块号： AAAAAN

数据行号： AAA

###总结：

3、子查询：结合限定查询、多表查询、分组统计查询完成各个复杂查询的操作，子查询一般在WHERE和FROM之后出现较多。

4、数据库的更新操作一定要受到事务的控制，事务的两个命令:COMMIT,ROLLBACK,每一个连接到数据库上的用户都用一个会话来表示。

5、数据表的分页查询显示依靠ROWNUM伪列，这个在以后的开发当中必定要使用。

>转自：http://www.uml.org.cn/sjjm/201905173.asp

Leave a Comment Cancel reply 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

SQL语句基础用法大全5（DML）

Leave a Comment Cancel reply
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款