SQL（Database Modifications）
目录
Insertion
Specifying Attributes in INSERT
Adding Default Values（缺省值）
Inserting Many Tuples
Creating a Table Using the SELECT INTO Statement
Deletion
Example: Deletion
Semantics of Deletion
Updates
Example: Update Several Tuples
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：
-- Another way to add the fact that Sally
-- likes Bud to Likes(drinker, beer):
INSERT INTO Likes(beer, drinker)
VALUES(’Bud’,‘Sally’);Adding Default Values（缺省值）
-
In a CREATE TABLE statement, we can follow an attribute by DEFAULT and a value.
-
When an inserted tuple has no value for that attribute, the default will be used.
Example：
CREATE TABLE Drinkers (name CHAR(30) PRIMARY KEY,addr CHAR(50) DEFAULT ’123 Sesame St.’,phone CHAR(16)
);Inserting Many Tuples
We may insert the entire result of a query into a relation, using the form:
INSERT INTO <relation>
( <subquery> );INSERT INTO PotBuddies(SELECT d2.drinkerFROM Frequents d1, Frequents d2WHERE d1.drinker = ’Sally’ ANDd2.drinker <> ’Sally’ ANDd1.bar = d2.bar
);Creating a Table Using the SELECT INTO Statement
-
Use to Create a Table and Insert Rows into the Table in a Single Operation（在一次操作中完成创建表和插入数据的操作）
-
Create a Local or Global Temporary Table（创建本地或全局临时表）
-
Set the select into/bulkcopy Database Option ON in Order to Create a Permanent Table
-
Create Column Alias or Specify Column Names in the Select List for New Table
Deletion
To delete tuples satisfying a condition from some relation:
DELETE FROM <relation>
WHERE <condition>;Example: Deletion
DELETE FROM Likes
WHERE drinker = ’Sally’ AND
beer = ’Bud’;-- Delete all Tuples
DELETE FROM Likes;Semantics of Deletion
DELETE FROM Beers b
WHERE EXISTS (SELECT name FROM BeersWHERE manf = b.manf ANDname <> b.name);-
Suppose Anheuser-Busch makes only Bud and Bud Lite.
-
Suppose we come to the tuple b for Bud first.
-
The subquery is nonempty, because of the Bud Lite tuple, so we delete Bud.
Now, when b is the tuple for Bud Lite, do we delete that tuple too?
Answer: we do delete Bud Lite as well.
The reason is that deletion proceeds in two stages:
-
Mark all tuples for which the WHERE condition is satisfied.
-
Delete the marked tuples.
- 我们可以将涉及子查询的语句拆成两个部分：标记满足条件的部分，然后是执行删除部分
- 在标记时，进行所有原始数据的扫描，然后标记满足条件的数据
- 然后在删除阶段将所有被标记的数据全部删除
Updates
To change certain attributes in certain tuples of a relation:
UPDATE <relation>
SET <list of attribute assignments>
WHERE <condition on tuples>;UPDATE Drinkers
SET phone = ‘555-1212’
WHERE name = ‘Fred’;Example: Update Several Tuples
UPDATE Sells
SET price = 4.00
WHERE price > 4.00;