阅读量:0
MySQL数据库练习包括创建数据库、表，进行增删改查操作，以及学习索引、视图和存储过程等高级功能。
MySQL是一种流行的开源关系型数据库管理系统，广泛应用于各类Web应用和企业系统中，掌握MySQL的基本操作和查询技能是每个数据库开发者的必备能力，以下是MySQL练习题的介绍及解答：
1、创建表
学生表：CREATE TABLE Student (SId VARCHAR(10), Sname VARCHAR(10), Sage DATE, Ssex VARCHAR(10));
课程表：CREATE TABLE Course (CId VARCHAR(10), Cname VARCHAR(20), TId VARCHAR(10));
教师表：CREATE TABLE Teacher (TId VARCHAR(10), Tname VARCHAR(20));
成绩表：CREATE TABLE SC (SId VARCHAR(10), CId VARCHAR(10), Score DECIMAL(5,2));
2、插入数据
学生表：INSERT INTO Student VALUES ('01', '赵雷', '19900101', '男');
课程表：INSERT INTO Course VALUES ('01', '语文', '02');
教师表：INSERT INTO Teacher VALUES ('02', '李四');
成绩表：INSERT INTO SC VALUES ('01', '01', 80);
3、经典练习题
查询“01”课程比“02”课程成绩高的学生信息及课程分数
```sql
SELECT a.*, b.Score AS Score_01, c.Score AS Score_02
FROM Student a
JOIN SC b ON a.SId = b.SId AND b.CId = '01'
JOIN SC c ON a.SId = c.SId AND c.CId = '02'
WHERE b.Score > c.Score;
```
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
```sql
SELECT SId, Sname, AVG(Score) AS Avg_Score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
GROUP BY SId, Sname
HAVING Avg_Score >= 60;
```
查询存在“01”课程但可能不存在“02”课程的情况（不存在时显示为NULL）
```sql
SELECT a.*, b.Score AS Score_01, c.Score AS Score_02
FROM Student a
LEFT JOIN SC b ON a.SId = b.SId AND b.CId = '01'
LEFT JOIN SC c ON a.SId = c.SId AND c.CId = '02';
```
查询至少有一门课与学号为“01”的同学所学相同的同学的信息
```sql
SELECT DISTINCT a.
FROM Student a
JOIN SC a_sc ON a.SId = a_sc.SId
JOIN SC b_sc ON a_sc.CId = b_sc.CId AND a.SId <> b_sc.SId AND b_sc.SId = '01';
```
查询各科成绩最高分、最低分和平均分
```sql
SELECT Cname, MAX(Score) AS Max_Score, MIN(Score) AS Min_Score, AVG(Score) AS Avg_Score,
SUM(CASE WHEN Score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS Pass_Rate,
SUM(CASE WHEN Score BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(*) AS Medium_Rate,
SUM(CASE WHEN Score BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(*) AS Good_Rate,
SUM(CASE WHEN Score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS Excellent_Rate
FROM Course c
JOIN SC ON c.CId = SC.CId
GROUP BY Cname;
```
按各科平均成绩进行排序，并显示排名
```sql
SELECT Cname, AVG(Score) AS Avg_Score,
RANK() OVER (ORDER BY AVG(Score) DESC) AS Rank
FROM Course c
JOIN SC ON c.CId = SC.CId
GROUP BY Cname;
```
查询学生的总成绩，并进行排名，总分重复时保留名次空缺
```sql
SELECT SId, Sname, SUM(Score) AS Total_Score,
DENSE_RANK() OVER (ORDER BY SUM(Score) DESC) AS Rank
FROM Student s
JOIN SC sc ON s.SId = sc.SId
GROUP BY SId, Sname;
```
统计各科成绩各分数段人数
```sql
SELECT Cname,
SUM(CASE WHEN Score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [85100],
SUM(CASE WHEN Score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS [7084],
SUM(CASE WHEN Score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS [6069],
SUM(CASE WHEN Score < 60 THEN 1 ELSE 0 END) AS [Below_60]
FROM Course c
JOIN SC ON c.CId = SC.CId
GROUP BY Cname;
```
查询每门课程被选修的学生数
```sql
SELECT Cname, COUNT(DISTINCT SId) AS Student_Count
FROM Course c
JOIN SC ON c.CId = SC.CId
GROUP BY Cname;
```
查询出只选修两门课程的学生学号和姓名
```sql
SELECT SId, Sname
FROM Student s
JOIN (SELECT SId, COUNT(DISTINCT CId) AS Course_Count
FROM SC
GROUP BY SId) sc ON s.SId = sc.SId
WHERE sc.Course_Count = 2;
```
查询男生、女生人数
```sql
SELECT Ssex, COUNT(*) AS Count
FROM Student
GROUP BY Ssex;
```
查询名字中含有“风”字的学生信息
```sql
SELECT * FROM Student
WHERE Sname LIKE '%风%';
```
查询同名同性学生名单，并统计同名同性人数
```sql
SELECT Sname, Ssex, COUNT(*) AS Count
FROM Student
GROUP BY Sname, Ssex
HAVING COUNT(*) > 1;
```
查询某年某月出生的学生名单
```sql
SELECT * FROM Student
WHERE YEAR(Sage) = 1990 AND MONTH(Sage) = 1;
```
查询本周过生日的学生
```sql
SELECT * FROM Student
WHERE WEEK(Sage, CURRENT_DATE()) = WEEK(CURRENT_DATE(), CURRENT_DATE());
```
查询本月过生日的学生
```sql
SELECT * FROM Student
WHERE MONTH(Sage) = MONTH(CURRENT_DATE());
```
查询下月过生日的学生
```sql
SELECT * FROM Student
WHERE MONTH(Sage) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH));
```
查询各学生的年龄，只按年份来算
```sql
SELECT SId, Sname, YEAR(CURRENT_DATE()) YEAR(Sage) AS Age
FROM Student;
```
按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一
```sql
SELECT SId, Sname, CASE WHEN CONCAT(MONTH(CURRENT_DATE()), '', DAY(CURRENT_DATE())) < CONCAT(MONTH(Sage), '', DAY(Sage)) THEN YEAR(CURRENT_DATE()) YEAR(Sage) 1 ELSE YEAR(CURRENT_DATE()) YEAR(Sage) END AS Age
FROM Student;
```
检索"01"课程分数小于60，按分数降序排列的学生信息
```sql
SELECT a.*, b.Score AS Score_01
FROM Student a
JOIN SC b ON a.SId = b.SId AND b.CId = '01'
WHERE b.Score < 60
ORDER BY b.Score DESC;
```
查询课程编号为“01”且课程成绩在80分以上的学生的学号和姓名
```sql
SELECT SId, Sname
FROM Student a
JOIN SC b ON a.SId = b.SId AND b.CId = '01'
WHERE b.Score > 80;
```
求每门课程的学生人数
```sql
SELECT Cname, COUNT(DISTINCT SId) AS Student_Count
FROM Course c
JOIN SC ON c.CId = SC.CId
GROUP BY Cname;
```
成绩不重复，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
```sql
SELECT a.*, b.Score AS Score_Zhangsan
FROM Student a
JOIN SC b ON a.SId = b.SId AND b.CId IN (SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname = '张三'))
WHERE NOT EXISTS (SELECT 1 FROM SC b1 WHERE b1.SId = a.SId AND b1.CId = b.CId AND b1.Score > b.Score)
AND b.Score IS NOT NULL;
```
成绩有重复的情况下，查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
```sql
SELECT a.*, b.Score AS Score_Zhangsan
FROM Student a
JOIN SC b ON a.SId = b.SId AND b.CId IN (SELECT CId FROM Course WHERE TId = (SELECT TId FROM Teacher WHERE Tname = '张三'))
WHERE b.Score = (SELECT MAX(b1.Score) FROM SC b1 WHERE b1.CId = b.CId)
AND b.Score IS NOT NULL;
```
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
```sql
SELECT SId, CId, Score
FROM SC a
WHERE (SId, Score) IN (SELECT SId, Score FROM SC b WHERE a.CId <> b.CId);
```
查询每门课程成绩最好的前两名
```sql
SELECT Cname, SId, Score, DENSE_RANK() OVER (PARTITION BY CId ORDER BY Score DESC) AS Rank
FROM Course c
JOIN SC ON c.CId = SC.CId;
```
统计每门课程的学生选修人数（超过5人的课程才统计）
```sql
SELECT Cname, COUNT(DISTINCT SId) AS Student_Count
FROM Course c
JOIN SC ON c.CId = SC.CId
GROUP BY Cname
HAVING COUNT(DISTINCT SId) > 5;
```
检索至少选修两门课程的学生学号
```sql
SELECT SId, Sname, COUNT(DISTINCT CId) AS Course_Count
FROM Student a
JOIN SC ON a.SId = SC.SId
GROUP BY SId, Sname
HAVING COUNT(DISTINCT CId) >= 2;
```
查询选修了全部课程的学生信息
```sql
SELECT a.
FROM Student a
WHERE NOT EXISTS (SELECT 1 FROM Course b WHERE NOT EXISTS (SELECT 1 FROM SC c WHERE c.SId = a.SId AND c.CId = b.CId));
```
查询各学生的年龄，只按年份来算
```sql
SELECT SId, Sname, YEAR(CURRENT_DATE()) YEAR(Sage) AS Age
FROM Student;
```
按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一
```sql
SELECT SId, Sname, CASE WHEN CONCAT(MONTH(CURRENT_DATE()), '', DAY(CURRENT_DATE())) < CONCAT(MONTH(Sage), '', DAY(Sage)) THEN YEAR(CURRENT_DATE()) YEAR(Sage) 1 ELSE YEAR(CURRENT_DATE()) YEAR(Sage) END AS Age
FROM Student;
```
查询本周过生日的学生
```sql
SELECT * FROM Student
WHERE WEEK(Sage, CURRENT_DATE()) = WEEK(CURRENT_DATE(), CURRENT_DATE());
```
查询下周过生日的学生
```sql
SELECT * FROM Student
WHERE WEEK(DATE_ADD(Sage, INTERVAL 1 WEEK), CURRENT_DATE()) = WEEK(CURRENT_DATE(), CURRENT_DATE());
```
查询本月过生日的学生
```sql
SELECT * FROM Student
WHERE MONTH(Sage) = MONTH(CURRENT_DATE());
```
查询下月过生日的学生
```sql
SELECT * FROM Student
WHERE MONTH(Sage) = MONTH(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH));
```
