데이터 형식
숫자형
INT : 4byte짜리 정수 (INTEGER)
REAL : 실수형 (DOUBLE)
BIT(N)
TINYINT
SMALLINT
MEDIUMINT
BIGINT
FLOAT
DECIMAL(m, [d]) : NUMERIC(m, [d])
문자형
CHAR(n) : 고정적 (n : 1~255)
VARCHAR(n) : 가변적 (n : 1~65535)
- 영문은 1byte, 한글은 3byte
- 용량때문에, 되도록 VARCHAR(n)을 사용할 것.
BINARY(n)
VARBINARY(n)
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
문자데이터형
ENUM
SET : 최대 64개를 준비한 후에 입력은 그 중에서 2개씩 세트 데이터를 저장
날짜와 시간 데이터형
DATE : 3byte, 'YYYY-MM-DD'
TIMESTAMP : 되도록 이 형으로 사용하기. 외부에서 작업할때는 꼭!!, 'YYYY-MM-DD HH:MM:SS'
TIME
DATETIME
YEAR
기타 데이터형
GEOMETRY
JSON : 8byte, JavaScript Object Notation 문서 저장
변수
SQL에서도 변수 선언하고 사용 가능
스토어드 프로시저나 함수 안에서 변수 사용 -> DECLARE문 필요 (@변수명 x / 변수명만 사용)
SET @변수이름 = 변수값 ; -- 변수 선언 및 값 대입
SELECT @변수이름 ; -- 변수 값 출력
USE sqldb;
SET @var1 = 5;
SET @var2 = 3;
SET @var3 = 1.23;
SET @var4 = '스타 : ';
SELECT @var1 ;
SELECT @var2 + @var3 ;
SELECT @var4, userName FROM usertbl WHERE height < 180 ;
SQL에서도 변수를 선언해 사용하기 실습
USE sqldb;
SET @var1 = 2;
PREPARE myQuery
FROM 'SELECT userName, height FROM usertbl order by height LIMIT ?';
EXECUTE myQuery USING @var1;
LIMIT 문 사용시 변수 지정
- PREPARE myQuery : 밑에 FROM절 뒤를 myQuery안에 저장
- FROM 'SELECT .... FROM ... ... LIMIT ?'; : 나중에 EXCUTE절 만나면 호출될 절
- EXCUTE myQuery USING @myVar1; : LIMIT ?에 USING 뒤 변수를 ?에 대입하고, myQuery에 저장된 쿼리문 실행
형변환
CAST( 표현식 AS 데이터형식[(길이)] )
CONVERT( 표현식 , 데이터형식[(길이)] )
- 둘 다 비슷. 형변환해야할 때 사용 (예: 실수->정수로)
- 강제 형변환
- SELECT CAST(AVG(amount) AS SIGNED INTEGER) [AS ... ] FROM ...;
- SELECT CONVERT(AVG(amount) , SIGNED INTEGER) [AS ... ] FROM ...;
-- 다양한 구분자를 날짜 형식으로 변경
SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);
모두 같은 결과를 출력
CONCAT(...)
- 쿼리 결과를 가독성 좋게 정리
- 괄호 안에 콤마(,) 연결자로 나열
- ... CONCAT( CAST(price AS CHAR(10)), 'X', CAST(amount AS CHAR(4)), '=' ) ...
- 데이터베이스 언어에서는 문자열(숫자가 들어있는)을 +로 더하면 문자열 연결이 아닌 산술연산이 일어남
문자열 연결은 CONCAT('10', '20') ; 이런식으로 연결해야한다.
비교연산자에서 문자열이 들어가면 문자는 0으로 변환된다.
SELECT '10' + '20' ; -- 문자와 문자를 더함(산술연산)
-- 30 리턴 --
SELECT CONCAT('10', '20') ; -- 문자열 연결
-- 1020 리턴 --
SELECT CONCAT(10, '20') ; -- 정수와 문자를 연결
-- 1020 리턴 --
-- (비교연산자에서 문자는 0으로 변환)
SELECT 5 > '7mega'; -- 문자열이 정수 7로 변환된다.
-- 0 리턴 --
SELECT 5 > '3mega';
-- 1 리턴 --
SELECT 0 = 'string22' ;
-- 1 리턴 --
내장 함수 (Maria DB)
IF(수식, 참, 거짓)
- 삼항 연산자로 생각
- SELECT IF (10>20, '참', '거짓') ; -- 거짓 리턴 --
IFNULL(수식1, 수식2)
- 수식 1이 NULL 이 아니면 수식1 리턴
- 수식 1이 NULL이라면 수식2 반환
- SELECT IFNULL(NULL, '널'), IFNULL(10, '널'); -> 널, 10 출력
NULLIF(수식1, 수식2)
- 수식1과 수식2 같으면 NULL, 다르면 수식1 리턴
- SELECT NULLIF(10, 10), NULLIF(20, 10); -> (NULL)과 20 출력
CASE ~ WHEN ~ ELSE ~ END
- CASE는 연산자!
ASCII(아스키코드)
- 문자의 아스키코드 값 출력
- ASCII('A) -> 65출력
CHAR(숫자)
- 숫자의 아스키코드값에 해당하는 문자 출력
- CHAR(65) -> A 출력
BIT_LENGTH(문자열)
- 문자 개수의 bit수 출력(1byte = 8bit)
CHAR_LENGTH(문자열)
- 문자 개수 반환
LENGTH(문자열)
- 바이트 개수 반환 (영문 1byte, 한글 3byte)
SELECT BIT_LENGTH('BLOG'), CHAR_LENGTH('BLOG'), LENGTH('BLOG');
SELECT BIT_LENGTH('하히티'), CHAR_LENGTH('하히티'), LENGTH('하히티');
CONCAT_WS( 구분자, 문자열1, 문자열2, ... )
- 문자열을 이어주는데, 사이사이에 구분자를 넣어준다.
FORMAT(숫자, 소수점자리수)
- 1000단위마다 콤마(,)를 넣어주며, 소수점 밑으로는 소수점자리수까지 출력
- 규정은 소수점 두자리수까지
INSTR(대상되는_문자열, 찾을_문자)
- 대상되는문자열에서 찾을문자를 찾아 그 시작 위치 반환 (DB는 0부터가 아닌 1부터!!!)
LOCATE(찾을_문자, 대상되는_문자열)
- 대상되는문자열에서 찾을문자를 찾아 그 시작 위치 반환
- INSTR()과 매개변수 위치가 반대
BIN(숫자)
- 2진수 리턴
HEX(숫자)
- 16진수 리턴
OCT(숫자)
- 8진수 리턴
INSERT(기준_문자열, 위치, 길이, 삽입할_문자열)
- 함수임
- 기준 문자열의 위치부터 길이만큼을 삭제, 그리고 삽입할 문자를 그 위치에 넣음
LEFT(문자열, 길이)
- 왼쪽에서부터 문자열의 길이만큼 반환
RIGHT(문자열, 길이)
- 오른쪽에서부터 문자열의 길이만큼 반환
SELECT LEFT('HaheeY',2), RIGHT('HaheeY',4) ;
UPPER(문자열)
- 소문자를 대문자로
LOWER(문자열)
- 대문자를 소문자로
SELECT UPPER('HaheeY'), LOWER('HaheeY') ;
LPAD(문자열, 길이, 채울_문자열)
- 왼쪽으로 문자열을 길이만큼 늘리고, 빈곳을 채울_문자열로 채운다.
RPAD(문자열, 길이, 채울_문자열)
- 오른쪽으로 문자열을 길이만큼 늘리고, 빈곳을 채울_문자열로 채운다.
TRIM(문자열)
- 문자열의 앞뒤 공백 제거
- TRIM(방향 자를_문자열 FROM 문자 열)
LTRIM(문자열)
- 문자열의 왼쪽 공백 제거
RTRIM(문자열)
- 문자열의 오른쪽 공백 제거
REPEAT(문자열, 횟수)
- 문자열을 횟수만큼 반복
REPLACE(문자열, 원래문자열, 바꿀문자열)
- 문자열에서 원래문자열을 바꿀문자열로 바꿈
REVERSE(문자열)
- 문자열 순서 거꾸로
SPACE(길이)
- 길이만큼 공백 리턴
SUBSTRING(문자열, 시작위치, 길이)
- 시작위치부터 길이만큼 문자열 반환 (길이 생략이라면 문자열 하나 리턴)
- SUBSTRING(문자열 FROM 시작위치 FOR 길이)
SUBSTRING_INDEX(문자열, 구분자, 횟수)
- 문자열에서 구분사가 왼쪽부터 횟수번째 나오면 그 이후의 오른쪽문자들은 삭제. (횟수가 음수면 오른쪽부터 세고, 왼쪽 삭제)
ABS(숫자)
- 절댓값
ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(num1, num2), SIN(num), COS(num), TAN(num)
CEILING(숫자)
- 무조건 올림
FLOOR(숫자)
- 무조건 버림
ROUND(숫자)
- 반올림
CONV(숫자, 원래진수, 변환할진수)
DEGREES(num), RADIANS(num), PI()EXP(X), LN(num), LOG(num), LOG(밑수, num), LOG2(num), LOG10(num)
MOD(num1, num2)
- 매개변수를 숫자1 % 숫자2 또는 숫자1 MOD 숫자2로 표현 가능
- num1을 num2로 나눈 나머지값
POW(숫자1, 숫자2)
- 거듭제곱값
- POWER()와 동일
SQRT(숫자)
- 제곱근
RAND( )
- 0이상 1미만 실수 (0 <= x < 1)
- FLOOR(m + (RAND( ) * (n-m) ) : m<=임의의 정수<n
SIGN(숫자)
- 양수(1리턴), 0(0리턴), 음수(-1리턴)
TRUNCATE(숫자, 정수)
- 숫자를 소수점 기준 정수위치까지 구하고, 나머지 버린다. (반올림x)
- 소수점 위 자리는 0으로 채움
- 값손실이 있는 함수이니 유의해서 사용하기
ADDDATE(날짜, 차이)
SUBDATE(날짜, 차이)
- 날짜 +, -
- 차이는 INTERVAL 숫자 DAY / INTERVAL 숫자 MONTH 등과 같이 표현
ADDTIME(날짜/시간, 시간)
SUBTIME(날짜/시간, 시간)
- 날짜/시간 기준으로 시간 +, -
CURDATE()
- 현재 year-month-date
CURTIME()
- 현재 시:분:초
NOW(), SYSDATE()
- 현재 year-month-date 시:분:초
YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시 간), SECOND(시간), MICROSECOND(시간
DATE()
- DATETIME()에서 DATE()만
TIME()
- DATETIME()에서 TIME()만
DATEDIFF(날짜1, 날짜2)
- 두 날짜의 차이
- 자동배송완료에 사용
TIMEDIFF(날짜1또는시간1, 날짜1또 는시간2) • DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)
- 두 시간의 차이
- 이벤트 종료에 사용
DAYOFWEEK(날짜)
- 일 : 1 ~ 토 : 7
MONTHNAME()
- month으 ㅣ영문 리턴
DAYOFYEAR(날짜)
- 1년 중 몇번째 날인지 리턴 (1~366)
LAST_DAY(날짜)
- 그 달이 몇 일까지 있는지 리턴 (주로 이 상황에서 많이 사용)
MAKEDATE(연도, 정수)
- 연도에서 정수만큼 지난 날짜를 리턴
MAKETIME(시, 분, 초)
- '시:분:초'의 형식으로 리턴
PERIOD_ADD(연월, 개월수)
- 년, 월에서 개월수만큼 지난 년, 월을 리턴 (YYYYMM)
PERIOD_DIFF(연월1, 연월2)
- 연월1-연월2 차이 개월 수 리턴
QUARTER(날짜)
- 4분기 중 몇분기?
TIME_TO_SEC(시간)
- 시간을 초 단위로
USER()
- 현재 사용자
- = SESSION_USER( ) = CURRENT_USER( )
DATABASE()
- 현재 데이터베이스
- = SCHEMA( )
FOUND_ROWS()
- 직전의 SELECT문의 조회된 행의 갯수
ROW_COUNT()
- 직전의 INSERT, UPDATE, DELETE문에서 변경된 행의 갯수
- CREATE, DROP 은 0 반환
- SELECT문은 -1 리턴
VERSION()
- 현재 MariaDB 버전
SLEEP(s)
- 쿼리의 실행 중단(기준 : 초)
순위 함수
- 결과에 순번, 순위, 등수를 매기는 함수
- 비집계 함수 RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER()
USE ...;
SELECT ROW_NUMBER()
OVER(ORDER BY height DESC) '키큰순위'
, userName, height
FROM userTBL;
- ROW_NUMBER() 함수 : 키 큰 순.
실행순서 : OVER함수 필수!! OVER()함수가 제일 먼저 선처리. (키를 내림차순으로 정렬)
그리고나서 OVER()함수가 window함수인 ROW_NUMBER()함수를 실행
OVER()함수 : 매개변수로 받은 값을 활용하여 정렬시켜준다. 그리고 윈도우 함수 호출시켜주는 역할
PARTITION BY를 OVER()함수에 넣어서 필드명으로 구역을 나눠줄 수 있다.
'MariaDB' 카테고리의 다른 글
MariaDB#15 : 뷰View (221229)8 (0) | 2022.12.29 |
---|---|
MariaDB#11 : 조인 join(내부, 외부), 스토어드 프로시저 (221229)7 (0) | 2022.12.29 |
MariaDB#9 : SQL - DDL, DML, DCL(221228)6 (0) | 2022.12.28 |
MariaDB#8 : HeidiSQL 살펴보기, 주석(221228)5 (0) | 2022.12.28 |
MariaDB#7 : 모델링 (221228)4 (0) | 2022.12.28 |
댓글