Mysql计算字段

一、拼接字段

多数DBMS是使用+或者||来拼接两个列，但是Mysql的实现有所不同。

• 函数：Concat();

• 功能：把两个列拼接起来，即把多个串拼接成一个串

• 注意：多个串之间用,号隔开

mysql> select concat(ename,'   (',job,')') from emp;
+------------------------------+
| concat(ename,'   (',job,')') |
+------------------------------+
| SMITH   (CLERK)              |
| ALLEN   (SALESMAN)           |
| WARD   (SALESMAN)            |
| JONES   (MANAGER)            |
| MARTIN   (SALESMAN)          |
| BLAKE   (MANAGER)            |
| CLARK   (MANAGER)            |
| SCOTT   (ANALYST)            |
| KING   (PRESIDENT)           |
| TURNER   (SALESMAN)          |
+------------------------------+
10 rows in set (0.00 sec

注意：job用到的两个括号也需要自己用,拼接上去。

使用别名

• 别名：一个字段或者值的替换名
• 关键字：as(可省略)

mysql> select ename as '姓名' , job as '职业' from emp;
+--------+-----------+
| 姓名   | 职业      |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
14 rows in set (0.00 sec)

二、计算字段进行算术运算

mysql中检索出来的列实可以直接进行算术运算的。

计算员工的年薪（sal列是员工月薪）

mysql> select ename,sal,sal*12 '年新' from emp;
+--------+---------+----------+
| ename  | sal     | 年新     |
+--------+---------+----------+
| SMITH  |  800.00 |  9600.00 |
| ALLEN  | 1600.00 | 19200.00 |
| WARD   | 1250.00 | 15000.00 |
| JONES  | 2975.00 | 35700.00 |
| MARTIN | 1250.00 | 15000.00 |
| BLAKE  | 2850.00 | 34200.00 |
| CLARK  | 2450.00 | 29400.00 |
| SCOTT  | 3000.00 | 36000.00 |
| KING   | 5000.00 | 60000.00 |
| TURNER | 1500.00 | 18000.00 |
| ADAMS  | 1100.00 | 13200.00 |
| JAMES  |  950.00 | 11400.00 |
| FORD   | 3000.00 | 36000.00 |
| MILLER | 1300.00 | 15600.00 |
+--------+---------+----------+
14 rows in set (0.00 sec)