SQL查询与数据操作详解-优快云博客

本文链接：https://blog.youkuaiyun.com/DriverKing/article/details/6791320

一、子查询

子查询（内查询）在住查询之前一次执行完成，子查询的结果被主查询使用（外查询）。单行操作符对应单行子查询，多行操作符对应多行子查询

注意：（group by 后，不能使用子查询，其他地方都可以用，主查询和子查询可以不是一张表，只要子查询返回的结果主查询可以使用就可以，可以将子查询视为一张新表）

1、单行子查询：

-where 后跟子查询：

查询比 SCOTT 工资高的员工：select * from emp where sal > (select sal from emp where ename='SCOTT')

查询部门名称为 SALES 的员工信息：select * from emp where deptno=(select deptno from dept where dname='SALES')

- from 后跟子查询：

查询员工的名字和工资：select * from (select ename,sal from emp) 这是将子查询视为一张新表（有两列ename、sal），然后查询这张表中的所有记录

- select 后跟子查询：select (select dname from dept where deptno=30) 部门,ename 员工 from emp

2、多行子查询：

-子查询分为单行子查询和多行子查询。上面的例子都是单行子查询，下面举例多行子查询

-查询部门名称为 SALES 和 ACCOUNTING 的员工信息：select * from emp where deptno in (select deptno from dept where dname='ACCOUNTING' or dname='SALSE')

-多行子查询操作符：in（等于列表中的任何一个）、any（和子查询返回的任意一个值比较）、all（和子查询返回的所有值比较）

-子查询中也可以使用组函数。

-查询工资最低的员工：select * from emp where sal = (select min(sal) from emp)

-非法使用子查询：多行子查询使用单行比较符（会返回错误：单行子查询返回多行）

-子查询中的空值问题：和表达式一样，子查询只要包含一个空值记录i，那么整个子查询结果将为空

-查询不是经历的员工信息：select * from emp where empno not in(select mgr from emp where mgr is not null)

-any 的使用：查询工资比10号部门员工中任意一个低的员工信息：
select * from emp where sal < (select max(sal) from emp where deptno=10) 等同于：

select * from emp where sal < any (select sal from emp where deptno=10)

-all 的使用：查询工资比10号部门都要低的员工信息：

select * from emp where sal < (select min(sal) from emp where deptno=10) 等同于：

select * from emp where sal < all (select sal from emp where deptno=10)

3、练习一：找到员工表中工资最高的前三名，显示格式如下：

select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3

分析：在 Oracle 中提供了一个伪列 rownum（行号）在我们创建表的时候 Oracle 为我们自动加上的一列，但是 rownum 一旦生成，就不会改变，并且 rownum 只能使用小于等于号，不能使用大于等于号（如果使用大于等于就会出现未选定行的提示，因为在 Oracle 看来，当使用小于等于很明显前面的行已经生成，而当使用大于等于，Oracle 会因为后面的行有可能不存在）。所以我们在分析这个题的时候，应该考虑怎么生成另一个行号，所以就必须利用另外一张表，我们可以使用子查询先查出按降序排列的员工，再在此基础上筛选行号，因为此时的行号是对这张虚拟的表重新生成的，所以此时的 rownum<=3 是针对 (select * from emp order by sal desc) 这张查出来的集合而言。

练习二：找到员工表中薪水大于本部门平均薪水的员工，显示格式如下：

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno=e.deptno);

分析：题目要求查出薪水大于本部门平均薪水的员工，所以我们就必须要先查出当前员工所在部门的平均薪水 select avg(sal) from emp，但是这种只是查出每个部门的平均薪水，我们要将这个薪水于当前的员工关联起来，select avg(sal) from emp where deptno=e.deptno，这就是相关子查询，相关子查询与普通的子查询是相反的，相关子查询是将主查询查询到的信息作为子查询的条件。然后我们再利用子查询查询出的数据作为主查询的条件。

练习三：统计每年入职员工的个数，显示格式如下：

