How to use SQL statements to convert and transfer data in MySQL?

Converting and transferring data in MySQL is a common task. There are many different ways to do this task, the most common of which is to use SQL statements. This article will introduce how to use SQL statements to convert and transfer data in MySQL, and provide specific code examples.

1. Data conversion

Data conversion is the process of converting one or more data types into another data type. In MySQL, you can use the CAST and CONVERT functions to achieve data type conversion.

1. CAST function

The CAST function is used to convert an expression to a specified data type. The basic syntax is as follows:

CAST (expression AS datatype)

where expression is the expression to be converted, and datatype is the target data type to be converted.

Example 1: Convert the string "123" to an integer.

SELECT CAST('123' AS SIGNED);

Example 2: Convert the floating point number 1.23 to an integer.

SELECT CAST(1.23 AS SIGNED);

2. CONVERT function

The CONVERT function can also implement data type conversion. The syntax is as follows:

CONVERT (expression, datatype)

Among them, expression is the expression to be converted, and datatype is the target data type to be converted. Unlike the CAST function, the CONVERT function can also specify a character set.

Example 1: Convert the string "123" to an integer.

SELECT CONVERT('123', SIGNED);

Example 2: Convert an ISO-8859-1 encoded string to a UTF-8 encoded string.

SELECT CONVERT('Hello, world!' USING utf8);

2. Data transfer

Data transfer is to copy a piece of data from one table or database to another A table or database procedure. In MySQL, you can use the INSERT INTO SELECT statement to implement data transfer.

1. INSERT INTO SELECT statement

The INSERT INTO SELECT statement is used to copy data from one table to another table. The basic syntax is as follows:

INSERT INTO table2 (column1, column2, ...)
SELECT column1, column2, ...
FROM table1
WHERE condition;

Among them, table1 is the source table to be copied, table2 is the target table to be copied, column1, column2, ... are the fields to be copied.

Example: Copy all data in the employees table to the employees_backup table.

INSERT INTO employees_backup
SELECT *
FROM employees;

In addition to copying data from one table to another, the INSERT INTO SELECT statement can also copy data from one table to another. to another location on the same table. The basic syntax is as follows:

INSERT INTO table (column1, column2, ...)
SELECT column1, column2, ...
FROM table
WHERE condition;

Example: Copy some data in the employees table to another location in the same table.

INSERT INTO employees (employee_id, first_name, last_name)
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 1;

This will copy the employees table Add the ID, first name, and last name of the employee with department number 1 to the employees table.

The above is an introduction to how to use SQL statements to convert and transfer data in MySQL. Through the CAST and CONVERT functions, conversion between different data types can be achieved; through the INSERT INTO SELECT statement, data can be copied from one table or location to another.

The above is the detailed content of How to use SQL statements to convert and transfer data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!