实验二 SQL 语言数据操纵语言 DML

CREATE TABLE test (  
    Name VARCHAR(20),  
    Age INTEGER,  
    Score NUMERIC(10, 2),  
    Address VARCHAR(60)  
);

INSERT INTO test (Name, Age, Score, Address) VALUES  
('赵一', 20, 580.00, '兴业苑 2-3-5'),  
('钱二', 19, 540.00, '南福苑 5-2-9'),  
('孙三', 21, 555.50, '兴业苑 21-5-15'),  
('李四', 22, 505.00, '知行苑 8-6-22'),  
('周五', 20, 495.50, '宁静苑 23-4-8'),  
('吴六', 19, 435.00, '南福苑 2-5-12');

CREATE TABLE test_temp (  
    Name VARCHAR(20),  
    Age INTEGER,  
    Score NUMERIC(10, 2),  
    Address VARCHAR(60)  
);

INSERT INTO test_temp (Name, Age, Score, Address) VALUES  
('郑七', 21, 490.50, '兴业苑 11-2-1'),  
('张八', 20, 560.00, '明理苑 3-3-3'),  
('王九', 19, 515.00, '知行苑 19-7-1');

INSERT INTO test (Name, Age, Score, Address)  
SELECT Name, Age, Score, Address FROM test_temp;

UPDATE test  
SET Score = Score + 5  
WHERE Age <= 20;

UPDATE test  
SET Age = Age - 1  
WHERE Address LIKE '%兴业苑%';

DELETE FROM test  
WHERE Age >= 21 AND Score >= 500;

DELETE FROM test  
WHERE Score < 550 AND Address LIKE '%南福苑%';