数据库英文课件

Using Studio and Movie, add to the relation Studio all movie studios that are mentioned in the relation Movie, but don’t appear in Studio.
7
Solution
INSERT INTO Studio(name) (SELECT DISTINCT studioName FROM Movie WHERE studioName NOT IN
movieYear = 1942 AND starName = ‘Sydney Greenstreet’;
10
Example: Delete all Tuples
Make the relation Likes empty: DELETE FROM Likes;
Note no WHERE clause needed.
Another way to add Sydney Greenstreet to the list of stars of The Maltese Falcon.
INSERT INTO StarsIn(movieTitle, movieYear, starName) VALUES(’The Maltese Falcon’, 1942, ’Sydney GreenStreet’);

Updates
To change certain attributes in certain tuples of a relation:
UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>;
(SELECT name FROM Studio);
8
Deletion
To delete tuples satisfying a condition from some relation:
DELETE FROM <relation> WHERE <condition>;
9
Example: Deletion
2. We don’t have values for all attributes, and we want the system to fill in missing components with NULL or a default value.
4
Example: Specifying Attributes
3
Specifying Attributes in INSERT
We may add to the relation name a list of attributes. Two reasons to do so:
1. We forget the standard order of attributes for the relation.
Delete from relation StarsIn the fact that Sydney GreenStreet was a star in The Maltese Falcon:
DELETE FROM StarsIn WHERE movieTitle = ‘The Maltese Falcon’ AND
More SQL
Database Modification Defining a Database Schema Views
1
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:
5
Inserting Many Tuples
We may insert the entire result of a query into a relation, using the form:
INSERT INTO <relation> ( <subquery> );
6
Example: Insert a Subquery
Example: add Sydney Greenstreet to the list of stars of The Maltese Falcon.
INSERT INTO StarsIn VALUES(‘The Maltese Falcon’, 1942, ’Sydney GreenStreet’);
13
Example: Update
Modify the relation MovieExec by prepending the title Pres. In front of every movie executives who is the president of a studio:
UPDATE MovieExec SET name = ‘Pres. ’ || name WHERE cert# IN (SELECT presC# FROM Studio);
11
Example: Delete Many Tuples
Delete from MovieExec all movie executives whose net worth is low-less than ten million dollars.
DELETE FROM MovieExec WHERE netWorth < 10000000;
1. Insert a tuple or tuples. 2. Delete a tuple or tuples. 3. Update the value(s) of an existing tuple or tuples.
2
Insertion
To insert a single tuple: INSERT INTO <relation> VALUES ( <list of values> );