Mysql 简明教程
MySQL - Aliases
The MySQL Alias
MySQL 别名用于为表或 SQL 中的列分配一个临时名称，称为 Alias 。
The MySQL Alias is used to assign a temporary name, called an Alias to a table or a column in SQL.
别名使用 AS 关键字创建，用于引用特定的表或列，而无需更改其原始名称。它们用于在使用名称相似的表或列时使查询易于阅读。
Aliases are created using the AS keyword and are used to refer to a specific table or a column without changing its original name. They are used to make the query easily readable when working tables or columns with similar names.
Aliasing Column Names
列别名化用于为表的列分配不同的名称。
Aliasing column names is used to assign a different name to a column of a table.
Syntax
列别名的基本语法如下 −
The basic syntax of a column alias is as follows −
SELECT column_name
AS alias_name
FROM table_name;Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −
First, let us create a table with the name CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，让我们使用 INSERT INTO 语句将值插入到上面创建的表中，如下所示 −
Now, let us insert values into the table created above using the INSERT INTO statement as shown below −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );获得的 CUSTOMERS 表如下：−
The CUSTOMERS table obtained is as follows −
Example
在以下查询中，我们创建了两个别名，一个用于 ID 列，另一个用于 AGE 列 −
In the following query, we are creating two aliases, one for the ID column and one for the AGE column −
SELECT ID AS CUST_ID, AGE
AS CUST_AGE
FROM CUSTOMERS;Example
如果想要别名中包含空格，则可以使用双引号，如下面的查询中所示 −
If we want the alias name to contain spaces, we can use the double quotation marks as shown in the query below −
SELECT ID AS "CUST ID", AGE
AS "CUST AGE"
FROM CUSTOMERS;Aliasing Table Names
表别名用于为表分配不同的名称。
Aliasing table names is used to assign a different name to a table.
Syntax
以下是表别名的语法 −
Following is the syntax of a table alias −
SELECT column1, column2....
FROM table_name AS alias_nameExample
让我们使用以下查询创建一个名为 ORDERS 的另一个表 −
Let us create another table with the name ORDERS using the following query −
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATES DATETIME NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT INT NOT NULL,
PRIMARY KEY (OID)
);现在，让我们使用 INSERT INTO 语句将值插入到上面创建的表中，如下所示 −
Now, let us insert values into the table created above using the INSERT INTO statement as follows −
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000),
(100, '2009-10-08 00:00:00', 3, 1500),
(101, '2009-11-20 00:00:00', 2, 1560),
(103, '2008-05-20 00:00:00', 4, 2060);获得的 ORDERS 表如下所示 −
The ORDERS table obtained is as follows −
在以下查询中，CUSTOMERS 表的别名是“C”，而 ORDERS 表的别名是“O”−
In the following query, the CUSTOMERS table is aliased as 'C' and the ORDERS table is aliased as 'O' −
SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE C.ID = O.CUSTOMER_ID;Aliasing with Self Join
MySQL 自连接用于将表连接到它自身，就像两个单独的表一样。自连接中的别名用于在 SQL 语句中临时重命名表以避免混淆。
The MySQL Self Join is used to join a table to itself as if it were two separate tables. Aliasing in self join is used to temporarily rename the table in the SQL statement to prevent confusion.
Syntax
以下是使用别名执行自连接的语法：
Following is the syntax for performing a self-join with aliases −
SELECT column_name(s)
FROM my_table a, my_table b
ON a.join_column = b.join_columnExample
现在，让我们使用自连接将 CUSTOMERS 表连接到它自身，以根据客户的收益在客户之间建立关系。
Now, let us join the CUSTOMERS table to itself using the self join to establish a relationship among the customers on the basis of their earnings.
在此，我们对列名和表名进行别名设置，以创建一个更具意义的结果表。
Here, we are aliasing column names and table names to create a more meaningful resultant table.
SELECT a.ID, b.NAME
AS EARNS_HIGHER, a.NAME
AS EARNS_LESS, a.SALARY
AS LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;