CREATE DATABASE test;
use test;
/*
# 建立TABLE1
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;
delete from TABLE1 where stock='平安银行';
select * from TABLE1;
delete from TABLE1;
select * from TABLE1;
*/
/*
#建立TABLE2
createtable TABLE2(
STOCK varchar(20),
SYMBOL varchar(20),
SHARETYPE varchar(10),
PRICE decimal(10,2),
PROVINCE VARCHAR(20)
);
INSERT INTO TABLE2 VALUES ('平安银行','200012','B',3.58,'广东省');
INSERT INTO TABLE2 VALUES ('长安B', '200625', 'B', 7.11,'重庆市');
#select* from TABLE2;
*/
/*
#J建立stock_info
createtable stock_info(
STOCK varchar(20),
SYMBOL varchar(20),
SHARETYPE varchar(10),
PRICE float,
PROVINCE VARCHAR(20)
);
insert into stock_info values('平安银行','000001','A',69.97,'广东省');
insert into stock_info values('长安B','200625','B',7.11,'重庆市');
insert into stock_info values('比亚迪','002594','A',172.78,'广东省');
insert into stock_info values('东信B','900941','B',0.47,'浙江省');
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 limit 2;
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 price between 0and10;
select * from stock_info where price not between 0and10;
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;
*/
/*
#建立TABLE4
createtable TABLE4(
STOCK varchar(20),
SYMBOL varchar(20),
SHARETYPE varchar(10),
PRICE float,
PROVINCE VARCHAR(20)
);
insert into TABLE4 values('平安银行','000001','A',69.97,'广东省');
insert into TABLE4 values('长安B','200625','B',7.11,'重庆市');
insert into TABLE4 values('比亚迪','002594','A',172.78,'广东省');
insert into TABLE4 values('东信B','900941','B',0.47,'浙江省');
insert into TABLE4 values('复星医药','600196','A',65.00,'上海市');
insert into TABLE4 values('ST星源','000005','A',1.82,'广东省');
insert into TABLE4 values('北京城乡','600861','A',14.38,'北京市');
insert into TABLE4 values('三环集团','300408','A',37.41,'广东省');
insert into TABLE4 values('万科A','000002','A',26.77,'广东省');
insert into TABLE4 values('南坡B','200012','B',3.58,'广东省');
select * from TABLE4;
select stock,symbol from TABLE4;
select STOCK,SYMBOL,PRICE from TABLE4 where price between 30and100
order by price desc;
*/
#建立TABLE5
createtable 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 limit 2;
#select stock,symbol from TABLE5;
#select symbol from TABLE5;
#select SHARETYPE from TABLE5;
#select distinct SHARETYPE from TABLE5;
#select * from TABLE5 where sharetype='A';
#select * from TABLE5 where price<50;
#select stock from TABLE5 where sharetype='A';
#select * from TABLE5 where symbol like '0%';
#select * from TABLE5 where sharetype='A'and province='广东省';
#select * from TABLE5 where price between 0and10;
#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;