数据库学习心得day6

1、Case then

select name as '姓名',

case level  --level和when后面的值，在数据类型兼容的情况下，会自动进行转换       

    when 1 then '骨灰' --then 后面的值的数据类型必须一致

    when '2' then '大侠'

    when '3' then '菜鸟'

end as '级别'

from user1

(当level等于1的时候在级别列输出骨灰，level等于2的时候在级别列输出大侠……)

select studentid as '学生编号',

case --区间判断，不允许加要判断的值

    when english >= 100 then '超级厉害'

    when english >=90 and english <100 then '厉害'

    when english >=80 and english <90 then '还可'

    when english >=70 and english <80 then '努力'

    when english >=60 and english <70 then '快挨打了'

    else '挨打'

end as '考试结果'

from score

select pName as '姓名',

case --like模糊的判断条件，属于区间判断

    when pHomeNum like '7%' then '北京号码'

    when pHomeNum like '8%' then '上海号码'

    when pHomeNum like '9%' then '广州号码'

    when pHomeNum like '3%' then '深圳号码'

    else '未知来电'

end as '家庭电话号码'

from phoneNums

2、子查询

将一个查询语句做为一个结果集供其他SQL语句使用，就像使用晋通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。SELECT * FROM(SELECT * FROM student where sAae<30) as t

--查询前3条数据   第一页            当前的页数，每页有几条

select top 3 * from student

where sid not in (select top (3*(1-1)) sid from student )

--查询第四条到第六条数据  第二页

select top 3 * from student

where sid not in (select top (3*(2-1)) sid from student )

--查询第七条到第九条的数据 第三页

select top 3 * from student

where sid not in (select top (3*(3-1)) sid from student )

--查询第四页的数据

select top 3 * from student

where sid not in (select top (3*(4-1)) sid from student )

select top 3 * from student

where sid not in (select top (@count*(@Page-1)) sid from student )

3、表连接

inner join 内连接（连接后形成新表）

left join 左连接（以左表为基础进行连接）

right join 右连接（以右表为基础进行连接）

--查询学生姓名、年龄、班级及成绩

select sname,sage,cname,english from student inner join class on student.sclassid = class.cid

inner join score on student.sid = score.studentid

4、变量

--把所有未及格的人的成绩都加及格

declare @failCount int

select @failCount = count(*) from score where english <130

while(@failCount>0)

    begin

       update score set english = english + 2 where English<60

       select @failCount = count(*) from score where english <130

    end

    select * from score

5、事务

begin transaction

declare @error int

set @error = 0

update bank set balance=balance-1000 where cid='0001'

set @error = @error + @@error

update bank set balance=balance + 1000 where cid='0002'

set @error = @error + @@error

if @error != 0

    rollback transaction --回滚

else

    commit transaction

go

select * from bank