SQL之再谈DML和DDL语句的用法（ddl dml dcl dql语句）

【十七】其他DML和DDL语句的用法

17.1 DML语句-MERGE

根据一个表的数据组织另一个表的数据，一般是对merge的目标表插入新数据或替换掉老数据。

Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的；

2、UPDATE和INSERT子句可以加WHERE子句；

3、ON条件使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表；

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行。

示例：首先创建表：

SQL>create table PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(30),
CATEGORY VARCHAR2(30)
);
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;

SQL>create table NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(30),
CATEGORY VARCHAR2(30)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;

SQL>select * from products;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1501 VIVITAR 35MM ELECTRNCS

1502 OLYMPUS IS50 ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER DVD

SQL> select * from newproducts;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1502 OLYMPUS CAMERA ELECTRNCS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

下面我们从表NEWPRODUCTS中合并行到表PRODUCTS中，但删除category为ELECTRNCS的行。

SQL>MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,p.category = np.category
DELETE WHERE (p.category = 'ELECTRNCS')
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category);
SQL>select * from products;

PRODUCT_ID PRODUCT_NAME CATEGORY

---------- ------------------------------ ------------------------------

1501 VIVITAR 35MM ELECTRNCS

1600 PLAY GYM TOYS

1601 LAMAZE TOYS

1666 HARRY POTTER TOYS

1700 WAIT INTERFACE BOOKS

【why？？？】为什么1502不在了，但1501还在，因为1502是matched，先被update，然后被delete，而1501是not matched。

注意几点：

1）例子里有update，delete和insert。它们是否操作是取决于on子句的，两个表如果符合on条件就是匹配，不符合就是不匹配；

2）匹配了就更新，不匹配则插入。10g后加入了delete语句，这个语句必须在匹配条件下出现，它是一种补充；

3）你必须对操作的表有对象权限；

4）ON子句里的字段不能被update子句更新。

17.2 WITH语句

可以使用一个关键字WITH， 为一个子查询块（subquery block）起一个别名，然后在后面的查询中引用该子查询块的别名。

好处：

1）使用with语句，可以避免在select语句中重复书写相同的语句块；

2）with语句将该子句中的语句块执行一次并存储到用户的临时表空间中；

3）使用with语句可以避免重复解析，提高查询效率。

示例：这个with语句完成三个动作

建立一个dept_costs，保存每个部门的工资总和；

建立一个avg_cost，根据dept_costs求出所有部门总工资的平均值；

最后显示出部门总工资值小于部门总工资平均值的那些部门的信息(dname)。

WITH
dept_costs AS (
SELECT d.dname, SUM(e.sal) AS dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname ),
avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total <
(SELECT dept_avg FROM avg_cost)
ORDER BY dname

DNAME DEPT_TOTAL

-------------- ----------

ACCOUNTING 8750

SALES 9400

可以分三个部分来看：

第一AS建立dept_costs，保存每个部门的工资总和。

第二个AS建立avg_cost，根据第一个AS dept_costs求出所有部门总工资的平均值（两个with子程序用逗号分开，第二个使用了第一个别名）。

最后是查询主体，SELECT * FROM... 调用了前两个with下的别名（子查询），显示部门总工资值小于部门总工资平均值的那些部门的信息。

1）with语句中只能有select子句，没有DML子句（注意和merge的区别）。

2）一般将主查询放在最后描述，因为查询主体中要引用的with别名需要在之前定义过。

如果不用WITH子句，套用with子句写法：

SELECT * FROM
(SELECT d.dname, SUM(e.sal) AS dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname)
WHERE dept_total <
(SELECT dept_avg FROM (SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM (SELECT d.dname, SUM(e.sal) AS dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname)))
ORDER BY dname

顺藤摸瓜写法（内连视图）

select b.dname,a.sumsal from (select deptno,sum(sal) sumsal from emp group by deptno having sum(sal)<(select avg(sum(sal)) from emp group by deptno)) a,dept b where a.deptno=b.deptno;

