编辑代码

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, SYMBOL, SHARETYPE, TRADEDATE, PRICE) VALUES ('比亚迪', 'A', 'A', '2021-05-20', 0);
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;
*/
/*
CREATE TABLE TABLE2
(
    STOCK VARCHAR(20),
    SYMBOL CHAR(6),
    SHARETYPE CHAR(1),
    TRADEDATE DATE,
    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, '广东省');
SELECT * FROM TABLE2 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, '广东省');
*/
DROP TABLE IF EXISTS TABLE3;

CREATE TABLE TABLE3
(
    Bankcd VARCHAR(20),
    Shortbnm VARCHAR(50),
    Stkcd CHAR(6),
    Typrep CHAR(1),
    Cash DECIMAL(15,2)
);

-- 插入北京银行和杭州银行的数据
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 Bankcd = '000077' AND Shortbnm = '杭州银行';

-- 删除报表类型为 D 的信息
DELETE FROM TABLE3
WHERE Typrep = 'D';

-- 插入招商银行的信息
INSERT INTO TABLE3 (Bankcd, Shortbnm, Stkcd, Typrep, Cash)
VALUES ('000014', '招商银行', '600036', 'B', 599416000000);