互联网服务数据库
学习DB2的第一天
1. 首先要学习，就必须有环境，所以就到IBM网站下载了[IBM.DB2.Express-C.9.7数据库].db2exc_970_WIN_x86的安装程序，先安装在电脑上面。整个过程很简单，一步步执行就可以了。 安装好了，就可以通过DB2控制中心可以看到SAMPLE数据库,里面有很多表如：EMPLOYEE,DEPARTMENT,CUSTOMER...显示全部
1. 首先要学习，就必须有环境，所以就到IBM网站下载了[IBM.DB2.Express-C.9.7数据库].db2exc_970_WIN_x86的安装程序，先安装在电脑上面。整个过程很简单，一步步执行就可以了。 安装好了，就可以通过DB2控制中心可以看到SAMPLE数据库,里面有很多表如：EMPLOYEE,DEPARTMENT,CUSTOMER等单。
2.数据库类型: 每一个数据库都有自己的数据类型的，数据库类型整体上差不多相同，就是名字叫法不一样。 DB2的数据类型有：BIGINT,BLOG,CHARACTER,CLOB,DATE,DECIMAL,GRAPHIC,INTEGER,LONG VARCHAR,SMALLINT,TIME,TIMESTAMP,VARCHAR,VARGRAPHIC,XML,SYSPROC.DB2SQLSTATE,SYSIBMADM.FILE_TYPE
3.DB2工具：DB2数据的工具实在太差，所以就下载了ToadForDB2Freeware_4.0.1.921工具，是免费的。还可以用，比较 DB2的数据工具方便多了，就是要配置一下连接而已，其实很简单。
4.建立表，修改表，删除表与其他数据库差不多。DB2数据库语句的格式实在是难看的懂，没有学过SQL的就要头大了，其实很简单 例如
建立表：
1.CREATE TABLE SYSTEM.STAXD( IHINCU DECIMAL(6,0), ILPROD CHAR(15),ILDATE DATE)；
2.CREATE TABLE SYSTEM.STAX AS SELECT ILPROD,ILDATE FROM SYSTEM.STAXD;
修改表:
1.ADD: ALTER TABLE SYSTEM.STAXD ADD ILDATES DATE
ADD ILUSER CHAR(20);
2.DROP:ALTER TABLE SYSTEM.STAXD DROP ILDATES
DROP ILUSER;
3.ALTER:ALTER TABLE SYSTEM.STAXD ALTER COLUMN ILPROD SET DATA TYPE CHAR(20)
ALTER COLUMN IUMS SET DATA TYPE CHAR(10)
ALTER COLUMN IHINCU SET DEFAULT '123';
注意：还可以混合在一起写的，要注意的部分是粗体。
IBM Examples
Example 1: Add a new column named RATING, which is one character long, to the DEPARTMENT table.
ALTER TABLE DEPARTMENT
ADD RATING CHAR(1)
Example 2: Add a new column named SITE_NOTES to the PROJECT table. Create SITE_NOTES as a varying-length column with a maximum length of 1000 bytes. The values of the column do not have an associated character set and therefore should not be converted.
ALTER TABLE PROJECT
ADD SITE_NOTES VARCHAR(1000) FOR BIT DATA
Example 3: Assume a table called EQUIPMENT exists defined with the following columns:
Column Name Data Type
EQUIP_NO INT
EQUIP_DESC VARCHAR(50)
LOCATION VARCHAR(50)
EQUIP_OWNER CHAR(3)
Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. DEPTNO is the primary key of the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.
ALTER TABLE EQUIPMENT
ADD CONSTRAINT DEPTQUIP
FOREIGN KEY (EQUIP_OWNER)
REFERENCES DEPARTMENT
ON DELETE SET NULL
Also, an additional column is needed to allow the recording of the quantity associated with this equipment record. Unless otherwise specified, the EQUIP_QTY column should have a value of 1 and must never be null.
ALTER TABLE EQUIPMENT
ADD COLUMN EQUIP_QTY
SMALLINT NOT NULL DEFAULT 1
Example 4: Alter table EMPLOYEE. Add the check constraint named REVENUE defined so that each employee must make a total of salary and commission greater than $30,000.
ALTER TABLE EMPLOYEE
ADD CONSTRAINT REVENUE
CHECK (SALARY + COMM > 30000)
Example 5: Alter table EMPLOYEE. Drop the constraint REVENUE which was previously defined.
ALTER TABLE EMPLOYEE
DROP CONSTRAINT REVENUE
Example 6: Alter a table to log SQL changes in the default format.
ALTER TABLE SALARY1
DATA CAPTURE NONE
Example 7: Alter a table to log SQL changes in an expanded format.
ALTER TABLE SALARY2
DATA CAPTURE CHANGES
Example 8: Alter the EMPLOYEE table to add 4 new columns with default values.
ALTER TABLE EMPLOYEE
ADD COLUMN HEIGHT MEASURE DEFAULT MEASURE(1)
ADD COLUMN BIRTHDAY BIRTHDATE DEFAULT DATE('01-01-1850')
ADD COLUMN FLAGS BLOB(1M) DEFAULT BLOB(X'01')
ADD COLUMN PHOTO PICTURE DEFAULT BLOB(X'00')
The default values use various function names when specifying the default. Since MEASURE is a distinct type based on INTEGER, the MEASURE function is used. The HEIGHT column default could have been specified without the function since the source type of MEASURE is not BLOB or a datetime data type. Since BIRTHDATE is a distinct type based on DATE, the DATE function is used (BIRTHDATE cannot be used here). For the FLAGS and PHOTO columns the default is specified using the BLOB function even though PHOTO is a distinct type. To specify a default for BIRTHDAY, FLAGS and PHOTO columns, a function must be used because the type is a BLOB or a distinct type sourced on a BLOB or datetime data type.
Example 9: A table called CUSTOMERS is defined with the following columns:
Column Name Data Type
BRANCH_NO SMALLINT
CUSTOMER_NO DECIMAL(7)
CUSTOMER_NAME VARCHAR(50)
In this table, the primary key is made up of the BRANCH_NO and CUSTOMER_NO columns. To distribute the table, you will need to create a distribution key for the table. The table must be defined in a table space on a single-node database partition group. The primary key must be a superset of the distribution key columns: at least one of the columns of the primary key must be used as the distribution key. Make BRANCH_NO the distribution key as follows:
ALTER TABLE CUSTOMERS
ADD DISTRIBUTE BY HASH (BRANCH_NO)
Example 10: A remote table EMPLOYEE was created in a federated system using transparent DDL. Alter the remote table EMPLOYEE to add the columns PHONE_NO and WORK_DEPT; also add a primary key on the existing column EMP_NO and the new column WORK_DEPT.
ALTER TABLE EMPLOYEE
ADD COLUMN PHONE_NO CHAR(4) NOT NULL
ADD COLUMN WORK_DEPT CHAR(3)
ADD PRIMARY KEY (EMP_NO, WORK_DEPT)
Example 11: Alter the DEPARTMENT table to add a functional dependency FD1, then drop the functional dependency FD1 from the DEPARTMENT table.
ALTER TABLE DEPARTMENT
ADD CONSTRAINT FD1
CHECK ( DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED
ALTER TABLE DEPARTMENT
DROP CHECK FD1
Example 12: Change the default value for the WORKDEPT column in the EMPLOYEE table to 123.
ALTER TABLE EMPLOYEE
ALTER COLUMN WORKDEPT
SET DEFAULT '123'
Example 13: Associate the security policy DATA_ACCESS with the table EMPLOYEE.
ALTER TABLE EMPLOYEE
ADD SECURITY POLICY DATA_ACCESS
Example 14: Alter the table EMPLOYEE to protect the SALARY column.
ALTER TABLE EMPLOYEE
ALTER COLUMN SALARY
SECURED WITH EMPLOYEESECLABEL
Example 15: Assume that you have a table named SALARY_DATA that is defined with the following columns:
Column Name Data Type
----------- ---------
EMP_NAME VARCHAR(50) NOT NULL
EMP_ID SMALLINT NOT NULL
EMP_POSITION VARCHAR(100) NOT NULL
SALARY DECIMAL(5,2)
PROMOTION_DATE DATE NOT NULL
Change this table to allow salaries to be stored in a DECIMAL(6,2) column, make PROMOTION_DATE an optional field that can be set to the null value, and remove the EMP_POSITION column.
ALTER TABLE SALARY_DATA
ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
ALTER COLUMN PROMOTION_DATE DROP NOT NULL
DROP COLUMN EMP_POSITION
Example 16: Add a column named DATE_ADDED to the table BOOKS. The default value for this column is the current timestamp.
ALTER TABLE BOOKS
ADD COLUMN DATE_ADDED TIMESTAMP
WITH DEFAULT CURRENT TIMESTAMP
DROP
Examples
Example 1: Drop table TDEPT.
DROP TABLE TDEPT
Example 2: Drop the view VDEPT.
DROP VIEW VDEPT
Example 3: The authorization ID HEDGES attempts to drop an alias.
DROP ALIAS A1
The alias HEDGES.A1 is removed from the catalogs.
Example 4: Hedges attempts to drop an alias, but specifies T1 as the alias-name, where T1 is the name of an existing table (not the name of an alias).
DROP ALIAS T1
This statement fails (SQLSTATE 42809).
Example 5:
Drop the BUSINESS_OPS database partition group. To drop the database partition group, the two table spaces (ACCOUNTING and PLANS) in the database partition group must first be dropped.
DROP TABLESPACE ACCOUNTING
DROP TABLESPACE PLANS
DROP DATABASE PARTITION GROUP BUSINESS_OPS
Example 6: Pellow wants to drop the CENTRE function, which he created in his PELLOW schema, using the signature to identify the function instance to be dropped.
DROP FUNCTION CENTRE (INT,FLOAT)
Example 7: McBride wants to drop the FOCUS92 function, which she created in the PELLOW schema, using the specific name to identify the function instance to be dropped.
DROP SPECIFIC FUNCTION PELLOW.FOCUS92
Example 8: Drop the function ATOMIC_WEIGHT from the CHEM schema, where it is known that there is only one function with that name.
DROP FUNCTION CHEM.ATOMIC_WEIGHT
Example 9: Drop the trigger SALARY_BONUS, which caused employees under a specified condition to receive a bonus to their salary.
DROP TRIGGER SALARY_BONUS收起
2.数据库类型: 每一个数据库都有自己的数据类型的，数据库类型整体上差不多相同，就是名字叫法不一样。 DB2的数据类型有：BIGINT,BLOG,CHARACTER,CLOB,DATE,DECIMAL,GRAPHIC,INTEGER,LONG VARCHAR,SMALLINT,TIME,TIMESTAMP,VARCHAR,VARGRAPHIC,XML,SYSPROC.DB2SQLSTATE,SYSIBMADM.FILE_TYPE
3.DB2工具：DB2数据的工具实在太差，所以就下载了ToadForDB2Freeware_4.0.1.921工具，是免费的。还可以用，比较 DB2的数据工具方便多了，就是要配置一下连接而已，其实很简单。
4.建立表，修改表，删除表与其他数据库差不多。DB2数据库语句的格式实在是难看的懂，没有学过SQL的就要头大了，其实很简单 例如
建立表：
1.CREATE TABLE SYSTEM.STAXD( IHINCU DECIMAL(6,0), ILPROD CHAR(15),ILDATE DATE)；
2.CREATE TABLE SYSTEM.STAX AS SELECT ILPROD,ILDATE FROM SYSTEM.STAXD;
修改表:
1.ADD: ALTER TABLE SYSTEM.STAXD ADD ILDATES DATE
ADD ILUSER CHAR(20);
2.DROP:ALTER TABLE SYSTEM.STAXD DROP ILDATES
DROP ILUSER;
3.ALTER:ALTER TABLE SYSTEM.STAXD ALTER COLUMN ILPROD SET DATA TYPE CHAR(20)
ALTER COLUMN IUMS SET DATA TYPE CHAR(10)
ALTER COLUMN IHINCU SET DEFAULT '123';
注意：还可以混合在一起写的，要注意的部分是粗体。
IBM Examples
Example 1: Add a new column named RATING, which is one character long, to the DEPARTMENT table.
ALTER TABLE DEPARTMENT
ADD RATING CHAR(1)
Example 2: Add a new column named SITE_NOTES to the PROJECT table. Create SITE_NOTES as a varying-length column with a maximum length of 1000 bytes. The values of the column do not have an associated character set and therefore should not be converted.
ALTER TABLE PROJECT
ADD SITE_NOTES VARCHAR(1000) FOR BIT DATA
Example 3: Assume a table called EQUIPMENT exists defined with the following columns:
Column Name Data Type
EQUIP_NO INT
EQUIP_DESC VARCHAR(50)
LOCATION VARCHAR(50)
EQUIP_OWNER CHAR(3)
Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. DEPTNO is the primary key of the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.
ALTER TABLE EQUIPMENT
ADD CONSTRAINT DEPTQUIP
FOREIGN KEY (EQUIP_OWNER)
REFERENCES DEPARTMENT
ON DELETE SET NULL
Also, an additional column is needed to allow the recording of the quantity associated with this equipment record. Unless otherwise specified, the EQUIP_QTY column should have a value of 1 and must never be null.
ALTER TABLE EQUIPMENT
ADD COLUMN EQUIP_QTY
SMALLINT NOT NULL DEFAULT 1
Example 4: Alter table EMPLOYEE. Add the check constraint named REVENUE defined so that each employee must make a total of salary and commission greater than $30,000.
ALTER TABLE EMPLOYEE
ADD CONSTRAINT REVENUE
CHECK (SALARY + COMM > 30000)
Example 5: Alter table EMPLOYEE. Drop the constraint REVENUE which was previously defined.
ALTER TABLE EMPLOYEE
DROP CONSTRAINT REVENUE
Example 6: Alter a table to log SQL changes in the default format.
ALTER TABLE SALARY1
DATA CAPTURE NONE
Example 7: Alter a table to log SQL changes in an expanded format.
ALTER TABLE SALARY2
DATA CAPTURE CHANGES
Example 8: Alter the EMPLOYEE table to add 4 new columns with default values.
ALTER TABLE EMPLOYEE
ADD COLUMN HEIGHT MEASURE DEFAULT MEASURE(1)
ADD COLUMN BIRTHDAY BIRTHDATE DEFAULT DATE('01-01-1850')
ADD COLUMN FLAGS BLOB(1M) DEFAULT BLOB(X'01')
ADD COLUMN PHOTO PICTURE DEFAULT BLOB(X'00')
The default values use various function names when specifying the default. Since MEASURE is a distinct type based on INTEGER, the MEASURE function is used. The HEIGHT column default could have been specified without the function since the source type of MEASURE is not BLOB or a datetime data type. Since BIRTHDATE is a distinct type based on DATE, the DATE function is used (BIRTHDATE cannot be used here). For the FLAGS and PHOTO columns the default is specified using the BLOB function even though PHOTO is a distinct type. To specify a default for BIRTHDAY, FLAGS and PHOTO columns, a function must be used because the type is a BLOB or a distinct type sourced on a BLOB or datetime data type.
Example 9: A table called CUSTOMERS is defined with the following columns:
Column Name Data Type
BRANCH_NO SMALLINT
CUSTOMER_NO DECIMAL(7)
CUSTOMER_NAME VARCHAR(50)
In this table, the primary key is made up of the BRANCH_NO and CUSTOMER_NO columns. To distribute the table, you will need to create a distribution key for the table. The table must be defined in a table space on a single-node database partition group. The primary key must be a superset of the distribution key columns: at least one of the columns of the primary key must be used as the distribution key. Make BRANCH_NO the distribution key as follows:
ALTER TABLE CUSTOMERS
ADD DISTRIBUTE BY HASH (BRANCH_NO)
Example 10: A remote table EMPLOYEE was created in a federated system using transparent DDL. Alter the remote table EMPLOYEE to add the columns PHONE_NO and WORK_DEPT; also add a primary key on the existing column EMP_NO and the new column WORK_DEPT.
ALTER TABLE EMPLOYEE
ADD COLUMN PHONE_NO CHAR(4) NOT NULL
ADD COLUMN WORK_DEPT CHAR(3)
ADD PRIMARY KEY (EMP_NO, WORK_DEPT)
Example 11: Alter the DEPARTMENT table to add a functional dependency FD1, then drop the functional dependency FD1 from the DEPARTMENT table.
ALTER TABLE DEPARTMENT
ADD CONSTRAINT FD1
CHECK ( DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED
ALTER TABLE DEPARTMENT
DROP CHECK FD1
Example 12: Change the default value for the WORKDEPT column in the EMPLOYEE table to 123.
ALTER TABLE EMPLOYEE
ALTER COLUMN WORKDEPT
SET DEFAULT '123'
Example 13: Associate the security policy DATA_ACCESS with the table EMPLOYEE.
ALTER TABLE EMPLOYEE
ADD SECURITY POLICY DATA_ACCESS
Example 14: Alter the table EMPLOYEE to protect the SALARY column.
ALTER TABLE EMPLOYEE
ALTER COLUMN SALARY
SECURED WITH EMPLOYEESECLABEL
Example 15: Assume that you have a table named SALARY_DATA that is defined with the following columns:
Column Name Data Type
----------- ---------
EMP_NAME VARCHAR(50) NOT NULL
EMP_ID SMALLINT NOT NULL
EMP_POSITION VARCHAR(100) NOT NULL
SALARY DECIMAL(5,2)
PROMOTION_DATE DATE NOT NULL
Change this table to allow salaries to be stored in a DECIMAL(6,2) column, make PROMOTION_DATE an optional field that can be set to the null value, and remove the EMP_POSITION column.
ALTER TABLE SALARY_DATA
ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
ALTER COLUMN PROMOTION_DATE DROP NOT NULL
DROP COLUMN EMP_POSITION
Example 16: Add a column named DATE_ADDED to the table BOOKS. The default value for this column is the current timestamp.
ALTER TABLE BOOKS
ADD COLUMN DATE_ADDED TIMESTAMP
WITH DEFAULT CURRENT TIMESTAMP
DROP
Examples
Example 1: Drop table TDEPT.
DROP TABLE TDEPT
Example 2: Drop the view VDEPT.
DROP VIEW VDEPT
Example 3: The authorization ID HEDGES attempts to drop an alias.
DROP ALIAS A1
The alias HEDGES.A1 is removed from the catalogs.
Example 4: Hedges attempts to drop an alias, but specifies T1 as the alias-name, where T1 is the name of an existing table (not the name of an alias).
DROP ALIAS T1
This statement fails (SQLSTATE 42809).
Example 5:
Drop the BUSINESS_OPS database partition group. To drop the database partition group, the two table spaces (ACCOUNTING and PLANS) in the database partition group must first be dropped.
DROP TABLESPACE ACCOUNTING
DROP TABLESPACE PLANS
DROP DATABASE PARTITION GROUP BUSINESS_OPS
Example 6: Pellow wants to drop the CENTRE function, which he created in his PELLOW schema, using the signature to identify the function instance to be dropped.
DROP FUNCTION CENTRE (INT,FLOAT)
Example 7: McBride wants to drop the FOCUS92 function, which she created in the PELLOW schema, using the specific name to identify the function instance to be dropped.
DROP SPECIFIC FUNCTION PELLOW.FOCUS92
Example 8: Drop the function ATOMIC_WEIGHT from the CHEM schema, where it is known that there is only one function with that name.
DROP FUNCTION CHEM.ATOMIC_WEIGHT
Example 9: Drop the trigger SALARY_BONUS, which caused employees under a specified condition to receive a bonus to their salary.
DROP TRIGGER SALARY_BONUS收起