코딩 21일 / 2022.07.19 / SQL Developer 툴, Oracle SQL문 기초

윗부분 나중에

---

오라클 수업 2주간 할 것들
1. Oracle SQL문 
2, SQL로 프로그램 만드는 PL/SQL 의 저장프로시저 라는걸배울것(실무에서 많이 씀) , Trigger 
3. 데이터 모델링
- 요구사항에 따라 논리적으로 모델링할것

- 직접 테이블 create 해서 안만든다

- 모델링 하는 툴을 가지고 요구사항 분석을 하고 거기에 따라 사용자가 어떤 요구하냐에 따라 요구사항 분석하고 그 결과로 모델링을함
- 논리적 모델링 -> 물리적 모델링 -> 배포 하면 테이블이 자동으로 만들어짐


SQL Developer 툴로 실습
- SQL 실행 위해선 콘솔창 작업 불편
- 그래픽 툴 중에 SQL Developer 이란 툴을 사용할 것
- 오라클은 이미 서버 구동되었기떄문에 클라이언트가 접근가능함
- sys, system, scott, hr 계정으로 접근가능 우리는 scott계정으로 실습할것

 

SQL Developer 다운
- SQL Developer 툴을 다운
- 최신버전은 oracle.com 으로 들어가야함, 또는 네이버 클라우드에 올린것 다운
- C드라이버에 넣고 압축풀면 sqldeveloper 폴더 나옴
- 그 안의 sqldeveloper 프로그램 바탕화면엔 바로가기 만듬 
- Sqldeveloper는 설치하는 프로그램이 아니라 압축만 풀면됨

- SQL Developer 임포트 :아니오
- 먼저 sql developer가 클라이언트용 프로그램이므로 서버로 접속하기위해, 오라클 데이터베이스의 커넥션 만들어야함

 

오라클 DB 커넥션 생성
1. 시스템 관리를 위해 system 계정에 대한 커넥션 생성해야함 (비번 oracle)
2. 실습을 위해 scott 계정에 대한 커넥션 생성해야함 (비번 tiger)

 

1. system 계정에 대한 커넥션 생성

- 좌측의 + 버튼 누르면 새로운 커넥션 생성가능

- 어떤 계정에 대한 커넥션인지 구분하기위해 계정명과 동일하게 만들자
- 접속이름도 system으로 하자
- 호스트 이름은 오라클 DB가 설치된 IP주소, 자기 컴에 오라클 있으면 localhost 로 지정
- SID : 전역 데이터베이스 명 , 여기선 xe
- 이후 접속 테스트 테스트 버튼 눌러서하자
- 성공적이면 좌측하단 성공 이라고 뜬다
- 그다음 접속 누르면 좌측에 system 계정에 대한 커넥션이 생성되어있음

2. scott 계정에 대한 커넥션 생성
- 두번째로는 scott계정에대한 커넥션을 만들자

- 같은 방법으로 scott계정 connection 만들기

- 커넥션 생성시 그 계정 소유의 테이블등도 볼수있다 ex) 우리가 만들었던 Board 테이블

- 해당 계정에 대한 커넥션 생성시 이렇게 테이블 정보 볼수있다

- 오라클은 계정단위로 모든것들이 이루어짐

- 만약 scott계정 설정 수정하고싶으면, 해당 커넥션에 오른마우스 -> 속성 메뉴

 

오라클의 DBA 계정
- sys 와 system계정
- sys계정은 DB 를 설치하거나 복구할때 주로 사용
- 나머지 관리적인것들은 다 system으로 처리
오라클의 실습계정

- scott 와 hr 계정
- scott계정은 이미 활성화되었다, 현재 이 계정으로 접속가능

hr계정 활성화

c:\> sqlplus  system/oracle

SQL> alter user hr account unlock;
SQL> alter user hr identified by 1234;
SQL> conn  hr/1234

- hr 계정도 락이 걸려있어서 언락을 해야함, scott 계정과는 다른 방법으로 언락
- 명령 프롬프트든 SQL Developer든 DBA계정인 system계정들어가서 작업을 해야함

 

SQL Developer에서 생성한 Connection 으로 연결하기 (.sql 파일 생성)

- SQL 워크시트 누르면 어떤 커넥션으로 연결할건지 물어봄 -> scott

- 여기다 -- 2022.07.19(화) 입력해보자, 그리고 폰트를 조절 하자

