SQL Foundation（18--23）

最新推荐文章于 2020-08-11 22:56:01 发布

bird_cat

最新推荐文章于 2020-08-11 22:56:01 发布

阅读量1k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/topwqp/article/details/8973567

版权

oracle 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

1：子查询

单行子查询用=

多行子查询用 in （相当于子查询结果为一个集合）

在子查询中，如果子查询是查询出来为null，返回结果为 no rows select 未选定行

ex：

之所以第一个查询为为选定行，是因为子查询中得到的结果为null，

相当于 select last_name ,department_id from employees where department_id = null;

所以为空，下面sql的执行验证了这个道理。

SQL> select last_name,department_id from employees
  2  where  department_id = (select department_id from employees where last_name
='Haas');

未选定行

SQL> select department_id from employees where last_name='Haas';

未选定行

SQL> select last_name,department_id from employees where department_id = null;

未选定行

SQL> select last_name,department_id from employees where department_id is null;

LAST_NAME                 DEPARTMENT_ID
------------------------- -------------
Grant

2： any 和 all 用法

1：any

< any 小于集合中的最大值

> any 大于集合中的最小值

= any 相当于 in

2：all

< all 小于集合中的最小值

> all 大于集合中的最大值

ex：

SQL> select employee_id ,last_name ,job_id,salary from employees where  salary <
 any (select  salary from employees where job_id ='IT_PROG');

SQL> select job_id , salary from employees where job_id ='IT_PROG';

JOB_ID         SALARY
---------- ----------
IT_PROG          9000
IT_PROG          6000
IT_PROG          4800
IT_PROG          4800
IT_PROG          4200

所以第一个sql 相当于

SQL>   select employee_id ,last_name,job_id ,salary from employees where  salary
 <9000;

select emp.last_name from employees emp
where  emp.employee_id not in (select mgr.manager_id from employees mgr)
not in  相当于  != all 所有都不相等。

select emp.last_name from employees emp
where emp.employee_id  in (select mgr.manager_id from employees mgr)

in 相当于 = any

3：union 和 union all ， intersect ， minus

1： union 操作符返回两个结果中不同的行排序（默认按照第一列ASC排序）

2：union 操作符返回两个结果中所有的行（包括相同的行）结果不排序

3：intersect 操作符，返回结果中的相同的行。交集结果排序（默认按照第一列ASC排序）

4： minux 操作符返回结果为：仅仅在第一个查询中出现，在第二个查询中没有出现的数据。

ex：

列数相同，有一列的数据类型要相同

可以通过函数和，让结果列数保持一致。

SELECT location_id, department_name "Department", 
   TO_CHAR(NULL) "Warehouse"  FROM departments
   UNION
   SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name 
   FROM warehouses;

LOCATION_ID Department            Warehouse
----------- --------------------- --------------------------
       1400 IT
       1400                       Southlake, Texas
       1500 Shipping
       1500                       San Francisco
       1600                       New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction
...

除了union all之外，其他的都自动排序，
按照第一列的数据的升序进行排序。

note：

在复合查询中，order by 只能出现一次，
而且只能出现在最后。
oder by 只认识第一行的列，不认识第二行的列；

ex：

SQL> select location_id ,department_name "DEP" , to_char(null) location
  2  from departments  union
  3  select location_id, to_char(null) "DEP", state_province "Location"
  4  from location  order by  state_province ;
from location  order by  state_province
                         *
第 4 行出现错误:
ORA-00904: "STATE_PROVINCE": 标识符无效

以上这种写法是错误的，因为 order by 只认识
第一列的行，不认识第二列的行，如果换成 location 即可。

4：操纵数据

复制一张表：
create table copy_departments as select * from departments;

批量插入（注意不需要values了）
ex：

insert into sales_reps(id,name,salary,commission_pct)
select  employee_id,last_name,salary,commission_pct
from employees where  job_id like '%REP%';

truncate 和 delete 的区别：
truncate 是DDL语言 rollback 不可以回滚(这个应该复制一张表试试)

delete 是DML语言 rollback 可以回滚直接真正删除。

delete是没有真正删除在 undo 表空间中。
可以进行查询。

5：数据库的事务

 结束标志：  
   1：commit ， rollback 语句出现的时候，
   2 ：A DDL 或者 DCL语句执行的时候，也会自动的提交，因为DDL或者DCL开始了一个新事务，上一个事务就自动提交了。	 
   3:正常退出SQLPLUS的时候 事务会进行提交就是执行exit或者quit命令正常退出，如果sqlplus异常退出，如直接关掉或者杀死进程，为异常退出，则进行回滚。
   4：系统崩溃的时候进行回滚掉。

关于事务回滚以及保存点：

SQL> delete from copy_departments where  department_id=270;

已删除0行。

SQL>  savepoint a;

保存点已创建。

SQL>  insert into copy_departments values(280,'BBBBBB',NULL,NULL);

已创建 1 行。

SQL>
SQL>  savepoint b;

保存点已创建。

SQL>
SQL>  update copy_departments set department_name  ='BBCCDD'  where department_i
d =280;

已更新2行。

SQL>
SQL>  rollback to b;

回退已完成。

SQL> commit;

提交完成。

SQL> select * from copy_departments;

如果想隐式提交，直接提交需要 set auto on 即可，
不过通常情况下不会这样打开。

数据在提交或者回滚前的状态。

为了保持数据的一致性：
插入或者删除数据的时候，多个用户同时操作，
相当于多个session会话，如果数据没有提交，
当前用户操作的数据，其他用户不会看到，
如果当前用户提交了数据，其他用户才能看到修改的数据。

oracle中每条语句之前都有一个隐含的 savepoint
如果失败了就回滚掉。
多条语句执行如果有一条语句执行失败了，则只有该条语句，
会回滚，其他语句执行正常。

读不用等待写，
写也不用等待读，
如果两个都写，第一个写需要加锁，写完提交后，其他的才能写。

FOR UPDATE 会一直把查找到的数据锁起来。只有当
执行了 commit 或者 rollback的时候才打开锁。

关于ｏｒａｃｌｅ为什么回滚和提交，当修改数据的时候，先把需要修改的表备份为以下，然后直接在现有表上操作，如果回滚了，就把老表替换掉新表，如果ｃｏｍｍｉｔ，备份的表就可以删除了。相当于刚才修改的数据有效了。