MySQL 고급 쿼리 문장의 심층 탐구와 실제 적용

목차

  • 소개
  • 1. 조건 쿼리
  • 2. 정렬 키워드
  • 3. 그룹화 및 집계 함수
  • 4. 쿼리 제한
  • 5. 별칭
  • 6. 서브쿼리
  • 7. 뷰
  • 8. 조인 쿼리
  • 9. 저장 프로시저

소개

데이터베이스 관리 및 데이터 분야에서 MySQL 고급 쿼리 기술을 숙달하는 것은 매우 중요한 역량입니다. 본 기사에서는 데이터 검색 효율성과 유연성을 향상시키기 위해 MySQL의 복잡한 쿼리 문장을 깊이 있게 탐색합니다.

이전 MySQL 기본 작성에서는 MySQL의 기본 쿼리 방법을 소개했으며, 본문에서는 주로 쿼리의 복잡한 문장과 조건의 조합 사용법을 소개합니다.

1. 조건 쿼리

다양한 where 조건에 따라 데이터를 쿼리할 수 있습니다.

1.1 비교 연산자 쿼리

주로 사용되는 비교 연산자는 다음과 같습니다.

비교 연산자 기능
> 또는 != 보다 큼
>= 보다 크거나 같음
< 보다 작음
<= 보다 작거나 같음
= 같음
<> 또는 != 같지 않음
BETWEEN ... AND ... 특정 범위 내(최소, 최대값 포함)
IN(..) in 뒤의 목록에 있는 값
LIKE 와일드카드 패턴 일치(_ : 단일 문자, % : 임의의 문자)
IS NULL NULL 값인 경우

1.1.1 패턴 일치 쿼리 사용

테이블의 원본 데이터를 먼저 살펴보겠습니다.

각각 age 값이 30인 데이터, age 값이 40보다 큰 데이터, age 값이 40보다 작은 데이터를 찾아봅니다.

각각 age 값이 45보다 크거나 같은 데이터, age 값이 45보다 작거나 같은 데이터, age 값이 45와 같지 않은 데이터를 찾아봅니다.

1.1.2 범위 검색

BETWEEN ... AND ...를 사용하여 쿼리할 값의 범위를 지정합니다. 예를 들어 age 값이 30부터 50 사이인 데이터를 찾으려면 30과 50을 포함합니다.

in을 사용하여 여러 값을 일치시킵니다. 예를 들어 age 값이 30과 45인 데이터를 보려고 합니다.

like를 사용하여 패턴을 일치시킵니다.

  • %: 뒤에 임의의 단일, 여러 또는 빈 문자가 있음을 의미합니다.
  • _ : 뒤에 임의의 단일 문자가 있음을 의미합니다.

필드 값이 비어 있는 데이터를 찾습니다.

1.2 논리 연산자

논리 연산자 기능
AND 또는 && 그리고(여러 조건이 동시에 만족)
OR 또는 || 또는(여러 조건 중 하나라도 만족)
NOT 또는 ! 아니요, 아님

1.2.1 AND, 조건 조합 쿼리

사용법: select 필드 from 테이블명 where 조건1 and 조건2;

1.2.2 다중 조건 쿼리

사용법: select 필드 from 테이블명 where 조건1 or 조건2;

1.2.3 역순 쿼리

사용법: select 필드 from 테이블명 where !조건;

2. 정렬 키워드

필드 값을 정렬할 수 있으며, 숫자 또는 문자일 수 있습니다.

기본 구문: select 필드1, 필드2, ... from 테이블명 [where 조건] order by 정렬필드1,정렬필드2, ... [asc|desc]

키워드 정렬 방식
asc 오름차순 정렬, 기본 정렬 방식 asc는 생략 가능
desc 내림차순 정렬

참고: 다중 필드 정렬의 경우, 첫 번째 필드 값이 동일할 때만 두 번째 필드에 따라 정렬됩니다.

2.1 숫자 정렬

2.2 문자 정렬

필드 값의 첫 글자에 따라 정렬합니다.

2.3 데이터 압축

