SQL INSERT INTO 语句

SQL INSERT INTO 语句用来将一条或多条记录插入数据库表中。

SQL INSERT INTO 语句语法

INSERT INTO TableName (列名1, 列名2, 列名3, ...)
VALUES (值1, 值2, 值3, ...)

列名也可以不写,这时,值将会安装数据表默认的列的顺序插入:

INSERT INTO TableName 
VALUES (值1, 值2, 值3, ...)

我们也可以从其他表中获得数据插入:

INSERT INTO Table1 (ColumnName1, ColumnName2, ColumnName3)
SELECT ColumnName1, ColumnName2, ColumnName3
FROM Table2
WHERE condition

其它几种写法:

INSERT INTO Table1 
SELECT ColumnName1, ColumnName2, ColumnName3
FROM Table2
WHERE condition
INSERT INTO Table1 
SELECT *
FROM Table2
WHERE condition

SQL INSERT INTO 语句范例

我们从一个空的Employees表开始:

EmployeeId FirstName LastName Department Salary

我么用下面的语句插入一条新的记录到Employees表中:

INSERT INTO Employees (EmployeeId, FirstName, LastName, Department, Salary)
VALUES (203, 'Mazojys', 'Fxoj', 'Finance', 78000)

Employees表会如下所示:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000

我们用更简单的方法插入另一条记录:

INSERT INTO Employees
VALUES (204, 'Jozzh', 'Lnanyo', 'Finance', 45800)

现在 Employees 表会如下所示:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800

如果我们插入新记录时数据不完全:

INSERT INTO Employees
VALUES (205, 'Syllauu', 'Dfaafk')

Employees表将会如下所示:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk

从一个备份表中恢复数据:

INSERT INTO Employees
SELECT *
FROM EmployeesBak
WHERE EmployeeId > 500