实现方式一（decode函数）：

                         select count(*) Total, sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
                                                          sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
                                                          sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
                                                          sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
                                                          from emp;

实现方式二（case语句）：

                         select count(*) Total,sum(case to_char(hiredate,'yyyy')
                                                                  when '1980' then 1
                                                                else 0
                                                              end) "1980",
                                                      sum(case to_char(hiredate,'yyyy')
                                                                  when '1981' then 1
                                                                  else 0
                                                              end) "1981",
                                                     sum(case to_char(hiredate,'yyyy')
                                                                 when '1982' then 1
                                                                 else 0
                                                              end) "1982",
                                                     sum(case to_char(hiredate,'yyyy')
                                                                when '1987' then 1
                                                               else 0
                                                              end) "1987"
                          from emp

分析：两种方式实现的效果是一样的，原理也类似，相对decode函数比较简洁。原理就是，我们需要5列数据，第一列数据很容易得到，剩下的四列也需要我们手动生成，我们需要先找出入职时间是xxxx年的员工，就需要一个换换函数 to_char(hiredate,'yyyy') 这样我们就得到了4位数字的年份，接下来要做的就是对得到的年份进行条件判断，Oracle 提供了两种条件判断，一种是 decode 函数，一种是 sql99 的 case 语法，当入职时间等于我们需要找出的这几种时间的某一个时，就返回 1，否则就返回 0。Oracle 在查找数据的时候也是采用游标移动行记录的原理，找到一个就得到一个返回值，那么我们就需要使用组函数对行记录进行统计 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0))。这样我们就得到了一列数据，剩下的三列以此类推就很容易得到结果。

练习四：相关子查询练习，要求如下（在查询结果中，并不是money1比money少1000块钱，而是上条记录的值是作为当前行的值显示）：

select id,name,money,(select money from test1 where id=t.id-1) money1 from test1 t

分析：相关子查询就是子查询的条件是由主查询提供的，只要知道这点就很容易得解。

练习五：行列转换练习，要求如下：

select ci_id,wm_concat(stu_name) stu_name from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)<>0 group by ci_id

分析：我们要查出每个选修课所报名的学生，就必须要对两张表进行操作，假如我们使用一般的查询，会得到一个课程号对应一个学生，一个课程对应的学生信息是以行记录的形式表现出来的，所以我们要将所选相同课程的学生放在同一行，Oracle提供了 wm_concat 函数帮助我们进行行列转换，但是对于这道题，课程号对应的学生编号是以字符串的形式存在的，所以我们还要利用instr(c.stu_ids,s.stu_id)字符串函数对数据进行筛选，如果有该学生号则返回一个大于0的数，没有则返回0，所以我们只需判断结果是否等于0，就可以知道这门课是否被当前查询的学生选中，之后的问题就迎刃而解了。

二、集合运算

1、集合运算符：并集、交集、差集

union 与 union all

union 取出两个集合的数据，但是两个集合交集的数据只取一次

union all 是取出两个集合的数据，但是两个集合的交集取出两次

intersect

取出集合中交集的部分

minus

取出只属于 A 但不属于 B 的数据，也就是前一个集合中去除两个集合的交集

2、并集练习：取出即属于10号部门又属于20号部门的员工：

方式一：select * from emp where deptno=10 or deptno=20

方式二：select * from emp where deptno in (10,20)

方式三（集合运算）：select * from emp where deptno=10
union

select * from emp where deptno=20

改造 Oracle一中的练习（求出每个职位每个部门和整个公司的总工资）：

select deptno,job,sum(sal) from emp group by deptno,job

union

select deptno,to_char(null),sum(sal) from emp group by deptno

union

select to_number(null),to_char(null),sum(sal) from emp

break on deptno skip 2

集合运算注意事项：

（1）参与运算的各个集合必须列数相同且类型一致，如果不够则根据列的类型补空

（2）结果集采用第一个集合作为表头

（3）排序需要在每个集合后面加 order by

（4）集合运算的效率比较低，因为是要对多个表进行操作，所以集合运算只是用于小表，对于大表应使用 rollup 函数

