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;