MySQL查询在一个表中包含不止一列
您可以使用多个AS关键字轻松添加一个查询中不存在的多个列。
让我们首先创建一个表。创建表的查询如下-
mysql> create table ColumnDoesNotExists -> ( -> UserId int, -> UserName varchar(20) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(100,'Larry'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(101,'Sam'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(102,'Mike'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(103,'David'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(104,'Robert'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(105,'Maxwell'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(106,'Bob'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(107,'John'); mysql> insert into ColumnDoesNotExists(UserId,UserName) values(108,'James');
示例
使用select语句显示表中的所有记录。查询如下-
mysql> select *from ColumnDoesNotExists;
输出结果
+--------+----------+ | UserId | UserName | +--------+----------+ | 100 | Larry | | 101 | Sam | | 102 | Mike | | 103 | David | | 104 | Robert | | 105 | Maxwell | | 106 | Bob | | 107 | John | | 108 | James | +--------+----------+ 9 rows in set (0.00 sec)
示例
这是查询以添加查询中不存在的列名-
mysql> select UserId,UserName,23 AS Age from ColumnDoesNotExists;
输出结果
+--------+----------+-----+ | UserId | UserName | Age | +--------+----------+-----+ | 100 | Larry | 23 | | 101 | Sam | 23 | | 102 | Mike | 23 | | 103 | David | 23 | | 104 | Robert | 23 | | 105 | Maxwell | 23 | | 106 | Bob | 23 | | 107 | John | 23 | | 108 | James | 23 | +--------+----------+-----+ 9 rows in set (0.00 sec)
示例
现在让我们看一下一次添加多个列的查询。在这里我们添加列'Marks'和'CountryName'-
mysql> select UserId,UserName,23 AS Age,99 AS Marks,'UK' AS CountryName from ColumnDoesNotExists;
输出结果
+--------+----------+-----+-------+-------------+ | UserId | UserName | Age | Marks | CountryName | +--------+----------+-----+-------+-------------+ | 100 | Larry | 23 | 99 | UK | | 101 | Sam | 23 | 99 | UK | | 102 | Mike | 23 | 99 | UK | | 103 | David | 23 | 99 | UK | | 104 | Robert | 23 | 99 | UK | | 105 | Maxwell | 23 | 99 | UK | | 106 | Bob | 23 | 99 | UK | | 107 | John | 23 | 99 | UK | | 108 | James | 23 | 99 | UK | +--------+----------+-----+-------+-------------+ 9 rows in set (0.00 sec)