251016 TIL

설치

Oracle 11g

SQL Developer

https://www.oracle.com/kr/database/sqldeveloper/technologies/download/

sysdba로 접속

Run SQL Command Line에서 접속

• Run SQL Command Line: SQL Developer의 콘솔 버전
• conn / as sysdba

• OS 인증 방식 사용
  • 운영체제 수준에서 이미 인증이 되었으므로, DB는 추가적으로 비밀번호 검증 없이 접근 가능
• SYS : 최상위 계정
  • 절대 오픈하면 안됨

cmd에서 접속

• sqlplus / as sysdba

• 쿼리 결과 보기 어려움 → 툴 사용(SQL Developer)

SQL Developer

접속 정보

• 사용자 이름 : sys
• 비밀번호 : oracle(설치할 때 설정한 비밀번호)
• 롤 : SYSDBA
• SID = Instance_name(인스턴스 ⇒ 메모리)
• db_name (데이터베이스 ⇒ 디스크)
• XE 버전은 SID와 db_name은 무조건 xe

앞으로 배울 내용

초급 (신입 필수!)

• SQL
• PL/SQL
  • SQL은 프로그래밍 언어처럼 변수 선언, 반복문, 예외 처리 등이 불가
  • PL/SQL을 통해 가능
• Administration(오라클 관리)
• Backup & Recovery(백업 & 복구) → 중요!

중급

• Performance Tuning(성능 진단)

고급

• SQL Tuning
• RAC(Real Application Cluster)

사용자 정보 조회

select * from dba_users;

• account_status(계정 상태)
  • expired : 비밀번호 만료
  • locked : 잠금 상태

alter user hr identified by hr account unlock;

• hr 계정의 비밀번호를 hr로 설정하고 계정의 락을 풀어줌
  • 실무에서는 계정명이랑 비밀번호 같게 만들면 X

• 오라클에서 관리하는 메타 데이터도 테이블에 저장
  • $가 붙어있음
  • 보기 힘든 정보가 많음
  • 보안 상 중요한 정보들도 있음
• 뷰를 통해 사용자가 보기 편하게, 보안 상 중요한 정보를 빼고 보여줌(ALL_, DBA_, USER_, V$ 등등)

DB 직무

• DBA : 현장에 상주하면서 DB 모니터링 및 관리
• DB 엔지니어 : 여러 사이트 돌아다니면서 DB 설치하고, DBA가 없는 환경에서 DBA 역할도 수행
• 솔루션 엔지니어 : DB를 모니터링 할 수 있는 GUI 솔루션들이 있음. 이러한 솔루션을 설치하고 관리하는 역할 수행
• 모델러 : 데이터 설계, ERD 작성, 업무 공부 필요(HR, CRM, SCM 등)

정규화 필요성

• 중복 제거
• 삽입/삭제/수정 이상 현상 방지

개요

Data

• 관찰이나 측정을 통해 수집된 사실(facts)이나 값(values)
• 컴퓨터가 처리할 수 있는 문자, 숫자, 소리, 그림의 형태로 된 자료

Database

• 데이터 집합
• 여러 사람이나 프로그램이 데이터를 쉽게 공유하기 위해 데이터의 조직화 된 모음으로 관리되는 데이터의 집합

DBMS(Database Management System)

• 데이터베이스 소프트웨어를 의미
• Oracle, MySQL, MSSQL, MariaDB, SQLite, PostgreSQL, DB2

RDBMS(Relational Database Management System)

• 2차원 표 형식(ex: 파이썬 Dataframe)
• 2차원 테이블 형태로 표현한 뒤 각 테이블 간의 관계를 정의
• 행(row)과 열(column)로 구성

SQL(Structured Query Language)

• 데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용하는 언어
• ANSI(American National Standards Institute) 표준어인 SQL 사용

1. DQL(Data Query Language)
   • 데이터베이스에 저장된 데이터를 조회하는 명령어
   • SELECT
   • 성능에서 가장 중요
   • 업무 파악 중요
2. DML(Data Manipulation Language)
   • 데이터베이스에 데이터를 입력, 수정, 삭제하는 명령어
   • INSERT, UPDATE, DELETE, MERGE
3. TCL(Transaction Control Language)
   • 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위한 명령어
   • COMMIT, ROLLBACK, SAVEPOINT
4. DCL(Data Control Language)
   • 데이터베이스에 저장된 데이터를 관리하기 위해서 데이터의 보안성 제어하기 위한 명령어, 권한 관리
   • GRANT, REVOKE
5. DDL(Data Definition Language)
   • 데이터베이스를 생성 또는 삭제하고 객체들을 생성 유지 관리하는 명령어
   • CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT

• 일반적으로 DCL, DDL은 DBA만 다룸

SELECT문

• 데이터베이스에서 데이터를 조회(검색)하는 문

SELECT 컬럼, ...
FROM 소유자이름.테이블;

• 소유자명을 쓰는 습관을 갖는게 좋음
• 키워드는 대문자로 작성하는 것이 좋음
• 절마다 서로 다른 줄에 작성하는 것이 좋음
• 아래 문장은 모두 같은 결과
  
  • SQL Developer 실행 계획 단축키 F10
  • 쿼리 실행 계획을 한 번 만들고 나면 공유하는 것이 좋음
  • 하드 파싱하면 CPU, 메모리 사용량 많음
  • 실행 계획은 Share Pool의 Library Cache에 저장하고 공유해서 사용(소프트 파싱)
  • 쿼리의 대소문자, 띄어쓰기, 소유자 명 유무 등 한 문자라도 다르면 다른 쿼리로 인식
    → 실행 계획 공유 불가

select * from employees;

select *
from employees;

SELECT *
FROM employees;

SELECT 문 기능

• Projection : 열 추출
• Selection : 행 추출
• Join : 서로 다른 테이블에 있는 데이터를 추출

SELECT 문 작성

• SQL문은 대소문자를 구분하지 않음
• SQL문은 한 줄 또는 여러 줄에 입력 가능
• 키워드는 약어로 표기하거나 여러 줄에 걸쳐 입력 불가
• 키워드 : SELECT, FROM, WHERE, …
• 절은 대개 별도로 줄에 입력
• 가독성을 위해 들여쓰기를 사용
• SQL Developer에서 SQL문은 선택적으로 세미콜론(;)으로 끝날 수 있음
  • 세미콜론은 여러 SQL문을 실행하는 경우에는 꼭 필요
• SQL*Plus에서는 SQL문은 반드시 세미콜론(;)으로 끝나야 실행

SQL 문 작성시 주의점(성능 관점)

• SQL문의 실행 계획 공유하기 위해서 SQL문은 규칙적으로 작성해야 함
  • SQL문은 대소문자를 구분해야 함
  • 공백 문자 구분
  • tab key, enter key 구분
  • 주석 내용 구분
  • 힌트(hint, 실행 계획을 제어)를 구분

테이블 구조 확인

desc 테이블;

• SQL 명령어 아님. SQL Developer 툴 명령어임
• 제약 조건을 사용하는 이유? 데이터의 품질을 위해 규칙이 필요
  • Primary Key, Foreign Key, Check, Unique, Not Null
  • 그 외의 제약 조건은 트리거로 만들어야 함
• SQL Developer에서 확인

산술 연산자

• *, /, +, -
• number 타입은 사칙연산 가능
• date 타입은 +, - 만 가능
• varchar2, char 타입은 사칙연산 불가