MySQL Cast as用法介绍|极客笔记

MySQL Cast as用法介绍

在MySQL中，CAST和AS是两个常用的关键词，用于实现数据类型转换和更改列的别名。本文将详细介绍CAST和AS的使用方法，并给出五个示例代码及其运行结果。

1. CAST关键词

CAST关键词用于将一个数据类型转换为另一个数据类型。它的语法如下：

CAST(expression AS dataType)

其中，expression表示要进行类型转换的表达式或列，dataType表示转换后的数据类型。下面是五个示例代码及其运行结果：

示例1：将整数转换为字符串

SELECT CAST(123 AS CHAR) AS result;

运行结果：

+--------+
| result |
+--------+
| 123    |
+--------+

示例2：将浮点数转换为整数

SELECT CAST(3.14 AS SIGNED) AS result;

运行结果：

+--------+
| result |
+--------+
| 3      |
+--------+

示例3：将字符串转换为日期

SELECT CAST('2022-01-01' AS DATE) AS result;

运行结果：

+------------+
|   result   |
+------------+
| 2022-01-01 |
+------------+

示例4：将日期转换为字符串

SELECT CAST(NOW() AS CHAR) AS result;

运行结果：

+---------------------+
|       result        |
+---------------------+
| 2022-03-15 12:34:56 |
+---------------------+

示例5：将布尔值转换为整数

SELECT CAST(TRUE AS SIGNED) AS result;

运行结果：

+--------+
| result |
+--------+
| 1      |
+--------+

2. AS关键词

AS关键词用于更改列或表的别名。它的语法如下：

SELECT column_name AS alias_name FROM table_name;

其中，column_name表示要更改别名的列名，alias_name表示新的别名，table_name表示表名。下面是五个示例代码及其运行结果：

示例1：为列添加别名

SELECT salary AS "工资" FROM employees;

运行结果：

+--------+
| 工资   |
+--------+
| 5000   |
| 6000   |
| 7000   |
| 8000   |
| 9000   |
+--------+

示例2：为表添加别名

SELECT e.employee_id, d.department_name 
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;

运行结果：

+-------------+------------------+
| employee_id | department_name  |
+-------------+------------------+
| 1           | Sales            |
| 2           | Marketing        |
| 3           | Human Resources  |
| 4           | Finance          |
| 5           | IT               |
+-------------+------------------+

示例3：为计算结果添加别名

SELECT 
    employee_id,
    salary * 1.1 AS "加薪后工资"
FROM employees;

运行结果：

+-------------+------------+
| employee_id | 加薪后工资  |
+-------------+------------+
| 1           | 5500       |
| 2           | 6600       |
| 3           | 7700       |
| 4           | 8800       |
| 5           | 9900       |
+-------------+------------+

示例4：使用别名进行排序

SELECT 
    employee_id,
    salary
FROM employees
ORDER BY salary DESC;

运行结果：

+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 9000   |
| 4           | 8000   |
| 3           | 7000   |
| 2           | 6000   |
| 1           | 5000   |
+-------------+--------+

示例5：使用别名进行分组

SELECT 
    department_id,
    AVG(salary) AS "平均工资"
FROM employees
GROUP BY department_id;

运行结果：

+---------------+------------+
| department_id | 平均工资   |
+---------------+------------+
| 1             | 6500       |
| 2             | 7500       |
| 3             | 8500       |
| 4             | 9500       |
| 5             | 10500      |
+---------------+------------+