Day23 Database

22/11/23

📌 4. 조인(join)

✏️ <추가 연습문제>

use shopdb;

desc members;
desc products;
desc orders;

select*
from members;  -- 11개

select*
from products; -- 6개

select*
from orders;  -- 10개

1. members 테이블과 orders 테이블 사용.
   order_num, prod_id, member_id, member_name, phone, address, order_date를 출력하시오.
   

select  o.order_num, o.prod_id, m.member_id, m.member_name, m.phone, m.address, o.order_date
from members m join orders o
on m.member_id = o.member_id;

2. products 테이블과 orders테이블 사용
   order_num, member_id, prod_id, prod_name, price, company를 출력하시오.
   

select o.order_num, o.member_id, o.prod_id, p.prod_name, p.price, p.company
from orders o join products p
on p.prod_id = o.prod_id
order by o.order_num; 	-- 정렬

3. members 테이블과 products 테이블과 orders 테이블 사용
   주문정보와 주문한 회원 정보와 주문한 제품 정보를 다음과 같이 출력하시오.
   order_num, prod_id, prod_name, price, company, member_id, member_name, phone, address, order_date를 함께 출력하시오
   또한 주문번호를 기준으로 오름차순 정렬하시오.
   

select o.order_num, p.prod_id, p.prod_name, p.price, p.company, m.member_id, 
		m.member_name, m.phone, m.address, o.order_date
from members m join orders o
on m.member_id = o.member_id
join products p
on p.prod_id = o.prod_id
order by o.order_num;
-- (==)
select o.order_num, p.prod_id, p.prod_name, p.price, p.company, m.member_id, 
		m.member_name, m.phone, m.address, o.order_date
from members m join orders o join products p    
on m.member_id = o.member_id
and p.prod_id = o.prod_id
order by o.order_num;

4. members 테이블과 products 테이블과 orders 테이블 사용
   주문정보, 주문자 정보, 주문한 제품 정보를 함께 출력하되
   가입 후 주문한 이력이 없는 회원도 결과에 출력하시오.
   member_id, member_name, phone, address, prod_name, price, order_date 출력하되
   member_id를 기준으로 오름차순 정렬해서 출력하시오.

select m.member_id, m.member_name, m.phone, m.address, p.prod_name, p.price, o.order_date
from orders o join products p
on o.prod_id = p.prod_id
right outer join members m
on o.member_id = m.member_id 
order by m.member_id;

---

📌 5-1. 단일 행 함수 - 문자함수

함수란? 인수를 받아서 정해진 조작을 한 후 반드시 하나의 결과값을 반환함.

• SQL함수 유형
  1) 단일행함수 : 행 별로 조작해서 하나의 결과값을 반환함. => 5장
  2) 다중행함수 : 행 그룹을 조작해서 하나의 결과값을 반환함. => 6장

---

📂 문자함수

ASCII(아스키코드) : 문자의 아스키 코드값을 반환해 주는 함수
CHAR(숫자) : 숫자의 아스키 코드값을 반환해 주는 함수

select ascii('A'), char(65);
-- from dual;	 모든 db에는 사용할 테이블이 없을때 사용하라고 만들어놓은 테이블이 있다 = dual

( 출력 결과에 BLOB로 표시되는 경우(Workbench의 버그), 오른쪽 마우스 클릭 후,
[Open Value in Viewer] 선택 후, [Text] 탭에 들어가면 정상적인 출력 결과를 확인할 수 있음.)


LENGTH(문자열) : 문자열의 byte 수를 반환해 주는 함수
BIT_LENGTH(문자열) : 할당된 bit 크기 또는 문자 크기를 반환해 주는 함수
CHAR_LENGTH(문자열) : 문자의 개수를 반환해 주는 함수

select length('abc'), bit_length('abc'), char_length('abc');
select length('가다나'), bit_length('가나다'), char_length('가나다');

CONCAT(문자열1, 문자열2, ...) : 문자열을 연결해 주는 함수
CONCAT_WS(구분자, 문자열1, 문자열2, ...) : 구분자와 함께 문자열을 연결해 주는 함수

use hr;

select employee_id, concat(first_name, last_name) as "이름"
from employees;

select employee_id, concat(first_name, ' ', last_name) as "이름"
from employees;

select employee_id, 
		concat(first_name, ' ', last_name, ' ', job_id, ' ', email) as value
from employees;
-- (==)
select employee_id, 
		concat_ws(' ', first_name, last_name, job_id, email) as value
from employees;

select concat_ws('--', last_name, job_id, salary)
from employees;

INSTR(기준 문자열, 부분 문자열) : 기준 문자열에서 부분 문자열의 시작 위치값을 반환해 주는 함수

select instr('하나둘셋','둘');
select last_name, instr(last_name, 'a') as "a의 첫번째 위치"
from employees;

UPPER(문자열) : 문자열을 대문자로 변환해 주는 함수
LOWER(문자열) : 문자열을 소문자로 변환해 주는 함수

select lower('abcdEFGH'), upper('abcdEFGH');

