신세게 - Java 공부

8주차 배운점 느낀점 - MysQL 데이터 형식, 문자열 함수, 파일 입출력

휘로그 2024. 10. 7. 20:49
728x90
반응형

8주차 시작

 

mySQL의 데이터 형식을 배웠다. 숫자 문자, 날짜, JSON 형식 등이 있다.
SQL에도 변수가 있다. 변수를 이용해서 쿼리를 만들고 원하는 데이터를 ?에 넣을 수 있다.
문자열을 다루는 함수들을 배웠다. 문자를 찾거나 교체하거나 앞 뒤 공백을 제거한다.
무조건 CHAR에는 VARCHAR만 있는게아니다. 메모리를 위해 VARCHAR에만 넣지말라고 하셨다.
잘 찾아서 써야한다.

 

배운점

 

MySQL 데이터 형식

 

1) 숫자 (정수, 실수 등의 숫자를 표현)
SMALLINT (2)
INT(INTEGER) (4)
BIGINT (8)
FLOAT (4) 소수점 아래 7자리까지 표현
DOUBLE (8) 15자리까지 표현
DECIMAL(m,d) m: 전체자릿수 d: 소숫점이하 자릿수

 

2) 문자
CHAR(n), CHAR 만쓰면 (1), CHARACTER: 고정길이
VARCHAR (1~ 65535) : 가변길이

 

TEXT 형식
TINYTEXT 1~255 : TEXT 데이터 값
TEXT 1~165535
MIDIUMTEXT
LONGTEXT 4G까지

 

BLOB 형식 - 사진, 동영상 파일, 대용량의 글자를 저장하기 위한 데이터타입

ENUM() 열거형 데이터 값
SET

 

3) 날짜/시간
DATE(3) YYYY-MM-DD 형식
DATETIME(8) YYYY-MM-DD HH:MM:SS

 

4) 지도/JSON 데이터 형식
GEOMETRY : 공간데이터 형식으로 선, 점, 다각형 같은 공간개체를 저장, 조작
JSON(JavaScript Object Notation)
{id: 'jack' , name = 'jack kim', age : 20 }
테이블 없음 이렇게 저장함

 

SQL 변수 사용

 

SET @변수이름 = 값; -- 변수의 선언 및 값 대입
SELECT @변수이름
select 는 print의미

 

set @myvar1 = 5;
set @myvar2 = 3;
set @myvar3 = 4.2;
set @myvar4 = '가수이름==>';

select @myvar1 + @myvar2;
select @myvar4, Name from usertbl where height>180;

 

 

 

 


PREPARE


쿼리 준비시키기 , 필요할때 ? 에 변수 넣어서 사용

 

set @myvar1 = 3;
prepare myQuery
from 'select name, height from usertbl order by height limit ?';
execute myQuery using @myVar1;

 

 


insert into 에 사용

prepare prpared_stmt from 'insert into employee values(?,?,?)';
set @name = 'ruby';
set @sal = 9000;
set @loc = 'jeju';
execute prpared_stmt using @name, @sal, @loc;
select * from employee;

 

쿼리를 변수로 사용


변수에 concat 사용

 

create table student(name varchar(30), age int , score int);
insert into student values('jimi',22,8);
set @table = 'student'; 
set @statment = concat('select * from ',@table); // from student

prepare pre_student from @statment; // from에 쿼리문 대입
execute pre_student;

 

제어 흐름 함수: 프로그램의 흐름 제어

 

IF(수식, 참, 거짓)

SELECT IF(100>200,'참','거짓');

 

IFNULL(수식1, 수식2)
수식1이 NULL이 아니면 수식 1이 반환, 수식 1이 NULL이면 수식 2가 반환
이거 많이 사용함!

select IFNULL(null,'null이구나!'),IFNULL(100,'NULL이 아니네');

 

NULLIF(수식1, 수식2)
수식1과 수식2가 같으면 NULL 반환한다.. 다르면 수식1 반환

select NULLIF(100,100), NULLIF(200,100);

 


CASE ~WHEN ~ ELSE ~ END
CASE 연산자 다중분기에서 사용되는 함수와 함께 사용한다.

SELECT CASE 11
WHEN 1 THEN '일'
WHEN 5 THEN '오'
WHEN 10 THEN '십'
ELSE '에라 모르겠다'
END AS 'CASE 연습';

 

문자열 ASCII(아스키코드), CHAR(숫자)
아스키문자 -> 숫자, 숫자 -> 문자

SELECT ASCII('A'),CHAR(65);

 


bIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)
비트 길이, 문자길이, 바이트 길이
영어는 한개에 1바이트, 한글은 한개에 3바이트

SELECT bit_length('abc'), char_length('abc'),length('abc');
SELECT bit_length('가나다'), char_length('가나다'),length('가나다');

 

 


CONCAT(문자열1, 문자열2, ....), CONCAT_WS(구분자,문자열1,문자열2)
WS 사용하면 / 구분으로 concat됨

 

SELECT concat_ws('/','2024','02','21');

 


ELT(위치, 문자열1, 문자열2, ...)
원하는자리꺼 반환 시킬 수 있다. 이것도 많이 씀

SELECT ELT(2,'하나','둘','셋');

 

FIELD('찾을 문자열',문자열1,문자열2)
인덱스 리턴

SELECT field('둘','하나','셋','둘');

 


FIND_IN_SET(찾을 문자열, 문자열 리스트)

SELECT find_in_set('둘','둘');

 


INSTR
'세번째 인덱스에서 원하는 이 문자열이 시작된다'

SELECT INSTR('하나둘셋','둘');

 


LOCATE
INSTR과 리턴값 같다. 파라미터 순서만 다름, 둘 중에 하나만 기억하셈

