编辑代码

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);

select*from TABLE1;#让表格显示出来

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;



CREATE TABLE TABLE2 (
    STOCK varchar(20),
    SYMBOL char(6),
    SHARETYPE char(1),
    PRICE decimal(10,2),
    PROVINCE varchar(20)
);

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

select*from TABLE2;

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

#创建银行数据表TABLE3
CREATE TABLE TABLE3(
    Bankcd varchar(20),
    Shortbnm varchar(50),
    Stkcd varchar(20),
    Typrep char(1),      
    Cash decimal(18)
);

#插入南京银行数据
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', 68901675000);

# 更新杭州银行股票代码
UPDATE TABLE3 SET Stkcd='600926' WHERE Shortbnm='杭州银行';

#删除报表类型为D的记录
DELETE FROM TABLE3 WHERE Typrep = 'D';

 #新增招商银行数据
INSERT INTO TABLE3 (Bankcd, Shortbnm, Stkcd, Typrep, Cash) 
VALUES ('000014', '招商银行', '600036', 'B', 599416000000);