DBMS를 조작할 때 필요한 기본 지식
(MySQL 설치 과정은 생략)
MySQL과의 커넥션 만들기
커넥션(Connection)
로그인해서 프롬포트(>)가 표시되었다는 것은 로그인 전후로 사용자와 MySQL의 관계가 변화했다는 것.
Connection id는 MySQL이 커넥션에 붙인 번호.
MySQL은 동시에 여러 개의 커넥션을 유지하는 것이 가능
== 동시에 복수의 사용자와 연결하는 것이 가능
커넥션의 상태를 조사하는 명령어로는 MySQL의 경우 'show status'를 사용. 결과는 Threads_connected의 수를 보임으로써, 연결된 커넥션 갯수를 보여줌. mysql> show status
연결을 할 때는 로그인.
연결을 끊을 때는 로그오프.
mysql> quit
Bye
세션(Session)
커넥션의 시작과 종료 사이에 DBMS와의 다양한 교환을 함.
그 교환의 시작과 종료까지의 단위를 세션이라고 부름.
SQL과 관리 명령의 차이
관리 명령
관계형 데이터베이스의 데이터를 조작하는 도구로 'SQL'을 사용하고, DBMS는 SQL 이외에도 다양한 용도의 명령어를 준비해두는 데 그것이 '관리 명령'. 관리 명령의 종류나 문법은 DBMS에 따라 다름.
SQL과 관리 명령을 확실히 구분하는 방법은
1. SQL문은 반드시 'SELECT','DELETE','INSERT','UPDATE' 중 하나의 단어로 시작.
2. 이 외의 단어로 시작한다면 관리 명령
물론 예외도 있지만 90%는 이 규칙이 적용.
관계형 데이터베이스의 계층
데이터베이스는 계층으로 나뉘어 있다
데이터베이스는 DBMS에 저장된 테이블을 SQL을 이용해 조작.
데이터베이스 내부의 테이블은 1개가 아닌 몇 개의 그룹으로 나뉘어서 관리됨.
폴더에 해당하는 스키마
파일을 분류하기 위해 사용하는 '폴더(디렉토리)'이 있는 것처럼 데이터베이스에서는 이 역할이 '스키마(Schema)'. 스키마는 권한 권리를 수행하는 것도 가능.
스키마의 상위에는 또 하나의 계층으로 '데이터베이스(Database)'가 있음. 여기서는 계층을 표시하는 의미.
최상위에 있는 인스턴스
데이터베이스보다 한 층 더 위에 있는 '인스턴스(Instance)'. DBMS가 동작할 때의 물리적 단위. OS 입장에서는 이것을 '프로세스'라고 부름. DBMS에 따라서는 '서버 프로세스'나 '서버'로 부르기도 함. 인스턴스는 메모리나 CPU를 사용하는 존재로 OS에 존재하는 '실체'.
MySQL과 Oracle의 계층 구조
MySQL은 인스턴스 - 스키마 (다수) - 테이블 (다수) 로 이루어진 3계층 구조.
Oracle은 인스턴스 - 데이터베이스 (1개만 가능) - 스키마 (다수) - 테이블 (다수) 로 이루어진 4계층 구조. 인스턴스 바로 아래에 데이터베이스가 1개로 제한되어 바로 스키마 층이 존재하는 듯한 느낌을 줌.
- 3계층 : Oracle, MySQL
- 4계층 : SQL Server, DB2, PostgreSQL
정렬과 연산
문자열 연산
+|| CONCAT SUBSTRING TRIM CHARACTER_LENGTH
데이터에는 수치형 외에 문자열형도 있음.
문자열 결합
'ABC'||'1234'->'ABC1234'
| 연산자/함수 | 연산 | 데이터베이스 |
|---|---|---|
| + | 문자열 결합 | SQL Server |
| || | 문자열 결합 | Oracle,DB2,PostgreSQL |
| CONCAT(인자1,인자2) | 문자열 결합 | MySQL |
SUBSTRING 함수
문자열의 일부분을 계산해서 반환해주는 함수. 데이터베이스에 따라 함수명이 SUBSTR인 경우도 있음.
- 앞 4자리(년) 추출
SUBSTRING('20140125001',1,4)->'2014' - 5번째 자리부터 2자리(월) 추출
SUBSTRING('20140125001',5,2)->'01'
TRIM 함수
문자열의 앞뒤로 여분의 스페이스가 있을 경우 이를 제거해주는 함수. 문자열 도중에 존재하는 스페이스는 제거되지 않음. 고정길이 문자열형(CHAR형)에 대해 많이 사용하는 함수.
TRIM('ABC ')->'ABC'
CHARACTER_LENGTH 함수
문자열의 길이를 계산해 돌려주는 함수. VARCHAR 형의 문자열은 가변 길이이므로 길이가 서로 다름. 문자열의 길이는 문자 단위로 계산되어 수치로 반환. CHAR_LENGTH로 줄여서 사용 가능.
OCTET_LENGTH 함수는 문자열의 길이를 바이트 단위로 계산해 돌려주는 함수.
컴퓨터 안에는 이미지, 음성, 수치, 문자열 데이터 등 다양한 종류의 데이터가 저장되는데 이런 데이터는 결국 모두 수치로 저장됨.
데이터 단위로 '바이트'가 존재. SQL의 OCTET_LENGTH 함수를 잘 사용하는 동시에 중요한 것은 문자 하나의 데이터가 몇 바이트의 저장공간을 필요로 하는지 '인코드 방식'에 따라 결정.
즉, 문자를 수치화(인코드)하는 방식에 따라 필요한 저장공간의 크기가 달라짐.
VARCHAR 형의 최대 길이 역시 바이트 단위로 지정. 다만, 문자세트(character set)에 따라 길이가 문자 수로 간주되기도 하니 주의.
문자세트(character set)
알파벳은 반각문자, 한글은 전각문자. 반각 문자는 전각 문자 폭의 절반밖에 안되며 저장용량 또한 전각문자 쪽이 더 큼.
반각의 알파벳이나 숫자, 기호는 'ASCII문자'라고 함.
한글의 경우 'EUC-KR','UTF-8' 등의 인코드 방식을 주로 사용.
한 문자가 몇 바이트인지는 쓰이는 문자 세트에 따라 다름.
CHAR_LENGTH 함수를 사용하는 경우에는 한글이든 ASCII 문자든 문자 수로 계산.
OCTET_LENGTH 함수의 경우는 문자 수가 아닌 바이트 단위로 길이를 계산.
- 문자세트 별 문자 수와 바이트 수
EUC-KR에서 ASCII문자는 1바이트, 한글은 2바이트의 용량을 가짐. UTF-8에서 ASCII문자는 1바이트, 한글은 3바이트의 용량을 가짐.문자세트 문자 수 바이트 수 EUC-KR 12 19 UTF-8 12 26
날짜 연산
CURRENT_TIMESTAMP CURRENT_DATE INTERVAL
날짜/시간 데이터를 저장하는 방법은 데이터베이스 제품에 따라 크게 달라짐. 날짜는 DATE형, 시간은 TIME형, 날짜와 시간은 DATETIME 형과 같이 세분화해 지정하는 제품도 있음.
SQL에서의 날짜
날짜/시간 데이터는 수치 데이터와 같이 사칙 연산 가능. 결과값으로 동일한 날짜/시간 유형의 데이터를 반환하는 경우도 있으며 기간(간격)의 차를 나타내는 기간형(interval)데이터를 반환하는 경우도 있음.
시스템 날짜
컴퓨터 안에는 반드시 시계가 내장됨. 표준 SQL에서는 'CURRENT_TIMESTAMP'함수로 실행했을 때를 기준으로 시간 표시.
- CURRENT_TIMESTAMP로 시스템 날짜 확인
SELECT CURRENT_TIMESTAMP;
Oracle과 같은 전통적인 데이터베이스에서는 FROM구 생략 불가.CURRENT_TIMESTAMP 2014-01-25 10:10:40
CURRENT_TIMESTAMP는 표준 SQL로 규정된 함수.
Oracle에서는 SYSDATE함수, SQL Server에서는 GETDATE 함수 사용.
날짜 서식
날짜 서식은 국가별로 다름.
- 2014/01/25
- 2014-01-25
- 25 Jan 2014
날짜 데이터의 서식을 임의로 지정, 변환할 수 있는 함수를 지원.
Oracle의 경우 TO_DATE 함수를 사용해 문자열 데이터를 날짜형 데이터로 변환할 수 있으며 서식 또한 별도로 지정할 수 있음.
TO_DATE('2014/01/25','YYYY/MM/DD')
'YYYY/MM/DD'가 서식 부분. 반대로 날짜형 데이터를 서식에 맞춰 변환해 문자열 데이터로 출력하는 함수는 Oracle의 경우, TO_CHAR 함수.
날짜의 덧셈과 뺄셈
날짜/시간형 데이터는 기간형 수치데이터와 덧셈 및 뺄셈 가능.
- 날짜를 연산해 시스템 날짜의 1일 후를 검색
SELECT CURRENT_DATE+INTERVAL 1 DAY;CURRENT_DATE+INTERVAL 1 DAY 2014-01-26
CURRENT_DATE는 시스템 날짜 확인 함수.
INTERVAL 1 DAY는 '1일 후'라는 의미의 기간형 상수.
기간형 상수의 기술방법은 데이터베이스마다 조금씩 다름.
날짜/시간형 데이터 간에 뺄셈.
Oracle에서는 '2014-02-28'-'2014-01-01'로 계산.
MySQL에서는 DATEDIFF('2014-02-28','2014-01-01')로 계산.
CASE 문으로 데이터 변환하기
CASE 문
CASE문
CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2...]
[ELSE 식3]
END
WHEN 절에는 참/거짓을 반환하는 조건식.
해당 조건을 만족하여 참이 되는 경우는 THEN 절이 기술한 식이 처리.
해당 조건을 만족하지 못한 경우에는 ELSE 절이 기술한 식이 처리.
ELSE는 생략 가능하고 생략했을 경우 'ELSE NULL'.
- CASE로 NULL 값을 0으로 변환하기
SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM 테이블명;
이 경우 a column 속 NULL 값이 0으로 변환.
COALESCE
NULL 값을 변환하는 경우라면 COALESCE 함수를 사용하는 게 쉬움.
SELECT a, COALSCE(a,0) FROM 테이블명;
a가 NULL이 아니면 a를 그대로 출력. 그렇지 않으면 (a가 NULL이면) 0을 출력.
COALESCE 함수는 여러 개의 인수를 지정할 수 있음. 주어진 인수 가운데 NULL이 아닌 값에 대해서는 가장 먼저 지정된 인수의 값을 반환함.
또 하나의 CASE 문
Encode : 수치화
Decode : 문자화
WHEN a=1 THEN '남자'
WHEN a=2 THEN '여자'
검색 CASE 문
CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2...]
[ELSE 식3]
END
단순 CASE 문
CASE 식 WHEN 식2 THEN 식3
[WHEN 식4 THEN 식5...]
[ELSE 식6]
END
식1의 값이 WHEN 식2의 값과 동일한지 비교 후,
값이 같으면 식3의 값이 결과값이 됨.
값이 같지 않을 경우, 그 뒤에 기술한 WHEN 절과 비교.
비교 결과 일치하는 WHEN 절이 하나도 없는 경우에는 ELSE 절이 적용.
CASE를 사용할 경우 주의사항
- CASE문은 어디서나 사용 가능.
- ELSE 생략 시 ELSE NULL.
대응하는 WHEN이 없어서 ELSE 절을 사용하는데, 이때 ELSE 절이 NULL일 때, 상정한 것 이외의 데이터가 왔을 때 NULL이 반환되어 생략하지 않는 것이 좋음 - WHEN에 NULL 지정 시 정상적으로 처리 불가.
비교 연산자 = 로는 NULL 값인지 아닌지 비교할 수 없음.
NULL은 IS NULL을 사용해야하는 데, 따라서 NULL 값 판정 시에는 검색 CASE문을 사용해야만 함. - DECODE NVL는 CASE문과 같은 용도. Oracle에서만 지원.
- NULL 값을 반환할 때는 Oracle은 NVL함수, SQL Server에서는 ISNULL함수. 그러나, 표준 SQL COALESCE함수 사용하는 게 좋음.
