탁 블로그 (이전)

전체 텍스트 검색은 긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 MySQL의 부가적인 기능이다. 전체 텍스트 검색을 사용하면 기사의 내용에 포함된 여러 단어들에 인덱스가 설정되어서 검색 시에 인덱스를 사용하여 검색 속도가 월등히 빨라진다.

전체 텍스트 인덱스는 신문기사와 같이 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다.

SELECT * FROM 신문기사_테이블 WHERE 신문기사내용 '교통 사고의 증가로 인해 오늘 ----';

SELECT * FROM 신문기사_테이블 WHERE 신문기사내용 LIKE '교통%';

인덱스가 정렬되어 있으므로, 해당되는 내용이 인덱스를 통해서 빠르게 검색함.

SELECT * FROM 신문기사_테이블 WHERE 신문기사내용 LIKE '%교통%';

전체 테이블 검색을 하게 됨. 만약 10년치 기사에서 검색한다면 과부하가 발생하고 응답시간도 긺.

이것을 전체 텍스트 검색이 해결한다. 전체 텍스트 검색은 첫 글자 뿐 아니라 중간의 단어나 문장으로도 인덱스를 생성해 주기 때문에 이와 같은 상황에서도 인덱스(정확히는 전체 텍스트 인덱스)를 사용할 수 있어 순식간에 검색 결과를 얻을 수 있다.

전체 텍스트 인덱스는 신문기사와 같이, 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다. MySQL에서 생성한 일반적인 인덱스와는 몇 가지 차이점이 있다.

- 전체 텍스트 인덱스는 InnoDB와 MylSAM 테이블만 지원한다.

- 전체 텍스트 인덱스는 char, varchar, text의 열에만 생성이 가능하다.

- 인덱스 힌트의 사용이 일부 제한된다.

- 여러 개의 열에 FULLTEXT 인덱스를 지정할 수 있다.

1. 전체 텍스트 인덱스 생성

3가지 방법:

CREATE TABLE 테이블이름(

             …

             열이름 데이터형식,

             …,

             FULLTEXT 인덱스이름 (열이름)

);

CREATE TABLE 테이블이름(

             …

             열이름 데이터형식,

             …,

);

ALTER TABLE 테이블이름

                 ADD FULLTEXT (열이름);

CREATE TABLE 테이블이름(

             …

             열이름 데이터형식,

             …,

);

CREATE FULLTEXT INDEX 인덱스이름

                 ON 테이블이름 (열이름);

2. 전체 텍스트 인덱스 삭제

ALTER TABLE 테이블이름
	DROP INDEX FULLTEXT (열이름);

3. 중지 단어(stopwords)

실제로 검색할 때 무시할 만한 단어들은 아예 전체 텍스트 인덱스로 생성하지 않는 편이 좋다.

‘이번’, ‘아주’, ‘모두’, ‘꼭’ 등과 같은 단어는 검색할 이유가 없으므로 제외한다. 이것이 중지 단어이다.

MySQL 5.7은 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 테이블에 약 36개의 중지 단어를 가지고 있다.

4. 전체 텍스트 검색을 위한 쿼리

전체 텍스트 인덱스를 생성한 후에 이를 사용하기 위한 쿼리는 일반 SELECT문의 WHERE절에 MATCH() AGAINST()를 사용하면 된다.

MATCH (col1, col2, ...) AGAINST (expr [search_modifier])

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

- 자연어 검색

특별히 옵션을 지정하지 않거나 IN NATURAL LANGUAGE MODE를 붙이면 자연어 검색을 한다. 자연어 검색은 단어가 정확한 것을 검색해 준다.

newspaper이라는 테이블의 article이라는 열에 전체 텍스트 인덱스가 생성되어 있다고 가정한다.

‘영화’라는 단어가 들어간 기사를 찾으려면 다음과 같이 사용한다.

SELECT * FROM newspaper 
	WHERE MATCH(article) AGAINST('영화');

‘영화는’, ‘영화가’ 등 검색 불가.

