编辑代码

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),
    tradedate date,
    price decimal(10,2),
    PROVINCE varchar(255)
);
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('三环集图',300408,'A',37.41,'广东省');
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.58,'广东省');
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 stock_info(
    stock varchar(20),
    symbol char(6),
    sharetype char(1),
    tradedate date,
    price decimal(10,2),
    PROVINCE varchar(255)
);
SELECT * FROM stock_info;
SELECT SYMBOL FROM stock_info;
SELECT DISTINCT SHARETYPE FROM stock_info;
SELECT STOCK,SYMBOL FROM stock_info;
SELECT * FROM stock_info;
SELECT * FROM stock_info WHERE SHARETYPE='A';
SELECT STOCK FROM stock_info WHERE SHARETYPE ='A';
select * FROM stock_info WHERE SYMBOL LIKE '0%';
select * FROM stock_info WHERE SYMBOL LIKE '%1';
select * FROM stock_info WHERE SYMBOL LIKE '%9%';
select * FROM stock_info WHERE SYMBOL NOT LIKE '%9%';
SELECT * FROM stock_info WHERE SHARETYPE='A' AND PROVINCE='广东省';
SELECT * FROM stock_info WHERE PRICE=7.11 OR SHARETYPE='B';
select * FROM stock_info WHERE SHARETYPE='B' AND (PROVINCE='广东省' OR PROVINCE ='浙江省');
SELECT * FROM stock_info WHERE SHARETYPE='A' AND PROVINCE='广东省';
SELECT * FROM stock_info WHERE PRICE NOT BETWEEN 0 AND 10;
SELECT * FROM stock_info ORDER BY SHARETYPE;
SELECT * FROM stock_info ORDER BY SHARETYPE,PRICE;
SELECT * FROM stock_info ORDER BY PRICE DESC;
SELECT * FROM stock_info ORDER BY SHARETYPE ASC,PRICE DESC;

CREATE table TABLE4(
    stock varchar(20),
    symbol char(6),
    sharetype char(1),
    tradedate date,
    price decimal(10,2),
    PROVINCE varchar(255)
);
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) NOT NULL,          -- 证券代码,固定6位字符
    Trddt DATE NOT NULL,             -- 交易日期
    Orange DECIMAL(10,2) NOT NULL,   -- 日开盘价
    Clsprc DECIMAL(10,2) NOT NULL,   -- 日收盘价
    Dnshrtrd INT NOT NULL,           -- 日个股交易股数
    PRIMARY KEY (Stkcd, Trddt)       -- 联合主键(证券代码+交易日期)
);
INSERT INTO TABLE6 (Stkcd, Trddt, Orange, Clsprc, Dnshrtrd)
VALUES 
('608897', '2019-01-02', 22.91, 22.81, 2176373),
('608898', '2019-01-02', 15.33, 15.33, 8526171),
('608899', '2019-01-02', 30.15, 28.86, 3435768),
('608900', '2019-01-02', 11.88, 11.82, 6295311),
('608901', '2019-01-02', 6.59, 6.62, 9681000),
('608908', '2019-01-02', 20.84, 20.15, 1865700),
('608906', '2019-01-02', 8.65, 8.68, 1106160),
('608909', '2019-01-02', 24.38, 24.51, 9636000),
('608912', '2019-01-02', 25.52, 25.70, 1089095);
SELECT Dnshrtrd 
FROM TABLE6 
WHERE Stkcd = '603903';
SELECT Stkcd, Orange FROM TABLE6;

SELECT Stkcd, Trddt 
FROM TABLE6 
WHERE Clsprc BETWEEN 10 AND 20 
ORDER BY Dnshrtrd DESC;
SELECT * FROM TABLE6 ORDER BY Stkcd;