编辑代码

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-02-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(50)
);
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 TABLE3
(
    Bankcd char(20),
    Shortbnm varchar(12),
    Stkcd char(6),
    Typrep char(1),
    Cash varchar(50)
);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000027','南京银行','601009','B','105220607000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000027','南京银行','601009','C','93065245000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000027','南京银行','601009','A','106225841000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000084','贵阳银行','601997','D','41210720000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000021','北京银行','601169','B','184491000000');
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000077','杭州银行','600927','D','68901675000');

UPDATE TABLE3
SET Stkcd = '600926'
WHERE Bankcd = '000077';

DELETE FROM TABLE3
WHERE Typrep = 'D';

INSERT INTO TABLE3(Bankcd, Shortbnm, Stkcd, Typrep, Cash)
VALUES('000014', '招商银行', '600036', 'B', '599416000000');

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;
*/
CREATE TABLE TABLE6
(
    Stkcd char(6),
    Trdtt date,
    Opnprc decimal(10,2),
    Clsprc decimal(10,2),
    Dnshrtrd varchar(50)
);
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603897','2019-01-02','23.01','22.81','2176373');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603898','2019-01-02','15.33','15.33','852951');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603899','2019-01-02','30.15','28.86','3435768');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603900','2019-01-02','11.88','11.82','629531');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603901','2019-01-02','6.59','6.62','968100');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603903','2019-01-02','20.84','20.15','1865700');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603906','2019-01-02','8.65','8.63','1106160');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603908','2019-01-02','24.38','24.51','963600');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603909','2019-01-02','25.52','25.7','1089096');
INSERT INTO TABLE6(Stkcd,Trdtt,Opnprc,Clsprc,Dnshrtrd)  VALUES('603912','2019-01-02','13.32','13.7','4097856');
SELECT Stkcd,Dnshrtrd FROM TABLE6;
SELECT Stkcd,Opnprc FROM TABLE6;
SELECT Stkcd,Trdtt,Clsprc FROM TABLE6 WHERE Clsprc BETWEEN 10 AND 20  ORDER BY Clsprc DESC;