编辑代码

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 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');

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 varchar(1),
    PRICE decimal(10,2),
    PROVINCE varchar(30)
);

INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('平安银行','200012','B',3.58,'广东省');
INSERT INTO TABLE2(STOCK,SYMBOL,SHARETYPE,PRICE,PROVINCE)VALUES('长安B','200625','B',7.11,'重庆市');
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,'广东省');
select * from TABLE2;

CREATE TABLE TABLE3
(
    Bankcd char(6),
    Shortbnm varchar(20),
    Stkcd char(6),
    Typrep varchar(10),
    Cash decimal(30)
);

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','贵阳银行','601009','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 Shortbnm='杭州银行';

DELETE FROM TABLE3 WHERE Typrep='D';
INSERT INTO TABLE3(Bankcd,Shortbnm,Stkcd,Typrep,Cash)VALUES('000014','招商银行','600036','B',599416000000);
select * from TABLE3
*/


/*
#J建立TABLE5
create table TABLE5(
  	STOCK varchar(20),
	SYMBOL varchar(20),
	SHARETYPE varchar(10),
	PRICE float,
    PROVINCE VARCHAR(20)
);
insert into TABLE5 values('平安银行','000001','A',69.97,'广东省');
insert into TABLE5  values('长安B','200625','B',7.11,'重庆市');
insert into TABLE5  values('比亚迪','002594','A',172.78,'广东省');
insert into TABLE5  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 limit 2;
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 price between 0 and 10;
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 TABLE5
(
  	STOCK varchar(20),
	SYMBOL varchar(20),
	SHARETYPE varchar(10),
	PRICE float,
    PROVINCE VARCHAR(20)
);
insert into TABLE5 values('平安银行','000001','A',69.97,'广东省');
insert into TABLE5  values('长安B','200625','B',7.11,'重庆市');
insert into TABLE5  values('比亚迪','002594','A',172.78,'广东省');
insert into TABLE5  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 limit 2;
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 price between 0 and 10;
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;