KIC 백엔드 수업

3월 29일 (수) - [ MariaDB - 함수 / 단일, 복수 서브쿼리 / join ]

Crecok 2023. 3. 29. 16:48
반응형

데이터베이스 ( DBMS - Database Management System )

- Oracle, MySQL, MariaDB 등..

 

데이터베이스 운영 ( DBA )

SQL ( 개발자 영역 ) : ANSI ( 표준 SQL ) + 확장 SQL

 

DML - select / insert / update / delete

DDL - create / alter / drop / rename / truncate

DCL - grant / revoke

TCL - commit / rollback / savepoint

 

데이터베이스화 ( ERP : Enterprise Resource Planning )

- 전사적 자원관리 시스템 ( ERP ) : 데이터의 관리가 장부에서 프로그램화 되어가는 과정

- insert, update, delete 중요

 

Extended ERP

- ERP의 기본 개념에서 고유기능 확장

- select 구문 중요

 

CRM ( Customer Relationship Management : 고객 관계 관리 ) : 고객 - 물건(회사)

- 회사의 현재 고객 및 잠재 고객과 관련된 정보를 관리, 추적, 저장하도록 지원하는 일련의 데이터기반 소프트웨어가 통합된 솔루션

SQL의 기본적인 사용법 및 명령어는 www.w3schools.com 의 Learn MySQL 항목을 참고한다.

 

● select문 나열 순서

- select 컬럼명 from 테이블명 where 조건 order by 정렬 limit

 

● 서버에 접속 하는 방법

1. mysql -h 아이피 -u 아이디 -p

2. 데이터베이스 목록 ( show databases; )

3. 특정 데이터베이스 사용 ( use 데이터베이스명; )

4. 테이블 목록 ( show tables; )

5. 특정 테이블 구조 ( describe 테이블명; )

 

특정 데이터베이스의 이름을 안다면 1번 과정에서 mysql -h 아이피 -u 아이디 -p 데이터베이스명 으로 접속할 수 있다.

■ 함수 ( 단일 행 함수 / 그룹 함수 ) : https://mariadb.com/kb/en/built-in-functions 참고

 

- 단일 행 함수 → 1 : 1

  ▶ infomation : 시스템 정보

  ▶ Numeric : 수학 함수

  ▶ String : 문자열 처리 함수

  ▶ Date : 날짜 처리

  ▶ Control : 제어

      ▷ if 

      ▷ ifnull

      ▷ case A when B then C else D end;

 

- 그룹 함수 → 여러개 : 1

  ▶ count : 데이터 개수

  ▶ min / max : 최소값 / 최대값

  ▶ sum : 합

  ▶ avg : 평균

  ▶ 그 외 분산 / 표준편차를 구하는 함수

 

예제 1. 사원 정보에서 사원 번호, 사원명, 급여, 커미션, 연봉을 출력하라. 이 때, 연봉은 sal * 12 + comm 으로 정의한다.

- select empno, ename, sal, comm, truncate(sal*12+ifnull(comm, 0), 0) '연봉' from emp;

 

● case A when B then C else D end;

- A의 값이 B이면 C를 출력하고 그렇지 않으면 D를 출력한다.

- when ~ then ~ 부분은 경우의 수만큼 추가해도 상관 없다.

 

- case 값 when 1 then 'one' 2 then 'two' else 'more' end;

- 값이 1이면 one, 2면 two, 그 외엔 more 라고 출력한다.

select case 1
    -> when 1 then 'one'
    -> when 2 then 'two'
    -> else 'more'
    -> end;

결과값 : one

 

select case 3
    -> when 1 then 'one'
    -> when 2 then 'two'
    -> else 'more'
    -> end 'case';

결과값 : case / more

 

 

사원 정보에서 사원 번호, 사원명, 직책을 출력하는데

직책이 clerk이면 사원, analyst면 분석, manager면 관리, prsident는 대표, 기타는 영업으로 바꿔서 출력하라.

