[MySQL] 문자열 함수, 유니온, 서브쿼리, 복사

---

MySQL 문자열 함수

concat

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

select * from MK_member;

 

현재 MK_member 테이블이 이렇다고 하자.

---

concat 함수의 사용

select concat('안녕하세요', '마이에스큐엘') as con;

 

---

MK_member 테이블에 적용

select concat(address1, ' ', address2, ' ', address3) as address from MK_member where userid='apple';

---

left, right

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

select left('안녕하세요반갑습니다', 5);
select userid, left(userid, 2) as 'left' from MK_member;

---

substring

문자열의 일부를 가져옴, 중간도 가능

-- substring(문자열, 시작위치): 시작위치부터 끝까지
-- substring(문자열, 시작위치, 길이): 시작위치부터 길이만큼
select substring('ABCDEFGHIJKLMN', 5) as sub; -- 5번째부터 끝까지
select substring('ABCDEFGHIJKLMN', 5, 3) as sub;

---

char_length

문자열의 길이를 반환

select char_length('ABCDEFGHIJKLMN') as cnt;
select email, char_length(email) as len from MK_member;

---

lpad, rlad

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

-- lpad(문자열, 총길이, 채울 문자열)
select lpad('ABCDEFG', 10, '0') as pad;
select point, lpad(point, 5, 0) as pad from member;

---

ltrim, rtrim, trim

왼쪽, 오른쪽, 모든 공백을 제거

select ltrim('     ABCDEF     ') as ltrim;
select trim('     ABCDEF     ') as trim;

---

replace

-- replace(문자열, 대상, 바꿀 문자열)
select replace('ABCDEFG', 'CD', ',') as repl;

---

유니온

유니온은 합집합을 나타내는 연산자로, 중복된 값을 제거한다.

서로 같은 종류의 테이블(칼럼이 같아야 함) 에서만 적용이 가능하다.

-- select 컬럼명1, 컬럼명2, .. from 테이블1 union select 컬럼명1, 컬럼명2, .. from 테이블2

 

유니온을 적용해보기 위해 두 개의 테이블을 생성한다.

 

create table product(
    code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);


