SQLserver2008-数据查询

--数据查询


-------------------------基本查询--------------


--1.查询所有Student表中的信息
use  Educ;
select * from Student;


--2.查询所有学生的姓名，出生年份和学号
select Sname , YEAR(Sbrithday),Sno from Student;


--3.查询选修了课程的学生编号(distinct的使用)
select distinct(sno) from Student;--distinct去除重复行


--4.查询SE学院的全体学生学号sno和姓名sname
select sno,sname from Student where Sdept='SE';


--5.查询年龄在18-22岁的学生姓名和出生年份以及年龄
select sname , YEAR(sbrithday) , YEAR(GETDATE())-YEAR(Sbrithday) 
from Student
where YEAR(GETDATE())-YEAR(Sbrithday) between 18 and 22;


--6.查询学生学号的最后一位为1或者为2或者为3的学生学号(模糊查询)
select * from Student where Sno like '%1' or Sno like '%2' or Sno like '%3';


--7.查询SE学院和CH学院的学生的信息(条件约束in的使用)
select * from Student where Sdept in ('SE','CH');


--8.查询所有的性李的学生的学生信息()
select * from Student where Sname like '李%';


--9.查询所有不姓李的学生的信息
select * from Student where Sname not like '李%';


--10.查询缺考的学生的学号和课程号
select * from SC where Grade IS NULL;


--11.查询所用参加了考试的学生的学号和课程号
select Sno , Cno  from SC where Grade is not null;


--12.查询选修了C002号课程的学习的信息，并按成绩的排序
select * from SC where Cno ='C002' order by Grade;


--13.查询全体学生的情况，查询的结果按所在院校的学院的Sdept的降序排序，
--同一学院的学生按年龄的升序排序
select * from Student order by Sdept desc , GETDATE() - Sbrithday ASC; -- 多字段排序


-----------------------聚合函数的使用--------------------------


--14.查询所有学生的总分数
select SUM(Grade) as 总分数 from SC; 


--15.查询了选修了课程的学生人数
select COUNT(distinct sno) from SC;


--16.查询选修了C001号课程的学生的平均成绩
select AVG(Grade) from SC;


--17.查询选修了C001号课程的学生的最高分数
select MAX(Grade) as C001最高分数 from SC;


----------------------分组查询---------------


--18.查询每个学院的学生数量
select Sdept , COUNT(Sno) as 人数 from Student group by Sdept;


--19.查询各门课程的平均成绩
select Cno , AVG(Grade) as 平均成绩 from SC group  by Cno; 


--20.查询各门课程号及相应的选课人数
select Cno , COUNT(*) as 选课人数 from SC group by Cno;


--21.查询每个学院男生和女生的人数
select * from Student;
select Sdept , Ssex , COUNT(*) from Student 
group by Sdept,Ssex 
order by Sdept; --多字段分组

--22.查询选修了3门及以上的学生的学号
select sno from SC  group by Sno having COUNT(*)>=3; --分组后的条件查询group by xxx having xxx


--23.查询平均分在60分以上的课程号以及该课程的平均分
select cno , AVG(grade) from SC group by Cno having AVG(Grade)>=60;


-----------------------明细查询------------------


--24.使用compute by子句按学院对学生进行明细汇总
select * from Student order by Sdept compute Count(Sno) by sdept; --依据某个字段排序后，进行明细


--25.使用compute子句对计算机学院的（SE）的所用男生的人数进行明细汇总
select * from Student where Sdept = 'SE' and Ssex = '男' compute Count(Sno); --明细




------------------------------为查询建立新表------------------------
--通常在查询语句执行完成之后，查询的结果是暂时的显示在查询语句的下方的，无法保存
--如果想要保存查询的结果集，并把它保存为表的形式，可以使用into子句来完成


--25.从student表中查询出所有女学生的基本信息，并生成一个新的女生信息表
select * into 女生信息表 from Student where Ssex = '女'; --采用select * into AA from BB 复制表和结构(要求表不存在)
--采用insert into A(字段) select 字段 from B只复制表内数据(要求表A存在)
select * from 女生信息表;


--26.将总分在20分以上的学生情况（学号，姓名，性别，总成绩）存储到数据表gradeList表中，假设GradeList表并不存在
select SC.Sno , Sname , Ssex , SUM (Grade) as SumGrade
into GradeList
from Student A ,SC
WHERE SC.Sno = A.Sno
group by SC.Sno , A.Sname , Ssex having (SUM(SC.Grade)>=50);


drop table GradeList;
select * from GradeList;

--------------------------------将查询结果保存在变量中-------------------------------
--declare @Grade int --定义变量


