oracle知识点小结3（子查询）

oracle知识点小结3（子查询）

 

一.子查询的使用

1.insert

用子查询在insert中进行插入数据时，要注意不要加上Vaules关键字。

SQL> create  table copy_dep  as  select *  from    departments;
Table created.
SQL> truncate table copy_dep;
Table truncated.
SQL> insert into copy_dep  
  2  select * from  departments;
27 rows created.

 

2.create

用子查询来创建表时，在原表中只有not null会被传递到新表，其它约束不会传递。 

 

在用子查询创建表和视图时，将表达式加上别名是必须的；

SQL> create table dept 
  2  as 
  3  select employee_id,last_name,salary*12,hire_date
  4  from employees where department_id=80;
select employee_id,last_name,salary*12,hire_date

 

                                   *

ERROR at line 3:

ORA-00998: must name this expression with a column alias

修改，加上别名

SQL> create table dept(emp_id,name,salary,hire_date)
  2  as  select employee_id,last_name,salary*12,hire_date
  3  from employees where department_id=80;
Table created.

 

 

或者

SQL> create table dept
  2  as
  3   select employee_id,last_name,salary*12 ANNSAL,hire_date
  4  from employees where department_id=80;
Table created.

 

 

 

3.update

用子查询update时，可以从其它表中获得数据。

SQL> select *  from  copy_dep;
DEPARTMENT_ID     DEPARTMENT_NAME       MANAGER_ID   LOCATION_ID
-----------------------      ------------------------------       -----------------     ------------------
           10               Administration                          200         1700
           20               Marketing                                201          1800
           30              Purchasing                               114          1700
           40          Human Resources                       203          2400
           50                    Shipping                              121          1500
           60                       IT                                    103         1400

SQL> update copy_dep  set  department_name=(select department_name  from  departments  where department_id=10),
  2  location_id=(select location_id from departments where department_id=10)
  3  where   department_id=50;
1 row updated.

SQL> select *  from  copy_dep;
DEPARTMENT_ID       DEPARTMENT_NAME    MANAGER_ID     LOCATION_ID
-------------                  ------------------------------             ----------           -----------
           10                      Administration                     200                 1700
           20                     Marketing                             201        1800
           30                     Purchasing                            114        1700
           40                   Human Resources                       203        2400
           50                  Administration                        121        1700
           60                      IT                                    103        1400

 

 

 

4.read-only表

表改为read-only可以阻止对表的增删改，但不被阻止删除表。

SQL> alter table dept read only;
Table altered.
SQL> delete  from  dept where employee_id=170;
delete  from  dept where employee_id=170
              *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."DEPT"
SQL> alter table dept  read write;
Table altered.
SQL> delete  from  dept where employee_id=170;
1 row deleted.
SQL> alter table dept read only;
Table altered.
SQL> drop table dept;
Table dropped.

 

 

 

5.View

简单视图：只要一张表，不使用函数；复杂视图：一张或者多张表，使用函数。

一般来说可以对简单视图执行DML，但并非总是如此，如果视图不包括具有NOT NULL的强制列，那么对视图进行insert操作就不会成功，复杂视图不能执行DML。

 

创建个视图

SQL> create view empvu 
  2  as  select employee_id,last_name,salary from employees where department_id=80;
View created.

SQL> desc empvu
 Name                                           Null?               Type
 -----------------------------------------   --------       ----------------------------
 EMPLOYEE_ID                          NOT NULL   NUMBER(6)
 LAST_NAME                             NOT NULL     VARCHAR2(25)
 SALARY                                                            NUMBER(8,2)

SQL> select *  from empvu;
EMPLOYEE_ID          LAST_NAME         SALARY
----------- -------          ------------------          ----------
        167                   Banda                    6200
        168                    Ozer                     11500
        169                    Bloom                   10000
        170                     Fox                      9600
        171                    Smith                     7400
        172                    Bates                     7300

 

 

把视图中employee_id=169的last_name=Bloom修改为last_name=’Blooms’。

SQL> update empvu set last_name='Blooms'
  2  where employee_id=169;
1 row updated.
SQL> commit;
Commit complete.

 

 

现在查看一下基表有没有发生修改，

SQL> select employee_id,last_name,salary from employees where employee_id=169;
EMPLOYEE_ID           LAST_NAME         SALARY
----------- ---------          ----------------           ----------
        169               Blooms               10000

 

 

基表也修改了。

对视图的修改成功，也是对基表的修改。

 

对于在创建视图时不加上with  check option。

SQL> create table emp as select * from employees;
Table created.

SQL> create or replace view empvu1 
  2  as select * from emp where department_id=20;
View created.

SQL> select  employee_id,last_name,department_id from  empvu1;
EMPLOYEE_ID         LAST_NAME                 DEPARTMENT_ID
----------- ------------       -------------                         -------------
        201            Hartstein                                 20
        202            Fay                                         20
SQL> update empvu1 set department_id=10  where employee_id=202;
1 row updated.

SQL> select employee_id,last_name,department_id from empvu1;
EMPLOYEE_ID         LAST_NAME                DEPARTMENT_ID
-----------               -------------------------           -------------
     201               Hartstein                             20

 

 

可以将where子句中 department_id=20修改为department_id=10，从而导致了这行的消失。

 

 

用with  check option来创建视图，可以防止导致行从视图中消失的DML操作。

SQL> create or replace view empvu1 
  2  as select * from emp where department_id=20 with check option;
View created.

SQL> select employee_id,last_name,department_id from empvu1;
EMPLOYEE_ID        LAST_NAME           DEPARTMENT_ID
     ----------- -       ----------------------           -- -------------
        201           Hartstein                       20

SQL> update empvu1 set department_id=10 where e