DB 2일 (23.03.22)

1. DB 용어

1-1. DDL DML

Data Definition Language (Create)
Data Manipulation Language (Select Insert Update Delete)

1-2. DB의 데이터타입

CHAR(고정길이의 문자) VARCHAR2(가변길이의 문자) NUMBER(숫자)
DATE(날짜) LONG(가변 길이의 문자)

• CHAR : 속도가 빠르다. 고정해야할 것은 고정해야한다.
• VARCHAR : 속도가 느리지만, 메모리를 절약할 수 있다.

2. 테이블 만들기

• CREATE TABLE EMP1 (
  EMPNO NUMBER(4) DEFAULT 1000 NOT NULL, -- 기본값은 1000이고, NULL이 아니다
  ENAME VARCHAR2(10), -- 가변길이의 String
  JOB VARCHAR2(9),
  MGR NUMBER(4), -- 4자리 NUMBER
  HIREDATE DATE,
  SAL NUMBER(7,2), -- 7자리 가변길이 정수 + 2자리 가변길이 소수
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2)
  );

• DESC EMP1;

• INSERT INTO EMP1 VALUES (7839, '홍길동', 'MANAGER', 1234, '1981/05/05', 5000, NULL, 10);
  COMMIT;
  SELECT * FROM EMP1;

• UPDATE EMP1 SET SAL = SAL * 1.1;

• INSERT INTO EMP1 VALUES (7000, '홍길순', 'MANAGER', 1234, '1981/05/05', 5000, NULL, 10);
  UPDATE EMP1 SET SAL = SAL * 1.1 WHERE ENAME = '홍길순';

• UPDATE EMP1 SET DEPTNO = 30 WHERE DEPTNODEPT = 10;

• UPDATE EMP1 SET JOB='EMPLOYEE' WHERE JOB = 'MANAGER';

• UPDATE EMP1 SET HIREDATE=SYSDATE;

• DELETE FROM EMP1 WHERE DEPTNO = 10;
  SELECT * FROM EMP1;
  COMMIT;

select * from dept;
select * from emp;
select * from salgrade;
--WHERE 조건은 SELECT문에서 마지막에 쓸 수 있는데 원하는 레코드만 검색하고자 할 때 쓰인다
--예) 사원테이블(emp)에서 급여 (SAL)가 3000 이상인 사원을 출력하는 쿼리문
select * from emp where sal > 3000;
--비교 연산자를 사용하여 부서 번호(DEPTNO)가 20인 사원에 관한 정보만 출력하는 쿼리문
select * from emp where deptno = 20;
----비교 연산자를 사용하여 부서 번호(DEPTNO)가 10인 사원에 관한 정보만 출력하는 쿼리문
select * from emp where deptno = 10;
----비교 연산자를 사용하여 부서 번호(DEPTNO)가 10 이상 인 사원 관한 정보만 출력하는 쿼리문
select * from emp where deptno >= 10;

--이름(ENAME)이 FORD인 사람의 사번(empno), 이름(ename), 급여(SAL)를 출력하는 쿼리문
select empno, ename, sal from emp where ename= 'FORD';
select sal from emp where ename= 'SMITH';

--테이블의 구조를 확인하기 위한 명령어이다
desc emp;
desc dept;
--1982년 1월 1일 이후에 입사한 사원을 출력하는 쿼리문
select * from emp where hiredate >= '82/01/01' ;
--1981년 1월 1일 이후에 입사한 사원을 출력하는 쿼리문
select * from emp where hiredate >= '81-01-01' ;

-- 10번부서 이고, 잡이 메니져인 사람
select * from emp where deptno=10 and job ='MANAGER';

-- sal 1000이상이고, 잡이 메니져인 사원
select * from emp where sal>=1000 and job ='MANAGER' and deptno = 10;

-- 10번 부서가 아닌사람
select * from emp where  deptno != 10;
select* from emp;
select * from emp where not deptno = 10;

--급여가 2000~3000 사이의 사원을 검색하는 쿼리문
select * from emp where sal >=2000 and sal <=3000;
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

--급여가 2000 미만이거나 3000 초과인 사원을 검색하는 쿼리문
select * from emp where sal < 2000 or sal > 3000;
SELECT * FROM EMP WHERE SAL not BETWEEN 2000 AND 3000;

--1981년에 입사한 사원을 출력하는 쿼리문
 SELECT * FROM EMP WHERE HIREDATE BETWEEN '81-01-01' AND '81.12.31';
 
 --부서 번호가 10번 또는 20번인 사원
SELECT * FROM EMP WHERE deptno =10 or deptno =20;

 --부서 번호가 10번 또는 20번인 사원
 SELECT * FROM EMP WHERE job ='MANAGER' or job='CLERK';
 --커미션(COMM)이 300 혹은 500 혹은 1400인 사원이 있는지 검색하는 쿼리문
 SELECT * FROM EMP WHERE comm =300 or comm = 500 or comm= 1400; 
 SELECT * FROM EMP WHERE COMM in (300, 500, 1400);
 --예)커미션(COMM)이 300 혹은 500 혹은 1400이 아닌 사원이 있는지 검색하는 쿼리문 
SELECT * FROM EMP WHERE comm !=300 and comm != 500 and comm != 1400; 
SELECT * FROM EMP WHERE  COMM not in (300, 500, 1400);

-- 오라클 테이블 생성
create table emp1(
    empno number(4) default 1000 not null,
    ename varchar2(10),
    job varchar2(9),
    mgr number(4),
    hiredate date,
    sal number(7,2), --7자리의 가변 길이 정수와 2자리의 가변길이 소수
    comm number(7,2),
    deptno number(2)
    );

