sql server操作2:查询数据库语句大全

注：以下操作均建立在上篇文章sql Server操作1的数据基础之上

一、实验目的

熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询

 

二、实验内容和要求

使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法。

 

三、实验主要仪器设备和材料

1．计算机及操作系统：PC机，Windows 2000/xp/win7；

2．数据库管理系统：SQL sever 2005/2008；

 

四、实验方法、步骤及结果测试

实验题目：

1、对实验一建立的数据库表进行查询

简单查询：

题目1、查询学生的系别，用小写字母表示。
 SQL语句
 select distinct LOWER(Sdept) from S;

题目2、查询叫“刘%”的学生学号、系别
SQL语句
select Sno Sdept from S where Sname like '刘\%' escape'\';

题目3、查询教师姓名第二个字是“一”的教师姓名，所教课程名。
SQL语句
select Tname,Cname from C where Tname like '_一_';

题目4、查询没有成绩的学生学号，学号降序排列。
SQL语句
select S.Sno from S where not exists(
   select * from Sc where S.Sno=Sc.Sno
)
order by S.Sno desc;

题目5、查询选修课程的学生人数
SQL语句
select count(distinct Sno) As '学生人数' from Sc
--select count(*)
--from S 
--where exists( select * from Sc where Sc.Sno = S.Sno )

题目6、各科选修课成绩及格的选修人数。
SQL语句
select count(distinct Sno)As'及格人数' from Sc where Grade>=60
group by Cno;

题目7、各学院男生人数,超过2人的输出学院名和人数，并按男生人数降序排列
SQL语句
select Ssp,count(*) from S where S.Ssex='男'
group by Ssp
having count(*)>2
order by count(*) desc;

题目8、查询IS系男女学生人数各是多少SQL语句
select S.Ssex As'性别',count(*) As'IS系人数' from S where S.Sdept='IS'
group by Ssex;

题目9、找出年龄>23岁，平均成绩不及格的学生学号和平均分
SQL语句
select sno,avg(grade) from SC
where sno in(
select sno from S where getdate()-sbirth>23
)
group by sno
having avg(grade)<60
或者：
--获得当前系统时间
--跨越表查询要用having，不能用在where
Select S.Sno As'学生学号',Avg(Sc.Grade) As'平均成绩'from S,Sc
    where S.Sno=Sc.Sno and getdate()-Sbirth >23
    group by S.Sno
　　having AVG(Sc.Grade)<60;

题目10、显示所有学生信息，如果该生选修过课程，显示其选修课号和考试成绩
SQL语句
select S.*,Sc.Cno,Sc.Grade from S left join  Sc on(SC.Sno=S.Sno);

题目11、查询每门课都是80分以上的学生的学号与姓名。
SQL语句
--如果没有加"S.Sno=Sc.Sno and"会出现有未选修了课程的学生学号
--如果没有distinct则会出现重复
Select distinct S.Sno As'学生学号',S.Sname As'姓名'from S,Sc
    where S.Sno=Sc.Sno and S.Sno not in
　　(select Sno from Sc where Grade<=80);
　　或者：
select sno,sname from S where sno in
  (select sno from sc group by sno
　　    having min(grade)>80)

题目12、查询学分比“计算机应用”多的课程号和课程名、学分
SQL语句
Select Cname As'课程名',cCredit As'学分' from C
  where cCredit>(Select cCredit from C where Cname='计算机应用')
group by Cname,cCredit;

题目13、查询CS系中年龄比IS系所有人年龄都小的学生学号和姓名
SQL语句
use Student
Select Sno As'学生学号',Sname As'姓名' from S
  where Sdept='CS' and Sbirth>(Select Max(sBirth) from S where Sdept='IS')
group by Sno,Sname;
或者：
use Student
Select Sno As'学生学号',Sname As'姓名' from S
  where Sdept='CS' and Sbirth>all(Select sBirth from S where Sdept='IS')
group by Sno,Sname;

