mysql/mariadb知识点总结（14）：select语句总结之一：基本语句

Author： admin
发布时间：May 24, 2021
121 views
No comments
3069 words
Categories： Mysql

在之前的文章中，已经总结了insert语句，update语句与delete语句，此处不再赘述，这篇文章总结了select语句的基本用法，select语句的分组与聚合、多表查询等常用语法将在其他文章中单独进行总结。

&nbsp;

在本博客中，”mysql”是一个系列文章，这些文章主要对mysql/mariadb的常用知识点进行了总结，每一篇博客总结的知识点有所不同，具体内容可参考mysql文章列表。

mysql文章列表直达链接：<a href="https://www.zsythink.net/archives/tag/mysql/" target="_blank" rel="noopener">mysql知识点总结</a>

&nbsp;

最简单最粗暴的查询语句如下，查询tb1表中的所有数据，如果表中的数据量巨大，那么使用如下语句纯属作死，非必要情况下，一般不要这样对数据进行查询，在如下示例中，为了方便总结，可能会经常使用这种查询方式。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1;</span></div>
</div>
</div>
</div>
&nbsp;

表示从tb1表中查询出所有数据，但是只显示前3行。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 limit </span><span class="enlighter-n1">3</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

从tb1表中查询出name字段与age字段的数据，即使这样写，也没有比上例的语句好多少，它仍然是显示表中的所有行的指定字段，表中的数据量较大时，这样写也是非常不好的，除非必要，一般不要这样写。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select name,age from tb1;</span></div>
</div>
</div>
</div>
&nbsp;

从tb1表中查询出符合条件的数据，使用where字句给定条件，带有筛选条件的查询语句则会比上面两种查询语句好很多，如下示例中给出常用的条件表达式。

如下语句表示从tb1表中查询出age等于25的行的name和age字段。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select name,age from tb1 where age = </span><span class="enlighter-n1">25</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

查出tb1表中age不等于28的数据。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age != </span><span class="enlighter-n1">28</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

如下两条语句均表示从tb1表中查询出age大于等于25并且小于等于28的数据。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age </span><span class="enlighter-g1">&gt;</span><span class="enlighter-text">= </span><span class="enlighter-n1">25</span><span class="enlighter-text"> and age </span><span class="enlighter-g1">&lt;</span><span class="enlighter-text">=</span><span class="enlighter-n1">28</span><span class="enlighter-text">;</span></div>
</div>
<div class="">
<div><span class="enlighter-text">select name,age from tb1 where age between </span><span class="enlighter-n1">25</span><span class="enlighter-text"> and </span><span class="enlighter-n1">28</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

如下语句表示从tb1表中查询出age等于25或者等于28的数据。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age = </span><span class="enlighter-n1">25</span><span class="enlighter-text"> or age = </span><span class="enlighter-n1">28</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

如下语句表示从tb1表中查询出age不在25到28区间中的数据。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age not between </span><span class="enlighter-n1">25</span><span class="enlighter-text"> and </span><span class="enlighter-n1">28</span><span class="enlighter-text">;</span></div>
</div>
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age </span><span class="enlighter-g1">&lt;</span> <span class="enlighter-n1">25</span><span class="enlighter-text"> or age </span><span class="enlighter-g1">&gt;</span> <span class="enlighter-n1">28</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

使用like结合通配符进行模糊查询，如下语句表示查询tb1表中name字段以j开头的数据，”%”在查询语句中表示”任意长度的任意字符”.
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where name like </span><span class="enlighter-s0">'j%'</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

如下语句表示查询tb1表中name字段以t开头，并且只有三个字符的数据，”_”在查询语句中表示”任意单个字符”，下例中的语句中，在t后面跟随了两个”_”,表示t后面的两个字符可以是任意字符。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where name like </span><span class="enlighter-s0">'t__'</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

也许你觉得还不够灵活，或许你更习惯使用正则表达式作为匹配条件，没有关系，满足你，我们可以使用rlike结合正则表达式，对字符数据进行模糊查询，所以，查询语句能有多强大的功能，就看你的正则表达式运用的有多熟练了，示例语句如下。

如下语句表示查询出tb1表中name字段以t开头的所有数据，正则表达式的含义此处不再赘述。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where name rlike </span><span class="enlighter-s0">'^t.*'</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

我们还可以从指定的列表中匹配对应的条件，使用in关键字指定列表，示例如下，如下语句表示从tb1表中查找出age等于22、23、24或25中的任意一个的行的所有数据。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age </span><span class="enlighter-k1">in</span> <span class="enlighter-g1">(</span><span class="enlighter-n1">22</span><span class="enlighter-text">,</span><span class="enlighter-n1">23</span><span class="enlighter-text">,</span><span class="enlighter-n1">24</span><span class="enlighter-text">,</span><span class="enlighter-n1">25</span><span class="enlighter-g1">)</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

除了使用in，我们还可以使用not in,聪明如你一定秒懂，not in就是in的对立面，比如，查询出tb1表中age不等于28、43、33的数据。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 where age not </span><span class="enlighter-k1">in</span> <span class="enlighter-g1">(</span><span class="enlighter-n1">28</span><span class="enlighter-text">,</span><span class="enlighter-n1">33</span><span class="enlighter-text">,</span><span class="enlighter-n1">43</span><span class="enlighter-g1">)</span><span class="enlighter-text">;</span></div>
</div>
</div>
</div>
&nbsp;

我们可以对查询出的数据进行排序，如下示例表示查询tb1表中的所有数据，并且按照age的值从小到大进行升序排序，asc表示升序排序，asc可省，默认使用升序排序。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 order by age;</span></div>
</div>
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 order by age asc;</span></div>
</div>
</div>
</div>
&nbsp;

如下示例表示查询tb1表中的所有数据，并且按照age的值从大到小进行降序排序。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 order by age desc;</span></div>
</div>
</div>
</div>
&nbsp;

查询tb1表中的所有数据，并且按照age的值从大到小进行降序排序，如果多行之间的age字段的值相同时，这些行再根据name字段进行升序排序。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from tb1 order by age desc,name asc;</span></div>
</div>
</div>
</div>
&nbsp;

我们可以在查询某字段的时候去重，使用DISTINCT关键字表示去重查询，比如，查询学生的年龄并去重显示年龄。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select distinct age from students;</span></div>
</div>
</div>
</div>
&nbsp;

我们也可以在查询时给字段添加别名，以便显示为我们指定的列名。
<div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll">
<div class="enlighter-toolbar">
<div class="enlighter-btn enlighter-btn-raw"></div>
<div class="enlighter-btn enlighter-btn-copy"></div>
<div class="enlighter-btn enlighter-btn-window"></div>
</div>
<div class="enlighter">
<div class="">
<div><span class="enlighter-text">select name as StuName,age from tb1;</span></div>
</div>
</div>
</div>
<img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011117_0232_1.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011117_0232_1.png"  style="">

&nbsp;

select的基本常用语句就总结到这里，下一篇文章将会总结select语句中的分组与聚合，直达链接 <a href="https://www.zsythink.net/archives/1058" target="_self" rel="noopener">select语句总结之二：分组与聚合</a>

转载自朱双印日志https://www.zsythink.net/archives/1051

Last modification：May 29, 2024