Database Memo

Table of Contents

1 SQL

SQL stands for Structured Query Language.

1.1 SQL标准及不同实现

Table 1: SQL standards
Year Name Alias Comments
1986 SQL-86 SQL-87 First formalized by ANSI.
1989 SQL-89 FIPS127-1 Minor revision, in which the major addition were integrity constraints. Adopted as FIPS 127-1.
1992 SQL-92 SQL2, FIPS 127-2 Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.
1999 SQL:1999 SQL3 Added regular expression matching, recursive queries (e.g. transitive closure),triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features (e.g. structured types). Support for embedding SQL in Java (SQL/OLB) and vice-versa (SQL/JRT).
2003 SQL:2003 SQL 2003 Introduced XML-related features (SQL/XML), window functions, standardized sequences, and columns with auto-generated values (including identity-columns).
2006 SQL:2006 SQL 2006 ISO/IEC 9075-14:2006 defines ways in which SQL can be used in conjunction with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the database and publishing both XML and conventional SQL-data in XML form. In addition, it enables applications to integrate into their SQL code the use of XQuery, the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents.
2008 SQL:2008 SQL 2008 Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement.
2011 SQL:2011    

注:1992发布的SQL2可以认为是SQL的第一个统一标准。BNF Grammar for SQL-92在这里可以找到:http://savage.net.au/SQL/sql-92.bnf
参考:http://en.wikipedia.org/wiki/SQL#Standardization

三个主要的SQL商业实现:
Oracle对SQL的实现——PL/SQL(stands for "Procedural Language extenstions to SQL")
IBM DB2对SQL的实现——SQL PL
Microsoft SQL Server和Sybase对SQL的实现——Transact-SQL(简称T-SQL)

1.2 基本格式

SQL语言大小写不敏感。

1.2.1 注释

单行注释:以两个连字符(–)开始到行尾。
块注释为:/* */之间的内容。

在Oracle中,可以在注释中写Hints,Hint一般以/*+开始,以*/结束。
Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer.
hint举例,如:

SELECT /*+ NO_USE_HASH_AGGREGATION */ a,COUNT(*) AS num FROM tab1 GROUP BY a;

说明:
Hint是Oracle提供的一种SQL语法,它允许用户在SQL语句中插入相关的语法(作为注释的一部分),从而影响SQL的执行方式。因为Hint的特殊作用,所以对于开发人员不应该在代码中使用它,Hint更像是Oracle提供给DBA用来分析问题的工具。在SQL代码中使用Hint,可能导致非常严重的后果,因为数据库的数据是变化的,在某一时刻使用这个执行计划是最优的,在另一个时刻,却可能很差。

参考:https://docs.oracle.com/database/121/TGSQL/tgsql_influence.htm

1.3 创建表

CREATE TABLE foo (a integer, b varchar(10), c varchar(10))

1.3.1 基于存在表创建新表

Oracle中:

CREATE TABLE new_table AS (SELECT * FROM old_table);

上面命令不仅会创建新表,还会复制表old_table中的所有数据到new_table中。
如果只想复制表的结构,而不复制表中的内容,可以使用下面语句:

CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 0=1);

DB2 LUW中:

CREATE TABLE new_table LIKE old_table

上面语句不会复制old_table里的记录到new_table中。
如果要复制所有记录,可以使用下面语句:

INSERT INTO new_table SELECT * FROM old_table

1.4 查询表的定义

Oracle中:

DESCRIBE table_name
DESC table_name       --Oracle中,DESCRIBE可简写为DESC

DB2 for LUW中:

DESCRIBE TABLE table_name  --DB2 LUW中,必须有TABLE关键字,且DESCRIBE不能简写为DESC

DB2 for z/OS中:
在DB2 for z/OS中,不支持describe table命令查看表的结构。
可通过查询系统内置表sysibm.syscolumns得到表的定义信息。

如,查询TAB1的定义:

db2 => select name, coltype, length from sysibm.syscolumns where tbname='TAB1'

1.5 清空和删除表

1.5.1 清空表

清空表时,仅表中记录没了,表不会被删除。
Oracle中:

TRUNCATE TABLE table_name

DB2 LUW中:

TRUNCATE TABLE table_name IMMEDIATE    --DB2 LUW中,必须有关键字IMMEDIATE

