Heim >php教程 >php手册 >DB2视图、序列、约束、外键练习测试

DB2视图、序列、约束、外键练习测试

WBOY
WBOYOriginal
2016-06-13 08:50:14963Durchsuche

DB2视图、序列、约束、外键练习测试

简单视图:

db2 => create view myview1 as select * from t1 where empnoDB20000I SQL 命令成功完成。
db2 => select * from myview1


EMPNO NAME SEQNO
----------- ---------- -----------
10 wan qi 1
30 xu xin 3
50 bbbb 6


3 条记录已选择。


简单序列


db2 => create sequence myseq start with 1 increment by 1 cache 5
DB20000I SQL 命令成功完成。


db2 => insert into tank1 values(nextval for myseq,'asdf')
DB20000I SQL 命令成功完成。
db2 => select * from tank1


ID NAME
----------- ----------
2 asdf
3 asdf
4 asdf
5 asdf
6 asdf
7 asdf
8 asdf



简单约束实验

create table employee
(
ID integer not null constraint ID_pk primary key,
NAME varchar(9),
DEPT smallint constraint dept_ck1
check (dept between 10 and 100),
JOB char(5) constraint dept_ck2
check (JOB IN('Sales','Mgr','Clerk')),
HIREDATE date,
SALARY decimal(7,2),
constraint yearsal_ck
check (year(HIREDATE) > 1986 OR SALARY > 40000 )


)


[db2inst2@localhost ~]$ db2 "insert into employee values(1,'asdf',11, 'Sales','1788-02-10',40100)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee values(1,'asdf',13, 'Sales','1788-02-10',40100)"
DB21034E 该命令被当作 SQL
语句来处理,因为它是无效的“命令行处理器”命令。在 SQL
处理期间,它返回:
SQL0803N INSERT 语句、UPDATE 语句或由 DELETE
语句导致的外键更新中的一个或多个值无效,因为由 "1"
标识的主键、唯一约束或者唯一索引将表 "DB2INST2.EMPLOYEE"
的索引键限制为不能具有重复值。 SQLSTATE=23505
[db2inst2@localhost ~]$ db2 "insert into employee values(2,'asdf',101, 'Sales','1788-02-10',40100)"
DB21034E 该命令被当作 SQL
语句来处理,因为它是无效的“命令行处理器”命令。在 SQL
处理期间,它返回:
SQL0545N 因为行不满足检查约束
"DB2INST2.EMPLOYEE.DEPT_CK1",所以不允许所请求的操作。
SQLSTATE=23513
[db2inst2@localhost ~]$ db2 "insert into employee values(2,'asdf',99, 'Sales','1788-02-10',40100)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee values(3,'asdf',99, 'Kales','1788-02-10',40100)"
DB21034E 该命令被当作 SQL
语句来处理,因为它是无效的“命令行处理器”命令。在 SQL
处理期间,它返回:
SQL0545N 因为行不满足检查约束
"DB2INST2.EMPLOYEE.DEPT_CK2",所以不允许所请求的操作。
SQLSTATE=23513
[db2inst2@localhost ~]$ db2 "insert into employee values(3,'asdf',99, 'Sales','1788-02-10',40100)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee values(4,'asdf',98, 'Mgr','1788-02-10',40100)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee values(5,'asdf',98, 'Mgr','1987-02-10',40)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee values(6,'asdf',98, 'Mgr','1984-02-10',40)"
DB21034E 该命令被当作 SQL
语句来处理,因为它是无效的“命令行处理器”命令。在 SQL
处理期间,它返回:
SQL0545N 因为行不满足检查约束
"DB2INST2.EMPLOYEE.YEARSAL_CK",所以不允许所请求的操作。
SQLSTATE=23513
[db2inst2@localhost ~]$ db2 "insert into employee values(6,'asdf',98, 'Mgr','1981-02-10',41000)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee values(6,'asdf',98, 'Mgr','1981-02-10',41000)"



简单主外键测试

1、查看约束:

SELECT * FROM SYSCAT.TABCONST

2、新建父表:

create table shopper.salesboys(sid int not null, name
varchar(40) not null, salary double not null, constraint
pk_boy_id primary key (sid));


3、新建子表:

create table employee.salesboys(
sid int,
name varchar(30) not null,
phone int not null,
constraint fk_boy_id
foreign key(sid)
references shopper.salesboys (sid)
on delete restrict //限制删除
);

4、父表插入数据,,

[db2inst2@localhost ~]$ more inste2.sql
insert into shopper.salesboys values(100,'raju',20000.00),
(101,'kiran',15000.00),
(102,'radha',10000.00),
(103,'wali',20000.00),
(104,'rayan',15000.00)
;

5、子表插入数据

[db2inst2@localhost ~]$ more inste3.sql
insert into employee.salesboys values(100,'raju',98998976),
(101,'kiran',98911176),
(102,'radha',943245176),
(103,'wali',89857330),
(104,'rayan',89851130);

6、测试外建约束:


[db2inst2@localhost ~]$ db2 "insert into employee.salesboys values(103,'rayan',89851130)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee.salesboys values(109,'rayan',89851130)"
DB21034E 该命令被当作 SQL
语句来处理,因为它是无效的“命令行处理器”命令。在 SQL
处理期间,它返回:
SQL0530N FOREIGN KEY "EMPLOYEE.SALESBOYS.FK_BOY_ID"
的插入或更新值不等于父表的任何父键值。 SQLSTATE=23503
[db2inst2@localhost ~]$


7、删除外建测试

[db2inst2@localhost ~]$ db2 "ALTER TABLE EMPLOYEE.SALESBOYS DROP FOREIGN KEY FK_BOY_ID"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$ db2 "insert into employee.salesboys values(109,'rayan',89851130)"
DB20000I SQL 命令成功完成。
[db2inst2@localhost ~]$
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn