Oracle_01

* 오라클 설치
일단 설치법에 앞서 오라클을 설치하면 생기게 되는 문제
- 바이러스에 취약해 진다(..아마도)
- 컴퓨터의 속도가 현저히 느려짐을 체감한다(사양이 낮은 컴은 똥컴이 된다)

1. setup.exe 실행
다운로드는 알아서 받고, 현재 버젼은 11g까지 나왔으나 실무에서 10g가 일반적이라 10g로 설치

2. 기다리시라니 기다린다.

3. 비밀번호 입력
전역 데이터베이스 이름 기억해두고
데이터베이스 암호는 특수문자를 포함하여 길게(?) 작성한다.
비밀번호를 잊었을 땐 윈도우의 Administrator 관리자의 비밀번호만 알고 있으면 쉽게 찾을 수 있다 하나
방법은 구글링-

4. 조건검사(?)

조건검사 라고는 나오는데 그냥 다음을 눌러준다.

아래와 같은 메시지로 당황할 수 있다

적당히 무시해주고 예(Y) 클릭


5. 설치 중 방화벽에서 차단 메시지가 나오면 계속 차단을 누르고 삽질을 해도 무방하다.

6. 설치
그냥 누르고 기다린다.
 

7. 설치중 화면
수분 소요

8. 설치중 화면
위의 7번의 화면에 겹쳐서 나오는 또 하나의 설치 화면

9. 설치중 화면
별 생각 없이 확인

10. 설치 완료
완료 후에는 종료 버튼을 눌러 수동으로 종료

11. 제어판-관리도구-서비스 항목 체크
OracleOraDb10g_home1TNSListener
OracleServiceORCL
두개의 서비스 항목이 상태가 시작됨으로 사용중이어야 정상 작동한다
 

- 오라클 서버접속 : sys 관리자 접속
cmd> sqlplus sys/"암호" as sysdba;
다음에 접속됨 이라고 메시지가 나온다

- 사용자 계정 추가 : sys 계정에서만 사용자 추가 가능
sql> create user 사용자명 identified by "암호";

- 사용자 접속
sql> conn 사용자명/"암호";
처음 접속시 데이터베이스에 접속할 수 있는 권한이 없어 오류가 나므로
다시 관리자로 접속하여 권한을 부여한다.
sql> conn sys/"암호" as sysdba;
sql> grant connect, resource to 사용자명;
connet : 데이터베이스 접속권한, resource : 테이블스페이스 사용권한
이제 다시 conn 사용자명/"암호"; 로 재 접속시 '연결되었습니다' 라고 메시지가 나온다

- 저장된 쿼리(*.sql) 실행
sql> @경로\파일명.sql

- 웹을 통한 오라클서버 접속 : 포트번호는 어떻게 설정되는건지 잘 모르겠음-.-a
http://자신의ip주소:5560/isqlplus

http://자신의ip주소:1158/em  관리자영역

- sqlplus를 통한 사용자 접속
cmd> sqlplus 사용자명/"암호";
sql> select * from insa;  insa.sql 내 테이블 화면 출력: 그지 같이 나옴

- sql developer 를 통한 접속
프로그램 실행 후 + 버튼을 클릭하여 접속설정 창이 뜨면
접속이름은 알아보기 좋은 것(?)으로 하고
sql plus에서 생성한 사용자 이름과 비밀번호를 입력하고
아래의 SID를 오라클 설치시 전역 데이터베이스 이름으로 한다.
테스트 후 성공시 접속 버튼을 누르고 저장, 비밀번호 저장은 꼭 체크할 것.
귀찮아지니까-

- 레코드 : 한줄

- 컬럼 : 항목, 아이템, 하나하나 모여서 자료가 된다.
  · 자료는 소문자로 작성해도 대문자로 저장되어 출력된다.

- 테이블 : 표형태의 데이터로 테이블 1개가 파일 1개라 보면 된다.
  · 데이터베이스내에 존재하는 테이블 목록 확인 : sql> select * from tab;

- 스키마 : 어떤 항목을 저장할지에 대한 구조....?
  · 테이블의 스키마(구조) 확인 : sql> desc insa;
  · 널? : 넣지 않아도 되는 항목
  · not null : 꼭 넣어야 하는 항목
  · number() : 괄호안의 숫자는 자리수로, 그 자리수 만큼 저장 가능함을 말함 38자리(???였나)까지 가능
  · vachar2 : 가변적인 문자(열), 최대값 4000자
  · date : 날짜 저장

