编辑代码

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('T星源','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(200)
 );
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(20),
     shortbnm char(4),
     stkcd char(6),
     typrep char(1),
     cash varchar(12)
 );
 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', '贵阳银行', '601997', '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
 */
 /*
 create table table4
 (
     stock varchar(20),
     symbol char(6),
     sharetype char(1),
     price decimal(10,2),
     province varchar(200)
 );
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 table6
 (
     stkcd varchar(6),
     trddt ,
     opnprc char(10,2),
     clsprc decimal(10,2),
     dnshrtrd varchar(7)
 );