SQL语句练习

本文主要是介绍SQL语句练习，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

练习环境搭建

CREATE DATABASE sql_test CHARSET='utf8';

USE sql_test;

CREATE TABLE tab_student(
    stu_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生id,起始2021001',
    stu_name VARCHAR(50) COMMENT '学生姓名',
    stu_age INT COMMENT '学生年龄',
    stu_sex CHAR(1) COMMENT '学生性别'
) COMMENT '学生表';

CREATE TABLE tab_teacher(
    tea_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '教师id,起始10001',
    tea_name VARCHAR(50) COMMENT '教师姓名'
) COMMENT '教师表';

CREATE TABLE tab_course(
    cou_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程id,起始1',
    cou_name VARCHAR(50) COMMENT '课程名称',
    tea_id INT COMMENT '教师id',
    CONSTRAINT fk_cou_tea_id FOREIGN KEY(tea_id) REFERENCES tab_teacher(tea_id)
) COMMENT '课程表';

CREATE TABLE tab_score(
    stu_id INT COMMENT '学生id',
    cou_id INT COMMENT '课程id',
    sco_score DOUBLE(4,1) COMMENT '成绩表',
    PRIMARY KEY(stu_id, cou_id),
    CONSTRAINT fk_sco_stu FOREIGN KEY(stu_id) REFERENCES tab_student(stu_id),
    CONSTRAINT fk_sco_cou FOREIGN KEY(cou_id) REFERENCES tab_course(cou_id) 
) COMMENT '成绩表';


