编辑代码

CREATE DATABASE test;
use test;
/*
CREATE TABLE TABLE1 (
    STOCK varchar(20), -- 可变字符，长度不超过20
    SYMBOL char(6), -- 长度为6位
    SHARETYPE char(1), -- 股票类型A或B
    TRADEDATE date, -- 交易日期
    PRICE decimal(10,2) -- 表示小数点左边不超过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(50),
    SYMBOL VARCHAR(10),
    SHARETYPE CHAR(1),
    PRICE DECIMAL(10,2),
    PROVINCE VARCHAR(50)
);

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


CREATE TABLE TABLE3 (
    Bankcd VARCHAR(50),
    Shortbnm VARCHAR(10),
    Stkcd CHAR(6),       
    Typrep CHAR(1),        
    Cash VARCHAR(50)
);

INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)VALUES('000021','北京银行','601169','B','184491000000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)VALUES('000077','杭州银行','600927','D','68901675000');

UPDATE  TABLE3 SET  Stkcd=600926 WHERE Shortbnm='杭州银行';

DELETE FROM TABLE3 WHERE Typrep='D';
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)VALUES('000014','招商银行','600036','B','59941600000');