Mysql数据库DML语句练习

Mysql数据库DML语句练习

1. 数据库自增长

   auto_increment
   

   说明：要满足自增长，这个字段必须是一个主键，并且必须是整数类型 int long，如果设置了自动增长，那么，在插入数据到这张表里面的时候，就可以不需要手动的去设置这一列的值，它会自动填充增长，增长的值的大小以上次出现的最大值为准，然后+1，

   同时，我们也可以手动的去设置它的值，并且，这个值可以回填。

2. 查询一共有多少学生

   select count(*) from studentinfo
   -- 在这个地方，有必要说明一点：别名转换
   select count(*) as 'sumCount' from studentinfo
   --   mysql里面，如果as是做别名转换的，那么就可以省略
   

3. 查询湖北省的学生

   select * from studentinfo where s_address like '湖北%'
   

4. 查询学号为’2005050209’或‘2005050102’的学生

   -- 第一种方法：
   select * from studentinfo where s_id ='2005050209' OR s_id='2005050102'
   -- 第二种方法：
   select * from studentinfo where s_id in ('2005050209','2005050102','2005030101')
   

5. 查询所有学生的信息，并且显示当前学生的班级名称

   -- 第一种方法：
   SELECT studentinfo.*,classinfo.c_name  FROM studentinfo,classinfo where studentinfo.c_id=classinfo.c_id
   -- 第二种方法：
   SELECT studentinfo.*,classinfo.c_name FROM studentinfo
   INNER JOIN classinfo ON studentinfo.c_id=classinfo.c_id
   WHERE classinfo.c_name='计算机科学与技术1班'
   

6. 查询学生的信息，显示班级名称及辅导员姓名

   -- 第一种方法：
   SELECT studentinfo.*,classinfo.c_name,instructorinfo.i_name FROM studentinfo,classinfo,instructorinfo
   where studentinfo.c_id=classinfo.c_id
   and classinfo.i_id=instructorinfo.i_id
   -- 第二种方法：
   SELECT studentinfo.*,classinfo.c_name,instructorinfo.i_name FROM studentinfo
   INNER JOIN classinfo ON studentinfo.c_id=classinfo.c_id
   INNER JOIN instructorinfo ON classinfo.i_id=instructorinfo.i_id
   -- 优项以后的，经过别名转换以后的
   SELECT a.*,b.c_name,c.i_name '辅导员名称' FROM studentinfo a
   INNER JOIN classinfo b on a.c_id=b.c_id
   INNER JOIN instructorinfo c on b.i_id=c.i_id
   WHERE c.i_name='王艳'
   

7. 查询各个班级的学生数量

   -- 第一步:得到每个班级编号的学生人数
   SELECT c_id,COUNT(*) FROM studentinfo
   GROUP BY c_id;
   -- 第二步：
   select c.c_name,count(*) from 
   (SELECT a.*,b.c_name from studentinfo a INNER JOIN classinfo b on a.c_id=b.c_id) c
   group BY c.c_name
   

8. 查询每个班级的学生人数，并且要显示当前班级的基本信息

   -- 第一种方法：
   SELECT a.*,b.* FROM
   (SELECT c_id,count(*) 'sumCount' FROM studentinfo
    group by c_id) a
    INNER JOIN classinfo b on a.c_id=b.c_id
    -- 这一种写法，是以学生表studentinfo为主，先在学生表里面做聚合查询，然后再连接到班级表
    -- 第二种方法：
    -- 这个时候，我们以班级表 classinfo 为主
    SELECT a.*,(SELECT COUNT(*) FROM studentinfo WHERE c_id=a.c_id) 'sumCount' FROM classinfo a
    -- 注意：这一种书写方法，简便，但是，运行效率很低，因为这个时候，要显示多少行，里面的子查询就会运行多少次
   