중복되지 않은 레코드를 보기 위해 데이터 압축합니다.

distinct 명령어를 사용하여 중복 정보를 필터링합니다.

3. 그룹화 및 집계 함수

특정 열 또는 여러 열 데이터를 기반으로 통계를 수행해야 할 때 그룹화 쿼리와 집계 함수가 사용됩니다. 그룹화의 키워드는 **group by**이며, group by는 일반적으로 집계 함수와 함께 사용됩니다. 일반적인 집계 함수는 다음과 같습니다.

함수 기능
count 개수 세기
max 최대값
min 최소값
avg 평균값
sum 합계

기본 구문:

select 필드명, 집계함수(필드명) from 테이블명 [where 조건] GROUP BY 필드명;

전체 테이블 데이터는 다음과 같습니다.

먼저 집계 함수의 사용법을 이해해 봅시다.

select 집계함수(필드) from 테이블명

count 함수는 값의 총 개수를 세며, 테이블에 있는 값의 수만 통계하고 null은 통계 범위에 포함되지 않습니다.

3.1 그룹화 쿼리 데이터 사용

첫 번째 예시는: 각(grender) 직원의 성별을 반환하는 쿼리입니다. 각 **성별**이 그룹을 구성하며, 함수는 각 그룹 내의 직원 성별 수를 통계합니다.

두 번째 예시는: 각 직원의 **성별(grender)** 및 **근무지(workid)**를 반환하는 쿼리입니다. 각 **근무지**가 그룹을 구성하며, 함수는 각 그룹 내의 직원 성별 수를 통계합니다.

4. 쿼리 제한

MySQL에서 LIMIT 키워드는 SELECT 문이 반환하는 레코드 수를 제한하는 데 사용됩니다.

기본 사용법은 두 가지가 있습니다. 

select 필드... from 테이블명 limit 행수;
# 첫 번째 행부터 지정된 행 수까지 데이터를 쿼리합니다

select 필드... from 테이블명 limit 행수1,행개수2;
# 행수1의 다음 행부터 쿼리를 시작하여 지정된 행 수까지 데이터를 쿼리합니다
# 예: select 필드... from 테이블명 limit 3,5;
# 3번째 행부터 아래로 5행을 쿼리합니다. 즉, 4,5,6,7,8행의 데이터입니다

limit는 일반적으로 order by와 함께 사용되어 레코드 수를 제한하면서 정렬의 정확성을 보장합니다.

예를 들어 age(나이)가 가장 높은 5명의 직원을 쿼리합니다.

5. 별칭

MySQL에서 쿼리 별칭(Alias)은 테이블 이름이나 열에 임시 이름을 부여하는 방식으로, 복잡한 쿼리를 처리하거나 여러 테이블을 연결할 때 SQL 쿼리를 더 간결하고 읽기 쉽게 만들 수 있습니다. 별칭은 일반적으로 쿼리에서 AS 키워드를 정의하여 사용하지만, 대부분의 경우 AS 키워드는 생략할 수 있습니다. 

select 필드명 [as] 별칭 from 테이블명;
# 열에 별칭 설정

select 테이블별칭.필드명 from 테이블명 [as] 테이블별칭;
# 테이블에 별칭 설정

5.1 열 별칭 설정

열에 별칭을 설정하여 데이터의 가독성을 높입니다.

5.2 테이블 별칭 설정

테이블에 별칭을 설정하여 다른 테이블 간의 동일한 필드를 구분합니다. 주로 다중 테이블 연결 시 사용됩니다.

5.3 as 연결 문장

as는 연결 문장으로 사용되어 테이블의 데이터를 새 테이블에 삽입할 수 있습니다.

이 방법은 이전에 소개한 테이블 복제와 기본적으로 동일하며, 테이블 제약 조건을 저장할 수 없습니다.

6. 서브쿼리

서브쿼리(Subquery)는 SQL 쿼리에 중첩된 쿼리 문장으로, 내부 쿼리를 먼저 실행한 후 결과를 외부 쿼리의 일부로 처리합니다. 서브쿼리는 일반적으로 비교 연산자의 오른쪽에 배치되거나 IN, ANY, ALL, EXISTS 등 키워드 뒤에 사용됩니다.

