sql 2005 的实例化学习 sql2005 2

sql2005    二

/*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ if exists(select * from master.dbo.sysdatabases where name='myBiz') drop database myBiz--检测是否已存在此数据库 CREATE DATABASE myBiz on primary ( /*--主数据文件的具体描述--*/ name='myBiz_data',--主数据文件逻辑名 filename='d:/work/myBiz_date.mdf',--物理文件名  大家要是用的话  需要修改一下路径或者去掉系统可以默认的创建路径，但是你要找得到噢！ size=5mb,--初始大小 maxsize=100mb,--最大值 filegrowth=15%--增长率 ) log on ( name='myBiz',--主数据文件逻辑名 filename='d:/work/myBiz_date.ldf',--物理文件名 size=2mb,--初始大小 filegrowth=1mb--增长率 ) go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ use myBiz  --切换到当前myBiz数据库 go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ /*--创建myUser--表--*/ if exists(select * from master.dbo.sysobjects where name='myUser') drop table myUser--检测是否已存在此表 create table myUser ( userID int identity(1,1),--用户名ID，非空 userName varchar(20),--用户名 passWord varchar(10),--密码 userPrice money,--电子货币余额 userConsume int,--消费的电子货币 userType varchar(10)--用户类型 ) go alter table myUser   --确定是myUser表创建约束   一下雷同 add constraint pk_userID primary key(userID)--主键约束,防止重复的值 alter table myUser add constraint fk_userID foreign key(userID) references myUser--外键表,关系建在外键表里 alter table myUser add constraint df_userType default('普通用户') for userType go --多行插入数据 insert myUser(userName,passWord,userPrice,userConsume,userType) select '星','024750',1000,100,'普通用户' union select '辉','024751',1001,101,'普通用户' union select '月','024752',1002,102,'高级用户' union select '静','024753',1003,103,'高级用户' union select '华','024754',1004,104,'高级用户' union select '莹','024755',1005,105,'高级用户' go select * from myUser go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ /*--创建myOrder--表--*/ if exists(select * from master.dbo.sysobjects where name='myOrder') drop table myOrder--检测是否已存在此表 create table myOrder ( orderID int identity(1,1),--订单编号，非空 orderType varchar(10),--订单类型 orderPrice int,--订单金额 orderTime dateTime,--添加时间 userID int,--用户ID orderName varchar(10)--商品名称 ) go alter table myOrder add constraint fk_myOrder foreign key(userID) references myUser--外键表,关系建在外键表里 alter table myOrder add constraint df_orderTime default(getDate())for orderTime--默认约束 alter table myOrder add constraint df_orderType default('普通订单') for orderType go --多行插入数据 insert myOrder(orderType,orderPrice,userID,orderName) select '普通订单',05911,1,'卜卜星'union select '高级订单',05912,2,'可比克'union select '高级订单',05913,3,'旺旺'union select '普通订单',05914,4,'加力加'union select '高级订单',05915,5,'伙食' go select * from myOrder go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ /*--创建myConsume--表--*/ if exists(select * from master.dbo.sysobjects where name='myConsume') drop table myConsume--检测是否已存在此表 create table myConsume ( consumeID int identity(1,1),--消费记录ID consumePrice int,--消费金额 consumeTime dateTime,--消费时间 userID int,--用户ID productName varchar(10)--商品名称 ) go alter table myConsume add constraint fk_myConsume foreign key(userID) references myUser--外键表,关系建在外键表里 alter table myConsume add constraint df_consumeTime default(getdate())for consumeTime--默认约束 go --多行插入数据 insert myConsume(consumePrice,userID,productName) select 2000,1,'卜卜星'union select 2001,2,'可比克'union select 2002,3,'旺旺'union select 2003,4,'加力加'union select 2004,5,'伙食' go select * from myConsume go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ /*--创建myAdd--表--*/ if exists(select * from master.dbo.sysobjects where name='myAdd') drop table myAdd--检测是否已存在此表 create table myAdd ( addID int identity(1,1),--充值记录ID addType varchar(10),--充值类型 addCoin int,--充值金额 addTime dateTime,--充值日期 userID int,--用户ID ) go alter table myAdd add constraint fk_myAdd foreign key(userID) references myUser--外键表,关系建在外键表里 alter table myAdd add constraint df_addTime default(getdate())for addTime --默认约束 alter table myAdd add constraint df_addType default('普通充值')for addType --默认约束 go --多行插入数据 insert myAdd(addType,addCoin,userID) select '普通充值',100,1  union select '普通充值',150,2 union select '高级充值',100,3 union select '高级充值',250,4 union select '高级充值',300,5 go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ select * from myConsume--消费记录 select * from myUser--用户信息   select userType,sum(userPrice)  from myUser group by userType --sum  是取和 /*--  统计高级用户总余额和普通用户总余额差额 --*/ declare @num2 float declare  @num1 float select @num1=sum(userPrice) from myUser where userType='高级用户' select @num2=sum(userPrice) from myUser where userType='普通用户' print '相差'+convert(varchar,@num1-@num2) select @num1-@num2   select * from myConsume--消费记录 /*--  根据用户类型输出不同形式的信息 --*/ select userName as 用户名,用户等级=    case     when userType='高级用户' then '*'     when userType='普通用户' then '**'     else '....' end from myUser   go /*--  如果消费金额不足2010，每次给消费金额+2，直到所有消费金额都大于2010 --*/ declare @n int while(1=1) begin  select @n=count(*) from myConsume where consumePrice<2010  if(@n>0)   update myConsume set consumePrice=consumePrice+2  else   break end print '加钱后结果如下：' select * from myConsume go /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ select userName from myUser where userId=(select userId from myOrder where userId=1) /*--查询所有用户的订单记录--*/ select * from myUser inner join myOrder on myUser.userid=myOrder.userid /*--查询所有订单的用户记录--*/ select * from myOrder o left join myUser u on o.userid=u.userid select top 2 percent userName as 用户姓名  from myUser--百分比查询 select top 2 * from myUser where userID not in(select top 2 userId from myUser)--查询3，4条 select count(*) from myUser where userType<>'高级用户'--不是高级用户 select * from myAdd where addCoin=(select min(addCoin) from myAdd)--充值最低的用户 select * from myOrder /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/ /*--判断商品，输出不同结果--*/ go declare @foodName varchar(20) select @foodName='可比克' if @foodName='旺旺'  print 'OK' else    begin   if @foodName='可比克'    select * from myUser where userid=(select userId from myOrder where orderName=@foodName)   else    print 'fail2'   end go select '高级充值' as 充值类型, (select sum(addCoin) from myAdd where addType='高级充值') as 充值金额 select '普通充值' as 充值类型, (select sum(addCoin) from myAdd where addType='普通充值') as 充值金额 select * from myAdd /*--根据充值类型分组查询充值总金额，过滤掉充值小于等于120的充值记录，并且过滤掉总金额小于200的充值类型--*/ select 充值类型=addType,sum(addCoin) from myAdd where addCoin>120  group by addType having sum(addCoin)>200 select * from myAdd,myUser where myAdd.userId=myUser.userid   set nocount on select distinct addType from myAdd /*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@*/