[MySQL]查询语句的应用 - 对教师表和学生表的查询应用

在我们进行查询语句的应用之前，我们要先往表中插入数据，我们什么也查不到，

由于上次我们创建数据表时创建了几个存储过程，所以我们可以利用存储过程来帮我们添加数据。

插入数据：

#使用存储过程来添加数据
CALL studentAdd(20050101, '李勇', '男', '1987-01-12', '山东济南', '计算机工程系', '计算机应用');
CALL studentAdd(20050201, '刘晨', '女', '1988-06-04', '山东青岛', '信息工程系', '电子商务');
CALL studentAdd(20050301, '王敏', '女', '1989-12-23', '江苏苏州', '数学系', '数学');
CALL studentAdd(20050202, '张立', '男', '1988-08-25', '河北唐山', '信息工程系', '电子商务');

CALL courseAdd('C01', '数据库');
CALL courseAdd('C03', '信息系统');
CALL courseAdd('C02', '数学');
CALL courseAdd('C04', '操作系统');

CALL scAdd(20050101, 'C01', 25);
CALL scAdd(20050101, 'C02', 62);
CALL scAdd(20050101, 'C03', 88);
CALL scAdd(20050201, 'C02', 52);
CALL scAdd(20050201, 'C03', 80);

CALL teacherAdd(101, '李新', '男', '1977-01-12', '计算机工程系');
CALL teacherAdd(102, '钱军', '女', '1968-06-04', '计算机工程系');
CALL teacherAdd(201, '王小花', '女', '1979-12-23', '信息工程系');
CALL teacherAdd(202, '张小青', '男', '1968-08-25', '信息工程系');

CALL teachingAdd('C01', 101, 2);
CALL teachingAdd('C02', 102, 1);
CALL teachingAdd('C03', 201, 3);
CALL teachingAdd('C04', 202, 4);

接下来我们就可以进行一系列查询操作了，例如获取每个学生的平均分，总分等。

#查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息:
SELECT sno AS '学号', sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期', saddress AS '生源地', sdept AS '系别', speciality AS '专业' FROM student;

SELECT cno AS '课程编号', cname AS '课程名' FROM course;

SELECT sno AS '学号', cno AS '课程号', degree AS '成绩'
FROM sc;


#查询所有学生的学号、姓名、性别和出生日期。
SELECT sno AS '学号', sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期' FROM student;

#查询所有课程的课程名称
SELECT cname AS '课程名' FROM course;

#查询前10门课程的课号及课程名称。
SELECT cno AS '课程编号', cname AS '课程名' FROM course LIMIT 10;

#查询所有学生的姓名及年龄。
SELECT sname AS '姓名',  TIMESTAMPDIFF(YEAR, sbirthday, CURDATE()) AS '年龄' FROM student;




#查询所有年龄大于18岁的女生的学号和姓名。
SELECT sno AS '学号', sname AS '姓名' FROM student WHERE ssex = '女' AND TIMESTAMPDIFF(YEAR, sbirthday, CURDATE()) > 18;

#查询所有男生的信息。
SELECT sno AS '学号', sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期', saddress AS '生源地', sdept AS '系别', speciality AS '专业' FROM student WHERE ssex = '男';

#查询所有任课教师的姓名（tname）和所在系别（tdept）。
SELECT tname AS '姓名', tdept AS '系别' FROM teacher;

#查询“电子商务”专业的学生姓名、性别和出生日期。
SELECT sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期' FROM student WHERE speciality = '电子商务';

#查询Student表中的所有系名。
SELECT sdept AS '系名' FROM student;


#查询“C01”课程的开课学期。
SELECT CTERM AS '开课学期'  FROM teaching where cno = 'C01';


#查询成绩在80～90分之间的学生学号及课号。
SELECT sno AS '学号', cno AS '课程号' FROM sc WHERE degree BETWEEN 80 AND 90;

#查询在1970年1月1日之前出生的男教师信息。
SELECT tno AS '教师号', tname AS '姓名', tsex AS '性别', tbirthday AS '出生日期', tdept FROM teacher WHERE tbirthday < DATE('1970-1-1') AND tsex ='男';

#输出有成绩的学生学号。
SELECT DISTINCT sno AS '学号' FROM sc WHERE sno != 0;


#查询所有姓“刘”的学生信息
SELECT sno AS '学号', sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期', saddress AS '生源地', sdept AS '系别', speciality AS '专业'
FROM student WHERE sname LIKE '刘%';

