Db2 简明教程
DB2 - Tables
表是由数据库管理器维护的逻辑结构。在表中，每个垂直块称为列（元组），每个水平块称为行（实体）。以列和行为形式存储的数据集合称为表。在表中，每一列都有不同的数据类型。表用于存储持久性数据。
Tables are logical structure maintained by Database manager. In a table each vertical block called as column (Tuple) and each horizontal block called as row (Entity). The collection of data stored in the form of columns and rows is known as a table. In tables, each column has different data type. Tables are used to store persistent data.
Type of tables
-
Base Tables: They hold persistent data. There are different kinds of base tables, including: Regular Tables: General purpose tables, Common tables with indexes are general purpose tables.Multidimensional Clustering Table (MDC): This type of table physically clustered on more than one key, and it used to maintain large database environments. These type of tables are not supported in DB2 pureScale.Insert time clustering Table (ITC): Similar to MDC tables, rows are clustered by the time they are inserted into the tables. They can be partitioned tables. They too, do not support pureScale environment.Range-Clustered tables Table (RCT): These type of tables provide fast and direct access of data. These are implemented as sequential clusters. Each record in the table has a record ID. These type of tables are used where the data is clustered tightly with one or more columns in the table. This type of tables also do not support in DB2 pureScale.Partitioned Tables: These type of tables are used in data organization schema, in which table data is divided into multiple storage objects. Data partitions can be added to, attached to and detached from a partitioned table. You can store multiple data partition from a table in one tablespace.Temporal Tables: History of a table in a database is stored in temporal tables such as details of the modifications done previously.
-
Temporary Tables: For temporary work of different database operations, you need to use temporary tables. The temporary tables (DGTTs) do not appear in system catalog, XML columns cannot be used in created temporary tables.
-
Materialized Query Tables: MQT can be used to improve the performance of queries. These types of tables are defined by a query, which is used to determine the data in the tables.
Creating Tables
以下语法创建表：
The following syntax creates table:
Syntax : [要创建新表]
Syntax: [To create a new table]
db2 create table <schema_name>.<table_name>
(column_name column_type....) in <tablespace_name>Example : 我们在“professional”架构中创建一个表来存储“employee”详细信息。此表有“id、name、jobrole、joindate、salary”字段，此表数据将存储在表空间“ts1”中。
Example: We create a table to store “employee” details in the schema of “professional”. This table has “id, name, jobrole, joindate, salary” fields and this table data would be stored in tablespace “ts1”.
db2 create table professional.employee(id int, name
varchar(50),jobrole varchar(30),joindate date,
salary double) in ts1Output:
Output:
DB20000I The SQL command completed successfully.Listing table details
以下语法用于列出表详细信息：
The following syntax is used to list table details:
Syntax : [要查看使用架构创建的表的列表]
Syntax: [To see the list of tables created with schemas]
db2 select tabname, tabschema, tbspace from syscat.tablesExample : [要查看当前数据库中的表的列表]
Example: [To see the list of tables in the current database]
db2 select tabname, tabschema, tbspace from syscat.tablesOutput:
Output:
TABNAME TABSCHEMA TBSPACE
------------ ------------- --------
EMPLOYEE PROFESSIONAL TS1
1 record(s) selected.Listing columns in a table
以下语法列出表格中的列：
The following syntax lists columns in a table:
Syntax : [要查看表的列和数据类型]
Syntax: [To see columns and data types of a table]
db2 describe table <table_name>Example : [要查看表“employee”的列和数据类型]
Example: [To see the columns and data types of table ‘employee’]
db2 describe table professional.employeeOutput:
Output:
Data type Column
Column name schema Data type name Length Scale Nulls
------ ----- --------- ----------------- --------- ----- ------
ID SYSIBM INTEGER 4 0 Yes
NAME SYSIBM VARCHAR 50 0 Yes
JOBROLE SYSIBM VARCHAR 30 0 Yes
JOINDATE SYSIBM DATE 4 0 Yes
SALARY SYSIBM DOUBLE 8 0 Yes
5 record(s) selected.Hidden Columns
可以隐藏表中的整个列。如果调用“select * from”查询，则结果表中不会返回隐藏的列。将数据插入表时，没有列列表的“INSERT”语句不会期望任何隐式隐藏列的值。这些类型的列在物化查询表中高度引用。这些类型的列不支持创建临时表。
You can hide an entire column of a table. If you call “select * from” query, the hidden columns are not returned in the resulting table. When you insert data into a table, an “INSERT” statement without a column list does not expect values for any implicitly hidden columns. These type of columns are highly referenced in materialized query tables. These type of columns do not support to create temporary tables.
Creating table with hidden column
以下语法使用隐藏列创建表：
The following syntax creates table with hidden columns:
Syntax : [要创建带有隐藏列的表]
Syntax: [To create a table with hidden columns]
db2 create table <tab_name> (col1 datatype,col2 datatype
implicitly hidden)Example : [要创建带有隐藏列“phone”的“customer”表]
Example: [To create a ‘customer’ table with hidden columns ‘phone’]
db2 create table professional.customer(custid integer not
null, fullname varchar(100), phone char(10)
implicitly hidden)Inserting data values in table
以下语法在表中插入值：
The following syntax inserts values in the table:
Syntax : [要在表中插入值]
Syntax: [To insert values into a table]
db2 insert into <tab_name>(col1,col2,...)
values(val1,val2,..)Example : [要在“customer”表中插入值]
Example: [To insert values in ‘customer’ table]
db2 insert into professional.customer(custid, fullname, phone)
values(100,'ravi','9898989')
db2 insert into professional.customer(custid, fullname, phone)
values(101,'krathi','87996659')
db2 insert into professional.customer(custid, fullname, phone)
values(102,'gopal','768678687')Output:
Output:
DB20000I The SQL command completed successfully.Retrieving values from table
以下语法从表中检索值：
The following syntax retrieves values from the table:
Syntax ：[从表中检索值]
Syntax: [To retrieve values form a table]
db2 select * from <tab_name>Example ：[从“customer”表中检索值]
Example: [To retrieve values from ‘customer’ table]
db2 select * from professional.customerOutput:
Output:
CUSTID FULLNAME
----------- ------------------------
100 ravi
101 krathi
102 gopal
3 record(s) selected.Retrieving values from a table including hidden columns
以下语法从选定列中检索值：
The following syntax retrieves values from selected columns:
Syntax ：[从表中检索选定的隐藏列值]
Syntax: [To retrieve selected hidden columns values from a table]
db2 select col1,col2,col3 from <tab_name>Example ：[从表中检索选定列值结果]
Example: [To retrieve selected columns values result from a table]
db2 select custid,fullname,phone from professional.customerOutput:
Output:
CUSTID FULLNAME PHONE
------- --------- ------------
100 ravi 9898989
101 krathi 87996659
102 gopal 768678687
3 record(s) selected.如果您想要查看隐藏列中的数据，您需要执行“DESCRIBE”命令。
If you want to see the data in the hidden columns, you need to execute “DESCRIBE” command.
Syntax ：
Syntax:
db2 describe table <table_name> show detailExample:
Example:
db2 describe table professional.customer show detailOutput:
Output:
Column name Data type schema Data type name Column
column Partitionkey code
Length Scale Nulls
number sequence page Hidden Default
--------------- -------------------- --------------- -------- ----
---- -------- ---------- ------------- -------- ----------- ------
---
CUSTID SYSIBM INTEGER 4 0
No 0 0 0 No
FULLNAME SYSIBM VARCHAR 100 0
Yes 1 0 1208 No
PHONE SYSIBM CHARACTER 10 0
Yes 2 0 1208 Implicitly
3 record(s) selected.Altering the type of table columns
您可以使用此“alter”命令修改我们的表结构，如下所示：
You can modify our table structure using this “alter” command as follows:
Syntax ：
Syntax:
db2 alter table <tab_name> alter column <col_name> set data type <data_type>Example: ：[将员工表的列“id”的数据类型从“int”修改为“bigint”]
Example: [To modify the data type for column “id” from “int” to “bigint” for employee table]
db2 alter table professional.employee alter column id set data type bigintOutput: ：
Output::
DB20000I The SQL command completed successfully.Altering column name
您可以更改列名，如下所示：
You can change column name as shown below:
Syntax ：[将表的列名从旧名称修改为新名称]
Syntax: [To modify the column name from old name to new name of a table]
db2 alter table <tab_name> rename column <old_name> to <new_name>Example: ：[将“customers”表中列名从“fullname”修改为“custname”]
Example: [To modify the column name from “fullname” to “custname” in “customers” table.]
db2 alter table professional.customer rename column fullname to custnameDropping the tables
要删除任何表，您需要使用“DROP”命令，如下所示：
To delete any table, you need to use the “DROP” command as follows:
Syntax ：
Syntax:
db2 drop table <tab_name>Example: ：[从数据库中删除客户表]
Example: [To drop customer table form database]
db2 drop table professional.customers要删除表的整个层次结构（包括触发器和关系），您需要使用“DROP TABLE HIERARCHY”命令。
To delete the entire hierarchy of the table (including triggers and relation), you need to use “DROP TABLE HIERARCHY” command.
Syntax ：
Syntax:
db2 drop table hierarchy <tab_name>Example: ：[删除表“customer”的整个层次结构]
Example: [To drop entire hierarchy of a table ‘customer’]
db2 drop table hierarchy professional.customers