编辑代码

CREATE DATABASE test;
use test
/*
CREATE TABLE TABLE1
(
STOCK  varchar(20),
SYMBOL char(6),
SHARETYPE  char(1),
TRADEDATE date,
PRICE  decimal(10,2)
):
INSERT INTO TABLE1(STOCK,SYMBOL,SHARETYPE,TRADEDATE,PRICE) VALUES ('平安银行','000001','A','2021-05-20', 69.97);
INSERT INTO TABLE1(STOCK,SYMBOL,SHARETYPE,TRADEDATE,PRICE) VALUES ('长安B','200625','B', '2021-05-20', 7.11);
INSERT INTO TABLE1(STOCK,SYMBOL,SHARETYPE,TRADEDATE,PRICE) VALUES ('ST星源','000005','A','2021-05-20',1.82);
INSERT INTO TABLE1(STOCK, SHARETYPE) VALUES ('比亚迪', 'A');
ALTER TABLE TABLE1 ADD (PROVINCE varchar(10));
UPDATE TABLE1 SET PRICE=70.2 WHERE STOCK='平安银行';
UPDATE TABLE1 SET PRICE=70.15,SYMBOL='000002' WHERE STOCK='平安银行';
DELETE FROM TABLE1 WHERE STOCK='平安银行';
DELETE FROM TABLE1;
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('ST星源','000005', 'A', 1.82, '广东省');
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('北京城乡','600861', 'A', 14.38, '北京市');
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('三环集团','300408', 'A', 37.41, '广东省');
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('万科A','000002', 'A', 26.77, '广东省');
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('南玻B','200012', 'B', 3.58, '广东省');
UPDATE TABLE2 SET PRICE=16.25 WHERE STOCK='平安银行';
DELETE FROM TABLE2 WHERE STOCK='B';
DELETE FROM TABLE2 WHERE STOCK='平安银行';
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('平安银行','000001', 'A',16.25, '广东省');
-- (1) 新增两条银行财务数据信息
INSERT INTO TABLE3(Banked, Shortbnm, Siked, Typrep, Cash) VALUES('000021', '北京银行', '601169', 'B', 184491000000);
INSERT INTO TABLE3(Banked, Shortbnm, Siked, Typrep, Cash) VALUES('000077', '杭州银行', '600927', 'D', 68901675000);

-- (2) 修改杭州银行的股票代码
UPDATE TABLE3 SET Siked='600926' WHERE Shortbnm='杭州银行';

-- (3) 删除报表类型D的信息并新增留存银行信息
DELETE FROM TABLE3 WHERE Typrep='D';
INSERT INTO TABLE3(Banked, Shortbnm, Siked, Typrep, Cash) VALUES('000014', '留存银行', '600036', 'B', 599416000000);