서문은 주문문과 동일한 테이블을 쿼리할 수도 있고 다른 테이블일 수도 있습니다.

기본 구문: 주문문(서문)

6.1 IN 키워드를 사용한 서브쿼리

테이블 데이터를 쿼리할 때 서문 쿼리한 데이터를 주문문의 매개변수로 사용하여 쿼리 테이블을 일치시킵니다.

서브쿼리를 사용하여 emp 테이블의 id,name 필드에서 grender 값이 '남성'인 모든 데이터를 쿼리합니다.

이 SQL 쿼리는 emp 테이블에서 id와 name 열을 선택하며, 조건은 id가 다른 서브쿼리에 존재하는 것입니다. 서브쿼리는 emp 테이블에서 성별이 '남성'인 모든 직원의 id를 선택합니다. 

select id,name from emp where id in (select id from emp_user where grender='남성');
# 주문문: select id,name from emp where id
    # select id, name: emp 테이블에서 id와 name 이 두 열의 데이터를 선택합니다.
    # from emp: 쿼리할 테이블을 emp로 지정합니다. 이는 직원 테이블입니다.
    # where id: 조건을 id로 지정합니다

# in: 문장을 연결합니다
# not in: 문장을 연결하고 반대 값을 반환합니다

# 서문: (select id from emp_user where grender = '남성');
    # grender 값이 '남성'인 모든 데이터의 id 값을 표시합니다
# 주문문 부분 where id in (...)는 서브쿼리 결과 집합에 있는 id를 가진 직원만 선택합니다.

서브쿼리를 사용할 때 동시에 수정, 삭제를 수행할 수 있습니다.

6.1.1 데이터 수정

예를 들어 id 필드 값이 5인 데이터의 age 값을 30으로 수정합니다.

6.1.2 데이터 삭제

데이터 삭제는 데이터 수정 방법과 동일합니다.

주의: 수정 또는 데이터 삭제 전에 먼저 확인해야 합니다. 삭제할 데이터가 많은 경우 확인 후 백업을 먼저 수행한 후 삭제하는 것이 좋습니다.

6.2 비교 연산자를 사용한 서브쿼리

비교 연산자를 사용하여 id 값이 4보다 큰 데이터를 찾고, 해당 id, name 필드 정보를 표시합니다.

이 문장은 먼저 서문을 실행하여 id=4인 데이터를 쿼리하고 id 값만 표시합니다. 즉, 4입니다.

그런 다음 주문문을 실행하여 id=4의 값을 비교하여 where 조건은 id > 4가 되며, 마지막으로 id 필드 값이 4보다 큰 데이터를 표시합니다.

마찬가지로 함수 통계도 수행할 수 있습니다.

6.3 EXISTS를 사용한 서브쿼리

MySQL에서 EXISTS 서브쿼리는 특정 조건을 만족하는 행이 존재하는지 테스트하는 데 사용됩니다. 서브쿼리는 특정 데이터 행을 반환하지 않으며, 부울 값(TRUE 또는 FALSE)을 반환합니다. 이 부울 값에 따라 주문문이 행을 포함할지 결정합니다.

7. 뷰

뷰는 하나 또는 여러 테이블을 기반으로 한 SQL 쿼리 결과의 가상 테이블입니다. 뷰는 실제로 데이터를 저장하지 않고 쿼리 정의를 저장합니다. 뷰를 쿼리할 때 데이터베이스 엔진은 뷰 정의에 따라 기본 SQL 쿼리를 실행합니다.

7.1 뷰의 역할

뷰의 주요 역할은 하나의 테이블이나 여러 테이블의 데이터를 다시 논리적으로 분할하여 다양한 종류 및 범위의 데이터를 제공하고, 다양한 권한이 필요한 고객 또는 관련 인원이 접근할 수 있도록 하는 것입니다.

7.2 뷰의 특징

