编辑代码

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
create table 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
create table 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 0 and 10;
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;
*/
/*
#建立TABLE4
create table 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 30 and 100
order by price desc;
*/
#建立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 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 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;