MariaDB#10 : 데이터형, 형변환, 내장함수, 변수, 테이블, SQL(221229)7

데이터 형식

숫자형

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()함수에 넣어서 필드명으로 구역을 나눠줄 수 있다.