编辑代码

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),
    price decimal(10,2),
    province varchar(34)
);
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,'广东省');
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(20),
    stkcd char(6),
    typrep char(1),
    cash decimal(18, 0)
);
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', 1844910000000);
insert into table3 (bankcd, shortbnm, stkcd, typrep, cash) values ('000077', '杭州银行', '600927', 'D', 68901675000);
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);
*/
create table table6 (
    stkcd char(6),
    trddt date,
    opnprc decimal(10,2),
    clsprc decimal(10,2),
    dnshrtrd varchar(7)
);

insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603897','2019-01-02',23.01,22.81,'2176373');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603898','2019-01-02',15.33,15.33,'852951');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603899','2019-01-02',30.15,28.86,'3435768');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603900','2019-01-02',11.88,11.82,'629531');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603901','2019-01-02',6.59,6.62,'968100');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603903','2019-01-02',20.84,20.15,'1865700');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603906','2019-01-02',8.65,8.63,'1106160');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603908','2019-01-02',24.38,24.51,'963600');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603909','2019-01-02',25.52,25.7,'1089096');
insert into table6(stkcd,trddt,opnprc,clsprc,dnshrtrd) values('603912','2019-01-02',13.32,13.7,'4097856');
#SELECT dnshrtrd FROM table6 WHERE stkcd = '603903';
#SELECT stkcd, opnprc FROM table6;
SELECT stkcd, trddt FROM table6 WHERE clsprc BETWEEN 10 AND 20 ORDER BY dnshrtrd DESC;