시스템 설정

SQL 모드

MySql 서버에는 sql_mode 라는 시스템 설정이 있다.
이 설정을 통해 SQL의 작성, 결과 등에 영향을 주게 된다.

STRICT_ALL_TABLES : 저장하려는 값의 길이가 컬럼의 길이보다 길 경우 오류 발생시킴. 원래는 초과된 부분만큼 자르고 저장하였음.
STRICT_TRANS_TABLE : 컬럼의 타입과 값의 타입이 일치하지 않을 경우 오류 발생시킴. 원래는 비슷한 타입으로 최대한 변환시켜서 저장했음.
ANSI_QUOTES : 문자열 표현에 오직 싱글쿼터만 사용가능. 더블쿼터는 컬럼명이나 테이블명만 사용 가능. 원래는 문자열에 싱글/더블 쿼터 모두 사용 가능했음.
ONLY_FULL_GROUP_BY : GROUP BY 절에 포함되지 않은 컬럼을 출력하거나 HAVING에 사용할 수 없음. 원래는 가능했음… MySQL의 이상했던 점.
PIPE_AS_CONCAT : 오라클 처럼 || 문자로 문자열 연결 가능. 원래는 불가능.

등등…

적용하는법은 my.cnf 파일에

1	sql_mode=STRICT_TRANS_TABLES,ANSI

의 형태로 작성해주면 된다. ,로 구분하여 여러개 지정할 수 있다.

sql_mode에서 알 수 있는 MySQL의 특징은?

영문 대소문자

MySql은 설치된 운영체제에 따라 테이블의 대소문자를 구분한다.
ex) 윈도우 : 구분X, 리눅스 : 구분O
DB나 테이블이 디스크의 디렉터리나 파일로 매핑되기 때문이다.
가능하면 대문자만 또는 소문자만으로 통일하는 것이 좋다.

예약어

일반적으로 테이블이나 컬럼 생성 시 예약어를 이름으로 사용하면 에러가 발생한다.
그러나 역따옴표(`)나 쌍따옴표(")로 감싸면 이를 피할 수 있다.
예를 들면 테이블 생성 시 역따옴표(`)로 둘러싸면 에러를 발생시키지 않는다.
하지만 역따옴표로 둘러싸서 생성하는 것을 권장하지는 않는다.

메뉴얼 읽는 법

대문자는 키워드를 의미한다.

이텔릭체는 사용자가 작성하는 이름 또는 표현식을 의미한다.

대괄호( [] )는 선택사항임을 의미한다.

파이프( | )는 연결된 것 중 단 하나만 선택할 수 있음을 의미한다.

중괄호( {} )는 괄호 내 선택사항 중 반드시 하나를 선택해야 함을 의미한다.

…는 앞에 명시된 키워드나 표현이 반복될 수 있음을 의미한다.

MySQL 연산자, 내장함수

리터럴

문자열

항상 홑따옴표(’)를 사용해서 표시한다.
홑따옴표 자체를 사용하고 싶을 때는 홑따옴표를 두번 연속 입력하면 된다.
쌍따옴표와 홑따옴표의 조합으로 위의 행위를 할 수 있지만 MySql만 지원되는 방식이다.

숫자

다른 시스템들과 동일하게 ’ 나 " 없이 숫자 값을 입력하면 된다.

※ 자동 형 변환
MySql에서는 문자와 숫자를 비교할 때 자동으로 숫자값으로 형 변환해준다. (숫자 > 문자)

1	WHERE number_col = '10001';

와 같은 경우는 문제가 되지 않지만,

1	WHERE string_col = 10001;

의 경우에는 문제가 발생할 수 있다.
위와 같은 경우 오른쪽 숫자가 우선순위가 더 높으므로
왼쪽의 string_col이 숫자로 형 변환하는 상황이 발생하는데,
이로 인해 string_col은 인덱스를 사용하지 못하고,
만약 문자값이 포함된 데이터가 있다면 SQL 실행 오류로도 이어진다.

날짜

MySql에서 정해진 형태의 포멧으로 날짜를 입력하면(문자열로),
MySql에서 자동으로 DATE나 DATETIME으로 변환하여 준다.
위의 숫자 리터럴과 동일하다. (날짜 > 문자)

불리언

BOOLEAN이란 타입이 있긴 하나 실상 TINYINT에 대한 동의어일 뿐이다.
(테이블 컬럼 타입을 BOOLEAN으로 생성한 뒤 확인해보면 TINYINT 이다.)
BOOLEAN의 TRUE는 1을 의미하고, FALSE는 0을 의미한다.
0과 1만을 사용한다는 점을 주의해야 한다!!

연산자

동등 비교

=, <=>
기본적으로 NULL과의 연산은 결과값이 모두 NULL이다.

1	SELECT NULL=1 FROM DUAL;

> NULL