1.5.2 删除表

DROP TABLE table_name

1.6 增删改记录

1.6.1 插入记录(insert)

INSERT INTO table_name (column1, column2) VALUES (value1, value2)
INSERT INTO table_name VALUES (value1, value2)        --不引起岐义时列名也可以省略
INSERT INTO table_name VALUES (value11, value12), (value21, value22) --插入多条记录,DB2支持,但Oracle不支持!

1.6.2 删除记录(delete)

DELETE FROM table_name WHERE column1=value1;
DELETE FROM foo WHERE a=7;

1.6.3 修改记录(update)

UPDATE table_name SET column1=new_val1, column2=new_val2 WHERE some_column=some_value
UPDATE foo SET a=999 WHERE b ='max';

1.6.4 merge

merge操作不是标准是SQL语句,Oracle和DB2都支持它(但语法有差异)。
用merge操作容易实现:当匹配上则更新表的数据,没有匹配上则插入记录到表中。

Oracle中merge实例:
The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740

参考:http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

1.7 查询记录

查询记录用SELECT,它是SQL中最复杂的语句之一。

1.7.1 去掉重复记录(SELECT DISTINCT colunm1, column2 FROM tab1)

The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

假设表CUSTOMERS如下所示:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

其中有两条记录的SALARY字段相同(为2000.00),用SELECT DISTINCT可以去重。如:

SQL> SELECT SALARY FROM CUSTOMERS
     ORDER BY SALARY;
+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+
SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
     ORDER BY SALARY;
+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

参考:http://www.tutorialspoint.com/sql/sql-distinct-keyword.htm

1.7.2 ORDER BY语句

ORDER BY语句能对结果集进行排序。
ORDER BY语句默认按照升序(ASC)对记录进行排序。如果希望按照降序对记录进行排序,可以在排序表达式后指定DESC关键字。

1.7.3 GROUP BY语句(分组查询)

GROUP BY是分组查询,一般和聚合函数(aggregate function)一起使用。

1.7.3.1 GROUP BY单列

实例:假设foo表如下;

SQL> SELECT * FROM foo;

NAME	   GRADE
---------- ----------
allen	   A
smith	   B
jones	   A
scott	   A
blake	   C

如何得到等级分别为A,B,C的人数?用GROUP BY语句:

SQL> SELECT grade, COUNT(name) AS nums FROM foo GROUP BY grade;

GRADE		 NUMS
---------- ----------
B		    1
A		    3
C		    1

上面语句,如果省略GROUP BY grade;会提示语法错误。
注:GROUP BY语句有个要求:SELECT后面的所有列中,那些没有使用聚合函数的列,都必须出现在GROUP BY语句后面。

1.7.3.2 GROUP BY多列

指定GROUP BY c1,c2,c3时,c1,c2,c3三列中只要有一个不同就会再分一个组。

1.7.4 HAVING语句

HAVING语句常常和GROUP BY语句同时使用。
在SQL中增加HAVING子句原因是,WHERE关键字无法与aggregate function一起使用。

1.7.4.1 HAVING实例

继续上面GROUP BY的例子,如何找到人数大于2的等级呢?增加HAVING语句:

SQL> SELECT grade, COUNT(name) AS nums
  2  FROM foo
  3  GROUP BY grade
  4  HAVING COUNT(name) > 2;

GRADE		 NUMS
---------- ----------
A		    3

注:HAVING语句和WHERE关键字的作用类似,但WHERE无法在上面场景中使用,后面用了聚合函数COUNT。

参考:http://www.w3school.com.cn/sql/sql_having.asp

1.8 JOIN(“横向”合并)

JOIN用来按指定条件(CROSS JOIN不用指定条件)“横向”合并两个或多个表,基本语法为:

joined-table:
                      .-INNER-----.                                                
>>-+-table-reference--+-----------+--JOIN--table-reference--ON--join-condition-+-><
   |                  '-| outer |-'                                            |   
   +-table-reference--CROSS JOIN--table-reference------------------------------+   
   '-(--joined-table--)--------------------------------------------------------'   

outer:
              .-OUTER-.   
|--+-LEFT--+--+-------+-----------------------------------------|
   +-RIGHT-+              
   '-FULL--'              

摘自:http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059207.html?cp=SSEPGG_10.5.0/2-12-6-1-1-1&lang=en

