AS语句在MySQL数据库中用于为表和列设置别名,可提高查询的可读性、简化操作和减少输入。
<表名> [AS] <表别名>
语法说明:<>是中文解释。[ ]中的内容是可选的。
把table_name表命名为a表
SELECT id FROM table_name AS a;
<列名> [AS] <列别名>
把a表的tableid字段命名为id
SELECT tableid AS id FROM a;
关键字可以省略,省略后需要将表名和别名用空格隔开。
表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。
提高查询的可读性:通过为列设置别名,可以使查询结果更易于理解,特别是当查询结果包含多个列时,使用别名可以清楚地标识每个列的含义。
简化操作:当需要使用多个列进行计算或组合时,为每个列设置别名可以避免列名称的混淆和冗余。
减少输入:当列名较长或难以拼写时,使用别名可以减少输入量,提高效率。
把table_name表命名为a表,把a表的tableid字段命名为id,表和列的别名都省略AS
SELECT tableid id FROM table_name a;
列别名不允许在WHERE子句中使用。施加此限制是因为当WHERE计算子句时,列值可能尚未确定。表别名可以。
SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;
正常编写别名可以不用引号、反引号和单引号和双引号。区别在于特定情况,比如说有空格和MySQL保留字情况下则需要使用引号,反引号和单引号和双引号都可以。
反引号和单引号和双引号都是定义为引用,引用的引用则需要不用引号和反引号才能使用。
SELECT id AS "a", COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY a; SELECT id AS 'a', COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY a; SELECT id AS `a`, COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY a; SELECT id AS a, COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY a; SELECT id AS "a", COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY `a`; SELECT id AS 'a', COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY `a`; SELECT id AS `a`, COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY `a`; SELECT id AS a, COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY `a`;
SELECT id AS 'a', COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY 'a';
SELECT id AS 'a', COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY "a";
SELECT id AS "a", COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY "a";
SELECT id AS "a", COUNT(*) AS cnt FROM hlwj_lanmu GROUP BY 'a';
反引号如何输入