1017(22일차) - 오라클,sql기초

다운로드 받을 파일 2개
1. 오라클 https://www.oracle.com/kr/database/technologies/xe-downloads.html
2. SQLDeveloper (Windows 32-bit/64-bit 두번째)  oracle.com 회원가입 필수
  https://www.oracle.com/database/sqldeveloper/technologies/download/

트래픽분산을 위해 짝수줄 학생은 강사pc dev에서 download 해주세요 🙂

 

ORACLE

-오라클 설치하기 (공용 비밀번호: oracle) 기본포트 1521

같이 설치한 sqldeveloper도 자바프로그램이라 java_home환경변수가 필요하고, 공유함

프로그램 실행 후 해야 할것, 환경설정

도구-환경설정-인코딩 utf-8(utf8)로 지정!

sqldeveloper와 오라클 연동 시 접속정보 등록 필요

 

 

 

SQLPLUS 설치 후 CMD에서 확인해봄

 

 

SQL DEVELOPER 설치 후 - 

UTF-8 혹은 UTF8로 변경

 

좌측상단 녹색 십자 버튼누르고 SYSTEM 계정 생성

이어서 SYS 계정도 생성

현재 코드 상태에서는 테스트 할 경우 실패함 이름,비번 모두 sh

권한을 부여 한 후 다시 테스트 하면 SH 상태가 성공으로 바뀐다

프로젝트 디렉토리

 

오라클과 sqldeveloper 설치 후 기본적인 셋팅 진행

-utf8로 변경

-sql 파일 생성 후 저장위치는 Workspaces 하위에 oracle_workspace 폴더 생성 후 해당폴더에 저장-진행

-글씨 크기나 색상 기타등등 설정은 도구-환경설정에서 설정한다.

 

system 사용자 상태에서

oracle_system.sql 생성 후 다음과 같은 코드를 작성해서 일반 사용자 sh 계정을 생성한다.

계정마다 중앙 뷰 테두리 색상을 지정할 수 있어서 관리자급인 sys와 system은 빨강색으로,

스터디용 계정인 sh는 초록색으로 지정하였다.

 

 

 

oracle_system.sql (일반계정인 sh를 생성하고 권한부여 하는 쿼리문)

--한 줄 주석

/*
여러 줄 주석
어쩌구
저쩌구..

*/
show user;

--sh계정 생성(관리자만 생성 가능)
--sql문은 대,소문자를 구별하지 않는다.(데이터 부분만 제외)
--비밀번호는 계정생성 시 대소문자 구분하니 유의
--개행 구별하지않음(한줄로도 가능)

alter session set "_oracle_script" = true; --c## 접두사 우회설정.
create user sh
identified by sh --"sh" 이렇게 쌍따옴표로 감싸도 됨
default tablespace users;

--ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.
--oracle12c 이후에는 일반 계정명은 c##접두사를 추가해야함.

--ORA-01045: 사용자 SH는 CREATE SESSION 권한을 가지고있지 않음; 로그온이 거절되었습니다
grant create session to sh; --session 권한을 부여
grant create table to sh; --테이블 생성 권한 부여

grant connect, resource to sh; 
--커넥트롤,리소스롤(롤:권한묶음) 이 한줄이면 위에 권한 부여 두줄 한번에 가능
--커넥트,리소스 create table외에 여러 권한 그룹임.

alter user sh quota unlimited on users; --테이블 스페이스(실제 데이터가 보관되는 장소..) 사용용량 무제한으로 수정.
--quota:사용용량 unlimited:제한없이.

 

oracle_sh.sql (sh에 SH.sql 활용한 수업내용,예제)

sh 계정 생성후 테스트까지 정상적으로 출력된다면

sh 계정으로 SH.SQL로 스터디용 데이터를 sh 계정의 테이블에 적용한다.

 

---

SQL

가장 기본적인건 관계형 데이터 베이스

DDL ,DML, DCL의 특징과 명령어 암기할 것

DDL	DML	DCL
데이터 정의어  객체에 대해 생성,수정 삭제	데이터 조작어 테이블의 행들을 조작하는 언어	데이터 제어어 권한 설정
CREATE ALTER DROP TRUNCATE	insert  update delete	GRANT : 권한 부여 REVOKE :권한 회수,삭제
객체: 사용자, 테이블 등..	DQL(QUERY) -DML하위분류 -select SELECT 5 FROM 1 WHERE 2  GROUP BY 3  HAVING 4  ORDER BY 6	priviliege TCL(Transaction) -DCL 하위분류 -commit -rollback

오늘 나간 진도 다시 한 번 훝어보기

 

 

oracle_system.sql (수업내용)

--sh계정으로 접속해보기~
show user;

--sh계정이 가진 table 조회
select * from tab; --tab:테이블

