INSERT INTO 语句用于向表中插入新记录。
SQL INSERT INTO 语法
INSERT INTO 语句可以用两种形式编写。
第一个表单没有指定要插入数据的列的名称,只提供要插入的值:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
如果要为表中的所有列添加值,则不需要在SQL查询中指定列名称。但是,请确保值的顺序与表中的列顺序相同。INSERT INTO语法如下所示:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
演示数据库
以下是"Customers" 表中的数据:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
87 | Wartian Herkku | Pirkko Koskitalo | Torikatu 38 | Oulu | 90110 | Finland |
88 | Wellington Importadora | Paula Parente | Rua do Mercado, 12 | Resende | 08737-363 | Brazil |
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 实例代码
假设我们想在"Customers"表中插入一个新行。
我们可以使用以下SQL语句:
实例
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
现在,选自 "Customers" 表的数据如下所示:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
87 | Wartian Herkku | Pirkko Koskitalo | Torikatu 38 | Oulu | 90110 | Finland |
88 | Wellington Importadora | Paula Parente | Rua do Mercado, 12 | Resende | 08737-363 | Brazil |
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 |
仅在指定的列中插入数据
我们还可以只在指定的列中插入数据。
以下SQL语句插入一个新行,但只在“CustomerName”、“City”和“Countryn”列中插入数据(CustomerID字段将自动更新):
实例
INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
现在,选自 "Customers" 表的数据如下所示:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
87 | Wartian Herkku | Pirkko Koskitalo | Torikatu 38 | Oulu | 90110 | Finland |
88 | Wellington Importadora | Paula Parente | Rua do Mercado, 12 | Resende | 08737-363 | Brazil |
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 |
2、SQL Delete 语句(删除表中的记录)
DELETE语句用于删除表中现有记录。
SQL DELETE 语句
DELETE 语句用于删除表中的行。
SQL DELETE 语法
DELETE FROM table_name WHERE condition;
以下是 "Customers" 表中的数据:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SQL DELETE 实例代码
假设我们想从"Customers" 表中删除客户“Alfreds Futterkiste”。
我们使用以下SQL语句:
实例
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
现在,"Customers" 表如下所示:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
删除所有数据
您可以删除表中的所有行,而不需要删除该表。这意味着表的结构、属性和索引将保持不变:
DELETE FROM table_name;
或者
DELETE * FROM table_name;
注意:在没有备份的情况下,删除记录要格外小心!因为你删除了不能重复!
3、SQL UPDATE 语句(更新表中的记录)修改
UPDATE 语句用于更新表中的现有记录。
SQL UPDATE 语句
UPDATE 语句用于更新表中已存在的记录。
SQL UPDATE 语法
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
演示数据库
以下是 "Customers" 表中的数据:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SQL UPDATE 实例
以下SQL语句为第一个客户(CustomerID = 1)更新了“CustomerName”和“City”:
实例
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
现在,选自 "Customers" 表的数据如下所示:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 |
Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
更新多个记录
WHERE子句决定了将要更新的记录数量。
以下SQL语句将把国家/地区为"Mexico"的所有记录的联系人姓名更新为“Juan”:
UPDATE Customers SET ContactName='Juan' WHERE Country='Mexico';
“Customers”表中的选择现在看起来像这样:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 |
Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Juan | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Juan | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Update 警告!
更新记录时要小心。如果您省略WHERE子句,所有记录将被更新!
UPDATE Customers SET ContactName='Juan';
"Customers" 表将如下所示:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 |
Alfreds Futterkiste | Juan | Obere Str. 57 | Frankfurt | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Juan | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Juan | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 |
Around the Horn | Juan | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Juan | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |