实 验 二 SQL 数据查询

一、无条件查询

1.查询全体学生的详细记录。

• select * from t_student;

  

看到这，我就傻眼了，明明上次打开时中文还没有乱码，于是开启了我寻求真理之路。

• 输入status，查看数据库状态。

  

  我们可以发现字符格式均为utf8，继续。

• 万能的百度告诉我，需要运行一下set names gbk;即可。试了一下，完美解决。

2.查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。

• select Sname,Sno,Sdept from t_student;

3.查询全体学生的姓名(Sname)、出生年份及学号(Sno)。

• 此处应用时间函数year(now())查询当前年份。

4.查询全体学生的姓名（Sname）、出生年份和学号(Sno)，要求用小写字母表示姓名中的字母。

• 由于数据库中所有数据姓名均用小写字母表示，则再此添加一条数据。insert into t_student(Sno,Sname,Ssex,Sage,Sdept)values("0804606","LIHUA","女",16,"jk20");

​ 插入成功。

• select lower(Sname) from t_student;

5.查询选修了课程的学生学号。

• select Sno from t_sc;

​ 学号0804606为刚加入数据，并未选课。

6. 查询课程号（Cno）和课程名(Cname)，Cno和Cname请用别名课程号和课程名来显示。

• select Cno no,Cname name from t_course;

二、条件查询

1.查询数学系全体学生的学号(Sno)和姓名 (Sname)。

• select Sno,Sname from t_student where Sdept="jk";

2.查询所有年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。

• select Sname,Sage from t_student where Sage between 18 and 22;
• select Sno,Sname,Sage from t_student where Sage in (18,19,20,21);

3.查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。

• select Sno,Sname,Sage from t_student where Sage < 18 or Sage > 22;
• select Sno,Sname,Sage from t_student where Sage not in (18,19,20,21);

4.查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。

• select Sno,Sname,Sage from t_student where Sdept = "jk" or Sdept = "jk19" or Sdept = "jk20";

• select Sno,Sname,Sage from t_student where Sdept in ("jk","jk19","jk20");

5.查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。

• select Sname,Sage from t_student where Sdept not in ("jk","jk19","jk20") ;

• select Sname,Sage from t_student where Sdept !="jk"and Sdept!= "jk19" and Sdept != "jk20";

  此图不予给出，与上图结果一致。

6.查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。

• select Sname,Sno,Ssex from t_student where Sname like "l%";

​ 此处考察模糊查询，姓“刘”，即开头字符为“刘”即可。

7.查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。

• select Sname,Sno,Ssex from t_student where Sname like "l___";

​ 用“__”表示占位，上句为三个下划线。

8.查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。

• select Sname,Sno from t_student where Sname not like "l%";

9.查询课程名为“DB_设计”的课程号(Cno)和学分(Credit)。_

• select Cno,Credits from t_course where Cname = "sjk";

10.查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。

• select Cno,Credits from t_course where Cname like "s%j_";

11.假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。

• 表中与符合条件数据，则插入数据如下：insert into t_sc(Sno,Cno) values ("0804606","101");，Grade为null。

• select Sno,Cno from t_sc where Grade is null;

12.查询所有有成绩的学生学号(Sno)和课程号(Cno)。

• select Sno,Cno from t_sc where Grade is not null;

13.查询课程名（Cname）中包括“设计”两个字的课程的课程号(Cno)和课程名(Cname)。

• select Cno,Cname from t_course where Cname like "%j%";

查询结果排序

1. 查询选修了C03课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。

   • select Sno,Grade from t_sc where Cno="101" order by Grade desc;

   

2. 查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。

   • select * from t_student order by Sdept,Sage desc;

   • 为更好的体现题目要求，添加数据如下

   • insert into t_student(Sno,Sname,Ssex,Sage,Sdept) values(“0804607”,“timi”,“男”,56,“jk19”);

     

3. 查询“数据库课程”考试成绩前十名同学的姓名和成绩。

   • select Sname,Grade from t_sc sc,t_Student s,t_course c where sc.Sno=s.Sno and c.Cname="sjk" and sc.Cno=c.Cno;

聚集函数的使用

1. 查询学生总人数。

   • select count(Sno) from t_student;

   

