SQL 别名 #
通过使用 SQL,可以为表名称或列名称指定别名。
基本上,创建别名是为了让列名称的可读性更强。
列的 SQL 别名语法 #
SELECT column_name AS alias_name
FROM table_name;
表的 SQL 别名语法 #
SELECT column_name(s)
FROM table_name AS alias_name;
列的别名实例 #
下面的 SQL 语句指定了两个别名,一个是 domainname 列的别名,一个是 icp 列的别名。提示:如果列名称包含空格,要求使用双引号或方括号:
MariaDB [learnsql]> select domainname as n, icp as i from domains;
+---------------+-----------+
| n | i |
+---------------+-----------+
| getos.org | namesilo |
| dotbbq.com | wanwang |
| lyq.wiki | wanwang |
| les.moe | namesilo |
| regex.in | namesilo |
| blbl.dev | namesilo |
| raspi.in | namesilo |
| pornie.in | namesilo |
| aosp.me | namesilo |
| bailuyuan.org | namesilo |
| linuxuc.com | wanwang |
| ustv.xyz | namesilo |
| unixetc.com | wanwang |
| ciux.org | namesilo |
| avlist.top | namesilo |
| suopo.net | google |
| ltecn.com | namesilo |
| pornie.top | namesilo |
| zjq.xyz | dnspod.cn |
| alair.cn | dnspod.cn |
| ymqd.net | dnspod.cn |
| nasplus.cn | dnspod.cn |
| manjushri.cn | dnspod.cn |
| pushto.cn | dnspod.cn |
| gfwlist.cn | dnspod.cn |
| byhzg.cn | dnspod.cn |
| mua.wiki | gandi.net |
+---------------+-----------+
27 rows in set (0.001 sec)
在下面的 SQL 语句中,我们把三个列(id、icp)结合在一起,并创建一个名为 “index_info” 的别名:
MariaDB [learnsql]> select domainname, concat(id,',',icp) as index_info from domains;
+---------------+--------------+
| domainname | index_info |
+---------------+--------------+
| getos.org | 4,namesilo |
| dotbbq.com | 6,wanwang |
| lyq.wiki | 7,wanwang |
| les.moe | 8,namesilo |
| regex.in | 9,namesilo |
| blbl.dev | 12,namesilo |
| raspi.in | 13,namesilo |
| pornie.in | 14,namesilo |
| aosp.me | 15,namesilo |
| bailuyuan.org | 16,namesilo |
| linuxuc.com | 17,wanwang |
| ustv.xyz | 18,namesilo |
| unixetc.com | 19,wanwang |
| ciux.org | 20,namesilo |
| avlist.top | 21,namesilo |
| suopo.net | 22,google |
| ltecn.com | 24,namesilo |
| pornie.top | 25,namesilo |
| zjq.xyz | 43,dnspod.cn |
| alair.cn | 44,dnspod.cn |
| ymqd.net | 45,dnspod.cn |
| nasplus.cn | 46,dnspod.cn |
| manjushri.cn | 47,dnspod.cn |
| pushto.cn | 48,dnspod.cn |
| gfwlist.cn | 49,dnspod.cn |
| byhzg.cn | 50,dnspod.cn |
| mua.wiki | 52,gandi.net |
+---------------+--------------+
27 rows in set (0.008 sec)
表的别名实例 #
下面的 SQL 语句选取icp为 “namesilo” 的所有访问记录。我们使用 “domains” 和 “logs” 表,并分别为它们指定表别名 “d” 和 “l”(通过使用别名让 SQL 更简短)
MariaDB [learnsql]> SELECT d.domainname, d.icp, l.google, l.baidu FROM domains AS d, logs AS l WHERE d.id=l.id and d.icp="namesilo";
+---------------+----------+--------+--------+
| domainname | icp | google | baidu |
+---------------+----------+--------+--------+
| getos.org | namesilo | 001864 | 002745 |
| les.moe | namesilo | 001166 | 000350 |
| regex.in | namesilo | 000945 | 002217 |
| blbl.dev | namesilo | 000919 | 001324 |
| raspi.in | namesilo | 001532 | 002424 |
| pornie.in | namesilo | 002552 | 002840 |
| aosp.me | namesilo | 001724 | 002267 |
| bailuyuan.org | namesilo | 000775 | 001534 |
| ustv.xyz | namesilo | 001616 | 000390 |
| ciux.org | namesilo | 001394 | 000944 |
| avlist.top | namesilo | 002042 | 000932 |
| ltecn.com | namesilo | 002466 | 002628 |
| pornie.top | namesilo | 002561 | 002076 |
+---------------+----------+--------+--------+
13 rows in set (0.004 sec)
不带别名的相同的 SQL 语句:
MariaDB [learnsql]> select domains.domainname,domains.icp,logs.google,logs.baidu from domains,logs where domains.id=logs.id and domains.icp='namesilo';
+---------------+----------+--------+--------+
| domainname | icp | google | baidu |
+---------------+----------+--------+--------+
| getos.org | namesilo | 001864 | 002745 |
| les.moe | namesilo | 001166 | 000350 |
| regex.in | namesilo | 000945 | 002217 |
| blbl.dev | namesilo | 000919 | 001324 |
| raspi.in | namesilo | 001532 | 002424 |
| pornie.in | namesilo | 002552 | 002840 |
| aosp.me | namesilo | 001724 | 002267 |
| bailuyuan.org | namesilo | 000775 | 001534 |
| ustv.xyz | namesilo | 001616 | 000390 |
| ciux.org | namesilo | 001394 | 000944 |
| avlist.top | namesilo | 002042 | 000932 |
| ltecn.com | namesilo | 002466 | 002628 |
| pornie.top | namesilo | 002561 | 002076 |
+---------------+----------+--------+--------+
13 rows in set (0.001 sec)
在下面的情况下,使用别名很有用:
- 在查询中涉及超过一个表
- 在查询中使用了函数
- 列名称很长或者可读性差
- 需要把两个列或者多个列结合在一起