sql.rar_SQL 增删 改查

use BillingSys exec sp_helpdb use BillingSys exec sp_spaceused use BillingSys exec sp_dboption --自动收缩 alter database BillingSys set auto_shrink on/off --手动收缩数据库 dbcc shrinkdatabase(0,20) --分离数据库 use master go sp_detach_db @dbname='BillingSys' --附加数据库 use master go sp_attach_db @dbname='BillingSys',@filename='d:\BillingSys\Billings.mdf' use master go sp_attach_db 'BillingSys','d:\BillingSys\Billings.mdf' alter database cmdb add file( name=data1, filename='d:\data1.ndf') to filegroup pf_cms alter database cmdb remove file data1 drop database cmdb create database BillingSys on primary (name=BillingSys, filename='d:\BillingSys\Billings.mdf', size=3, filegrowth=1), filegroup fp_bs (name=bs_data, filename='d:\BillingSys\bs_data.ndf', size=5, filegrowth=1) log on (name=BillingSys_log, filename='d:\BillingSys\BillingSys_log.ldf', size=1, maxsize=20, filegrowth=10% ) use BillingSys create database BillingSys on primary (name=BillingSys, filename='d:\Billings.mdf', size=3, filegrowth=1), filegroup fp_bs (name=bs_data, filename='d:\bs_data.ndf', size=5, filegrowth=1) log on (name=BillingSys_log, filename='d:\BillingSys_log.ldf', size=1, maxsize=20, filegrowth=10% ) use BillingSys go --联系人信息表 create table Relationer (Rid int, Rname varchar(16) not null, RidentifyNo char(18), Rduty varchar(12), Raddr varchar(60), Rtel char(11), Rzipcode char(6), Remail varchar(60), Rfax char(8) constraint pk_rel primary key(Rid) ) go --客户信息表 create table customer (Cid int, Cname varchar(16), Cpsd char(6), Rid int, Cregistdate datetime, Ctype char(6) default('普通'), Cstatus bit, Caccountbalance numeric(6,2) constraint pk_cus primary key(Cid) constraint fk_cus_rel foreign key(Rid) references Relationer(Rid) ) go --产品信息表 create table Eproduct (ENo char(11), Ename varchar(30) not null, Cid int, Ebdate datetime, Eaddr varchar(60), Euniprice numeric(6,2) constraint pk_epro primary key(ENo), constraint fk_epro_cus foreign key(Cid) references Customer(Cid) ) go --账单信息表 create table Bills (Bid bigint, Cid int, ENo char(11), Bdate datetime, Bfixedcharge numeric(5,2), Bcommunications numeric(7,2) constraint pk_bills primary key(Bid) constraint fk_bills_cus foreign key(Cid) references Customer(Cid), constraint fk_bills_epro foreign key(ENo) references Eproduct(ENo) ) go --账单明细表 create table Details (Did bigint, Bid bigint, ENo char(11), DcallNo char(11), Ddate datetime, Dbegintime datetime, Dduration smallint, Dcost numeric(6,2) constraint pk_det primary key(Did) constraint fk_det_bills foreign key(Bid) references Bills(Bid), constraint fk_det_epro foreign key(ENo) references Eproduct(ENo) ) go --支付信息表 create table Payment (Pid int, Cid int, ENo char(11), Pdate datetime, Pway char(10), PaccountNo varchar(20) constraint pk_pay primary key(Pid) constraint fk_pay_cus foreign key(Cid) references Customer(Cid), constraint fk_pay_epro foreign key(ENo) references Eproduct(ENo) ) go --附加服务项目表 create table AdditionalServices (ASid smallint, ASitem char(20) not null, ASprice numeric(5,2) constraint pk_AS primary key(ASid) ) go --开通附加服务表 create table StartAdditionalServices (ENo char(11), ASid smallint, Stime datetime constraint pk_SAS primary key(ENo,ASid), constraint fk_SAS_AS foreign key(ASid) references AdditionalServices(ASid), constraint fk_SAS_epro foreign key(ENo) references Eproduct(ENo) ) go --1.删除数据表Relationer中的列Rid； alter table relationer drop constraint pk_relationer go alter table relationer drop column Rid go --2.为数据表Relationer添加两个新列，新列名称和数据类型分别为Rid，char(10),主键;Rsex,char(2); alter table relationer add column Rid char(10),Rsex char(2) go alter table relationer add constraint pk_relationer primary key(Rid) go --3.修改Customer表中的名为Cname列的数据类型，将其原数据长度从16改为20； alter table Customer alter column Cname varchar(20) go --4.将数据表Details中列Ddate的原数据类型Datetime改为Date，列Dbegintime的原数据类型Datetime改为time； alter table Details alter column ddate date go alter table Details alter column dbegintime time go --5.创建数据表Test，属性及数据类型分别如下：Tid，标识列，主键；Tname，varchar(20)，不为空；Tpic，image，该数据存放在次数据文件上；Ttime，datetime,默认为系统日期时间； create table Test (Tid int identity, Tname varchar(20) not null, Tpic image, Ttime datetime default(getdate()) constraint pk_test primary key(Tid)) textimage_on fp_bs go --6.删除上一步骤创建的数据表 drop table Test go --7.根据下图为相应数据表添加相应数据（用三种方式） delete from Customer go alter table customer alter column rid int null go insert into Customer(CID,CName,CRegistrationDate,CType,CStatus,CAccountBalace) values('20120415','李丽君','2012-1-12','普通',1,200) go insert into Customer(CID,CName,CRegistrationDate,CType,CStatus,CAccountBalace,RID,CPassword) values('20120315','福州饭店','2012-3-15','商业',1,2000,'','') go insert into Customer values('20120808','','赖大金','','2012-8-8','流动',1,50) go select * from Customer go --8.将联系人编号为20120315的客户编号改为3104175，名称改为福州大饭店； update Customer set CID='3104175',CName='福州大饭店' where CID='20120315' go --9.删除客户编号为20120145的客户记录； delete from Customer where CID='20120415' go --10.删除客户表Customer所有数据 delete from Customer go --11.分离数据库BillingSys use master go exec sp_detach_db @dbname='billingsys' go exec sp_attach_db @dbname='billingsys',@filename1='d:\Billings.mdf' go --1.根据产品信息表中产品号码的单价，查询客户购买的产品号码的消费金额 select d.eno,dcost=sum(dduration * euniprice) from eproduct e join details d on e.eno=d.eno group by d.ENo go select * from details --2.检索开通了天气预报的客户信息 select * from customer where Cid in( select Cid from Eproduct where ENo in( select ENo from StartAdditionalService where ASid=( select ASid from AdditionalService where ASitem='天气预报'))) go --3.查询购买了ADSL拨号产品的客户信息 select * from customer where Cid in( select Cid from Eproduct where Ename='ADSL拨号') go --4.查询开通了移动秘书的产品号码（用相关子查询实现） select distinct eno from startAdditionalService s where exists(select * from AdditionalService a where a.ASid=s.ASid and ASitem='移动秘书') go select * from StartAdditionalService --5.将所有中国电信移动电话统一开通梦网短信，并将开通时间设置为系统当前时间 insert into StartAdditionalService select distinct e.ENo,(select ASid from AdditionalService where ASitem='梦网短信'),GETDATE() from Eproduct e join StartAdditionalService s on e.ENo=s.ENo join AdditionalService a on a.ASid=s.ASid where Ename='中国电信移动电话' go insert into StartAdditionalService select distinct ENo,(select ASid from AdditionalService where ASitem='梦网短信'),GETDATE() from Eproduct where Ename='中国电信移动电话' go select * from eproduct where Ename='中国电信移动电话' delete from StartAdditionalService where ASid=6 go select * from StartAdditionalService go --6.查询联系人联系地址或产品安装地址是广州市的相关信息。 select rid,raddr from Relationer where Raddr like '广州市%' union select cid,eaddr from Eproduct where Eaddr like '广州市%' go select * from Eproduct select * from AdditionalService select * from StartAdditionalService --1.查询联系人信息表中联系人编号、联系人姓名、地址和电话，并将结果显示标题改为中文显示； select rid 联系人编号,rname 联系人姓名,raddr 地址,rtel 电话 from Relationer go --2.统计联系人的个数，并改变列标题显示，如图所示： select COUNT(rid) as 联系人个数 from Relationer go --3. 查询明细表中通话时间最长的三个客户的通话记录 select top 3 with ties * from Details order by Dduration desc go --4. �