- DQL : 데이터베이스 정보를 검색한다(select 문)

- SQL
  · 데이터 정의어 DDL : create, lock, alter
  · 데이터 조작어 DML : insert, delete, update, merge
  · 데이터 제어

- Startup : 종료된 계정 다시실행(sys, administrator....당장은 설명한 단계는 아님...)

- cmd 창으로 임시로 나가기
sql> host
cmd> exit  → 복귀

- 자료검색
select 필드명, 필드명, .... from 테이블명 where 조건

- 기술순서
from절 → where절 → group by절 → select절 → order by절

- 전체자료 출력
select * from insa;
전체자료를 출력할 경우에도 *를 사용하지 말고 컴럼(필드)명을 반드시 입력해야 한다.

- 특정 컬럼만 확인
이름, 전화번호, 부서
select name, tel, buseo from insa;

- 테이블작성에서의 필드 순서는 의미가 없으며 select 문에서의 필드 순서가 중요

- 이름, 주민번호, 출신도, 기본급, 수당 출력
select name,ssn,city,basicpay,sudang from insa;

- 계산된 결과 출력
select name, buseo, basicpay+sudang "급여" from insa;

- 산술연산자 : +, -, *, /

- 문자열 결합연산자 : ||
select name || '님' as 이름, city 출신도 from insa;
결합되는 문자(열)상수는 홑따옴표 안에 기술한다(쌍따옴표 아님)

- 필드명 변경
select name as "이름", ssn 주민번호, basicpay+sudang "급 여" from insa;
공백/특문 있으면 따옴표를 꼭 붙이고 as는 생략 가능하다.

- 조건: 기본급+수당이 200만원 이상인 사람 출력(이름, 급여)
select name 이름, basicpay+sudang 급여 from insa where (basicpay+sudang)>=2000000;

- dual 테이블
dual 테이블은 데이터 딕셔너리와 함께 오라클에 의해 자동으로 생성된 테이블로 sys계정에 존재하고
모든 사용자가 사용 가능한 테이블이다.

- 컴퓨터 시스템의 날짜 확인
select sysdate from insa;
insa 의 데이터가 60개라 60개의 날짜를 출력
select sysdate from dual;
한개만 출력

- 연산자
산술/비교 연산자는 뻔하니 생략하고
논리연산자에서 not 연산자는 시스템 성능을 저하 할 수 있으므로 사용을 자제한다.

* 문자 함수

- substr : 오라클에서 문자열의 위치는 1부터 시작하고 -1일 땐 뒤부터 시작

-이름, 주민번호의 8번째에서 1의 길이 만큼 문자열 추출
select name, substr(ssn, 8, 1) from insa;

- 이름, 주민번호, 출신도 출력(단, 여자만 출력)
select name, ssn, city from insa where substr(ssn,8,1)/2=0 or substr(ssn,8,1)=4;

- 78~82년생만 출력(이름, 주민번호, 부서)
select name, ssn, buseo from insa where substr(ssn,1,2)>=78 and substr(ssn,1,2)<=82;

- length : 문자열의 길이를 반환
select length('대한민국') from dual; → 4

- lengthb : 문자열의 byte수를 반환
select lengthb('대한민국') from dual; → 8

- instr : 문자의 위치를 반환
select instr('korea','e') from dual; → 4

- lpad : 문자열을 제외한 공간에 지정 문자를 왼쪽에 채운다.
-- 이름, 기본급, 그래프(그래프는 기본급 100000원당 * 하나 출력)
select name, basicpay, lpad('*', basicpay/100000, '*') 그래프 from insa 
*이라는 문자를 기본급을 10만원으로 나눈값 만큼의 byte수를 갖도록 하고 왼쪽에 *을 채운다.
select lpad('Korea', 12, '*') from dual;
Korea라는 문자를 12byte크기의 공간을 갖는 문자가 되도록 하고 왼쪽 나머지 공간에 *을 채운다.

- rpad : 문자열을 제외한 공간에 지정 문자를 우측에 채운다.
select rpad('KOR : ', 10, '*') from dual;
'KOR : ' 은 공백포함 6byte, 4byte공간 우측에 별4개 채움 → KOR : ****

