DB2 数据类型

数据类型：

数字：整数，小数，浮点数

字符串：字符字符串（单字节字符串，多字节字符串），二进制字符串

日期时间：日期，时间，时间戳

XML：

 

例如：

创建表：

create table clients(
id int primary key not null,
name varchar(50),
status varchar(10),
contactinfo xml
);

插入数据：

insert into clients values (3227, 'Ella Kimpton', 'Gold',
'<Client>
<Address>
<street>5401 Julio Ave.</street>
<city>San Jose</city>
<state>CA</state>
<zip>95116</zip>
</Address>
<phone>
<work>4084630000</work>
<home>4081111111</home>
<cell>4082222222</cell>
</phone>
<fax>4087776666</fax>
<email>love2shop@yahoo.com</email>
</Client>'
);


insert into clients values (8877, 'Chris Bontempo', 'Gold',
'<Client>
<Address>
<street>1204 Meridian Ave.</street>
<apt>4A</apt>
<city>San Jose</city>
<state>CA</state>
<zip>95124</zip>
</Address>
<phone>
<work>4084440000</work>
</phone>
<fax>4085555555</fax>
</Client>'
);


insert into clients values (9077, 'Lisa Hansen', 'Silver',
'<Client>
<Address>
<street>9407 Los Gatos Blvd.</street>
<city>Los Gatos</city>
<state>CA</state>
<zip>95032</zip>
</Address>
<phone>
<home>4083332222</home>
</phone>
</Client>'
);


insert into clients values (9177, 'Rita Gomez', 'Standard',
'<Client>
<Address>
<street>501 N. First St.</street>
<city>Campbell</city>
<state>CA</state>
<zip>95041</zip>
</Address>
<phone>
<home>4081221331</home>
<cell>4087799881</cell>
</phone>
<email>golfer12@yahoo.com</email>
</Client>'
);


insert into clients values (5681, 'Paula Lipenski', 'Standard',
'<Client>
<Address>
<street>1912 Koch Lane</street>
<city>San Jose</city>
<state>CA</state>
<zip>95125</zip>
</Address>
<phone>
<cell>4085430091</cell>
</phone>
<email>beatlesfan36@hotmail.com</email>
<email>lennonfan36@hotmail.com</email>
</Client>'
);


insert into clients values (4309, 'Tina Wang', 'Standard',
'<Client>
<Address>
<street>4209 El Camino Real</street>
<city>Mountain View</city>
<state>CA</state>
<zip>95033</zip>
</Address>
<phone>
<home>6503310091</home>
</phone>
</Client>'
);

 

查询：

1. 进入db2命令行

2. 查询

a. 全部记录：xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')

 

b. 查询FAX相关信息：

xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/fax return $y
或者 xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/fax

c. 查询FAX相关信息，只返回值：xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/fax/text()

 

d. 带where条件查询：

xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/Address where $y/zip="95116" return $y
或者 xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client/Address[zip="95116"]

 

e. 带where多个条件查询：

xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client where $y/Address/zip="95032" or $y/Address/city="San Jose" return $y/email

或者 xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client[Address/zip="95032" or Address/city="San Jose"]/email;

f. 带where条件查询只输出第一个结果：

xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client where $y/Address/zip="95032" or $y/Address/city="San Jose"
return $y/email[1]
或者 xquery db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client[Address/zip="95032" or Address/city="San Jose"]/email[1];

g. 带复杂查询逻辑：

xquery for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client return (if ($y/email) then $y/email[1] else if ($y/phone/home) then <homePhone>{$y/phone/home/text()}</homePhone> else $y/Address)

说明：email有显示第一个email；没有email家庭电话是否有，有显示家庭电话；没有家庭电话显示地址

 

1. 支持唯一索引。

2. 没有XML extender时存放方式Hierarchically

3. 支持SQL,xquery 查询，直接查询部分内容，不用全文档扫描

用户定义类型：

单值类型

结构化类型

引用类型