뷰의 주요 의미는 select 쿼리 결과 집합을 저장하여 논리적 테이블로 만드는 것입니다. 결과 집합의 데이터 매핑과 유사하며 디스크 공간을 차지하지 않습니다. 기본 테이블의 데이터를 수정하면 뷰의 데이터도 수정됩니다.

뷰의 또 다른 특징은 쿼리 속도가 빠르며, 원하는 쿼리 데이터를 빠르게 위치시킬 수 있어 반복적인 복잡한 조건 검색이나 다중 테이블 쿼리가 필요하지 않습니다.

7.3 뷰 생성

7.3.1 단일 테이블로 뷰 생성

기본 구문: create view 뷰이름 as select 필드1,필드2,... 테이블명 [where 조건];

예를 들어 하나의 테이블에 대해 다양한 논리적 분할을 수행합니다.

새로 생성된 뷰는 논리적 테이블이며 디스크 공간을 차지하지 않습니다. 새로 생성된 뷰는 테이블 값의 관련 필드를 저장하지만 제약 조건은 저장하지 않습니다. 예를 들어 기본 키, 고유 키 등이 있습니다.

기본 테이블의 데이터를 수정하면 뷰의 데이터도 변경됩니다. 기본 테이블을 삭제하면 뷰의 값도 비어집니다.

뷰를 삭제하면 기본 테이블 자체에는 영향을 미치지 않지만, 뷰 데이터를 수정하면 기본 테이블의 데이터도 수정됩니다.

7.3.2 다중 테이블로 뷰 생성

다중 테이블 뷰 생성 구문:

create view 뷰이름 as select 필드1,필드2,... 테이블명1,테이블명2.... [where 조건];

또는

create view 뷰이름(필드1,필드2,... ) as select 필드1,필드2,... 테이블명1,테이블명2.... [where 조건];

뷰 보기

뷰에서 테이블 구조에 제약 정보가 표시되지 않지만, 기본 테이블에 있는 제약 정보를 수정할 수는 없습니다. 예를 들어 기본 키 값은 수정할 수 없습니다. 다른 제약이 없는 뷰 데이터는 정상적으로 수정할 수 있으며 기본 테이블의 데이터에도 영향을 미치지만 기본 키 값은 수정할 수 없습니다.

이는 데이터 보안을 크게 향상시키며 다른 사용자가 데이터를 조회할 수만 있도록 허용합니다.

8. 조인 쿼리

MySQL의 조인 쿼리(JOIN)는 두 개 이상의 테이블에서 관계에 따라 행을 단일 결과 집합으로 병합하는 과정입니다. 조인 쿼리는 주어진 조건에 따라 테이블 간의 관계를 설정하여 여러 테이블에서 관련 데이터를 가져옵니다.

예를 들어 name 필드를 지정하여 두 테이블의 데이터를 연결하면 두 테이블에서 동시에 관련 데이터를 가져올 수 있습니다.

조인 쿼리는 주로 **내부 조인, 왼쪽 조인, 오른쪽 조인**으로 나뉩니다.

먼저 두 개의 테이블을 생성하고 해당 데이터를 추가합니다.

8.1 내부 조인

내부 조인은 두 테이블에서 일치하는 값만 반환합니다.

MySQL의 내부 조인은 두 개 이상의 테이블에서 동시에 특정 조건을 만족하는 데이터 레코드의 조합입니다. 일반적으로 FROM 절에서 INNER JOIN 키워드를 사용하여 여러 테이블을 연결하고 ON 절을 사용하여 조건을 설정합니다. 내부 조인은 시스템의 기본 테이블 연결이므로 FROM 절 뒤에 INNER 키워드를 생략하고 JOIN 키워드만 사용할 수 있습니다.

기본 구문: **select 필드... from 테이블1 [inner] join 테이블2 on 테이블1.필드= 테이블2.필드;** 

select a.id,a.name,a.grender,b.age,b.workip 
# 쿼리할 필드
#a.id: 뒤의 문장에서 테이블 이름 또는 별칭이 a인 id 필드의 값을 표시합니다.
#b.age: 뒤의 문장에서 테이블 이름 또는 별칭이 b인 age 필드의 값을 표시합니다

