更新数据
例1
UPDATE customers
SET cust_email='peng@orystore.com'
WHERE cust_id=1000000006;
例2
UPDATE customers
SET cust_contact='Sam Roberts',
cust_email='Sam@toyland.com'
WHERE cust_id=1000000007;
例3
UPDATE customers
SET cust_email=NULL
WHERE cust_id=1000000005;
删除数据
例4
DELETE FROM customers
WHERE cust_id=1000000006;
更新和删除的指导原则
- 除非真的要更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE和DELETE语句
- 保证每个表都有主键,尽量像WHERE子句那样使用
- 在UPDATE或DELETE语句使用WHERE子句之前,应该先用SELECT进行测试,保证它过滤的是正确的行
- 使用强制实施引用完整性的数据库
- 如果使用的DBMS允许数据库管理员施加约束,防止执行不带WHERE子句的UPDATE或DELETE子句,使用它
小结
- 如何使用UPDATE或DELETE语句处理表中的数据
- 知道了可能存在的危险
- 学习了为保证数据安全而应该遵循的一些指导原则
挑战题
1
USA State abbreviations should always be in upper case. Write a SQL statement to update all USA addresses, both vendor states (vend_state in Vendors) and customer states (cust_state in Customers) so that they are upper case.
UPDATE vendors
SET vend_state=UPPER(vend_state);
UPDATE customers
SET cust_state=UPPER(cust_state);
2
In Lesson 15 Challenge 1 I asked you to add yourself to the Customers table. Now delete yourself. Make sure to use a WHERE clause (and test it with a SELECT before using it in DELETE) or you’ll delete all customers!
SELECT * FROM customers
WHERE cust_id = 1000000009;
DELETE FROM customers
WHERE cust_id=1000000009;