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

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 스크립트를 잘 저장해 두지않아 복구하는데 힘들었다. 저장을 잘하는 습관을 들여야겠다.