MySQL8第48期-MySQL查询数据之为表和字段取别名
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeOFTiaEocddJGsc71cX1fKNqZGG5gfEqdGczZwN6BCKFEibJjIY2IHIyA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;">点击蓝字，关注<span style="color: black;">咱们</span></strong><p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在前几期的<span style="color: black;">查找</span>中，有<span style="color: black;">无</span><span style="color: black;">朋友</span>们<span style="color: black;">重视</span>到有的<span style="color: black;">地区</span><span style="color: black;">运用</span>AS关键字<span style="color: black;">做为</span><span style="color: black;">查找</span>结果中的某一列的指定名<span style="color: black;">叫作</span>。</span></p><strong style="color: blue;">（1）为表取别名</strong>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">当表名字很长<span style="color: black;">或</span>执行<span style="color: black;">有些</span>特殊<span style="color: black;">查找</span>时，为了方便操作<span style="color: black;">或</span><span style="color: black;">必须</span>多次<span style="color: black;">运用</span>相同的表时，<span style="color: black;">能够</span>为表指定别名，用这个别名替<span style="color: black;">表率</span>原来的名<span style="color: black;">叫作</span>。为表取别名的基本语法格式为：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">表名 表别名</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">“表名”为数据库中存储的数据表的名<span style="color: black;">叫作</span>，“表别名”为<span style="color: black;">查找</span>时指定的表的新名<span style="color: black;">叫作</span>，AS关键字为可选参数。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为orders表取别名o，<span style="color: black;">查找</span>30001订单的下单日期，SQL语句如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT * FROM orders AS o</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">WHERE o.o_num=30001;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeicrWTpGJAMMHxeTwC98C2Qd7I5YejsWO04DmDDobpbjZhPnm5m9RNFA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为customers和orders表分别取别名，并进行连接<span style="color: black;">查找</span>，SQL语句如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT c.c_id,o.o_num</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">FROM customers AS c</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">LEFT OUTER JOIN orders AS o</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">ON c.c_id=o.c_id;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeCmyQICialUx4B5fgic3PG1waGkiboTu0uFICXqB0lAZB3eQz1ZDibQYIhQ/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在前面介绍内连接<span style="color: black;">查找</span>时指出自连接是一种特殊的内连接，在连接<span style="color: black;">查找</span>中的两个表都是同一个表，其<span style="color: black;">查找</span>语句如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT f1.f_id,f1.f_name</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">FROM fruits AS f1,fruits AS f2</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">WHERE f1.s_id=f2.s_id</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">AND f2.f_id=a1;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeiaoYOt0gokC3u6CfBPcAaTHoWIMuOicfkVg1BFnrsFQ7oYC2lxibmTLQw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">以上<span style="color: black;">查找</span>结果，等同于：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT f_id,f_name</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">FROM fruits</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">WHERE s_id=101;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaewA6gtx762Y8AobAGzu01qaic7lrSo0YbWncEB5YfuU30pW13dMRtg9w/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在为表取别名时，要<span style="color: black;">保准</span><span style="color: black;">不可</span>与数据库中其他表的名<span style="color: black;">叫作</span>冲突。</span></p><strong style="color: blue;">（2）为字段取别名</strong>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在有些<span style="color: black;">状况</span>下，<span style="color: black;">表示</span>的列的名<span style="color: black;">叫作</span>会很长<span style="color: black;">或</span>名<span style="color: black;">叫作</span><span style="color: black;">不足</span>直观，MySQL<span style="color: black;">能够</span>指定列别名，替换字段或表达式。为字段取别名的基本语法格式为：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">列名 列别名</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">“列名”为表中字段定义的名<span style="color: black;">叫作</span>，“列别名”为字段新的名<span style="color: black;">叫作</span>，AS关键字为可选参数。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">查找</span>fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，<span style="color: black;">查找</span>表中f_price < 8的<span style="color: black;">果蔬</span>的名<span style="color: black;">叫作</span>，SQL语句如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">f1.f_name AS fruit_name,</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">f1.f_price AS fruit_price</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">FROM fruits AS f1</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">WHERE f1.f_price<8;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeoZQMc4zpq4icboljQvtwXgPVibL0VmP5ibBWvChsjBGJVwAnnkwBymUmQ/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">亦</span><span style="color: black;">能够</span>为SELECT子句中的计算字段取别名。例如，对<span style="color: black;">运用</span>COUNT聚合函数<span style="color: black;">或</span>CONCAT等系统函数执行的结果字段取别名。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">查找</span>suppliers表中字段s_name和s_city，<span style="color: black;">运用</span>CONCAT函数连接这两个字段值，并取列别名为suppliers_title。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">倘若</span><span style="color: black;">无</span>对连接后的值取别名，其<span style="color: black;">表示</span>列名<span style="color: black;">叫作</span>将会<span style="color: black;">不足</span>直观，SQL语句如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT CONCAT(TRIM(s_name),(,TRIM(s_city),))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">FROM suppliers</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">ORDER BY s_name;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaefs5bHIHtoSjClVTYrIianYuygt8Oma5UGPc2MjaEHicsWicl8dxsfkZjg/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">这般</span>的结果让人很<span style="color: black;">不易</span>理解，<span style="color: black;">倘若</span>为字段取一个别名，将会使结果清晰，SQL语句如下：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">SELECT CONCAT(TRIM(s_name),(,TRIM(s_city),))</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">AS suppliers_title</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">FROM suppliers</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">ORDER BY s_name;</span></p><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtae81nF4nL8Czygrsbo7x4hfOKJQBN37hBLWbVnBM0ePhNCewE4LBgNyw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">表别名<span style="color: black;">仅在</span>执行<span style="color: black;">查找</span>的时候<span style="color: black;">运用</span>，并不在返回结果中<span style="color: black;">表示</span>，而列别名定义之后，将返回给客户端显示，<span style="color: black;">表示</span>的结果字段为字段列的别名。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">（以上内容参考书籍《MySQL8从入门到精通》，作者王英英，如有<span style="color: black;">必须</span>请<span style="color: black;">自动</span>购买，如有侵权，请联系删除。）</p><img src="https://mmbiz.qpic.cn/sz_mmbiz_jpg/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeQYXmRLicvMplY0agXJOIcgwia4S6ic7RmYVOZd0YKLkWPMCR5CibGOibw9g/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;">扫码关注</strong><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/xU2ibUkcVOo1DhJX8ypudK8P8fVSfZtaeOFTiaEocddJGsc71cX1fKNqZGG5gfEqdGczZwN6BCKFEibJjIY2IHIyA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
感谢你的精彩评论，为我的思绪打开了新的窗口。 外链发布论坛学习网络优化SEO。 你的话语真是温暖如春，让我心生感激。 “板凳”（第三个回帖的人）
页:
[1]