SQL
-
맥에서 오라클 사용하기
https://velog.io/@devsaza/M1-M2-Mac-OS%EC%97%90%EC%84%9C-Oracle-DB-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0 -
맥에서 toad 설치가 안 되는 경우
https://dbeaver.io/download/
TOAD 단축키
- 전체 파일 실행: F5
- 선택 쿼리문 실행: ctrl+e / ctrl+enter
- sql 문장 실행: F9
- 테이블명 더블클릭 F4: 테이블 작성
데이터 관리하기 위해 배운 것
- JSON 포맷
- List, List
-> 이것들을 뽑아내기 위해 쿼리를 어떻게 작성해야 하는지 배워보자
개요
Oracle11g(PL/SQL 표준) 설치
- 관계지향형 데이터베이스 - RDBMS 제품이다
: 고정된 2차원 테이블을 통해서 데이터를 관리하고, 이런 데이터들의 영속성을 보장받기 위해서
이런 제품들을 사용하고 있다(우리가 사용한 변수는 유지가 안 되고 사라지니까). 양방향이며, 복잡한 산술식을 더 잘 처리한다.- 장점
: 데이터 양이 많고 복잡한 처리가 가능하다
: 양방향 서비스이기 때문에 한 쪽 테이블에 fk 지정하면 데이터 관리가 용이하다- 단점
: 상속을 지원하지 않는다
: 구조적으로 나누기 어렵다- R(Relation)DBMS: 관계를 나타내고, 관계 형태는 1:1, 1:n, n:n.
*이런 관계 형태는 객체지향 언어와 마찬가지다. 객체 사이의 관계를 정의해서 처리하니까(대신 여기는 단방향)- 객체지향언어 장점
: 나누기 쉽고 / 상속 지원되고 / 값 비교 가능, 동등한지도 비교 가능- 특징
: 객체간의 참조 연결 통해 조회가 가능하다
: 단방향만 가능(단방향*2로 해서 양방향 처리해야 함)(관계형은 조인으로 처리)객체모델(Object Model) vs 관계형모델(Relational Model)
- 객체모델에서는 String, 관계형 모델에서는 VARCHAR2
: 데이터 타입에 대한 차이 (EmpVO, DeptVO, List)
: 객체와의 관계를 RDB테이블로 변환하는 게 쉽지 않다
: 개발자가 직접 해야된다면 작업 실수나 변할 때마다 반영해야 한다는 점이 우려 - Hibernate 등장함(자동)- MyBatis는 sql을 버리지 않았다(쥐고 있다) - 튜닝팀 - 직관적이고 튜닝이 가능하다
- 하이브리드앱 나타나면서 NoSQL 등장 -> 굳이 MyBatis같이 sql문을 쥐고 있을 필요가 없다
어차피 텍스트 기반의 서버이니까(JSON형식 firebase도 NoSQL)- MyBatis는 DB 중심의 개발이라면, Hibernate는 어플리케이션 중심의 개발이다.
- Hibernate는 ORM framework이다(MyBatis는 SQL Mapper이다 )
: 자바클래스로 작성한 것을 자동으로 Table로 작성해줌
(객체모델을 통해서 테이블이 만들어진다)- 데이터를 사용하는 클라이언트 환경이 계속 바뀌어가고 있다.
: 오랫동안 데이터중심의 개발로 진행되어 왔다- 문제제기: 테이블 구조가 변경되면 다시 개발을 해야하니 번거롭지 않나?
테이블 설계
오라클(toad)2차원 테이블로 데이터를 관리함
- 테이블 구성요소 = row + cols
- 테이블 설계를 하려면?
- 컬럼 추가: 컬럼을 추가 해야 하고, 그 컬럼에는 대응하는 데이터 타입이 필요하고 크기도 결정해야 한다 -> 물리적인 모델링 단계에서 결정된다
- 제약조건: NOT NULL, Unique
- 인덱스 추가: 인덱스에 대한 전략을 어떻게 가져갈 것이냐에 따라 검색 속도에 영향을 미친다
: PK로 지정되면 인덱스가 제공됨 - 이게 unique index니까 중복되지 않는다. 중복되지 않는다는 것은 똑똑한 조건이다(2차로 필터링하지 않아도 되니까)
: ex. 5억명 집합에서 500번째 아이디가 발견되면 더 이상 검색하지 않아도 된다. 501번째를 굳이 검사하지 않아도 된다
DML(select, insert, update, delete)
select : 검색속도와도 관련이 있다. (ex. 3초 안에 조회가 안 되면 에러다)
- Spring 프레임워크를 활용한 개발을 진행하면 JPA API를 구현하게 됨
: 관계형 모델에 대한 이해가 있어야 객체 모델의 설계가 될 수 있다 - EMP
- DEPT
-- DML - 데이터 조작어
SELECT * FROM temp;
SELECT * FROM tdept;
SELECT
ename
FROM emp;
-- 테이블을 드라이브 할 때 인덱스 정보 읽는다
-- 테이블을 엑세스하지 않고도, 즉 인덱스 정보만으로도 '조회가 된다'
-- 테이블을 access하지 않고 조회됨
SELECT
empno
FROM emp;
SELECT
empno, rowid rid
FROM emp;
SELECT ename
FROM emp
WHERE rowid = 'AAARE8AAEAAAACTAAC';
SELECT
empno
FROM emp
ORDER BY empno desc;
-- 옵티마이저가 조건을 만족하는 정보를 직접 찾아온다
-- 병렬처리 지원, 클러스터 지원
SELECT
ename
FROM emp
ORDER BY empno desc;내용정리: 테이블 스키마
- PK, 데이터타입, 인덱스, 제약조건, 물리적인 위치
- DML(select) - 조건검색 포함
- 옵티마이저가 일꾼이다
- 일의 순서가 있다. 순서에 따라 속도차이가 난다
- FROM절에 테이블이 여러 개(조인을 공부해야 하는 이유 - 객체모델설계가 중요하다) 올 수도 있다
-> 어떤 게 먼저 실행될까?(순서 문제)rowid
: 18자리 / 물리적인 정보를 쥐고 있는 값이 데이터베이스에 있다
: DBMS(데이터베이스 매니저)가 가지고 있는 모든 데이터의 각각의 고유한 식별자다
: rowid는 index와도 깊은 관련이 있는데, index 테이블은 index key와 rowid로 이뤄져 있기 때문이다.
: 이렇게 저장공간을 가지고 있는 rowid는 마치 물리적인 정보라고 생각할 수 있지만 실제로 존재하지 않으며, index 테이블 내에 있는 rowid는 해당 데이터를 찾기 위한 하나의 논리적인 정보일 뿐이다.
1) 6자리: 데이터의 오브젝트 번호
2) 3자리: 상대적 파일 번호
3) 6자리: 블록번호
4) 3자리: 블록 내의 행 번호
-- 부적합한 식별자: 자바 에러가 아님
SELECT
dname
FROM emp;
-- SELECT
-- dname
-- FROM emp,dept;
-- 카타시안곱: 집합을 복제해서 총합,소계,계를 구할 때 사용
-- 왜 복제인가? 하나의 값이 총계에서 한 번,소계에서 한 번,계를 구할 때 한 번
-- 같은 값이 4번 사용되어야 한다
-- 한 명의 사원이 근무할 수 있는 부서의 종류가 모두 출력되었기 때문에 14*4=56명의 직원이 나오고 있다
SELECT
dept.dname,emp.deptno,dept.deptno
FROM emp,dept;
-- 쓰레기 값이 포함되어 있으니 제거해줘야 함
-- Natural join
-- 1:n의 관계: 이런 경우는 조인을 걸 수 있다
SELECT
dept.dname,emp.deptno,dept.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno;
delete from dept where deptno IN(60,80,90);
commit;
SELECT * FROM dept;
rollback;내용정리
- select 뒤에는 컬럼명이 온다. 여러 개가 올 수 있다. (부적합한 식별자는 컬럼명이 문제다)
- select문 뒤에 연산처리도 가능하다
- 컬럼명이 오는 자리에 함수를 중첩해서 사용할 수 있다
카테시간 곱(Cartesian Product)
=> union(교집합)/ interction(합집합)
- From절에 2개 이상의 Table이 있는데 두 Table 사이에 유효 join 조건을 적지 않았을 때, 해당 테이블에 대한 모든 데이터를 전부 결합하여 Table에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것
- 즉, 카테시안 곱은 join 쿼리 중에 WHERE 절에 기술하는 join 조건이 잘못 기술되었거나 아예 없을 경우 발생
ex1. SELECT 1+2,2*3,10/2 FROM dual;
ex2. SELECT max(sal),min(sal) FROM emp;
ex3-1. SELECT count(empno) FROM emp;: 이런 max,min,avg,count() 같은 것을 그룹함수라 한다
: 왜 그룹함수라고 할까?
→ 전체조회를 해야 답을 구할 수 있는 함수(검색속도가 느리다). 일부 정보만 보고 결과를 말할 수 없고, 시간이 걸리더라도 전체 직원을 모두 검사해야 함
ex3-2. SELECT count(comm) FROM emp;: commition 안에는 null 값이 포함돼 있기 때문에 x
- from절 뒤에는 집합이 온다.
: 왜 테이블이라고 하지 않고 집합이라고 할까?
→ 집합 자리에 select문도 가능하기 때문(인라인뷰)
: from절 뒤에는 여러 개의 집합도 가능하다
→ 이 경우 조인의 대상이다
- where절: 조건검색시 사용함(앞에는 컬럼, 뒤에는 값)
ex. where deptno = 10;- select문은 only read: 집합의 내용을 읽기만 하는 것(어떤 물리적인 변화 없다)
: select문은 commit(물리적으로 테이블에 내용 반영)이나 rollback의 대상이 아니다
오라클 연습문제
- 월 급여는 연봉을 18로 나누어 홀수 달에는 연봉의 1/18이 지급되고, 짝수달에는 연봉의 2/18가 지급된다고 가정했을 때 홀수 달과 짝수 달에 받을 금액을 나타내시오.
SELECT salary FROM temp;
SELECT salary/18, salary*2/18 FROM temp;
SELECT salary/9 FROM temp;
SELECT dname"부서명1",dname as"부서명2",dname"부서 이름"
FROM dept; -- 별칭사용
SELECT salary/18 as "홀수달급여", salary*2/18 as "짝수달급여" FROM temp;
-- 소수 첫 번째 자리에서 반올림인지, 아니면 두 번째 자리에서 반올림인지?
SELECT round(12345.6789,1), round(12345.6789,-1), round(12345.6789,0) FROM dual;
-- 몇 번째 자리에서 반올림을 하나?
SELECT round(salary/18,-1) as "홀수달급여", round(salary/18,0)
,round(salary*2/18,-1) as "짝수달급여"
FROM temp;
SELECT round(salary/18,-1)||'원' as "홀수달급여"
,round(salary*2/18,-1)||'원' as "짝수달급여"
FROM temp;
SELECT TO_CHAR(round(salary/18,-1), '999,999,999')||'원' as "홀수달급여"
,round(salary*2/18,-1)||'원' as "짝수달급여"
FROM temp; - TO_CHAR( )
-- 오라클에서도 형전환 함수가 있다
-- TO_CHAR():숫자형을 문자형으로 바꾼다
-- (관계지향형 데이터베이스는 동일하게)함수에 파라미터도 있고, 리턴값도 있다
SELECT sysdate FROM dual;
SELECT sysdate,TO_CHAR(sysdate,'YYYY-MM-DD')
,TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM')
,TO_CHAR(sysdate,'YYYY-MM-DD HH:MI:SS AM')
,TO_CHAR(sysdate,'day')
FROM dual;
<정답>
SELECT TO_CHAR(round(salary/18,-1), '999,999,999')||'원' as "홀수달급여"
,round(salary*2/18,-1)||'원' as "짝수달급여"
FROM temp;- 쿼리문 작성 스타일 참고
: 가로로 되어있는 값을 세로에 출력할 수 있어야 한다(세로에 있는 값을 가로에 출력)
-- 컬럼수가 증가하면 가로가 늘어남
SELECT 1,2,3,4,5 FROM dual;
-- 합집합을 사용하면 로우가 늘어남
SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual
UNION ALL
SELECT 3 FROM dual;- 조건에는 점조건(==,in구문(or문))과 선분조건(between A and B,LIKE)이 있다
- 차집합
-- 40만 나오는데
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
-- 전부 다 나옴(두 번째 인자 때문에)
SELECT deptno,dname FROM dept
MINUS
SELECT deptno,ename FROM emp;- 교집합
SELECT deptno FROM dept
INTERSECT
SELECT deptno FROM emp;- 합집합
: UNION ALL - 두 집합을 그대로 더하므로 중복제거가 없다(2차 가공을 하지 않기 때문에 UNION보다 검색 속도가 빠르다)
: UNION - 중복제거를 하기 때문에 정렬을 한다(중복제거 하려면 값을 비교해야하기 때문). 2차 가공을 하기 때문에 느리다
: 과금하기, 배민 1일 정산, 집계(통계), 분포도 계산 등에 필요하다
-- UNION ALL(중복제거X)
SELECT deptno FROM dept
UNIONALL
SELECT deptno FROM emp;
-- UNION(중복제거)
SELECT deptno FROM dept
UNION
SELECT deptno FROM emp;- 위에서 구한 월 급여에 교통비가 10만원씩 지급된다면(짝수달은 20만원)위의 문장이 어떻게 바뀔지 작성해 보시오.
<정답>
SELECT TO_CHAR((round(salary/18,-1)+100000),'999,999,999')||'원' as "홀수달급여"
,TO_CHAR((round(salary*2/18,-1)+200000),'999,999,999')||'원' as "짝수달급여"
FROM temp;SELECT TO_CHAR(123456789,'999,999')
,TO_CHAR(123456789,'999,999,999')
FROM dual;연습1. 사원중에서 인센티브를 받는 사람의 이름과 사원번호를 출력하시오
SELECT empno,ename FROM emp;
-- 인센티브를 받는 사람
SELECT empno,ename FROM emp
WHERE comm > 0;
SELECT empno,ename FROM emp
WHERE comm IS NULL;
SELECT empno,ename FROM emp
WHERE comm IS NOT NULL;
<정답>
SELECT empno,ename FROM emp
WHERE comm IS NOT NULL
AND comm > 0;
SELECT empno,ename FROM emp
WHERE comm IS NOT NULL
OR comm > 0;
SELECT empno,ename FROM emp WHERE comm IS NOT NULL
UNION
SELECT empno,ename FROM emp WHERE comm > 0; 연습2. 취미가 낚시 또는 등산인 경우를 구하시오
SELECT
emp_name,hobby
FROM temp
WHERE hobby IN('등산','낚시');연습3. hobby가 null 또는 등산인 경우를 구하시오
SELECT
emp_name,hobby
FROM temp
WHERE hobby = '등산'
OR hobby IS NULL;연습4. hobby가 null 또는 '낚시' 모두에 속하지 않는 경우를 구하시오
SELECT
emp_name,hobby
FROM temp
WHERE hobby not in(null,'낚시');
-- NULL과 낚시를 제외한 나머지 경우인 3건을 모두 검색할까?
-- 아니면 낚시인 경우만 제외한 9건을 검색할까?SELECT
emp_name,hobby
FROM temp
WHERE hobby IS NOT NULL
AND hobby !='낚시';- TEMP 테이블에서 취미가 NULL이 아닌 사람의 성명을 읽어오시오.
SELECT
emp_name
FROM temp
WHERE hobby IS NOT NULL - TEMP 테이블에서 취미가 NULL인 사람은 모두 HOBBY를 “없음”이라고 값을 치환하여 가져오고 나머지는 그대로 값을 읽어오시오.
<정답>
SELECT nvl(hobby,'없음')as"취미"FROM temp;SELECT ename,comm,nvl(comm,0)FROM emp;5.TEMP의 자료 중 HOBBY의 값이 NULL인 사원을 ‘등산’으로 치환했을 때 HOBBY가 ‘등산’인 사람의 성명을 가져오는 문장을 작성하시오.
SELECT emp_name FROM temp;
SELECT emp_name FROM temp WHERE hobby = '등산';
SELECT emp_name FROM temp WHERE hobby is null;
<정답>
SELECT emp_name FROM temp
WHERE nvl(hobby,'등산')='등산';- 데이터 타입
SELECT ename,empno FROM emp
WHERE empno = 7566;
SELECT ename,empno FROM emp
WHERE empno ='7566';
SELECT ename,empno FROM emp
WHERE empno =TO_NUMBER('7566');