MySQL数据库DML语句

MySQL数据库的DML语句

在之前学习webSql的时候，我们已经对DML语句有过了解，并且也已经知道数据库操作语言的四大关键字，但这些都是基础，今天我们来继续深入学习

回顾基础

1. 计数函数count()

   SELECT count(*) FROM 学生信息;
   

2. as别名转换，将一个列或和一个表或一个查询结果做别名转换

   SELECT count(*) as 'stuCount' FROM 学生信息;
   

   如果as做了别名转换，可以省略

   select count(*) 'stuCount' FROM 学生信息;
   

3. 模糊查询使用关键字 like,通配符使用%，占位符使用_

   SELECT * FROM 学生信息 WHERE `家庭住址` like '湖北%';
   

---

MySQL数据库DML语句练习

1. 在学生信息表里面查询学号为2005050209或2005050102的学生

   SELECT * FROM 学生信息 WHERE 学号 = '2005050209' OR 学号='2005050102'
   

2. 查询所有学生信息，显示当前学生所对应的班级名称

-- 第一各方式  子查询，在原有的列里去追加一个新的列
SELECT a.*,(SELECT cname from classinfo where a.cid=classinfo.cid)  'cname' from stuinfo a;
-- 第二种方式  多表联查
SELECT a.*,b.cname FROM stuinfo a INNER JOIN classinfo b ON a.cid=b.cid

3. 查询所有学生信息，显示当前学生所对应的班级名称以及系别名称，并只显示“计算机科学与技术系”的

   -- 第一种方式  多表联查
   SELECT a.*,b.cname,c.dname FROM stuinfo a
   INNER JOIN classinfo b ON a.cid=b.cid
   INNER JOIN departmentinfo c on b.did=c.did
   WHERE c.dname='计算机科学与技术系'
   
   -- 第二种方式  多表联查
   SELECT a.*,b.cname,c.dname FROM stuinfo a,classinfo b,departmentinfo c
   WHERE a.cid=b.cid AND b.did=c.did
   AND c.dname='计算机科学与技术系'
   

   这两种方式我们都可以得到查询结果，但我们更推荐使用第一种方式，因为第一种方式的查询条件与联接条件是分开的，而第二种方式里面，它的查询条件与联接条件都写在了where里面，这样不清淅

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

   -- 第一种方式
   SELECT s.*,c.cname,i.instructorname FROM stuinfo s
   INNER JOIN classinfo c ON s.cid=c.cid
   INNER JOIN instructorinfo i ON c.instructorid=i.instructorid
   
   -- 第二种方式
   SELECT s.*,c.cname,i.instructorname FROM stuinfo s,classinfo c,instructorinfo i
   WHERE s.cid=c.cid AND c.instructorid=i.instructorid
   

5. 查询学生信息表里在男生共有多少，女生共有多少

   SELECT ssex,COUNT(*) FROM stuinfo
   GROUP BY ssex
   

   group by聚合，也叫分组

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

   -- 以学生信息为主体，分组统计以后，再次做子查询，内联接到另一张表
   SELECT b.*,a.totalCount FROM
   (SELECT cid,COUNT(*) 'totalCount' FROM stuinfo
   GROUP BY cid) a
   INNER JOIN classinfo b ON a.cid=b.cid
   
   -- 以班级信息表为主表，进行查询
   SELECT *,(SELECT COUNT(*) FROM stuinfo  WHERE classinfo.cid=stuinfo.cid GROUP BY cid) 'totalCount' FROM classinfo
   

   通过这两次的查询方式 ，我们可以得到一个差异，有一个班级的信息为空值 ，这是因为在查询的过程当中的，它所针对的主表是不一样的

   

   当我们在进行多表联查的时候，有三个关键查询连接 inner join取两个表交集部分，left join以左边表的结果为主，right join以右边表的结果为主

7. 查询学生成绩表，显示每个学生的平均成线，同时显示这个学生的所有信息

   SELECT stuinfo.*,a.avgscore FROM
   (SELECT sid, AVG(score) 'avgscore' FROM gradeinfo
   GROUP BY sid) a
   INNER JOIN stuinfo ON a.sid=stuinfo.sid
   
   -- 第二种现像，如果想查询有那些学生在这里它没有成绩，则这个时候应该以学生表为主表
   SELECT stuinfo.*,a.avgscore FROM
   (SELECT sid, AVG(score) 'avgscore' FROM gradeinfo
   GROUP BY sid) a
   RIGHT JOIN stuinfo ON a.sid=stuinfo.sid
   

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

   SELECT c.cid,AVG(c.score) FROM
   (SELECT a.*,b.cid FROM gradeinfo a 
   INNER JOIN stuinfo b ON a.sid=b.sid) c
   GROUP BY c.cid
   
   --  如果还需要显示班级的信息，可以再一次做一次子查询
   SELECT d.*,classinfo.cname FROM
   (SELECT c.cid,AVG(c.score) FROM
   (SELECT a.*,b.cid FROM gradeinfo a 
   INNER JOIN stuinfo b ON a.sid=b.sid) c
   GROUP BY c.cid) d
   INNER JOIN classinfo ON d.cid=classinfo.cid
   

9. 在成绩信息表里，求出学生在期中考试与期末考试的总成绩

   SELECT sid,eid,SUM(score) '平均成绩' FROM gradeinfo
   GROUP BY sid,eid
   

   这个语句的group by是以两个条件为分组

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

    -- 第一种方法
    SELECT a.*,c.* FROM instructorinfo a
    INNER JOIN classinfo b ON a.instructorid=b.instructorid
    INNER JOIN stuinfo c ON b.cid=c.cid
    WHERE a.instructorname='王艳'
    
    -- 第二种方式 
    SELECT a.*,c.* FROM
    (SELECT * FROM instructorinfo
    WHERE instructorname='王艳') a
    INNER JOIN classinfo b ON a.instructorid=b.instructorid
    INNER JOIN stuinfo c ON b.cid=c.cid
    