‘영화’ 또는 ‘배우’ 두 단어 중 하나가 포함된 기사 검색.

SELECT * FROM newspaper 
	WHERE MATCH(article) AGAINST('영화 배우');

- BOOLEAN MODE 검색

단어나 문장이 정확히 일치하지 않는 것도 검색하는 것을 의미한다.

5. 실습

MySQL은 기본적으로 3글자 이상만 전체 텍스트 인덱스로 생성한다. 이러한 설정을 2글자까지 전체 텍스트 인덱스가 생성되도록 시스템 변숫값을 변경한다.

아직 인덱스를 만들지 않은 상태.

실행계획(전체 테이블 검색)

전체 텍스트 인덱스 생성.

실행계획(전체 텍스트 인덱스 검색)

‘남자’ 또는 ‘여자’가 들어간 행 검색.

‘남자’, ‘여자’ 모두 들어간 행 검색.

‘남자’가 들어간 행 중에 ‘여자’는 제외된 행 검색.

전체 텍스트 인덱스로 만들어진 단어들.

원래 있던 전체 텍스트 인덱스를 삭제하고 불필요한 중지 단어를 지정.

잠금(Locking)을 알기 위해선 그 전에 동시성 제어(Concurrency Control)를 먼저 짚고 넘어가야 한다. 동시성 제어는 다중 사용자 환경을 지원하는 데이터베이스 시스템에서 동시에 실행되는 여러 트랜잭션 간의 간섭으로 문제가 발생하지 않도록 트랜잭션의 실행 순서를 제어하는 기법이다. 한 트랜잭션을 실행하는 중에 다른 트랜잭션이 간섭하게 되면 갱신 분실(lost update), 연쇄 복귀(cascading rollback) 또는 회복 불가능(unrecoverability), 불일치 분석(inconsistent analysis) 등과 같은 문제들이 발생할 수 있다.

동시성 제어는 다시 비관적 동시성 제어 (Pessimistic concurrency Control)와 낙관적 동시성 제어(Optimistic concurrency control)로 나뉠 수 있다. 비관적 동시성 제어란 사용자들이 같은 데이터를 동시에 수정할 것이라고 가정하여 데이터를 읽는 시점에 Locking을 걸고 트랜잭션이 완료될 때까지 이를 유지하는 것을 말한다. 반면, 낙관적 동시성 제어란 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정하여 데이터를 읽을 때 Locking을 설정하지는 않는다.

한 트랜잭션의 실행 중에 다른 트랜잭션의 끼어들기로 발생한 문제를 해결하기 위해서는 동시성(병행 수행)을 최대한 보장하면서 직렬 스케줄과 동일한 결과를 얻을 수 있는 직렬 가능한 스케줄로 만들도록 해야 한다. Locking은 트랜잭션의 실행 순서를 강제로 제어하여 직렬 가능한 스케줄이 되도록 보장하는 방법이다.

Locking은 하나의 트랜잭션이 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 상호배제(Mutual Exclusive)하는 기법이다. 하나의 트랜잭션이 데이터 항목에 대하여 잠금(lock)을 설정하면, 잠금을 설정한 트랜잭션이 해제(unlock)할 때까지 데이터를 독점적으로 사용할 수 있다.

Locking은 기본적으로 lock 연산과 unlock 연산을 사용한다.

Locking의 연산은 데이터에 대한 연산의 성격에 따라 공유잠금(Shared lock: S-lock)과 배타잠금(Exclusive lock: X-lock)으로 나눌 수 있다. 모든 트랜잭션은 데이터 항목에 대한 접근을 시도할 때 둘 중 하나의 잠금 방법을 사용한다. S-lock 과 X-lock 모두 잠금을 해제할 경우에는 unlock 연산을 이용한다.

