一些简单的查询

四．高级查询  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 stuInner join result r on stu.studentno=r.studentnoInner join subject sub on r.subjectno=sub.subjectno  Where studentresult=60 and subjectname=’java’  Go 方法二：  Select studentname from student   Where studentno=(Select studentno from resultInner join subject on result.subjectno=subject.subjectnoWhere 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 subjectWhere 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 Gradewhere GradeName='S1' )begin  update Grade set GradeName='S2' where  GradeId in (  select gradeid from Grade  where GradeName='S1' )endelsebegin 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.StudentNowhere student.StudentNo in(select studentno from Studentwhere Result.StudentNo=student.StudentNo)andsubject.SubjectId in(select subjectid from Subjectwhere ExamDate in(select MAX(examdate) from Resultwhere 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 tempwhere 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_housejoin dbo.hos_street on dbo.hos_house.SID=dbo.hos_street.SIDjoin dbo.hos_type on dbo.hos_house.HTID=dbo.hos_type.HTIDjoin dbo.hos_district on dbo.hos_street.SDID=dbo.hos_district.DIDwhere UID=(select UID from dbo.sys_userwhere UNAME='张三') ---根据区县制作房屋出租清单select htname as 户型,UNAME as 姓名,dname as 区县,sname as 街道from dbo.hos_housejoin dbo.sys_user on dbo.hos_house.UID=dbo.sys_user.UIDjoin dbo.hos_street on dbo.hos_house.SID=dbo.hos_street.SIDjoin dbo.hos_district on dbo.hos_street.SDID=dbo.hos_district.DIDjoin 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 Myschoolcreate table bank(id int identity(1,1) primary key not null,name varchar(32),price decimal(18,2))insert into bankvalues('张三',1000)insert into bankvalues('李四',1)--添加检查约束，银行卡余额必须>=1alter table bankadd 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+@@ERRORif(@errorSum>0)begin--存在错误回滚事务  rollback transactionendelsebegin--没有错误提交事务  commit transactionend  3.视图     视图是一种虚拟的表，通常是作为来自一个或多个表的行或列的子集创建的。     视图并不是数据库中存储的数据值的集合，它的行和列来自查询中引用的表，在执行时，它直接显示来自表中的数据。     视图充当着查询中指定表筛选器  4.创建视图的语法:      create view vw_name      as        <select语句>     删除视图的语法：      drop view vw_name     查看视图的语法：     select  *  from  vw_name    示例：     use Myschoolcreate view vw_stuinfoas select Studentname,gradename,subjectname,examdate,StudentResult from Gradeinner join Subject on Grade.GradeId=Subject.GradeIdinner join Result on subject.SubjectId=result.SubjectIdinner join Student on result.StudentNo=student.StudentNo and student.GradeId=Grade.GradeIdgoselect * from vw_stuinfo 索引（Index）加快表中记录的检索速度，同时降低增删改速度