针对数据库设计各种单表查询SQL语句、分组统计查询语句;设计单个表针对自身的连接查询,设计多个表的连接查询。理解和掌握SQL查询语句的特点和作用,按照SQL程序设计规范写出具体的SQL查询语句,并调试通过。
利用P45-46页的Student、Course和SC表。用SQL语句完成下面的操作,并调试通过。 Student表
- 查询学生选课表中的全部数据。
- 查询计算机系的学生的姓名、年龄。
- 查询成绩在70~80分之间的学生的学号、课程号和成绩。
- 查询计算机系年龄在18~20之间且性别为“男”的学生的姓名、年龄。
- 查询课程号为“c001”的课程的最高的分数。
- 查询计算机系学生的最大年龄和最小年龄。
- 统计每个系的学生人数。
- 统计每门课程的选课人数和考试最高分。
- 统计每个学生的选课门数和考试总成绩,并按选课门数升序显示结果。
- 查询总成绩超过200分的学生,要求列出学号、总成绩。
- 查询选修了“c002”号课程的学生的姓名和所在系。
- 查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩降序排列结果。
- 查询哪些学生没有选课,要求列出学号、姓名和所在系。
- 查询与Java在同一学期开设的课程的课程名和开课学期。
- 查询与李勇年龄相同的学生的姓名、所在系和年龄。
- 用子查询实现如下查询:
①查询选修了“c001”课程的学生姓名和所在系。
②查询数学系成绩80分以上的学生的学号、姓名、选的课程号和成绩。
③查询计算机系考试成绩最高的学生的姓名。
④查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩。
4、实验小结
通过本次实验我能熟练运用SQL语言实现数据基本查询,包括单表查询、分组统计查询和连接查询,并掌握了聚集函数,嵌套查询,WHERE和HAVING的区别。还能够针对数据库设计各种单表查询SQL语句、分组统计查询语句;设计单个表针对自身的连接查询,设计多个表的连接查询。并掌握了SQL查询语句的特点和作用,能按照SQL程序设计规范写出具体的SQL查询语句,并调试通过。
源码
#创建实验二数据库 CREATE DATABASE IF NOT EXISTS 实验二; #使用实验二 数据库 USE 实验二; #创建Student表 CREATE TABLE IF NOT EXISTS Studetn( Sno INT NOT NULL PRIMARY KEY, Sname VARCHAR(20) NOT NULL, Ssex CHAR(2) NOT NULL, Sage INT, Sdept VARCHAR(10) NOT NULL ); DROP TABLE Course; #创建Corse表 CREATE TABLE IF NOT EXISTS Course( Cno INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Cname VARCHAR(20) NOT NULL, Credit VARCHAR(20) NOT NULL, Semester VARCHAR(20) NOT NULL ); DROP TABLE SC; #创建SC表 CREATE TABLE IF NOT EXISTS SC( Sno CHAR(9) NOT NULL, Cuo VARCHAR(20) NOT NULL, Grade INT NOT NULL ); #向Student中插入自己编的数据(乱编的) INSERT INTO studetn VALUES('201215121', '李勇','男', '18','SC'), INSERT INTO studetn VALUES('201215122', '刘晨','女', '19','SC'), INSERT INTO studetn VALUES('201215123', '王敏','男', '18','MA'), INSERT INTO studetn VALUES('201215124', '张力','女', '19','SC'), INSERT INTO studetn VALUES('201215125', '二狗','男', '20','IS'); #其他两个表的数据 我在表格中进行添加的 没有用sql语句进行添加 #1.查询学生选课表中全部数据 SELECT * FROM Course; #2.查询计算机系专业同学的姓名和年龄 SELECT Sname,Sage FROM Studetn WHERE Sdept="SC"; #3.查询你分数成绩70-80之间的学生的学号,课程好和成绩 SELECT Sno,Cuo,Grade FROM sc WHERE Grade<=80 AND Grade>=70; #4.查询计算机系年龄在18~20之间且性别为“男”的学生的姓名、年龄。 SELECT Sname,Sage FROM Studetn WHERE Sage BETWEEN 18 AND 20 AND Ssex="男" AND Sdept="sc"; #5.查询课程号为“c001”的课程的最高的分数。 SELECT MAX(Grade) FROM sc WHERE Cuo=1; #6.查询计算机系学生最大年龄和最小年龄 SELECT MAX(Sage),MIN(Sage) FROM studetn WHERE Sdept="sc"; #7.统计每个系的人数 SELECT Sdept,COUNT(Sno) num FROM studetn GROUP BY Sdept; #8.统计每门课程的选课人数和 最高分 SELECT Cuo,COUNT(Sno) num, MAX(Grade) FROM sc GROUP BY Cuo; #9.统计每个学生的需那颗门数和考试总成绩 ,并按选课门数升序显示结果 SELECT Sno,COUNT(Cuo) 选课数量,SUM(Grade)total_Grade FROM sc GROUP BY Sno ORDER BY 选课数量; #10.查询总成绩超过200的学生 列出学号 总成绩 SELECT Sno,SUM(Grade) 总成绩 FROM sc GROUP BY Sno HAVING 总成绩>=200; #11.查询选修了c002课程同学的姓名和系 SELECT studetn.Sname,studetn.Sdept FROM studetn ,sc WHERE studetn.Sno=sc.Sno AND sc.Cuo=2; #12.查询成绩80以上的姓名 课程号 和成绩 按照成绩降序 SELECT Sname,Cuo,Grade FROM studetn ,sc WHERE Grade>80 AND studetn.Sno=sc.Sno ORDER BY Grade DESC; #13.查询哪些学生没有选课,要求列出学号、姓名和所在系 SELECT Sno,Sname,Sdept FROM studetn WHERE Sno NOT IN( SELECT Sno FROM sc ); #14.查询与Java在同一学期开设的课程的课程名和开课学期。 SELECT Cname, Semester FROM Course WHERE Semester=(SELECT Semester FROM course WHERE Cname='Java' ); #15.查询与李勇年龄相同的学生的姓名、所在系和年龄。 SELECT Sname,Sdept ,Sage FROM studetn WHERE Sage =(SELECT Sage FROM studetn WHERE Sname='李勇'); #子查询 查询选修了“c001”课程的学生姓名和所在系。 SELECT Sname,Sdept FROM studetn WHERE Sno =( SELECT Sno FROM sc WHERE Cuo=1 ); #②查询数学系成绩80分以上的学生的学号、姓名、选的课程号和成绩。 SELECT studetn.`Sno`,Cuo,Grade FROM studetn,sc WHERE Sdept="MA" AND studetn.Sno IN( SELECT Sno FROM sc WHERE Grade>10 ); #③查询计算机系考试成绩最高的学生的姓名。 SELECT Sname FROM studetn WHERE Sno=(SELECT Sno FROM sc WHERE Grade=(SELECT MAX(Grade) FROM sc WHERE Sno IN( SELECT Sno FROM studetn WHERE Sdept="sc"))); #④查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩。 SELECT Sname ,Sdept,Ssex,Grade FROM studetn,sc WHERE studetn.`Sno`=sc.`Sno` AND sc.`Grade`IN( SELECT Grade FROM sc WHERE Grade=( SELECT MAX(Grade) FROM sc WHERE Cuo=( fromCourse WHERE Cname="数学")) );