2020-08-14

2020-08-14
mysql的一些题
student表
题目

1. 列出至少有两个学生的班级的信息:(班级名称,编辑编号,班级人数,平均分)
2. 列出班级所有班级的信息,按平均分排序(没有学生的班级也需要列出来)
3. 列出分数和李青所在班级所有学生不同的学生的信息(NOT IN)
4. 列出所有学生的信息即其组长的姓名
5. 列出比其组长大的所有学生的信息
6. 列出和李青一个班级的所有学生的信息
7. 列出比李青所在班级的学生的分数都高的所有学生的信息
8. 列出所有班级的信息即其扳机最高分学生的信息
9. 列出分数高于平均分的所有学生的信息及其班级信息和组长的信息
   10.列出所有学生的信息即等级名称
   
   等级表
   
   班级表
   

-- 创建一个student表
CREATE TABLE student(
	sid INT auto_increment PRIMARY key,
	sname VARCHAR(4),
	score FLOAT(4,1),
	cid INT,
	sbirthday DATE,
	zid INT
);

-- 创建一个class表
CREATE TABLE class(
	cid INT auto_increment PRIMARY KEY,
	cname VARCHAR(10)
);

-- 创建一个dengji表
CREATE TABLE dengji(
	hightscore FLOAT(4,1),
	lowscore FLOAT(4,1),
	levelname VARCHAR(3) PRIMARY KEY
);
-- ===================================
INSERT INTO student VALUES(1001,'李青',70,2,'1998-05-14',1000);
-- 1 列出至少有两个学生的班级的信息:(班级名称,编辑编号,班级人数,平均分)
SELECT
	c.cname,
	c.cid,
	COUNT(s.cid),
	AVG(s.score)
FROM
	class c,
	student s
WHERE
	s.cid = c.cid
GROUP BY
	cid
HAVING
	COUNT(c.cid) > 2;
-- 2列出班级所有班级的信息,按平均分排序(没有学生的班级也需要列出来)
SELECT
	c.*, AVG(s.score) avg
FROM
	class c
LEFT JOIN student s ON s.cid = c.cid
GROUP BY
	cid
ORDER BY
	avg;
-- 3 列出分数和李青所在班级所有学生不同的学生的信息(NOT IN)
SELECT
	*
FROM
	student
WHERE
	score NOT IN (
		SELECT
			score
		FROM
			student
		WHERE
			cid = (
				SELECT
					cid
				FROM
					student
				WHERE
					sname = '李青'
			)
	);
-- 4 列出所有学生的信息即其组长的姓名
SELECT
	s.sid,
	s.sname,
	s.score,
	s.sbirthday,
	s.cid,
	zc.sname
FROM
	student s,
	(
		SELECT
			sid,
			sname
		FROM
			student
		WHERE
			ISNULL(zid)
	) zc
WHERE
	s.zid = zc.sid;
-- 5 列出比其组长大的所有学生的信息
SELECT
	s.*, c.cname
FROM
	class c,
	(
		SELECT
			s1.*
		FROM
			student s1,
			(
				SELECT
					sbirthday,
					sid
				FROM
					student
				WHERE
					ISNULL(zid)
			) zc
		WHERE
			s1.zid = zc.sid
		AND s1.sbirthday < zc.sbirthday
	) s
WHERE
	s.cid = c.cid;
-- 6 列出和李青一个班级的所有学生的信息
SELECT
	*
FROM
	student
WHERE
	cid = (
		SELECT
			cid
		FROM
			student
		WHERE
			sname = '李青'
	)
AND sname != '李青';
-- 7列出比李青所在班级的学生的分数都高的所有学生的信息
SELECT
	*
FROM
	(
		SELECT
			*
		FROM
			student
		WHERE
			score > (
				SELECT
					MAX(score)
				FROM
					student
				WHERE
					cid = (
						SELECT
							cid
						FROM
							student
						WHERE
							sname = '李青'
					)
			)
	) a;
-- 8列出所有班级的信息即其扳机最高分学生的信息
SELECT c.*, s2.* FROM  class c,
	(SELECT s1.* FROM student s1,
(SELECT MAX(score) max,s.cid FROM	student s GROUP BY s.cid)ms 
WHERE s1.cid = ms.cid AND s1.score=ms.max) s2 WHERE c.cid = s2.cid;

-- SELECT cid,MAX(score) FROM student  GROUP BY cid;
-- 9 列出分数高于平均分的所有学生的信息及其班级信息和组长的信息
SELECT
	s1.*, c.cname,
	s2.*
FROM
	student s1,
	class c,
	student s2,
	(
		SELECT
			AVG(s.score) avg,
			s.cid
		FROM
			student s
		GROUP BY
			s.cid
	) a
WHERE
	s1.score > a.avg
AND s1.cid = a.cid
AND s1.cid = c.cid
AND s1.zid = s2.sid;
-- 10 列出所有学生的信息即等级名称
SELECT
	s.*, d.levelname
FROM
	student s,
	dengji d
WHERE
	s.score > d.lowscore
AND s.score <= d.hightscore;