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 comple