论坛 › 量化数据库 › 数据库(MySQL)-DQL数据查询语言

小秦哥 发表于 2024-8-25 17:42:53

数据库(MySQL)-DQL数据查询语言

        DQL(Data Query Language 数据查询语言)的用途是查询数据库数据，如select语句。其中，可以根据表的结构和关系分为单表查询和多表联查。
单表查询

        单表查询：针对数据库中的一张数据表举行查询
全字段查询

        语法：select 字段名 from 表名
#推荐
select sid,sname,birthday,ssex,classid from student;
#不推荐，不利于SQL优化
SELECT * from student; 结果：
https://i-blog.csdnimg.cn/direct/34c0f2b5d63a41f18d613e441f4f47a5.png
 部分字段查询

SELECT sname from student; 结果：
https://i-blog.csdnimg.cn/direct/926b08a2e32d4e7c9b870ca335da24aa.png
字段起名

        语法：select 旧字段名 as 新字段名，旧字段名 ‘新字段名’，...， 旧字段名 ‘新字段名’  from 表名
select sname as '姓名', birthday '生日' from student; 结果：
https://i-blog.csdnimg.cn/direct/c2856db9ca334569b31d9467c744b4e1.png
添加字段

        语法：select 旧字段名，...，旧字段名，‘新字段值’  新字段名 from 表名
SELECT sname,'猿究院' 学校 from student; 结果：
 https://i-blog.csdnimg.cn/direct/1fc3c2b2938d479486a1114eb70a42c6.png
select sid,sname,birthday,ssex,classid,'猿究院' 学校 from student; https://i-blog.csdnimg.cn/direct/9dbc2d76759d470b8b9cbdcd07d37964.png去重

使用distinct去重

        语法： select distinct 须要去重的值 from 表名
select distinct ssex from student; 结果：
https://i-blog.csdnimg.cn/direct/d8eca890d7224737a9c5dece6af2b036.png
        要注意的是，所有字段都相同时,distinct才去重。若有一个陈同学 男和陈同学 女，我们不应该将其视为去重对象：
select distinct sname,ssex from student; 结果：
https://i-blog.csdnimg.cn/direct/485bad6410fc43889df8ea8f106036aa.png
使用group by去重

条件查询

        使用【where】条件子句举行查询。
单条件查询

        语法：select * from 表名 where 条件；
# 在student表中查询sid等于5的数据
select * from student where sid=5;
select * from student where sid<>5; 结果：
https://i-blog.csdnimg.cn/direct/9d020749f6fe4c58a964440cc455d3e1.png
多条件查询

        使用"and" 和"or"来连接多个条件
#查找1班的女同学
select * from student where classid=1 and ssex='女';
结果：
https://i-blog.csdnimg.cn/direct/1374536e22ac45d5bc553f10e45d5ae5.png
         那我们想查找sid为3，6，9的门生信息呢？
SELECT * FROM student
WHERE sid=3 OR sid=200 OR sid=6;         也可以使用in关键字：
SELECT * FROM student
WHERE sid in(3,6,9);          结果：
https://i-blog.csdnimg.cn/direct/f50e52c937df4f07955361ef5f38057f.png
 含糊查询

        含糊符号：① "%"：匹配任意多个字符
                         ② ”_“：匹配任意一个字符
        首先我们先插入几条数据：
insert into student (sname)
values('卫小佳'),('小佳佳'),('卫公主'),('宇宙无敌美女佳佳'),('卫美女'); 我们想查找关于‘卫小佳’的信息，就可以通过含糊查询举行查找：
SELECT *FROM student WHERE sname LIKE '%佳%'  结果：
https://i-blog.csdnimg.cn/direct/7db1b5bbc82448068e46ff46f9d7c99d.png
SELECT *FROM student WHERE sname LIKE '卫__';
SELECT *FROM student WHERE sname LIKE '卫%'; 结果：
https://i-blog.csdnimg.cn/direct/ad1d9c38032f49e890708717fd6527f2.png
 查找null和非null值

关键字：is null
SELECT * FROM student WHERE birthday IS NULL; 结果：
https://i-blog.csdnimg.cn/direct/1c0e3a1c18f546519ac7403db87ae9cc.png
关键字：is not null 
select * from student where birthday is not null; 结果：
https://i-blog.csdnimg.cn/direct/5bb43ab69e5a4bb98bd0c4ba9dd3fa79.png常用聚合函数