select * from department; --department 테이블의 내용
select * from employee;  --사원 테이블
select * from job;  -- 직급 테이블
select * from location; -- 지역 테이블
select * from nation; -- 국가 테이블
select * from sal_grade; -- 급여 등급 별 범위 테이블

desc employee; --테이블 상세보기.
--NULLABLE(NULL여부) : NOT NULL(필수)->아이디,이름 등등...... 그 외에 NULL이여도 상관없는 속성(주소,취미)

--table 객체 구분
--table(entity, relation) date를 보관하는 주체
--column(field,attribute) 테이블의 구조, 자료형별 구분
--row(record,tuple)data를 구분하는 단위 (행단위)
--domain 하나의 속성(컬럼)에서 가질 수 있는 값의 범위(남/여 , 0~100....)



/*
================================================
DATA TYPE (컬럼에 적용)
================================================
1.문자형
2.숫자형
3.날짜형
*/

--1.문자형
-- char : 고정형 (최대 2000byte)
-- char(10) 고정형문자타입  최대크기 (10byte)
--    'korea' 입력시..영문자는 한 글자에 1byte씩 처리됨. 실제로 5byte이지만..저장된 데이터 크기는 10byte이다..
--    '오라클' 입력시..한글은 한 글자(3byte-xe ,2byte - ee) 실제로 9byte이지만, 저장된 데이터는 10byte이다..
--    '대한민국' 입력시..한글 *4라 실제는 12byte라서 저장불가.
--varchar2 : 가변형 (최대 4000byte)
--varchar2(10) 가변형 문자타입 최대크기 (10byte)
--    'korea' 입력시..영문자는 한 글자에 1byte씩 처리됨. 실제로 5byte이지만..저장된 데이터 크기는 5byte이다..
--    '오라클' 입력시..한글은 한 글자(3byte-xe ,2byte - ee) 실제로 9byte이지만, 저장된 데이터는 9byte이다..
--    '대한민국' 입력시..한글 *4라 실제는 12byte라서 저장불가.

--long : 최대 2GB 가변형 문자타입(사장됨)
--clob : 최대 4GB 가변형 문자타입 Character Large Object

--자바랑 다르게 대소문자 구분을 못해서 카멜케이싱을 못함.....대신 언더스코어로 구분해줘야.(Snake Casing)
create table tb_datatype_char(
    a char(10),
    b varchar2(10)
);

desc tb_datatype_char;
--데이터는 행 단위로 추가됨.
insert into tb_datatype_char values ('korea','korea'); 
insert into tb_datatype_char values ('오라클','오라클');
--insert into tb_datatype_char values ('대한민국','abc');--SQL 오류: ORA-12899: "SH"."TB_DATATYPE_CHAR"."A" 열에 대한 값이 너무 큼(실제: 12, 최대값: 10)
--행단위로 데이터 저장 시 컬럼값 하나라도 문제면 안들어감. 대한민국만 문제인데 abc도 안들어감. 올바르지 않은 데이터는 아예 insert도 못함

select * from tb_datatype_char; --여기까지 저장된 데이터 확인 * = all

select a, b,lengthb(a),lengthb(b) 
                    from tb_datatype_char;

--dml 처리 (insert,update,delete) 메모리 상에서만 작업이 진행됨. 실제 db반영은 commit 명령어를 전송해야 한다.
commit; --커밋 완료. 뜸
--rollback; --실행취소 (commit 이후에는 사용 x)

--------------------------------------------------
--숫자형
--------------------------------------------------
--number 타입으로 정수/실수 통합해서 관리
--number(3,2)와 같이 전체자리수, 소수점 이하 자리수 지정가능

--number에 1234.567을 저장하면, 1234.567이 그대로 저장됨..
--number에(7,0) 1234.567을 저장하면, 1235로 저장됨.. = number(7)
--number에(7,1) 1234.567을 저장하면, 1234.6로 저장됨.. 
--number에(7,-2) 1234.567을 저장하면, 1200로 저장됨..(1,2자리수인 3,4를 날림)
--기본반올림이라 다른 처리를 원한다면 연산을 해야함.

create table tb_datatype_number(
    a number,
    b number(7),
    c number(7,1),
    d number(7,-2)
);

insert into tb_datatype_number values(1234.567, 1234.567, 1234.567,1234.567);
insert into tb_datatype_number values(123456789, 123456789, 1234.567,1234.567);
--SQL 오류: ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다.

select * from tb_datatype_number;


-----------------------------------------------------
--날짜형
-----------------------------------------------------
--date 년/월/일 시/분/초 관리
--timestamp 년/월/일 시/분/초하위 상세시각까지 관리
--timestamp with time zone 지역대 추가