create table product_new(
    code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

두 테이블의 컬럼은 같아야 한다.

각 테이블에 값들을 넣어준다.

insert into product values('00001', '닌텐도 스위치', '동숲 하고싶어요!', 500000, now());
insert into product values('00002', '그래픽 카드', '4090', 1200000, now());
insert into product values('00003', '무선 이어폰', '노이즈 캔슬링', 200000, now());
insert into product values('00004', '플립5', '접혀요', 1300000, now());
insert into product values('00005', '모니터', '오딧세이 32', 700000, now());

insert into product_new values('10001', '자동차', '포르쉐911', 210000000, now());
insert into product_new values('10002', '집', '한남더힐', 2000000000, now());
insert into product_new values('10003', '젤리', '하리보', 3000, now());
insert into product_new values('00004', '플립5', '접혀요', 1300000, now());

---

union

select code, name, price, regdate from product
union
select code, name, price, regdate from product_new;

플립5 항목은 중복된 항목이다. union은 중복을 없애기 위해 사용하는데, union을 사용했지만 중복된 이유는 regdate에 있다.

시간값은 매 순간 다르기 때문에 두 항목은 다른 항목으로 생각된다.

 

regdate 항목을 제외하고 union을 사용해보았다.

select code, name, price from product
union
select code, name, price from product_new;

 

 

regdate 컬럼이 사라지고 플립5가 중복 없이 잘 들어가있는 것을 알 수 있다.

 

중복을 유지하는 방법도 있다.

 

select code, name, price from product
union all
select code, name, price from product_new;

이처럼 union all을 사용하면 두 테이블에 중복되는 항목이더라도 합쳐서 보여준다.

---

서브쿼리

• 다른 쿼리 내부에 포함되어 있는 select 문
• 서브쿼리를 포함하는 쿼리를 외부쿼리, 서브쿼리는 내부쿼리
• 서브쿼리는 괄호()를 사용하여 표현
• select, where, from, having 절 등에서 사용할 수 있음.

---

where 절에서의 서브쿼리

select price from product where code='00001';

코드가 '00001'인 상품의 가격을 출력하는 코드이다.

 

만약 이 상품의 가격보다 크거나 같은 가격을 가지고 있는 상품의 모든 정보를 출력하고자 한다면 어떻게 해야할까?

 

select * from product where price >= (select price from product where code='00001');

where 조건절에 서브쿼리를 사용하여 비교할 값을 설정해준다.

출력: price가 '00001' 상품의 price보다 크거나 같은 모든 상품

---

select 절에서의 서브쿼리

select code, name, price, max(price) as max_price from product;
# Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'kdt.product.code'; this is incompatible with sql_mode=only_full_group_by

위와 같이 코드를 작성하면 에러가 발생한다. max()함수가 집계함수이기 때문이다. 집계함수는 그룹또는 집계함수로 사용해야한다.

( max() 자체는 최대값 하나이므로 전체 항목에서의 최대값 하나 라고 생각하면 구문이 이상함을 느낄 수 있을 것이다.)

 

따라서 max(price) 부분을 서브쿼리로 작성해보자

selcet code, name, price, (select max(price) from product) as max_price from product;

product 중 price가 최대인 항목의 price를 서브쿼리를 통해 나타내었다.

---

응용예제

member와 PK - FK 를 공유하는 orders 생성

create table orders(
    no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    total_price int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

auto_increment: 필드의 identity한 숫자를 자동으로 부여, 보통 PK로 사용된다.

 

orders table에 항목들을 추가해준다

insert into orders (userid, product_code, cnt, total_price) values ( 'apple', '00004', 1, 1300000);
insert into orders (userid, product_code, cnt, total_price) values ( 'apple', '00002', 1, 1200000);
insert into orders (userid, product_code, cnt, total_price) values ( 'apple', '00003', 1, 200000);
insert into orders (userid, product_code, cnt, total_price) values ( 'banana', '00004', 1, 1300000);
insert into orders (userid, product_code, cnt, total_price) values ( 'orange', '00004', 1, 1300000);
insert into orders (userid, product_code, cnt, total_price) values ( 'orange', '00001', 1, 500000);
insert into orders (userid, product_code, cnt, total_price) values ( 'melon', '00004', 1, 1300000);

 

문제1. 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력

 

우선 2번 이상 구입한 사람의 이름과 구입횟수를 그룹화하여 출력해보았다.

select userid, sum(cnt) from orders group by userid having sum(cnt) >= 2;

하지만 원하는 결과는 아이디, 이름, 성별이다. orders 테이블에는 이름, 성별에 대한 정보가 없기 때문에 key를 공유하는 member 테이블을 활용해야 한다.

 

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

# 서브쿼리
# (select userid from orders group by userid having count(no) >= 2)
# 2번 이상 구매한 사람의 아이디를 반환 -> apple, orange

실제 동작은 아래 코드와 같다.

select userid, name, gender from member where userid in ('apple', 'orange');

 

문제2. 상품을 최소 2번이상 구입한 아이디의 앞 2글자와 이름, 상품 구매횟수를 출력

 

(join)

select left(m.userid, 2), m.name, count(o.no) as cnt
from (member m inner join orders o on m.userid=o.userid)
group by m.userid having cnt >= 2;

다른 의미는 없고 가독성을 위해 join을 괄호 안에 담아보았다.

 

 

(서브 쿼리)

select left(m.userid, 2), m.name, sub.ocnt from member as m inner join 
(select userid, count(no) as ocnt from orders group by userid having count(no) >= 2) as sub 
on m.userid = sub.userid;

 

위 코드의 서브쿼리를 확인해보자

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

 

orders 테이블의 userid, count(no)를 그룹화한 또 하나의 테이블이라고 볼 수 있다.

이해를 위해 이 테이블의 이름을 UN이라고 하겠다.

 

select left(m.userid, 2), m.name, sub.ocnt 
from member as m inner join UN as sub 
on m.userid = sub.userid;

새로 그룹화된 테이블과 member 테이블을 join하여 아이디, 이름, 상품구매횟수를 출력할 수 있었다.

 

이처럼 서브쿼리, 조인, 그룹화를 적절하게 사용하여 원하는 결과를 얻을 수 있다.

 

다소 복잡할 수 있으니 천천히 생각해보는 연습을 하면 좋다.

---

복사

해당 SQL 기본 문법 마지막 포스팅이다. 다음 포스팅은 MySQL 사용자를 관리하는 문법으로 SQL은 마무리가 된다.

 

복사를 하기 위핸 샘플이 필요하다. orders_new를 만들어보자

 

create table orders_new(
    no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    total_price int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

---

복사하기

insert into orders_new(select * from orders);

단순하게 oreders 전체를 select한 것을 insert하면 항목 전체가 orders_new에 추가된다.

그럼 복사가 완료된다.

 

또 다른 방법

create table orders_new_new(select * from orders);

 

* CRUD

CRUD는 데이터베이스와 관련된 기본적인 데이터 조작 작업을 나타내는 약어이다. 

Create, Read, Update, Delete

 

해당하는 SQL 문법

Create -> insert into

Read -> select

Update -> update

Delete -> delete from