MySQL 保姆级教程（八）：创建计算字段

第 10 章 创建计算字段

10.1 计算字段

直接从数据库中检索出转换、计算或格式化过的数据

10.2 拼接字段

拼接: 将值联结到一起构成单个值
​
输入: SELECT CONCAT(database_name,' date:',last_update) FROM innodb_index_stats ORDER BY database_name;
输出: 
+--------------------------------------------+
| CONCAT(database_name,' date:',last_update) |
+--------------------------------------------+
| mysql date:2024-01-03 15:22:15             |
| mysql date:2024-01-03 15:22:15             |
| mysql date:2024-01-03 15:22:15             |
| sys date:2024-01-03 15:22:18               |
| sys date:2024-01-03 15:22:18               |
| sys date:2024-01-03 15:22:18               |
+--------------------------------------------+
分析: CONCAT() 拼接串，各个串之间用逗号分隔

10.3 使用别名

输入: SELECT CONCAT(database_name,' date:',last_update) AS '曹礼成是世界上最帅的男人' FROM innodb_index_stats ORDER BY database_name;
输出: 
+--------------------------------------+
| 曹礼成是世界上最帅的男人             |
+--------------------------------------+
| mysql date:2024-01-03 15:22:15       |
| mysql date:2024-01-03 15:22:15       |
| mysql date:2024-01-03 15:22:15       |
| sys date:2024-01-03 15:22:18         |
| sys date:2024-01-03 15:22:18         |
| sys date:2024-01-03 15:22:18         |
+--------------------------------------+
分析: 它指示 SQL 创建一个名为 '曹礼成是世界上最帅的男人' 的字段，任何客户机都可以引用这个列

10.4 执行算数计算

输入: SELECT vend_id,vend_city,vend_zip FROM vendors ORDER BY vend_id;
输出: 
+---------+-------------+----------+
| vend_id | vend_city   | vend_zip |
+---------+-------------+----------+
|    1001 | Southfield  | 48075    |
|    1002 | Anytown     | 44333    |
|    1003 | Los Angeles | 90046    |
|    1004 | New York    | 11111    |
|    1005 | London      | N16 6PS  |
|    1006 | Paris       | 45678    |
+---------+-------------+----------+
​
输入: SELECT vend_id,vend_city,vend_zip,vend_id+vend_zip AS sum FROM vendors ORDER BY vend_id;
输出: 
+---------+-------------+----------+-------+
| vend_id | vend_city   | vend_zip | sum   |
+---------+-------------+----------+-------+
|    1001 | Southfield  | 48075    | 49076 |
|    1002 | Anytown     | 44333    | 45335 |
|    1003 | Los Angeles | 90046    | 91049 |
|    1004 | New York    | 11111    | 12115 |
|    1005 | London      | N16 6PS  |  1005 |
|    1006 | Paris       | 45678    | 46684 |
+---------+-------------+----------+-------+
分析: sum 为一个新字段，客户机可以使用这个新字段