S-lock을 설정한 트랜잭션은 데이터에 대해 읽기 연산만 가능하다. 한 데이터에 대해 T1이 S-lock을 설정했다면 T2역시 S-lock이 실행되는 동안에는 읽기 연산만이 가능하다. 또한 하나의 데이터 항목에 대해 여러 개의 S-lock이 가능하다. 예를 들어 T1에서 x에 대해 S-lock을 설정한 경우, 동시에 T2에서도 x에 대해 S-lock을 설정할 수 있다.

트랜잭션이 X-lock을 설정하면 데이터 항목에 대해서 읽기 연산과 쓰기 연산 모두 가능하다. 또한 하나의 데이터 항목에 대해서는 하나의 X-lock만 가능하다. 동시에 여러 개의 X-lock은 불가능한데, 예를 들어 T1이 한 데이터에 대해 X-loc을 설정하였다면 unlock하기 전까지는 T2가 그 데이터에 X-lock을 설정할 수 없다.

S-lock과 X-lock에 대한 표를 아래에서 확인할 수 있다.

Lock에 대해서는 다음과 같은 규칙을 지켜야 한다.

-        트랜잭션은 데이터 항목 x에 대해 read(x) 연산을 실행하기 전에 S-lock(x)이나 X-lock(x) 중 하나를 실행해야 한다.

-        write(x) 연산을 실행하기 위해서는 X-lock(x)을 실행해야 한다.

-        연산 종료 후에는 unlock(x) 연산을 실행해야 한다.

-        S-lock(x)이나 X-lock(x) 연산 실행 후에만 unlock(x) 연산을 실행할 수 있다.

Locking의 대상이 되는 데이터 객체의 크기를 단위로 나타낼 수 있다. 작게는 레코드의 필드 값, 하나의 레코드, 물리적 입출력 단위가 되는 디스크 블록이 될 수도 있으며, 크게는 테이블이나 데이터베이스까지 하나의 잠금 단위가 될 수 있다. 잠금 단위가 클수록 동시성(병행성) 수준은 낮아지고, 동시성 제어 기법은 간단해진다. 반면, 잠금 단위가 작을수록 동시성(병행성) 수준은 높아지고, 관리는 복잡해진다(예: 잠금단위가 레코드인 경우vs테이블인 경우.)

Locking은 트랜잭션 작업의 완전성을 보장해주는 기능처럼 보이지만 그 한계도 존재한다. 첫째로 교착상태(deadlock)가 발생할 수 있으며, 둘째로 직렬 가능한 스케줄이 항상 보장되지만은 않는다는 것이다.

위와 같이 두 트랜잭션이 교착상태에 빠지면 외부에서 강제로 트랜잭션을 중단하거나 잠금을 해제하지 않는 이상 무한정 대기 상태로 남게 된다.

위에서 직렬 가능한 스케줄이 항상 보장되지만은 않는다고 하였는데 이를 보완하는 것이 2단계 잠금 규약(2-Phase Locking protocol: 2PL)이다. 2PL은 잠금을 설정하는 단계와 해제하는 단계로 나누어 수행하도록 하는 것이다. 먼저 트랜잭션이 lock 연산만 수행할 수 있고 unlock 연산은 수행할 수 없는 확장단계(growing phase)를, 다음으로 트랜잭션이 unlock 연산만 수행할 수 있고 lock 연산은 수행할 수 없는 축소단계(shrinking phase)를 거친다.

왼쪽, 오른쪽 모두 2PL을 준수한 사례인데 오른쪽은 그럼에도 불구하고 교착상태가 발생했다. 이러한 문제를 해결하기 위해서는 각 트랜잭션을 시작하기 전에 모든 필요한 잠금을 동시에 설정할 수 있다. 또는 교착상태 회피 방법이나 탐지 방법을 통해 교착상태를 해결해야 한다.

http://www.gurubee.net/lecture/2398

https://medium.com/pocs/%EB%8F%99%EC%8B%9C%EC%84%B1-%EC%A0%9C%EC%96%B4-%EA%B8%B0%EB%B2%95-%EC%9E%A0%EA%B8%88-locking-%EA%B8%B0%EB%B2%95-319bd0e6a68a

