59일 차 - 테이블 CRUD, LIKE 연산자, 조인(JOIN), 카티션 프로덕트 (23.03.22)

💡 CRUD

• Create, Read, Update, Delete

📝 테이블 구조 정의

🔎 Create (DDL)

• CHAR : 고정 문자
• VARVHAR2 : 가변 문자
• NUMBER : 숫자
  NUMBER(4): 0~9999까지 사용가능
  NUMBER(7,2): 7자리의 정수와 2자리의 소수

🔎 Insert (DML): 생성된 테이블에 맞게 데이터 삽입

insert into emp1 values(7839,'홍길동','MANAGER',1234,'1981/05/06',5000,null,10); 		// 데이터 삽입
commit;		 // **영구저장**

• 결과
  

🔎 Update (DML): 업데이트할 조건 지정

update emp1 set sal = sal * 1.1;

• 결과
  

update emp1 set sal = sal * 1.1 where ename='김마크';		// 김마크만 업데이트

• 결과
  

✔ 모든 직원의 입사일을 오늘로 수정하는 쿼리문

UPDATE emp1 set hiredate = sysdate;
 select * from emp1;

🔎 Delete (DML): 테이블의 내용을 삭제

delete from 테이블명 where 조건;

📝 LIKE 연산자와 와일드 카드

✔ ename이 F로 시작하는 사람을 찾는 쿼리문

select * from emp where ename like 'F%';

✔ 위치 상관없이 이름 중에 A가 포함된 사람을 찾는 쿼리문

select * from emp where ename like '%A%';

• 결과
  

✔ 이름의 두번째 글자가 A인 사원을 찾는 쿼리문

select * from emp where ename like '_A%';

• 결과
  
• _(언더바): 문자에 대한 와일드 카드 역할

✔ 이름에 A를 포함하지 않는 사원을 찾는 쿼리문

select * from emp where ename not like '%A%';

• NOT LIKE 활용

📝 NULL을 위한 연산자

• 임의의 컬럼이 NULL인지 아닌지를 비교/검색
• 특정 컬럼값이 NULL값인지를 비교할 경우 IS연산자를 사용

• JAVA에서 null은 참조형일때만 알 수 없는 주소로 할당
• DB에서 null은 알 수 없는 값을 의미하여 연산, 할당, 비교 불가

✔ 커미션(COMM)을 받지 않는 사원을 검색하는 쿼리문

select * from emp where comm is null;

✔ 커미션(COMM)을 받는 사원을 검색하는 쿼리문

select * from emp where comm is not null;

📝 정렬을 위한 ORDER BY절

• 오름차순(ascending) : 작은 값이 위에 출력되고 아래로 갈수록 큰 값이 출력 (default)

• 내림차순(descending) : 큰 값이 위에 출력되고 아래로 갈수록 작은 값이 출력
  

✔ 사원들의 급여를 오름차순으로 정렬하는 쿼리문

select * from emp order by sal asc;

✔ 가장 최근에 입사한 사원부터 정렬하는 쿼리문

select * from emp order by hiredate desc;

📝 DISTINCT

• 동일한 데이터 값들이 중복되어 출력되지 않도록 사용

✔ 사원들이 소속되어 있는 부서의 번호를 출력하는 쿼리문

select DISTINCT deptno from emp;

📝 별칭 연산자

✔ 사원들의 salary를 10% 인상한 결과값을 출력하여 '연봉 인상'으로 컬럼명을 지정한 쿼리문 (as는 생략가능)

select ename,(sal*1.1) as "연봉 인상" from emp;

• 결과
  

📝 DUAL 테이블

• 한 행으로 결과를 출력하기 위한 테이블

select 15 * 67 from dual;

• 결과
  

📝 그룹함수

• 하나 이상의 행을 그룹으로 묶어 연산하여, 한 행으로 하나의 결과를 나타내는 함수

• SUM : 해당 컬럼 값들에 대한 총합을 구하는 함수

  select sum(sal) from emp; 	✔ 연봉의 총합 구하기

• AVG : 해당 컬럼 값에 대해 평균을 구하는 함수

  select avg(sal) from emp; 	✔ 연봉의 평균 구하기

• MAX/MIN
  • MAX : 지정한 컬럼 값 중에서 최대값을 구하는 함수
  • MIN: 해당 컬럼 값들의 최소값을 구하는 함수

    select MAX(sal) from emp; 	✔ 연봉의 최대값 구하기
     select MIN(sal) from emp; 	✔ 연봉의 최소값 구하기

• COUNT : 테이블에서 조건을 만족하는 행위 개수를 반환하는 함수
  • 해당 컬럼에서 값을 갖고 있는 행의 개수를 계산하여 반환한다.
  • COUNT 함수는 NULL값에 대한 개수를 세지 않는다

    select count(*) from emp; 	✔ 총 사원 수 구하기
    
     select count(comm) from emp; 	✔ 사원들 중 커미션(comm)을 받은 사원의 수를 구하는 쿼리문
    
     select count(DISTINCT job) from emp; 	✔ job 종류의 수 구하는 쿼리문

• 활용
  

🔎 GROUP BY 절

• 특정 컬럼을 기준으로 그룹화하여 테이블에 존재하는 행들을 그룹별로 구분하기 위해 사용

✔ 소속 부서별 평균 급여를 구하는 쿼리문

select deptno,avg(sal) from emp group by deptno;

• 결과
  

✔ 소속 부서별 사원 수와 커미션(comm)을 받는 사원의 수를 구하는 쿼리문

select deptno,count(*),count(comm) from emp group by deptno;

• 결과
  

🔎 HAVING 절

• GROUP BY 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 보고자 할 때 사용

  ✔ 부서별 평균 급여가 2000 이상인 부서의 번호와 부서별 평균 급여를 출력하는 쿼리문

  SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;

📝 조인(JOIN)⭐

🔎 카티션 프로덕트 (CARTESIAN PRODUCT)

• 두개 이상의 테이블이 조인될 때 WHERE절에 공동되는 컬럼에 의한 조인이 발생되지 않아 모든 데이터가 검색 결과로 나타나는 경우

• 서로 다른 테이블에 있는 컬럼들을 하나의 테이블에서 보기 위해 사용

• 기본키 (PRIMARY KEY)

  • UNIQUE 제약 조건 + NOT NULL 제약 조건
  • 중복되지 않는 키

• 외래키 (FOREIGN KEY)

  • 다른 테이블의 기본키를 참조해서 사용하는 키

🔎 EQUI JOIN

• 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 기법
• 두 테이블은 key를 통해 관계를 맺는다. (기본키를 가진 테이블이 부모)
• 관계의 종류 - 1:1, 1:n, n:m

• ✔ JONES의 사무실 위치를 출력하는 쿼리문

select emp.ename, dept.loc from emp, dept where emp.deptno = dept.deptno and emp.ename = 'JONES';

• 결과