数据库原理(双语)-Chapter 5 SQL Data Manipulation.ppt

INSERT Include INSERT…VALUES and INSERT…SELECT (2) INSERT…SELECT INSERT [ INTO ] TableName [ ( columnList ) ] SELECT ... Function: insert all rows produced by the subquery into existing table (将子查询的结果全部插入已存在的表中). Restrictions between columnList and SELECT list are analogous to that between columnList and dataValueList in the first form (columnList和子查询目标列之间的限制类似于INSERT…VALUES中columnList和dataValueList之间的限制 ). INSERT Example 5.37 INSERT … SELECT Assume there is a table StaffPropCount that contains names of staff and number of properties they manage: StaffPropCount(staffNo, fName, lName, propCnt) Populate(装载) StaffPropCount using Staff and PropertyForRent tables. INSERT INTO StaffPropCount ( SELECT s.staffNo, fName, lName, COUNT(*) FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo GROUP BY s.staffNo, fName, lName) staffNo who manage at least one property staff who manage zero properties staff who manage at least one property UNION ( SELECT staffNo, fName, lName, 0 FROM Staff WHERE staffNo NOT IN ( SELECT DISTINCT staffNo FROM PropertyForRent ) ) ; 10. Database Updates Database update operations include: INSERT UPDATE DELETE UPDATE Format： UPDATE tbn 　 SET col=exp [,…] [ FROM cbn [, …n] ] [ WHERE Condition] Note： 1) Modify rows in table tbn that satisfy Condition (修改指定表中满足WHERE条件的行) 2) Assign value of exp to col(将exp的值赋予相应列). 3) If WHERE clause is omitted, then modify all rows in table tbn (如果省略WHERE子句，则修改表的所有行). 4) WHERE Condition can use columns from tbn and cbns (WHERE条件可以访问的列名来自UPDATE后面的表和FROM后面的表). UPDATE Give all staff a 3% pay increase. UPDATE Staff SET salary = salary*1.03; Example 5.39 UPDATE specific rows Give all Managers a 5% pay increase. UPDATE Staff SET salary = salary*1.05 WHERE position = Manager; Example 5.40 UPDATE Multiple Columns Promote David Ford (staffNo=‘SG14’) to Manager and change his salary to ￡18,000. UPDATE Staff SET position = Manager,