SELECT LOCATE('둘','하나둘셋');

 

FORMAT (숫자, 소수점 자릿수)
4째자리까지 반올림

SELECT FORMAT(12345.1234567,4);

 


BIN(숫자), HEX(숫자), OCT(숫자) 2진수, 16진수 8진수

SELECT BIN(30), HEX(30), OCT(30);

 

INSERT()
3부터 4개 지우고, 이 문자를 넣겠다.

SELECT INSERT ('abcdefghi',3,4,'!!!!');

 


LEFT(), RIGHT()
왼쪽, 오른쪽 문자열의 길이만큼 반환

SELECT LEFT('abcdefghi',3);

 


UPPER(문자열), LOWER(문자열)
: 대소문자 변환

 

LPAD, RPAD

SELECT LPAD('자바 백엔드 개발자',15,'##');

 

LTRIM, RTRIM, TRIM() : 공백 제거

SELECT LTRIM('    SQL 프로그래밍'),RTRIM('SQL 프로그래밍    ');

 


앞뒤에 있는 해당 문자를 다 잘라내라, BOTH : 양쪽, LEADING: 앞, TAIL: 뒤

SELECT TRIM(' 안녕  '),TRIM(BOTH '^' FROM '^^ 재미있네요.^^');

 


REPEAT(문자열, 횟수)

SELECT REPEAT ('SQL', 10);

 


REPLACE(문자열, 원래 문자열, 바꿀문자열);

SELECT REPLACE('MySQL 문법 배우기','문법','저장 프로그램');

 

my.ini 열기
메모장 notepad my.ini 로 열어서 max_allowed_packet 이랑 secure-file-priv 수정

#The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
#mysql_stmt_send_long_data() C API function.
max_allowed_packet=1024M

#Secure File Priv.
secure-file-priv="C:\datasource\movies"

 

 

 


서버 껐다킴

insert into movietbl values (1,'쉰들러 리스트','스티븐 스필버그','리암 니슨',
	load_file('C:/Movies_add/schindler.TXT'),load_file('C:/Movies_add/schindler.mp4'));
insert into movietbl values(2,'쇼생크 탈출','프랭크 다라본트', ' 팀 로빈슨',
load_file('C:/Movies_add/shawshank.txt'), load_file('C:/Movies_add/shawshank.mp4'));
insert into movietbl values(3,'라스트 모히칸','마이클 만', '다니엘 데이 루이스',
load_file('C:/Movies_add/mohican.txt'), load_file('C:/Movies_add/mohican.mp4'));

 

TEXT 파일로 내려받기

SELECT m_script from movietbl where m_id =1
into outfile 'C:/Movies_add/schindler_out.txt'
lines terminated by '\\n';

 

바이너리 파일로 영화클립 파일 다운로드

select m_film from movietbl where m_id = 3
into dumpfile 'C:/Movies_add/mohican_out.mp4';

 

피벗 (pivot)
열에 포함된 여러 값을 출력하고, 여러 열로 변환하여 테이블 반환 식을 회전하고 필요하면 집계까지 수행하는 작업

create table pivotTest(
name char(3),
season char(2),
amount int);

insert into pivotTest values ('ss1','여름',10);
insert into pivotTest values ('ss2','가을',10);
insert into pivotTest values ('ss3','겨울',10);
insert into pivotTest values ('ss4','봄',10);

select * from pivotTest;

select name,
sum(if(season='봄',amount,0)) as '봄',
sum(if(season='여름',amount,0)) as '여름',
sum(if(season='가을',amount,0)) as '가을',
sum(if(season='겨울',amount,0)) as '겨울',
sum(amount) as '합계' from pivotTest group by name;

 


JSON 데이터
웹과 모바일 응용 프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷이다. ( 속성 : 값 ) 독립적인 데이터 포멧이다.

select json_object('name',name,'height',height) as 'JSON 값' FROM usertbl where height >= 180;

 


json 변수 set 후 SELECT

set @json = '{
				"usertbl": [{"name":"임재범","height":182},{"name":"이승기","height":182},{"name":"성시경","height":182}]
    }';
    
select json_valid(@json) as json_valid;
select json_search(@json,'one','성시경') as json_serach;
select json_extract(@json,'$.usertbl[2].name') as json_extract;
select json_insert(@json,'$.usertbl[0].mDate','2024-01-21') as json_insert;-- 컬럼 추가
select json_replace(@json,'$.usertbl[0].name','신세계') as json_replace;
select json_remove(@json,'$.usertbl[0]') as json_remove;

 

json_valid

 

search
one 과 all 사용가능 , one은 저게 나오는 첫번째

 

칼럼추가

 

replace

 


[0] 삭제

 

 

회고

 

SQL에서 문자열을 다루고 , 자바에 있는 CASE 문 비슷한걸 배웠다. if문같은건 데이터를 뽑아오고 자바에서 처리해주면 되지 않을까 생각했지만 데이터를 잘 정제해야 속도면에서도 빠를 것이고 신뢰도를 높일 수 있다.
잘 외워두어야할 것 같다. 생각보다 많다.

 

txt 와 영상파일을 가져오고 파일쓰는 작업을 했다. 과정에서 ini 설정파일을 변경해서 패킷의 최대 용량을 1GB로 바꿔주고 secure_file_priv옵션으로 파일을 읽고 쓸 경로를 적어줬다.
이 과정을 거치니 여기저기서 워크밴치가 멈추는 에러가 발생했다. 원인은 정확히 모르지만 서버와 connect가 잘 안된듯싶다. 난 다시 제거하고 설치했다. sql 스크립트를 잘 저장해 두지않아 복구하는데 힘들었다. 저장을 잘하는 습관을 들여야겠다.

반응형
저작자표시