MySQL alias for SELECT * columns?

MySQLMySQLi Database

MySQL alias cannot be used with *. However, it can be used for individual column. The syntax is as follows −

select anyaliasName.yourColumnName1 as anyaliasName1,
anyaliasName.yourColumnName2 as anyaliasName2,
anyaliasName.yourColumnName3 as anyaliasName3,
anyaliasName.yourColumnName4 as anyaliasName4,
.
.
.
.
N
from yourTableName as anyaliasName;

MySQL alias is a variable of table that can be used to access the column name of that particular table. To understand the above syntax, let us create a table.

The query to create a table is as follows −

mysql> create table TableAliasDemo
   −> (
   −> Id int,
   −> Name varchar(100),
   −> Age int
   −> );
Query OK, 0 rows affected (0.46 sec)

Insert some records using insert command. The query is as follows −

mysql> insert into TableAliasDemo values(1,'John',23);
Query OK, 1 row affected (0.13 sec)

mysql> insert into TableAliasDemo values(2,'Sam',24);
Query OK, 1 row affected (0.23 sec)

mysql> insert into TableAliasDemo values(3,'David',26);
Query OK, 1 row affected (0.15 sec)

mysql> insert into TableAliasDemo values(4,'Carol',20);
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from TableAliasDemo;

The following is the output −

+------+-------+------+
| Id   | Name  | Age  |
+------+-------+------+
|    1 | John  |   23 |
|    2 | Sam   |   24 |
|    3 | David |   26 |
|    4 | Carol |   20 |
+------+-------+------+
4 rows in set (0.00 sec)

To create an alias for a table, the following is the query −

mysql> select alias.Id as aliasForIdColumn,
   −> alias.Name as aliasForNameColumn,
   −> alias.Age as aliasForAgeColumn
   −> from TableAliasDemo as alias;

The following is the output −

+------------------+--------------------+-------------------+
| aliasForIdColumn | aliasForNameColumn | aliasForAgeColumn |
+------------------+--------------------+-------------------+
|                1 | John               |                23 |
|                2 | Sam                |                24 |
|                3 | David              |                26 |
|                4 | Carol              |                20 |
+------------------+--------------------+-------------------+
4 rows in set (0.00 sec)
raja
Published on 16-Jan-2019 10:18:09
Advertisements