(MySQL) 기초 문법(문자열 함수)

member 테이블

select * from member;

member 테이블

concat

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

select concat('안녕', '하세요') as concat;

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

left, right

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

select left('ABCDEFGHIJKLMN', 5);

select userid, left(email, 5) as email from member where userid='apple';

substring

• 문자열의 일부를 가져옴

# substring(문자열, 시작위치, 길이)

select substring('ABCDEFGHIJKLMN', 3, 2);

select substring(address1, 1, 3) as 주소 from member where userid='orange';

char_length

• 문자열의 길이를 반환

select char_length('ABCDEFGHIJKLMN');

select char_length(email) as len from member;

lpad, rpad

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

select lpad('ABCDEFG', 10, '0');

select lpad(point, 5, 0) as lpad from member;

ltrim, rtrim, trim

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

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

replace

• 문자열에서 특정 문자열을 변경

# replace(문자열, 대상, 바꿀 문자열)

select replace('ABCDEFG', 'CD', '✅') as repl;

문제

• 상품을 2개이상 구입한 사용자의 아이디, 상품 구입횟수, 시도이름을 출력
• 서브쿼리를 사용

orders 테이블

select * from orders;

 

나의 풀이

select m.userid, o.cnt, substring(m.address1, 1, 3) as address from member as m
inner join
(select userid, count(no) as cnt from orders group by userid having cnt >= 2) as o on m.userid = o.userid;

강사님 풀이 (조인 사용)

select m.userid, count(o.no) as cnt, substring(m.address1, 1, 3) as address from member as m
right outer join
orders as o on m.userid = o.userid group by userid having cnt >= 2;

강사님 풀이 (from절)

select m.userid, t.ocnt, substring(m.address1, 1, 3) as address from member as m
right outer join
(select userid, count(no) as ocnt from orders group by userid having count(no) >= 2)
as t on m.userid = t.userid;