第 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 为一个新字段，客户机可以使用这个新字段
