주식 데이터로 sql 연습 (section 1)

sql에 익숙해지기 위해 주가데이터를 mysql에서 연습 

데이터 형태 

컬렴 : 티커, 날짜 , 시가, 고가, 저가, 종가,거래대금, 거래량, 등락율, 회사명 

예시

'000020','20190102','9190','9430','9080','9340','88403','819092510','2.64','동화약품'
'000020','20190103','9340','9390','9130','9170','54992','505672260','-1.82','동화약품'
'000020','20190104','9100','9700','9100','9530','115336','1084780820','3.93','동화약품'
'000020','20190107','9660','9800','9510','9530','107912','1045428960','0','동화약품'
'000020','20190108','9610','9640','9320','9520','108681','1034274500','-0.1','동화약품'
'000020','20190109','9520','9620','9380','9450','82564','780664990','-0.74','동화약품'
'000020','20190110','9430','9530','9350','9450','53611','505170030','0','동화약품'

SELECT * FROM stock.price;


-- 산술  
SELECT ticker,day1,high,low,high/low as "고가/저가" FROM stock.price;



# splice 문자열 구간 나누기 
SELECT substr(name1,1,3) FROM stock.price;

#문자열 길이 
SELECT length(name1) FROM stock.price;

#replace 
SELECT replace("삼성전자","삼","일") FROM stock.price;

#varchar로 저장된 날짜를 date 타입으로 변환 
select date_format(day1,"%Y/%m/%d")  from stock.price;

#date에서 year month day 만 가져오기 
select extract(YEAR from date_format(day1,"%Y/%m/%d")) as year  from stock.price;

#명시적 형변환 cast(열 as 타입) 이나 conver(열,타입) 
select cast( high as char) from stock.price;

#null이면 2번째 인자로 채워줌 
select isnull(name1,"이름없음") from stock.price;

#2번째 인자와 같으면 null로 출력 
select nullif(high,"0") from stock.price;

#case 

select ticker, 
	case  when day1 = "20191120" then "1"
		  when day1 = "20191121" then "2"
		  when day1 = "20191122" then "3"
          else "0"
	end as "ddday"
  from stock.price;


# in 
SELECT * FROM stock.price
where ticker in ("004920","000530");

#between 
SELECT * FROM stock.price
where rate between 8 and 10 ;

#like 
SELECT * FROM stock.price
where name1 like "삼성%";

#and 
SELECT * FROM stock.price
where name1 like "삼성%" and rate between 8 and 10 ;