mysql고급문법

숫자 데이터 형식

숫자형 데이터 형식은 정수, 실수 등의 숫자를 표현한다.

 

데이터 형식	바이트 수	숫자 범위	설명
BIT(N)	N/B		1~64bit를 표현,b10000 형식으로 표현
TINYINT	1	-128 ~ 127	정수
★SMALLINT	2	-32,768 ~ 32,767	정수
MEDIUMINT	3	-8,388,608~8,388,607	정수
★INT INTEGER	4	약 –21억 ~ +21억	정수
★BIGINT	8	약 –900경~ +900경	정수
★FLOAT	4	-3.4E+38~1.17E-38	소수점 아래 7자리까지 표현
★DOUBLE REAL	8	-1.22E-308~1.79E+308	소수점 아래 15자리까지 표현
★DECIMAL(m,(d)) NUMERIC(m,(d))	5~17	-10 +1~+10 -1

 

UNSIGNED 예약어

TINYINT 0~255, SMALLINT 0~65535, MEDIUMINT 0~16777215, INT 0~약42억, BIGINT 0~약 1800경으로 표현할 수 있다.

 

데이터 형식	바이트 수	설명
CHAR	1~255	고정길이 문자열(EX 전화번호)
VARCHAR	1~65535	가변길이 문자열(EX 주소)
BINARY(n)	1~255	고정길이의 이진 데이터 값
VARBINARY(n)	1~255	가변길이의 이진 데이터 값
TEXT 형식(LONGTEXT)	1~4294967295	최대 4GB의 크기의 TEXT 데이터 값
BLOB (LONGBLOB)	1~4294967295	최대 4GB 크기의 BLOB 데이터 값
ENUM(값들...)	1 또는 2	최대 65535개의 열거형 데이터 값
SET(값들..)	1,2,3,4,8	최대 64개의 서로 다른 데이터 값

VARCAHR형은 데이터를 넣었을 때의 크기로 변한다. 데이터 접근이 CHAR형보다 느리다 형식이 일정한 전화번호 같은 것은 CHAR형으로 주소같이 길이가 각자 다른것들은 VARCHAR형으로 한다.

 

 

 

 

데이터 형식	바이트 수	설명
★DATE	3	날짜는 1001-01-01~9999-12-31 까지 저장되며 날짜 형식만 사용 ‘YYYY-MM-DD’형식으로 사용됨
TIME	3	-838:59:59.000000~838:59:59.000000까지 저장되며 ‘HH:MM:SS’형식으로 사용
★DATETIME	8	날짜는 1001-01-01 00:00:00 ~9999-12-31 23:59:59 까지 저장되며 날짜 형식만 사용 ‘YYYY-MM-DD HH:MM:SS’형식으로 사용됨
TIMESTAMP	4	날짜는 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59 까지 저장되며 형식은 ‘YYYY-MM-DD HH:MM:SS’ 형식으로 사용, time_zone 시스템 변수와 관련이 있으며 UTC 시간대로 변환하여 저장
YEAR	1	1901~2155까지 저장, ‘YYYY’형식으로 사용

 

SELECT CAST(‘2020-10-19 12:35;29.123’ AS DATE) AS ‘DATE’;

SELECT CAST(‘2020-10-19 12:35;29.123’ AS TIME) AS ‘TIME’;

SELECT CAST(‘2020-10-19 12:35;29.123’ AS DATETIME) AS ‘DATETIME’;

 

기타 데이터 형식

데이터 형식	바이트 수	설명
GEOMETRY	N/A	공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작
JSON	8	JSON(JavaScript Object Notation) 문서를 저장

 

LONGTEXT(ex 영화대본), LONGBLOB(ex 영화 동영상)

LOB(Large Object) -4GB 까지 저장가능

 

 

변수의 사용

SET @변수이름 = 변수의 값; -- 변수의 선언 및 값 대입

SELECT @변수이름; -- 변수의 값 출력

 

USE sqlDB;

SET @myVar1 = 5;

SET @myVar2 = 3;

SET @myVar3 = 4.25;

SET @myVar4 = ‘가수 이름 ==>’;

 

SELECT @myVar1;

SELECT @myVar2 + @myVar3;

SELECT @myVar4, Name FROM userTbl WHERE height > 100;

@myVar4 Name

가수이름==> 임재범

 

Limit를 사용 할 수가 없음

PREPARE은 사용하면 가능

SET @myVar1 = 3;

PREPARE myQuery

From ‘SELECT Name, height FROM userTbl ORDER BY height LIMIT ?’;

EXECUTE myQuery USING @myVar1;

 

데이터 형식 변환 함수

CAST(), CONVERT() 함수가 존재한다. 왜 사용할까?

 

USE sqlDB;

SELECt AVG(amount) AS ‘평균 구매 개수’ FROM buyTbl;

SELECT CAST(AVG(amount) AS SIGNED INTEGER)

AS ‘평균 구매 개수’ FROM buyTbl;

SELECT CONVERT(AVG(amount), SIGNED INTEGER)

AS ‘평균 구매 개수’ FROM buyTbl;

 

