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),
PRICE decimal(10,2),
PROVINCE varchar(9)
);
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 VARCHAR(10),
Shortbnm VARCHAR(50),
Stkcd VARCHAR(10),
Typrep CHAR(1),
Cash DECIMAL(20, 2)
);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000027', '南京银行', '601009', 'B', 105220607000.00);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000027', '南京银行', '601009', 'C', 93065245000.00);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000027', '南京银行', '601009', 'A', 93065245000.00);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000084', '贵阳银行', '601997', 'D', 93065245000.00);
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash) VALUES('000021', '北京银行', '601169', 'B', 184491000000.00);
INSERT INTO TABLE3 (Bankcd, Shortbnm, Stkcd, Typrep, Cash) VALUES ('000077', '杭州银行', '600926', 'D', 68901675000.00);
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.00);
select * from TABLE3
*/
/*
CREATE TABLE TABLE4
(
STOCK varchar(20),
SYMBOL char(6),
SHARETYPE char(1),
PRICE decimal(10,2),
PROVINCE varchar(9)
);
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, '广东省') ;
UPDATE TABLE4 SET PRICE=16.25 WHERE STOCK='平安银行';
DELETE FROM TABLE4 WHERE STOCK='B';
DELETE FROM TABLE4 WHERE STOCK='平安银行';
INSERT INTO TABLE4 (STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('平安银行',000001, 'A',16.25, '广东省');
#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 stock_info (
STOCK VARCHAR(50),
SYMBOL INT,
SHARETYPE CHAR(1),
PRICE DECIMAL(10,2),
PROVINCE VARCHAR(50)
);
INSERT INTO stock_info(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('平安银行',000001,'A',69.97,'广东省');
INSERT INTO stock_info(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('长安B',200625, 'B', 7.11, '重庆市') ;
INSERT INTO stock_info(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('比亚迪',002594, 'A',172.78, '广东省') ;
INSERT INTO stock_info(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE) VALUES('东信B',900941, 'B',0.47, '浙江省') ;
select * from stock_info
DER BY PRICE DESC;
*/
CREATE TABLE TABLE6 (
Stkcd char(6),
Trddt date,
Oppnprc decimal(10,2),
Clsprc decimal(10,2),
Dnshrtrd int
);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603897', '2019-01-02', 23.01, 22.81, 2176373);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603898', '2019-01-02', 15.33, 15.33, 852951);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603899', '2019-01-02', 30.15, 28.86, 3435768);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603900', '2019-01-02', 11.88, 11.82, 629531);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603901', '2019-01-02', 6.59, 6.62, 968100);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603902', '2019-01-02', 20.84, 20.15, 1865700);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603906', '2019-01-02', 8.65, 8.63, 1106160);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603908', '2019-01-02', 24.38, 24.51, 963600);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603909', '2019-01-02', 25.52, 25.7, 1089096);
INSERT INTO TABLE6 (Stkcd, Trddt, Oppnprc, Clsprc, Dnshrtrd) VALUES ('603912', '2019-01-02', 13.32, 13.7, 4097856);
SELECT Dnshrtrd FROM TABLE6 WHERE Stkcd = '603903';
SELECT Stkcd, Oppnprc FROM TABLE6;
SELECT Stkcd, Trddt FROM TABLE6 WHERE Clsprc BETWEEN 10 AND 20 ORDER BY Dnshrtrd DESC;