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;
*/
/*
CREATE TABLE TABLE2
(
STOCK varchar(20),
SYMBOL char(6),
SHARETYPE char(1),
TRADEDATE date,
PRICE decimal(10,2),
PROVINCE varchar(30)
);
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',1.82,'广东省');
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,'广东省');
*/
/*
CREATE TABLE TABLE3
(
Bankcd char(6),
Shortbnm varchar(50),
Stkcd char(6),
Typrep char(1),
Cash decimal(20,2)
);
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 Bankcd='000077';
DELETE FROM TABLE3 WHERE Typrep = 'D';
INSERT INTO TABLE3 (Bankcd, Shortbnm, Stkcd, Typrep, Cash)VALUES ('000014', '招商银行', '600036', 'B', 599416000000.00);
*/
/*
CREATE TABLE TABLE4
(
STOCK varchar(20),
SYMBOL char(6),
SHARETYPE char(1),
PRICE decimal(10,2),
PROVINCE varchar(30)
);
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('平安银行','000001','A',69.97,'广东省');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('长安B','200625','B',7.11,'重庆市');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('比亚迪','002594','A',172.78,'广东省');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('东信B','900941','B',0.47,'浙江省');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('复星医药','600196','A',65.00,'上海市');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('ST星源','000005','A',1.82,'广东省');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('北京城乡','600861','A',14.38,'北京市');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('三环集团','300408','A',37.41,'广东省');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('万科A','000002','A',26.77,'广东省');
INSERT INTO TABLE4(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('南玻B','200012','B',3.58,'广东省');
#select*FROM TABLE4;
#select STOCK,SYMBOL FROM TABLE4;
select STOCK,SYMBOL,PRICE FROM TABLE4 WHERE PRICE BETWEEN 30 AND 100 ORDER BY PRICE DESC;
*/
CREATE TABLE TABLE6
(
STOCK varchar(20),
Trddt date,
Opnprc decimal(10,2),
Clsprc decimal(10,2),
Dnshrtrd int unsigned
);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603897', '2019-01-02', 23.01, 22.81, 2176373);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603897', '2019-01-02', 23.01, 22.81, 2176373);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603898', '2019-01-02', 15.33, 15.33, 852951);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603899', '2019-01-02', 30.15, 28.86, 3435768);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603900', '2019-01-02', 11.88, 11.82, 629531);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603901', '2019-01-02', 6.59, 6.62, 968100);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603903', '2019-01-02', 20.84, 20.15, 1865700);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603906', '2019-01-02', 8.65, 8.63, 1106160);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603908', '2019-01-02', 24.38, 24.51, 963600);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603909', '2019-01-02', 25.52, 25.7, 1089096);
INSERT INTO TABLE6 (STOCK, Trddt, Opnprc, Clsprc, Dnshrtrd) VALUES('603912', '2019-01-02', 13.32, 13.7, 4097856);
SELECT Dnshrtrd FROM TABLE6 WHERE STOCK = '603903';
SELECT STOCK, Opnprc FROM TABLE6;
SELECT STOCK, Trddt FROM TABLE6 WHERE Clsprc BETWEEN 10 AND 20 ORDER BY Dnshrtrd DESC;