MySQL数据库基础补充-数据库别名

MySQL-别名

用于展示

mysql> SELECT number AS 学号 ,name AS 姓名 ,klass AS 班级,age AS 年龄 ,birth AS 生日  FROOM students;
+-----------+--------+--------+--------+------------+
| 学号      | 姓名   | 班级   | 年龄   | 生日       |
+-----------+--------+--------+--------+------------+
| 201804001 | 刘一   | 19     |     16 | 2002-01-01 |
| 201804002 | 陈二   | 18     |     17 | 2001-01-02 |
| 201804003 | 张三   | 19     |     18 | 2000-01-03 |
| 201804004 | 李四   | 19     |     19 | 2001-01-04 |
| 201804005 | 王五   | 19     |     16 | 2002-01-05 |
| 201804006 | 赵六   | 18     |     19 | 1999-01-06 |
| 201804007 | 孙七   | 19     |     17 | 2001-01-07 |
| 201804008 | 周八   | 19     |     18 | 2000-01-08 |
| 201804009 | 吴九   | 18     |     17 | 2001-01-09 |
| 201804010 | 郑十   | 19     |     18 | 2000-01-10 |
+-----------+--------+--------+--------+------------+
10 rows in set (0.00 sec)

子查询

注意：如果一个查询的结果是一个表，而不是一个值。那么，你希望把它作为子查询，就必须给它一个别名。

报错版本：

SELECT * FROM (SELECT * FROM students WHERE klass=19);
ERROR 1248 (42000): Every derived table must have its own alias

正确版本：

SELECT * FROM (SELECT * FROM students WHERE klass=19) AS tb
    WHERE age > 17;
+-----------+--------+-------+------+------------+
| number    | name   | klass | age  | birth      |
+-----------+--------+-------+------+------------+
| 201804003 | 张三   | 19    |   18 | 2000-01-03 |
| 201804004 | 李四   | 19    |   19 | 2001-01-04 |
| 201804008 | 周八   | 19    |   18 | 2000-01-08 |
| 201804010 | 郑十   | 19    |   18 | 2000-01-10 |
+-----------+--------+-------+------+------------+
4 rows in set