编辑代码

CREATE DATABASE test;
use test;
/*
# 建立TABLEL1
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 VALUES('长安B','200625','B','2021-05-20',7.11);
INSERT INTO TABLE1 VALUES('ST星源','000005','A','2021-05-20',1.82);
insert into TABLE1(stock,sharetype) values('比亚迪','A');
select * from TABLE1;


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='平安银行';
select * from TABLE1;


CREATE TABLE TABLE2 (
    STOCK varchar(20),
    SYMBOL char(6),
    SHARETYPE char(1),
    PROVINCE varchar(50),
    TRADEDATE date,
    PRICE decimal(10,2)

);


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,'广东省');
select * from TABLE2;

DELETE FROM TABLE2 WHERE STOCK='B';
select * from TABLE2;

DELETE FROM TABLE2 WHERE STOCK='平安银行';
INSERT INTO TABLE2 (STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('平安银行',000001,'A',16.25,'广东省');
select * from TABLE2;
*/

CREATE TABLE TABLE3(
    BANKCD VARCHAR(10),  
    SHORTBNM VARCHAR(50),  
    STKCD VARCHAR(10),  
    TYPERP VARCHAR(10),  
    CASH BIGINT,  
    PRIMARY KEY (BANKCD, STKCD, TYPERP)  
);

insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000027','南京银行','601009','B',105220607000);
insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000027','南京银行','601009','C',93065245000);
insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000027','南京银行','601009','A',106225841000);
insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000084','贵阳银行','601997','D',41210720000);
select * from TABLE3;
insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000021','北京银行','601169','B',184491000000);
insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000077','杭州银行','600927','D',68901675000);
select * from TABLE3;
UPDATE TABLE3 SET STKCD='600926' WHERE SHORTBNM='杭州银行';
select * from TABLE3;
DELETE FROM TABLE3 WHERE TYPERP='B';
insert into TABLE3 (BANKCD,SHORTBNM, STKCD,TYPERP,CASH) values('000014','招商银行','600036','B',599416000000);
select * from TABLE3;
/*
create table TABLE4(
    STOCK varchar(20),
    SYMBOL varchar(20),
    SHARETYPE varchar(10),
    PRICE float,
    PROVINCE varchar(20)
);

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 TABLE5(
    STOCK varchar(20),
    SYMBOL varchar(20),
    SHARETYPE varchar(10),
    PRICE float,
    PROVINCE varchar(20)
);

insert into TABLE5 (STOCK, SYMBOL, SHARETYPE, PRICE, PROVINCE) values('平安银行','000001','A',69.97,'广东省');
insert into TABLE5 (STOCK, SYMBOL, SHARETYPE, PRICE, PROVINCE) values('比亚迪','002594','A',172.78,'广东省');
insert into TABLE5 (STOCK, SYMBOL, SHARETYPE, PRICE, PROVINCE) values('长安B','200625','B',7.11,'重庆市');
insert into TABLE5 (STOCK, SYMBOL, SHARETYPE, PRICE, PROVINCE) values('东信B','900941','B',0.47,'浙江省');
select * from TABLE5;

select symbol from TABLE5;
select distinct sharetype from TABLE5;
select stock,symbol from TABLE5;
select*from TABLE5;
select*from TABLE5 where sharetype='A';
select stock from TABLE5 where sharetype='A';
select*from TABLE5 where symbol like '0%';
select*from TABLE5 where symbol like '%1';
select*from TABLE5 where symbol like '%9%';
select*from TABLE5 where symbol not like '%9%';
select*from TABLE5 where sharetype='A'AND PROVINCE='广东省';
select*from TABLE5 where price=7.11 or sharetype='B';
select*from TABLE5 where sharetype='B' and (province='广东省' or province='浙江省');
select*from TABLE5 where sharetype='A' and province='广东省';
select*from TABLE5 where price not between 0 and 10;
select*from TABLE5 order by sharetype;
select*from TABLE5 order by sharetype,price;
select*from TABLE5 order by price desc;
select*from TABLE5 order by sharetype asc,price desc;

CREATE table TABLE6(
    STKCD VARCHAR(10),  
    TRDDT DATE,  
    OPNPRC DECIMAL(10, 2),  
    CISPRC DECIMAL(10, 2),  
    DNSHRTRD BIGINT,  
    PRIMARY KEY (Stkcd, Trddt)  
);

insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603897','2019-01-02',23.01,22.81,2176373);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603898','2019-01-02',15.33,15.33,852951);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603899','2019-01-02',30.15,28.86,3435768);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603900','2019-01-02',11.88,11.82,629531);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603901','2019-01-02',6.59,6.62,968100);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603903','2019-01-02',20.84,20.15,1865700);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603906','2019-01-02',8.65,8.63,1106160);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603908','2019-01-02',24.38,24.51,963600);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603909','2019-01-02',25.52,25.7,1089096);
insert into TABLE6 (STKCD,TRDDT,OPNPRC,CISPRC,DNSHRTRD) values('603912','2019-01-02',13.32,13.7,4097856);
select * from TABLE6;
*/