뷰의 작동 방식은 다음과 같다. 뷰를 생성하여 SELECT문으로 데이터를 조회하면 테이블과 동일한 모양으로 확인할 수 있다. 

뷰를 사용하는 이유는 다음과 같은 장점이 있기 때문이다.

보안에 도움이 된다: 

위와 같은 뷰에는 사용자의 이름과 주소만 있을 뿐, 시용자의 중요한 개인정보인 출생년도, 연락처, 키, 가입일 등은 들어 있지 않다. 예를 들어 다른 사용자에게 무언가 작업을 맡겼을 때 연락처 등의 중요한 정보에는 접근하지 않고 이름과 주소 데이터만 가지고 작업하도록 할 때 뷰를 사용할 수 있다.

복잡한 쿼리를 단순화할 수 있다:

이렇게 줄도 길고 여러 조건이 들어간 쿼리를 자주 사용하게 될 경우 이 쿼리문을 뷰로 만들어 놓으면 편리하다.

• CREATE VIEW Statement
• ALTER VIEW Statement
• DROP VIEW Statement
• Using Views

1. View Syntax

2. View Processing Algorithms

3. Updatable and insertable Views

4. The View WITH CHECK OPTION Clause

5. View Metadata

• CREATE VIEW Statement

CREATE VIEW 명령문은 새 뷰를 만든다. OR REPLACE 절을 뒤에 붙이면 존재하고 있던 뷰를 대체한다. 

여기서 select_statement는 만들고자 하는 뷰를 정의해주는 SELECT 명령문이다. select_statement는 기존의 테이블이나 다른 뷰들의 SELECT 명령문이 될 수 있다.

뷰는 만든 시간에 멈취있으며, 뷰를 만든 이후에는 뷰의 근본이 되는 테이블을 정의하는 변화에 영향을 받지 않는다. 예를 들어 어떤 테이블의 SELECT *을 사용한 뷰가 있다면, 새 컬럼들이 그 테이블에 나중에 추가되어도 그 뷰에는 추가되지 않는다. 뷰에서 그 컬럼들을 선택하면 에러가 발생한다.

ALGORITHM 절은 MySQL이 뷰를 처리하는 데 영향을 미친다. 

DEFINER와 SQL SECURITY 절은 뷰를 만들 때 접근권한을 체크할 때 사용한다. 

WITH CHECK OPTION 절은 뷰에 참조된 테이블에 행을 INSERT 또는 UPDATE 하는 것을 제약한다.

(CREATE VIEW 명령문에서는 다른 곳에서 SELECT 명령문이 쓰인 컬럼에 대한 SELECT 우선권을 가진다. OR REPLACE 절이 있으면 뷰에 대한 DROP 우선권도 가진다. )

뷰는 데이터베이스에 속한다. 뷰를 만들면 기본 설정되어 있는 데이터베이스에 생성되는 것이 디폴트이다. 다른 데이터베이스에 뷰를 만들고 싶으면 db_name.view_name 구문을 사용하여 원하는 데이터베이스의 이름을 명시한다.

한 데이터베이스 안에서는 베이스 테이블과 뷰가 같은 namespace를 공유하기 때문에 뷰 이름을 테이블 이름과 동일하게 설정할 수 없다.

뷰는 베이스 테이블과 마찬가지로 중복되지 않는 유니크한 컬럼명을 가진다. AS 뒤의 SELECT문에 사용된 컬럼명이 뷰의 컬럼 이름으로 사용되는 것이 디폴트이다. 베이스 테이블의 컬럼명과 다르게 컬럼명을 설정하려면  VIEW view_name 뒤에 [(column_list)]와 같이 컬럼들의 이름을 지정한다. column_list에는 반드시 SELECT 문의 컬럼 수와 동일한 컬럼을 작성해야 한다.

뷰를 만들 때 SELECT문을 사용하여 테이블이나 다른 뷰를 선택한다고 하기는 했지만 SELECT문 없이 아무 테이블도 지정하지 않을 수도 있다.

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