+ SQL Developer에서 글자 크기 설정 방법
- 도구 - 환경설정 - 코드 편집기 - 글꼴

Connection 연결한 sql 파일 저장하는 방법

- 디스켓모양 클릭
- 위치설정은 C드라이브 하위에 폴더 ;OraclePractice' 를 만들어서 저장했다
- C:\OraclePractice 안에 2022_07_19_01.sql을 만듬!

scott 계정에서 제공되는 테이블
- DEPT, EMP, SALGRADE, BONUS
- DEPT 테이블은 3개의 컬럼, 4개의 로우 있음

DEPT 3개의 컬럼
1. DEPTNO : 부서번호
2. DNAME : 부서이름 no 10번은 'Accounting', 20번은 'Research' 등
3. LOC : 지역

 

오라클 기본 명령어 : 테이블 목록 확인 명령어
- scott 계정으로 커넥션 연결한 2022_07_19_01.sql 파일에서 테이블 목록을 확인해보자

-- 테이블 목록
select * from tab;

 

- scott계정 소유의 테이블 목록을 보여달라는 명령

 

+ 공개 동의어

- 여기서 tab 이란 건 sys.tab 인데 줄여서 쓰는것
- sys는 DB 계정명이고 tab은 공개 동의어 이름이다, 공용 동의어 라고도 함
- 원래소유자.tab; 으로 쓰는걸 짧게 tab으로만 쓰는것임
- 원래 소유자는 sys계정이고 뒤에 나와있는 tab은 sys계정 소유의 테이블 이름인데, 공개되었기때문에 공개 동의어

 

SQL Developer 에서 SQL 명령어 실행시키는 방법

- 실행하는법은 그 줄에 커서 올려놓고 위쪽 초록색 화살표버튼
- 단축키는 해당 줄에 커서 위치시키고 Ctrl + Enter

- 그래픽 화면이 작업하기 수월하지만 기본적인 이런 SQL문은 알고있어야함

+ mysql 에선 show tables; 사용

scott 기본 제공 테이블 

- DEPT, EMP, SALGRADE, BONUS
- DEPT : 부서테이블 , 3개 컬럼과 4개 데이터(로우)로 구성
- EMP : 사원테이블, 8개의 컬럼과 14개의 데이터(로우) 로 구성
- 당분간 이 두개 테이블로 실습할것이라서 이 두 개 테이블에 대해 설명할것

EMP 테이블 구조
EMPNO : 사원번호
ENAME : 사원이름
JOB : 담당 업무,직책
MGR : 자기 직속상관의 사원번호
HIREDATE : 입사일
SAL : 급여
COMM : 커미션
DEPTNO : 부서번호

 

오라클 기본 명령어 : 테이블 구조 (스키마) 확인 명령어

- 테이블 구조를 볼때는 desc 명령 사용

-- DEPT 테이블 구조
describe dept;

 

- 또는

desc dept;

 

- describe 명령 사용시 이렇게 테이블 dept 의 구조가 나타난다, 3개의 컬럼이 있다
- VARCHAR2(14)는 14바이트까지, 영문 14자까지 저장가능
- NUMBER(2) 는 숫자 두자리까지 가능
- DEPTNO에 지금은 NOT NULL만 표시되어있지만, 실제론 기본키로 되어있다
- 즉, 이 부서를 식별하는 번호값이 DEPTNO이다

오라클 기본 명령어 : 테이블안의 데이터 검색 명령어

-- DEPT 데이터 검색
select * from DEPT;
SELECT * FROM DEPT;

+ SQL문은 대소문자 구분없다
- 안에 들은 영문 데이터는 대소문자를 구분함, 이거뺴곤 다 대소문자 구분안한다
- 이 DEPTNO는 Primary 키라는 기본키 제약조건이 설정되어있어서 중복되지 않는다

---

- 테이블 DEPT 의 구조와 데이터를 확인했다

- 이젠 테이블 EMP 의 구조와 데이터 확인해보기

테이블 EMP 구조 (스키마) 확인

dsec emp;

<제약 조건>

- NOT NULL : 널값 허용불가

<자료형>
- NUMBER(4) : 네자리 숫자 까지 가능
- DATE : 년/월/일 정보만 저장되어있음
- NUMBER(7,2) : 숫자값이 들어있는 자료형인데, 7은 전체 7자리까지 할당받게되고 뒤에 2는 소숫점자리 두자리까지 저장하겠다 라는 것
-> 결과적으로 정수값은 5자리, 소숫점아래는 2자리 까지 저장하겠다는 의미
<컬럼>
- COMM 은 영업사원들에만 해당, 커미션 관련
 - 부서 번호는 10,20,30,40 있지만, 각 사원은 10번, 20번, 30번 부서에 소속되어있음