select employee_id, upper(last_name) as "L-name", lower(job_id) as "Job",
	   phone_number, lower(email) as "E-mail"
from employees;

select concat('The job id for ', upper(last_name), ' is ', lower(job_id))
from employees;

LEFT(문자열, 길이) : 왼쪽에서 문자열의 길이만큼 반환해 주는 함수
RIGHT(문자열, 길이) : 오른쪽에서 문자열의 길이만큼 반환해 주는 함수
(ex) left(jumin_number, 6) => 901223

select left('abcdefghi', 3), right('adbcdfghi',3);
select last_name, left(last_name, 2), right(last_name, 2)
from employees;

LPAD(문자열, 길이, 채울 문자)
: 문자열을 길이만큼 늘린 후 빈곳을 왼쪽부터 채울 문자로 채워주는 함수, 오른쪽 정렬 함수
RPAD(문자열, 길이, 채울 문자)
: 문자열을 길이만큼 늘린 후 빈곳을 오른쪽부터 채울 문자로 채워주는 함수, 왼쪽 정렬 함수

select lpad('가나다',5,'#'), rpad('가나다',5,'#');
select lpad(last_name,20, '_')as "L-name", rpad(first_name, 20,'_') as "F-name"
from employees;

LTRIM(문자열) : 문자열의 왼쪽 공백을 제거해 주는 함수
RTRIM(문자열) : 문자열의 오른쪽 공백을 제거해 주는 함수

select ltrim('     SQL문법 '), rtrim('     SQL문법     '),
	   length('     SQL문법 '), length(rtrim('     SQL문법     '));

TRIM(문자열) : 문자열의 앞/뒤 공백을 제거해 주는 함수
TRIM(방향 자를문자열 FROM 문자열)방향 - leading(앞), trailing(뒤), both(양쪽)
: 문자열로부터 해당 방향의 자를 문자열을 제거해 주는 함수

select trim('     SQL 문법   '), trim(both'_'from'__SQL_문법___');

REPLACE(문자열, 기존문자열, 바꿀문자열) : 문자열에 기존 문자열을 바꿀 문자열로 교체해주는 함수

select employee_id, phone_number, replace(phone_number,'.','-')as"전화번호"
from employees;

SPACE(길이) : 길이만큼의 공백을 반환해 주는 함수

select concat('MySQL',space(10),'DBMS');

SUBSTR(문자열, 시작위치, 길이) : 문자열의 일부분을 반환해 주는 함수

select substr('대한민국만세',3,2);
select last_name, substr(last_name,2,3), substring(last_name,2,3)
from employees;

• 시작위치에(두번째인수) 음수값이 있으면 : 시작위치를 끝에서부터 찾아옴
• 반환할 문자수(세번째인수) 생략 : 시작 위치로부터 끝까지 반환함.

select last_name,
		substr(last_name,3,2),substr(last_name,-3,2),
        substr(last_name,2), substr(last_name,-3)
from employees;

---

✏️ <연습문제>

1. employees 테이블로부터 사원들의 last_name과 last_name의 길이를 출력하되
   last_name이 ‘J’, ‘A’, ‘M’으로 시작되는 사원만 출력하시오.
   또한 last_name을 기준으로 오름차순 정렬해서 출력하시오.

select last_name as "Name", length(last_name) as "Length"
from employees
where last_name Like 'J%'
or last_name Like 'A%'
or last_name Like 'M%'
order by last_name;
-- (==)
select last_name as "Name", length(last_name) as "Length"
from employees
where substring(last_name, 1, 1) in('j','m','a')
order by last_name;

2. employees 테이블로부터 사원들의 last_name과 salary를 출력하되
   특히 급여는 15자리로 표시하고 왼쪽부터 $ 기호가 채워지도록 지정하시오.

select last_name, lpad(salary,15,'$') as 'salary'
from employees;

3. employees 테이블로부터 last_name과 급여 액수를 별표(*)로 나타내는 query를 작성하시오.
   각 별표는 $1,000를 의미하며 백단위 이하는 표시하지 않습니다.
   또한 급여의 내림차순으로 데이터를 정렬하여 출력하고
   컬럼 제목(column alias)을 EMPLOYEES_AND_THEIR_SALARIES로 지정하시오.

• [오답] rpad, lpad 함수는 두번째 인수가 실수인 경우 자동 반올림함!

select last_name, salary,
		concat(last_name,' ', RPAD('*',salary/1000,'*'))
as EMPLOYEES_AND_THEIR_SALARIES
from employees
order by SALARY desc;

결과 -> RPAD('*',salary/1000,'*')
	2천 후반대는 3개의 *이 찍힘
	3700/1000=3.7 
	rpad, lpad는 두번째 인수가 자리수 -> 실수일때는 자동 반올림
	3700/1000=3.7 => 4

• [정답] truncate함수 사용!

select last_name, salary,
		concat(last_name,' ', RPAD('*', truncate(salary/1000,0),'*'))
as EMPLOYEES_AND_THEIR_SALARIES
from employees
order by SALARY desc;