4주차 정리(1/24~1/28)

자바를 마무리하고 SQL 학습을 시작했다.

오라클 데이터 베이스를 사용하기 때문에 이미 제공해주는 테이블과 데이터를 이용해서 작업했다.

(SCOTT/TIGER, hr/hr 계정을 이용해 학습)

제일 처음은 select 문에 대해 학습했다.

select 기본 syntax
    select 절 from 절;
정렬 포함
    select 절 from 절 order by 절;
    asc, desc
조건절 포함
    select 절 from 절 where 절;

참고
    - dual table
        잉여 테이블, 데이터 없음, syntax 적으로 from 필요시 사용
        ex) select 2+3 from dual; select sysdate from dual;

select에는 사용가능한 여러 함수들이 있다.

단일행 함수와 집계 함수로 크게 나눠볼 수 있는데, 

우선 단일행 함수에는 숫자, 문자, 날짜 함수등이 있다.

abs(), round(), length(), next_day() 등이 여기에 속한다.

 

그룹함수는 다음과 같다.

-- 그룹함수란? 다수의 행 데이터를 한번에 처리
-- 장점 : 함수 연산시 null 데이터를 함수 내부적으로 사전에 고려해서 null값 보유한 field는 함수 로직 연산시 제외, sql 문장 작업 용이
/*
1. count() : 개수 확인 함수
2. sum() : 합계 함수
3. avg() : 평균
4. max(), min() : 최대값, 최소값 
*/
 
/* 기본 문법
1. select절
2. from 절
3. where절 

 * 그룹함수시 사용되는 문법
1. select절 : 검색하고자 하는 속성
2. from절	: 검색 table
3. group by 절 : 특정 조건별 그룹화하고자 하는 속성
4. having 절 : 그룹함수 사용시 조건절
5. order by절 : 검색된 데이터를 정렬

 

다음은 진짜 중요한 개념이자 이해를 잘 해야 하는 개념인 join이다.

1. 조인이란?
	다수의 table간에  공통된 데이터를 기준으로 검색하는 명령어
	다수의 table이란?
		동일한 table을 논리적으로 다수의 table로 간주
			- self join
				예) 상사의 사번으로 상사의 이름을 검색
		물리적으로 다른 table간의 조인
  
2. 사용 table 
	1. emp & dept 
	  : deptno 컬럼을 기준으로 연관되어 있음

	 2. emp & salgrade
	  : sal 컬럼을 기준으로 연관되어 있음

  
3. table에 별칭 사용 
	검색시 다중 table의 컬럼명이 다를 경우 table별칭 사용 불필요, 
	서로 다른 table간의 컬럼명이 중복된 경우,
	컬럼 구분을 위해 오라클 엔진에게 정확한 table 소속명을 알려줘야 함
	- table명 또는 table별칭
	- 주의사항 : 컬럼별칭 as[옵션], table별칭 as 사용 불가


4. 조인 종류 
	1. 동등 조인
		 = 동등비교 연산자 사용
		 : 사용 빈도 가장 높음
		 : 테이블에서 같은 조건이 존재할 경우의 값 검색 

	2. not-equi 조인
		: 100% 일치하지 않고 특정 범위내의 데이터 조인시에 사용
		: between ~ and(비교 연산자)

	3. self 조인 
		: 동일 테이블 내에서 진행되는 조인
		: 동일 테이블 내에서 상이한 칼럼 참조
			emp의 empno[사번]과 mgr[사번] 관계

	4. outer 조인 
		: 조인시 조인 조건이 불충분해도 검색 가능하게 하는 조인
		: 두개 이상의 테이블이 조인될때 특정 데이터가 모든 테이블에 존재하지 않고 컬럼은 존재하나 null값을 보유한 경우
		  검색되지 않는 문제를 해결하기 위해 사용되는 조인
		  null 값이기 때문에 배제된 행을 결과에 포함 할 수 있으며 (+) 기호를 조인 조건에서 정보가 부족한 컬럼쪽에 적용
		
		: oracle DB의 sql인 경우 데이터가 null 쪽 table 에 + 기호 표기

여러개의 테이블에서 데이터를 연결해서 가져오거나 여러 조건을 붙여서 데이터를 뽑고 싶다면 서브쿼리를 이용하면 된다.

/*
	select
	from (select 절)
	where (select 절)
*/

