T-SQL语句训练

心已赠人 2022-05-15 04:45 42阅读 0赞

–第一步 创建数据库

create database Textdb

–第二步 创建数据表

use Textdb
    create table stuInfo(
    stuName varchar(8) not null,
    stuNo char(6) not null primary key,
    stuSex char(2) not null constraint CK_stusex CHECK(stuSex in ('男','女')),
    stuAge int not null,
    stuSeat int not null,
    stuAddress varchar(10) Default '地址不详'
    )

–第三步 添加数据

use Textdb
    insert into stuInfo values('张秋丽','S25301','男',18,1,'北京海淀')
    insert into stuInfo values('李文才','S25302','男',28,2,'地址不详')
    insert into stuInfo values('李斯文','S25303','女',22,3,'山东淄博')
    insert into stuInfo values('欧阳俊雄','S25304','男',28,4,'江苏南京')
    insert into stuInfo values('梅超风','S25318','女',99,5,'桃花岛')

–4、查找李才文的左右同桌

DECLARE @name varchar(8)  --学员姓名
    SET @name = '李文才'      --使用set赋值
    select * from stuInfo where stuName = @name --查找李才文的信息是否存在
    declare @seat int         --座位号
    select  @seat = stuSeat from stuInfo where stuName = @name  --使用select进行赋值
    select * from stuInfo where (stuSeat = @seat+1)or(stuSeat = @seat-1)
    go

–5、全局变量案例

print 'SQL Server的版本：'+@@VERSION
    print '服务器的名称：'+@@SERVERNAME
    --insert into stuInfo(stuName,stuNo,stuSex,stuAge)values('武松','s25328','男','23')
    print '当前错误号'+convert(varchar(5),@@ERROR)
    --print '刚才报名的学员，座位号：'+convert(varchar(5),@@IDENTITY)
    --UPDATE stuinfo SET stuAge = 85 where stuName = '李文才'
    --print '当前错误号：'+convert(varchar(5),@@ERROR)
    GO

–6、建立stuMarks

use Textdb
    
    create table stuMarks(
    ExamNo varchar(10) not null primary key,
    stuNo varchar(10) not null,
    writtenExam int not null,
    LabExam int not null
    )

insert into stuMarks values('s271811','s25303',80,58)
    insert into stuMarks values('s271813','s25302',50,90)
    insert into stuMarks values('s271816','s25301',77,82)
    insert into stuMarks values('s271818','s25328',45,65)

–7、计算笔试平均分，判断平均分是否大于七十

declare @avg float
    select @avg = avg(writtenExam) from stuMarks
    print '本班平均分'+convert(varchar(5),@avg)
    
    if(@avg>70)
     begin
      print '本班笔试成绩优秀，前三名成绩为：'
      select top 3 * from stuMarks order by writtenExam desc
     end
    else
     begin
      print '本班笔试成绩较差，后三名的成绩为：'
      select top 3 * from stuMarks order by writtenExam asc
     end

–8、对笔试进行提分，每次提两分

declare @n int
    while(1=1)--条件永远成立
     begin
      select @n=count(*)from stuMarks
       where writtenExam<60  --统计不及格人数
      if(@n>0)
       update stuMarks  --每人加2分
        set writtenExam = writtenExam+2
      else
       break  --退出循环
     end
    print '加分后的成绩如下：'

–9、用abcde来表示成绩等级

print 'abcde五级显示成绩如下：'
    select stuNo,
     成绩=case
           when writtenExam<60 then 'E'
           when writtenExam between 60 and 69 then 'D'
           when writtenExam between 70 and 79 then 'C'
           when writtenExam between 80 and 89 then 'B'
           else 'A'
          end
    from stuMarks

–10、查询学员成绩

go
    select 考号=ExamNo,学号=stuNo,笔试=writtenExam,机试=labExam,平均分=(writtenExam+labExam)/2,
     等级=case
       when (writtenExam+labExam)/2<60 then '不及格'
       when (writtenExam+labExam)/2 between 60 and 69 then '差'   
       when (writtenExam+labExam)/2 between 70 and 79 then '中'
       when (writtenExam+labExam)/2 between 80 and 89 then '良'   
       else '优'  
    end
    from stuMarks

–11、对机试成绩进行反复加分，直到平均分超过85为止

select * from stuMarks
    declare @labAvg int
    while(1=1)
     begin
      update stuMarks
      set labExam = 
      case
        when labExam<60 then labExam+5
        when labExam between 60 and 69 then labExam+3
        when labExam between 70 and 79 then labExam+2
        when labExam between 80 and 89 then labExam+1
        else labExam
      end
     select @labAvg = avg(labExam)from stuMarks
     if @labAvg>85
      break
     end
    select * from stuMarks

–12银行转账

use textdb
    
    create table bank(
    customerName char(10),
    currentMoney MONEY
    )
    Go
    
    alter table bank
    add constraint CK_currentMoney CHECK(currentMoney>=1)
    GO
    
    insert into bank values('张三',1000)
    insert into bank values('李四',1)
    
    update bank set currentMoney = currentMoney-1000 where customerName = '张三'
    update bank set currentMoney = currentMoney+1000 where customerName = '李四'
    Go
    select * from bank
    Go
    */
    
    begin transaction
    declare @errorSum int
    set @errorSum = 0
    update bank set currentMoney=currentMoney-1000 where customerName = '张三'
    set @errorSum = @errorSum+@@error
    update bank set currentMoney=currentMoney+1000 where customerName = '李四'
    set @errorSum = @errorSum+@@error
    if(@errorSum<>0)
     begin
      print '交易失败，回滚事务'
      rollback transaction
     end
    else
     begin
      print '交易成功，写入硬盘'
      commit transaction
     end
    go
    print '转帐后'
    select * from bank
    go