编辑代码

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(20)
);
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('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 varchar(20),
    SHortbnm char(4),
    Stkcd char(6),
    Typrep char(1),
    Cash varchar(20)
);
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 STOCK='杭州银行';
DELETE FROM TABLE3 WHERE STOCK='D';
INSERT INTO TABLE3(Bankcd,SHortbnm,Stkcd,Typrep,Cash)VALUES('000014','招商银行','600036','B','599416000000');
*/
/*
CREATE TABLE TABLE4(
    STOCK varchar(20),
    SYMBOL char(6),
    SHARETYPE char(1),
    PRICE decimal(10,2),
    PROVINCE varchar(20)
);
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(
    Stkcd char(6),
    Trddt date,
    Opnprc decimal(10,2),
    Clsprc decimal(10,2),
    Dnshrtrd varchar(30)
);
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 CAST(Dnshrtrd AS UNSIGNED) DESC;








SELECT * FROM TABLE6;