编辑代码

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 COLUMN 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),
    price decimal(10,2),
    PROVINCE varchar(34)
);

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',0000002,'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('ST星源',000005,'A',1.82,'广东省');
*/

/*
create table TABLE3(
   bankcd char(6),
    shortbnm varchar(20),
    stkcd char(6),
    typrep char(1),
    cash decimal(20)
);
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 STKCD=600927;
DELETE FROM TABLE3 WHERE TYPREP='D';
INSERT INTO TABLE3(BANKCD,SHORTBNM,STKCD,TYPREP,CASH)VALUES(000014,'招商银行',600036,'B','59941600000');
*/

/*
create table stock_info(
    stock varchar(20),
    symbol char(6),
    sharetype char(1),
    price decimal(10,2),
    province char(34)
);
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 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),
    price decimal(10,2),
    province char(34)
);

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),
    trddt date,
    opnprc decimal(10.2),
    clsprc decimal(10,2),
    dnshrtrd decimal(20)
);

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