题目14、is系没有选修02号课程的学生学号和学生姓名
SQL语句
Select Sno As'学生学号',Sname As'学生姓名' from S
  where S.Sdept='IS'and not exists (select * from Sc where  Sc.Sno=S.Sno and Sc.Cno=02)
group by Sno,Sname;

题目15、被全部学生都选修的课程号、课程名
SQL语句
select Cno As'课程号',Cname As'课程名' from C
   where not exists(select * from S where not exists (select * from Sc where Sc.Sno=S.Sno and Sc.Cno=C.Cno))

题目16、选修了01号课又选修了02号课的学生的学号和姓名
实现代码及查询结果截图：
SQL语句
select S.Sno As'学生学号',S.Sname As'学生姓名' from S,Sc
    where  Sc.Sno=S.Sno and Sc.Cno=01
intersect
select S.Sno As'学生学号',S.Sname As'学生姓名' from S,Sc
　　where  Sc.Sno=S.Sno and Sc.Cno=02;
　　或者：
select Sname,Sno from S
where Sno in(
    select Sno from SC where cno='01' and Sno in(
        select Sno from SC where Cno='02'))

题目17、被全部IS系的学生都选修的课程号和课程名
SQL语句
 select Cno As'课程号',Cname As'课程名' from C
      where not exists(select * from S where S.Sdept='IS'and not exists (select * from Sc where Sc.Sno=S.Sno and Sc.Cno=C.Cno))

题目18、查询选修高等数学与选修数据库的学生差集
SQL语句
select * from S 
   where  exists (select *from Sc where Sc.Sno=S.Sno and exists(select * from C where Sc.Cno=C.Cno and C.Cname='高等数学'))
except
select * from S 
   where  exists (select *from Sc where Sc.Sno=S.Sno and exists(select * from C where Sc.Cno=C.Cno and C.Cname='数据库'));
或者：
select Sno from SC,C where
C.Cname='高等数学' and C.Cno=SC.Cno and SC.Sno not in(
select Sno from SC,C where SC.Cno=C.Cno and C.Cname='数据库')
   

题目19、没有选修“李一”老师开设的课程的学生学号、姓名、系别
SQL语句
select S.Sno As'学生学号',S.Sname As'学生姓名',S.Ssp As'系别' from S 
    where not exists(
        select * from SC,C where SC.cno=C.cno and SC.sno=S.sno
           and tname='李一')     

题目20、查询各选修学生最高分最低分之差大于30分的学生学号
SQL语句
select Sc.Sno As'学生学号' from Sc 
       group by Sno
       having max(Grade)-min(Grade)>30;

五、心得总结

    1、题目4中查询没有成绩的学生学号，一开始爸它当成查询成绩为0或者说查询成绩为空的方式去查询，所以多了一条记录，但是实际上第九位学生是没有成绩的，即Sc表中无记录。

    2、题目5中，查询选修课程的学生人数，如果没有加上distinct就会导致学生人数结果为16，这是因为重复挑选的结果.

    3、题目9中，获得当前系统时间选用getdate()，并且跨越表查询要用having，不能用在where

    4、题目10中，判断过后显示其选修课号和考试成绩，这个一开始很纠结，感觉要重复读取学生信息才行，所以不知道这样合不合适。

    5、题目11中，如果没有加"S.Sno=Sc.Sno and"会出现有未选修了课程的学生学号

同时，如果没有distinct则会出现重复

 

六、进行连接查询时应注意哪些问题？

     1、使用连接查询时必须在from子句后指定两个或两个以上的表。

     2、使用连接查询时应在列名前加表名作为前缀但是如果不同表之间的列名不同可以不加表名作为前缀如果在不同表之间存在同名列在列名前必须加表名作为前缀否则会因为列的二义性而报错。

     3、使用连接查询时必须在where子句中指定有效的连接条件在不同表的列之间进行连接。如果不指定连接条件或者指定无效的连接条件那么会导致生成笛卡尔积。

     4、使用连接查询时使用表别名可以简化连接查询语句。当指定表别名时别名应该跟在表名的后面。