그래서 NULL의 경우 IS NULL등의 연산자로 비교해야 하는데, <=> 의 경우에는 NULL 또한 하나의 값으로 인식하여 계산한다.

1	SELECT NULL<=>NULL FROM DUAL;

> 1

부정 비교

<>, !=
둘중 어느것을 사용하든 상관없지만 통일하는 것이 좋다.

NOT 연산자

결과를 반대로 만든다. 부정의 결과를 정확히 예측할 수 없는 경우 사용하지 말자.
ex) !‘test’

AND, OR 연산자

&&, ||로 대체할 수 있으나 혼란을 야기하니 사용하지 말자.

나누기, 나머지 연산자

SELECT 29/9 FROM DUAL;
-- 3.222.. // 몫 + 나머지
SELECT 29 DIV 9 FROM DUAL;
-- 3 // 몫
SELECT 29 MOD 9 FROM DUAL;
-- 2 // 나머지

LIKE 연산자

상수 문자열이 있는지 없는지 정도만을 판단한다.
와일드 카드는 딱 2개만 사용가능하다.

% : 0개 또는 1개 이상 일치
_ : 정확히 1개 일치

와일드카드 문자가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔을 사용할 수 있다!
와일드카드가 앞쪽에 올 경우 인덱스 풀 스캔 또는 테이블 풀 스캔을 하게 된다.(B-Tree 인덱스의 특징 때문)

와일드카드 문자를 검색에 사용하고 싶을 경우 ESCAPE 구문을 사용한다.

1	SELECT 'a%' LIKE 'a/%' ESCAPE '/' FROM DUAL;

BETWEEN 연산자

>= 연산자와 <= 연산자를 합친 기능을 수행한다.
BETWEEN을 위의 연산자로 풀어도 성능상 차이가 없으니 크게 고려하지 않아도 된다.

IN 연산자

동등비교를 여러번 하는 연산자이다. 즉 일반적으로 빠르게 처리된다.
연산자의 입력이 상수가 아니라 서브쿼리일 경우 상당히 느려질 수 있으니 주의해야 한다.
NOT IN의 경우 부정형 비교여서 인덱스 풀 스캔을 사용한다.

※ BETWEEN 연산자와 IN 연산자 차이점
BETWEEN의 경우 범위만큼 인덱스를 다 읽어야 하는 반면,
IN의 경우 위에서 언급했듯이 동등 연산자를 여러번 수행하는 것과 같다.
실행계획을 비교해보면 인덱스를 타고 있지만 rows에서 차이가 나는것을 확인할 수 있다.

내장함수

NULL 값 비교

IFNULL : 값이 NULL인지 비교하고 NULL이면 다른 값으로 대체한다.
첫번째 인자는 NULL인지 비교할 값, 두번째 인자는 NULL일시 대체값이다.
첫번째 인자가 NULL이 아닐경우 그대로 출력된다.
ISNULL : 값이 NULL인지 아닌지 비교. NULL일 경우 TRUE(1), 아닐경우 FALSE(0)

현재 시각 조회

NOW : 시간 조회. 하나의 SQL내면 모두 같은 값을 가진다.
SYSDATE : 시간 조회. 하나의 SQL내에서도 호출 시점에 따라 결과값이 달라진다.

SELECT NOW(), SLEEP(1), NOW() FROM DUAL;
-- '2017-12-19 22:39:52' | '0' | '2017-12-19 22:39:52'
SELECT SYSDATE(), SLEEP(1), SYSDATE() FROM DUAL;
-- '2017-12-19 22:40:27' | '0' | '2017-12-19 22:40:28'

보다시피 SYSDATE의 경우 SLEEP(1)로 인해 1초가 차이난다.
SYSDATE의 경우 호출때마다 다른 값을 반환하므로 상수가 아니다.
그래서 인덱스를 효율적으로 사용하지 못한다. (인덱스라기 보단 캐싱을 못한다는게 더 맞지 않을까?)
이러한 문제점이 있으니 SYSDATE는 가급적 사용하지 않는것이 좋다.
CURRENT_TIMESTAMP, CURRNET_DATE, CURRENT_TIME은 NOW와 동일한데다 출력을 다양하게 활용할 수 있으니 이것을 사용해도 좋을 것 같다.

날짜 <> 문자

지정 문자	내용
%Y	4자리 연도
%y	2자리 연도
%m	월
%d	일자
%H	24h 시간(00 ~ 23)
%h	12h 시간(01 ~ 12)
%i	분
%s	초

DATE_FORMAT : 날짜를 문자열로 변환한다.
ex) DATE_FORMAT(NOW(), %Y-%m-%d)
> 2017-12-19
STR_TO_DATE : 문자를 날짜로 변환한다. 여기서 주는 포멧은 기존 문자열의 포멧을 알려주는 것이다.
ex) STR_TO_DATE(‘1992⁰²07^’,’%Y%m^%d’)
> 1992-02-07
참고로 SQL 표준형태로 입력된 문자열은 STR_TO_DATE를 사용하지 않아도 자동으로 DATETIME으로 변환되어 처리된다.