테이블 EMP 데이터 검색

-- EMP 데이터 검색
select * from emp;

컬럼 설명

- EMPNO : Primary Key, 사원번호 4자리
- ENAME : 사원 이름
- JOB : 직무 명
- MGR :  직속상관의 사원번호 4자리가 저장되어있음
ex) SMITH 사원의 직속상사는 사원번호가 7902 -> 7902 사원은 FORD 이다
- KING이란 사원은 PRESIDENT (사장) 이라서 자기 직속상관이 null이다
- HIRDDATE는 DATE 타입이므로 년/월/일 로 저장되어있음
- SAL은 달 급여, 사장이 가장 높은 급여를 받고 있다
- COMM : 영업활동을 하고 돌려받는 돈 JOB이 SALESMAN으로 된 4명만 커미션비를 받고있음 
- DEPTNO : 10,20,30번 부서에 각각 소속되어있다, 40번부서는 존재하지만 아무도 소속되어있지 않음
- DEPTNO는 Foreign Key 설정되어있다
+ 부모테이블의 부모키 값만 참조할수있는게 Foreign key

- 이 두 테이블로 DEPT, EMP 로 실습할거다

 

SQL Developer 에서 그래픽으로 테이블 구조, 데이터, 제약조건 확인

- 지금까지 처럼 select, desc 명령어로 테이블을 확인하는 대신 그래픽으로 테이블을 확인하는 방법

- 이거 누르면 구조 데이터 제약조건 등 알수있다

- 중복되지 않는 값이며, 부서 식별위한 고유한 번호값 DEPTNO

- EMP 테이블의 제약조건도 보자

- 명령어로 실행하거나, 위의 사진처럼 그래픽 프로그램으로 해당 테이블 선택시 컬럼, 자료형, 데이터, 제약조건 확인가능

SQL Developer 주석

- Ctrl + / 하거나 -- 로 사용

오라클 데이터 타입
1. 숫자 데이터

2. 문자 데이터

3. 날짜 데이터
- 지원자료형이나 크기 등 알아보자

1. 숫자 데이터

- number란 타입은 괄호열고 정수가 들어간다

1) numer(n)
ex) numer(2)이면 정수 2자리까지 저장가능하단 의미 , 최대 99, 정수만 가능
2) number(n1, n2)

- n1은 전체 자리수, n2는 소숫점에 할당된 자리수
ex) number(7,2) 에서첫자리 7은 전체 자리수, 두번째자리 2는 전체 자리수 중에서 소숫점 아래 자리

2. 문자 데이터
- 크게 3가지가 있다
1) char() : 고정길이 문자, 최대 2000바이트까지 저장가능

2) varchar2(), 가변길이 문자

- varchar2는 4000바이트까지 저장할수있음 영문기준 4000자

3) long() : 4000바이트 넘어설땐 long, 2GB까지 저장가능

- 단 long형으로 설정된 컬럼은 검색기능 지원안함

 

고정길이 문자 vs 가변길이 문자

- char에는 값의 길이값이 정해져있는 데이터만 정해두므로 안쓰면 낭비

- varchar2는 가변길이 문자타입이라서 20바이트 지정해도 데이터 양이 5바이트면 5바이트만 사용
- 지정된크기만큼 저장할수있지만 데이터 크기만큼만 씀. 공간 낭비 막아줌 주로 문자 저장할떈 이거 쓴다

 

ex) char(20)

- char(20) 은 1개 컬럼에 20 바이트 할당을 딱 해둠,

- 만약 seoul 을 써서 5바이트를 쓰면 15바이트는 남았지만 남아있다고 해서 남은공간 쓰진 못함, 낭비가 됨.

- 그래서 값의 길이값이 정해져있는 우편번호,카드번호 이런경우에 char 씀
ex) varchar2(20)
- 가변길이 varchar2(20) 인 address컬럼에 저장된 값이 seoul이면 5바이트만 사용되고 나머지 15바이트는 낭비되지 않음
- 주로 문자 저장할땐 대부분 이거 씀

 

