MySQL 03. 문법(2)(서브 쿼리, View..등)

MySQL

by 아리벱 2024. 3. 29. 12:02

저번시간에 이어서 MySQL 문법을 알아보도록 하겠습니다.

MySQL 기본 문법은 SQL 다른 서비스와 동일하지만 함수는 MySQL과 상이할 수 있습니다. 이 점 유념해서 문법들을 살펴볼게요.

맨 처음!

어떤 데이터베이스를 사용할 지 지정하고 시작해야 합니다!

1. 문자열 다루기

1-1. concat 함수

- 복수의 문자열을 연결해주는 함수

select concat             ('안녕하세요!', 'MySQL')

select concat       (address1, ' ', address2)

1-2. left, right 함수

- 왼쪽 또는 오른쪽에서 길이만큼 문자열을 가져옴

1-3. substring 함수

- 문자열의 일부를 가져옴

1-4. char_length

- 문자열의 길이를 반환 (파이썬에서의 len()와 동일한 역할)

1-5. lpad, rpad

왼쪽 또는 오른쪽의 해당 길이만큼 늘리고, 빈 공간을 채울 문자열을 반환

lpad로 인해 ABCDEFG 왼쪽 3자리를 0으로 채웁니다.

rpad로 인해 userid의 문자열이 끝나면 문자열 오른쪽 남은 칸을 *로 채웁니다.

1-6. ltrim, rtrim, trim

왼쪽, 오른쪽, 모든 공백을 제거(파이썬에서의 strip과 비슷한 역할)

1-7. replace

문자열에서 특정 문자열을 반환

2. Union 연산자

합집합을 나타내는 연산자로 중복된 값을 제거함

* 서로 같은 종류(필드가 같아야 함)이어야 유니온 연산자 사용 가능

유니온 연산자 사용 예를 들기 위해서 두 개의 테이블을 새로 생성해보겠습니다.

두 테이블 사이에 유니온 연산자를 삽입해서 연산해보겠습니다.

product_new에 밀키트 데이터를 삽입하여 product테이블에 동일한 값을 입력해보겠습니다. 위에서 저희는 union연산자는 중복을 제거하는 역할이 있다고 배웠습니다. 확인해보겠습니다!

결과는 두 개의 데이터 모두 출력되었습니다. 왜 그럴까요??

그 이유는 regdate를 제외한 데이터는 동일하지만 regdate의 데이터는 상이하기 때문입니다. now()를 insert하였는데 insert 쿼리를 실행한 시간이 서로 다르기 때문에 sql에서는 서로 다르다고 인식했기 때문에 두 개의 데이터 모두 출력이 된 것입니다.

그렇다면 regdate를 제외한 나머지 필드의 데이터를 select하게 되면 중복 데이터라고 인식할까요??

네 맞습니다. 밀키트 데이터가 하나만 출력된 것을 확인할 수 있습니다.

union은 중복 데이터를 제거한다고 잘 알고 있습니다. 여기에 더해 union all이라는 연산자도 존재합니다. union all은 중복데이터 또한 모두 출력해줍니다.

3. Sub Query(서브 쿼리)

서브 쿼리가 쓰이는 경우를 살펴보고 서브 쿼리 사용법을 이해봅시다!

문제의 내용을 나누어서 생각하면 이해하기 쉽습니다.

1. 상품코드가 '00001'의 가격보다 크거나 같은 가격을 가지고 있는 상품.

2. 상품의 모든 정보 출력

2번의 상품에는 1번이 내포되어 있는 관계입니다.

따라서 2번 쿼리문에 괄호를 통해 1번 쿼리를 삽입하여 사용할 수 있습니다.

위의 서브쿼리를 사용한 결과를 확인해보겠습니다.

price 40000보다 크거나 같은 상품들이 모두 출력된 것을 확인할 수 있습니다.

위의 경우도 문제를 나눠서 생각합니다.

1. product 테이블에서 모든 사품의 code, name, price, 가장비싼가격를 모두 출력

2. 가장비싼가격

1번에서 2번을 내포하고 있습니다.

2번은 select max(price) from product;로 추출할 수 있습니다.

1번의 쿼리문은 select code, name, price, 가장비싼가격 from product;입니다.

여기에 가장비싼가격 = select max(price) from product를 대입하면 됩니다.

4. auto_increment

필드에 identitiy한 숫자를 자동으로 부여

orders 테이블을 생성하여 auto_increment의 쓰임에 대해서 살펴보겠습니다.

1, 10번까지는 숫자를 지정해주고 apple부터 no에 값을 지정해주지 않았습니다. no에 어떤 값이 출력될 것으로 기대되나요??

10번 이후의 숫자가 1씩 증가하면서 숫자가 자동으로 할당되었습니다.

예제들을 통해서 위에 MySQL 문법 내용들을 복습해보겠습니다.

쿼리문을 작성하기 쉽게 문제를 나누어 생각하겠습니다.

1. 상품을 최소 2번이상 구입한 횟수가 있는 회원

2. 회원의 아이디와 이름, 성별을 출력

이번에는 2번 안에 1번이 내포되어 있는 관계입니다. 이런 경우에 서브 쿼리를 사용합니다.

1번을 위해 다음과 같은 명령문을 작성할 수 있습니다.

select userid from orders group by userid having count(no) >= 2;

2번을 위해서 다음과 같은 명령문을 작성할 수 있습니다.

select userid, name, gender from member;

1번을 2번에 대입하려고 보니 '회원의'의 회원에 대입한 후 '의'를 나타내는 쿼리가 필요합니다. where문을 이용하여 조건을 표현할 수 있습니다.

select userid, name, gender from member where userid in (select userid from oders group by userid having count(no) >= 2);

위의 문제는 전에 배웠던 조인만을 이용하거나 조인과 서브쿼리를 이용하여 코드를 작성해볼 수 있습니다.

# 조인을 이용하여 코드 작성

member 테이블에 userid, name 필드가 존재합니다.

orders 테이블에 no가 존재합니다. no를 count()하여 구입횟수를 알 수 있습니다.

orders 테이블의 userid의 데이터는 중복된 경우가 존재함을 유념해서 select 쿼리를 작성합니다.

# 조인과 서브쿼리를 이용하여 코드 작성

(select userid, count(no) as ocnt from orders group by userid having count(no) >= 2)의 sub별명을 붙입니다. 이는 상품을 최소 2번 이상 구입한 userid와 count(no)를 출력하는 쿼리입니다. 이 sub의 count(no)를 출력해야 하므로 sub.ocnt라고 작성합니다.

서브 쿼리를 이용하여 해당 테이블의 데이터를 복사하여 다른 테이블에 입력할 수 있습니다.