select empno, ename,
case job
when 'analyst' then '분석'
when 'clerk' then '사원'
when 'manager' then '관리'
when 'president' then '대표'
else '영업'
end 'job'
from emp;

 

예제 2. 사원정보에서 사원 번호, 사원명, 부서 번호, 부서명을 출력하는데 아래 조건을 충족시켜라.

부서번호는 10 - 회계 / 20 - 조사 / 30 - 영업 / 40 - 총무 로 변경 후 부서명 항목으로 이름 변경한다.

select empno, ename, deptno,
case deptno
when 10 then '회계'
when 20 then '조사'
when 30 then '영업'
when 40 then '총무'
end 'dname'
from emp;

- 부서번호 종류가 10, 20, 30, 40 뿐이므로 마지막은 when 40 then 대신 else 를 써도 상관 없다.

 

 

예제 3. 사원정보에서 사원 번호, 사원명, 현재 급여, 인상 급여을 출력하는데 아래 조건을 충족시켜라.

부서번호별 인상률은 10 - 10% / 20 - 20% / 30 - 30% / 40 - 인상없음 이다.

select empno, job, ename, sal '현재 급여',
case deptno
when 10 then sal*1.1
when 20 then sal*1.2
when 30 then sal*1.3
else sal
end '인상 급여'
from emp;

 

 

그룹 함수

- 그룹화 ( group by ) 

- group by 기능 안에 order by가 포함되어 있으므로 자동 정렬된다.

 

● count : 데이터 개수

- select count(empno) from emp where deptno=30; // 결과값 : 6

- select count(comm), count(empno) from emp; // 결과값 : 4 / 14

- count 함수는 데이터가 null인 데이터의 수를 세지 않는다. 따라서 count 안에는 * 을 넣는 방식으로 많이 사용한다.

- select count(*) from emp; // 결과값 : 14

 

● 모르는 데이터베이스 접속 시 ( count 함수의 활용 )

- desc 테이블명;

- select count(*) from 테이블명;

- select 컬렴명 ... from 테이블명 limit 5;

- select * from 테이블명은 많은 양의 전체 데이터가 전부 출력되므로 권장하지 않는다.

 

● max / min : 최대값 / 최소값

- select max(sal), min(sal) from emp where deptno=30;

- select max(sal), ename from emp; 처럼 그룹 함수와 단일 목록을 함께 사용하지 않는다.

  (오류는 없지만 출력상 데이터 값이 정확하지 않게 출력된다.)

 

● sum : 합계

-select sum(sal) from emp; // 결과 값 : 20925.00

- select avg(comm), sum(comm)/count(*), sum(comm)/count(cumm) from emp;

- 출력 결과 셋 다 평균의 연산이지만 결과값은 다르게 나온다.

- 이는 comm 컬럼에 Null 값이 있기 때문이다. avg 함수는 기본적으로 null 항은 세지 않는다.

   따라서 아래처럼 null 데이터를 0으로 치환하고 avg 함수를 사용해야 한다.

- select avg(ifnull(comm, 0)) from emp;

 

● group by (그룹화)

- select deptno, count(*) from emp group by deptno; ( 부서번호의 갯수를 부서번호별로 출력 )

- 원래는 그룹함수와 단일 목록을 함께 사용하면 안되지만, group by 이후의 목록은 단일 목록으로 쓰여도 상관 없다.

 

● 부서별 최대 급여를 출력하라.

- select deptno, max(sal) from emp group by deptno;

- select deptno, max(sal), ename from emp group by deptno; (X)

 

● 직책별 평균 급여를 출력하라.

- select truncate(avg(sal),0) '직책별 평균 급여' from emp group by job;

 

● 부서 + 직책별 급여의 합계를 구하여라.

- select deptno, job, sum(sal) from emp group by deptno, job;

- group by 이후에 여러개의 컬럼을 적으면 그 컬럼끼리 묶은 쌍 단위로 그룹이 적용된다.

 

● 각 부서 번호의 직책별로 급여의 합이 2000 이상인 부서 번호, 직책, 급여의 합계를 구하여라. 

