mysql练习

作者: Sophie12138 | 来源:发表于2018-09-03 20:41 被阅读24次

MySQL Operation
MySQL 数据库SQL练习
数据库语言杂记
2019-08-09
Python day25_mysql数据库
2019-08-06
sql 练习(五)
sql 练习(四)
MySQL查询语句的45道练习
mysql 练习

class表：

CREATE TABLE `class`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

course表：

CREATE TABLE `course`  (
  `cid` int(11) NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `teacher_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

score表：

CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NULL DEFAULT NULL,
  `course_id` int(11) NULL DEFAULT NULL,
  `number` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `course_id`(`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27693 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

student表：

CREATE TABLE `student`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

teacher表：

CREATE TABLE `teacher`  (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

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

SELECT
    A.student_id, A.sw, B.wl 
FROM
    (SELECT score.student_id, score.number sw 
        FROM
            score, course
        WHERE
            score.course_id=course.cid AND course.cname = '生物' ) A,
    (SELECT score.student_id, score.number wl 
        FROM
            score,course
        WHERE
            score.course_id = course.cid AND course.cname = '物理' ) B 
WHERE
    A.sw > B.wl AND A.student_id = B.student_id

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

SELECT
    s.student_id,
    AVG( s.number ) 
FROM
    score s 
GROUP BY
    s.student_id 
HAVING
    AVG( s.number ) > 60;

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

SELECT
    d.sid,
    d.sname,
    SUM( s.number ),
    COUNT( s.number ) 
FROM
    student d,
    score s 
WHERE
    d.sid = s.student_id 
GROUP BY
    s.student_id;

查询姓“李”的老师的个数；

SELECT
    COUNT( * ) 
FROM
    teacher t 
WHERE
    t.tname LIKE '李%';

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

SELECT
    s.sid,
    s.sname 
FROM
    student s 
WHERE
    s.sid NOT IN (
    SELECT DISTINCT
        ( score.student_id ) 
    FROM
        score,
        course,
        teacher 
    WHERE
        score.course_id = course.cid 
    AND course.teacher_id = teacher.tid 
    AND teacher.tname='叶平'
    );

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

SELECT
    s.sid,
    s.sname 
FROM
    student s,
    score 
WHERE
    s.sid = score.student_id 
    AND score.course_id = 1 
    AND EXISTS ( SELECT * FROM score AS score2 WHERE score2.student_id = score.student_id AND score2.course_id = 2 )

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

SELECT
DISTINCT 
d.sid,d.sname
FROM
    course c,
    teacher t,
    score s,
    student d 
WHERE
    c.teacher_id = t.tid 
    AND t.tname = '叶平'
    AND c.cid=s.course_id
    AND s.student_id=d.sid

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

SELECT DISTINCT
    s.sid,
    s.sname
FROM
    (SELECT * FROM score WHERE course_id=1) AS c1,
    (SELECT * FROM score WHERE course_id=2) AS c2,
    student s
WHERE
    c1.number < c2.number
    AND c1.student_id = c2.student_id
    AND c1.student_id = s.sid

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

SELECT
    A.sid,
    A.sname 
FROM
    student AS A,
    (
    SELECT DISTINCT
        ( student.sid ) sid 
    FROM
        score,
        student 
    WHERE
        student.sid = score.student_id 
        AND score.number < 60 
    ) AS B 
WHERE
    A.sid = B.sid

查询没有学全所有课的同学的学号、姓名；分组查询的真正运用

SELECT
    d.sid,
    d.sname
FROM
    score s,
    student d 
WHERE
    s.student_id = d.sid 
GROUP BY
    d.sid
HAVING
    COUNT( s.course_id ) < ( SELECT COUNT( cid ) FROM course )

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

SELECT
    d.sid,
    d.sname
FROM
    student d,
    score s 
WHERE
    d.sid = s.student_id 
    AND s.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 )

查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

SELECT
    d.sid,
    d.sname
FROM
    student d,
    score s 
WHERE
    d.sid = s.student_id 
    AND s.course_id IN ( SELECT course_id FROM score WHERE student_id = 1 )

查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

SELECT DISTINCT
    * 
FROM
    student AS d,
    (
    SELECT
        a.student_id
    FROM
        student d,
        score AS a
        INNER JOIN ( SELECT * FROM score s WHERE s.student_id = 2 ) AS b ON a.course_id = b.course_id 
        AND a.student_id != b.student_id 
    ) AS x 
WHERE
    d.sid = x.student_id

删除学习“叶平”老师课的score表记录；

DELETE FROM score WHERE course_id IN (SELECT tid FROM teacher WHERE tname = '叶平')

向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；

SELECT
    A.student_id,
    A.number 生物,
    B.number 物理,
    C.number 体育,
    D.xuanke,
    E.ae
FROM
    ( SELECT * FROM score s WHERE s.course_id = 1 ) AS A,
    ( SELECT * FROM score s WHERE s.course_id = 2 ) AS B,
    ( SELECT * FROM score s WHERE s.course_id = 3 ) AS C,
    ( SELECT COUNT( student_id ) xuanke, student_id FROM score s WHERE s.number != 0 GROUP BY student_id ) AS D,( SELECT avg( s.number ) ae, student_id FROM score s WHERE s.number != 0 GROUP BY s.student_id ) AS E 
WHERE
    A.student_id = B.student_id 
    AND B.student_id = C.student_id 
    AND C.student_id = D.student_id 
    AND D.student_id = E.student_id

课程平均分从高到低显示（显示任课老师）；

SELECT
    c.cname,
    AVG( s.number ),
    t.tname 
FROM
    course c,
    teacher t,
    score s 
WHERE
    s.course_id = c.cid 
    AND c.teacher_id = t.tid 
GROUP BY
    s.course_id

查询各科成绩前三名的记录:(不考虑成绩并列情况)?

( SELECT * FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname = '生物' ) ORDER BY number DESC LIMIT 3 ) UNION
( SELECT * FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname = '物理' ) ORDER BY number DESC LIMIT 3 ) UNION
( SELECT * FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname = '体育' ) ORDER BY number DESC LIMIT 3 )