1.8.1 内联接

下面将以实例形式展示内联接基本含义,先准备两个数据表persons和orders:

create table persons (id_p integer, lastname char(10), firstname char(10), city char(10))
insert into persons values (1, 'Adams', 'John', 'London')
insert into persons values (2, 'Bush', 'George', 'New York')
insert into persons values (3, 'Carter', 'Thomas', 'Beijing')

create table orders (id_o integer, orderno integer, id_p integer)
insert into orders values (1, 77895, 3)
insert into orders values (2, 44678, 3)
insert into orders values (3, 22456, 1)
insert into orders values (4, 24562, 1)
insert into orders values (5, 34764, 65)

执行完上面语句后,测试数据表已准备好。

db2 => select * from persons

ID_P        LASTNAME   FIRSTNAME  CITY      
----------- ---------- ---------- ----------
          1 Adams      John       London    
          2 Bush       George     New York  
          3 Carter     Thomas     Beijing   

  3 record(s) selected.

db2 => select * from orders

ID_O        ORDERNO     ID_P       
----------- ----------- -----------
          1       77895           3
          2       44678           3
          3       22456           1
          4       24562           1
          5       34764          65

  5 record(s) selected.

如何查询谁定购了产品,定购了什么产品?用内联接(INNER JOIN … ON …)可解决问题:

db2 => SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo \
db2 (cont.) => FROM (Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P)

LASTNAME   FIRSTNAME  ORDERNO    
---------- ---------- -----------
Carter     Thomas           77895
Carter     Thomas           44678
Adams      John             22456
Adams      John             24562

  4 record(s) selected.

说明1:上面语句中的INNER关键字和小括号可以省略。
说明2:不用内联接,用where同样可以解决问题。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

参考:http://www.w3school.com.cn/sql/sql_join.asp

1.8.2 内联接和各种外联接的区别

内联接和外联接的区别如表 2 所示。

Table 2: 内联接和各种外联接的区别
type of join description
tab1 [INNER] JOIN tab2 ON … 按ON指定的条件找左右表中的关联数据
tab1 LEFT [OUTER] JOIN tab2 ON … 左表(tab1)一定会在结果表中
tab1 RIGHT [OUTER] JOIN tab2 ON … 右表(tab2)一定会在结果表中
tab1 FULL [OUTER] JOIN tab2 ON … 左右表都在结果表中

各种内联接和外联接的测试如下:

db2 => SELECT * FROM Persons JOIN Orders ON Persons.Id_P = Orders.Id_P

ID_P        LASTNAME   FIRSTNAME  CITY       ID_O        ORDERNO     ID_P       
----------- ---------- ---------- ---------- ----------- ----------- -----------
          3 Carter     Thomas     Beijing              1       77895           3
          3 Carter     Thomas     Beijing              2       44678           3
          1 Adams      John       London               3       22456           1
          1 Adams      John       London               4       24562           1

  4 record(s) selected.

db2 => SELECT * FROM Persons LEFT JOIN Orders ON Persons.Id_P = Orders.Id_P

ID_P        LASTNAME   FIRSTNAME  CITY       ID_O        ORDERNO     ID_P       
----------- ---------- ---------- ---------- ----------- ----------- -----------
          1 Adams      John       London               3       22456           1
          1 Adams      John       London               4       24562           1
          2 Bush       George     New York             -           -           -
          3 Carter     Thomas     Beijing              1       77895           3
          3 Carter     Thomas     Beijing              2       44678           3

  5 record(s) selected.

db2 => SELECT * FROM Persons RIGHT JOIN Orders ON Persons.Id_P = Orders.Id_P

ID_P        LASTNAME   FIRSTNAME  CITY       ID_O        ORDERNO     ID_P       
----------- ---------- ---------- ---------- ----------- ----------- -----------
          3 Carter     Thomas     Beijing              1       77895           3
          3 Carter     Thomas     Beijing              2       44678           3
          1 Adams      John       London               3       22456           1
          1 Adams      John       London               4       24562           1
          - -          -          -                    5       34764          65

  5 record(s) selected.

db2 => SELECT * FROM Persons FULL JOIN Orders ON Persons.Id_P = Orders.Id_P

