目 录CONTENT

文章目录

MySQL数据库DML语句

Administrator
2020-07-24 / 1 评论 / 3 点赞 / 11681 阅读 / 8755 字

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. 在学生信息表里面查询学号为20050502092005050102的学生

    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
  1. 查询所有学生信息,显示当前学生所对应的班级名称以及系别名称,并只显示“计算机科学与技术系”的

    -- 第一种方式  多表联查
    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里面,这样不清淅

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

    -- 第一种方式
    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
    
  3. 查询学生信息表里在男生共有多少,女生共有多少

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

    group by聚合,也叫分组

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

    -- 以学生信息为主体,分组统计以后,再次做子查询,内联接到另一张表
    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
    

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

    1569651695910

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

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

    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
    
  6. 查询每个班的平均成绩

    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
    
  7. 在成绩信息表里,求出学生在期中考试与期末考试的总成绩

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

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

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

    -- 第一种方法
    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
    
  9. 查询成绩信息表里面每一科成绩的最高分, 并且显示学生姓名,使用 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
    
  10. 在学生信息表里面,对每个学生的平均成绩做一个评级(在平均成绩的后面追加一个列为评级列)

    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
    
  11. 查询学生信息里面,同省份的学生有多少

    SELECT a.province,COUNT(*) 'totalCount' FROM
    (SELECT *,SUBSTR(saddr,1,2) 'province' FROM stuinfo) a
    GROUP BY a.province
    
  12. 把辅导员为王艳的所有学生成绩分数+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语句的查询条件
  1. 当查询结果为空值的时候转换为其它值

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

    1569658853094

    现在我们希望把这个空值替换成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
    
  2. 分页查询

    查询学生信息表,每页显示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;  -- 第三页
    
  3. 成绩信息表里面查询平均分高于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`
  1. 查询每个学生的平均,按从高到底进行排列

    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

1570781168351

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

SELECT * FROM stuinfoview WHERE sid='1002'

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

通过工具创建

1570781746553

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

SELECT * FROM gradeinfoview
WHERE tname='孙鹏'
3

评论区