17.3 表的DDL操作

1）在数据库打开的情况下，可以使用DDL语句修改数据字典信息，主要DDL语句对列的操作有：

增加（add) 一列；

修改（midify)列的宽度，及相似类型；

删除（drop [colunm])一列；

更名（rename column 旧列名 to 新列名。

2）当想要add一列，并约束该列为not null时，如果该表已经有数据了，加的列本身是null,则与not null约束矛盾，报错。

SQL> select * from a;

ID NAME

---------- ----------

1 a

2 b

SQL> alter table a add C number(5) not null;

报错：ORA-01758: 要添加必需的 (NOT NULL) 列，则表必须为空。

SQL> alter table a add C number(5) default 0 not null;

修改成功了，可以看到C列全是0，这样才能使C列的约束为not null。

3）要删除某一个表格上的某个字段，但是由于这个表格拥有非常大量的资料，如果你在尖峰时间直接执行 ALTER TABLE ABC DROP （COLUMN），可能会收到 ORA-01562 - failed to extend rollback segment number string

Oracle推荐：使用 SET UNUSED 选项标记一列（或多列），使该列不可用。

然后，当业务量下降后再使用DROP UNUSED column 选项删除被被标记为不可用的列。

SET UNUSED COLUMNS用于drop多列时效率更高，该方法系统开销比较小，速度较快，但效果等同于直接drop column，就是说这两种方法都不可逆，无法再还原该字段及其内容了。

语法：

ALTER TABLE table SET UNUSED [column] (COLlist多个)
ALTER TABLE table DROP UNUSED [COLUMN];

当对单列操作时要选column关键字，查看unused后的视图：

select * from user_unused_col_tabs;

1）如果set unused某列，该列上有索引，约束，并定义了视图，引用过序列，结果如何？索引和约束自动删除，序列无关，视图保留定义。

2）无法删除属于 SYS 的表中的列，会报ORA-12988错误，哪怕你是sys用户都不可以。

实验：scott下

SQL>create table a (id int, name char(10));
SQL>create index id_idx on a(id);
SQL>alter table a add constraint unq_id unique(id);
SQL>create sequence a_id start with 1 increment by 1;
SQL>create view v as select id from a;
SQL>insert into a values(a_id.nextval,'tim');
SQL>insert into a values(a_id.nextval,'ran');
SQL>commit;
SQL> select * from a;
SQL> select index_name from user_indexes where table_name='A';

查看有关索引

SQL> select constraint_name from user_constraints where table_name='A';

查看有关约束

SQL> select object_name from user_objects where object_type='SEQUENCE'; 

查看有关序列

SQL> select text from user_views where view_name='V';

查看有关视图

SQL> alter table a drop column id;

重复查看有关信息，索引和约束随该列数据虽然被删除，但序列和视图的定义还在。

17.4 模式及名称空间

模式(Schema)是一种逻辑结构，它对应于用户，每建一个用户就有一套模式与之对应。

我们通常说对象的唯一标识符是前缀为模式名加上对象名称，如scott.emp。

同一模式下的同类对象是不可以重名的。比如在scott模式里，表emp是唯一的，但在不同模式下可以重名。

名称空间定义了一组对象类型，同一个名称空间里的不同对象不能同名，而不同的名称空间中的不同对象可以共享相同的名称。

1）表，视图，序列，同义词是不同类型的对象，但它们属于同一名称空间, 因此在同一模式下也是不可以重名的，比如scott下不可以让一个表名和一个视图名同名；

2）索引、约束有自己的名称空间，所以在scott模式下，可以有表A，索引A和约束A共存；

3）表名最长可以30个字符，表名中若包含特殊字符需要使用“”将表名全部包括在内，表的最大列数为1000。

the end ！！！

@jackman 共筑美好！