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;
*/
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);
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', 599416000000.00);
SELECT * FROM TABLE3;