long 형 데이터 장단점
- 4000바이트 넘어서는 데이터는 long형 자료형 사용

- long형은 1개의 데이터당 2G 까지 지원 , 엄청나게 큰 데이터
- 단, long형으로 설정된 컬럼은 검색기능 지원안함
ex) char, varchar 사용시 게시판에서 제목이나 작성자명, 내용을 기준으로 검색가능, 근데 만약 long형으로 설정되어있으면 검색기능 지원하지 않음

3. 날짜 데이터

1) date : 년/월/일 정보 저장 ex) 80/12/21

2) timestamp : 년/월/일 시:분:초 정보 저장

- 좀 더 자세한 날짜 정보는 timestamp 형이다

오라클의 데이터 타입 정리
1. 숫자 데이터
- number(n) : number(2) 는 정수 2자리까지 저장
- number(n1, n2) : n1 - 전체 자리수, n2 - 소숫점에 할당된 자리수
2. 문자 데이터
- char() : 고정 길이 문자형, 최대 2000 byte 까지 저장 가능
- varchar2() : 가변 길이 문자형, 최대 4000 byte 까지 저장 가능
- long : 2GB 까지 저장 가능함, long형으로 설정된 컬럼은 검색 기능을 지원하지 않는다.
3. 날짜 데이터
- date : 년/월/일 정보 저장
- timestamp : 년/월/일 시:분:초 정보 저장

 

- 이제 SQL문 살펴보자

---

SQL문

- DDL , DML, DQL, DCL, TCL 등
- DCL : 권한부여, 권한 주는 Grant, 취소의 Revoke
- TCL : 트랜젝션 처리어
- DQL = SELECT SQL문

 

SELECT SQL 문 (DQL)

- SQL문 중에서 가장 복잡하고 다양하게 활용됨
- 전체 SQL문 중 50% 이상

SELECT 컬럼명들 FROM 테이블

- 여태껏 SELECT * FROM 테이블 에서 * 자리에 검색할 컬럼명을 이 자리에 쓰는것
ex)

select enum from emp;

SELECT SQL문 사용 방법
1.

select * from dept;

 

- * : ALL COLUMNS 의미함

2.

select deptno from dept;

 

- deptno값만 나옴

3.

select loc, deptno, dname from dept;

 

- 검색시 컬럼순서 바꿀수있음

 

- 테이블 emp 도 똑같이 사용해보자

select * from emp;

select empno, ename, sal from emp;

 

- 특정컬럼만 선택적으로 검색하고 있다

SELECT SQL문 확장

- 컬럼들 사이 산술적인 연산을 수행하자
- number 타입인 컬럼만 연산 가능
- '+', '-', '*', '/' 를 컬럼사이에 사용가능

SQL문에 산술 연산자 사용 ( +, -, *, /)

select sal + comm from emp;
select sal - 100 from emp;
select sal * 12 from emp;
select sal / 2 from emp;

 

- 숫자데이터가 저장된 컬럼만 산술연산 가능하다

- 사원들이 매달 받는 돈이 SAL 컬럼에 있다

---

NULL 값이 있을때 연산하는 방법 (nvl() 함수)

 

산술 연산자를 사용해서 EMP 테이블에서 사원들 연봉 계산
- 사원들 연봉을 계산해보자
- SAL*12하고 +COMM(커미션) 해야함 (커미션 받는 사원들은 COMM 추가 해야함)

-- Q. 사원테이블(EMP)에 소속된 사원들의 연봉을 구해보자?
--  연봉 = 급여(SAL) * 12 + 커미션(COMM)
select ename, job, sal, sal * 12, sal * 12 + comm from emp;

 

문제 상황

- 이때 COMM받는 사원은 4명뿐 이라는게 문제이다
- JOB이 SALESMAN 으로 되어있는 4명만 커미션받고 나머지 사원들은 커미션이 없는 NULL 값
- NULL값은 정해지지 않는값을 의미하고 비어있다, 문제는 NULL값인 데이터는 산술적인 연산 + 를 지원안함
- 커미션을 받는 4명만 연봉이 구해지고, 나머지는 연봉이 NULL값이 나옴

- COMM이 NULL이면 산술적인 연산이 되지 않는다

NULL값

- 정해지지않은값, 산술연산 불가능

ex) PRESIDENT 사장의 직속상사 MGR이 NULL
ex) SALESMAN이 아닌 직원의 COMM이 NULL