- replace : 지정 문자를 다른문자로 치환
select replace('Seoul Korea', ' ', '') from dual;
 ▲ 공백을 없앰
select name, city, replace(buseo, '부', '팀') from insa;
 ▲ but, 앞에 부 라는 글자가 와도 바꿔버린다. 순서에 관계 없이..
 ▼ 아래와 같이 하는 것이 정답
select name, city, substr(buseo, 1, length(buseo)-1)||'팀' 부서 from insa;
select name, rtrim(buseo, '부') 부서 from insa;

select name, rtrim(buseo, '부')||'팀' 부서 from insa;

- rtrim : 문자열 중 오른쪽에서부터 지정 문자열에 포함된 글자중 하나라도 만나면 제거한다.
공백도 지정할 수 있고 오른쪽부터 훑어 가다 지정문자열에 포함된 글자가 아닌 다른 글자를 만나면
멈추고, 그러므로 중간에 포함된 문자도 제거할 수 없다.
select rtrim('ABAAAAABBBBBABCDAB', 'AB') from dual; → ABAAAAABBBBBABCD

- ltrim : 위와 같고 왼쪽부터..
select ltrim('ABAAAAABBBBBABCDAB', 'AB') from dual; → CDAB

- trim : 문자열의 앞, 뒤 또는 앞뒤에서 공백 문자를 제거 한다. 특정 문자제거도 가능하다
select trim('   ABC    ') from dual; → ABC
select trim('A' from name); 

- round : 반올림
select round(15.193, 1) round from dual; → 15.2
1은 소수점 1째자리, -1은 1의 자리 미입력시 default 0 입력

- trunc : 단위절삭
위와 같은 형식으로 사용
select round (12345, -3) round from dual; → 12000

- mod : 나머지값
mod(m, n) m을 n으로 나눈 나머지
SELECT MOD(11, 4) "Modulus" FROM DUAL; → 3

- to_char : 숫자나 날짜를 문자열로 변환
기존 데이터의 출력형식을 기술한 형식으로 변환

select name, to_char(ibsadate, 'YYYY"년" MM"월" DD"일"') 입사일 from insa;
▲ 출력 형식을 YYYY년 MM월 DD일 로 바꿔줌

select name, to_char(ibsadate, 'YYYY') || '년' from insa;
select name, to_char(ibsadate, 'YYYY"년"') from insa;
▲ YYYY로 출력 형식을 바꾸고 '년'이라는 문자를 연결한다 

- 국가에 따른 통화 기호 출력 : L

select name, to_char(basicpay, 'L99,999,999') from insa;
▲ 기본급을 세자리당 , 로 구분하고 앞에 \을 붙인다.

- 살아온 날수 구하기

select trunc(sysdate - to_date('1983/06/08', 'yyyy/mm/dd') )살아온날수 from dual;
▲ sysdate : 시스템에 저장된 현재 날짜 반환, to_date : 문자를 날짜로 변환
... 이라지만 솔직히 sysdate - to_date 구문이 이해가 가지 않는다-_-a
현재 날짜에서 해당 날짜를 빼라는 말인가?!

- 이름, 입사일, 근무일수

select name, ibsadate, trunc(sysdate - ibsadate) from insa;

select name, ibsadate, sysdate-ibsadate from insa;
▲ 그렇다면 이것도 현재날짜-입사날짜 인거네.. trunc는 절삭 자리수 미입력으로 0이니까
소수점 1째자리에서 반올림되어 1의 자리까지 출력될 것이고.. 그런가? 그러네..

- 이름, 입사일, 단 2000년에 입사한 사람만 출력

select name, ibsadate from insa where to_char(ibsadate, 'yyyy')='2000';

select name, ibsadate from insa where substr(ibsadate, 1,2)='00';
▲ where 조건절 : 입사날짜를 to_char로 yyyy형식으로 년도를 바꾸고 그것이 2000과 같은 조건의
데이터 출력, substr은 입사날짜의 기본값이 두자리이므로 1번째 위치에서 2만큼의 문자열이
00 일때의 조건

- 주민번호를 기준으로 나이를 출력. 단, 1년은 365일로 하고 1900년도에 태어났다는 가정하에

