数据库，简单数据查询小命令

数据查询

一、实验目的

本实验的目的是使学生掌握SQL Server查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。

二、实验内容

（一）、数据准备

（二）、练习使用Select语句实现数据查询

1. 单表查询

2. 查询结果排序：——order by 子句

3. 连接查询:——关系数据库中最主要的查询，Where子句

4. 使用带IN谓词的子查询——适用于集合查询

5. 使用带比较运算的子查询

6. 使用带Any, All谓词的子查询

7. 使用带Exists谓词的子查询和相关子查询

8. 使用聚集函数

9. 分组查询

10.集合查询

三、实验源程序（或实验步骤）

use xsgl
select * from student

use xsgl
select * from course

use xsgl
select * from sc

select sno, sname from student

select *  from student

select sname, '出生年份为: ', year(getdate()) - sage, lower(sdept) from student

select sname, '出生年份为: ' 出生, year(getdate())- sage  年份, lower(sdept)  系名from student

select distinct sno from sc

select cno, cname from course where cpno is null

select sno, grade from sc where cno='3' order by grade DESC

select * from student order by sdept ASC, sage DESC

select student.*, sc.* from student, sc where student.sno=sc.sno

select First.cno, Second.pcno  间接先行课from course First, course Second where First.pcno=Second.cno

select * from student where sdept in (select  sdept from  student where sname='刘晨')

select sno, sname from student where sno in(select sno from sc where cno in 
(select cno from course where cname='信息系统'))

select sno from student where sno in (selectsnofrom sc where cno='1')
and sno in (select sno from sc where cno='2')

select * from student where sage<  (select  sage from  student where sname='刘晨')

select sname, sage from student where sage <Any (select sage from student where sdept='IS') 
and sdept<>'IS'

select sname, sage from student where sage <ALL(select sage from student where sdept='IS') and sdept<>'IS'

select sno,sname,sage from student where sage<>all (select sage from student where sdept='CS')

select sname from student where exists (select * from sc where sno=student.sno and cno='1')

select sname from student where not exists (select * from sc where sno=student.sno and cno='1')

select sname from student where not exists (select * from course where not exists
( select * from sc where sno=student.sno and cno=course.cno))

Select Count(*)  as 学生总数 from student

select count(distinct sno) as 选课学生总数 from sc

select sum(credit) as 总credit,avg(credit) as 课程平均学分,max(credit) as 最高学分min(credit) as 最低学分 from course

select avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分from scwhere cno='1'

select avg(grade) from student, course, sc where student.sno=sc.sno and course.cno=sc.cno and sdept='IS' and cname='数据结构'

Select sdept, Count(*) as 人数 from student group by sdept order by 人数 desc

select sdept,ssex,Count(*) as 人数 from student group by sdept, ssex order by sdept,ssex desc

select sno, sname from student where sno in (select sno from  sc group by (sno) having count(*)>3)

select sno, avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分 count(*) as 选课门数 from sc group by sno

select * from student where sdept=’MA’ union select * from student where sdept='IS'

select sno from sc where cno='1' Union select sno from sc where cno='2'

四、实验结果及分析