1.count():统计个数
语法：select count(字段\常量\*) from 表名  
注意：count()方法不统计null
统计行数：
SELECT COUNT(sid) from student;
SELECT COUNT('a') from student; -- 不推荐
SELECT COUNT(1) from student;
SELECT COUNT(*) from student;  2.max():求最大值
3.min():求最小值
4.sum():求和
5.avg():求平均
SELECT COUNT(score) from sc;
SELECT sum(score) from sc;
SELECT avg(score) from sc;
SELECT max(score) from sc;
SELECT min(score) from sc; 6.综合
案例1. 统计出一共有多少场测验，平均分，总成绩、最高分、最低分
select count(*),sum(score),avg(score),max(score),min(score) from sc; 结果：
https://i-blog.csdnimg.cn/direct/d0de54c0c6dd4c73a833db57fd090ede.png
 分组-group by

关键字:group by
案例1.
select ssex,count(*) from student group by ssex; 结果：
https://i-blog.csdnimg.cn/direct/3d518fb2c42c467d81163cd4d5041c28.png
案例2.查询各班有多少人
select classid,count(*) from student group by classid; 结果：
https://i-blog.csdnimg.cn/direct/ae822704231c41b0b000c19a72cc0a01.png
案例3.查看成绩表中每个同学的总分和平均分
select sid,sum(score),avg(score) from sc group by sid; 运行结果：
https://i-blog.csdnimg.cn/direct/dc43da68c7eb4a838d0cadf28ab35ac7.png
案例4.统计平均分不合格的同学
SELECT sid,avg(score) FROM sc
WHERE avg(score)<60 GROUP BY sid;          运行后发现报错：
https://i-blog.csdnimg.cn/direct/93a147082de44790acbeba4e8efc2eb0.png
        缘故原由是：where语句是用来判断每一条子句的，反面不能跟聚合函数，我们可以使用having关键字来判断。
select sid,avg(score) from sc group by sid having avg(score)<60;
结果：
https://i-blog.csdnimg.cn/direct/08c6141ab3f74178b0d103441507a08c.png
排序-order by

升序-asc

使用ASC关键字，也可以不写，默认为升序排序:
select * from sc order by score;
select * from sc order by score asc; 结果：
https://i-blog.csdnimg.cn/direct/8e8d874f564a4a87bfc12a1474cfad95.png
降序-desc

        使用dsc关键字举行降序排序，不可省略：
select * from sc order by score desc; 结果：
https://i-blog.csdnimg.cn/direct/4b524cc68e1f406997f7c30048e75027.png
综合排序

        案例：先按score降序，若相同，再按cid升序排序
select * from sc score desc,cid asc; 结果：
https://i-blog.csdnimg.cn/direct/c93496c5e1a74f06b62ff61c6258e287.png
分页-limit

        语法：select * from student limit 开始位置 步长，位置=页码-1*步长
SELECT * from student LIMIT 0,3 结果：
https://i-blog.csdnimg.cn/direct/adffcca37ed24701ba30d7e3cc8bd422.png
2.
SELECT * from student LIMIT 1,3  结果：
https://i-blog.csdnimg.cn/direct/36ceb56fe29445f9b576de2925f4ab71.png
3. 找到成绩合格的总分数排名第二的 sid总成绩
select sid,sum(score) from sc
group by sid
having sum(score)>60
order by sum(score) desc
limit 1,1; 结果：
https://i-blog.csdnimg.cn/direct/d15b0f36c57c4cfb925efcf635184908.png
多表联查

        多表联查：针对数据库中两张或两张以上的表同时举行查询
        多表联查可以通过连接运算，即将多张通过主外键关系关联在一起举行查询，以下五张表是我们的数据：
https://i-blog.csdnimg.cn/direct/eab137444e7a4ce8b26b79693245f4a1.png


内联查询

 非等值查询

        非等值查询就是将两个表做了笛卡尔积运算，运行结果的逻辑时杂乱的
SELECT * FROM student,class;  https://i-blog.csdnimg.cn/direct/be5dd1dbeabe4dfbb2bb4beacd60592a.png
等值查询

        只有完全满足条件(主外键关系)的数据才气显示结果