• ALTER VIEW Statement

이 명령문은 정의된 뷰를 수정하는 것인데 여기서 뷰는 반드시 존재하는 것이어야 한다. 문법은 CREATE VIEW와 거의 유사하다.

• DROP VIEW Statement

(맞게 해석했는지 모르겠지만) DROP 명령문에 존재하지 않는 뷰의 이름을 넣으면 그 뷰를 DROP할 수 없을 뿐만 아니라 존재하고 있는 모든 뷰 역시도 DROP된다는 에러가 발생한다.

If any views named in the argument list do not exist, the statement returns an error indicating by name which nonexisting views it was unable to drop, but also drops all views in the list that do exist.

• Using Views

1. View Syntax

CREATE VIEW

ALTER VIEW

DROP VIEW

2. View Processing Algorithms

MySQL의 CREATE VIEW나 ALTER VIEW 와 같은 ALGORITHM절은 표준 SQL에 대한 MySQL의 확장이다. ALGORITHM은 MERGE, TEMPTABLE, UNDEFINED 세 변수를 가진다.

MERGE는 뷰와 뷰 정의문을 참조하는 명령문의 문장은 명령문의 부분과 상응되는 뷰 대체 정의문 부분과 병합된다.

TEMPTABLE은 뷰에서 나온 결과는 임시 테이블 속으로 들어가게 되는데, 이것은 명령문을 실행하기 위해 사용된다.

UNDEFINED는 MySQL은 사용할 알고리즘을 선택한다. 가능하다면 MySQL은 MERGE를 선호하게 되는데, 왜냐하면 MERGE는 일반적으로 보다 효율적이며 또한 뷰는 임시 테이블이 사용되는 경우에는 업데이트를 할 수 없기 때문이다.

확실하게 TEMPTABLE가 선택되는 경우는 임시 테이블이 생성되고 임시 테이블이 명령문 수행을 마치기 위해 사용되기 전에 잠금이 언더라잉(underlying) 테이블에서 릴리즈될 수 있을 경우만 해당된다. 이 결과로 잠금 릴리즈가 MERGE알고리즘 보다 빨리 구현되며, 따라서 뷰를 사용하는 다른 클라이언트가 더 이상 갇히지 않게 된다.

뷰 알고리즘은 세 가지 이유로 UNDEFINED될 수 있다.

• CREATE VIEW명령문안에 ALGORITHM구문이 전혀 없다.
• CREATE VIEW명령문이 명확한 ALGORITHM = UNDEFINED구문을 가지고 있다.
• ALGORITHM = MERGE은 임시 테이블이로 처리되는 뷰에 대해서만 지정된다. 이 경우, MySQL은 경고문을 내 보내고 알고리즘을 UNDEFINED으로 설정한다.

3. Updatable and insertable Views

기본적으로 뷰는 읽기 전용으로 많이 사용되지만 어떤 뷰들은 업데이트가 가능하다. UPDATE, DELETE, INSERT와 같은 명령문에 있는 뷰들을 사용해서 언더라잉 테이블의 내용을 업데이트 할 수가 있다. 또한, 업데이트가 되지 않도록 뷰를 구성할 수도 있다. 보다 특징적으로 하기 위해서, 뷰는 아래의 것 중에 하나를 가지고 있으면 업데이트가 되지 않는다.

• Aggregate functions (SUM(), MIN(), MAX(), COUNT(), 등등)
• DISTINCT
• GROUP BY
• HAVING
• UNION또는 UNION ALL
• 선택한 리스트에 있는 서브 쿼리
• 조인(Join)
• FROM구문에 있는 업데이트할 수 없는(non-updatable) 뷰
• FROM구문에 있는 테이블을 참조하는 WHERE구문에 있는 서브쿼리
• 문자 값만을 참조 (이와 같은 경우, 업데이트할 언더라잉 테이블이 없다)
• ALGORITHM = TEMPTABLE (임시 테이블 사용은 항상 뷰를 업데이트 불가능 한 것으로 만든다)

