中国石油大学华东数据库实验五 龚安.doc

实验五 SQL练习3 (1)增加备注 alter table shujubiao add 备注 char(50); select * from shujubiao; （2） 增加主码约束： alter table shujubiao add primary key(shigongdanwei,nianyue,jiesuanjine); //不能在允许空的列上增加主键 再次执行实验四中实验内容3.2的操作： 先删除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 xiangmubiao group 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 cailiaofeibiao alter column zuoyexiangmuhao varchar(10) not null; ter table cailiaofeibiao alter column wuma varchar(10) not null; alter table xiangmubiao alter column zuoyexiangmuhao varchar(10) not null; alter table shigongdanweibiao alter 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)