编辑代码

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 COLUMN 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;
select * from TABLE1;
*/
/*
CREATE TABLE TABLE2 (
    STOCK varchar(20),
    SYMBOL char(6),
    SHARETYPE char(1),
    TRADEDATE date,
    PRICE decimal(10,2),
    PROVINCE VARCHAR(20)
);
INSERT INTO TABLE2 VALUES
('平安银行', '200012', 'B', '2021-05-20', 3.58, '广东省'),  -- 修正了股票代码和引号
('长安B', '200625', 'B', '2021-05-20', 7.11, '重庆市'),
('ST星源', '000005', 'A', '2021-05-20', 1.82, '广东省'),
('北京城乡', '600861', 'A', '2021-05-20', 14.38, '北京市'),
('三环集团', '300408', 'A', '2021-05-20', 37.41, '广东省'),
('万科A', '000002', 'A', '2021-05-20', 26.77, '广东省'),
('南玻B', '200012', 'B', '2021-05-20', 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 TABLE2;
*/
-- 创建 TABLE3(修正字段类型)
CREATE TABLE TABLE3 (
    Bankcd varchar(20),
    Shortbnm varchar(50),  -- 扩大长度以适应银行名称
    Stkcd varchar(10),     -- 改为字符型以保留前导零
    Typrep char(1),        -- 修正为字符类型
    Cash decimal(15,2)     -- 扩大精度以容纳大额数值
);

-- 插入数据(统一格式)
INSERT INTO TABLE3 VALUES
('000027', '南京银行', '601009', 'B', 105220607000.00),
('000027', '南京银行', '601009', 'C', 105220607000.00),
('000027', '南京银行', '601009', 'A', 105220607000.00),
('000027', '南京银行', '601009', 'D', 105220607000.00),
('000021', '北京银行', '601169', 'B', 184491000000.00),
('000077', '杭州银行', '600927', 'D', 68901675000.00);

-- 更新语句修正(假设要更新的是Shortbnm)
UPDATE TABLE3 SET Stkcd = '600926' WHERE Shortbnm = '南京银行';

-- 删除语句修正
DELETE FROM TABLE3 WHERE Typrep = 'D';

-- 插入TABLE2的语句应单独执行(需确认TABLE2结构)
-- 假设TABLE2有对应字段:
INSERT INTO TABLE3 (Bankcd,Shortbnm,Stkcd,Typrep,Cash)VALUES ('000014','招商银行','600036', 'B', 599416000000.00);
SELECT * FROM TABLE3;