编辑代码

-- 1. 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS securities_db;
USE securities_db;  -- 切换到新创建的数据库

-- 2. 创建TABLE6表
CREATE TABLE TABLE6 (
    Siked    CHAR(6),
    Tiddt    DATE,
    Omprc    DECIMAL(10,2),
    Cisprc   DECIMAL(10,2),
    Dnshtrd  BIGINT
);

-- 3. 插入原始数据
INSERT INTO TABLE6 (Siked, Tiddt, Omprc, Cisprc, Dnshtrd) 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);

-- 4. 执行查询任务
-- (1) 查询证券代码为603903的日个股交易股数
SELECT Dnshtrd 
FROM TABLE6 
WHERE Siked = '603903';

-- (2) 查询所有证券代码和对应的日开盘价
SELECT Siked, Omprc 
FROM TABLE6;

-- (3) 查询日收盘价在10~20之间的证券代码和交易日期，按交易股数降序排序
SELECT Siked, Tiddt 
FROM TABLE6 
WHERE Cisprc BETWEEN 10 AND 20 
ORDER BY Dnshtrd DESC;