MySQL经典45题

最新推荐文章于 2024-09-12 01:15:37 发布

还没想好~

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量220

点赞数

文章标签： mysql 数据分析

本文链接：https://blog.csdn.net/LiuXF93/article/details/108654422

版权

建表+数据
建表语句
Student(sid,Sname,Sage,Ssex) 学生表

CREATE TABLE Student(
	sId varchar(10) NOT NULL ,
	sName varchar(20) DEFAULT NULL,
	sAge date DEFAULT '1990-01-01',
	sSex ENUM('男','女'),
	PRIMARY KEY (sId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Course(cid,Cname,tid) 课程表

CREATE TABLE Course(
	cId varchar(10) NOT NULL ,
	cName varchar(20) DEFAULT NULL,
	tId varchar(20) DEFAULT NULL,
	PRIMARY KEY (cId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SC(sid,cid,score) 成绩表

CREATE TABLE SC(
	sId varchar(10) NOT NULL ,
	cId varchar(10) NOT NULL ,
	score int(10) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Teacher(tid,Tname) 教师表

CREATE TABLE Teacher(
	tId varchar(10) NOT NULL ,
	tName varchar(20) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

增加数据

INSERT INTO teacher(tId,tName) VALUES 
(1,'李老师'),(2,'何以琛'),(3,'叶平');
INSERT INTO SC(sId,cId,score) VALUES 
('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),('1002','002',70),
('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);
INSERT INTO course(cId,cName,tId) VALUES
('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
INSERT INTO student(sId,sName,sAge,sSex) VALUES
('1001','张三丰','1980-10-12','男'),('1002','张无极','1995-10-12','男'),('1003','李奎','1992-10-12','女'),
('1004','李元宝','1980-10-12','女'),('1005','李世明','1981-10-12','男'),('1006','赵六','1986-10-12','男'),
('1007','田七','1981-10-12','女');

1.查询“001”课程比“002”课程成绩高的所有学生的学号;
– 方法一

SELECT *
FROM 
	(SELECT * FROM sc WHERE cId ="001") AS table1,
	(SELECT * FROM sc WHERE cId ="002") AS table2
WHERE 
	table1.score > table2.score AND table1.sId = table2.sId

– 方法二

SELECT TABLE1.sId "学生编号",table3.sName  "学生姓名",table1.score "001课程成绩", table2.score "002成绩"
FROM 
	(SELECT * FROM sc WHERE cId = "001") AS table1
	INNER JOIN 
	(SELECT * FROM sc WHERE cId = "002") AS table2
	ON table1.sId =table2.sId
	INNER JOIN student AS table3
	ON table1.sId = table3.sId
WHERE table1.score > table2.score

2.查询平均成绩大于60分的同学的学号和平均成绩;

SELECT 
	sId, avg(score)AS avg_score
FROM sc
GROUP BY sId 
HAVING  avg(score) >= 60

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

SELECT
	student.sId,
	MAX(student.sName),
	COUNT(sc.cId) cidnum,
	SUM(sc.score) score
FROM
	student
LEFT JOIN sc ON student.sId = sc.sId
GROUP BY
	student.sId

4.查询姓“李”的老师的个数;

SELECT 
	sum(teacher.tId) 
FROM 
	teacher
WHERE  teacher.tName LIKE  '李%'

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

SELECT student.sId,student.sName from student  where student.sId 
NOT IN (
select student.sId from student
LEFT JOIN sc on sc.sId = student.sId
LEFT JOIN course on course.cId = sc.cId
LEFT JOIN teacher on teacher.tId = course.tId
WHERE teacher.tName = '叶平'
)

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

SELECT  sId, sName
FROM student 
WHERE sId IN 
(SELECT sId 
FROM sc 
WHERE cId = '001' AND '002')

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

SELECT student.sId,student.sName from student  where student.sId 
IN (
select student.sId from student
LEFT JOIN sc on sc.sId = student.sId
LEFT JOIN course on course.cId = sc.cId
LEFT JOIN teacher on teacher.tId = course.tId
WHERE teacher.tName = '叶平')

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

SELECT 
	student.sId, student.sName 
FROM 
	(SELECT sc.sId  ,sc.score FROM sc WHERE sc.cId = '001') AS table1
	LEFT JOIN 
	(SELECT sc.sId ,sc.score FROM sc WHERE sc.cId = '002') AS table2
	ON table1.sId=table2.sId
	LEFT JOIN student ON student.sId =  table2.sId
WHERE 
	table1.score > table2.score

/9.查询所有课程成绩小于60分的同学的学号、姓名;/
– 第一种情况：只考虑有成绩的学生

SELECT 
	student.sId, student.sName 
FROM 
	(SELECT sc.sId  ,sc.score FROM sc WHERE sc.cId = '001') AS table1
	LEFT JOIN 
	(SELECT sc.sId ,sc.score FROM sc WHERE sc.cId = '002') AS table2
	ON table1.sId=table2.sId
	LEFT JOIN student ON student.sId =  table2.sId
WHERE 
	table1.score < 60 AND table2.score < 60

– 第二种情况：没有成绩的学生均被视为成绩小于60分

SELECT 
	sId ,sName
FROM 
	student 
WHERE
	sId NOT IN (SELECT sc.sId FROM sc WHERE sc.score > 60)

10.查询没有学全所有课的同学的学号、姓名;

SELECT 
	student.sId ,student.sName 
FROM 
	student 
WHERE   student.sId
NOT IN (SELECT sc.sId FROM sc GROUP BY sc.sId HAVING count(sc.sId) =3)

11.查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
– 方法1

SELECT student.sId ,student.sName 
FROM student
WHERE student.sId IN 
	(SELECT sc.sId 
	FROM sc 
	WHERE sc.cId IN (SELECT sc.cId FROM sc WHERE sc.sId = '1001'))
AND student.sId != '1001'

– 方法2

SELECT 
	student.sId ,student.sName 
FROM 
	student
LEFT JOIN sc ON student.sId = sc.sId 
WHERE 
	sc.cId IN (SELECT 
					sc.cId 
				FROM 
					sc 
				WHERE 
					sc.sId = '1001'
				)
AND sc.sId != '1001'
GROUP BY 
	student.sId

12.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

UPDATE sc, 	
	(SELECT 
		c.cId 
		,AVG(score) avgs 
	FROM 
		teacher t2 ,course c , sc s 
	WHERE 
		s.cId = c.cId 
	AND c.tId = t2.tId 
	AND t2.tName = '叶平'
	GROUP BY cId
	) AS sc_2
SET sc.score = sc_2.avgs
WHERE sc.cId = sc_2.cId

未完待续—2020.9.20