9. 查询成绩信息表，查询每个学生的平均成绩

   SELECT s_id,avg(score) FROM gradeinfo
   GROUP BY s_id
   --   这一个时候，查询的结果是每个学生的平均分，这些平均分通过聚合函数 group by 得到，平均值函数是avg
   -- 第一种方法：
   SELECT a.*,(SELECT AVG(score) FROM gradeinfo WHERE a.s_id=s_id) '平均分' FROM studentinfo a
   -- 第二种方法：
   SELECT * FROM
   (SELECT s_id,AVG(score) FROM gradeinfo
    GROUP BY s_id) a
    INNER JOIN studentinfo b ON a.s_id=b.s_id
   
   

10. 查询每个班的平均成绩

    SELECT c_id,c_name,AVG(score) FROM(
    SELECT a.*,c.c_id ,c.c_name FROM gradeinfo a
    INNER JOIN studentinfo b ON a.s_id=b.s_id
    INNER JOIN classinfo c ON c.c_id=b.c_id) d
    GROUP BY c_id,c_name
    

11. 在学生的成绩信息表里面，对学生的成线， 做一个评级

    90分或以上为优秀、80_{89为良好、70}79为中等、60~69为及格、否则不及格

    SELECT *,
    CASE 
    WHEN score >89 THEN '优秀'
    WHEN score >79 THEN '良好'
    WHEN score >69 THEN '中等'
    WHEN score >59 THEN '及格'
    ELSE '不及格'
    END '评级'
    FROM gradeinfo
    

12. 在成绩信息表里面，求出所有学生，在期中或期末考试的总成绩

    SELECT s_id,s_name,e_type,sum(score) FROM
    (SELECT a.*,b.e_type,d.s_name FROM gradeinfo a
    INNER JOIN examinfo b on a.e_id=b.e_id
    INNER JOIN studentinfo d on a.s_id=d.s_id) c
    GROUP BY s_id,e_type
    

13. 查询成绩信息表里面，每一科成绩的最高得分

    SELECT s_id,s_name,co_name,MAX(score) FROM
    (SELECT a.*,d.s_name,b.co_name FROM gradeinfo a
     INNER JOIN courseinfo b on a.co_id=b.co_id
     INNER JOIN studentinfo d on d.s_id=a.s_id
    ) c
    GROUP BY co_name
    

14. 查询辅导员为王艳所有的学生

    -- 第一种方法：
    SELECT a.* FROM studentinfo a
    INNER JOIN classinfo b ON a.c_id=b.c_id
    INNER JOIN instructorinfo c on b.i_id=c.i_id
    WHERE c.i_name='王艳'
    -- 第二种方法：
    SELECT studentinfo.* FROM studentinfo,classinfo,instructorinfo
    WHERE studentinfo.c_id=classinfo.c_id
    AND classinfo.i_id=instructorinfo.i_id
    AND instructorinfo.i_name='王艳'
    

15. 把辅导员为王艳的学生所有的考试成绩全部+1

    UPDATE gradeinfo SET score=score+1 WHERE s_id IN(
    SELECT a.s_id FROM studentinfo a
    INNER JOIN classinfo b ON a.c_id=b.c_id
    INNER JOIN instructorinfo c ON c.i_id=b.i_id
    WHERE c.i_name='王艳')
    

16. 查询学生信息里面，同省的学生各占多少

    SELECT substr(s_address,1,2) as 'privince',COUNT(*) '人数' FROM studentinfo
    GROUP BY privince
    

17. 查询所有老师的学生的老乡个数

    SELECT *,
    CASE
     WHEN count is NULL THEN 0
    ELSE count
    END as 'totalCount'
     FROM 
    (SELECT b.*,a.count FROM
    (SELECT s_address,COUNT(*) 'count' FROM studentinfo
    GROUP BY s_address) a
    RIGHT JOIN instructorinfo b ON a.s_address=b.i_place) c
    

    说明：在这里，我们使用inner join就相当于取两张表的交集，如果使用right join那么就是取右边的表 如果是left那就是左边的。