MySQL数据库的DML语句
在之前学习webSql的时候,我们已经对DML语句有过了解,并且也已经知道数据库操作语言的四大关键字,但这些都是基础,今天我们来继续深入学习
回顾基础
-
计数函数
count()
SELECT count(*) FROM 学生信息;
-
as
别名转换,将一个列或和一个表或一个查询结果做别名转换SELECT count(*) as 'stuCount' FROM 学生信息;
如果
as
做了别名转换,可以省略select count(*) 'stuCount' FROM 学生信息;
-
模糊查询使用关键字
like
,通配符使用%
,占位符使用_
SELECT * FROM 学生信息 WHERE `家庭住址` like '湖北%';
MySQL数据库DML语句练习
-
在学生信息表里面查询学号为
2005050209
或2005050102
的学生SELECT * FROM 学生信息 WHERE 学号 = '2005050209' OR 学号='2005050102'
-
查询所有学生信息,显示当前学生所对应的班级名称
-- 第一各方式 子查询,在原有的列里去追加一个新的列
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
-
查询所有学生信息,显示当前学生所对应的班级名称以及系别名称,并只显示“计算机科学与技术系”的
-- 第一种方式 多表联查 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
里面,这样不清淅 -
查询学生信息,显示班级名称及辅导员姓名
-- 第一种方式 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
-
查询学生信息表里在男生共有多少,女生共有多少
SELECT ssex,COUNT(*) FROM stuinfo GROUP BY ssex
group by聚合,也叫分组
-
查询每个班级的实际学生人数,并且显示当前班级的基本信息
-- 以学生信息为主体,分组统计以后,再次做子查询,内联接到另一张表 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
以右边表的结果为主 -
查询学生成绩表,显示每个学生的平均成线,同时显示这个学生的所有信息
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
-
查询每个班的平均成绩
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
-
在成绩信息表里,求出学生在期中考试与期末考试的总成绩
SELECT sid,eid,SUM(score) '平均成绩' FROM gradeinfo GROUP BY sid,eid
这个语句的group by是以两个条件为分组
-
查询辅导员为王艳的所有学生
-- 第一种方法 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
-
查询成绩信息表里面每一科成绩的最高分, 并且显示学生姓名,使用
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
-
在学生信息表里面,对每个学生的平均成绩做一个评级(在平均成绩的后面追加一个列为评级列)
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
-
查询学生信息里面,同省份的学生有多少
SELECT a.province,COUNT(*) 'totalCount' FROM (SELECT *,SUBSTR(saddr,1,2) 'province' FROM stuinfo) a GROUP BY a.province
-
把辅导员为王艳的所有学生成绩分数+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语句的查询条件
-
当查询结果为空值的时候转换为其它值
我们刚刚在查询班级的学生人数的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
-
分页查询
查询学生信息表,每页显示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; -- 第三页
-
成绩信息表里面查询平均分高于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`
-
查询每个学生的平均,按从高到底进行排列
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语句执行的查询结果,然后将这个结果可以转化为视图
- 视图是一个虚拟的表,本质是一个SQL语句
- 视图只能查询
通过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='孙鹏'
评论区