from user1 as a join user2 as b 
#user1 테이블의 별칭을 a로 설정합니다; user2 테이블의 별칭을 b로 설정합니다. 두 테이블에서 데이터를 가져옵니다


on a.name=b.name;
# 조건 판단, 두 테이블의 name 필드가 같은 데이터만 표시합니다

8.2 왼쪽 조인

**왼쪽 조인(LEFT JOIN / LEFT OUTER JOIN 사용)** 왼쪽 조인은 왼쪽 테이블(첫 번째 테이블)의 모든 행과 오른쪽 테이블(두 번째 테이블)과 일치하는 행을 반환합니다. 오른쪽 테이블에 일치하는 행이 없으면 결과에서 오른쪽 테이블의 열은 NULL로 표시됩니다.

8.3 오른쪽 조인

**오른쪽 조인(RIGHT JOIN / RIGHT OUTER JOIN 사용)** 오른쪽 조인은 왼쪽 조인과 반대로 오른쪽 테이블의 모든 행과 왼쪽 테이블과 일치하는 행을 반환합니다. 왼쪽 테이블에 일치하는 행이 없으면 결과에서 왼쪽 테이블의 열은 NULL로 표시됩니다.

9. 저장 프로시저

MySQL 저장 프로시저(Stored Procedure)는 데이터베이스 서버에 저장된 미리 컴파일된 SQL 문 집합으로, 사용자가 특정 작업을 수행하는 SQL 명령 그룹을 정의할 수 있게 합니다. 저장 프로시저는 조건문, 반복문, 변수 선언, 함수 호출 및 기타 복잡한 논리를 포함할 수 있어 코드 재사용성과 데이터베이스 작업 효율성을 향상시킵니다.

9.1 주요 역할

  1. 자주 사용되는 SQL 문을 한곳에 모아 사용할 때 직접 호출하여 반복 작업을 피하고 작업 효율성을 크게 향상시킵니다. 쉘 스크립트와 유사합니다.
  2. 데이터베이스 처리 속도를 높이고 실제 응용 프로그램에서 데이터베이스의 유연성을 향상시킵니다.

9.2 저장 프로시저의 장점

  1. 코드 재사용: 한 번 정의하여 여러 번 호출하여 동일한 SQL 문을 반복 작성하는 번거로움을 피합니다.
  2. 성능 향상: 저장 프로시저는 서버 측에서 미리 컴파일되어 실행 효율성이 더 높고 네트워크 전송 데이터량을 줄입니다.
  3. 모듈화 프로그래밍: 복잡한 데이터베이스 작업을 저장 프로시저에 캡슐화하여 코드 유지 관리 및 관리에 유리합니다.
  4. 보안 제어: 저장 프로시저에 대한 권한 부여를 통해 사용자의 데이터베이스 작업 권한을 더 정밀하게 제어할 수 있습니다.
  5. 트랜잭션 처리: 저장 프로시저 내부에 트랜잭션 제어를 포함할 수 있어 데이터의 일관성과 무결성을 보장합니다.

9.3 매개변수 없이 저장 프로시저 생성

저장 프로시저 생성의 기본 문장:

**delimiter 사용자 정의 종결자**

**create procedure 저장프로시저이름()**

**SQL 문장 ...... ......**

**end 종결자**

**delimiter ;** 

delimiter @@
'MySQL 클라이언트의 문장 종결자를 @@로 수정하여, 저장 프로시저 생성 시 기존의 세미콜론(;)과 충돌하는 것을 방지합니다. SQL 문장에서 세미콜론은 일반적으로 문장의 끝을 나타내는 데 사용되기 때문입니다. 저장 프로시저 생성 후 다시 세미콜론으로 복원합니다.'

create procedure store()
'store라는 이름의 저장 프로시저를 정의합니다. 저장 프로시저 이름은 store이며 입력 또는 출력 매개변수가 없습니다.'

begin 
'저장 프로시저 본체의 시작을 표시합니다.'