- select deptno, job, sum(sal) from emp group by deptno, job having sum(sal)>=2000;

- where 절 다음에는 그룹 함수를 사용할 수 없다.
- select deptno, job, sum(sal) from emp where sal >= 2000 group by deptno, job;

- where 이후 sum(sal) 부분을 sal로 고치면 사용 가능하다.

- having 절은 group by 이후에, where 절은 group by 이전에 사용된다.

 

예제 3. 부서별 인원수가 6명 이상인 부서번호, 부서이름, 인원수를 출력하라.

- select deptno, dname, 

(select count(deptno) from emp group by deptno having count(deptno) >= 6) '인원수'

from dept where deptno = (select deptno from emp group by deptno having count(deptno) >= 6);

 

복합 형태 ( 쿼리 안의 쿼리 )

 

단일행

- 1행 1열의 결과를 가지고 다시 쿼리

- 비교연산자만 사용 가능 ( =, <, > 등 )

 

복수행

- 여러행 1열의 결과를 가지고 다시 쿼리

 

■ 단일행 서브쿼리

● SCOTT의 급여보다 많은 급여를 받는 사원 정보 출력 (단일행)

1. SCOTT의 급여

2. 그 급여보다 많은  급여를 받는 사원 정보 출력

 

1. select sal from emp where ename = 'scott'; // 이름이 'scott'인 사원의 급여

2. select ename, sal from emp where sal > ( select sal from emp where ename = 'scott' ); // 서브쿼리

 

● 최고 급여를 받는 사원 정보(이름, 급여) 출력 (단일행)

1. 최고 급여

2. 그 급여를 받는 사원

 

1. select max(sal) from emp;

2. select ename, sal from emp where sal = ( select max(sal) from emp );

 

■ 복수행 서브쿼리

 

● 부서별 최고 급여를 받는 사원에 대한 정보 출력

1. 부서별 최고 급여

2. 이 급여를 받는 사원

- select empno, ename, sal from emp where sal in (select max(sal) from emp group by deptno);

 

● 20번 부서의 사원이 속한 직책과 같은 사원들에 대한 정보

- select empno, ename, job from emp where job in (select distinct job from emp where deptno=20);

 

- in 연산자 : A in B, B의 값을 A에 대응하여 가져온다.

< Any : 최댓값보다 작은 ( < 서브쿼리의 리턴값 중 최대값 )

> Any : 최솟값보다 큰 ( > 서브쿼리의 리턴값 중 최소값 )

< ALL : 모든 것보다 작은 ( < 서브쿼리의 리턴값 중 최소값 )

> ALL : 모든 것보다 큰 ( > 서브쿼리의 리턴값 중 최대값 )

 

● 직책이 manager인 사원들의 급여보다 큰 사원에 대한 정보

1. 직책이 manager인 사람들의 급여 : select sal from emp where job='manager';

2. 이 급여보다 큰 사원 : select empno, ename, sal from emp where sal > all ( select sal from emp where job='manager' );

 

● 각 부서의 평균 급여 전체보다 적은 사원들의 대한 정보 출력

1. 각 부서의 평균 급여 : select avg(sal) from emp group by deptno

2. 이 급여보다 적은 사원들의 대한 정보 : select empno, ename, sal from emp where sal < all (select avg(sal) from emp group by deptno);

 

● join : 메모리상에서 두 개 이상의 데이터 목록을 병합하는 것 ( relation )

- 곱의 개념으로 적용된다. 14행 테이블, 4행 테이블을 join 하면 총 56행의 테이블이 된다.

- select * from emp cross join dept;

- select * from emp inner join dept;

 

● 같은 이름의 컬럼끼리 값을 일치시켜 출력하는 조건을 넣을 수 있다. ( 조인 조건 = : equi join )

- select * from emp inner join dept where emp.deptno = dept.deptno;

- and 연산자도 사용할 수 있다.

- select * from emp inner join dept where emp.deptno = dept.deptno and emp.deptno=10;

 

● 별명으로 어느 테이블의 어느 컬럼인지 구별할 수 있다. ( table Ailas )

