首页 > 代码库 > mysql练手

mysql练手

2024-08-12 23:14:12   219人阅读

1、根据图创建下列表格

技术分享

　　没有外键的表先创建，创建顺序为teacher，class，course，student

CREATE TABLE class (	cid INT NOT NULL auto_increment PRIMARY KEY,	caption CHAR (32) NOT NULL) ENGINE = INNODB DEFAULT charset = utf8;INSERT INTO score (caption)VALUES	(‘三年二班‘),	(‘一年三班‘),	(‘三年一班‘) ；

 

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号

CREATE VIEW vw1 AS SELECT	score.student_id AS "学号",	student.sname AS "姓名",	course.cname AS "科目",	number AS "生物分数"FROM	scoreLEFT JOIN course ON course.cid = score.course_idLEFT JOIN student ON score.student_id = student.sidWHERE	course.cname = ‘生物‘;CREATE VIEW vw2 AS SELECT	score.student_id AS "学号",	student.sname AS "姓名",	course.cname AS "科目",	number AS "物理分数"FROM	scoreLEFT JOIN course ON course.cid = score.course_idLEFT JOIN student ON score.student_id = student.sidWHERE	course.cname = ‘物理‘;SELECT	*FROM	vw1INNER JOIN vw2 ON vw1.学号 = vw2.学号WHERE	vw1.生物分数 > vw2.物理分数;

 

 

 　　我们发现按照当前的表结构，没有符合上述条件的学生，不具可比性，但是如果数据量扩大时，具备可比性了，那应该怎么写呢？

 

3、查询平均成绩大于60分的同学的学号和平均成绩

SELECT	score.student_id AS "学号",	student.sname AS "姓名",	sum(score.number) AS "总分数",	avg(score.number) AS gvaFROM	scoreLEFT JOIN course ON course.cid = score.course_idLEFT JOIN student ON score.student_id = student.sidGROUP BY	student_idHAVING	gva > 60;

 

4、查询所有同学的学号、姓名、选课数、总成绩

SELECT	score.student_id AS "学号",	student.sname AS "姓名",	sum(score.number) AS "总成绩",	count(score.course_id) AS ‘课程数‘FROM	scoreLEFT JOIN course ON course.cid = score.course_idLEFT JOIN student ON score.student_id = student.sidGROUP BY	student_id

 

5、查询姓“李”的老师的个数

SELECT	count(tname) AS "个数"FROM	teacherWHERE	tname LIKE "波%" ;

 

6、查询没学过“叶平”老师课的同学的学号、姓名

　　思路：没学过某个老师，我可以找到学过这个老师的学生，并在学生表判断，排除这些学过的就是没学过了

　　　　（学生学的课程id in （先找叶平老师教的课程id））

　　　　最后只要排除 not in这群学生就可了

SELECT	*FROM	studentWHERE	sid NOT IN (		SELECT DISTINCT			student_id		FROM			score		WHERE			course_id IN (				SELECT					cid				FROM					course				LEFT JOIN teacher ON teacher_id = tid				WHERE					tname = "波多"			)	)

 

 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

　　思路：先查出所有学了 001 或 002 的学生 course_id  in (001,002)

　　　　然后group by  学生id，having进行科目数统计，等于2的就是符合条件的

SELECT	student_id,	snameFROM	studentLEFT JOIN score ON student_id = student.sidWHERE	course_id IN (1, 2)GROUP BY	student_idHAVING	count(student_id) = 2;

 

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名

SELECT	*FROM	studentWHERE	student.sid IN (		SELECT DISTINCT			student_id		FROM			score		WHERE			course_id IN (				SELECT					cid				FROM					course				LEFT JOIN teacher ON teacher.tid = course.teacher_id				WHERE					teacher.tname = ‘饭岛‘			)	);

 

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

SELECT	id1,	NAMEFROM	(		SELECT			student_id AS id1,			number AS number1,			student.sname AS NAME		FROM			score		LEFT JOIN student ON student.sid = score.student_id		WHERE			score.course_id = 1	) AS ALEFT JOIN (	SELECT		student_id AS id2,		number AS number2	FROM		score	LEFT JOIN student ON student.sid = score.student_id	WHERE		score.course_id = 2) AS B ON A.id1 = B.id2WHERE	number1 > number2;

 

10、查询有课程成绩小于60分的同学的学号、姓名

SELECT DISTINCT	student.sid,	snameFROM	studentLEFT JOIN score ON student.sid = score.student_idWHERE	student.sid IN (		SELECT			student_id		FROM			score		WHERE			number < 60	);

 

11、查询没有学全所有课的同学的学号、姓名

SELECT	sid,	snameFROM	studentWHERE	sid IN (		SELECT			student_id		FROM			score		GROUP BY			student_id		HAVING			count(student_id) = 3	);

 

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

　　思路：先找到001同学的course---一个元组

　　　　　　course_id in 元组  ---student id元组

　　　　　　sid in student id元组

SELECT DISTINCT	sid,	snameFROM	studentWHERE	sid IN (		SELECT			student_id		FROM			score		WHERE			course_id IN (				SELECT					course_id				FROM					score				WHERE					student_id = 1			)	)AND sid != 1;

 

13、查询学过学号为“001”同学所有一门课的其他同学学号和姓名

SELECT	sid,	snameFROM	studentWHERE	sid IN (		SELECT			student_id		FROM			score		WHERE			course_id IN (				SELECT					course_id				FROM					score				WHERE					student_id = 1			)		GROUP BY			student_id		HAVING			count(student_id) = 1	)AND sid != 1;

 

mysql练手


联系
我们