SQL之修改

因为同一SQL语句，不同厂商有不同的实现方式，因此同一SQL语句不一定在所有的数据库编辑器上能够正确运行，这里采用的是华为的openGauss。

1.INSERT

基本格式为：

INSERT INTO <relation> VALUES (<list of values>);

如：向likes表中插入Sally喜欢Bud啤酒

INSERT INTO Likes VALUES('Sally', 'Bud');

我们也可以指定插入元素的顺序或者只插入某几个元素，基本格式为：

INSERT INTO <relation>(...) ( <subquery> );

如：只向drinkerss表中插入Obama

INSERT INTO drinkers(name) VALUES('Obama');

也可以插入一个子查询

如：用frequents(drinker, bar)表插入新的关系potbuddies(name)，选择条件为那些经常光顾至少一个Charles Babbage光顾的酒吧的人

INSERT INTO PotBuddies
(SELECT d2.drinker
FROM Frequents d1, Frequents d2
WHERE d1.drinker = 'Charles Babbage'
AND d2.drinker <> 'Charles Babbage' 
AND	d1.bar = d2.bar
);

或者

CREATE TABLE AvgPriceOfBeer
(   
    beer varchar(20),
    avgPrice float
);
INSERT INTO AvgPriceOfBeer (avgPrice, beer)
SELECT avg(price), beer FROM Sells
GROUP BY beer;

2.DELETE

基本格式为：

DELETE FROM <relation>
WHERE <condition>;

如：删除likes表中Sally喜欢Bud这条记录

DELETE FROM likes
WHERE drinker = 'Sally’ AND beer = 'Bud';

如：删除likes表中所有元素（应谨慎使用）

DELETE FROM likes;

使用WHERE语句，如删除同一生产商的不同啤酒

DELETE FROM Beers b
WHERE EXISTS (SELECT name FROM Beers
			  WHERE manf = b.manf AND
			  name <> b.name);

3.UPDATE

基本格式为：

UPDATE <relation>
SET <list of attribute assignments>
WHERE <condition on tuples>;

如：将drinker表中Tim Berners-Lee的手机号码改成555-1212

UPDATE Drinkers
SET phone = '555-1212'
WHERE name = 'Tim Berners-Lee';