3、交集练习：查询员工信息，工资位于1000-2000和1500-2500的员工，也就是同时满足这两个条件的员工

select * from emp where sal between 1000 and 2000

intersect

select * from emp where sal between 1500 and 2500

4、差集练习：查询员工的信息，工资位于1000-2000，但不属于1500-2500的员工

select * from emp where sal between 1000 and 2000

minus

select * from emp where sal between 1500 and 2500

三、处理数据

1、sql语句的类型：

DML（Data Manipulation Language）数据操作语言：select、insert、update、delete

DDL（Data Defination Language）数据定义语言：create table、alter table、create view

DCL（Data Control Language）数据控制语言：commit、rollback

2、插入数据

插入空值分为：隐式插入和显示插入，隐式插入空值就是不指定列的值，Oracle 会自动增加一个空值，显示插入空值就是在列的 values时将 null 插入数据库

地址符的应用：采用 &xx 声明一个参数。insert into emp(empno,ename,sal) values (&empnp,'&ename',&sal) 回车后会提示为参数赋值，注意如果是字符串、日期类型要有单引号引起来。

地址符不但可以应用于 insert 中，还可以应用在所有的 DML 语言中

拷贝多条数据到新表：create table emp10 as select * from emp where 1=2。此时创建的表只是将 emp 表中的表结构拷贝到了 emp10 中，数据并没有拷贝，因为子查询中的查询数据为空。

一次插入多条记录：insert into emp10 select * from emp where deptno=10。这是使用子查询插入多条数据。注意：（1）没有 values 关键字（2）子查询结果的列顺必须和插入语句中的列顺序一致。

3、更新数据

delete 和 truncate 区别：

（1）delete 是一条条删除数据，truncate 是直接摧毁表，再重建

（2）delete 不会释放空间，truncate 会释放空间，也就是当我们使用 delete 删除数据的时候，只是将数据清除，并没有释放数据库所在的硬盘空间。

（3）delete 可能会产生碎片，truncate 不会。因为在 Oracle 中，会为每个行生成一个伪列，rowid（行号），这个行号保存的是该条记录指向真实硬盘空间的指针，在初始的情况下，行地址肯定是连续的，我们一旦将某个数据删除了，那么行地址就会不连续，也就是被删除数据的空间将不再被引用。这就产生了碎片。但是 Oracle 对这点又做了优化，当使用 delete 删除了一条记录时，Oracle 将下条记录向上移动保证了空间的引用。但是这样又产生了一个问题，当我们将删除的数据闪回后，该数据就没有位置存放了，不过 Oracle 又给出了方案，就是：行移动，当撤销后，将行移动开关开启，将后面的数据向后移动。

四、事务

Oracle 以 DML 语句作为起始标志（自动开启事务）。结束标志分为：提交和回滚，提交又分为显示提交和隐式提交，显示提交：commit，隐式提交：DDL DCL 语句。回滚又分为显示回滚和隐式回滚，显示回滚：rollback，隐式回滚：系统异常、掉电、非正常退出等。

存储点（回滚点）：savepoint a 定义一个回滚点，rollback to savepoint a 回滚到回滚点

五、Oracle 中的回收站

在将 Oracle 中的对象删除以后，会默认删除到回收站中，可以使用 show recyclebin 查看回收站，清空回收站：purge recyclebin，将对象彻底删除：drop table xxx purge（就无法执行闪回）

六、其它数据库对象

视图、序列、索引、同义词

视图：从表中抽出的逻辑上相关的数据集合（不建议使用视图做更新操作）

序列：提供有规律的数值

索引：提高查询效率

同义词：为对象起名（常用分布式数据库）

索引：一种福利与表的模式对象，可以存储在与表不同的磁盘或表空间中，索引被删除或损坏不会对表产生印象，其影响的只是查询的速度，索引一旦建立，Oracle 管理系统会对其进行自动维护，而且由 Oracle 管理系统决定何时使用索引，在删除一个表时所有基于表的索引会自动被删除。