编辑代码

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(20)
);

-- 插入与表格完全一致的数据
INSERT INTO TABLE4 VALUES
('平安银行', '000001', 'A', '2021-05-20', 69.97, '广东省'),
('长安B', '200625', 'B', '2021-05-20', 7.11, '重庆市'),
('比亚迪', '002594', 'A', '2021-05-20', 172.78, '广东省'),
('东信B', '900941', 'B', '2021-05-20', 0.47, '浙江省'),
('复星医药', '600196', 'A', '2021-05-20', 65.00, '上海市'),
('ST星源', '000005', 'A', '2021-05-20', 1.82, '广东省'),
('北京城乡', '600861', 'A', '2021-05-20', 14.38, '北京市'),
('三环集团', '300408', 'A', '2021-05-20', 37.41, '广东省'),
('万科A', '000002', 'A', '2021-05-20', 26.77, '广东省'),
('南玻B', '200012', 'B', '2021-05-20', 3.58, '广东省');
SELECT STOCK,SYMBOL FROM TABLE4;
SELECT * FROM TABLE4;
SELECT STOCK,SYMBOL,PRICE FROM TABLE4 WHERE PRICE BETWEEN 30 AND 100  ORDER BY PRICE DESC;*/

-- 创建新的股票交易数据表（根据您提供的数据结构）
CREATE TABLE TABLE6 (
    Stkcd CHAR(6) NOT NULL,        -- 股票代码
    Trddt DATE NOT NULL,           -- 交易日期
    Opnprc DECIMAL(10,2),          -- 开盘价
    Clsprc DECIMAL(10,2),          -- 收盘价
    Dnshrtrd INT,                  -- 成交量(股)
    PRIMARY KEY (Stkcd, Trddt)     -- 设置复合主键
);

-- 插入您提供的所有交易数据
INSERT INTO TABLE6 (Stkcd, Trddt, Opnprc, 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.70, 1089096),
('603912', '2019-01-02', 13.32, 13.70, 4097856);
SELECT * FROM TABLE6;
SELECT * FROM TABLE6 WHERE Stkcd='603903' AND Dnshrtrd;
SELECT Stkcd  , Opnprc FROM TABLE6;
SELECT Stkcd,Trddt	,Opnprc,	Clsprc,	Dnshrtrd FROM TABLE6 WHERE Clsprc BETWEEN 10 AND 20  ORDER BY Dnshrtrd DESC;

;