SQL INSERT INTO 语句
SQL INSERT INTO 语句
The INSERT INTO
statement is used to insert new records in a table.
INSERT INTO 语法
It is possible to write the INSERT INTO
statement in two ways
1. 指定要插入的列名和值
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
2. 如果您为表中的所有列添加值,则无需在 SQL 查询中指定列名。但是,请确保值的顺序与表中的列顺序相同。在此, INSERT INTO
语法如下
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
演示数据库
以下是示例中使用的 Customers 表的选择
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 |
Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 |
Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
INSERT INTO 示例
The following SQL statement inserts a new record in the "Customers" table
示例
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The selection from the "Customers" table will now look like this
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 |
Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 |
Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
92 | Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is an auto-increment field and will be generated automatically when a new record is inserted into the table.
仅插入指定列的数据
It is also possible to insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically)
示例
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
The selection from the "Customers" table will now look like this
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 |
Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 |
Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
92 | Cardinal | null | null | Stavanger | null | Norway |
插入多行
It is also possible to insert multiple rows in one statement.
To insert multiple rows of data, we use the same INSERT INTO
statement, but with multiple values
示例
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
Make sure you separate each set of values with a comma ,
.
The selection from the "Customers" table will now look like this
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
89 | White Clover Markets | Karl Jablonski | 305 - 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
90 |
Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
91 |
Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
92 | Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
93 | Greasy Burger | Per Olsen | Gateveien 15 | Sandnes | 4306 | Norway |
94 | Tasty Tee | Finn Egan | Streetroad 19B | Liverpool | L1 0AA | UK |
Video: SQL NOT Operator