-- select문 내에 포함된 또 다른 select문 작성 방법
-- 참고 : join 또는 subquery로 동일한 결과값 검색

-- 문법 : 비교 연산자(대소비교, 동등비교) 오른쪽에 () 안에 select문 작성 
--	   : create 및 insert 에도 사용 가능
--	   : tip - 개발시 다수의 test 위해서 원본이 아닌 복사본 table활용 권장 
-- 실행순서 : sub query가 main 쿼리 이전에 실행

다음은 DDL이다. 테이블 생성과 삭제, 구조 수정을 담당하는 것들이다.

-- DDL(Data Definition Language)
/*
 * DB에 데이터를 CRUD 작업 가능하게 해주는 기본 구성
 [1] table 생성 명령어
    create table table명(
		칼럼명1 칼럼타입[(사이즈)] [제약조건] ,
		칼럼명2....
    ); 

[2] table 삭제 명령어
	drop table table명;
	- 삭제할때 테이블이 없으면 에러발생

[3] table 구조 수정 명령어
	alter table
*/

다음은 데이터 조작언어인 DML이다.

- DML : Data Mainpulation Language
            데이터 조작 언어
	   (select/insert/update/delete 모두 다 DML)
	   - 이미 존재하는 table에 데이터 저장, 수정, 삭제, 검색 
	- create는 트랜젝션과 무관
	- insert/update/delete 문장의 필수항목
		: 작업 후에 실제 DB에 저장시에는 반드시 commit;명령어 필수
		: DB 자체에는 임시 저장소와 영구 저장소로 분리
			위 작업은 commit 명령어 전까지는 임시 저장소에 저장
			commit 실행시 영구 저장 영역으로 이관
			rollback 실행시 임시 저장소에서도 휘발
	- 트랜젝션
		여러 작업을 마치 하나의 작업으로 간주 (예시 - 계좌 이체의 개별 작업들이 다 성공시에만 실 계좌이체 성공)
		commit - 영구 저장, rollback - 복원
		- application 으로 개발시에 db에 insert 후에 커밋 안하고 데이터 검색시 검색 불가(에러)
	참고 : DBeaver는 기본이 auto commit 기능 보유
    
1. insert sql문법
	1-1. 모든 칼럼에 데이터 저장시 
		- table 구조상의 컬럼 순서에 맞게 모든 데이터 저장시 사용하는 문법
		- 컬럼명 명시 생략 
		insert into table명 values(데이터값1, ...)

	1-2.  특정 칼럼에만 데이터 저장시,
		명확하게 칼럼명 기술해야 할 경우 
		insert into table명 (칼럼명1, ...) values(칼럼과매핑될데이터1...)

	1-3. 하나의 sql문장으로 다수의 table에 데이터 입력 방법
		insert all 
			into table명 [(칼럼명,...)] values(데이터,,,)		
		select 검색칼럼 from....;

2. update 
	2-1. 모든 table(다수의 row)의 데이터 한번에 수정
		- where조건문 없는 문장
		- update table명 set 칼럼명=수정데이타;

	2-2. 특정 row값만 수정하는 방법
		- where조건문으로 처리하는 문장
		- update table명 set 칼럼명=수정데이타 where 조건sql;

다음은 제약 조건이다.

-- DB 자체적으로 강제적인 제약 사항 설정
-- 개발자 능력 + 시스템(HW/SW) 다 유기적으로 연계
-- 모델링하는 테이블의 제약조건 설정은 개발자의 책임
-- db별 사전 테이블 제공
	-- db별 자체적인 관리 table, 유저들은 select 만 가능

