--表连接Join--
查询所有学生的姓名、年龄及所在班级
使用子查询
select StudentName,
DATEDIFF(yyyy,borndate,getdate()),
(select classname from grade where ClassId=Student.ClassId)
from Student
使用from多表
select StudentName,DATEDIFF(yyyy,borndate,getdate()),classname
from Student,grade
where Student.ClassId=grade.ClassId
使用多表连接 表 inner join 另外的表 on 如何建立关联(能够建立关联的字段一般就是能够创建主外键关系的字段)
select StudentName,DATEDIFF(yyyy,borndate,getdate()),classname
from Student
join grade on Student.ClassId=grade.ClassId
1.内连接：inner join.找到两个表建立关系的字段值相等的记录,如果字段值不相等,那么就抛弃
查询学生姓名、年龄、班级及成绩
select Student.StudentName,
DATEDIFF(YYYY,Student.BornDate,GETDATE()),
grade.classname,
Result.StudentResult
from Student
inner join grade on Student.ClassId=grade.ClassId
inner join Result on Student.StudentNo=Result.StudentNo
where student.ClassId=2
查询每个学员需要参加的考试科目名称
select Student.StudentNo,Student.StudentName,Subject.SubjectName
from Student
inner join Result on Student.StudentNo=Result.StudentNo
inner join Subject on Result.SubjectId=Subject.SubjectId;
select Student.StudentNo,Student.StudentName,Subject.SubjectName
from Student
inner join Subject on Student.ClassId=Subject.ClassId;
select * from PhoneType
inner join PhoneNum on PhoneNum.pTypeId=PhoneType.ptId;
左,右连接
左连接，可以得到左表的所有记录，只不过如果左表的字段的值在右表中找不到相应的关联记录，那么右表中的所有字段会以null值替代
select * from PhoneType left join PhoneNum on PhoneNum.pTypeId=PhoneType.ptId
查询没有参加考试的学员信息
select * from Student
left join Result on Student.StudentNo=Result.StudentNo
where Result.StudentNo is null and Result.SubjectId is null
右连接:
select * from PhoneType left join PhoneNum on PhoneNum.pTypeId=PhoneType.ptId
交叉连接:
select * from Student
cross join grade
做多表连接的建议:
1.先确定字段都来自于那一些表,通过 表.字段 的方式进行确定
2.查看这些表都有那一些关联（可以建立主外键关系的字段），如果没有关联，考虑是否需要使用中间其它表进行关联
3.多表连接后可以得到一个虚拟的表，可以对这个表的所有字段添加where条件
练习1：查询所有英语及格的学生姓名、年龄及成绩
select Student.StudentName,DATEDIFF(YYYY,Student.BornDate,GETDATE()),Result.StudentResult
from Student
inner join Result on Student.StudentNo=Result.StudentNo
inner join Subject on Result.SubjectId=Subject.SubjectId
where Subject.SubjectName='office' and Result.StudentResult>=60
练习2：查询所有参加考试的(english分数不为null)学生姓名、年龄及成绩
练习3：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格office
select Student.StudentName,DATEDIFF(yyyy,student.borndate,getdate()),
case
when Result.StudentResult is null then '没有考试'
when Result.StudentResult>=60 then cast(Result.StudentResult AS CHAR(3))
else '不及格'
end
from Student
left join Result on Student.StudentNo=Result.StudentNo
inner join Subject on Result.SubjectId=Subject.SubjectId
where Subject.SubjectName='office'
变量的种类--全局变量
go
insert into grade values('fdasfas')
select * from Student
select @@IDENTITY ---它得到的值不是指点上一条语句，而是最近的insert语句的值
go
select * from Student
select @@ROWCOUNT
delete from Student where StudentNo=12433
select @@ROWCOUNT
go
@@ERROR 得到最近这一条语句的错误号
select * from stu --语法错误的错误号得不到
select @@ERROR --针对于增加删除和修改而言,如果有错误那么错误号一定大于0，如果没有错误那么就一定是0
update Student set LoginPwd='ss' where StudentNo=1
update Student set LoginPwd='ss' where StudentNo=1
select * from Student
go
select @@ERROR
视图
视图的本质就是一句sql语句--select.它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
视图的本质就是一张虚拟表：它的操作与操作表基本一致
视图里面并没有真正存储数据，它存储的是sql语句，当执行视图获取数据的时候，本质就是执行视图中的sql语句去获取数据
select * from Student
select * from vw_getstudentResultInfo
通过命令语句创建视图
语法：
go
create view vw_自定义名称
as
select 命令
1.可不可以写多条select查询？只能写一条
2.视图里面只能写select，不能添加任何的update/delete/insert语句
go
--获取六期班学员信息
if exists(select * from sysobjects where name='vw_getStudentByClassId_6')
drop view vw_getStudentByClassId_6
go
create view vw_getStudentByClassId_6
as
select top 2 * from Student order by studentname
对视图进行查询
go
select * from vw_getStudentByClassId_6 order by studentname
对视图进行修改,可以对视图进行删除增加和修改操作，况且操作会直接影响物理表，所以一般不建立去做这些操作，因为视图的本质目的是为查询
update vw_getStudentByClassId_6 set classhour = 10, subjectname='c#', classname='111' where subjectid=2
delete from vw_getStudentByClassId_6 where Studentno=3
视图的增加删除和修改操作只能针对于单个表，如果涉及了多个表的操作，那么将不成功
select * from vw_getstudentResultInfo
delete from vw_getstudentResultInfo where Studentno=6
update vw_getstudentResultInfo set studentresult=55 , subjectname='c#' where Studentno=6
子查询
一个查询里面还包含着另外一个查询
子查询做为条件
1.包含在()里面先执行的查询就是子查询语句,包含子查询的就称为父查询语句
2.引入子查询时，在选择列表中只能指定一个表达式,就是意味着子查询做为条件(有关系运算符)的时候子查询永远不可能出现多列的情况
3.子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的,就意味着如果子查询跟在关系运算符之后，必须保证子查询只返回了单个值,如果真的需要这么做就可以使用关键字 in/not in
查询比林思年龄大的学员信息
4.有子查询的sql语句返回的列只与父查询有关，子查询中的列仅仅是做为条件判断
1.先查询出林思的年龄
select borndate from Student where StudentName='林思'
返回一行多列值----做为条件永远错误
select * from Student where BornDate<(select * from Student where StudentName='林思')
返回多行一列值
select * from Student where BornDate<(select borndate from Student)
查询班级ID《=3的学员信息
select classid from grade where ClassId<=3
select StudentNo,StudentName from Student where ClassId not in(select classid from grade where ClassId<=3)
子查询得到某列的值 --用得最少
查询学员是10的学员office考试成绩和考试日期,显示学员姓名
select (select studentname from student where studentno=10),
StudentResult,
ExamDate
from Result
where StudentNo=10
and SubjectId=(select SubjectId from Subject where SubjectName='office')
3.查询得到结果集，可以使用子查询返回一张虚拟表
查询班级ID为1的所有学员的考试成绩
1查询出班级ID为1的学员的学号
select studentno from Student where ClassId=1
查询指定学号的学员的成绩
select * from Result where StudentNo in(select studentno from Student where ClassId=1)
分页
select top 5 * from Student
select top 5 * from Student where StudentNo not in(select top 5 StudentNo from Student order by StudentNo)
ROW_NUMBER 可以为查询出的每一行返回一个行号，行号类似于标识列，永远不会重复，同是它默认是连续的,这个函数可以为结果集添加一个新列
over说明在那一个字段上进行排序，因为按不同字段排序结果集的显示是不一样的
select ROW_NUMBER() over(order by studentno) as id,* from Student
如果子查询做为结果集那么必须为其添加别名
select * from (select ROW_NUMBER() over(order by studentno) as id,* from Student) as temp
where temp.id>=6 and id<=10
使用Row_number() over(指定排序字段)实现分页,这个函数可以为结果集添加一个新列
select ROW_NUMBER() over(order by studentno) as id,* from Student
select * from (select ROW_NUMBER() over(order by studentno) as id,* from Student
) as temp where id>0 and id<=5
查询年龄比“廖杨”大的学员，显示这些学员的信息
select * from Student where BornDate<(select BornDate from Student where StudentName='廖杨')
查询二期班开设的课程
select * from Subject where ClassId=(select ClassId from grade where classname='二期班')
查询参加最近一次“office”考试成绩最高分和最低分
select subjectid from Subject where SubjectName='office'
select MAX(ExamDate) from Result where SubjectId=(select subjectid from Subject where SubjectName='office')
select max(StudentResult),MIN(StudentResult) from Result where SubjectId=
(select subjectid from Subject where SubjectName='office')
and ExamDate=
(select MAX(ExamDate) from Result where SubjectId=
(select subjectid from Subject where SubjectName='office'))
select * from Result
select * from Subject
子查询的分类:
1.独立子查询:单独可以运行的子查询
2.相关子查询：子查询中引用了父查询中的列
查询参加了考试的学员信息
select distinct StudentNo from Result
select * from Student where StudentNo in(select distinct StudentNo from Result)
select * from Student where StudentNo=(select distinct StudentNo from Result where Result.StudentNo=Student.StudentNo)
select StudentNo from Result where StudentNo=33
select * from Student where StudentNo=(select StudentNo from Result where StudentNo=33)