데이터베이스화 - (장부 -> 프로그램화 : ERP)
전사적 자원 관리(Enterprise Resource Planning)는 ERP라고도 하며 재무, 제조, 소매유통, 공급망, 인사 관리, 운영 전반의 비즈니스 프로세스를 자동화하고 관리하는 시스템
Extended ERP - SI
기본의 ERP시스템이 발전된 개념
ERP구축이란 사내망을 구축한다는 것
기존 업무였던것을 컴퓨터 프로그램화 시키는 것
ERP - 데이터를 넣는것이 중요
insert / update / delete
Extended ERP
select가 제일 중요
Select 진행
Select 컬럼명
from 테이블명
where 조건
order by 정렬
limit
중요 명령어
서버에 접속 방법
mysql -h 아이피 -u 아이디 -p
mysql -h 아이피 -u 아이디 -p 데이터베이스명 : use까지 한번에 처리된다.
데이터 베이스 목록
show databases
특정 데이터베이스 사용
use 데이터베이스명;
테이블 목록
show tables;
특정 테이블 구조
describe 테이블 명 / desc로 줄여서 쓸 수 있음.
함수
단일행 함수
1 개당 1개 데이터 보기
information - 시스템 정보
Numeric - 수학 함수
String - 문자열 처리함수 (1.중요)
Date - 날짜처리 (2.중요)
Control - 제어 (3.중요)
ifnull + 산술연산자
if elseif else => switch case
그룹 함수
여러개의 데이터를 넣고 한개의 데이터 보기
데이터 갯수 - count
최대 / 최소값 - min / max
합 - sum
평균 - avg
분산 / 표준편차
외부스크립트
텍스트 파일을 만들어서 코드 작성 후 프롬프트에서 실행 시키기
-- 는 sql의 주석역할
utf-8로 해도 글 안깨진다
사원번호, 사원이름, 월급, 보너스 연봉을 출력하고 보너스(comm)이 null인 경우는 0으로 바꿔주고 소수점은 없이 출력
스위치구문 - if elseif else => switch case
case 1이 when과 같다면 해당 값 출력
case 1은 when 1과 같기때문에 one이 출력된다.
3이면 없기때문에 more / end 'case'로 이름 바꿈
사원정보에 사원번호, 사원명, 직책을 출력
직책이 clerk이면 사원
analyst 분석
manager 관리
president 대표
기타 영업
사원정보에서 사원번호, 사원명, 부서번호, 부서명을 출력
10 -> '회계'
20 -> '조사'
30 -> '영업'
40 -> '총무'
사원정보에서 사원번호, 사원명, 현재급여, 인상급여를 출력
인상급여
10 -> 10% - sal*1.1
20 -> 20%
30 -> 30%
40 -> 인상없음
count
empno의 데이터 개수
30번 부서에 해당하는 empno의 개수는
deptno가 30인 부서의 카운트를 해줌
테이블의 데이터 개수를 알고싶으면 count(별)
count(comm)은 null을 제거(미포함)시키고 카운트 한다
null이 아닌 것은 4개 그래서 4가 출력이 됐음
모르는 데이터베이스 접근
이렇게 접근하면 테이블이 얼만큼의 크기인지 모르기때문에 위험. (전체 다 출력되기 때문)
select * from 테이블명
모르는 테이블에 접근하기
desc 테이블명;
select count(*) from 테이블명;
select 컬럼명 ... from 테이블명 limit 5;
최대 / 최소 - max(), min()
emp 테이블의 sal 컬럼의 최대 / 최소값
최대 최소 주의사항
mariadb 자체의 오류가 있어 불일치하게 뜨기도 한다.
합계 구하기 - sum()
- emp 테이블의 sum(sal)로 sal의 총 합을 구함
- emp 테이블의 sum(comm)로 comm의 총 합을 구함
평균 구하기 - avg()
emp 테이블의 comm컬럼의 평균값을 avg(comm)으로 구함
주의 사항
count(comm)은 comm이 null을 포함하고 있는데 null은 개수로 치지않아서 null이 아닌 값의 개수로 된다. 4개
sum(comm) / count(*) 하면 comm합계 / 데이터 개수 로 계산이 돼서 이상한 값이 나온다.
치환해준다 null을 처리할때는 ifnull(comm, 0)
ifnull(comm,0)으로 null을 0으로 치환하여 개수에 포함되게 만듦
avg(comm) : comm의 평균
sum(comm)/count(comm) : comm의 합/comm의 개 수
sum(comm)/count(*) : comm의 합/emp의 개수(14) <- emp의 테이블 모두 comm을 가지고있음
gruop by 컬럼명 - group by 에 oder by 가 내장돼있음
그룹별 부분 가능, 그룹함수
group by절은 원하는 데이터 그룹끼리 데이터를 묶어서 사용할 수 있다.
group by에 쓰인 컬럼을 select 절에 쓰면 연동되서 나오는 느낌
부서별 카운트(데이터 개수)를 구해라
emp 테이블의 deptno(부서번호)를 그룹화하여 count로 전체 카운트 함
부서번호 10은 3개가 있어서 3
20은 5개있어서 5
30은 6개 있어서 6
group by에 쓰인 컬럼을 앞에 적어줄 수 있다.
부서별 최대 급여...
emp 테이블의 deptno(부서번호)를 그룹화하여 월급 최대값 구함
직책별 평균급여 ...
emp 테이블의 job(직책)을 그룹화하여 직책별 avg(sal)(평균 월급)을 구함
부서 직책을 하나로 묶어서 그룹화
부서와 직책이 집합으로 묶여서 그것의 합계를 뽑음
오름차순으로 정렬이 돼 있으며 번호와 직책을 한 쌍으로 중복되는것이 있을경우 합쳐서 하나로 출력됨 sal도 합산돼서 출력이 된다.
합계가 2000이상인 조건
where 절 다음에는 그룹함수를 쓸수없다.
그래서 having 사용
group by에서 나온 결과를 가지고 뽑을 정보만 뽑을 수 있다.
2000보다 크거나 같은 데이터 구함 / 2000 이하의 데이터가 빠진 것.
where절 쓰기
부서별 인원수가 6명 이상인 부서번호, 부서이름, 인원수 출력(집)
select
단순
복합
1. 서브쿼리
쿼리 안에 쿼리
서브쿼리는 단일행, 복수행으로 나뉜다
단일행 - 1행 1열의 결과를 가지고 다시 쿼리
비교연산자만 사용 가능
복수행 - 여러행 1열의 결과를 가지고 다시 쿼리
단일행 서브쿼리
ex) 최고 급여를 받는 사원정보 출력
1. 최고 급여 (단일행)
2. 그 급여를 받는 사원
ex2) scott의 급여보다 많은 사원정보 출력
1. scott의 급여(단일행)
2. 그 급여보다 많은 사원
- scott의 급여
select sal from emp where ename = 'scott'; - 급여보다 많은 사원
select ename, sal from emp where sal > ?(1.의 sal)
=> select ename, sal from emp where sal > (select sal from emp where ename = 'scott');
=> > 뒤에 나오는 괄호가 먼저 계산 후 > 앞으로 간다.
scott의 급여보다 많은 사원정보 출력해보기
최고급여를 받는 사원 정보 출력 해보기
부서별 최고급여를 받는 사원에 대한 정보 출력
- 부서별 최고급여
- 이 급여를 받는 사원
부서별 최고급여
select max(sal) from emp group by deptno;
이 급여를 받는 사원
sal in (select max(sal) from emp group by depto());
where 일치하길 바라는 컬럼명 in (조건) : (조건)을 넘겨준다.
20번 부서의 사원이 속한 직책과 같은 사원들에 대한 정보
목록형 숫자데이터 범위 연산자
in 자리에 들어감
< Any : < 서브쿼리의 리턴값 중 최대값 / 최대값보다 작은 영역
> Any : > 서브쿼리의 리턴값 중 최소값 / 최소값보다 큰 영역모두보다 큰 모두보다 작은
< ALL : 서브쿼리의 리턴값 중 최소값 / 전체보다 작은
> ALL : 서브쿼리의 리턴값 중 최대값 / 전체보다 큰직책이 매니저인 사원들의 급여보다 큰 사원에 대한 정보
-
직책이 매니저인 사원들의 급여
job이 manager인 사원들의 sal
-
이 급여보다 큰 사원
각 부서의 평균 급여 전체보다 적은 사원들에 대한 정보 출력
2개이상의 테이블을 결합하는것 - join / relation
연결된 컬럼이 있으면 연결해서 사용할 수 있다.
테이블간 컬럼이 일치할 때 사용
detp 테이블 안에 emp 테이블을 묶어놨다.
select * from dept cross join emp; (emp와 dept 결합)
select * from emp inner join dept;
cross join / inner join 똑같이 출력됨
emp의 deptno와 dept의 deptno와 같은 것들만 나오게 필터링하여 출력한 것
equi join (조건식에 '='사용) / 행을 제한, 10번부서에 관련된 데이터 / and로 추가 연결도 가능
JOIN은 두개의 테이블을 합쳐서 하나의 테이블인 것 처럼 보여준다.
기본적으로 두개의 테이블을 한개의 select문을 실행했을 경우(기본적인 JOIN) 특별한 조건을 주지 않으면 행이 곱연산으로 표현된다.
이때 두 테이블에서 공통적으로 존재하는 컬럽의 값이 일치되는 행을 연결해서 결과를 생성하는 것을 equal join이라 한다
문장이 길어져서 별명을 만들어줄 수 있음. / 테이블 별칭
where : join 후에 조건을 필터링 (=join을 한 결과에서 where 조건절로 필터링이 이뤄진다.)
inner join으로 emp 안에 dept를 넣고, 조건으로 emp의 deptno와 dept의 deptno와 같은 조건 그리고 emp의 deptno가 10인 결국엔 둘 다 10인 조건을 가진것을 출력하는 것.
on
on : join 전에 조건을 필터링 (=on 조건으로 필터링된 레코드(행))간 join이 이루어짐
위와 결과는 같음
on으로 join전에 e.deptno와 d.deptno가 같은 조건을 걸고 join이후에 조건을 필터링 하는 where조건으로 e.deptno가 10인 조건을 걸어서 출력
직책이 clerk인 사원에 대한 사원번호, 사원이름, 급여, 부서이름, 부서위치 출력(집)
조인 조건으로 테이블 사이에 값 넣기 non equi join
non-equi join은 기준 컬럼의 값(레코드)이 서로 같지 않더라도 서로가 지정한 범주에 속하는 관계라면 JOIN이 가능
on 조건으로 join전에 emp의 sal이 salgrade의 sal보다 크거나 같고, emp의 sal이 salgrade의 hisal보다 작거나 같다는 범위의 조건을 넣어주고 그 범위 안에 속하면 join을 한다.
emp의 sal들의 값과 비교했을때 emp에 sal의 조건의 바로 다음값 낮은건 losal, 높은건 hisal에 출력 (ex: sal이 800이면 losal은 바로 한단계 낮은 700, hisal은 한단계 높은 1200 입력)
이렇게
between으로 간단하게 쓸 수 있다.
10번 부서만 뽑기
입사년도가 2011년인 사원에 대한 사원번호, 사원이름, 급여, 급여등급을 출력
두 개 이상의 테이블 결합
이렇게도 가능
outer join - 한 쪽 정보는 완전히 출력하고 나머지는 연결만 시켜줌.. 연결되지 않은 정보는 null
A, B 테이블을 join 할 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 outer join을 사용합니다.
right outer join (오른쪽 중심(전체데이터)의 왼쪽 연결)
left outer join (왼쪽 중심의 오른쪽 연결)
full outer join (양쪽 연결)
사원이 없는 부서 / 부서가 없는 사원
주문이 없는 상품 / 상품이 없는 주문
inner join / outer join
self join
- 한 테이블 내부에 조인
inner join
outer join 사용
king은 관리자 없음
king이 최종, king 관리자 보기 / 관리자 없어서 null
king은 관리자 없음으로 나타냄
내일 퀴즈
클래스와 객체 기본 형태까지
약 217p 까지