11. 查询成绩信息表里面每一科成绩的最高分, 并且显示学生姓名，使用 MAX() 函数

    SELECT a.*,c.sname FROM
    (SELECT courseid,MAX(score) 'maxScore' FROM gradeinfo
    GROUP BY courseid) a
    INNER JOIN gradeinfo b ON a.maxScore=b.score
    INNER JOIN stuinfo c ON b.sid=c.sid
    

12. 在学生信息表里面，对每个学生的平均成绩做一个评级（在平均成绩的后面追加一个列为评级列）

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

    SELECT *, 
    (CASE
    WHEN c.avgScore>=90 THEN '优秀'
    WHEN c.avgScore>=80 THEN '良好'
    WHEN c.avgScore>=70 THEN '中等'
    WHEN c.avgScore>=60 THEN '及格'
    ELSE '不及格'
    END) '评级'
    FROM
    (SELECT b.sname,a.* FROM
    (SELECT sid,avg(score) 'avgScore' FROM gradeinfo
    GROUP BY sid) a
    INNER JOIN stuinfo b on a.sid=b.sid) c
    

13. 查询学生信息里面，同省份的学生有多少

    SELECT a.province,COUNT(*) 'totalCount' FROM
    (SELECT *,SUBSTR(saddr,1,2) 'province' FROM stuinfo) a
    GROUP BY a.province
    

14. 把辅导员为王艳的所有学生成绩分数+1

```sql
UPDATE gradeinfo SET score=score+1 WHERE sid in(
SELECT c.sid FROM
(SELECT * FROM instructorinfo
WHERE instructorname='王艳') a
INNER JOIN classinfo b ON b.instructorid=a.instructorid
INNER JOIN stuinfo c ON c.cid=b.cid
)
```

把一个查询结果当成另一个SQL语句的查询条件

15. 当查询结果为空值的时候转换为其它值

    我们刚刚在查询班级的学生人数的SQL语句里面出现了一个空值

    

    现在我们希望把这个空值替换成0

    SELECT c.cid,c.cname,c.ccount,c.did,c.instructorid,
    (CASE
    WHEN totalCount IS NULL THEN 0 ELSE totalCount
    END ) 'totalCount2'
    FROM
    (SELECT b.*,a.totalCount FROM
    (SELECT cid,COUNT(*) 'totalCount' FROM stuinfo
    GROUP BY cid) a
    RIGHT JOIN classinfo b ON a.cid=b.cid) c
    

16. 分页查询

    查询学生信息表，每页显示5条数据

    分页主要使用的到的是limit关键字，这个关键一般排在SQL句语的最后，指查询结束以后再进行一次分页

    一般我们使用pageIndex当表当前页码，pageSize代表页容量（每页显示的记录数）,offset代表跳过多少条

    limit关键字的用户是limit 跳过多少条,取多少条,所以分页的SQL语句可以如下进行

    select * from 表名
    where 条件表达式
    group by 分组依赖
    limit (pageIndex-1)*pageSize,pageSize
    

    有where条件的，就给where条件，有分组的就给分别条件

    SELECT * FROM stuinfo LIMIT 0,5;   -- 第一页
    
    SELECT * FROM stuinfo LIMIT 5,5;   -- 第二页
    
    SELECT * FROM stuinfo LIMIT 10,5;  -- 第三页
    

17. 成绩信息表里面查询平均分高于85分信息

```sql
-- 第一种方案
SELECT * FROM 
(SELECT sid,avg(score) 'avgScore' FROM gradeinfo
GROUP BY sid) a
WHERE avgScore>85

-- 第二种方案
SELECT sid,avg(score) 'avgScore' FROM gradeinfo
GROUP BY sid
HAVING avgScore>85
```

如果希望在`GROUP BY`以后做条件过滤 ，我们不能使用`WHERE`,必须使用另一个关键字`having`

18. 查询每个学生的平均，按从高到底进行排列

    SELECT * FROM gradeinfo
    ORDER BY score ASC
    -- 关键点就在ORDER BY 排序依据 默认是从低到高（ASC），如果想从高到低则是DESC
    
    -- 完整SQL语句，平均分从高到底
    SELECT sid,avg(score) 'avgScore' FROM gradeinfo
    GROUP BY sid
    ORDER BY avgScore DESC
    

---

MySQL视图

mysql的视图叫view，它是一个虚拟的表，可以把视图当成是一条SQL语句执行的查询结果，然后将这个结果可以转化为视图

1. 视图是一个虚拟的表，本质是一个SQL语句
2. 视图只能查询

通过SQL语句创建

CREATE VIEW stuinfoview
AS
SELECT a.*,b.cname,c.instructorname FROM stuinfo a
INNER JOIN classinfo b ON a.cid=b.cid
INNER JOIN instructorinfo c ON c.instructorid=b.instructorid

这个时候，我们就可以在视图里面看到刚刚创建好的stuinfoview了

当视图创建好了以后，我们就可以去执行查询操作了

SELECT * FROM stuinfoview WHERE sid='1002'

上面的创建过程是通过SQL语句来创建的，同样，我们可以借助于第三方工具navicate去创建 ，这样非常方便

通过工具创建

创建成功以后就可以去执行查询了

SELECT * FROM gradeinfoview
WHERE tname='孙鹏'