数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `s_score` int(3) NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

题目

208211112_31_20201124120018728

答案

1.
SELECT student.*,b.s_score as '课程1',c.s_score as '课程2' FROM student
INNER JOIN score b ON student.s_id=b.s_id AND b.c_id='01'
INNER JOIN score c ON student.s_id=c.s_id AND c.c_id='02'
WHERE b.s_score>c.s_score

2.
SELECT student.*,b.s_score as '课程1',c.s_score as '课程2' FROM student
INNER JOIN score b ON b.s_id=student.s_id AND b.c_id='01'
INNER JOIN score c ON c.s_id=student.s_id AND c.c_id='02'
WHERE b.s_score<c.s_score

3.
SELECT student.s_id,student.s_name,ROUND(AVG(score.s_score),2) AS avg_score
FROM student
INNER JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_score >= 60

4.
SELECT student.s_id,student.s_name,ROUND(AVG(IFNULL(score.s_score,0)),2) AS avg_score
FROM student
LEFT JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_score < 60
-----或者-----
SELECT student.s_id,student.s_name,ROUND(AVG(score.s_score),2) AS avg_score
FROM student
LEFT JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_score < 60 OR avg_score IS NULL

5.
SELECT student.s_id,student.s_name,COUNT(score.c_id) as '选课总数',SUM(score.s_score) as '总成绩'
from student
LEFT JOIN score ON student.s_id=score.s_id
GROUP BY student.s_id,student.s_name

6.
SELECT COUNT(teacher.t_name) FROM teacher WHERE teacher.t_name LIKE '李%'

7.
SELECT student.*,teacher.t_name,course.c_name FROM student
LEFT JOIN score on score.s_id = student.s_id
LEFT JOIN course on course.c_id = score.c_id
LEFT JOIN teacher ON  teacher.t_id = course.t_id
WHERE teacher.t_name = '张三'

8.
SELECT * FROM student
WHERE student.s_id not in(
    # 学生学过张三的课(上题得出)
    SELECT student.s_id FROM student
        LEFT JOIN score on score.s_id = student.s_id
        LEFT JOIN course on course.c_id = score.c_id
        LEFT JOIN teacher ON  teacher.t_id = course.t_id
        WHERE teacher.t_name = '张三'
)

9.
SELECT student.*,s1.s_score AS '01分数',s2.s_score AS '02分数'
FROM student
LEFT JOIN score s1 ON s1.s_id = student.s_id
LEFT JOIN score s2 ON s2.s_id = student.s_id 
WHERE s1.c_id = '01' AND s2.c_id = '02'

10.
SELECT student.*,s1.s_score AS '01分数',s2.s_score AS '02分数'
FROM student
INNER JOIN score s1 ON s1.s_id = student.s_id AND s1.c_id = '01'
LEFT JOIN score s2 ON s2.s_id = student.s_id AND s2.c_id = '02'
WHERE s2.s_score IS NULL

11.
SELECT * FROM student
WHERE student.s_id IN (
    SELECT score.s_id FROM score GROUP BY score.s_id HAVING COUNT(score.s_score)<3
)

12.
SELECT * FROM student WHERE student.s_id IN (
    SELECT DISTINCT score.s_id FROM score WHERE score.c_id IN(
        SELECT score.c_id FROM score WHERE score.s_id='01'
    )
)

13.
SELECT * FROM student WHERE student.s_id IN (
    SELECT score.s_id FROM score WHERE score.c_id IN(
        SELECT score.c_id FROM score WHERE score.s_id='01'
    )AND score.s_id != '01' GROUP BY score.s_id HAVING COUNT(score.c_id)>=3
)

14.
SELECT * FROM student 
WHERE student.s_id NOT IN(
    SELECT score.s_id FROM score
    INNER JOIN course ON course.c_id = score.c_id
    INNER JOIN teacher ON teacher.t_id = course.t_id
    WHERE teacher.t_name = '张三'
)

15.
SELECT student.s_id,student.s_name,AVG(score.s_score) AS AVG_SCORE FROM student
INNER JOIN score on student.s_id = score.s_id
WHERE student.s_id in (
    SELECT score.s_id FROM score WHERE score.s_score<60
)
GROUP BY student.s_id