2. 查询选修了课程的学生人数。

   • select count(Sno) from t_sc;

   

3. 计算选修C01号课程的学生平均成绩。

   • select avg(Grade) from t_sc sc join t_course c on sc.Cno=c.Cno where Cname="sjk";

   

4. 查询选修C01号课程的学生最高分数。

   • select max(Grade) from t_sc ;

   

5. 查询超过自己所选课程平均成绩的课程名。

   • select Cname from t_sc sc join t_course c on sc.Cno=c.Cno where sc.Sno="0806601" and (select avg(Grade) from t_sc sc join t_course c on sc.Cno=c.Cno);
   • 这里假设自己为的学号为“08064601”.

   

查询结果分组

1. 求各个课程号(Cno)及相应的选课人数。

   • select Cno,count(Sno)from t_sc group by Cno;

   

2. 查询选修了3门或3门以上课程的学生学号(Sno)。

• select Sno from t_sc group by Sno having count(Cno)>3;

不同表之间的连接查询

1. 查询每个学生及其选修课程的情况。
   • select s.Sno,sc.Cno from t_sc sc join t_Student s on sc.Sno=s.Sno;

2. 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。

   • select s.Sno,s.Sname,c.Cname,sc.Grade from t_sc sc join t_Student s on sc.Sno=s.Sno join t_course c on c.Cno=sc.Cno;

     

自身连接

1. 查`询每一门课的间接先修课(即先修课的先修课)。

   • select a.Cno,b.Pre_Cno from t_course a join t_course b on a.Pre_Cno=b.Cno;

   

外连接

1. 把课本上例3.50中的等值连接改为左外连接。

带谓词IN的嵌套查询

1. 查询选修了编号为“101”的课程的学生姓名(Sname)和所在系(Sdept)。

• ​ select s.Sname,s.Sdept from t_student s join t_sc sc on sc.Sno=s.Sno where sc.Cno in (101);

2. 查询与“mary”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

   • select Sno,Sname,Sdept from t_student where Sdept in (select Sdept from t_student where Sname in ("mary"));

   

3. 查询选修了课程名为“sjk”的学生学号(Sno)和姓名(Sname)。

   • select s.Sno,s.Sname from t_sc sc join t_Student s on sc.Sno=s.Sno join t_course c on c.Cno=sc.Cno and c.Cname in ("sjk") ;

   

带有比较运算符的嵌套查询

1. 求“c02”课程的成绩中高于“王敏”同学成绩的学生学号和成绩。

   • select s.Sno,sc.Grade from t_student s join t_sc sc on s.Sno=sc.Sno where (select sc.Grade from t_student s join t_sc sc on s.Sno=sc.Sno where s.Sname="mary")<sc.Grade;

   

带谓词ANY或ALL的嵌套查询

1. 查询jk19的不超过jk所有学生的年龄的学生姓名(Sname)和年龄(Sage)。

   • select Sname,Sage,Sdept from t_student where Sage>any(select Sage from t_student where Sdept="jk" ) and Sdept="jk19" ;

   

带谓词EXISTS的嵌套查询

1. 查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。

   • select Sname,Sdept from t_student t where exists (select * from t_sc sc where sc.Cno="101" and t.Sno = sc.Sno );

   

2. 查询选修了所有课程的学生姓名(Sname)和所在系。

   • select Sname,Sdept from t_student s where not exists (select * from t_course c where not exists (select * from t_sc sc where Sno=s.Sno and Cno= c.Cno ));

   

集合查询

1. 查询计算机科学系的学生或年龄不大于20岁的学生信息。

   • select * from t_student where Sdept = "jk" union select * from t_student where Sage<=20;

   

2. 查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。

   • select * from t_student where Sdept = "jk19" intersect select * from t_student where Sage<=20;

   • 由于MySQL并不支持intersect，再此处使用其他语句代替。

   • select * from t_student where Sdept="jk" and Sage<=20;

   

3. 查询数学系的学生与年龄不大于20岁的学生的差集。

   • select * from t_student where Sdept="jk19" except select * from t_student where Sage<=20;
   • 很遗憾，MySQL并不支持except，此处仍用其他语句代替。
   • select * from t_student where Sdept="jk19" and Sage>20;