ID_P        LASTNAME   FIRSTNAME  CITY       ID_O        ORDERNO     ID_P       
----------- ---------- ---------- ---------- ----------- ----------- -----------
          3 Carter     Thomas     Beijing              1       77895           3
          3 Carter     Thomas     Beijing              2       44678           3
          1 Adams      John       London               3       22456           1
          1 Adams      John       London               4       24562           1
          - -          -          -                    5       34764          65
          2 Bush       George     New York             -           -           -

  6 record(s) selected.

注:在外联接中,往往会有NULL值出现,上面例子中的连字符就是NULL值。

1.8.3 交叉联接(笛卡尔积,CROSS JOIN)

交叉联接的结果集是两个表中所有行的每种可能组合,如一个表有10条记录,另一个表有20条记录,则交叉联接后的结果表会有200条记录。交叉联接不需也不能指定ON条件。
交叉联接的开销很大,常用来生成测试数据,实际应用很少。

db2 => SELECT * FROM Persons CROSS JOIN Orders

ID_P        LASTNAME   FIRSTNAME  CITY       ID_O        ORDERNO     ID_P       
----------- ---------- ---------- ---------- ----------- ----------- -----------
          1 Adams      John       London               1       77895           3
          1 Adams      John       London               2       44678           3
          1 Adams      John       London               3       22456           1
          1 Adams      John       London               4       24562           1
          1 Adams      John       London               5       34764          65
          2 Bush       George     New York             1       77895           3
          2 Bush       George     New York             2       44678           3
          2 Bush       George     New York             3       22456           1
          2 Bush       George     New York             4       24562           1
          2 Bush       George     New York             5       34764          65
          3 Carter     Thomas     Beijing              1       77895           3
          3 Carter     Thomas     Beijing              2       44678           3
          3 Carter     Thomas     Beijing              3       22456           1
          3 Carter     Thomas     Beijing              4       24562           1
          3 Carter     Thomas     Beijing              5       34764          65

  15 record(s) selected.

1.9 UNION(和集),MINUS(EXCEPT,差集),INTERSECT(并集)

进行这些操作时,前后两个表必须具有相同的列数,且对应每列的类型必须兼容。

默认地,进行UNION操作时,结果集中的重复记录只会显示一条,如果想保留重复记录可以使用 UNION ALL ,如:

SQL> SELECT 123 FROM DUAL UNION SELECT 123 FROM DUAL;

       123
----------
       123

SQL> SELECT 123 FROM DUAL UNION ALL SELECT 123 FROM DUAL;

       123
----------
       123
       123

说明:对于差集运算,Oracle只能使用MINIS关键字,DB2可以使用EXCEPT或MINUS关键字。

1.10 Blob, Clob

Blob stands for Binary Large Object.
Clob stands for Character Large Object.
这两个类型都是用来存储大量数据的,其中BLOB是用来存储大量二进制数据;CLOB用来存储大量文本数据。

Oracle和DB2中都支持这两种类型。

2 DB2 VS Oracle

2.1 Differents between DB2 and Oracle

Oracle官方文档(迁移DB2到Oracle):
http://docs.oracle.com/cd/E35137_01/nav/portal_booklist.htm
http://docs.oracle.com/cd/E35137_01/doc.32/e18460/oracle_db2_compared.htm

IBM官方文档(迁移Oracle到DB2):
Oracle to DB2 Conversion Guide for Linux, UNIX, and Windows
http://www.redbooks.ibm.com/abstracts/sg247048.html

DB2 and Oracle – An Architectural Comparison
ftp://ftp.software.ibm.com/software/data/db2/9/labchats/20110331-slides.pdf

第三方公司(sqlines)的文档:
IBM DB2 to Oracle Migration
http://www.sqlines.com/db2-to-oracle ,这里是它的一个 备份

2.2 Oracle和DB2中instance概念的区别

Oracle terms "instance" & "database" often used interchangeably by DBAs and users, however:

  • "instance" is logical (or temporal) and related to memory and processes.
  • "database" is persistent and related to files.

Oracle database中(非RAC环境),一个实例对应一个数据库(由CREATE DATABASE创建),实例和数据库的区别有点像操作系统中process和program的区别。

DB2中,instance是指database manager,一个实例可以管理很多数据库(由CREATE DATABASE创建)。

An instance is a logical database manager environment where you catalog databases and set configuration parameters.

