首页 > 代码库 > SQL进阶-（2）
SQL进阶-（2）
--表连接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 命令
--go
--获取六期班学员信息
--1.可不可以写多条select查询？只能写一条
--2。视图里面只能写select，不能添加任何的update/delete/insert语句
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
--CREATE PROC[EDURE]
--procedure
--子查询--
--一个查询里面还包含着另外一个查询
--子查询做为条件--
--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)
SQL进阶-（2）