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:

1. We forget the standard order of attributes for the relation.

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.

• 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.drinker
    FROM Frequents d1, Frequents d2
    WHERE d1.drinker = ’Sally’ AND
    d2.drinker <> ’Sally’ AND
    d1.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 Beers
    WHERE manf = b.manf AND
    name <> 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.

1. 我们可以将涉及子查询的语句拆成两个部分：标记满足条件的部分，然后是执行删除部分
2. 在标记时，进行所有原始数据的扫描，然后标记满足条件的数据
3. 然后在删除阶段将所有被标记的数据全部删除

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;