db2_instance.png

Figure 1: DB2实例可管理多个数据库

参考:
http://db2commerce.com/2013/02/04/db2-basics-what-is-an-instance/
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0004900.html
ftp://ftp.software.ibm.com/software/data/db2/9/labchats/20110331-slides.pdf

2.3 dual表和sysibm.sysdummy1表

dual表是Oracle中的内置表,Oracle保证dual表中只有一条记录。
sysibm.sysdummy1表是DB2中的内置表,DB2保证sysibm.sysdummy1表中只有一条记录。
利用它们只有一条记录的特点,可以用它们来做计算,或查询系统的配置。

SQL> select (4+3) from dual;

     (4+3)
----------
	 7

SQL> select user from dual;

USER
------------------------------
SCOTT
db2 => select (4+3) from sysibm.sysdummy1

1          
-----------
          7

  1 record(s) selected.

db2 => select user from sysibm.sysdummy1

1          
-----------
DB2INST1   

  1 record(s) selected.

2.4 common-table-expression(db2)和subquery_factoring_clause(Oracle)

2.4.1 为什么需要它?

Creating a common table expression saves you the overhead of creating and dropping a regular view that you need to use only once.

2.4.2 DB2中common-table-expression及实例

common-table-expression前以WITH打头,语法如下:

select-statement:
>>-+-----------------------------------+--fullselect--●--------->
   |       .-,-----------------------. |                  
   |       V                         | |                  
   '-WITH----common-table-expression-+-'                  

>--+------------------+--●--+---------------------+--●---------->
   +-read-only-clause-+     '-optimize-for-clause-'      
   '-update-clause----'                                  

>--+------------------+--●-------------------------------------->
   '-isolation-clause-'      

>--+-------------------------------------+--●------------------><
   '-concurrent-access-resolution-clause-'      

common-table-expression:
>>-table-name--+---------------------------+-------------------->
               |    .-,-----------.        |   
               |    V             |    (1) |   
               '-(----column-name-+--)-----'   

>--AS--(--fullselect--)----------------------------------------><

Notes:
1. If a common table expression is recursive, or if the fullselect results in duplicate column names, column names must be specified.

参考:http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000879.html?cp=SSEPGG_10.5.0/2-12-6-3&lang=en

实例:

WITH
   paylevel AS
      (SELECT empno, YEAR(hiredate) AS hireyear, edlevel,
              salary+bonus+comm AS total_pay
              FROM employee
              WHERE edlevel > 16
      ),
   paybyed (educ_level, year_of_hire, avg_total_pay) AS
      (SELECT edlevel, hireyear, AVG(total_pay)
              FROM paylevel
              GROUP BY edlevel, hireyear
      )
SELECT empno, edlevel, year_of_hire, total_pay, avg_total_pay
    FROM paylevel, paybyed
    WHERE edlevel=educ_level
          AND hireyear = year_of_hire
          AND total_pay < avg_total_pay;

其中,paylevel和paybyed是临时创建的表,仅在这个语句中使用。

2.4.3 Oracle中subquery_factoring_clause

Oracle中也有和DB2中common-table-expression对应的概念,就是subquery_factoring_clause

oracle_subquery_factoring_clause.png

Figure 2: subquery_factoring_clause in Oracle

2.5 Oracle和DB2中tablespace概念的区别

Table space在DB2和Oracle中的含义有所不同。

A DB2® table space is a set of volumes on disks that hold the data sets in which tables are actually stored. All tables are kept in table spaces. A table space can have one or more tables.
As a general rule, you should have only one table in each table space. (IBM不推荐在1个table space中放多个table,在z/OS db2中创建table时,如果不指定table space,会自动创建一个和table名相同的table space)

参考:http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc.intro/src/tpc/db2z_tablespaces.htm

An Oracle database consists of one or more tablespaces. Tablespaces provide logical storage space that links a database to the physical disks hold the data. A tablespace is created from one or more datafiles. Datafiles are files in the file system or an area of disk space specified by a device. A tablespace can be enlarged by adding more datafiles.

参考:http://docs.oracle.com/cd/E35137_01/doc.32/e18460/oracle_db2_compared.htm

说明:DB2中table space是物理存储概念;而Oracle中的table space是逻辑存储概念,datafile才是物理存储的概念。