select name, ssn, trunc((sysdate - to_date('19'||substr(ssn, 1,6), 'yyyymmdd'))/365) 나이 from insa;
▲ 현재날짜-다음의날짜(주민번호의 1위치에서 6까지의 문자열앞에 19를 붙이고 그 형식은 yyyymmdd로 하고
그 날짜를 365일로 나눈 값) to_date는 문자를 날짜로 변환하므로 계산이 되는것 같다.

- 문제1. 컴퓨터 시스템의 날짜를 다음과 같은 형식으로 출력하기
1) 2011-11-07 월요일 03:10:10
2) 2011-11-07 15:10:10
3) 2011-11-07 오후 03:10:10

select to_char(sysdate, 'yyyy-mm-dd day hh:mi:ss') from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate, 'yyyy-mm-dd am hh:mi:ss') from dual;
▲ day: 요일, hh24: 24시간제로 표시, am:오전/오후 값 표시

- 문제2. 주민번호 8번째는 성별이다. mod를 이용하여 여자이면서 서울사람만 출력(이름, 주민번호, 출신도)

select name, ssn, city from insa where (MOD(substr(ssn, 8, 1), 2)=0) and city='서울';
▲ 주민번호의 8번째 위치에서 1까지니까 123456-8 성별을 나타내는 위치에 있다.
그 위치의 값을 2로 나눈 값이 0이고(2와 4로 시작하는 주민번호) 출신도가 서울인 조건의 값 출력

- 문제3. 기본급+수당 200만원 이상인 사람중 2000년 이후에 입사한 사람만 출력(이름, 입사일, 기본급+수당)
단, 급여는 3자리마다 컴마가 출력되고 앞에 원화기호를 표시한다.

select name, ibsadate, to_char(basicpay+sudang, 'L99,999,999') 수당 from insa where basicpay+sudang>=2000000 and to_char(ibsadate, 'yyyy')>='2000';
▲  to_char를 이용하여 급여의 값을 3자리마다 출력하는 형식과 원화기호를 표시하기 위한 L99,999,999 의
형식의 문자열로 변환을 해주고 200만원 이상의 급여의 조건으로 where절 다음에 기재해 주고
to_char를 이용하여 입사 날짜형식을 yyyy로 바꿔줌과 2000년 이상의 값이 되는지 조건을 기재
말로 설명이 어렵네;;

- 문제4. 서울사람이면서 김씨만 출력(이름, 출신도)

select name, city from insa where city='서울' and substr(name,1,1)='김';
▲ where조건절에 출신도가 서울이고 이름의 1번째 위치부터 1의 위치까지의 문자열이 김 인 사람의 조건

- 문제5. 출신도가 서울, 인천, 광주인 사람 출력(이름, 출신도)

select name, city from insa where city='서울' or city='인천' or city='광주';
▲ 문제4번과 유사하다

- 문제6. 다음을 출력한다.
이름, 기본급, 수당, 급여(기본급+수당), 세금(급여*0.03), 실수령액(급여-세금) 단, 세금은 일의 자리에서 절삭한다.

select name 이름, basicpay 기본급, sudang 수당, basicpay+sudang 급여, trunc((basicpay+sudang)*0.03, -1) 세금,
(basicpay+sudang)-trunc((basicpay+sudang)*0.03, -1) 실수령액 from insa;
▲ 세금의 절삭을 위해 trunc명령어를 사용하여 기본값으로 일의 자리에서 절삭되도록 -1의 조건을 달아주고
문제에서 제기한 식대로 select 문에 출력해준다.

- 문제7. 생년월일이 1980년 2월 1일 이후에 태어난 사람만 출력한다. 이름, 주민번호, 출신도(생년월일은 주민번호 기준)

select name 이름, ssn 주민번호, city 출신도 from insa where substr(ssn, 1,6)>800201 or (substr(ssn,1,2)>=0 and substr(ssn,1,2)<=11);
▲ 조건으로 substr로 1의 위치부터 6까지의 값이 800201 보다 큰 주민번호를 가지고 있거나 1~2위치가 0이고 1~2위치가 11보다작거나 같은 조건이라고는 하는데
위에 1900년도에 태어났다는 조건의 예제를 잘 활용해보면 좀 간단하다 싶은 답안이 있을 것 같기도...