SQL Server 表的管住_关于数据增加和删除查改的操作的详解(案例代码)

SQL Server 表的军事管制_有关数据增加和删除查改的操作的详解(案例代码)-DML

1、SQL INSERT INTO 语句(在表中插入)

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” 表中剔除客户“Ayr弗瑞德s 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
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
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

有关查看比较复杂,前边详细再说吧!!!

 

相关文章