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