<!doctype html>
4. 데이터 조회하기
SQL을 통해 데이터를 조회해보자.
1) 'SELECT * FORM 테이블명' 실행
SQL 명령은 mysql클라이언트를 통해 실행할 수 있다.
SELECT * FROM 테이블명xxxxxxxxxxSELECT * FROM employees;
SELECT 문을 통해 데이터를 조회할 수 있다.
2) SELECT 명령 구문
SELECT는 DML에 속하는 명령으로 SQL에서 자주 사용된다. SELECT 명령으로 데이터베이스의 데이터를 읽어올 수 있다.
xxxxxxxxxxSELECT * //열 이름, *은 모든 열FROM employees;*는 테이블의 모든 열을 출력하도록 한다. 열 이름을 지칭하면 해당 열의 데이터들만 표시된다.
3) 예약어와 데이터베이스 객체명
SQL에서 이미 사용되고 있는 키워드(SELECT, FROM, INSERT, ...)는 테이블이나 기타 데이터 베이스 객체의 이름으로 사용할 수 없다.
또 예약어와 데이터베이스 객체명은 대소문자를 구분하지 않는다. 예약어는 대문자로, 데이터베이스 객체는 소문자로 표기하는 것이 일반적이다.
4) NULL
SELECT의 결과 NULL값을 가지는 셀이 존재할 수 도 있다. NULL은 특별한 데이터 값으로 아무것도 저장되어 있지 않은 상태를 의미한다.
5. 테이블 구조 참조하기
DESC 명령으로 테이블 구조를 참조할 수 있다.
1) DESC 명령
xxxxxxxxxxDESC 테이블명;xxxxxxxxxxDESC employees
이와 같이 DESC 명령으로 테이블에 어떤 열이 정의되어 있는지 알 수 있다.
- Field : 열 이름
- Type : 해당 열의 자료형
- Null : NULL값 허용 여부
- Key : 키로 지정되었는지
- Default : 기본값
※모든 테이블 보기
xxxxxxxxxxSHOW TABLES이걸로 현재 스키마의 테이블 목록을 확인할 수 있다.
2) 자료형
테이블은 하나 이상의 열로 구성된다. 자료형을 통해 데이터를 올바른 형식에 맞게 저장/관리 할 수 있다.
CHAR 데이터 타입
CHAR와 VARCHAR(VARiable length CHARacter string)은 모두 텍스트 문자열을 허용하고, 필드의 크기를 제한한다. 두 타입의 차이점은 CHAR 필드의 모든 문자열은 크기가 정해진다는 것. 즉 더 작은 문자열을 입력하면 공백으로 채워진다. 반면 VARCHAR의 경우, 텍스트를 채우지 않으며, 입력한 텍스트 크기에 맞게 가변적으로 크기를 가진다. 그러나, VARCHAR는 각 값의 크기를 추적할 수 있는 약간의 오버헤드가 필요하기 때문에 모든 데이터의 크기가 비슷하다면, CHAR가 더 효율적이다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| CHAR(n) | 정확히 n (<=255) | CHAR(5) 'Hello'는 5바이트 사용CHAR(50) / 'Hello'는 50바이트 사용 |
| VARCHAR(n) | 최대 n 까지(<=65535) | VARCHAR(100)'Hello'는 5바이트 사용 / VARCHAR(5) 'Hello'는 5바이트 사용 |
BINARY 데이터 타입
BINARY 데이터 타입은 관련된 문자 세트가 없는 문자의 전체 바이트를 저장하는데 사용된다. 예를 들면 GIF 이미지를 저장하는데 사용할 수 있다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| BINARY(n) 혹은 BYTE(n) | 정확히 n (<=255) | CHAR이지만 바이너리 데이터를 가짐 |
| VARBINARY(n) | 최대 n 까지(<=65535) | VARCHAR이지만 바이너리 데이터를 가짐 |
TEXT와 VARCHAR 데이터 타입
TEXT와 VARCHAR에는 작은 차이점이 있다.
TEXT 필드는 기본 값을 가질 수 없다.
MySQL은 TEXT 열의 처음 n 개의 문자만 인덱싱 할 수 있다.
이것은, 만약 전체 내용을 검색할 때는 VARCHAR이 더 알맞고, 빠르다는 것이다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| TINYTEXT(n) | 최대 n (<=255) | 문자열로 취급 |
| TEXT(n) | 최대 n (<=65535) | 문자열로 취급 |
| MEDIUMTEXT(n) | 최대 n (<=16777215) | 문자열로 취급 |
| LONGTEXT(n) | 최대 n (<=4294967295) | 문자열로 취급 |
BLOB 데이터 타입
BLOB(Binary Large OBject)는 65535 바이트를 넘는 바이너리 데이터에 유용하며, 기본 값을 가질 수 없다. MySQL에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용하는데, 이 두 타입은 많은 부분에서 거의 똑같은 설정이나 방식으로 작동한다. 차이점은 TEXT 타입은 문자열을 저장하는 대용량 칼럼이라서 Character Set과 Collation을 가진다는 점이다.
| TYPE | 사용되는 바이트 | 예제 |
|---|---|---|
| TINYBLOB(n) | 최대 n (<=255) | 바이너리 데이터로 취급 |
| BLOB(n) | 최대 n (<=65535) | 바이너리 데이터로 취급 |
| MEDIUMBLOB(n) | 최대 n (<=16777215) | 바이너리 데이터로 취급 |
| LONGBLOB(n) | 최대 n (<=4294967295) | 바이너리 데이터로 취급 |
숫자형 데이터 타입
| TYPE | 사용되는 바이트 | 최소 값(signed/unsigned) | 최대 값(signed/unsigned) |
|---|---|---|---|
| TINYINT | 1 | -128 / 0 | 127 / 255 |
| SMALLINT | 2 | -32768 / 0 | 32767 / 65535 |
| MEDIUMINT | 3 | -8388608 / 0 | 8388607 / 16777215 |
| INT or INTEGER | 4 | -2147483648 / 0 | 2147483647 / 4294967295 |
| BIGINT | 8 | -9223372036854775808 / 0 | 9223372036854775807 / 18446744073709551615 |
| FLOAT | 4 | -3.40E+45 / (no unsigned) | 3.40E+45 / (no unsigned) |
| DUBLE or REAL | 8 | -1.7976E+320 / (no unsigned) | 1.7976E+320 / (no unsigned) |
참고로 INT[(n)]에서 (n)의 의미는 ZEROFILL을 위한 값이다.
xxxxxxxxxxid INT(5) ZEROFILL이런식으로 열을 생성 했을 때, 5자리에 맞게 0이 들어간다.
날짜, 시간
| TYPE | 사용되는 바이트 | 값의 범위 |
|---|---|---|
| YEAR | 1 | YEAR(2) : 70(1970) ~ 69(2069) |
| TIME | 3 | '-838:59:59' ~ '838:59:59 |
| DATE | 3 | '1000-01-01' ~ '9999-12-31' |
| TIMESTAMP | 4 | '1970-01-01 00:00:01' ~ '2038-01-19 : 03:14:07'(UTC) |
| DATETIME | 8 | '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' |
TIMESTAMP와 DATETIME의 차이는 TIMESTAMP는 저장시 UTC로 데이터를 저장하며 조회 시 설정된 time_zone으로 시간을 자동 변경해준다.
6. 검색 조건 지정하기
SELECT 문 사용시 WHERE을 통해 특정 조건에 맞는 데이터를 검색할 수 있다.
1) SELECT 구에서 열 지정하기
xxxxxxxxxxSELCT 열1, 열2, ...FROM 테이블명xxxxxxxxxxSELECT emp_no, first_nameFROM employees;실행 결과 SELECT로 지정한 열만 출력하는 것을 확인할 수 있다.
2) WHERE 구에서 행 지정하기
테이블에 저장된 수많은 데이터 중 필요한 데이터만 검색하기 위해 WHERE 구를 사용한다.
xxxxxxxxxxSELCT 열FROM 테이블명WHERE 조건식WHERE 구 뒤의 조건에 일치하는 결과만 반환하게 된다. 조건식에 다양한 표현을 사용할 수 있다.
| 연산자 | 설명 |
|---|---|
| \= | 같음 |
| <, >, <=, >= | 수치비교 연산 |
| !=, <> | 다름 |
| IN(값1, 값2, ...) | ()안의 모든 값 |
| BETWEEN a AND b | a와 b사이의 모든 값 |
| LIKE% | 일부 문자열 일치 |
| IS NULL | NULL값인지 확인 |
SELECT *FROM employeesWHERE emp_no in(10001, 10002); //emp_no = 10001 OR emp_no = 10002SELECT *FROM employeesWHERE birth_date BETWEEN '1950-01-01' AND '1959-12-31'SELECT *FROM employeesWHERE birth_date IS NULL;
7. 조건 조합하기
조건문에 논리 연산자를 추가하여 더 구체적인 조건을 명세할 수 있다.
xxxxxxxxxx조건식1 AND 조건식2조건식1 OR 조건식2NOT 조건식
1) AND
xxxxxxxxxx조건식1 AND 조건식2조건식1과 조건식2를 모두 만족하는 행을 선택한다.
xxxxxxxxxxSELECT *FROM employeesWHERE gender = 'M' AND birth_date > '1950-01-01'
2) OR
xxxxxxxxxx조건식1 AND 조건식2조건식1, 조건식2 중 하나 이상을 만족하는 행을 선택한다.
xxxxxxxxxxSELECT *FROM employeesWHERE hire_date > '1986-01-01' OR birth_date > '1959-12-31'
3) AND, OR 주의점
AND와 OR 중 AND의 연산자 우선 순위가 더 높기 때문에 주의가 필요하다.
xxxxxxxxxxWHERE a = 1 OR a = 2 AND b = 1 OR b = 2=> WHERE a = 1 OR (a = 2 AND b = 1) OR b = 2xxxxxxxxxxINTERVALBINARY, COLLATE!- (unary minus), ~ (unary bit inversion)^*, /, DIV, %, MOD-, +<<, >>&|= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, INBETWEEN, CASE, WHEN, THEN, ELSENOTAND, &&XOROR, ||= (assignment), :=
4) NOT
xxxxxxxxxxNOT 조건식조건식의 T/F를 F/T로 반환한다.
xxxxxxxxxxSELECT *FROM employeesWHERE NOT emp_no = 10003;
8. 패턴매칭에 의한 검색
LIKE를 이용하여 문자열이 일부분을 비교하는 부분 검색을 할 수 있다.
\= 연산자는 데이터 값이 완전히 동일한지를 비교한다. 하지만 특정 문자나 문자열이 포함되어 있는지를 검색할 경우도 있다. 이런 경우 사용하는 방법이 패턴매칭 또는 부분 검색이다.
1) LIKE로 패턴 매칭하기
xxxxxxxxxx열명 LIKE '패턴'패턴을 정의할 때는 %, _를 이용한다.
테이블
xxxxxxxxxxSELECT *FROM sampleWHERE text LIKE 'SQL%';
'SQL%'는 'SQL'로 시작하는 텍스트를 가져오게 된다. 'SQL'이 포함된 모든 텍스트를 검색하려면 다음과 같이 해야한다.
xxxxxxxxxxSELECT *FROM sampleWHERE text LIKE '%SQL%';
1번 행은 'SQL'앞에 아무것도 없는데 검색이 되었다. 이는 %가 빈 문자열도 포함하기 때문이다. _는 빈문자열을 포함하지 않고 하나의 문자를 나타낸다.
LIKE로 %나 _를 검색하기 위해 이스케이프 문자로 \를 사용한다.
xxxxxxxxxx'%\%%''%\_%'문자 '는 ''로 쓴다. 이스케이프문자 \도 가능
또한 패턴 매칭을 위해 MySQL에서는 정규 표현식을 지원한댜.
LIKE 사용 시 인덱스가 사용되는지 고려해야한다. %가 앞에 붙을 경우, 인덱스를 사용하지 못하게 된다.
xxxxxxxxxx# 개선전 index XSELECT A.KOR_NM, A.STD_NO, A.RES_NOFROM SREG110 AWHERE KOR_NM LIKE '%홍길동%„# %LIKE 비교 개선후SELECT A.KOR_NM, A.STD_NO, A.RES_NOFROM SREG110 AWHERE KOR_NM LIKE '홍길동%„#LIKE 사용 시 가급적이면 „%‟를 비교 값 앞에 붙이지 않는다.
인덱스를 타지 않는 쿼리
인덱스 컬럼 절을 변형한 경우
- 수식이나 함수 등으로 인덱스 컬럼 절을 변형하였을 경우
- 반드시 함수나 수식을 사용해야 하는 경우에는 인덱스 컬럼 부분에 적용하지 말고, 여기에 대입되는 컬럼이나 상수부분에 적용해야 한다.
xxxxxxxxxxSELECT column_name FROM table_name WHERE TO_CHAR(column_name, 'YYYYMMDD') = '20130909';> SELECT column_name FROM table_name WHERE column_name = TO_DATE('20130909', 'YYYYMMDD');SELECT column_name FROM table_name WHERE column_name * 100 > 10000;> SELECT column_name FROM table_name WHERE column_name = 10000 / 100;
내부적으로 데이터 형 변환이 일어난 경우
- 서로 대입되는 항목끼리 데이터 타입이 다르면 내부적인 형 변환에 의해 컬럼이 함수를 사용한 효과를 나타낸다.
xxxxxxxxxxSELECT column_name FROM table_name WHERE column_name = '20130909'; // DATE 타입의 column> SELECT column_name FROM table_name WHERE column_name = TO_DATE('20130909', 'YYYYMMDD');SELECT column_name FROM table_name WHERE column_name = 100; // 문자 타입의 column> SELECT column_name FROM table_name WHERE column_name = '100';
조건절에 NULL 또는 NOT NULL을 사용하는 경우
- 기본적으로 인덱스를 구성한 컬럼 값이 전부 NULL이라면 인덱스는 이런 값을 저장하지 않는다.
따라서 NULL인 값이 많지 않아 인덱스를 통해 엑세스를 하고자 한다면 데이터 생성 시 디폴트로 0과 같이 데이터를 만들어주는 것이 좋다. 반대로, 만약 NOT NULL이 분석 대상이라면 해당 컬럼을 NULL 허용 컬럼으로 두는 것이 좋다.
xxxxxxxxxxSELECT column_name FROM table_name WHERE column_name IS NULL;SELECT column_name FROM table_name WHERE column_name IS NOT NULL;> SELECT column_name FROM table_name WHERE column_name > '';> SELECT column_name FROM table_name WHERE column_name >= 0;
부정형으로 조건을 사용한 경우
- 부정문은 인덱스를 활용하지 못한다.
xxxxxxxxxxSELECT column_name FROM table_name WHERE column_name != 30;> SELECT column_name FROM table_name WHERE column_name < 30 AND column_name > 30;# 또는 테이블을 한 번 더 읽어 NOT EXISTS를 사용> SELECT column_name FROM table_name WHERE NOT EXISTS> (SELECT column_name FROM table_name WHERE column_name = 30);
LIKE 연산자를 사용하였을 경우
- LIKE 연산자를 이용하여 검색을 할 경우 %를 앞에 넣어 사용하게 되면 인덱스를 타지 않는다.
xxxxxxxxxxSELECT column_name FROM table_name WHERE column_name LIKE '%S%';> SELECT column_name FROM table_name WHERE column_name LIKE 'S%';가능하면 INSTR을 사용하는 것도 나쁘지 않다.[MySQL INSTR() Function](https://www.w3schools.com/SQl/func_mysql_instr.asp)INSTR('비교할 대상', '비교하고자하는 값', 비교를 시작할 위치, 검색된 결과의 순번);SELECT column_name FROM table_name WHERE INSTR(column_name , 'cmp_value') > 0;
OR 조건 사용
SELECT column_name FROM table_name1 t1, table_name2 t2
WHERE (t1.column_name1 = t2.column_name1 OR t1.column_name2 = t2.column_name2)
AND t1.column_name3 = 'cmp_value';
> SELECT column_name FROM table_name1 t1, table_name2 t2
WHERE t1.column_name1 = t2.column_name1 AND t1.column_name3 = 'cmp_value';
UNION ALL
SELECT column_name FROM table_name1 t1, table_name2 t2
WHERE t1.column_name2 = t2.column_name2 AND t1.column_name3 = 'cmp_value';