/*
1. table 생성시 제약조건을 설정하는 기법 

2. 제약 조건 종류
	emp와 dept의 관계
	
	2-1. PK[primary key] - 기본키, 중복불가, null불가, 데이터들 구분을 위한 핵심 데이터
		: not null + unique
	2-2. not null - 반드시 데이터 존재
	2-3. unique - 중복 불가 , null 허용
	2-4. check - table 생성시 규정한 범위의 데이터만 저장 가능 
	2-5. default - insert시에 특별한 데이터 미저장시에도 자동 저장되는 기본 값
					- 자바 관점에는 멤버 변수 선언 후 객체 생성 직후 멤버 변수 기본값으로 초기화
	* 2-6. FK[foreign key] 
		- 외래키[참조키], 다른 table의 pk를 참조하는 데이터 
		- table간의 주종 관계가 형성
		- pk 보유 table이 부모, 참조하는 table이 자식
		- 부모의 미 존재하는 데이터를 자식에서 새로 생성가능? 불가 
		- 자식 table들이 존재할 경우 부모table만 삭제 가능? 불가
			- 해결책 : 관계를 해제도 가능하나 가급적 분석설계시 완벽하리만큼 고민후 설계
	

3. 사용자 정의하는 제약 조건에 제약 조건명 명시하는 방법
	3-1. oracle engine이 기본적으로 설정
		- 사용자가 제약 조건에 별도의 이름을 부여하지 않으면 오라클 자체적으로 SYS_시작하는 이름을 자동 부여
		- SYS_Xxxx

	3-2. sql개발자가 직접 설정
		- table명_컬럼명_제약조건명등 기술..단 순서는 임의 변경 가능
			: dept의 deptno이 제약조건명
				PK_DEPT
				pk_dept_deptno
		- 약어 사용도 가능[분석, 설계시 용어사전 제시후 작성 권장]
	
4. 제약조건 선언 위치
	4-1. 컬럼 레벨 단위
		- 컬럼선언 라인에 제약조건 설정 

	4-2. 테이블 레벨 단위 
		- 모든 컬럼 선언 직후 별도로 제약조건 설정 

	4-3. 이미 생성된 테이블에 제약조건 추가 및 수정
		- alter
		- 현업에서는 model toll로 모델링 필수로 작업
			- 잘 구축된 모델링인 경우 툴로 sql 자동 생성
			- 대부분의 툴 테이블 생성 후 alter 명령어로 제약조건 추가 형식으로 sql 생성
	
5. 오라클 자체 특별한 table
	5-1. user_constraints
		- 제약조건 정보 보유 table
		- 개발자가 table의 데이터값 직접 수정 불가
		- select constraint_name, constraint_type, table_name 
			from user_constraints;

6. 이미 존재하는 table의 제약조건 수정(추가, 삭제)명령어
	6-1. 제약조건 추가
		alter table 테이블명 add constraint 제약조건명 제약조건(컬럼명);
		alter table dept01 add constraint dept01_deptno_pk primary key(deptno);
		
	6-2. 제약조건 삭제(drop)
		- table삭제 
		alter table 테이블명 cascade constraint;
		
		alter table 테이블명 drop 제약조건명;
		alter table dept01 drop primary key;
		
	6-3. 제약조건 임시 비활성화
		alter table emp01 disable constraint emp01_deptno_fk;

	6-4. 제약조건 활성화
		alter table emp01 enable constraint emp01_deptno_fk;

다음은 알아두면 유용한 view 개념이다.

view 사용을 위한 필수 선행 설정
	1단계 : admin 계정으로 접속
	2단계 : view 생성해도 되는 사용자 계정에게 생성 권한 부여
		> connect system/manager
		> grant create view to SCOTT;
		> conn SCOTT/TIGER

1. view 에 대한 학습
	- 물리적으로는 미 존재, 단 논리적으로 존재
	- 물리적(create table)
	- 논리적(존재하는 table들에 종속적인 가상 table)
	- emp 또는 dept 처럼 create table 이라는 명령어로 table 생성 안함
	- 이미 존재하는 table로 파생되는 가상의 table
	- 원본 table 없이는 생성 불가

2. 개념
	- 보안을 고려해야 하는 table의 특정 컬럼값 은닉
	또는 여러개의 table의 조인된 데이터를 다수 활용을 해야 할 경우
	특정 컬럼 은닉, 다수 table 조인된 결과의 새로운 테이블 자체를 
	가상으로 db내에 생성시킬수 있는 기법 

	예시
	 	- emp의comm은 반드시 받는 자만 인지, 존재 자체도 모르는 직원들도 있다 가정시
		 	- 일반 직원들에겐 comm 은닉 필수
			- comm을 제외한 view 자체를 생성해서 제공

3. 문법
	- create와 drop : create view/drop view
	- crud는 table과 동일
		- 원본 table에도 crud 작업 영향을 미침

4. view 기반으로 crud 반영시 실제 원본 table에도 반영이 된다.

5. 종류
	5-1. 단일 view : 별도의 조인 없이 하나의 table로 부터 파생된 view
	5-2. 복합 view : 다수의 table에 조인 작업의 결과값을 보유하는 view
	5-3. 인라인 view : sql의 from 절에 view 문장  

6. 실습 table
	-dept01 table생성 -> dept01_v view 를 생성 -> crud -> view select/dept01 select

rownum에 대한 설명은 아래와 같다.

-- *** rownum
-- oracle 자체적으로 제공하는 컬럼
-- table 당 무조건 자동 생성
-- 검색시 검색된 데이터 순서대로 rownum값 자동 반영(1부터 시작)

-- *** 인라인 뷰
	-- 검색시 빈번히 활용되는 스펙
	-- 다수의 글들이 있는 게시판에 필수로 사용(paging 처리)
	-- 서브쿼리의 일종으로 from절에 위치하여 테이블처럼 사용
	-- 원리 : sql문 내부에 view를 정의하고 이를 테이블처럼 사용

mySQL에 auto_increment가 있다면 오라클엔 sequence가 있다.

1. 시퀀스 
	: 순차적인 순서 번호를 자동으로 반영할수 있는 매우 유용한 기술
	: 기본은 1씩 자동 증가
		- 증가치, 최대값 추가 설정도 가능
		- 권장 : 하나의 시퀀스를 다수의 table에서 사용 비추

2. 대표적인 활용 영역
	- 게시물 글번호에 주로 사용
	- insert 시에 고유한 순차적인 번호를 부여하는 기술
	- 게시글 테이블에 어떤 컬럼에 적용할지 먼저 분석, 설계 후 생성
	- 주의 사항
		: 시퀀스는 여러 테이블에서 공유 금지

3. sequence가 보유한 속성
	1. nextval - 새로 적용되는 숫자 / insert시에만 적용
	2. currval - 현재 sequence값

4. 참고
	create 명령어로 생성 가능 한 것: table, view, sequence

검색을 용이 하게 하기 위해선 인덱스가 중요하다. sql에도 당연히 존재하는 데 설명은 다음과 같다.

1. db의 빠른 검색을 위한 색인 기능의 index 학습
	- primary key는 기본적으로 자동 index로 설정됨 
	- db 개발자가 필요에 의해서 특정 컬럼에 인덱스 기능 추가 가능
	
	- DB 자체적으로 빠른 검색 기능 부여
		이 빠른 검색 기능 - index
	- 어설프게 사용자 정의 index 설정시 오히려 검색 속도 다운
	- 데이터 셋의 15% 이상의 데이터들이 잦은 변경이 이뤄질 경우 index 설정 비추

2. 실행 속도 체크를 위한 옵션 명령어
	set timing on
	set timing off

3. sql명령문의 검색 처리 속도 향상을 위한 oracle db 자체의 객체


4. 주의사항
	- index가 반영된 컬럼 데이터가 수시로 변경되는 데이터라면 index 적용은 오히려 부작용