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-28', 69.97);
INSERT INTO TABLE1(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE) VALUES ('长安汽车', '000625', '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 char(20)
);
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 ('南玻A', '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(6),
SHORTBTHNM varchar(20),
STKCD char(6),
TYPREP char(1),
CASH decimal(20,0)
);
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000027', '南京银行', '601009', 'B', 105220607000);
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000027', '南京银行', '601009', 'C', 93065245000);
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000027', '南京银行', '601009', 'A', 106225841000);
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000084', '贵阳银行', '601997', 'D', 42107020000);
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000021', '北京银行', '601169', 'B', 184491000000);
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000077', '杭州银行', '600927', 'D', 68901675000);
UPDATE TABLE3 SET STKCD = '600926' WHERE SHORTBTHNM = '杭州银行';
DELETE FROM TABLE3 WHERE TYPREP = 'D';
INSERT INTO TABLE3 (BANKCD, SHORTBTHNM, STKCD, TYPREP, CASH) VALUES ('000014', '招商银行', '600036', 'D', 599416000000);
CREATE TABLE TABLE4 (
STOCK varchar(20),
SYMBOL char(6),
SHARETYPE char(1),
TRADEDATE date,
PRICE decimal(10,2),
PROVINCE varchar(20)
);
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('平安银行', '000001', 'A', '2023-05-15', 69.97, '广东省');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('长安8', '000625', 'B', '2023-05-15', 7.11, '重庆市');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('比亚迪', '002594', 'A', '2023-05-15', 172.78, '广东省');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('东信', '600941', 'B', '2023-05-15', 0.47, '浙江省');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('复星医药', '600196', 'A', '2023-05-15', 65.00, '上海市');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('ST星源', '000005', 'A', '2023-05-15', 1.82, '广东省');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('北京城乡', '600861', 'A', '2023-05-15', 13.38, '北京市');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('三环集团', '300408', 'A', '2023-05-15', 37.41, '广东省');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('万科A', '000002', 'A', '2023-05-15', 26.77, '广东省');
INSERT INTO TABLE4(STOCK, SYMBOL, SHARETYPE, TRADEDATE, PRICE, PROVINCE) VALUES('南玻B', '200012', 'B', '2023-05-15', 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 stock_info (
STOCK varchar(20),
SYMBOL char(6),
SHARETYPE char(1),
PRICE decimal(10, 2),
PROVINCE varchar(20)
);
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;
SELECT DISTINCT SHARETYPE FROM stock_info;
SELECT STOCK, SYMBOL FROM stock_info;
SELECT * FROM stock_info LIMIT 2;
SELECT * FROM stock_info WHERE SHARETYPE = 'A';
SELECT * FROM stock_info WHERE PRICE < 50;
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 TABLE6 (
Stkcd VARCHAR(20),
Trddt DATE,
Opnprc DECIMAL(10, 2),
Clsprc DECIMAL(10, 2),
Dnshrtrd BIGINT
);
INSERT INTO TABLE6 (Stkcd, Trddt, Opnprc, Clsprc, Dnshrtrd)
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.7, 1089096),
('603912', '2019-01-02', 13.32, 13.7, 4097856);
SELECT Dnshrtrd
FROM TABLE6
WHERE Stkcd = '603903';
SELECT Stkcd, Opnprc
FROM TABLE6;
SELECT Stkcd, Trddt
FROM TABLE6
WHERE Clsprc BETWEEN 10 AND 20
ORDER BY Dnshrtrd DESC;