8db.txt

the original code of the book the principle and practice in sql server 2005

declare @str1 char(10),@str2 varchar(50),@x1 int,@x2 real,@time1 datetime
set @str1='good'
set @str2='hello how are you!'
set @x1=10
set @x2=1.789
set @time1=1984/5/4

print @str1
print @str2
print @x1
print @x2
print @time1


declare @str1 char(10),@str2 varchar(50),@x1 int,@x2 real,@time1 datetime
select @str1='good',@str2='hello how are you' ,@x1=12,@x2=0.432,@time1=1984-05-04
select @str1,@str2,@x1,@x2,@time1

/*declare @str1 char(10),@str2 varchar(50),@x1 int,@x2 real,@time1 datetime
select @str1='good',@str2='hello how are you' ,@x1=12,@x2=0.432,@time1=1984-05-04
select @str1,@str2,@x1,@x2,@time1*/


declare @yz real,@w int
set @w=1139
if @w<=100
  set @yz=@w*0.12
else
 set @yz=100*0.12+(@w-100)*0.05

print '邮件得重量是：'+cast(@w as varchar(20))+'克'
print '邮费是:'+cast(@yz as varchar(20))+'元'



declare @hyuser varchar(50),@hypwd varchar(50),@msg varchar(50)
select @hyuser='hystu2',@hypwd='222'
if @hyuser='hystu1'
  begin
    if @hypwd='111'
     set @msg='用户名与密码正确,成功登录'
    else
     set @msg='密码不正确，请重新输入！'
    
  end
else if @hyuser='hystu2'
  begin
    if @hypwd='222'
     set @msg='用户名与密码正确,成功登录'
    else
     set @msg='密码不正确，请重新输入！'
    
  end
else if @hyuser='hystu3'
  begin
    if @hypwd='333'
     set @msg='用户名与密码正确,成功登录'
    else
     set @msg='密码不正确，请重新输入！'
    
  end
else
 set @msg='密码不正确，请重新输入！'

print @msg

 /*创建数据库*/
--create database hystu
--on
--(
--  name='user_data',
--  filename='D:\sql\user_data.mdf',
--  size=4,
--  maxsize=10,
--  filegrowth=10%
--
--)
--log on
--(
--  name='user_log',
--  filename='D:\sql\user_log.mdf'
-- 
-- 
--
--)
/*在数据库中创建数据表*/
create table hyuser1
(
  hyid int identity(1,1) primary key,
  hyname varchar(50) unique,
  hypsd varchar(50) 
)

use hystu
insert into hyuser1(hyname,hypwd) values('stu1','111111')
insert into hyuser1(hyname,hypwd) values('stu2','222222')
insert into hyuser1(hyname,hypwd) values('stu3','333333')


--use hystu
--create table hyuser1
--(
--  hyid int identity(1,1) primary key,
--  hyname varchar(50) unique,
--  hypsd varchar(50) 
--)
--insert into hyuser1(hyname,hypsd) values('stu1','111111')
--insert into hyuser1(hyname,hypsd) values('stu2','222222')
--insert into hyuser1(hyname,hypsd) values('stu3','333333')
--select *
--from hyuser1

declare @hyuser varchar(50),@hypsd varchar(50),@msg varchar(50),@num int,@num1 int
select @hyuser='stu3',@hypsd='333333'
select @num=count(*) from hyuser1 where hyname=@hyuser
if @num>=1
 begin
  select @num1=count(*) from hyuser1 where hyname=@hyuser and hypsd=@hypsd
  if @num1>=1
   set @msg='用户名正确，成功登录!'
   else
   set @msg='密码不正确，新重新输入！'
 end
 else
set @msg='用户名不正确，请重新输入！'
print @msg

--use hystu
--create table hyuser1
--(
--  hyid int identity(1,1) primary key,
--  hyname varchar(50) unique,
--  hypsd varchar(50) 
--)
--insert into hyuser1(hyname,hypsd) values('stu1','111111')
--insert into hyuser1(hyname,hypsd) values('stu2','222222')
--insert into hyuser1(hyname,hypsd) values('stu3','333333')
--select *
--from hyuser1

declare @hyuser varchar(50),@hypsd varchar(50),@msg varchar(50)
select @hyuser='stu1',@hypsd='111111'
--select @num=count(*) from hyuser1 where hyname=@hyuser
if exists(select * from hyuser1 where hyname=@hyuser)

--if @num>=1
 begin
--  select @num1=count(*) from hyuser1 where hyname=@hyuser and hypsd=@hypsd
  
if exists(select * from hyuser1 where hyname=@hyuser and hypsd=@hypsd)
   set @msg='用户名正确，成功登录!'
   else
   set @msg='密码不正确，新重新输入！'
 end
 else
set @msg='用户名不正确，请重新输入！'
print @msg



declare @cj float,@str varchar(60)
set @cj=70
set @str=
case
  when @cj>100 or @cj<0 then '你输入的成绩不正确，成绩应该在0~100之间'
  when @cj>=60 and @cj<70 then '及格'
  when @cj>=70 and @cj<80 then '中等'
  when @cj>=80 and @cj<90 then '优良'
  when @cj>=90 and @cj<=100 then '优秀'
  else
  '不及格' 
end

print '该学生的成绩评语是：'+@str


declare @x int ,@sum int 
select @x=0,@sum=0
while @x<=1654
 begin
  set @sum=@sum+@x
  set @x=@x+1
 end
set @x=@x-1
print '从1加到'+cast(@x as varchar(50))+'之间是：'+cast(@sum as varchar(50))

declare @x int, @sum int
select @x=0,@sum=0
while @x<=10
 begin
   set @x=@x+1
   set @sum=@sum+@x
  if @sum>30
  break
 end
waitfor delay '00:00:03'
print '等三秒后输出'
print '最后的结果是：'+cast(@sum as varchar(50))