16.
SELECT student.*,score.s_score AS 01_score FROM student
INNER JOIN score ON student.s_id = score.s_id AND score.s_score<60
WHERE score.c_id = '01'
ORDER BY 01_score DESC

17.
SELECT
    student.*,
    01_score.s_score AS 01_score,
    02_score.s_score AS 02_score,
    03_score.s_score AS 03_score,
    AVG( sc.s_score ) AS AVG_score 
FROM
    student
    LEFT JOIN score 01_score ON student.s_id = 01_score.s_id 
    AND 01_score.c_id = '01'
    LEFT JOIN score 02_score ON student.s_id = 02_score.s_id 
    AND 02_score.c_id = '02'
    LEFT JOIN score 03_score ON student.s_id = 03_score.s_id 
    AND 03_score.c_id = '03'
    LEFT JOIN score sc ON student.s_id = sc.s_id 
GROUP BY
    student.s_id 
ORDER BY
    AVG_score DESC
    
18.
SELECT
    score.c_id,
    course.c_name,
    MAX( score.s_score ) AS max_sc,
    MIN( score.s_score ) AS min_sc,
    AVG( score.s_score ) AS avg_sc 
FROM
    score
    INNER JOIN course ON course.c_id = score.c_id 
GROUP BY
    score.c_id

19.
SET @scrank = 0;
SELECT
    student.*,
    score.c_id,
    score.s_score,
    @scrank := @scrank + 1 AS sc_rank 
FROM
    student
    INNER JOIN score ON score.s_id = student.s_id 
ORDER BY
    score.c_id,
    score.s_score DESC

20.
SET @scrank = 0;
SELECT
    student.*,
    SUM( score.s_score ) AS sum_sc,
    @scrank := @scrank + 1 AS sc_rank 
FROM
    student
    INNER JOIN score ON score.s_id = student.s_id 
GROUP BY
    student.s_id 
ORDER BY
    sum_sc DESC

21.
SELECT
    teacher.*,
    course.c_name,
    AVG( score.s_score ) AS avg_sc 
FROM
    teacher
    LEFT JOIN course ON course.t_id = teacher.t_id
    LEFT JOIN score ON score.c_id = course.c_id 
GROUP BY
    teacher.t_id,
    course.c_name 
ORDER BY
    avg_sc DESC
    
22.
(SELECT st.*,sc.c_id,sc.s_score FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '01' ORDER BY sc.s_score DESC LIMIT 1,2)
UNION ALL
(SELECT st.*,sc.c_id,sc.s_score FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '02' ORDER BY sc.s_score DESC LIMIT 1,2)
UNION ALL
(SELECT st.*,sc.c_id,sc.s_score FROM score sc,student st WHERE sc.s_id = st.s_id AND sc.c_id = '03' ORDER BY sc.s_score DESC LIMIT 1,2)

23.
SELECT c.c_id,c.c_name 
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=100 AND sc.s_score>80)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "100-85"
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=85 AND sc.s_score>70)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "85-70"
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=70 AND sc.s_score>60)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "70-60"
,((SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id AND sc.s_score<=60 AND sc.s_score>=0)/(SELECT COUNT(*) FROM score sc WHERE sc.c_id=c.c_id )) "60-0"
FROM course c ORDER BY c.c_id

24.
SELECT
    student.s_id,
    student.s_name,
    ROUND(( CASE WHEN AVG( score.s_score ) IS NULL THEN 0 ELSE AVG( score.s_score ) END ), 2 ) AS avg_sc 
FROM
    student
    INNER JOIN score ON score.s_id = student.s_id 
GROUP BY
    score.s_id 
ORDER BY
    avg_sc DESC
    
25.
SELECT
    sc1.* 
FROM
    score sc1 
WHERE
    ( SELECT COUNT(*) FROM score sc2 WHERE sc1.c_id = sc2.c_id AND sc1.s_score < sc2.s_score )< 3 
ORDER BY
    sc1.c_id,
    sc1.s_score DESC

26.
SELECT score.c_id,course.c_name,COUNT(score.s_id) AS stu_num FROM score,course
WHERE course.c_id = score.c_id
GROUP BY score.c_id