create table if not exists bg(id int(4),name varchar(15),age int(3));
'SQL 문장: 테이블 생성
하지만 저장 프로시저의 주요 목적은 SQL 로직을 캡슐화하는 것이지 데이터베이스를 생성하는 것이 아닙니다.'

insert into bg values (1,'sunwukong',28);
'SQL 문장: bg 테이블에 데이터 삽입'

select * from bg;
'SQL 문장: bg 테이블의 모든 데이터 조회'

end @@
'저장 프로시저 본체의 끝을 표시합니다.'

delimiter ;
'MySQL 클라이언트의 문장 종결자를 세미콜론(;)으로 복원합니다. 참고: 세미콜론(;)과 delimiter 사이에 공백이 있습니다'

9.4 저장 프로시저 호출

call 명령어를 사용하여 호출합니다. 기본 구문은 다음과 같습니다: call 저장프로시저이름();

저장 프로시저를 호출하면 내의 모든 SQL 문장이 실행됩니다.

9.5 저장 프로시저 조회

사용: show create procedure [저장프로시저이름];를 사용하여 조회합니다.

저장 문장이 너무 많으면 저장 프로시저의 관련 정보만 볼 수 있습니다.

사용: SHOW PROCEDURE STATUS

9.6 매개변수를 사용하여 저장 프로시저 생성

저장 프로시저의 매개변수는 정의 시 설정된 입력, 출력 또는 입력 출력 변수를 의미합니다. 이러한 매개변수는 저장 프로시저를 호출할 때 데이터를 저장 프로시저 내부로 전달하거나 저장 프로시저에서 호출자에게 데이터를 반환하는 데 사용됩니다.

9.6.1 입력 매개변수(IN)

입력 매개변수는 저장 프로시저를 호출할 때 저장 프로시저 내부로 값 전달을 허용합니다. 저장 프로시저 내에서 이러한 매개변수는 입력 데이터로만 사용할 수 있으며, 호출자에게 반환하기 위해 저장 프로시저에서 수정할 수 없습니다.

호출 매개변수 구문: call 저장프로시저이름(입력매개변수)

이 저장 프로시저를 호출하는 것은 다음과 같이 실행하는 것과 동일합니다: select * from user2 where workip=입력매개변수

9.6.2 출력 매개변수(OUT)

출력 매개변수는 저장 프로시저에서 호출자에게 값을 반환하는 데 사용됩니다. 호출 전에 호출자가 출력 매개변수에 값을 할당할 필요는 없으며, 저장 프로시저가 이러한 매개변수에 값을 할당하는 책임을 집니다.

in empid int(4): empid는 정수(int) 유형의 입력 매개변수이며 길이는 4바이트입니다. 이 매개변수에 전달할 정수 값을 제공해야 합니다.

out out_ename varchar(50): out_ename은 가변 길이 문자열(varchar) 유형의 출력 매개변수이며 최대 길이는 50자입니다. 저장 프로시저 실행이 완료되면 이 매개변수를 통해 결과가 호출자에게 반환됩니다.

select name into out_ename from user1 where id = empid; 이것은 저장 프로시저 내의 SQL 문장입니다. 이 문장은 쿼리를 실행하여 user1 테이블에서 empid 매개변수와 일치하는 레코드의 name 필드를 선택하고 쿼리 결과를 출력 매개변수 out_ename에 저장합니다.

이 저장 프로시저를 호출합니다.

9.6.3 입력 출력 매개변수(INOUT)

입력 출력 매개변수는 입력으로도 출력으로도 사용할 수 있습니다. 저장 프로시저를 호출할 때 입력 출력 매개변수에 초기 값을 부여할 수 있으며, 저장 프로시저는 필요에 따라 이 값을 수정하고 호출자에게 수정된 값을 반환할 수 있습니다.

9.7 저장 프로시저 삭제

drop 명령을 사용하여 저장 프로시저를 삭제할 수 있습니다.

기본 구문: drop procedure [if exists] 저장프로시저이름;

태그: MySQL 고급쿼리 서브쿼리 조인 뷰

6월 20일 06:33에 게시됨

인기 태그

©2026 괴물 클럽