--27.查询学生20121323012选修课程的成绩，将其保存到变量Grade中
declare @Grade int --声明变量
select @Grade = Grade 
from SC
where Sno = '20121323012' 
print @Grade;


-------------------------------TOP关键字的使用------------------------------
--常常使用在分页操作中


--28.查询C001号课程的前3名
select Top 3 * from SC where Cno = 'C001' order by Grade Desc;


--------------------------------表连接 --------------------------------------
--多表查询


--29.查询选修了C001课程的学生的姓名
select Sname
from Student , SC
where Student.Sno = Sc.Sno and SC.Cno = 'C001';


--30.查询学号为20121323001的学生的姓名，院系，课程号以及成绩
select *  from Student , SC
where Student.Sno = '20121323001' and Student.Sno = SC.Sno;


--31.查询课程的课程号，课程名，课程的学生选修人数以及课程的平均分
select Course.Cno , Cname , COUNT(*)as 选修人数,AVG(Grade) as 平均分  from 
Course , SC
where Course.Cno = SC.Cno group by Course.Cno , Course.Cname; --Cname分组显示


--32.查询所有选修课程为C003的学生学号，姓名和成绩
select Student.Sno , Sname , Grade 
from Student , SC
where Student.Sno = SC . Sno and Cno = 'C003';


--33.查询被选修了的课程的课程号，课程名，该课程的学生选修人数及课程平均分
select Course.Cno , cname , COUNT(*) as 选修人数 , AVG(Grade) as 平均分 
from Course , SC
where Course.Cno  = SC.Cno
group by Course.Cno , Cname ;


--34.查询与何燕同一学院的其他学生的学号，姓名，性别，学院以及专业情况
select b.sno , b.Sname , b.Ssex , b.Sdept , b.Smajor 
from Student a , Student b
where a .Sname = '何燕' and a.Sdept = b.Sdept; --自身连接的妙用(查询同一张表中的数据)


--35.查询学生中年龄相同的学生情况。要求按同龄学生分组显示其学号，姓名，性别，年龄，籍贯，
------学院，年级和专业，且能显示各同龄组人数能信息
select  distinct a.Sno , a.Sname , YEAR(a.Sbrithday) as 年龄 ,a.sadress , a.Sdept,a.Sgrade,a.Smajor
from Student a, Student b
where YEAR(a.Sbrithday) = YEAR(b.Sbrithday) and a.Sno <>b.Sno;


--36.查询所有选修了课程的学生学号，姓名和成绩
select * from 
Student , SC
where Student.Sno = SC.Sno; --等值连接（内连接的一种）


Select * from 
Student Inner JOIN SC
on Student.sno = SC.Sno; --内连接（两个表相匹配的行才能在结果集中出现）


--37.查询所有学生的选课情况（也包括未选课的学生），显示学生的学号，姓名，课程以及成绩
--OUTER JOIN（外连接：可以指定左右表不加限制）
--A right outer join B on XXX (显示右表内的所有数据，左边的表无限制)
--A left outer join B on xxx （显示左边表内的所有数据，右边的表无限制）
--A full outer join B on xxx (显示左右表内的所有数据，左右表都要匹配，无发匹配的用null补齐)
select * from Student right outer join SC
on Student.Sno = SC.Sno;


--------------------------------------------视图---------------------------------


--视图是从一个表或多个表中（其他试图）中产生的虚拟表，其结构和数据来自于一个或多个表（或视图）
--的查询
--视图的本质是：其实就是为了保存select的查询结果。
--语法：
/*
create view 试图名 
as
select .....;
*/


--38.为SE学院的所有学生创建信息视图
create view SE_Student
as
select Sno , Sname , Ssex , Sbrithday , Sadress , Sgrade , Smajor , Sdept
from Student
where Sdept = 'SE'; --视图是虚表，定义的视图并不存储记录信息，视图中的信息仍然存放在原来的表中
--视图只是一个窗口，透过它可以看到自己需要的预先定义的数据，基表中的数据发生改变，视图中的数据也会发生改变


--39.为讲授‘数据结构’课程的老师创建一个DS_Teacher，包括课程编号，课程名称和教师名称
create view DS_Teacher
as
select Course.Cno , Course.Cname , Teacher.Tname
from Course , Teacher , TC
where Course.Cno = TC.Cno and TC.TID = Teacher.TID and TC.Cno = Course.Cno; 


--查看视图数据
select * from SE_Student;
select * from DS_Teacher;


--查看视图定义(表显示)
sp_help SE_Student;


--查看视图定义，文本形式
sp_helpText SE_Student;


--删除视图
drop view SE_Student;

--视图的作用:安全性,化繁为简