CRUD
명령어 종류 : DDL, DML = Select
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;
desc emp1;
select * from emp1;
--이름이 홍길동인 사원을 삭제하시오.
delete from emp1 where ename = '홍길동';
commit;
--월급이 5500미만인 사람을 삭제
delete from emp1 where sal < 5500;
삭제는 커밋한 데이터도 모두 삭제해버리기 때문에 주위!!
--SQL문을 이용하여 데이터 가져오기2
--검색하고자 하는 값을 정확히 모를 경우 와일드 카드와 함께 사용할 수 있다.
--% 특정문자가 포함되기만 하고 % 이전이나 이후에 어떤 문자가 몇 개가 오든지 상관없다는 의미를 표현하기 위해서는 LIKE 연산자와 같이 사용
--이름이 F로 시작하는 사람을 찾는 쿼리문
select * from emp where ename like 'F%'; --% '와일드 카드'
--위치 상관 없이 이름 중에 A가 들어가있는 사람을 찾는 쿼리문
select * from emp where ename like '%A%';
--이름이 N로 끝나는 사람을 찾는 쿼리문 %부분에는 어떤 문자가 오든 몇 개가 오든지 상관없지만, 끝은 반드시 N
select * from emp where ename like '%N';
-- _(데시바) 는 한 문자에 대해서만 와일드 카드 역할, 자리수를 가리키는 기호
--이름이 두 번째 글자가 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%';
--NULL을 위한 연산자. IS로 접근 NULL은 미확정
--COMM을 받지 않는 사원을 검색하는 쿼리문
SELECT * FROM EMP WHERE COMM IS NOT NULL;
NULL의 의미
자바에도 존재
A a = new A()
a = null; // 참조 관계를 끊는다.더 이상 A개체에 접근 못함.
->a = null;
은 더 이상 사용안하므로 메모리를 회수해도 된다는 명령.(가비지(JVM역할))
DB에도 존재
--정렬을 위한 ORDER BY절, seiretsu
--사원들의 급여를 오름차순(ASC)으로 정렬하는 쿼리문 내림차순(DESC), default는 오름차순
SELECT * FROM EMP ORDER BY SAL ASC;
SELECT * FROM EMP ORDER BY SAL DESC;
--가장 최근에 입사한 사원부터 출력
SELECT * FROM EMP ORDER BY HIREDATE DESC;
--사번순으로 출력
SELECT * FROM EMP ORDER BY EMPNO ASC;
--이름순으로 출력
SELECT * FROM EMP ORDER BY ENAME ASC;
--사원들이 소속되어 있는 부서의 번호를 출력
SELECT DEPTNO FROM EMP; --중보되어 나온다
--동일한 데이터 값들이 중복되어 출력되지 않도록 사용. 컬럼명 앞에 DISTINCT선언
SELECT DISTINCT DEPTNO FROM EMP;
--사원들의 JOB종류 출력
SELECT DISTINCT JOB FROM EMP;
--사원들의 10% SAL 인상(상승)한 결과값
SELECT ENAME, (SAL * 1.1) AS "연봉 인상" FROM EMP ; --AS를 사용해 컬럼명을 변경, AS는 생략 가능, 뛰어쓰기 할 경우는 " "로 감싸주기
--DUAL테이블
--산술 연산이나 가상 컬럼 등의 값을 한번만 출력하고 싶을 때 가장 많이 사용한다.
SELECT 15 * 67 FROM DUAL; --DUAL->한 줄 테이블, 한 행으로 결과를 출력하기 위한 테이블
SELECT SYSDATE FROM DUAL;
--그룹함수
--사원의 총 급여 구하기
SELECT SUM(SAL) AS 총합 FROM EMP; --SUM함수 총합
SELECT AVG(SAL) FROM EMP; --AVG함수 평균
SELECT MAX(SAL) FROM EMP; --최대값
SELECT MIN(SAL) FROM EMP; --최소값
--총사원수
SELECT COUNT(*) FROM EMP;
--사원 테이블의 사원들 중에서 COMM을 받는 사원의 수를 구하는 쿼리문
SELECT COUNT (COMM) FROM EMP;
--JOB종류 와 JOB 수
SELECT COUNT(DISTINCT JOB) FROM EMP;
SELECT ENAME, MAX(SAL) FROM EMP; --출력 불가
SELECT MIN(SAL), MAX(SAL), COUNT(*) FROM EMP; --집합 함수끼리는 같이 선언 가능
❌ 안돼는 이유!
SELECT ENAME, MAX(SAL) FROM EMP;
⭕️ 가능!
SELECT MIN(SAL), MAX(SAL), COUNT(*) FROM EMP; --집합 함수끼리는 같이 선언 가능
그룹함수란
함수 종류
COUNT함수
--GROUP BY절
--소속 부서별, 평균 급여를 구하는 쿼리문
SELECT AVG(SAL) AS 부서별평균 FROM EMP GROUP BY DEPTNO; --GROUP BY DEPTNO ->부서별로 묶어서 그 안에서 연산
SELECT DEPTNO, AVG(SAL) AS 부서별평균 FROM EMP GROUP BY DEPTNO; --부서별로 묶었기 때문에 DEPTNO컬럼을 출력할 수 있다.
--JOB별로 급여 평균 구하는
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;
--소속 부서별 급여 총액과 평균 급여
SELECT SUM(SAL) AS "급여 총액", AVG(SAL) AS "평균 급여" FROM EMP GROUP BY DEPTNO;
--부서별 사원의 수와 COMM을 받는 사원 수를 계산
SELECT COUNT(*), COUNT(COMM) FROM EMP GROUP BY DEPTNO;
--평균 급여가 2000이상인
--부서별 부서의 번호와 부서별 평균 급여를 출력하는 쿼리문
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
--부서별 평균 급여가 2000이상인 부서의 번호와 부서별 평균 급여를 출력하는 쿼리문
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;
--조건을 걸 떄는 HAVING뒤에 선언. HAVING 다음은 반드시 그룹함수 / WHERE 다음은 단순 컬럼
--부서의 최대값과 최소값을 구하되, 최대 급여가 2900이상인 부서만 출력하는 쿼리문
SELECT MAX(SAL), MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) >= 2900;
JOIN이란...
--쿼리의 핵심 = JOIN
SELECT * FROM EMP;
SELECT * FROM DEPT;
--JOIN의 개념 = 카테이션 곱 =전체의 모든 경우의 수
SELECT * FROM EMP, DEPT; --EMP=12 와 DEPT=4의 모든 경우의 수 (곱하기)
--이름이 SMITH인 사람의 부서명을 출력하는 쿼리(SMITH는 EMP테이블에 정보가 있고, 부서명(LOC))
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME = 'SMITH';
--두 테이블의 일치하는 컬럼(항목)을 기준으로 두 테이블을 합친다. (중복을 제거)
--테이블의 키는 중복되면 안된다.(PRIMARY KEY)
--다른 테이블의 PROMARY키를 참조하여 사용하면 그 키는 외래 키라고한다 -> 다른 테이블의 키를 가져오게되면 관계를 갖게된다(RDBMS)
--JONES의 사무실 위치를 출력
SELECT EMP.ENAME, DEPT.LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.ENAME = 'JONES';