#查询生源地不是山东省的学生信息
SELECT sno AS '学号', sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期', saddress AS '生源地', sdept AS '系别', speciality AS '专业'
FROM student WHERE saddress NOT LIKE '山东%';

#查询成绩为79分、89分或99分的记录。
SELECT sno AS '学号', cno AS '课程号', degree AS '成绩'
FROM sc WHERE degree IN (79,89,99);

#查询名字中第二个字是“小”字的男生的学生姓名和地址。
SELECT sname AS '姓名', saddress AS '地址'
FROM student WHERE sname LIKE '_小%';

#查询名称以“计算机”开头的课程名称。
SELECT cname AS '课程名' FROM course WHERE cname LIKE '计算机%';

#查询计算机工程系和软件工程系的学生信息。
SELECT sno AS '学号', sname AS '姓名', ssex AS '性别', sbirthday AS '出生日期', saddress AS '生源地', sdept AS '系别', speciality AS '专业'
FROM student WHERE sdept IN ('计算机工程系','软件工程系');

#查询有考试成绩的课程号
SELECT DISTINCT course.cno AS '有考试成绩的课程号' FROM course RIGHT JOIN sc s on course.cno = s.cno WHERE degree != 0;

#查询数学系的男生信息
SELECT * FROM student WHERE ssex = '男' AND sdept = '数学系';


# 查询计算机工程系和数学系学生的姓名，性别，出生日期
SELECT sname AS '姓名', ssex AS '性别', DATE(sbirthday) AS '出生日期' FROM student WHERE sdept = '计算机系' OR sdept = '数学系';


# 查询所有姓李的学生的个人信息
SELECT * FROM student WHERE sname LIKE '李%';

#每个课程的最低分的统计
SELECT c.cname AS '课程' , IFNULL(MIN(s.degree),'无人选课') AS '最低分'
FROM sc s RIGHT JOIN gradem.course c on c.cno = s.cno GROUP BY s.cno;

#每个课程的平均分的统计
SELECT c.cname AS '课程' , IFNULL(AVG(s.degree),'无人选课') AS '平均分'
FROM sc s RIGHT JOIN gradem.course c on c.cno = s.cno GROUP BY s.cno;


#每个课程的总分的统计
SELECT c.cname AS '课程' , IFNULL(SUM(s.degree),'无人选课') AS '总分'
FROM sc s RIGHT JOIN gradem.course c on c.cno = s.cno GROUP BY s.cno;

#结合聚合函数以及各种连接嵌套来进行查询
#查询每个学生的平均成绩
SELECT d.sname AS '学生姓名', IFNULL(AVG(s.degree), '该学生未选课') AS '平均成绩'
FROM sc s
         RIGHT JOIN gradem.student d on d.sno = s.sno
GROUP BY d.sname;

#各系每门课的总成绩
SELECT d.sdept AS '系别', sum(s.degree) AS '总分'
FROM student d
         RIGHT JOIN gradem.sc s on s.sno = d.sno
GROUP BY d.sdept;

#各系每门课的平均成绩
SELECT d.sdept AS '系别', AVG(s.degree) AS '平均分'
FROM student d
         RIGHT JOIN gradem.sc s on s.sno = d.sno
GROUP BY d.sdept;

#每个课程的最高分的获取
SELECT c.cname AS '课程', IFNULL(MAX(s.degree), '无人选课') AS '最高分'
FROM sc s
         RIGHT JOIN gradem.course c on c.cno = s.cno
GROUP BY s.cno;

#每个课程的最低分的获取
SELECT c.cname AS '课程', IFNULL(MIN(s.degree), '无人选课') AS '最低分'
FROM sc s
         RIGHT JOIN gradem.course c on c.cno = s.cno
GROUP BY s.cno;

#每个课程的平均分的获取
SELECT c.cname AS '课程', IFNULL(AVG(s.degree), '无人选课') AS '平均分'
FROM sc s
         RIGHT JOIN gradem.course c on c.cno = s.cno
GROUP BY s.cno;

#统计不及格人数超过50的系别
SELECT d.sdept AS '系别', COUNT(s.degree) AS '不及格人数'
FROM student d
         RIGHT JOIN gradem.sc s on s.sno = d.sno
WHERE s.degree < 60
GROUP BY d.sdept
HAVING COUNT(s.degree) > 50;

#统计不及格人数超过50的课程号。
SELECT c.cno AS '课程号', COUNT(s.degree) AS '不及格人数'
FROM course c RIGHT JOIN gradem.sc s on  c.cno = s.cno
WHERE s.degree < 60
GROUP BY s.cno HAVING COUNT(s.degree) > 50;