例如:现在有department表,如下:
mysql> select * from department;
+------+-----------+--------------+-------------+
| d_id | d_name | function_ | address |
+------+-----------+--------------+-------------+
| 1001 | 科研部 | 研发产品 | 3号楼5层 |
| 1002 | 生产部 | 生产产品 | 5号楼1层 |
| 1003 | 销售部 | 策划销售 | 5号楼1层 |
+------+-----------+--------------+-------------+
3 rows in set (0.00 sec)
mysql> select d_name as department_name from department where
-> department_name = 销售部;
ERROR 1054 (42S22): Unknown column 'department_name' in 'where clause'
对比地,表别名却可以在查询说中使用,例如:
mysql> select * from department d where d.d_id = 1003;
+------+-----------+--------------+-------------+
| d_id | d_name | function_ | address |
+------+-----------+--------------+-------------+
| 1003 | 销售部 | 策划销售 | 5号楼1层 |
+------+-----------+--------------+-------------+
1 row in set (0.00 sec)
总结,mysql字段别名在显示的时候用,查询时候不能用,表别名在查询时候用,在显示的时候不用。