编辑代码

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;
*/

CREATE TABLE TABLE2
(
    STOCK varchar(20),
    SYMBOL char(6),
    SHARETYPE char(1),
    PRICE decimal(5,2),
    PROVINCE varchar(100)
);

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, '广东省');

-- 创建 TABLE3 表（银行资产负债表）
CREATE TABLE TABLE3 (
    Bankcd VARCHAR(6),
    Shortbnm VARCHAR(20),
    Stkcd VARCHAR(6),
    Typrep CHAR(1),
    Cash DECIMAL(15,2)
);

-- 插入初始数据
INSERT INTO TABLE3 VALUES
('000027', '南京银行', '601009', 'B', 105220607000),
('000027', '南京银行', '601009', 'C', 93065245000),
('000027', '南京银行', '601009', 'A', 106225841000),
('000084', '贵阳银行', '601997', 'D', 41210720000);

-- (1) 新增两条银行财务数据信息
INSERT INTO TABLE3 VALUES
('000021', '北京银行', '601169', 'B', 184491000000),
('000077', '杭州银行', '600927', 'D', 68901675000);

-- (2) 将杭州银行的股票代码"600927"改为"600926"
UPDATE TABLE3 
SET Stkcd = '600926' 
WHERE Shortbnm = '杭州银行' AND Stkcd = '600927';

-- (3) 将报表类型D的信息删除
DELETE FROM TABLE3 
WHERE Typrep = 'D';

-- 新增招商银行信息
INSERT INTO TABLE3 VALUES
('000014', '招商银行', '600036', 'B', 599416000000);