desc emp1;
-- insert
insert into emp1 values(7839,'홍길동','메니져',1234,'1981/05/05',5000,null,10);
insert into emp1 values(7000,'홍길순','메니져',1234,'1981/05/05',5000,null,10);
commit;
select * from emp1;
--사원의 급여를 10% 인상시키는 UPDATE문을 만드는 쿼리문
--update
update emp1 set sal = sal *1.1;
--홍길순 월급 업데이트
update emp1 set sal = sal * 1.1 where ename='홍길순';
--부서번호가 10번인 사원을 부서번호를 30번으로 수정하시오.
update emp1 set deptno = 30 where deptno  =10;

--job 이 메니져인 사람을 변경 -> 사원
update emp1 set job = '사원' where job ='메니져';
-- 모든 입사일을 오늘로 수정하는 쿼리문
update emp1 set hiredate = sysdate;

--삭제

delete from emp1 where deptno = 10;

---

• DELETE FROM EMP1 WHERE ENAME = '홍길동';
• DELETE FROM EMP1 WHERE SAL < 5500;

3. 문자열 다루기 (와일드카드)

• SCOTT.sql > EMP에서 찾는다.

3-1. % : 글자 상관 없이, 위치에 따라 출력

• SELECT * FROM EMP WHERE ENAME LIKE 'F%';
  (F% : F로 시작되는 이름을 가진 사람 출력)

• SELECT * FROM EMP WHERE ENAME LIKE '%A%';
  (%A% : A가 필드의 어디에든지 있으면 된다.)

• SELECT * FROM EMP WHERE ENAME LIKE '%N';
  (%N : 이름이 N으로 끝나는 사람 출력)
  

• SELECT * FROM EMP WHERE ENAME NOT LIKE '%A%';
  (이름에 A가 들어가지 않는 사람 출력)

3-2. _ : n번째 글자

• SELECT * FROM EMP WHERE ENAME LIKE '_A%';
  (들어간 자리수만큼은 다른 문자가 있어야 한다.)

3-3. NULL

각 프로그램 속의 NULL
(Java) 참조형에만 사용, 아직 참조할 주소가 없는 초기화 상태
(Oracle) 미확정. 알 수 없는 값. (연산, 할당, 비교 불가능)

• SELECT * FROM EMP WHERE COMM IS NULL;

• SELECT * FROM EMP WHERE COMM IS NOT NULL;

4. 필드 정렬하기 (order by / asc? desc?)

• SELECT * FROM EMP ORDER BY SAL ASC;
  (오름차순 : default)

• SELECT * FROM EMP ORDER BY SAL DESC;
  (내림차순)

5. DISTINCT (데이터 값 중복 없애기)

• SELECT DEPTNO FROM EMP;
• SELECT DISTINCT DEPTNO FROM EMP;
• SELECT DISTINCT JOB FROM EMP;

6. 필드값을 고치지 않고 출력하기

• SELECT ENAME, SAL FROM EMP;
• SELECT ENAME, SAL * 1.1 FROM EMP;

• SELECT ENAME, (SAL * 1.1) AS 연봉인상 FROM EMP;
• SELECT ENAME, (SAL * 1.1) 연봉인상 FROM EMP;
• SELECT ENAME, (SAL * 1.1) "연봉 인상" FROM EMP;
  

7. DUAL 테이블

한 행의 결과를 출력하기 위한 테이블 (더미 데이터)

• SELECT 15*67 FROM DUAL;
• SELECT SYSDATE FROM DUAL;

8. 그룹 함수

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

8-1. 계산하기

• SELECT SUM(SAL) FROM EMP;
• SELECT AVG(SAL) FROM EMP;
• SELECT MAX(SAL) FROM EMP;
• SELECT MIN(SAL) FROM EMP;

8-2. COUNT 함수 : 값을 갖고 있는 행의 개수 계산 (NULL 값에 대한 개수는 세지 않는다!)

• SELECT COUNT(*) "총 사원 수" FROM EMP;
• SELECT COUNT(*) FROM EMP WHERE COMM IS NOT NULL;
• SELECT COUNT(COMM) FROM EMP;
• SELECT DISTINCT JOB FROM EMP;
• SELECT COUNT(DISTINCT JOB) FROM EMP;

8-3. GROUP BY

• SELECT AVG(SAL) FROM EMP;
• SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO;
• SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
  (부서번호 별로 급여의 평균을 구한다.)

• SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;
• SELECT DEPTNO, SUM(SAL) "급여 총액", AVG(SAL) "평균 급여" FROM EMP GROUP BY DEPTNO;
• SELECT DEPTNO, COUNT(*) "사원 수", COUNT(COMM)"커밋 있음" FROM EMP GROUP BY DEPTNO;

8-4. HAVING (GROUP BY에 조건을 줄 때)

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

• SELECT DEPTNO, MAX(SAL), MIN(SAL) FROM EMP GROUP BY DEPTNO;
• SELECT DEPTNO, MAX(SAL), MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) >= 2900;

9. Join하기

9-1. CARTESIAN PRODUCT (카테이션 곱)

• SELECT * FROM EMP;

• SELECT * FROM DEPT;
  -- 10 ACCOUNTING 20 RESEARCH 30 SALES

• SELECT * FROM EMP, DEPT;
  (12 X 4)

• SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
  (공통된 조건만 나올 수 있도록 하기 >> 12개 출력)

9-2. 조인하기

primary key : 중복되면 안되는 컬럼을 표시하는 것
foreign key : 외부에 있는 것을 참조하게 되는 키

• SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME = 'SMITH';
• SELECT EMP.ENAME, DEPT.LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME = 'JONES';