삽입이 가능하게 끔 하는 것과 관련해서는 (INSERT명령문을 가지고 업데이트가 될 수 있도록), 업데이트가 가능한 뷰는 뷰 컬럼에 대해 아래의 부가적인 요구 사항이 만족된다면 삽입 가능해(insertable) 진다.

• 뷰 컬럼 이름이 중복 사용되지 말아야 한다.
• 뷰는 디폴트 값을 가지고 있지 않는 베이스 테이블에 있는 모든 컬럼을 가지고 있어야 한다.
• 뷰 컬럼은 단순 컬럼 참조(simple column reference)만 하고 컬럼을 가져오지는(derived column) 않는다. 획득하는 컬럼(derived column)은 수식에서 얻어지는 것이다. (예: 3.14159, col1 + 3, UPPER(col2), col3 / col4, (subquery))

4. The View WITH CHECK OPTION Clause

UPDATE 명령문이 LIMIT 구문을 가지고 있다면, 언더라잉 테이블에서 정의된 primary key에 대한 모든 컬럼을 뷰가 가질 수 없을 때 뷰에 대한 업데이트를 생성할 수 있는지를 결정하는 변수이다. 

5. View Metadata

SHOW CREATE VIEW 명령문을 사용하면 뷰의 메타데이터를 확인할 수 있다.

• 실습

뷰 생성

뷰 수정(호환성 문제로 한글 사용 지양)

뷰 삭제

뷰 정보 확인

primary key 등의 정보는 확인되지 않음

뷰의 소스코드 확인

데이터 수정

원래 테이블에서 김병만의 birthYear가 null이면 안되기 때문에 에러 발생

집계함수를 포함한 뷰 생성

IS_UPDATABLE이 NO로 설정되어 있음

키 177 이상인 사람들의 뷰 생성

해당되는 데이터가 없음

실행은 되어도 뷰의 조건에 맞지 않으므로 뷰에서는 보이지 않음

WITH CHECK OPTION

뷰 조건에 맞지 않으므로 김병만 에러 발생

JOIN 된 테이블의 뷰는 업데이트 불가능

select문에 들어가기 앞서...

RDBMS에서는 데이터베이스 조작을 위해 SQL문을 명령어로 사용한다.

SQL문을 세 가지로 분류할 수 있다.

SQL문의 특징이 있다.

• 대소문자를 구분하지 않는다: USE, use, uSE를 모두 동일하게 인식한다.
• MySQL의 주석: 주석으로 묶으면 글자들이 회색이 된다. 

-한줄 주석: --

-블록 주석: /* */

• 열 이름에 별칭(Alias)을 지정할 수 있다: select first_name as ‘이름’ from employees;
• 식별자(데이터베이스 개체의 이름)를 만들 때 규칙:

-a-z, A-Z, 0-9, $, _를 사용할 수 있다.

-최대 64자로 제한

-예약어를 사용할 수 없다.

-공백을 사용하려면 백틱(``)으로 묶어서 사용한다.

-의미 있는 이름을 짓되 너무 길거나 짧은 것은 좋지 않다.

DML 중 테이블의 행을 선택할 수 있는 SELECT문에 대해서 알아본다.

MySQL 5.7버전 document에서 가져온 SELECT문이다.

보이는 것이 SELECT문의 거의 전부이다.

가장 기본적으로 사용하는 것이 select * from tablename; 인데

이 외에도 사진에서 보듯 다양한 옵션을 붙일 수 있다.

DB를 먼저 선택한다.

employees DB에서 테이블의 정보를 확인한다.

• FROM

맨 처음 언급했던 가장 기본적으로 쓰이는 select * from tablename; 형태이다.

*는 모든 행을 뜻한다.

from은 지정한 테이블에서 찾는다는 의미이다.

• AS

as를 사용하면 그 행의 alias(별칭)을 지정할 수 있다. 보다 쉽게 불러오기 위하여..