--현재시각 확인
--sysdate : 현재 시스템(os) 시각
--systimestamp : 현재 시스템(os)시각 timestamp with time zone
select sysdate, systimestamp from dual; --dual:1행짜리 가상테이블

--RRRR/MM/DD HH24::MI::SS

--날짜형 연산
--1.날짜 +- 숫자 (1일)  = 날짜 타입
--2.날짜 - 날짜 = 숫자(두 날짜 차이(일))

select 
    sysdate + 1 "내일",
    sysdate "오늘",
    sysdate - 1 "어제",
    to_date('231225','yymmdd') -sysdate --68.5일
from dual;


create table tb_datatype_date(
    a date,
    b timestamp,
    c timestamp with time zone --9시간 빠른 
);

insert into tb_datatype_date values (sysdate, systimestamp, systimestamp);
select * from tb_datatype_date;



--============================================
--DQL
--============================================
--DML 하위 분류 
--(Data Query Language, 데이터 질의 언어)
--테이블의 데이터를 검색/조회하는 명령어
--조회결과를 ResultSet(결과의 집합)이라고 한다.
--조회결과는 0행 이상이고, 시각화할 컬럼을 선택할 수 있다.


/*
    DQL 구조★★★★★★★아주 중요
    
    select  컬럼명   5     -필수
    from 테이블     1      -필수         
    where 조건절(필터링)    2
    group by 그룹핑       3
    having 조건절(그룹핑에 대한 필터링)    4
    order by 정렬기준컬럼명  6


*/

select * from employee;

--job 테이블에서 job_name정보만 출력
select JOB_NAME from job;

--department 테이블에서 컬럼 전체를 출력
select * from department;

--employee 테이블에서 이름,이메일,전화번호,고용일만 출력
select EMP_NAME,EMAIL,PHONE,HIRE_DATE from employee; 

--employee 테이블에서 이름, 급여, 고용일 출력(이름 가나다순)
select EMP_NAME,SALARY,HIRE_DATE from employee order by EMP_NAME; //ASC:오름차순(생략),DESC:내림차순

--employee 테이블에서 급여가 250만원 이상인 사원 정보 출력 = != > < >= <=
select * from employee where SALARY>=2500000;

--employee 테이블에서 급여가 350만원 이상이면서, 직급코드가 j3인 사원의 이름과 전화번호를 출력. and or
select EMP_NAME,PHONE,SALARY,JOB_CODE from employee where  SALARY>=3500000 and JOB_CODE='J3'; --데이터 부분의 대소문자는 정확히해야할 것


-------------------------------------------
--SELECT
-------------------------------------------
--결과 집합에 포함시킬 컬럼명을 지정.
--존재하는 컬럼, 연산결과 등의 가상컬럼도 지정가능.(salary*12)

--nvl함수 : null일 경우 기본값 지정.(위에 bonus 없는사람들도 값을 나오게 하기위한..)
select 
    emp_name,salary,salary*12,
    bonus,nvl(bonus,0),
    salary+(salary * nvl(bonus,0)) "실급여"--연산에 null이 포함되면 결과도 무조건 null(보너스 null인 사람들은 실급여도 null)
from
    employee;


--ex)사원테이블에서 이름, 연봉(보너스포함), 매달 실수령액(세금-3%)  조회..
select 
    emp_name,
    (salary+(salary * nvl(bonus,0)))*12"연봉", (salary+(salary * nvl(bonus,0)))*0.97 "매달 실수령액" 
from employee;

--ex)사원테이블에서 사원명,입사일,근무일수 조회(소수점 이하 날리는 floor() 사용 )
select
    emp_name,hire_date, floor(sysdate-hire_date) as "근무 일수"
from employee;

--별칭 as "별칭"
--as와 "" 모두 생략 가능
--단, 숫자로 시작하거나 공백등 특수문자가 포함된다면 ""생략불가.

--distinct
--컬럼에 중복된 중복을 제거해서 유일값 목록으로 결과 집합 반환
--select에 딱 한 번만 사용 가능.
--여러컬럼을 나열하면, 여러 컬럼을 묶어서 유일한 값 반환
select distinct
    job_code
from
    employee
order by
    job_code;

select distinct
    dept_code,job_code
from
    employee;

--문자열 연결 연산자 ||
--sql에서 더하기 연산은 숫자 사이에만 가능하다.

select
    emp_name,
    salary,
    '원' "단위",
    salary||'원'
from 
    employee;

-- 나누기연산 %없고 mod()함수있음..

------------------------------------------
--where
------------------------------------------
--from 테이블 다음에 실행
--지정한 테이블에서 결과집합에 포함시킬 대상행을 판별하는 구문
--조건절 행단위로 수행, 결과값 true인 경우만 결과집합에 포함
select
 *
from
    employee
where
    dept_code='D5'; --동등비교 연산자

