实训24 使用子查询数据表记录——MySQL数据库

Ex24.1

• 导入school_DB数据库，做以下操作：
• 子查询
  – 1.查询教师所有信息及其所在部门名称和专业或课程组名称。（知识点：SELECT中使用子查询）
  – 2.查询课程体系中的所有信息及其专业名称、课程名称。
  – 3.查询所有班级信息及其专业名称和归属的院部名称。（知识点：内连接、子查询）
  – 4.查询所有学生信息及其班级名称、所属专业名称、归属院部名称。
  – 5.查询名称为“信息技术学院”开设的所有专业信息；所有开出的班级信息；所有2022级学生。（知识点：WHERE中使用子查询）
  – 6.统计各班学生人数赋值班级表中NUM字段班级人数。（知识点：UPDATE语句、子查询、聚合函数）

# 子查询
# 1.查询教师所有信息及其所在部门名称和专业或课程组名称。（知识点：SELECT中使用子查询）
SELECT teachers.*,
	(SELECT dname FROM departs WHERE teachers.departid=departs.id) AS dname,
	(SELECT mname FROM majors WHERE teachers.majorid=majors.id) AS mname
FROM teachers;

# 2.查询课程体系中的所有信息及其专业名称、课程名称。
SELECT major_grade_term_courses.*,
	(SELECT mname FROM majors WHERE major_grade_term_courses.majorid=majors.id) mname,
	(SELECT cname FROM courses WHERE major_grade_term_courses.courseid=courses.id) cname
FROM major_grade_term_courses;

# 3.查询所有班级信息及其专业名称和归属的院部名称。（知识点：内连接、子查询）
SELECT mclasss.*,majors.mname,
	(SELECT dname FROM departs WHERE departs.id=mclasss.majorid) dname
FROM mclasss,majors 
WHERE mclasss.majorid=majors.id;

# 4.查询所有学生信息及其班级名称、所属专业名称、归属院部名称。
SELECT students.*, mclasss.mname class_name,majors.mname major_name,
	(SELECT dname FROM departs WHERE departs.id=majors.departid)  depart_name
FROM students,mclasss,majors
WHERE students.mclassid=mclasss.id AND majors.id=mclasss.majorid;

# 5.查询名称为“信息技术学院”开设的所有专业信息；所有开出的班级信息；所有2022级学生。（知识点：WHERE中使用子查询）
SELECT * FROM majors 
WHERE departid IN (SELECT id FROM departs WHERE dname='信息技术学院');

SELECT * FROM mclasss 
WHERE majorid IN (
	SELECT id FROM majors WHERE departid IN (
		SELECT id FROM departs WHERE dname='信息技术学院'));
		
SELECT * FROM students 
WHERE mclassid IN(
	SELECT id FROM mclasss 
		WHERE grade='2022' AND majorid IN (
			SELECT id FROM majors WHERE departid IN (
				SELECT id FROM departs WHERE dname='信息技术学院')));
				
# 6.统计各班学生人数赋值给班级表中NUM字段班级人数。（知识点：UPDATE语句、子查询、聚合函数）
UPDATE  mclasss 
INNER JOIN (SELECT mclassid,COUNT(*) cnt FROM students GROUP BY mclassid) students_t 
ON students_t.mclassid=mclasss.id
SET num=students_t.cnt;