MySQL聚合、日期、字符串等函数深度剖析
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">MySQL系列</a></li><li><a href="#_label1">前言</a></li><li><a href="#_label2">一、聚合函数</a></li><li><a href="#_label3">二、日期函数</a></li><li><a href="#_label4">三、字符串函数</a></li><li><a href="#_label5">四、数学函数</a></li><li><a href="#_label6">五、其他函数</a></li></ul></div><p class="maodian"><a name="_label0"></a></p><h2>MySQL系列</h2><p class="maodian"><a name="_label1"></a></p><h2>前言</h2>
<p>MySQL 提供了丰富的内置函数，用于处理数据、执行计算、转换格式等操作，本篇将介绍MySQL中常用的一些函数。<br /><code>本篇文章内容已操作为主</code></p>
<p><code>这里的函数比较简单，不再解释了，再对其解释就有一种强说愁的感觉了</code></p>
<p><a href="https://www.jb51.net/database/347430j4k.htm" target="_blank">上篇文章：MySQL 数据操作全流程：创建、读取、更新与删除实战</a></p>
<p class="maodian"><a name="_label2"></a></p><h2>一、聚合函数</h2>
<p><code>这部分函数都比较简单</code></p>
<table><thead><tr><th>函数名</th><th>作用</th><th>示例</th><th>结果</th></tr></thead><tbody><tr><td><code>SUM(col)</code></td><td>求和</td><td><code>SUM(amount)</code></td><td>所有 amount 的总和</td></tr><tr><td><code>AVG(col)</code></td><td>平均值</td><td><code>AVG(age)</code></td><td>平均年龄</td></tr><tr><td><code>COUNT(col)</code></td><td>计数（忽略 NULL）</td><td><code>COUNT(id)</code></td><td>行数</td></tr><tr><td><code>COUNT(*)</code></td><td>计数（包含 NULL）</td><td><code>COUNT(*)</code></td><td>总行数</td></tr><tr><td><code>MAX(col)</code></td><td>最大值</td><td><code>MAX(score)</code></td><td>最高分数</td></tr><tr><td><code>MIN(col)</code></td><td>最小值</td><td><code>MIN(price)</code></td><td>最低价格</td></tr></tbody></table>
<p><strong>测试表</strong></p>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
create table exam_result (
id int unsigned primary key auto_increment,
name varchar(20) not null comment '同学姓名',
chinese float default 0.0 comment '语文成绩',
math float default 0.0 comment '数学成绩',
english float default 0.0 comment '英语成绩'
);
</pre></div>
<p style="text-align:center"><strong>表内容</strong></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592480.png" /></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592488.png" /></p>
<p><code>本篇文章主要以上面两表做测试，上篇文章中已经创建，这里直接使用</code></p>
<p><strong>1、统计班级共有多少同学</strong></p>
<div class="jb51code"><pre class="brush:sql;">select count(*) from students;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592417.png" /></p>
<p><strong>2、统计班级有多少 qq 号</strong></p>
<div class="jb51code"><pre class="brush:sql;">select count(qq) from students;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592416.png" /></p>
<p>对比上表可以看到<code>count</code>函数，对于<code>NULL</code>值，不做统计。</p>
<p><strong>3、统计本次考试的数学成绩分数个数</strong></p>
<div class="jb51code"><pre class="brush:sql;">select count(math) from exam_result;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592473.png" /></p>
<p>对比上表可以看到<code>count</code>函数，对于重复值，不做统计。</p>
<p><strong>4、统计数学成绩不及格人数</strong></p>
<div class="jb51code"><pre class="brush:sql;">select count(math) from exam_result where math<60;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592471.png" /></p>
<p><code>count</code>函数可以配合其他语句使用。</p>
<p><strong>5、统计平均总分</strong></p>
<div class="jb51code"><pre class="brush:sql;">select avg(math+chinese+english) 平均总分 from exam_result ;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592412.png" /></p>
<p><strong>6、返回英语最高分</strong></p>
<div class="jb51code"><pre class="brush:sql;">select max(english)from exam_result ;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592596.png" /></p>
<p><strong>7、返回 > 70 分以上的数学最低分</strong></p>
<div class="jb51code"><pre class="brush:sql;">select min(math)from exam_result where math >70;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592570.png" /></p>
<p class="maodian"><a name="_label3"></a></p><h2>二、日期函数</h2>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592662.png" /></p>
<p><strong>1、获取当前年月日</strong></p>
<div class="jb51code"><pre class="brush:sql;">select current_date();
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592632.png" /></p>
<p><strong>2、获取当前时分秒</strong></p>
<div class="jb51code"><pre class="brush:sql;">select current_time;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592615.png" /></p>
<p><strong>3、获取时间戳</strong></p>
<div class="jb51code"><pre class="brush:sql;">select current_timestamp;
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592718.png" /></p>
<p><strong>4、在时间中提取日期部分</strong></p>
<div class="jb51code"><pre class="brush:sql;">select date(current_timestamp());
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592625.png" /></p>
<p><strong>5、在日期的基础上加上日期</strong></p>
<div class="jb51code"><pre class="brush:sql;">select date_add(current_date,interval 10 day);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592631.png" /></p>
<p><code>获取当前日期，并在该日期的基础上增加十天</code></p>
<blockquote><p><code>interval</code>后可以根据需要使用不同单位（年、月、日、分、秒)</p></blockquote>
<p><strong>6、在日期的基础上减去日期</strong></p>
<div class="jb51code"><pre class="brush:sql;">select date_sub(current_date,interval 10 day);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592742.png" /></p>
<p><code>获取当前日期，并在该日期的基础上减去十天</code></p>
<p><strong>7、计算两个日期之间相差多少天</strong></p>
<div class="jb51code"><pre class="brush:sql;">select datediff(current_date,'1949-10-01');
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592621.png" /></p>
<p><code>中国成立，距今多少天</code></p>
<p><strong>8、获取当前日期和时间</strong></p>
<div class="jb51code"><pre class="brush:sql;">select now();
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592774.png" /></p>
<p><strong>9、测试</strong></p>
<div class="jb51code"><pre class="brush:sql;">//创建一个留言表
create table msg (
id int primary key auto_increment,
content varchar(30) not null,
sendtime datetime
);
//向表中插入测试数据
insert into msg(content,sendtime) values('hello1', now());
insert into msg(content,sendtime) values('hello2', now());
select * from msg;</pre></div>
<p><strong>显示所有留言信息，发布日期只显示日期，不用显示时间：</strong></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592727.png" /></p>
<p><strong>查询在1分钟内发布的帖子：</strong></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592746.png" /></p>
<p><code>可以看到日期是支持直接比较的</code></p>
<p class="maodian"><a name="_label4"></a></p><h2>三、字符串函数</h2>
<p><code>函数都可以配合select操作对表中的数据进行操作，这里仅对部分场景做演示</code></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592885.png" /></p>
<p><strong>1、查看字符串的字符集</strong></p>
<div class="jb51code"><pre class="brush:sql;">select charset(string)；
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592880.png" /></p>
<p><strong>2、要求显示exam_result表中的信息，显示格式：“XXX的语文分：XXX，数学分：XXX，英语分：XXX”</strong></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592875.png" /></p>
<p><strong>3、在字符串中查找字符串</strong></p>
<div class="jb51code"><pre class="brush:sql;">select instr(string,substring);
</pre></div>
<p>在<code>string</code>中查找字符串<code>substring</code>出现的位置，找到返回下标（从1开始），未找到返回0。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592970.png" /></p>
<blockquote><p>当目标字符串重复出现时，返回的时第一次出现的下标</p></blockquote>
<p><strong>4、字符串转为大写</strong></p>
<div class="jb51code"><pre class="brush:sql;">select ucase(strig);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592966.png" /></p>
<p><strong>5、字符串转为小写</strong></p>
<div class="jb51code"><pre class="brush:sql;">select lcase(string);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592977.png" /></p>
<p><strong>6、从字符串左端提取len个字符</strong></p>
<div class="jb51code"><pre class="brush:sql;">select left(string,len);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592914.png" /></p>
<p><strong>6、从字符串右端提取len个字符</strong></p>
<div class="jb51code"><pre class="brush:sql;">select right(string,len);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592982.png" /></p>
<p><strong>7、求字符串占用的字节数</strong></p>
<div class="jb51code"><pre class="brush:sql;">selecty
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410592911.png" /></p>
<p><code>length（）</code>函数在 MySQL 中计算的是字符串的字节长度，而不是字符个数，当前所使用的字符集汉字占三个字节。</p>
<p><strong>8、在字符串中进行字符串的替换 replace</strong></p>
<div class="jb51code"><pre class="brush:sql;">select replace(substring,string,str);
</pre></div>
<p>在<code>substring</code>中查找<code>string</code>，并将其替换为<code>str</code></p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593042.png" /></p>
<p><code>这种替换方式不会影响原表内容，若未找到则不做处理</code></p>
<p><strong>9、字符串截取 substring</strong></p>
<div class="jb51code"><pre class="brush:sql;">select substring(string,pos,len);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593055.png" /></p>
<p>从字符串<code>string</code>的<code>pos</code>处开始，向后截取<code>len</code>个字符。</p>
<p><strong>10、去除字符串中最开始和最后的空格 trim</strong></p>
<ul><li>trime：去除字符串两端空格</li><li>ltrim：去除字符串最左边的空格</li><li>rtrim：去除字符串右边的</li></ul>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593163.png" /></p>
<blockquote><p>在保存用户信息数据时，一般先对数据执行去除空格操作。由于网络传输过程可能引入不可见空字符，若直接存储含此类字符的数据，后续用户登录时，比如输入密码因存在空格匹配不上，会引发登录失败问题，且排查难度极大。所以，要先过滤掉字符串中的空格，再将处理后的数据存入数据库，以此规避因隐性空格导致的登录故障</p></blockquote>
<p class="maodian"><a name="_label5"></a></p><h2>四、数学函数</h2>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593188.png" /></p>
<p><strong>1、abs 取绝对值</strong></p>
<div class="jb51code"><pre class="brush:sql;">select abs(N);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593137.png" /></p>
<p><strong>2、bin 转二进制</strong></p>
<div class="jb51code"><pre class="brush:sql;">select bin(N);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593145.png" /></p>
<p>可以看到在对小数，进行二进制转换时，会将小数进行向下取整后再操作。</p>
<p><strong>3、hex 转十六进制</strong></p>
<div class="jb51code"><pre class="brush:sql;">select hex(N);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593157.png" /></p>
<p><strong>4、 conv 进制转换</strong></p>
<div class="jb51code"><pre class="brush:sql;">select conv(N,fromm_base,to_base);
</pre></div>
<p>将数字<code>N</code>，从<code>from_base</code>进制 转换成<code> to_base</code>进制.</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593119.png" /></p>
<p><strong>5、format 格式化，保留小数</strong></p>
<div class="jb51code"><pre class="brush:sql;">select format(N,D);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593145.png" /></p>
<p>将<code>N</code>保留<code>D</code>位小数,处理小数部分遵循四舍五入，若小数部分不够就补0.</p>
<p><strong>6 mod 取模</strong></p>
<div class="jb51code"><pre class="brush:sql;">select mod(x,y);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593291.png" /></p>
<p><code>mod</code>返回<code>x</code>对<code>y</code>取模的值，这里负数取模的方式大家可以自己尝试。<br /><strong>7、rand生成随机数</strong></p>
<div class="jb51code"><pre class="brush:sql;">select rand();
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593260.png" /></p>
<p>生成的数是从 0.0 ~ 1.0，若想要生成指定范围的我们就直接 * 10n即可实现（如 * 10的话就是 0 ~ 10）</p>
<p><strong>8、ceiling 向上取整</strong></p>
<div class="jb51code"><pre class="brush:sql;">select ceiling(N);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593225.png" /></p>
<p>可以看到向上取整，就是当存在小述部分时，去掉小鼠部分直接+1；</p>
<p><strong>9、floor 向下取整</strong></p>
<div class="jb51code"><pre class="brush:sql;">select floor(N);
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593255.png" /></p>
<p class="maodian"><a name="_label6"></a></p><h2>五、其他函数</h2>
<p><strong>1、查看当前用户 user</strong></p>
<div class="jb51code"><pre class="brush:sql;">select user();
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593287.png" /></p>
<p>获取当前连接到 MySQL 服务器的用户信息，返回结果的格式为 <code>用户名@主机名'</code></p>
<p><strong>2、database查看当前数据库</strong></p>
<div class="jb51code"><pre class="brush:sql;">select database();
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593254.png" /></p>
<p>返回当前会话中使用的数据库名称</p>
<p><strong>3、md5 加密</strong><br />在实际开发中，密码通常不会以明文形式直接存储在数据库中，而 <strong>MD5 哈希算法是常用的密码加密方案之一</strong>。其核心作用是将原始密码通过加密计算转换为一段固定长度（32 位）的哈希字符串，从而避免明文密码在存储或传输过程中泄露的风险。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593224.png" /></p>
<p>这种加密方式，缺点很多，这个我在网络传输部分已经介绍了，这里就补赘述了。</p>
<p><strong>4、ifnull(val1,val2)</strong></p>
<p>当 <code>val1</code> 为 <code>NULL</code> 时返回 <code>val2</code>，否则返回 <code>val1</code> 本身</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010410593266.png" /></p>
頁:
[1]