编辑代码

CREATE DATABASE test;
USE test;
/*
CREATE TABLE TABLE4 (
    STOCK VARCHAR(20),
    SYMBOL CHAR(6),
    SHARETYPE CHAR(1),
    TRADEDATE DATE,
    PRICE DECIMAL(10,2),
    PROVINCE VARCHAR(50)
);
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;

SELECT * 
FROM TABLE4 
WHERE SHARETYPE = 'A' AND PRICE BETWEEN 30 AND 100 
ORDER BY PRICE DESC;
SELECT * FROM TABLE4 WHERE SYMBOL NOT LIKE '%9%';
SELECT * FROM TABLE4 WHERE SHARETYPE='A' AND PROVINCE='广东省';
SELECT * FROM TABLE4 WHERE PRICE=7.11 OR SHARETYPE='B';
SELECT * FROM TABLE4 WHERE SHARETYPE='B' AND (PROVINCE='广东省' OR PROVINCE='浙江省');
SELECT * FROM TABLE4 WHERE SHARETYPE='A' AND PROVINCE='广东省';
SELECT * FROM TABLE4 WHERE PRICE NOT BETWEEN 0 AND 10;
SELECT * FROM TABLE4 ORDER BY SHARETYPE;
SELECT * FROM TABLE4 ORDER BY SHARETYPE, PRICE;
SELECT * FROM TABLE4 ORDER BY PRICE DESC;
SELECT * FROM TABLE4 ORDER BY SHARETYPE ASC, PRICE DESC;
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 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 * FROM TABLE6;
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;