做数据库迁移时,它们是怎么的对应关系呢?请参考:
http://docs.oracle.com/cd/E35137_01/doc.32/e18460/oracle_db2_compared.htm#autoId8

3 Common Schema Objects

3.1 View

视图(view)是另一种察看一张或多张表中数据的方式。它就像在一个幻灯片上放一个覆盖物,以允许人们只能看到该幻灯片的一部分内容。例如,您可以在部门表上创建一个视图,使得用户只能去访问特定部门并更新其工资信息。您不希望让用户看到其他部门的工资信息。您创建的表的视图使用户只能去访问一个部门,而用户像使用表一样地使用该视图。因此,视图的使用是出于安全性的原因。大多数公司并不允许用户直接访问表,而是利用一个视图来完成它。用户通过视图来访问。对于缺乏经验的用户而言,视图可以被用来简化复杂的查询。

Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself.

参考:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8004.htm#SQLRF01504
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000935.html?cp=SSEPGG_10.5.0/2-12-7-117&lang=en

3.1.1 with check option

with check option可以用来限制对视图的操作。

我们来看下面的例子:

CREATE VIEW testview
AS SELECT empno,ename FROM emp
WHERE ename LIKE 'M%'
WITH CHECK OPTION;

这里我们创建了一个视图,并使用了with check option来限制了视图。先查看视图的内容:
select * from testview
EMPNO ENAME
———– ———–
7654 MARTIN
7934 MILLER

用下面语句更新其中一条记录:

UPDATE testview
SET ename = 'Mike'
WHERE empno = 7654;

这条更新语句可以成功执行,再执行另一条更新语句:

UPDATE testview
SET ename = 'Robin'
WHERE empno = 7654;

这条更新语句会出现错误ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句的错误。
这是因为前面我们在创建视图时指定了witch check option关键字,也就是说,更新后的每一条数据仍然要满足创建视图时指定的where条件,所以我们这里发生了错误ORA-01402。

参考:http://blog.sina.com.cn/s/blog_78bf12790100y377.html

3.2 Trigger

数据库中出现特定的事件时,触发器会自动执行。
在SQL:2003中定义了触发器,但标准推出的时间太晚,各个RDBMS产品中触发器语法都不同。

3.2.1 trigger例子

要求:创建一个trbu_product触发器,每当product表中的prod_price_n值发生改变时,该触发器就把一行插入到专门的审核表product_audit中。记录信息包括被修改行的主键、新的和旧的价格、修改该记录的用户名以及时间戳。

Oracle中方法如下:

CREATE OR REPLACE TRIGGER trbu_product
BEFORE UPDATE OF prod_price_n ON product
FOR EACH ROW
BEGIN
    INSERT INTO product_audit
    VALUES (:NEW.prod_id_n,
            :OLD.prod_price_n,
            :NEW.prod_price_n,
            USER,
            SYSDATE);
END;

DB2中方法如下:

CREATE TRIGGER trbu_product
AFTER UPDATE OF prod_price_n ON product
REFERENCING NEW AS NNN OLD AS OOO
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO product_audit
    VAULES (NNN.prod_id_n,
            OOO.prod_price_n,
            NNN.prod_price_n,
            USER,
            CURRENT DATE);
END

说明1:Oracle和DB2都支持语句REFERENCING NEW AS NNN OLD AS OOO来定制修改前和修改后的列值标记。
说明2:Oracle中可以直接使用:OLD和:NEW标记修改前和修改后的列值;而DB2中必须显式定制,例子中使用OOO和NNN。
说明3:触发器在Oracle中不能用一般的SQL语句定义,而是用PL/SQL定义。

参考:
SQL宝典,原书第二版,14.5.1 CREATE TRIGGER语法
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS01374

3.3 Sequence

Sequence是数据库中按照一定规则自动增加的数字序列,这个序列一般作为代理主键。
Oracle、DB2、PostgreSQL等数据库有Sequence,而MySQL、SQL Server、Sybase等数据库没有Sequence。
Oracle、DB2、PostgreSQL数据库中用currval、nextval分别表示Sequence的当前值和下一个值。

3.3.1 MySQL,SQL Server如何实现自动增加?

MySQL使用关键字AUTO_INCREMENT
SQL Server使用关键字IDENTITY