날짜 연산

DATE_ADD : 첫번째 인자로 날짜, 두번째 인자로 더할 날짜를 주면 된다.
두번째 인자는 INTERVAL n {KEYWORD} 의 형태로 줘야 한다.
DATE_SUB 함수도 있으나 두번째 인자로 음수값을 줄 수 있기 때문에 DATE_ADD만으로도 충분하다.
ex) DATE_ADD(NOW(), INTERVAL 3 DAY)
※ KEYWORD : YEAR, MONTH, WEEK, DAY, HOUR …

문자열 처리

LPAD, RPAD : 좌측 혹은 우측에 특정한 문자를 지정한 바이트 만큼 채우는 함수이다.
첫번째 인자로 대상 문자열, 두번째 인자로 채울 바이트(기존 문자열 포함), 세번째 인자로 채울 문자를 넣는다.
LTRIM, RTRIM, TRIM : 연속된 공백문자를 제거하는 함수이다.
LTRIM은 왼쪽, RTRIM은 오른쪽, TRIM은 양쪽의 공백을 제거한다.

문자열 결합

CONCAT : 문자열을 결합해서 하나의 문자열로 합치는 함수로 인자 개수는 제한이 없다.
ex) CONCAT('My name is ', 'JoonT. ', 'Nice to ', ‘meet you.’);
> My name is JoonT. Nice to meet you.
CONCAT_WS : CONCAT과 같은데 첫번째 인자로 구분할 문자열을 넣어줄 수 있다.
ex) CONCAT_WS(’-’, ‘a’, ‘b’, ‘c’);
> a-b-c
GROUP_CONCAT : GROUP BY 로 그룹핑된 집합을 문자열로 연결시킬 수 있다.
단순 문자열 연결 외에도 구분자 추가, 정렬, 중복 제거 등을 할 수 있으므로 상당히 유용하다.

SELECT GROUP_CONCAT(emp_no) FROM dept_emp GROUP BY dept_no;
-- 1,2,3,4,5... : 기본적으로 ,로 연결됨
SELECT GROUP_CONCAT(emp_no SEPARATOR '-') FROM dept_emp GROUP BY dept_no;
-- 1-2-3-4-5... : 구분자를 줄 수 있음
SELECT GROUP_CONCAT(emp_no ORDER BY from_date DESC) FROM dept_emp GROUP BY dept_no;
-- dept_emp에 있는 컬럼으로 정렬 가능
SELECT GROUP_CONCAT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;
-- 중복제거도 가능

값의 비교와 대체(CASE WHEN…THEN…END)

CASE WHEN…THEN…END
switch문과 비슷하다.
CASE str
WHEN ‘A’ THEN ‘A matched’
WHEN ‘B’ THEN ‘B matched’
ELSE ‘Nothing matched’
END
각각의 부분에 대해 동등(=) 연산을 한다.

아래처럼 따로 조건을 줄 수도 있다.
CASE
WHEN str >= 65 AND str <= 90 THEN ‘Upper Case’
WHEN str >= 97 AND str <= 122 THEN ‘Lower Case’
END

THEN 부분에 쿼리를 실행해서 조건별 쿼리 실행도 가능하다.
CASE sex
WHEN ‘M’ THEN (SELECT salary from salaries WHERE ~~ LIMIT 1)
WHEN ‘F’ THEN 0
END
남자일때는 쿼리를 실행하고, 여자일떄는 쿼리를 실행하지 않는다.
이로 인해 불필요한 쿼리가 실행되는 것을 막을 수 있다.

타입 변환

CAST : 명시적 형 변환을 하는 함수이다.변환할 값과 변환할 타입을 지정해주면 된다. 둘은 AS 로 연결한다.
ex) CAST(‘1234’ AS INTEGER)
CONVERT : CAST와 동일하게 형 변환을 하는 함수이다.
형 변환외에 문자집합 변환도 가능하다는 점이 차이점이다.
ex) CONVERT(1234, UNSIGNED)
ex) CONVERT(‘asd’ USING ‘utf8’)

이진값 <> 16진수

HEX : 이진값 -> 16진수
UNHEX : 16진수 -> 이진값

MD5, SHA

비대칭형 암호화 알고리즘에 사용된다.
SHA는 SHA1 알고리즘을 써서 160비트(20바이트) 해시값을 반환하고,
MD5는 메세지 다이제스트 알고리즘을 사용해서 128비트(16바이트) 해시값을 반환한다.
비밀번호 암호화 하는 과정에서 자주 사용되며, URL과 같은 긴 문자열에 인덱스를 주고싶을 때 해당 문자열을 해시값으로 변환해 인덱스를 주기도 한다.
해시값은 거의 진짜 왠만해선 중복되는 일이 없기 때문이다.