NULL 값을 산술연산 가능하게 만드는 방법
- NULL을 0으로 치환시켜주는 함수 NVL (nvl) 란 함수 사용해서 null 값을 + 연산에 영향을 끼치지않는 값인 0 으로 설정

 

NVL(컬럼, 변환될 값)

- NULL 값을 다른 값으로 변환해주는 오라클 지원 함수
ex) NVL(COMM, 0) : COMM 컬럼의 NULL값을 0으로 치환 하라는 의미
- NULL 값인 경우만 치환되고 NULL 값이 아닌 경우는 그대로임
+ 오라클 지원함수 NVL 은 오라클 지원함수이므로 대소문자 구분없음

 

문제 해결

select ename, job, sal, sal * 12, sal * 12 + nvl(comm,0) from emp;

- 라고 하면 제대로 구해짐, null값에 해당하는건 0으로 치환 null아닌건 그대로

---

별칭만들기

- 수식 전체가 컬럼명이 되므로 보기 불편
- 해당 컬럼에 별칭을 부여할수있다

별칭 부여

- 해당 컬럼 뒤에 as "별칭명" 붙이기
- 별칭을 부여할때 " " 쌍따옴표을 쓴다

- 그럼 이 별칭명이 컬럼명으로 나타남

- as 와 " " 생략하고 실행해도 된다 

-- 별칭 부여 : as "별칭명"
select ename, sal * 12 + nvl(comm,0) as "Annsal" from emp; 
select ename, sal * 12 + nvl(comm,0) "Annsal" from emp; -- as 생략 가능
select ename, sal * 12 + nvl(comm,0) Annsal from emp; -- as 와 쌍따옴표("") 생략 가능

- 위 3가지 모두 가능

- 이제 한글별칭명 으로 만들어보자

-- 한글 별칭 부여 : as "별칭명"
select ename, sal * 12 + nvl(comm,0) as "연봉" from emp; 
select ename, sal * 12 + nvl(comm,0) "연봉" from emp; -- as 생략 가능
select ename, sal * 12 + nvl(comm,0) 연봉 from emp; -- as 와 쌍따옴표("") 생략 가능
-- select ename, sal * 12 + nvl(comm,0) 연 봉 from emp; -- 별칭명에 띄어쓰기 있을땐 쌍따옴표 생략 불가

 

- 별칭명에 띄어쓰기가 있는 경우는 " " 를 생략할 수 없다

select ename, sal * 12 + nvl(comm,0) "연 봉" from emp; -- 가능
-- select ename, sal * 12 + nvl(comm,0) 연 봉 from emp; -- 별칭명에 띄어쓰기 있을 경우에는 쌍따옴표 생략 불가

---

Concatenation 연산자 
- 오라클에서 제공됨
- "||" 연산자임
- 컬럼과 문자열을 연결할때 사용함

-- Concatenation 연산자 : ||
-- : 컬럼과 문자열을 연결할 때 사용함.
select ename || ' is a ' || job from emp;

 

- || 를 콘케이트네이션 연산자 라고함

---

DISTINCT 키워드
- 중복행을 제거하고 한번만 출력해주는 역할
- 아래의 문제상황에서 사용

 

문제 상황

select deptno from emp; -- 중복행 제거 안됨

- 이거 해결법은 컬럼 앞에 distinct 키워드 사용

 

문제 해결

select distinct deptno from emp; -- 중복행 제거됨, 3개의 부서번호 출력 : 10, 20, 30

문제 1

-- Q. EMP 테이블에서 각 사원들의 job을 1번만 출력하는 SQL문을 작성하세요.
select distinct job from emp;

문제 2

-- Q. EMP 테이블에서 중복을 제거한 job의 갯수를 구하는 SQL문을 작성하세요.
select count(distinct job) from emp;

 

- COUNT 함수 쓰면 JOB의 개수를 구할수있음

- 또한 JOB 이 중복되지 않도록 컬럼 JOB 앞에 DISTINCT 를 사용

---

COUNT 함수

- 데이터의 개수를 구해주는 그룹함수

- dept 테이블의 데이터 개수를 구하고 있다

-- count(컬럼명) : 데이터 갯수를 구해주는 역할
select count(*) from dept; -- 4
select count(*) from emp; -- 14
select count(job) from emp; --14

select count(distinct job) from emp; -- 5

 

- * 는 전체 컬럼 기준으로 데이터 몇 개인지, 즉 총 데이터 수