--비교연산자
--1.동등 = , !=,<>,^=
--2.비교 > >= < <=
--3.범위 between 시작값 and 종료값
--4.문자 패턴비교 like
--5.null비교 is null(!=null 작동하지않음)
--6.값 목록 비교 in(값1,값2.....)
--7.논리비교 and or not( &&, || 사용불가)

--ex)부서코드가 D6인 사원 조회
select * from employee where dept_code='D6';

--ex)부서코드가 D6가 아닌 사원 조회
select * from employee where dept_code != 'D6';
select * from employee where dept_code<> 'D6';
select * from employee where dept_code^='D6';

--ex)부서코드가 D5이거나 D6이고 급여를 300만원 보다 많이 받는 사원 조회
select
    *
from
    employee
where
    (dept_code='D5' or dept_code='D6') and salary>3000000;

--ex)직급코드가 J1이 아닌 사원들의 급여 등급을 중복없이 출력
select
        sal_level
from
    employee
where
    job_code != 'J1';

--ex)20년 이상 근속자의 이름,급여,보너스율(없는경우 0)을 출력
select
    emp_name,salary,nvl(bonus,0)
from
    employee
where
    quit_yn!='Y' and
     floor(sysdate-hire_date)/365 >=20;






--범위 연산 between and
--숫자/날짜
--급여가 350만원 이상 600만원 이하인 사원 조회

select
    *    
from
    employee
where
   salary between 3500000 and 6000000; --시작값과 끝값 포함(=salary >=3500000 and salary <=6000000;)
   --not 위치는 자유로운 편
   --salary not between 3500000 and 6000000; --시작값과 끝값 포함
   --not salary not between 3500000 and 6000000; --시작값과 끝값 포함 (=salary < 3500000 or salary >6000000;)
   
   
   
--날짜 : 90년대 입사자 조회 (90.01.01~99.12.31)
select
    *
from
    employee
where
    hire_date between to_date('1990/01/01','yyyy/mm/dd') and to_date('1999/12/31','yyyy/mm/dd'); --시분초가있으면 값이 안나올 수있음
   
   
   
    
--문자열 패턴비교 like
--와일드카드(대체문자) 사용
-- %: 0개 이상의 문자를 의미
-- _: 1개문자를 의미

--전씨 성을 가진 사원조회
select
    *
from
    employee
where
   -- emp_name like '전%';
    emp_name like '전__';
    
    
--이름에 옹이 들어가는 사원 조회
select
*
from
employee
where
    emp_name like'%옹%';
    

--이메일 조회 언더스코어 앞 글자가 3글자인 사원조회..
select
    *
from
    employee
where
    email like '___#_%' escape '#' ; --escape문자 직접 선언. 보통은 역슬래시 사용.
    
    
--값 목록 비교 in
--D5 또는 D6 부서원을 조회
select 
    *
from
    employee
where
    --dept_code='D5' or dept_code='D6';
   -- dept_code in ('D5','D6'); --1개 이상의 값목록에 해당컬럼값이 포함되어있다면 true반환 괄호안에는 1개이상부터 갯수제한없음.('D5','D6','D1')
  --   not dept_code in ('D5','D6');
       dept_code !='D5' and dept_code!='D6';
    -- not(dept_code='D5' or dept_code='D6'); 앞에 not을 붙이거나 이걸 풀어쓸때 not을 연산에 모두 분배하면 =은 !=이 되고 , or은 and가 된다.
    
--null값 비교
--bonus가 null인 사원 조회
--dept_code가 null인 사원조회
select
    *
from
    employee
where
    --dept_code is null; -- = null로하면 값이 안나옴. is null을 사용해야한다.. is null <-> is not null
    --dept_code is not null; 
    nvl(dept_code, 'D0') = 'D0'; --null을 비교할 수 없으니, 널일때 dept_code로 D0값을 부여하고 그걸로 검색.(우회적인 방법)
    
    
--------------------------------------
-- ORDER BY
--------------------------------------
-- 결과 집합 처리시 가장 마지막에 수행되는 정렬 작업구문..
-- 컬럼명 , 별칭, 컬럼 순서를 기준으로 정렬
-- 정렬이 필요한 경우 반드시 명시적으로 order by를 사용해야 한다.

-- 기본 정렬 ASC <->desc (내림차순)
-- 숫자형 : 작은수->큰수
-- 날짜형 : 과거->미래
-- 문자형 : 가나다, abc순..
-- null : last(nulls first 설정 가능)

select
    *
from
    employee
order by
    dept_code asc nulls first,emp_name desc;
    
    
    
--자바와 다르게 1부터 시작함
select
    emp_name "이름",  --1
    salary "급여" --2
from
    employee
order by
   -- "급여" desc;
    2 desc;

 

 

드디어 DB구나