案例1：查询出门生和班级的信息
select * from student,class
where student.classid=class.classid; 结果：
https://i-blog.csdnimg.cn/direct/68c8776f5fec4183a87104c38d3e5307.png
案例2： 5张表联查
select * from student,class,teacher,sc,course
where student.classid=class.classid and
course.Tid=teacher.Tid and
course.Cid=sc.Cid and
sc.Sid=student.sid; 结果：
https://i-blog.csdnimg.cn/direct/870c231e5da8410fa24ad75845b47143.png案例3：查询学过张三老师课程的门生的学号姓名
select sid,sname from student,teacher,sc,course
where course.Tid=teacher.Tid and
course.Cid=sc.Cid and
sc.Sid=student.sid and
tname='张三'; 结果：
https://i-blog.csdnimg.cn/direct/d0f6505d0f5b48c7bd687fba9a1346c2.png
案例4：查询每个门生的平均成绩
select sname,classname,avg(score) 'avg'
from student,class,sc
where student.classid=class.classid and student.Sid=sc.Sid
group by student.sid;
结果：
https://i-blog.csdnimg.cn/direct/31d22e3d402d4de3b0643e164a9b3dc8.png
inner join on内联 

        关键字：inner join on 两表的关系
        内联查询得当于：表少但数据量大,内存占用小，io高
案例1：查询出门生和班级的信息
select * from student
inner join class on student.classid=class.classid; 结果：
https://i-blog.csdnimg.cn/direct/20e07e5622f24c0c899a890f9a053765.png
 案例2：男同学的班级信息
SELECT * FROM student
INNER JOIN class on student.classid=class.classid
WHERE ssex='男'; 结果：
https://i-blog.csdnimg.cn/direct/d7740eef0ad9407c930d027211685ab7.png案例3：五表联查
SELECT * FROM studen
INNER JOIN class on student.classid=class.classid
INNER JOIN sc on sc.Sid=student.sid
INNER JOIN course ON course.Cid=sc.Cid
INNER JOIN teacher on course.Tid=teacher.Tid; 结果：
https://i-blog.csdnimg.cn/direct/48219e0291a24fc49cebd5146fcb57ab.png
案例4：查询每门课程的平均成绩 显示:课程名称 老师名称 平均成绩
SELECT Cname,Tname,avg(sc.score) from sc
inner JOIN course ON course.Cid=sc.Cid
INNER JOIN teacher on teacher.Tid=course.Tid
GROUP BY course.Cid; 结果：
https://i-blog.csdnimg.cn/direct/ecab6d689a3f4aaeb73994d3cdf13408.png
外联查询

重点要找到主查表：student
LEFT JOIN ON 左外联

主表(student)在 jion的左边：
SELECT *FROM student
LEFT JOIN class on student.classid=class.classid; 结果：
https://i-blog.csdnimg.cn/direct/3dddd71e751f4bae963b5bbf941fece2.png
 RIGHT JOIN ON 右外联

主表(student)在 jion的右边
SELECT *FROM class
RIGHT JOIN student on student.classid=class.classid;
结果：
https://i-blog.csdnimg.cn/direct/dcd912454ca048f9b0b625f877a67b85.png
综合

案例1：查询所有的门生都学过多少门课程
select sname,count(cname) '课程数' from student
left join sc on student.sid=sc.sid
LEFT JOIN course ON sc.cid=course.Cid
group by student.sid 结果：
https://i-blog.csdnimg.cn/direct/13537dddaf314122bd4efb47728e2d54.png
案例2：查询没有班级的同学
SELECT * FROM student
LEFT JOIN class ON class.classid=student.classid
WHERE class.classid is null; 结果：

案例3：查询没有同学的班级
SELECT * FROM class
LEFT JOIN student on class.classid=student.classid
WHERE student.sid is not NULL; 结果：
https://i-blog.csdnimg.cn/direct/657c88a2f95e43e1970592a1f96ec7f8.png并集-UNION

