MySQL 查询包含表中尚不存在的多个列

mysqlmysqli database更新于 2024/12/17 19:56:17

您可以使用多个 AS 关键字轻松添加查询中不存在的多个列。

我们首先创建一个表。创建表的查询如下 −

mysql> create table ColumnDoesNotExists
   -> (
   -> UserId int,
   -> UserName varchar(20)
   -> );
Query OK, 0 rows affected (0.67 sec)

使用 insert 命令在表中插入一些记录。查询语句如下 −

mysql> insert into ColumnDoesNotExists(UserId,UserName) values(100,'Larry');
Query OK, 1 row affected (0.14 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(101,'Sam');
Query OK, 1 row affected (0.22 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(102,'Mike');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(103,'David');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(104,'Robert');
Query OK, 1 row affected (0.10 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(105,'Maxwell');
Query OK, 1 row affected (0.20 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(106,'Bob');
Query OK, 1 row affected (0.17 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(107,'John');
Query OK, 1 row affected (0.17 sec)
mysql> insert into ColumnDoesNotExists(UserId,UserName) values(108,'James');
Query OK, 1 row affected (0.18 sec)

示例

使用 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)