INSERT INTO tab_student VALUES(2021001,'赵一',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'钱二',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'孙三',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'李四',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'周五',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'吴六',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'郑七',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'王八',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'冯九',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
                              (NULL,'陈十',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女'));

/*查询学生表中数据是否正确被插入*/
SELECT * FROM tab_student;


INSERT INTO tab_teacher VALUES(10001,'褚一'),
                              (NULL,'卫二'),
                              (NULL,'卫二二'),
                              (NULL,'蒋三'),
                              (NULL,'蒋三三'),
                              (NULL,'蒋三三三');

/*查询老师表中数据是否正确被插入*/
SELECT * FROM tab_teacher;

INSERT INTO tab_course VALUES(1,'C语言',10001),
                             (NULL,'Java',10002),
                             (NULL,'操作系统',10003),
                             (NULL,'MySQL',10004),
                             (NULL,'计算机网络',10005),
                             (NULL,'数据结构',10006);

/*查询课程表中数据是否正确被插入*/                             
SELECT * FROM tab_course;

/*随机学生id,课程id,分数,执行多次*/
INSERT INTO tab_score VALUES(CEIL(RAND()*10+2021000),CEIL(RAND()*6),ROUND(RAND()*100,1));

/*查询分数表中数据是否正确被插入*/
SELECT * FROM tab_score;

## 语句练习
~~~sql
-- 1、查询id为1的课程比id为2的课程，成绩高的所有学生的学号
/*
思路：
1、分别查出课程id为1以及课程id为2的课程，并做笛卡尔积
2、根据要求制定条件，找出学号相同且课程1的分数大于课程2的分数的同学
*/
SELECT score1.stu_id 学生id
FROM 
    (SELECT stu_id,
            sco_score
     FROM tab_score
     WHERE cou_id=1) score1,
    (SELECT stu_id,
            sco_score
     FROM tab_score
     WHERE cou_id=2) score2
WHERE score1.stu_id=score2.stu_id
      AND
      score1.sco_score>score2.sco_score;


-- 2、查询平均成绩大于60分的同学的学号和平均成绩
/*
思路：
1、在成绩表中根据学号进行分组
2、求出每个学号对应的平均分
3、选出平均分大于60的学生
*/
SELECT stu_id 学号,
       AVG(sco_score) 平均分
FROM tab_score
GROUP BY stu_id
HAVING AVG(sco_score)>60;

-- 3、查询所有学生的学号、姓名、选课数、总成绩
/*
思路：
1、将学生表中的全部信息与成绩表中的全部信息汇总为一张表，保持学生表的完整性，借助学生id清洗数据
2、利用学生id的唯一性进行分组，每组的课程id总数正好为选课数，分数总数正好为总分数
*/
SELECT stu.stu_id 学号,
       stu.stu_name 姓名,
       COUNT(sco.cou_id) 选课数 ,
       SUM(sco.sco_score) 总成绩
FROM (tab_student stu LEFT JOIN tab_score sco
      ON stu.stu_id=sco.stu_id)
GROUP BY stu.stu_id;


-- 4、查询姓“卫”的老师的个数
/*
思路：
使用模糊查询，查询姓名第一个字是卫的老师，名字的总长不定因此应该使用%
*/
SELECT COUNT(tea_name) 卫姓老师总数
FROM tab_teacher
WHERE tea_name LIKE '卫%';

-- 5、查询没学过“褚一”老师课程的同学的学号、姓名
/*
思路：
1、找出褚一老师的教师id
2、根据教师id寻找褚一老师所教的课程id
3、将学生根据学号进行分组
4、找出分组后学生课程号组成的集合中不包含褚一老师课程的学生id
5、根据学生id在学生表中寻找数据
*/
SELECT stu_id 学号,
         stu_name 姓名
FROM tab_student
WHERE stu_id IN
    (SELECT stu_id
    FROM 
        (SELECT stu_id,
         cou_id
        FROM tab_score
        GROUP BY  stu_id
        HAVING (
            (SELECT cou_id
            FROM tab_course
            WHERE tea_id= 
                (SELECT tea_id
                FROM tab_teacher
                WHERE tea_name='褚一'))) NOT IN (cou_id)) student);


-- 6、查询学过课程号1和学习过课程号2的同学的学号、姓名
/*
思路：
1、分别查出课程id为1以及课程id为2的课程，并做笛卡尔积
2、两张表中学号一致的列为同时学习id为1以及id为2课程的学生
3、根据步骤二查询出的学号在学生表中找出对应学生
*/
SELECT stu_id 学号,
       stu_name 姓名
FROM tab_student
WHERE stu_id IN
    (SELECT score1.stu_id
    FROM 
        (SELECT *
        FROM tab_score
        WHERE cou_id=1) score1, 
        (SELECT *
        FROM tab_score
        WHERE cou_id=2) score2
        WHERE score1.stu_id=score2.stu_id); 

-- 7、查询学过“褚一”老师课程的同学的学号、姓名
/*
思路：
先找到褚一老师的教师id
根据教师id找到褚一老师的所教课程id
根据课程id在成绩表找到选择该课程的学生学号
最后根据学生学号在学生表中找到对应学生
*/
SELECT stu_id 学生学号,
       stu_name 学生姓名
FROM tab_student
WHERE stu_id IN
    (SELECT stu_id
    FROM tab_score
    WHERE cou_id=
        (SELECT cou_id
         FROM tab_course
         WHERE tea_id=
            (SELECT tea_id
            FROM tab_teacher
            WHERE tea_name='褚一')));

-- 8、查询课程号2的成绩比课程号1的成绩低的所有同学的学号、姓名
/*
思路：
1、分别查出课程id为1以及课程id为2的课程，并做笛卡尔积
2、根据要求制定条件，找出学号相同且课程2的分数低于课程1的分数的同学
*/
SELECT stu_id,
         stu_name
FROM tab_student
WHERE stu_id IN
    (SELECT score1.stu_id FROM
        (SELECT *
        FROM tab_score
        WHERE cou_id=1) score1, 
            (SELECT *
            FROM tab_score
            WHERE cou_id=2) score2
            WHERE score2.sco_score<score1.sco_score
                    AND score1.stu_id=score2.stu_id);

-- 9、查询所有课程成绩小于60分的同学的学号、姓名
/*
思路：
最大分数小于60的同学其他分数一定小于60
*/
SELECT tab_student.stu_id 学号,
         stu_name 姓名
FROM tab_student,
    (SELECT stu_id,
         MAX(sco_score)
    FROM tab_score
    GROUP BY  stu_id
    HAVING MAX(sco_score)<60) student1
WHERE student1.stu_id=tab_student.stu_id;
 
-- 10、查询没有学全所有课的同学的学号、姓名
/*
先查看所有的课程一共有多少门
根据学号分组，查看学生的选课总数是否等于课程总数
*/
SELECT stu_id 学号,
         stu_name 姓名
FROM tab_student
WHERE stu_id IN
    (SELECT stu_id
    FROM tab_score
    GROUP BY stu_id
    HAVING COUNT(cou_id)!=(SELECT COUNT(*)
                           FROM tab_course));

这篇关于SQL语句练习的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！