查询每门课程被选修的学生数；

SELECT
    s.course_id,
    COUNT( 1 ) 总人数 
FROM
    score s 
GROUP BY
    s.course_id

查询出只选修了一门课程的全部学生的学号和姓名；

SELECT
    d.sid,
    d.sname 
FROM
    score s,
    student d 
WHERE
    s.student_id = d.sid 
GROUP BY
    d.sid,
    d.sname 
HAVING COUNT(s.student_id) = 1

查询男生、女生的人数；

SELECT SUM(CASE s.gender WHEN '男' THEN 1 ELSE 0 END) AS 男生,
                SUM(CASE s.gender WHEN '女' THEN 1 ELSE 0 END) AS 女生
FROM student s;

查询同名同姓学生名单，并统计同名人数；

SELECT
    s.sname,
    COUNT( 0 ) 同名人数 
FROM
    student s 
GROUP BY
    s.sname 
HAVING
    COUNT( 0 ) > 1;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

SELECT
    score.student_id,
    student.sname,
    AVG( score.number ) 
FROM
    student,
    score 
WHERE
    student.sid = score.student_id 
GROUP BY
    score.student_id 
HAVING
    AVG( score.number ) > 85

网友评论

本文标题：mysql练习

本文链接：https://www.haomeiwen.com/subject/rdwiwftx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

mysql练习

相关文章

MySQL Operation

MySQL 数据库SQL练习

数据库语言杂记

2019-08-09

Python day25_mysql数据库

2019-08-06

sql 练习(五)

sql 练习(四)

MySQL查询语句的45道练习

mysql 练习

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读