- select * from emp e inner join dept d where e.deptno = d.deptno and e.deptno=10;

 

테이블간 공통 이름의 컬럼명이 아니어도, 다른 컬럼들에도 별명을 붙여 주는것을 권장한다.

select empno, ename, dname, loc, e.deptno
from emp e inner join dept d
where e.deptno = d.deptno
and e.deptno=10;

 

조인조건을 ON 으로 표시 할 수도 있다. 이 경우 where 절과의 구분이 분명해진다.

select e.empno, e.ename, d.dname, d.loc, e.deptno
from emp e inner join dept d
ON (e.deptno = d.deptno)
where e.deptno = 10;

 

Q. 직책이 clerk인 사원에 대한 사원번호, 사원이름, 급여, 부서이름, 부서위치를 출력하라.

select e.empno, e.ename, e.sal, d.dname, d.loc
from emp e inner join dept d
ON (e.deptno = d.deptno)
where e.job = 'clerk';

 

조인 조건 ( 부등호 조건 ) : non equi join

-- non equi Join
select *
from emp e inner join salgrade s
ON ( e.sal >= s.losal and e.sal <= s.hisal )
where e.deptno = 10;
-- non equi Join
select *
from emp e inner join salgrade s
on ( e.sal between s.losal and s.hisal )
where e.deptno = 10;

between A and B 를 사용해도 동일한 결과를 얻을 수 있다.

 

 

Q. 입사년도가 2011년인 사원에 대한 사원번호, 사원이름, 급여, 급여등급을 출력하라.

- select e.empno, e.ename, e.sal, s.grade from emp e inner join salgrade s on ( e.sal between s.losal and s.hisal ) where e.hiredate like '2011%';

 

 

● 3개의 테이블 조합 (  join ~ on 2회 사용 )

select e.empno, e.ename, e.sal, s.grade, d.dname, d.loc
from emp e inner join dept d
on (e.deptno = d.deptno)
inner join salgrade s
on (e.sal between s.losal and s.hisal);
select e.empno, e.ename, e.sal, s.grade, d.dname, d.loc
from emp e inner join dept d inner join salgrade s
on ( e.deptno = d.deptno and e.sal between s.losal and s.hisal );

- 바로 위 코드처럼 inner join 행과, on 행에 따로 몰아서 작성해도 된다.

 

 

● outer join : 한 쪽 정보는 완전히 출력하고, 나머지 정보는 연결만 시켜주는 방식 ( null - 연결되지 않는 정보 )

- 사원이 없는 부서 / 부서가 없는 사원 / 주문이 없는 상품 / 상품이 없는 주문 등을 조회할때 많이 사용한다.

- right outer join : 오른쪽 중심의 왼쪽 연결

- left outer join : 왼쪽 중심의 오른쪽 연결

- full outer join : 양쪽 모두를 중심으로 연결

select d.deptno, d.dname, e.empno, e.ename
from emp e right outer join dept d
on ( e.deptno = d.deptno );

 - rhght outer join 이므로 오른쪽 dept 테이블의 데이터는 모두 표기된다.

 

 

● 사원이 없는 부서를 출력하라.

select d.deptno, d.dname, d.loc
from  dept d left outer join emp e
on ( e.deptno = d.deptno )
where e.empno is null;

 

 

● self join : 한 테이블 내부에 조인 ( + inner join, + outer join )

● 각 사원들의 관리자 번호와 이름을 출력하라.

select e.empno, e.ename '사원', e.mgr, m.ename '관리자'
from emp e inner join emp m
on (e.mgr = m.empno);

 

● 위 예제에서 KING도 나올 수 있게 출력하라.

select e.empno, e.ename '사원', e.mgr, ifnull( m.ename, '관리자없음') '관리자'
from emp e left outer join emp m
on (e.mgr = m.empno);

 

내일 퀴즈 예정 - 11 : 10

217 page 까지

로또 번호 통계

반응형
저작자표시 비영리 변경금지 (새창열림)