第三章 10-sql2.ppt

More SQL Database Modification Controlling Concurrent Behavior Database Modifications A modification command does not return a result (as a query does), but changes the database in some way. Three kinds of modifications: Insert a tuple or tuples. Delete a tuple or tuples. Update the value(s) of an existing tuple or tuples. Insertion To insert a single tuple: INSERT INTO relation VALUES ( list of values ); Example: add to Likes(drinker, beer) the fact that Sally likes Bud. INSERT INTO Likes VALUES(’Sally’, ’Bud’); Specifying Attributes in INSERT We may add to the relation name a list of attributes. Two reasons to do so: We forget the standard order of attributes for the relation. We don’t have values for all attributes, and we want the system to fill in missing components with NULL or a default value. Example: Specifying Attributes Another way to add the fact that Sally likes Bud to Likes(drinker, beer): INSERT INTO Likes(beer, drinker) VALUES(’Bud’, ’Sally’); Inserting Many Tuples We may insert the entire result of a query into a relation, using the form: INSERT INTO relation ( subquery ); Example: Insert a Subquery Using Frequents(drinker, bar), enter into the new relation PotBuddies(name) all of Sally’s “potential buddies,” i.e., those drinkers who frequent at least one bar that Sally also frequents. Solution INSERT INTO PotBuddies (SELECT d2.drinker FROM Frequents d1, Frequents d2 WHERE d1.drinker = ’Sally’ AND d2.drinker ’Sally’ AND d1.bar = d2.bar ); Deletion To delete tuples satisfying a condition from some relation: DELETE FROM relation WHERE condition; Example: Deletion Delete from Likes(drinker, beer) the fact that Sally likes Bud: Example: Delete all Tuples Make the relation Likes empty: Example: Delete Many Tuples Delete from Beers(name, manf) all beers for which there is another beer by the same manufacturer. Semantics of Deletion --- (1) Suppose Anheuser-Busch makes only Bud and Bud Lite. Suppose we come to the tuple b for Bu