=============================================================
标题:SQL语句练习-更新和删除数据
备注:SQL SERVER 2000
日期:2011.4.17
姓名:朱铭雷
=============================================================
1 更新某一行,某一列
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005'
验证一下是否更新成功:
SELECT cust_id, cust_email
FROM Customers
WHERE cust_id = '1000000005'
2 更新某一行,多个列
UPDATE Customers
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000005'
验证一下是否更新成功:
SELECT cust_id, cust_contact, cust_email
FROM Customers
WHERE cust_id = '1000000005'
3 更新所有行,某个列
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
验证一下是否更新成功:
SELECT cust_email FROM Customers
在更新数据时要格外小心,如果漏掉了WHERE子句,则会更新表中的所有行。
4 删除某一行
DELETE FROM Customers
WHERE cust_id = '1000000005'
验证一下是否更新成功:
5 删除某一列的值
UPDATE Customers
SET cust_email = NULL
6 删除所有行
DELETE FROM Customers
注意:虽然删除了表Customers的所有行,但并不删除表Customers本身。
7 删除表中所有行,有速度更快的TRUNCATE TABLE语句。(SQlite不支持此语句)