一些简单的查询

小鱼儿 2022-06-16 06:44 296阅读 0赞

![复制代码][copycode.gif]

四．高级查询
    
      1. 子查询和相关子查询
    
     示例：
    
       Select studentno,studentname,sex,borndate,address  from student
    
       Where borndate>
    
    (
    
    Select borndate from student where studentname=’李斯文’
    
    )
    
     
    
      采用变量实现:
    
       Declare @birthday datetime
    
       Seelct @birthday=borndate from student
    
          Where studentname=’李斯文’
    
       Select studentno,studentname,sex,borndate,address  from student
    
          Where borndate>@birthday
    
       Go
    
     
    
      语法：
    
      Select ...... From 表1 where 列1>(子查询)
    
      子查询必须放置在一对圆括号内，在列1后面除了‘>’,还可以使用其他运算符号。
    
     
    
     注意：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。
    
     
    
     问题:查询Java课程至少一次考试刚好等于60分的学生
    
     
    
    方法一：
    
      Select studentname from student stu
    
    Inner join result r on stu.studentno=r.studentno
    
    Inner join subject sub on r.subjectno=sub.subjectno
    
      Where studentresult=60 and subjectname=’java’
    
      Go
    
     
    
    方法二：
    
      Select studentname from student
    
       Where studentno=
    
    (
    
    Select studentno from result
    
    Inner join subject on result.subjectno=subject.subjectno
    
    Where studentresult=60 and subjectname=’java’
    
    )
    
      Go
    
     
    
    注意：一般来说，表连接都可以用子查询替代，但是反过来可不一定，有的子查询不能用表连接来替代。子查询比较灵活，方便，形式多样，适合于作为查询的筛选条件，而表连接更适合于查看多表的数据。
    
     
    
    2.In 和 not in 子查询
    
    使用in关键字可以使父查询匹配子查询返回的多个单列值
    
    示例：
    
      /*---采用in子查询---*/
    
     Select studentname from student where studentno in
    
      (
    
       Select studentno from result
    
       Where subjectno=
    
    (
    
    Select subjectno from subject
    
    Where subjectname=’java’
    
    )
    
    And studentresult=60
    
     
    
      )
    
      Go
    
     
    
     问题：查询参加Java课程最近一次考试的在读学生名单
    
      （1）获得java 课程的课程编号
    
      （2）根据课程编号查询最近一次Java考试的考试日期
    
      （3）根据课程编号和最近的考试日期查询学生信息
    
      
    
    Select studentno,studentname from student
    
     Where studentno in
    
      (
    
      Select studentno from result
    
       Where subjectno=
    
      (
    
         Select subjectno from subject
    
          Where subjectname=’java’
    
     
    
         ) and examdate=
    
           (
    
             Selcct max(examdate) from result
    
              Where subjectno=
    
               (
    
                Select subjectno from subject
    
                Where subjectname=’java’
    
               )
    
           )
    
      )
    
     
    
    Not in 子查询：
    
    示例：
    
    /*---采用not in子查询，查看未参加考试的在读的学生名单---*/
    
    Select studentno,studentname from student
    
     Where studentno not in
    
      (
    
      Select studentno from result
    
       Where subjectno=
    
       (
    
          Select subjectno from subject
    
          Where subjectname=’java’
    
     
    
         ) and examdate=
    
           (
    
             Selcct max(examdate) from result
    
              Where subjectno=
    
               (
    
                Select subjectno from subject
    
                Where subjectname=’java’
    
               )
    
           )
    
      )
    
    3.Exists 和 not exists 子查询
    
     Exists 关键字能够检测数据是否存在
    
     
    
    --查询年级名称是否存在
    if exists
    (
    select gradeid from Grade
    where GradeName='S1'
    
     
    
    )
    begin 
     update Grade set GradeName='S2'
     where  GradeId in
     (
      select gradeid from Grade
      where GradeName='S1'
     )
    end
    else
    begin
     print '没有这个班级'
    end
    
     --查询学生姓名，年级名称，课程名称，考试日期，考试成绩
    
     select Studentname,gradename,subjectname,examdate,StudentResult
     from Grade
     join Subject on Grade.GradeId=Subject.GradeId
     join Result on subject.SubjectId=result.SubjectId
     join Student on result.StudentNo=student.StudentNo
    where student.StudentNo in
    (
    select studentno from Student
    where Result.StudentNo=student.StudentNo
    
    )
    and
    subject.SubjectId in
    (
    select subjectid from Subject
    where ExamDate in
    (
    select MAX(examdate) from Result
    where result.SubjectId=Subject.SubjectId
    
    )
    )
    group by studentname,gradename,subjectname,examdate,StudentResult
    
    子查询注意事项：
    
      （1） 子查询语句可以嵌套在SQL语句的任何表达式出现的位置
    
      （2）在子查询的select子句中不能出现text,ntext或image数据类型的列
    
      （3）只出现在子查询中而没有出现在父查询中的表不能包含在输出列
    
     合并多个表中的数据的3种方法是：联合，子查询和表连接
    
    4.分页
    
      分页的目的是：为了加快网站对数据的查询速度
    
    --分页1
    --跳过三条取三条
    --
       select top 3 * from student
       where studentno not in
       (
        select top 3 studentno from student
       
       )
    
     
    
    ---分页2
      --在内存中增加一列  查询列的表  然后条件
      --1.from 表名  2.where 条件  3.查询结果
      
    select * from
    (
    select *,ROW_NUMBER() over(order by studentno) as myid 
    from student
    ) as temp
    
    where myid between 4 and 6
    
    五.
    
    ---查询6~10条的租房信息
    select top 5 * from dbo.hos_house 
    where HMID not in
    (
    select TOP 5 HMID from dbo.hos_house
    )
    
    
    --查询张三发布的所有出租房信息
    
    select dname as 区县,sname as 街道,htname as 户型,PRICE  as 价格,TOPIC as 标题,CONTENTS  as  描述,htime as 时间,copy as 备注 
    from dbo.hos_house
    join dbo.hos_street on dbo.hos_house.SID=dbo.hos_street.SID
    join dbo.hos_type on dbo.hos_house.HTID=dbo.hos_type.HTID
    join dbo.hos_district on dbo.hos_street.SDID=dbo.hos_district.DID
    where UID=
    (
    select UID from dbo.sys_user
    where UNAME='张三'
    )
    
     
    
    ---根据区县制作房屋出租清单
    select htname as 户型,UNAME as 姓名,dname as 区县,sname as 街道
    from dbo.hos_house
    join dbo.sys_user on dbo.hos_house.UID=dbo.sys_user.UID
    join dbo.hos_street on dbo.hos_house.SID=dbo.hos_street.SID
    join dbo.hos_district on dbo.hos_street.SDID=dbo.hos_district.DID
    join dbo.hos_type on dbo.hos_house.HTID=dbo.hos_type.HTID
     where dbo.hos_street.sID in
     (
     select SID from dbo.hos_street
     where SDID in 
    (select SDID from dbo.hos_street 
     where sid in 
     (
     select sid from dbo.hos_house 
     group by sid 
     having COUNT(HMID)>0 
     )
     group by SDID
     having COUNT(SDID)>=2
     )
     )
     order by 户型  
    
    六.事务，视图和索引
    
      1. 事务是单个的工作单元，不可分割的整体。具有要么都成功或者都失败的属性
    
         事务的四个属性：原子性（atomicity） 一致性（consistency） 隔离性（isolation） 持久性（durability）
    
      2.执行事务的语法：
    
        （1）开始事务
    
              begin transaction
    
          (2)提交事务
    
              commit transaction
    
          (3)回滚事务
    
              rollback transaction
    
           SQL Server的默认模式：自动提交事务
    
       
    
    use Myschool
    create table bank
    (
    id int identity(1,1) primary key not null,
    name varchar(32),
    price decimal(18,2)
    
    )
    
    insert into bank
    values('张三',1000)
    
    insert into bank
    values('李四',1)
    
    --添加检查约束，银行卡余额必须>=1
    alter table bank
    add constraint ck_price check(price>=1)
    --开始转账
     --开启事务
     begin transaction
     
     declare @errorSum int
     set @errorSum=0
     --更改张三的余额
     update bank set price-=500
     where id=1
     
     set @errorSum=@errorSum+@@ERROR
     --更改李四的余额
     update bank set price+=500
     where id=2
     
     set @errorSum=@errorSum+@@ERROR
    
    
    if(@errorSum>0)
    begin
    --存在错误回滚事务
      rollback transaction
    end
    
    else
    begin
    --没有错误提交事务
      commit transaction
    end
    
      3.视图
    
         视图是一种虚拟的表，通常是作为来自一个或多个表的行或列的子集创建的。
    
         视图并不是数据库中存储的数据值的集合，它的行和列来自查询中引用的表，在执行时，它直接显示来自表中的数据。
    
         视图充当着查询中指定表筛选器
    
      4.创建视图的语法:
    
          create view vw_name
    
          as 
    
           <select语句>
    
         删除视图的语法：
    
          drop view vw_name
    
         查看视图的语法：
    
         select  *  from  vw_name
    
        示例：
    
         
    
    use Myschool
    
    create view vw_stuinfo
    as 
    select Studentname,gradename,subjectname,examdate,StudentResult
     from Grade
    inner join Subject on Grade.GradeId=Subject.GradeId
    inner join Result on subject.SubjectId=result.SubjectId
    inner join Student on result.StudentNo=student.StudentNo
     and student.GradeId=Grade.GradeId
    go
    
    
    select * from vw_stuinfo
    
     
    
    索引（Index）加快表中记录的检索速度，同时降低增删改速度

![复制代码][copycode.gif]

[copycode.gif]: /images/20220616/afb340e36441446bbb178f139babf4c6.png