编辑代码

CREATE DATABASE test;
use test;
/*
CREATE TABLE TABLE4
 (
    STOCK VARCHAR(50),        -- 股票简称
    SYMBOL CHAR(6),           -- 股票代码
    SHARETYPE CHAR(1),        -- 股票类型（如 A/B 股）
    PRICE DECIMAL(10,2),      -- 价格
    PROVINCE VARCHAR(50)      -- 所属省份
);
INSERT INTO TABLE4 (STOCK, SYMBOL, SHARETYPE, PRICE, PROVINCE) VALUES
('三环集团', '300408', 'A', 37.41, '广东省'),
('万科A', '000002', 'A', 26.77, '广东省'),
('南玻B', '200012', 'B', 3.58, '广东省'),
('平安银行', '000001', 'A', 16.25, '广东省'),
('贵州茅台', '600519', 'A', 1900.00, '贵州省'),
('中国平安', '601318', 'A', 52.30, '广东省');
SELECT * FROM TABLE4;
SELECT SYMBOL FROM TABLE4;
SELECT DISTINCT SHARETYPE FROM TABLE4;
SELECT STOCK, SYMBOL FROM TABLE4;
SELECT * FROM TABLE4;
SELECT * FROM TABLE4 WHERE SHARETYPE='A';
SELECT * FROM TABLE4 WHERE SYMBOL LIKE '0%';
SELECT * FROM TABLE4 WHERE SYMBOL LIKE '%1';
SELECT * FROM TABLE4 WHERE SYMBOL LIKE '%9';
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;SELECT Dnshrtrd 
*/
CREATE TABLE TABLE6 (
    Stkcd CHAR(6),
    Trddt DATE,
    Opprc DECIMAL(10,2),
    Clsprc DECIMAL(10,2),
    Dnshrtrd INT
);
INSERT INTO TABLE6 (Stkcd, Trddt, Opprc, Clsprc, Dnshrtrd) VALUES
('603897', '2019-01-02', 23.01, 22.81, 2176373),
('603898', '2019-01-02', 15.33, 15.33, 852951),
('603899', '2019-01-02', 30.15, 28.86, 3435768),
('603900', '2019-01-02', 11.88, 11.82, 629531),
('603901', '2019-01-02', 6.59, 6.62, 968100),
('603903', '2019-01-02', 20.84, 20.15, 1865700),
('603906', '2019-01-02', 8.65, 8.63, 1106160),
('603908', '2019-01-02', 24.38, 24.51, 963600),
('603909', '2019-01-02', 25.52, 25.7, 1089096),
('603912', '2019-01-02', 13.32, 13.7, 4097856);
SELECT Dnshrtrd 
FROM TABLE6 
WHERE Stkcd = '603903';
SELECT Stkcd, Opprc 
FROM TABLE6;
SELECT Stkcd, Trddt, Dnshrtrd 
FROM TABLE6 
WHERE Clsprc BETWEEN 10 AND 20 
ORDER BY Dnshrtrd DESC;