SQL SELECT INTO

SQL SELECT INTO 语句在不同的数据库中有不同的应用。这篇文章将讨论它在 MS SQL Server, Oracle 和 MySql 中的应用。


MS SQL Server

对于MS SQL Server，SQL SELECT INTO 语句用来从一或多个数据表中选取数据，然后插入到另一个表中。在数据库备份中会有用。

SQL SELECT INTO 语法

选择所有的数据到另一个表中：

SELECT *
INTO table2
FROM table1

或者列出你想插入到新表的列：

SELECT column1, column2, column3, ...
INTO table2
FROM table1

SQL SELECT INTO 范例

把所有Employees中的数据备份到Employees_Bak表中：

SELECT *
INTO Employees_Bak
FROM Employee

仅拷贝一些列到新表中：

SELECT EmployeeId, FirstName, LastName, Title
INTO Employees_Bak
FROM Employee

使用 IN 子句拷贝数据：

SELECT *
INTO Employees_Bak IN 'Bak.mdb'
FROM Employee

使用WHERE子句仅拷贝新数据：

SELECT *
INTO Employees_Bak
FROM Employee
WHERE StartDate >=  '2010-06-01'

从多个表中选取数据：

SELECT Employees.EmployeeId, Employees.FirstName, Employees.LastName,Projects.ProjectId
INTO Employees_Project_Bak
FROM Employees
INNER JOIN Projects
ON Employees.EmployeeId = Projects.EmployeeId

Oracle PL/SQL

在Oracle中, SELECT INTO 子句用来从一个或多个数据表中读取数据，然后将数据赋给一个变量或集合。

Oracle PL/SQL SELECT INTO 子句范例

选择一个职员的LastName到一个变量中：

DECLARE
    v_EmployeeName Employees.LastName%TYPE;
BEGIN
    SELECT LastName
    INTO v_EmployeeName
    FROM Employees
    WHERE  Employees.EmployeeId = 203;
    dbms_output.put_line('Name: '||v_EmployeeName);
END;
 /

选择所有的数据到一个集合中：

DECLARE
    v_Employee Employees%ROWTYPE;
BEGIN
    SELECT *
    INTO v_Employee
    FROM Employees
    WHERE  Employees.EmployeeId = 203;
    dbms_output.put_line('Name: ' || v_Employee.FirstName || '  ' || v_Employee.LastName);
END;
 /

读取FirstName，LastName到一个变量中：

DECLARE
    v_EmployeeFullName VARCHAR2(100);
BEGIN
    SELECT FirstName || ' ' || LastName
    INTO v_EmployeeFullName
    FROM Employees
    WHERE  Employees.EmployeeId = 203;
END;
 /

读取FirstName，LastName到两个变量中：

DECLARE
    v_EmployeeFirstName Employees.FirstName%TYPE;
    v_EmployeeLastName Employees.LastName%TYPE;
BEGIN
    SELECT FirstName, LastName
    INTO v_EmployeeFirstName, v_EmployeeLastName
    FROM Employees
    WHERE  Employees.EmployeeId = 203;
END;
 /

SQL SELECT INTO 仅能读取一行数据。如果你想读取超过一行数据，可以使用 SQL SELECT BULK INTO 语句：

DECLARE
    TYPE Employees_Table IS TABLE OF Employees%ROWTYPE INDEX BY PLS_INTEGER;
    v_All_Employees  Employees_Table;
BEGIN
    SELECT *
    BULK INTO v_All_Employees
    FROM Employees;
    DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT);
END;
 /

对于MySQL，SQL SELECT INTO 语句用来从数据表中读取数据并赋给变量，或者使用 SELECT INTO OUTFILE 语句将结果写入服务器的一个文本文件中。

读取FirstName，LastName到两个变量中：

DECLARE v_EmployeeFirstName  VARCHAR(30);
DECLARE v_EmployeeLastName  VARCHAR(30);

SELECT FirstName, LastName
INTO v_EmployeeFirstName, v_EmployeeLastName
FROM Employees
WHERE EmployeeId = 203;

使用 SQL SELECT INTO OUTFILE 语句将结果存入到 /download/employees.txt 文件中：

SELECT EmployeeFirstName, EmployeeLastName, Department, Salary
FROM Employees
INTO OUTFILE '/download/employees.txt'

我们也可以加入一些格式信息：

SELECT EmployeeFirstName, EmployeeLastName, Department, Salary
FROM Employees
INTO OUTFILE '/download/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

在上面的例子中，每个数据都被包含在双引号(“)中，并用逗号隔开，每一行数据会加入一个分行符(\n)。输出结果会类似于：

"Mary","Fox","Finance","78000"
"Joe","Lanyon","Finance","45800"
"Sally","Daff","Finance","57000"
...