SELECT CAST(‘2020$12$12’ AS DATE);

SELECT CAST(‘2020/12/12’ AS DATE);

SELECT CAST(‘2020%12%12’ AS DATE);

SELECT CAST(‘2020@12@12’ AS DATE);

 

 

SELECT num,

CONCAT(CAST(price AS CHAR(10)), ‘x’, CAST(amount AS CHAR(4)),‘=’)

price*amount AS ‘구매액’

FROM buyTbl;

 

CONCAT은 문자를 연결해 주는 것.

 

암시적 변환

 

SELECT ‘100’ + ‘200’ ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)

SELECT CONCAT(‘100’, ‘200’); -- 문자와 문자를 연결 (문자로 처리)

SELECT CONCAT(100, ‘200’); -- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)

SELECT 1 > ‘2mege’; -- 정수인 2로 변환되어서 비교 0출력(FALSE)

SELECT 3 > ‘2MEGE’; -- 정수인 2로 변환되어서 비교 1출력(TRUE)

SELECT 0 = ‘MEGA2’; -- 문자는 0으로 변환됨 1출력(TRUE)

 

MySQL의 내장함수

 

SELECT IF (100>200, ‘참이다’, ‘거짓이다’); -- 거짓이다 출력

SELECT IFNULL(NULL, ‘널이군요’), IFNULL(100, ‘널이군요’) -- 널이군요 100 출력

SELECT NULLIF(100, 100), IFNULL(200,100) --같으면 NULL이 반환되고

--다르면 200이 출력

 

CASE 문

SELECT CASE 10

WHEN 1 THEN ‘일’

WHEN 5 THEN ‘오’

WHEN 10 THEN ‘십’

ELSE ‘모름’

END;

CASE 가 1일 경우 일 출력 10일 경우 십 출력

 

SELECT ASCII(‘A’), CHAR(65); -- 숫자 65출력 BLOB면 오른쪽클릭해서 확인하면 41(16진수)나옴

 

SELECT BIT_LENGTH(‘abc’), CHAR_LENGTH(‘abc’), LENGTH(‘abc’);

-- 24, 3, 3 출력 8비트 * 3이라 24 비트 출력 한글자당 1바이트

SELECT BIT_LENGTH(‘가나다’), CHAR_LENGTH(‘가나다’), LENGTH(‘가나다’);

-- 72, 3, 9 출력 (8비트 * 9 = 72) UTF – 8 은 한글자당 3바이트

 

 

SELECT CONCAT_WS(‘/’,‘2020’,‘01’,‘01’); --출력: 2020/01/01

CONCAT_WS는 처음 나온 구분자로 뒤에있는 친구들을 문장으로 합쳐준다.

 

SELECT ELT(2, ‘하나’, ‘둘’, ‘셋’), FIELD(‘둘’, ‘하나’, ‘둘’, ‘셋’),

FIND_IN_SET(‘둘’, ‘하나,둘,셋’), INSET(‘하나둘셋’,‘둘’), LOCATE(‘둘’, ‘하나둘셋’);

 

-- ELT는 2는 인덱스같은 개념으로 2번째 있는 값을 출력하라는 의미

-- FIELD는 ‘둘‘이라는 문자가 몇 번째에 있는지 출력

-- FIND_IN_SET ‘문장,문장,문장‘에서 둘이라는 글자가 몇 번째에 있는 지 출력

-- INSET ‘하나둘셋‘ 이라는 글자에서 둘이라는 글자가 몇 번 째에 있는지 출력

-- LOCATE 둘이라는 글자가 하나둘셋이라는 글자에 몇 번째 인지 출력

-- 출력값 둘 2 2 3 3

 

SELECT FORMAT(123456.123456, 4);

-- 소숫점 4자리까지 출력하라는 의미 123456.1235 출력

 

SELECT BIN(31), HEX(31), OCT(31); --이진수로 출력 16진수로 출력 8진수로 출력

SELECT INSERT(‘abcdefghi’, 3, 4, ‘@@@@’), INSERT(‘abcdefghi’, 3, 2, ‘@@@@’);

--abcdefghi에서 3번째 글짜부터 4번째 글짜까지를 @@@@를 넣어라 출력: ab@@@@ghi

--abcdefghi에서 3번째 글자부터 2번째 글자까지를 @@@@로 넣어라 출력: ab@@@@efghi

 

SELECT LEFT(‘abcdefghi’, 3), RIGHT(‘abcdefghi’, 3);

-- 왼쪽에 3글자 출력 오른쪽에 3글자 출력 abc, ghi 나옴

 

SELECT LCASE(‘abcdEFGH’),UCASE(‘abcdEFGH’);

SELECT lower(‘abcdEFGH’), UPPER(‘abcdEFGH’);

-- 둘다 같은거임 L은 소문자로 출력 U는 대문자로 출력

 

SELECT LPAD(‘이것이’, 5, ‘##’), RPAD(‘이것이’,5,‘##’);

-- 총 5글자로 하고 빈부분에## 왼쪽에 채워라 ##이것이

-- 총 5글자로 하고 빈부분에## 오른쪽에 채워라 이것이##