参考:http://www.w3schools.com/sql/sql_autoincrement.asp

说明:
Sequence是数据库系统中的一个对象,可以在整个数据库中使用,和表没有任何关系。
MySQL或SQL Server中的AUTO_INCREMENT和IDENTITY仅仅是指定在表中某一列上,作用范围只是这个表。

3.4 Partition

A partition is a division of a logical database or its constituent elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons.
PARTITION技术常用于构建数据仓库。PARTITION语句在CREATE TABLE语句里指定。

3.4.1 Oracle分区

Oracle为构建数据仓库提供了4种类型的分区方法:Range Partition, Hash Partition, List Partition, Composite Partition.

Range Partition
这是最常用的分区方法,基于列的值范围做分区,最常见的是基于时间字段的数据的范围的分区,比如:对于SALE表,可以对销售时间按照月份做一个Range Partition。

CREATE TABLE sales_range
(salesman_id   NUMBER(5),
 salesman_name VARCHAR2(30),
 sales_amount  NUMBER(10),
 sales_date    DATE)
COMPRESS
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));

分区的好处:如果要查询2000年2月的数据,将直接找到2000年2月的分区,避免了大量不必要的数据扫描。

参考:http://blog.csdn.net/outget1/article/details/4974301

3.4.2 DB2分区

DB2分区语法和Oracle有差异。如,DB2采用"ENDING AT"关键字,而不是Oracle的"VALUES LESS THAN"。当分区特征列为数字类型时,DB2中的"ENDING AT (n)"相当于Oracle中的"VALUES LESS THAN (n+1)"。

参考:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html?cp=SSEPGG_10.5.0/2-12-7-101&lang=en
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0608mcinerney/

3.5 Constraint和Index区别

约束(Constraint)是关系模型理论中的部分,属于逻辑层面的概念,有主键(Primary Key),外键(Foreign Key)等,用于保证数据完整性、唯一性等用途。

而索引(Index)则处于实现层面,比如可以对表的任意列建立索引。如果建立索引的列处于SQL语句中的Where条件中时,就可以得到快速的数据定位,从而进行更快的检索。

说明:对于Oracle database,会自动对主键和唯一键建立索引。

4 Dynamic SQL

What is Dynamic SQL?
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

However, some applications must accept (or build) and process a variety of SQL statements at run time. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the statement's makeup is unknown until run time. Such statements can, and probably will, change from execution to execution. They are aptly called dynamic SQL statements.

如:程序中嵌入SQL语句时,表名和字段名保存在程序的变量中,既然在变量中,则运行时表名和字段名才有具体的值,这时就要用Dynamic SQL了。

参考:http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg09dyn.htm

5 Referential integrity

引用完整性(referential integrity)是指为两个表中存在一定的联系,从而保证数据的完整性。例如:班级和学生,学生所在班级一定是在班级表中所存在的,不然就会出现不准确。引用完整性可以通过外键约束来实现。

参考:
http://en.wikipedia.org/wiki/Referential_integrity
http://www.databasejournal.com/features/db2/article.php/3870031/Referential-Integrity-Best-Practices-for-IBM-DB2.htm
http://www.cnblogs.com/zhengcheng/p/4177669.html

6 Deadlocks

A deadlock is created when two applications lock data that is needed by the other, resulting in a situation in which neither application can continue executing.

For example, in Figure 3, there are two applications running concurrently: Application A and Application B. The first transaction for Application A is to update the first row in Table 1, and the second transaction is to update the second row in Table 2. Application B updates the second row in Table 2 first, and then the first row in Table 1. At time T1, Application A locks the first row in Table 1. At the same time, Application B locks the second row in Table 2. At time T2, Application A requests a lock on the second row in Table 2. However, at the same time, Application B tries to lock the first row in Table 1. Because Application A will not release its lock on the first row in Table 1 until it can complete an update to the second row in Table 2, and Application B will not release its lock on the second row in Table 2 until it can complete an update to the first row in Table 1, a deadlock occurs. The applications wait until one of them releases its lock on the data.

deadlock_concept.png

Figure 3: Deadlock between applications

参考:http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005419.html


Author: cig01

Created: <2013-04-28 Sun 00:00>

Last updated: <2017-12-12 Tue 15:19>

Creator: Emacs 25.3.1 (Org mode 9.1.4)