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;
select * from TABLE1
*/
/*
CREATE TABLE TABLE2
(
STOCK varchar(20),
SYMBOL char(6),
SHARETYPE char(1),
PRICE decimal(10,2),
PROVINCE varchar(10)
);
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)
VALUES('平安银行','200012','B',3.58,'广东省');
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)
VALUES('长安B','200625','B',7.11,'重庆市');
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 TABLE2
*/
CREATE TABLE TABLE3
(
Bankcd char(6),
Shortbnm varchar(20),
Stkcd char(6),
Typrep char(1),
Cash bigint
);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)
VALUES('000027','南京银行','601009','B','105220607000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)
VALUES('000027','南京银行','601009','C','93065245000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)
VALUES('000027','南京银行','601009','A','106225841000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)
VALUES('000084','贵阳银行','601997','D','41210720000');
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','41210720000');
UPDATE TABLE3 SET Stkcd='600927' WHERE Bankcd='000077';
DELETE FROM TABLE3 WHERE Typrep='D';
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)
VALUES('000014','招商银行','600036','B','599416000000');
select * from TABLE3