中国石油大学华东数据库实验五--龚安.doc
6页
实验五 SQL练习31. (1)增加备注 alter table shujubiao add 备注 char(50); select * from shujubiao;（2） 增加主码约束：alter table shujubiao add primary key(shigongdanwei,nianyue,jiesuanjine);//不能在允许空的列上增加主键：先删除shujubiao中所有数据：TRUNCATE TABLE shujubiao;//删除表中所有行(TRUNCATE TABLE)select * from shujubiao;insert into shujubiao(shigongdanwei,nianyue,jiesuanjine)(select shigongdanwei,year(jiesuanriqi)*100+month(jiesuanriqi),sum(jiesuanjine)from xiangmubiaogroup by shigongdanwei,year(jiesuanriqi)*100+month(jiesuanriqi));（3）删除上述数据表中的数据：（delect shujubiao;或TRUNCATE TABLE shujubiao;都可以）delete from shujubiao where shigongdanwei='作业公司作业二队';delete from shujubiao where shigongdanwei='作业公司作业三队';delete from shujubiao where shigongdanwei='作业公司作业一队';select * from shujubiao;删除该数据表： drop table shujubiao;对这两个操作进行比较：区别delect TRUNCATE TABLE drop table drop table Table_name; --------------------完全删除表 delete Table_name;-----------------删除表内数据，保留表结构 truncate table Table_name;-----------------删除表内数据，保留表结构 delete from Table_name where 条件；-------------------------------删除相关数据2. (1)alter table cailiaofeibiaoalter column zuoyexiangmuhao varchar(10) not null;ter table cailiaofeibiaoalter column wuma varchar(10) not null;alter table xiangmubiaoalter column zuoyexiangmuhao varchar(10) not null;alter table shigongdanweibiaoalter column shigongdanweimingcheng varchar(40) not null;alter table wumabiao--alter column wuma varchar(10) not null;增加主码： alter table cailiaofeibiao add primary key(zuoyexiangmuhao,wuma); alter table danweidaimabiao add primary key(danweidaima); alter table shigongdanweibiao add primary key(shigongdanweimingcheng); alter table wumabiao add primary key(wuma); alter table xiangmubiao add primary key(zuoyexiangmuhao); alter table youshuijingbiao add primary key(jinghao);检测： insert into cailiaofeibiao values('zy2011001','wm004',100,10) insert into cailiaofeibiao values('zy2011002',NULL,200,10)（2）增加相应的参照完整性约束:alter table cailiaofeibiaoadd constraint clfb_fk1 foreign key (zuoyexiangmuhao) references xiangmubiao(zuoyexiangmuhao);alter table cailiaofeibiaoadd constraint clfb_fk2 foreign key (wuma) references wumabiao(wuma);alter table xiangmubiaoadd constraint xmb_fk1 foreign key(yusuandanwei) references danweidaimabiao(danweidaima);alter table xiangmubiaoadd constraint xmb_fk2 foreign key(jinghao) references youshuijingbiao(jinghao);alter table xiangmubiaoadd constraint xmb_fk3 foreign key(shigongdanwei) references shigongdanweibiao(shigongdanweimingcheng);alter table youshuijingbiaoadd constraint ysjb_fk1 foreign key(danweidaima) references danweidaimabiao(danweidaima);检测： begin transaction insert into youshuijingbiao values('y007','油井','112203002'); insert into cailiaofeibiao(zuoyexiangmuhao,wuma,xiaohaoshuliang,danjia)values('zy2011007','wm006',100,10); update xiangmubiao set shigongdanwei='作业公司五队'where zuoyexiangmuhao='zy2011001'; delete from danweidaimabiao where danweidaima='112202002'; update wumabiao set wuma='wm04' where wuma='wm004'; rollback;（3）① 单位代码表的单位名称不能取空值、且取值唯一。
alter table danweidaimabiao alter column danweimingcheng varchar(40) not null; alter table danweidaimabiaoadd constraint dwdmb_unique unique(danweimingcheng);② 油水井表的井别只允许取“油井”或“水井”，单位代码不能取空值 alter table youshuijingbiaoadd constraint ysjb_check check(jingbie in('油井','水井')); alter table youshuijingbiaoalter column jinghao varchar(10) not null;③ 物码表的名称规格不能取空值、且取值唯一，计量单位不能取空值 alter table wumabiao alter column mingchengguige varchar(10) not null; alter table wumabiao add constraint wmb_unique unique(mingchengguige); alter table wumabiao alter column jiliangdanwei varchar(10) not null;④ 材料费表的消耗数量不能取空值，单价不能取空值。
alter table cailiaofeibiao alter column xiaohaoshuliang float not null; alter table cailiaofeibiao alter column danjia money not null;⑤ 对作业项目表根据实际应用的要求定义适当的用户定义的完整性约束条件 alter table xiangmubiao alter column yusuanren varchar(20) not null;3.（1）begin transactiongocreate view xmb_clfb asselect xiangmubiao.*,wuma,xiaohaoshuliang,danjia from xiangmubiao,cailiaofeibiaowhere xiangmubiao.zuoyexiangmuhao=cailiaofeibiao.zuoyexiangmuhao（2）select zuoyexiangmuhao,wuma from xmb_clfb where wuma='wm002'; select zuoyexiangmuhao,xiaohaoshuliang from xmb_clfb where zuoyexiangmuhao='zy2011001' and wuma='wm001';（3）gocreate view yusuanzhuangtaias select zuoyexiangmuhao,yusuandanwei,jinghao,yusuanjine,yusuanren,yusuanriqi from xiangmubiaoinsert into yusuanzhuangtaivalues('zy2011008','112202002','y005',10000,'张三','2011-07-02');/*插入的记录实际上被插入到了工程项目表中*/rollback;(有错）。