注意：UNION有去重机制(所有参数必须完全相同才会被去重)
案例1：查询所有人的名字
SELECT sname FROM student
UNION
SELECT tname FROM teacher 结果：
https://i-blog.csdnimg.cn/direct/7ee4241b140342508a3783e46ed8aa42.png
案例2：查询所有人的姓名和性别(在student表中，性别的范例是char,而在teacher表中，性别的范例是int,但是差别范例的字段可以合并)
SELECT sname,ssex FROM student
UNION
SELECT tname ,tsex FROM teacher 结果：
https://i-blog.csdnimg.cn/direct/b7f2ff91664b4907b84c67328830715f.png
//差别列数量的结果集不可以合并：
SELECT sname,ssex,sid FROM student
UNION
SELECT tname ,tsex FROM teacher 结果：
https://i-blog.csdnimg.cn/direct/4ee5f954dcf1441280fd93bbecc9c8d1.png
//起别名给第一个结果集才有效：
SELECT sname 姓名,ssex 性别 FROM student
UNION
SELECT tname ,tsex FROM teacher 结果：
https://i-blog.csdnimg.cn/direct/c04f3734f53d4da8bef90a6d65c9be36.png
SELECT sname ,ssexFROM student
UNION
SELECT tname 姓名,tsex 性别 FROM teacher 结果：
https://i-blog.csdnimg.cn/direct/57f9a69c756248d6a35abfaed73172ae.png
案例3： 查询没有班级的同学和查询没有同学的班级
SELECT * FROM student
LEFT JOIN class ON class.classid=student.classid
WHERE class.classid is null
UNION
SELECT * FROM student
RIGHT JOINclass on class.classid=student.classid
WHERE student.sid is not NULL;
结果：
https://i-blog.csdnimg.cn/direct/bd7110de74b34d938f0cee031d2301a0.png
全连接

去重

案例：要获取没有班级的同学、查询没有同学的班级还要既有班级又有同学的
SELECT * FROM student
LEFT JOIN class ON class.classid=student.classid
UNION
SELECT * FROM student
RIGHT JOINclass on class.classid=student.classid 不去重

SELECT * FROM student
LEFT JOIN class ON class.classid=student.classid
UNION ALL
SELECT * FROM student
RIGHT JOINclass on class.classid=student.classid  

子连接

        子查询又叫内部查询，子查询必须放在小括号中，子查询的效率极低
1.WHERE子查询

案例1：找出最大id的门生信息
select * from student
where sid=(select max(sid) from student) 结果：(先实行子查询，查找出最大的id，根据最大i查出门生信息)
https://i-blog.csdnimg.cn/direct/e61b16ac4cdc415db13f0bee524f4506.png
案例2：查询每个班id最大的门生
#子句：查询出每个最大的id
#select max(sid) from student group by classid

select * from student
where sid in(
select max(sid) from student group by classid
) 结果：
https://i-blog.csdnimg.cn/direct/60e506201f5645b19bf290b3ba632c2b.png
案例3：查询学过张三老师课程的门生
select * drom student where sid in(
    select sid from sc where cid=(
      select cid from course where tid=(
            select tid from teacher where tname='张三'))) 结果：
https://i-blog.csdnimg.cn/direct/783b2583bc4342d9ade678e9eafb9257.png
案例4：查询没学过张三老师课程的门生
select * drom student where sid not in(
    select sid from sc where cid=(
      select cid from course where tid=(
            select tid from teacher where tname='张三'))) 结果：
https://i-blog.csdnimg.cn/direct/b8741972f1e841fdbfcc79762c3d3585.png
2.FROM子查询

案例1：查询人数大于5的班级 显示：班级 人数
方法1：不使用子查询
select classname count(*) from class
left join student on class.classid=student.classid
group by class.classid
having count(*)>5 结果：
https://i-blog.csdnimg.cn/direct/cfeea0acfa31442dbc6188c1228099f7.png
方法2：使用子查询
select classname,人数 from class
left join
(select classid,count(*) 人数 from student
group by classid) t1
on class.classid=t1.classid
where 人数>5 结果：
https://i-blog.csdnimg.cn/direct/6b991c5aebec45eea6f2cfe10f86ea81.png
3.EXISTS子查询

用法：子句有结果，父查询实行；子句没有结果，父查询不实行：
select * from teacher
where exists(SELECT * FROM student where classid=1) 子句有结果：
https://i-blog.csdnimg.cn/direct/a03c22f50ceb4c16bfc7e932c11d4ced.png
父查询实行：
https://i-blog.csdnimg.cn/direct/25d4fa673a4349e8a2021deaff74d3bb.png

4.ANY\SOME\ALL子查询

案例1：查询1班比2班最低成绩高的门生
方法1：使用子查询
//子查询先查询出2班的最低成绩
/*
SELECT min(score) from sc
LEFT JOIN student on sc.sid=student.Sid
where sc.cid=2;
*/
select student.* from sc
left join student on sc.sid=student.Sid
where student.classid=1 and score>(
    SELECT min(score) from sc
    LEFT JOIN student on sc.sid=student.Sid
    where sc.cid=2;
) 方法2：使用any
SELECT DISTINCT student.* FROM sc
LEFT JOIN student on sc.sid=student.Sid
WHERE student.classid=1 and score >any(
SELECT score from sc
        LEFT JOIN student on sc.sid=student.Sid
        where student.classid=2) 结果：
