编辑代码

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(10)
);
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 ('万科A', '000002', 'A', 26.77, '广东省');

INSERT INTO TABLE2 (STOCK, SYMBOL, SHARETYPE, PRICE, PROVINCE)
VALUES ('南玻B', '200012', 'B', 3.78, '广东省');
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),
    STOCK VARCHAR(20),
    SYMBOL CHAR(6),
    SHARETYPE CHAR(1),
    CASH DECIMAL(12, 0)
);
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000027', '南京银行', '601009', 'B', 105220607000);
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000027', '南京银行', '601009', 'C', 93065245000);
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000027', '南京银行', '601009', 'A', 106225841000);
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000084', '贵阳银行', '601997', 'D', 41210720000);
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000021', '北京银行', '601169', 'B', 184491000000);
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000077', '杭州银行', '600927', 'D', 68901675000);
UPDATE  TABLE3 SET  SYMBOL=600926  WHERE STOCK='杭州银行';
DELETE FROM TABLE3 WHERE SHARETYPE = 'D';
INSERT INTO TABLE3 (BANKCD, STOCK, SYMBOL, SHARETYPE, CASH)
VALUES ('000014', '招商银行', '600036', 'B', 599416000000);
*/

CREATE TABLE TABLE6 (
    Stkcd CHAR(6),
    Trddt DATE,
    Opnprc DECIMAL(10,2),
    Clsprc DECIMAL(10,2),
    Dnshrtrd INT
);

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 Stkcd, Dnshrtrd 
FROM TABLE6 
WHERE Stkcd = '603903' AND Dnshrtrd = 1865700;
SELECT Stkcd, Opnprc FROM TABLE6;
SELECT Stkcd,Trddt,Clsprc,Dnshrtrd FROM TABLE6 WHERE Clsprc BETWEEN 10 AND 20   
ORDER BY Dnshrtrd DESC;