27.
SELECT student.s_id,student.s_name 
FROM student
WHERE student.s_id IN (
    SELECT score.s_id FROM score 
    GROUP BY score.s_id 
    HAVING COUNT(score.s_id) =2 
)

28.
SELECT student.s_sex,COUNT(student.s_sex) FROM student GROUP BY student.s_sex

29.
SELECT student.* FROM student WHERE student.s_name LIKE '%风%'

30.
# 为了重名,加一条学生信息
# INSERT INTO `practice_test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('09', '李云', '1992-01-02', '女')
# sql语句如下
SELECT student.s_name,COUNT(*) FROM student GROUP BY student.s_name HAVING COUNT(*)>1

31.
SELECT student.* FROM student WHERE student.s_birth LIKE '1990%'

32.
# 为了有平均分相同的情况,在分数表(score)中插入两条数据,
# s_id  c_id  s_score
# 08    04      69
# 09    04      68
SELECT score.c_id,AVG(score.s_score) AS avg_sc FROM score GROUP BY score.c_id ORDER BY avg_sc DESC,c_id

33.
SELECT student.s_id,student.s_name,AVG(score.s_score) AS avg_sc FROM student
INNER JOIN score ON score.s_id=student.s_id
GROUP BY student.s_id
HAVING avg_sc >=85

34.
SELECT student.s_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score<60 and course.c_name='数学'

35.
SELECT student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id

36.
SELECT student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score>70

37.
SELECT student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score<60

38.
SELECT student.s_id,student.s_name,course.c_name,score.s_score FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
WHERE score.s_score>=80 AND score.c_id = '01'

39.
SELECT course.c_name,COUNT(score.s_score) AS st_num FROM course
LEFT JOIN score ON score.c_id=course.c_id
GROUP BY course.c_id

40.
SELECT student.*,score.s_score,teacher.t_name FROM student
INNER JOIN score ON score.s_id=student.s_id
INNER JOIN course ON course.c_id = score.c_id
INNER JOIN teacher ON teacher.t_id = course.t_id
WHERE teacher.t_name = '张三'
ORDER BY score.s_score DESC
LIMIT 0,1

41.
SELECT st.s_id,st.s_name,sc.c_id,sc.s_score 
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id 
WHERE(
    SELECT COUNT( * ) 
    FROM student st2
    LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
    LEFT JOIN course c2 ON c2.c_id = sc2.c_id 
    WHERE sc.s_score = sc2.s_score 
    AND c.c_id <> c2.c_id 
    )>1
    
42.
SELECT a.* FROM score AS a
LEFT JOIN score as b
ON a.c_id = b.c_id AND a.s_score < b.s_score # 列出同一门课内所有分数比较的情况
GROUP BY a.c_id, a.s_id
HAVING COUNT(b.c_id)<=1 #他只比1个低,或者比0个低(即他就是第一)
ORDER BY a.c_id;

43.
SELECT score.c_id,COUNT(score.s_id) AS st_num FROM score
GROUP BY score.c_id
HAVING st_num>5

44.
SELECT sc.s_id,COUNT(sc.s_score) count_sc FROM score sc
GROUP BY sc.s_id
HAVING count_sc>=2

45.
SELECT student.* FROM student
WHERE student.s_id IN(
    SELECT sc.s_id FROM score sc
    GROUP BY sc.s_id
    HAVING COUNT(sc.s_score)>2
)

46.
select *, TIMESTAMPDIFF(YEAR,student.s_birth,NOW()) as 年龄 from Student

47.
SELECT student.*,WEEK(student.s_birth),WEEK(NOW()) FROM student
WHERE WEEK(student.s_birth)=WEEK(NOW())

48.
SELECT student.*,WEEK(student.s_birth),WEEK(NOW()) FROM student
WHERE WEEK(student.s_birth)=WEEK(NOW())+1

49.
SELECT student.*,MONTH(student.s_birth),MONTH(NOW()) FROM student
WHERE MONTH(student.s_birth)=MONTH(NOW())

50.
SELECT student.*,MONTH(student.s_birth),MONTH(NOW()) FROM student
WHERE MONTH(student.s_birth)=MONTH(NOW())+1
Last modification:February 21st, 2021 at 06:55 pm
如果觉得我的文章对你有用,请随意赞赏