변환된 해시값은 16진수로 출력되기 때문에 값을 저장하려면 변환되는 해시값의 크기보다 2배 큰 값을 할당해줘야 하는데(1바이트 == 16진수 2개), 원래의 16바이트, 20바이트에 저장하고 싶다면 BINARY 타입의 컬럼에 저장하면 된다.
BINARY로 변환은 위에서 사용한 HEX, UNHEX 함수를 사용하면 된다.

디버깅용 함수

SLEEP(초) : 실행되면 그 구간만큼 멈춘다. 디버깅 용도로 사용한다.
BENCHMARK(쿼리문, 실행횟수) : 쿼리문이 실행되는데 몇초 걸리는가 테스트 해볼 수 있다.
실행횟수로 여러번 반복해볼 수도 있는데… 솔직히 별로 안쓸듯.
왜냐면 쿼리문은 하나의 컬럼, 레코드인 스칼라값을 반환해야 하고,
실행횟수도 실제로 10번 실행하는 것과 BENCHMARK로 10번 실행하는것이 다르기 떄문.
실제 쿼리를 10번 실행하는 서비스 환경에서는 10번의 네트워크 자원, 서버 자원등이 다 요청되지만 BENCHMARK의 경우 단 1번만 요청되기 떄문.

IP 주소 변환

IP주소를 UNSIGNED INTEGER의 형태로 상호변환 가능하다.

INET_ATON : IP 주소 -> UNSIGNED INTEGER
INET_NTOA : UNSIGNED INTEGER -> IP 주소
ex)

SELECT name, INET_NTOA(ip_addr) 
FROM TEST 
WHERE ip_addr BETWEEN 
INET_ATON('192.168.0.1') AND INET_ATON('192.168.0.100');

훨씬 효과적으로 저장 및 조회가 가능하다.

MySQL 전용 암호화

MySQL내의 user를 생성할때 썼던 PASSWORD 함수를 말한다.
많은 사람들이 이 함수의 이름만 보고 실제 회원의 비밀번호를 이것으로 암호화하곤 했다.
하지만 이는 절대 암호화 함수가 아니다. MySQL 내부에서 그냥 사용하는 것이고, 내부 암호화 알고리즘도 명시된것이 없다. 바뀔수도 있다는 것이다.
실제로도 바뀌었다. MySQL 4.1 이전과 이후로 PASSWORD 함수를 입력하면 변경되는 값이 다름을 볼 수 있다.
이로인해 DB 버전 업데이트를 했을 경우 사용자가 로그인하지 못하는 심각한 일이 발생할 수 있다.
이럴 경우 기존의 PASSWORD함수를 OLD_PASSWORD로 바꿔서 임시로 막을 수 있다.
변경된 PASSWORD 함수에서는 암호화시에 *를 앞에 붙인다. 이로인해 어플리케이션에서 구분할수도 있다. 하지만…
기본적으로 이 함수를 절대 패스워드 암호화 하는데 쓰지 않도록 해야한다.

VALUES

INSERT INTO table_statistics(member_id, visit_count)
    SELECT member_id, COUNT(*)
    FROM tab_accesslog
    GROUP BY member_id
ON DUPLICATE KEY 
-- UPDATE visit_count = visit_count + COUNT(*) // 불가능
UPDATE visit_count = visit_count + VALUES(visit_count) -- 인서트하려 했던 값 참조 가능

UPDATE 문은 서브쿼리의 일부가 아니기 때문에 COUNT(*)로 접근이 불가능하다.
하지만 위와 같이 VALUES 함수를 사용하면 위에 INSERT에서 추가하려고 했던 컬럼에 접근 가능하다.

COUNT

출력된 레코드의 개수를 세거나, GROUP BY 함수와 같이 사용해 그룹화 된 요소들의 개수를 출력하는데 사용한다.
COUNT() 라고 행 전체를 다 읽어오거나… 그런 의미가 아니라,
그냥 레코드 1개 자체를 의미한다.
즉 COUNT(), COUNT(‘A’), COUNT(1)은 아무 차이가 없다.
하지만 COUNT(컬럼명) 으로 했을 경우 NULL인 값은 제외되므로 이부분은 조심해서 사용해야 한다.

주석

--- 한줄 주석(SQL 표준)

/*
여러줄 주석(SQL 표준)

여러줄
여러줄
여러분
*/ 

/*! 변형된 C언어 스타일의 주석인데, 이는 SQL에서 힌트로 사용할 수 있다. */
/*! STRAIGHT_JOIN */

# 한줄주석(비표준)

SELECT


18, 204, 206, 211, 213, 215, 217, 232, 238