编辑代码

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),
    tradedate date,
    price decimal(10,2),
    PROVINCE varchar(255)
);
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,'广东省');
SELECT * FROM stock_info;
*/

-- 1. 创建TABLE3表
CREATE TABLE TABLE3 (
    Bankcd CHAR(6) NOT NULL,        -- 银行代码，固定6位字符
    Shorthnn VARCHAR(20) NOT NULL,  -- 银行简称，可变长度字符串
    Stkcd CHAR(6) NOT NULL,         -- 股票代码，固定6位字符
    Typrep CHAR(1) NOT NULL,        -- 报表类型，单字符(A/B/C/D等)
    Cash DECIMAL(18,2) NOT NULL,    -- 存放金额，最大18位数字含2位小数
    PRIMARY KEY (Bankcd, Typrep)    -- 联合主键(银行代码+报表类型)
);

-- 2. 插入初始数据
INSERT INTO TABLE3 (Bankcd, Shorthnn, Stkcd, Typrep, Cash)
VALUES 
('000027', '南京银行', '601009', 'B', 105220607000.00),
('000027', '南京银行', '601009', 'C', 93065245000.00),
('000027', '南京银行', '601009', 'A', 106225841000.00),
('000084', '贵阳银行', '601997', 'D', 41210720000.00);

-- 3. 执行技能实训任务
-- (1) 新增两条数据
INSERT INTO TABLE3 (Bankcd, Shorthnn, Stkcd, Typrep, Cash)
VALUES 
('000021', '北京银行', '601169', 'B', 184491000000.00),
('000077', '杭州银行', '600927', 'D', 68901675000.00);

-- (2) 修改杭州银行股票代码
UPDATE TABLE3 
SET Stkcd = '600926' 
WHERE Bankcd = '000077' AND Stkcd = '600927';

-- (3) 删除D型报表并新增招商银行
DELETE FROM TABLE3 WHERE Typrep = 'D';

INSERT INTO TABLE3 (Bankcd, Shorthnn, Stkcd, Typrep, Cash)
VALUES ('000014', '招商银行', '600036', 'B', 599416000000.00);

-- 4. 验证最终结果
SELECT * FROM TABLE3 ORDER BY Bankcd, Typrep;