• WHERE

where은 조건을 붙이는 것이다. DB 테이블에서 이름이 김경호인 행을 찾는다.

• 연산자 사용

다음은 연산자를 사용하여 행을 선택하는 것이다.

연산자의 종류는 두 가지로 나눌 수 있다.

• BETWEEN ... AND

between과 and를 사용하면 조건연산자를 사용하지 않고도 범위를 정할 수 있다.

• IN()

in은 or 조건연산자를 보다 쉽게 사용할 수 있게 한다.

• LIKE

like는 검색할 때 쓰는 예약어이다. 글자 앞뒤에 %를 붙이면 그 글자를 포함한 단어를 검색한다.

_종신이면 종신앞에 한 글자가 있는 단어를 찾는다.

• 서브쿼리

예를 들어 김경호보다 키 큰 사람을 선택하려고 한다.

김경호의 키는 177이다. 직접 김경호의 키를 알아놓은 후에 그 키를 설정해서 select할 필요가 없다.

서브쿼리를 사용하면 김경호의 키를 알 수 없어도 김경호보다 큰 사람을 선택할 수 있다.

• 서브쿼리 ANY

서브쿼리의 다른 예이다.

주소지가 경남인 사람보다 키가 큰 사람을 찾으려고 서브쿼리를 사용했더니 오류가 발생했다.

오류문을 자세히 읽어보면 값이 1개보다 많다고 한다. 주소지가 경남인 사람이 1명보다 더 많기 때문인 것이다.

이러한 경우에는 서브쿼리 앞에 any를 붙여주면 주소지가 경남인 사람들 모두보다 키가 큰 사람들을 선택할 수 있는 것이다.

• 서브쿼리 =ANY

주소지가 경남인 사람들과 키가 같은 사람들을 선택하고자 한다.

height=any(서브쿼리)를 했더니 두 사람의 값이 나왔다.

=any 대신 in을 썼더니 같은 값을 출력하였다. 주소지가 경남인 사람과 키가 같은 사람 1이거나 2인 사람 모두를 출력한 것이다.

• ORDER BY

DESC는 내림차순, ASC는 올림차순이다. ASC가 디폴트이다.

• DISTINCT

중복된 값을 제거하고 선택한다.

• LIMIT

데이터가 30만 개나 되는 행을 다 출력할 수는 없다. 지정된 개수만 선택할 수 있다.

지정할 개수와 시작점을 설정할 수 있다.

• 테이블을 복사하여 CREATE

한 테이블을 복사한 새로운 테이블을 생성할 수 있다. 기존 테이블의 특정 열만 선택하여 생성할 수도 있다.

단, PK나 FK 등 제약조건은 복사할 수 없다.

• GROUP BY

userid로 묶어서 선택한 값들은 볼 수 있다.

sql문을 사용할 때 간단한 사칙연산도 할 수 있는 것을 아는가?

이를 이용한 것이 집계함수이다.

집계함수를 사용하여 키가 가장 큰 사람과 작은 사람을 선택하고자 한다.

첫 번째 명령문처럼 단순히 이름, 키의 최대값, 최소값을 선택하였더니 예상대로 나오지 않는다.

다시 이름별로 지정하여 SQL문을 실행하여도 원하는대로 나오지 않는다.

최대값과 최소값을 서브쿼리를 사용하여 설정하였더니 원하는대로 값을 확인할 수 있었다.

• HAVING

총 구매액 중 1000을 넘은 사람을 선택하고자 한다.

WHERE절을 사용하였지만 오류가 발생한다.

집계함수에 대한 조건을 설정할 때에는 HAVING을 사용해야 한다.

• ROLLUP

이름별로 중간합계를 내고 싶을 때 사용할 수 있다.

맨 처음 보여주었던 MySQL 5.7 document의 SELECT문의 거의 모든 것이다.

나도 실습하면서 SELECT문을 아는 데 많이 도움이 되었다.