https://i-blog.csdnimg.cn/direct/3d0a3def71aa44e9b5324d7b4a49c8ac.png
 案例2：查询1班比2班最高成绩高的门生
使用all关键字：
SELECT DISTINCT student.* FROM sc
LEFT JOIN student on sc.sid=student.Sid
WHERE student.classid=1 and score >all(
SELECT score from sc
        LEFT JOIN student on sc.sid=student.Sid
        where student.classid=2) 结果：
https://i-blog.csdnimg.cn/direct/dd3be67cc72a48db870e694587893eba.png
结果集的控制语句

1.IF(expr1,expr2,expr3)

参数：
-- expr1 条件
-- expr2 条件建立，显示数据
-- expr3 条件不建立，显示数据
//例如在teacher表中，性别是通过int范例表示的，当我们想让结果集按我们规定的:1代表女，0代表男来显示，就可以使用if控制语句：
select tid,tname,if(tsex=1,'女','男') 性别,tbirthday,taddress from teacher; 结果：
https://i-blog.csdnimg.cn/direct/fcb8c8482c1f43cb84b14318865d7abe.png
2.IFNULL(expr1,expr2)

参数：
-- expr1 字段
-- expr2 字段为null时显示的默认值
//查询student表中的数据，当birthday为null时显示"石头里蹦出来的"：
select sidsname,ifnull(birthday,'石头里蹦出来的') 生日,ssex from student 结果：
https://i-blog.csdnimg.cn/direct/c6c0c1f2a4b842948144279e80d74426.png
3.CASE WHEN THEN END

这个语句类似于java中的switch语句，详细用法如下：
select tid,tname
case tsex
    when 0 then '男'
    when 1 then '女'
    else '保密'
END '性别' ,tbirthday FROM teacher 结果：
https://i-blog.csdnimg.cn/direct/09e1c73d22f3408fabf6bf1eb61fda86.png
注意：这个语句自带break语句：
SELECT score,
CASE
        when score>=90 THEN 'A'
        when score>=80 THEN 'B'
        when score>=70 THEN 'C'
        when score>=60 THEN 'D'
        when score <60 THEN '不及格'
end '等级' from sc; 结果：
https://i-blog.csdnimg.cn/direct/640c0d3b63444f538d9e97ba1c193795.png
案例1：统计各个分数段的人数
思绪：通过CASE WHEN THEN END语句可以只显示出满足该成绩段的成绩，其余不显示：
SELECT
CASE WHEN score<=100 and score>=90 then score end '100-90',
CASE WHEN score<=90 and score>=70 then score end '90-70',
CASE WHEN score<=70 and score>=60 then score end '70-60',
CASE WHEN score<60 then score end '不及格'
from sc 结果：
https://i-blog.csdnimg.cn/direct/20b112d1df6944e5bd915dfcc00b7e76.png
然后可以直接通过count()统计每个分数段的行数(count不统计null值），得出的行数就是该分数段的人数：
SELECT'人数' 分数段,
count(CASE WHEN score<=100 and score>=90 then score end)'100-90',
count(CASE WHEN score<=90 and score>=70 then score end)'90-70',
count(CASE WHEN score<=70 and score>=60 then score end) '70-60',
count(CASE WHEN score<60 then score end)'不及格'
FROM sc 结果：
https://i-blog.csdnimg.cn/direct/2c0c4d88e809407eb9ec6379babe438b.png
方法2：
SELECT '100-90' 分数段 ,count(score) 分数 FROM sc
WHERE score<=100 and score>=90
UNION
SELECT '90-70' 分数段 ,count(score) 分数 FROM sc
WHERE score<=90 and score>=70
UNION
SELECT '70-60' 分数段 ,count(score) 分数 FROM sc
WHERE score<=70 and score>=60
UNION
SELECT '不及格' 分数段 ,count(score) 分数 FROM sc
WHERE score<=100 and score<60  结果：
https://i-blog.csdnimg.cn/direct/fed7a492487240d58ad5ff74f1c0d35b.png

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

查看完整版本: 数据库(MySQL)-DQL数据查询语言