DB2-LAB3-创建表和索引
Creating database objects
• Create a set of tables using the Data Server Manager to build the CREATE TABLE statement or using saved DDL in a file.
• Create indexes for tables using the CREATE INDEX statement.
• Create views and alias objects using SQL statements in a file.
• Create foreign key and check constraints for a table using SQL statements in a file.
• Use the db2look utility to extract database object definitions from a DB2 database.
本次LAB中我们使用的表格模型为
Create the ALBUMS table.
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_table_albums.ddl
create table music.albums (title varchar (50), artno smallint not null, itemno smallint not null) in tsp04 index in tsp05
DB20000I The SQL command completed successfully.
alter table music.albums primary key (itemno)
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2 describe table music.albums
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TITLE SYSIBM VARCHAR 50 0 Yes
ARTNO SYSIBM SMALLINT 2 0 No
ITEMNO SYSIBM SMALLINT 2 0 No
3 record(s) selected.
db2inst1@shahuang-lt:~/ddl$
使用SQL创建一组表，从最后打印的表格类型来看。有3种类型，ATV，T:table, A:Alias, V:visial
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_tables.ddl
create table MUSIC.concerts (artno smallint not null, date date not null, city varchar (25) not null with default) in tsp04
DB20000I The SQL command completed successfully.
create table MUSIC.reorder (itemno smallint not null, timestamp timestamp) in TSP02
DB20000I The SQL command completed successfully.
create table MUSIC.artists (artno smallint not null, name varchar(50), classification char(1) not null, bio clob(100K) logged compact, picture blob(500K) not logged compact, primary key (artno)) in tsp01 index in tsp02 long in tsp03
DB20000I The SQL command completed successfully.
create table MUSIC.stock (ITEMNO SMALLINT NOT NULL , TYPE CHAR(1) NOT NULL , PRICE DECIMAL(5,2) NOT NULL WITH DEFAULT , QTY INTEGER NOT NULL WITH DEFAULT, SYS_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN, SYS_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN, TX_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN, PERIOD SYSTEM_TIME (SYS_START,SYS_END) ) in tsp06
DB20000I The SQL command completed successfully.
CREATE TABLE MUSIC.STOCK_HISTORY LIKE MUSIC.STOCK IN tsp06
DB20000I The SQL command completed successfully.
ALTER TABLE MUSIC.STOCK ADD VERSIONING USE HISTORY TABLE MUSIC.STOCK_HISTORY
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2 list tables for schema music
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ALBUMS MUSIC T 2024-05-19-13.20.21.793875
ARTISTS MUSIC T 2024-05-19-13.31.22.277756
CONCERTS MUSIC T 2024-05-19-13.31.22.199753
REORDER MUSIC T 2024-05-19-13.31.22.239356
STOCK MUSIC T 2024-05-19-13.31.22.452103
STOCK_HISTORY MUSIC T 2024-05-19-13.31.22.505028
6 record(s) selected.
db2inst1@shahuang-lt:~/ddl$
创建索引，针对STOCK表格的ITEMNO列。
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_stock_ix.ddl
create index music.stockitem_ix on music.stock(itemno)
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2 describe indexes for table music.stock
Index Index Unique Number of Index Index Null
schema name rule columns type partitioning keys
------------------------------- ------------------- -------------- -------------- --------------------------- -------------- ------
MUSIC STOCKITEM_IX D 1 RELATIONAL DATA - Y
1 record(s) selected.
创建视图和别名
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_VIEW_ALIAS.ddl
create view music.music as select title, classification, name from music.albums alb, music.artists art where art.artno = alb.artno
DB20000I The SQL command completed successfully.
create view music.inventory (type, itemno, totcost, totqty) as select type, itemno, sum (price * qty), sum (qty) from music.stock group by type, itemno
DB20000I The SQL command completed successfully.
create alias music.singers for music.artists
DB20000I The SQL command completed successfully.
create alias music.emptystock for music.reorder
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$ db2 list tables for schema music
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ALBUMS MUSIC T 2024-05-19-13.20.21.793875
ARTISTS MUSIC T 2024-05-19-13.31.22.277756
CONCERTS MUSIC T 2024-05-19-13.31.22.199753
EMPTYSTOCK MUSIC A 2024-05-19-13.35.36.179948
INVENTORY MUSIC V 2024-05-19-13.35.36.162634
MUSIC MUSIC V 2024-05-19-13.35.36.139440
REORDER MUSIC T 2024-05-19-13.31.22.239356
SINGERS MUSIC A 2024-05-19-13.35.36.176985
STOCK MUSIC T 2024-05-19-13.31.22.452103
STOCK_HISTORY MUSIC T 2024-05-19-13.31.22.505028
10 record(s) selected.
db2inst1@shahuang-lt:~/ddl$
创建表约束。
增加两个外键。
on delete都是删除主表，对主表的操作很自由。对子表的操作是有条件的。
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_ri_cc.ddl
alter table music.albums add constraint ARTNO_FK foreign key (artno) references music.artists (artno) on delete cascade on update no action
DB20000I The SQL command completed successfully.
alter table music.stock foreign key ITEMNO_FK (itemno) references music.albums on delete restrict
DB20000I The SQL command completed successfully.
alter table music.stock add constraint cctype check (type in ('D', 'C', 'R'))
DB20000I The SQL command completed successfully.
创建触发器：每行触发。
db2inst1@shahuang-lt:~/ddl$ db2 -tvf create_trigger.ddl
create trigger music.reorder
after update of qty on music.stock
referencing new as n
for each row
mode db2sql
when (n.qty <= 5)
insert into music.reorder values (n.itemno, current timestamp)
DB20000I The SQL command completed successfully.
db2inst1@shahuang-lt:~/ddl$
查看数据库的结构。将结构抓取出来导出到ALBUMS.DDL
db2inst1@shahuang-lt:~/ddl$ db2look -d musicdb -e -z music -t albums -o ALBUMS.DDL
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Specified SCHEMA is: MUSIC
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Schema name is ignored for the Federated Section
-- Output is sent to file: ALBUMS.DDL
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful
db2inst1@shahuang-lt:~/ddl$ cat ALBUMS.DDL
-- This CLP file was created using DB2LOOK Version "11.5"
-- Timestamp: Sun May 19 13:53:59 2024
-- Database Name: MUSICDB
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.4.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO MUSICDB;
------------------------------------------------
-- DDL Statements for Table "MUSIC "."ALBUMS"
------------------------------------------------
CREATE TABLE "MUSIC "."ALBUMS" (
"TITLE" VARCHAR(50 OCTETS) ,
"ARTNO" SMALLINT NOT NULL ,
"ITEMNO" SMALLINT NOT NULL )
IN "TSP04" INDEX IN "TSP05"
ORGANIZE BY ROW;
-- DDL Statements for Primary Key on Table "MUSIC "."ALBUMS"
ALTER TABLE "MUSIC "."ALBUMS"
ADD PRIMARY KEY
("ITEMNO")
ENFORCED;
-- DDL Statements for Foreign Keys on Table "MUSIC "."ALBUMS"
ALTER TABLE "MUSIC "."ALBUMS"
ADD CONSTRAINT "ARTNO_FK" FOREIGN KEY
("ARTNO")
REFERENCES "MUSIC "."ARTISTS"
("ARTNO")
ON DELETE CASCADE
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
----------------------------
-- DDL Statements for Views
----------------------------
SET CURRENT SCHEMA = "DB2INST1";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2INST1";
create view music.music as select title, classification, name from music.albums
alb, music.artists art where art.artno = alb.artno;
COMMIT WORK;
CONNECT RESET;
TERMINATE;
db2inst1@shahuang-lt:~/ddl$
ppt讲解
schema
模式（Schema）是一个逻辑分组，每个数据库内部的对象在创建时，都可以显式或者隐式地指定模式，同一个模式下的对象构成一个集合。从某种角度来说，模式与用户比较相似，但是与用户也有不同之处，DB2的用户必须存在于操作系统中，而模式并不需要存在于操作系统中，也不需要与用户一一对应上。
https://blog.51cto.com/u_16213719/9906007
temporary tables
在DB2中，临时表是一种特殊类型的表，它用于存储会话或者事务过程中的数据。临时表中的数据只在当前会话或者事务中有效，且当会话或者事务结束时数据会自动清除。
临时表可以分为两类：
- 会话临时表：这种类型的表中的数据只在当前数据库会话期间有效。
- 事务临时表：这种类型的表中的数据只在当前事务期间有效。
相关语法：
https://blog.csdn.net/shirsl/article/details/130930483
表分区
在DB2中，表分区是将一个大表物理分割成多个小表的技术，目的是为了改善数据库的性能。分区表通过减少查询必须扫描的数据量，可以显著提高查询和数据操纵语言（DML）操作的性能。
分区表可以通过多种方式进行分区，包括范围分区、列表分区、散列分区和复合分区。
视图
视图在数据库内存储的是 SELECT 语句，可以理解为一条SQL 语句的快捷方式, 每次select 视图的时候都会执行构成视图的select 语句。它和你自己写select 查询基本表是一样的，所以在性能上没有区别，都是根据缓存来实现性能优化。
视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。
索引
定义：数据库索引实际上是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针的清单（序列）。
从索引的定义中可以得到：
1.索引是一个物理数据结构，也就是说，它是需要保持到物理磁盘上的，和普通的表数据一样要占用磁盘空间，也是存储在数据页上的。
2.索引是一个清单（或者说序列），由两部分组成：数据库表中的一列或多利的列值的集合、指向这些列值的数据页的逻辑指针。
可以把索引看作是一张只有两列的表，一列是普通数据表的列值（key-value），另一列是该列值的行对应的数据页的逻辑指针（Row-Pointer），这个逻辑指针可以理解为就是RID。需要说明的是，根据数据库产品和索引类型的不同，逻辑指针的结构也各不相同。如下图是索引的一个概览性描述，右侧是数据表页，左侧是对应的索引页。还有一点，从图上可以看到，表中的行之间是有指针相连的，即数据页中的每个记录除了存放数据，还会包含一个指针，指向其下一行记录。各记录形成一种链表结构。当然，这种结构并不是所有数据库系统都会采用的。
https://blog.csdn.net/qq_39552268/article/details/112034753
约束
DB2有五种约束：
- NOT NULL 约束是这样一种规则，它防止在表的一列或多列中输入空值。
- 唯一约束（也称为唯一键约束）是这样一种规则，它禁止表的一列或多列中出现重复值。唯一键和主键是受支持的唯一约束。例如，可对供应商表中的供应商标识定义唯一约束以确保不会对两个供应商指定同一供应商标识。
- 主键约束是与唯一约束具有相同属性的一列或列的组合。可使用主键和外键约束来定义表之间的关系。
- 外键约束（也称为引用约束或引用完整性约束）是关于一个或多个表中的一列或多列中的值的一种逻辑规则。例如，一组表共享关于公司的供应商的信息。供应商的名称有时可能会更改。可定义一个引用约束，声明表中的供应商的标识必须与供应商信息中的供应商标识相匹配。此约束会阻止可能导致丢失供应商信息的插入、更新或删除操作。
- （表）检查约束（也称为检查约束）对添加至特定表的数据设置限制。例如，表检查约束可确保每当在包含个人信息的表中添加或更新薪水数据时，职员的薪水级别至少为 $20000。
触发
触发器（Trigger）是一种根据数据库内容变化而自动执行的程序，它定义于一张表上，当该表中的数据发生变化的时候，如果满足了触发器中预定义的条件，就会引发触发器的执行，进而去做其他操作。
DB2 中的触发器分为前触发器、后触发器和替代触发器三种。(NO CASCADE BEFORE；AFTER；INSTEAD OF)
一个触发器涉及到两个动作，一个是能够激活触发条件的数据变化，即触发器执行的原因，另一个是触发器中指定的操作，即触发器执行的结果。
- 前触发器是先执行触发器中指定的操作，再执行引发触发器动作的操作，即先果后因。
- 后触发器正好相反，先执行激活触发器的那个操作，再执行触发器中指定的操作，即先因后果。
- 替代触发器是专门定义于视图之上的触发器，把原本在视图当中无法执行的操作（如对只读视图执行插入操作）替换为一系列具体可以对基本表执行的操作。
db2look
db2look是IBM Db2数据库提供的命令行工具，旨在生成数据库对象的定义（DDL）语句。它能够导出表、视图、索引等对象的定义，包括相关的约束、触发器等信息。以下是db2look的主要特点和用法：https://blog.csdn.net/szial/article/details/134951471
浙公网安备 33010602011771号