24일차: Oracle -데이터베이스, SQL문 - 2021.07.16

목차

1.     오라클 SQL Developer 설치 및 연동

2.     데이터베이스의 개념과 SQL 기본 개념

3.     SQL 기본 기능

4.     Select로 특정 데이터를 추출하기

 

1. 오라클 SQL Developer 설치 및 연동

 

1) 오라클 SQL Developer 설치

• SQL Developer 설치

- 오라클 그래픽 프로그램인 ‘sqldeveloper’ 압축 해제 후 ‘sqldeveloper’ 파일 바로가기 생성

- OracleServiceXE와 OracleXETNSListener가 실행 중인지 확인(내PC – 관리 – 서비스)

 

2) 오라클 SQL Developer 연동

• 커넥션 3개 연결

① system 계정에 대한 연결(DBA)

② scott 계정에 대한 연결

③ hr 계정에 대한 연결

 

① system 계정에 대한 커넥션 설정

- 좌측 상단의 + 버튼 클릭

 

- system 계정 정보를 입력하여 연결

 

- 하단의 테스트 누르고 좌측에 성공 메시지 확인

 

 

② scott 계정에 대한 커넥션 설정

- 동일한 방법으로 scott 계정 정보를 입력하여 연결

 

③ hr 계정에 대한 커넥션 설정

- 동일한 방법으로 hr 계정 정보를 입력하여 연결

 

* SQL Developer에서 글자 크기 설정

도구 - 환경설정 - 코드 편집기 - 글꼴

 

2. 데이터베이스의 개념과 SQL 기본 개념

 

1) 관계형 데이터베이스(RDBMS)의 이해

- 관계형 데이터베이스 관리시스템(RDBMS: Relational DataBase Management System)은 가장 일반적인 형태의 DBMS

 

 

2) SQL (Structured Query Language)

- 사용자와 관계형 데이터베이스를 연결시켜 주는 표준 검색 언어

- 데이터베이스에 저장된 데이터를 조회, 입력, 수정 삭제하는 등의 조작이나, 테이블을 비롯한 다양한 객체(시퀀스. 인덱스 등)를 생성 및 제어하는 역할

 

• SQL의 종류

① 데이터 정의어(DDL)

- 데이터베이스 관리자나 응용 프로그래머가 데이터베이스의 논리적 구조를 정의하기 위한 언어로서 데이터 사전(Data Dictionary)에 저장됨

 

② 데이터 조작어(DML)

- 데이터베이스에 저장된 데이터를 조작하기 위해 사용하는 언어로서 데이터 검색(Retrieval), 추가(Insert), 삭제(Delete), 갱신(Update) 작업 수행함

 

③ 데이터 제어어(DCL)

- 데이터에 대한 접근 권한 부여 등의 데이터베이스 시스템의 트랜잭션을 관리하기 위한 목적으로 사용되는 언어

 

유형	명령문
DQL (Data Query Language) - 질의어	SELECT(데이터 검색시 사용)
DML (Data Manipulation Language) - 데이터 조작어	INSERT(데이터 입력) UPDATE(데이터 수정) DELETE(데이터 삭제)
DDL (Data Definition Language) - 데이터 정의어	CREATE(데이터베이스 생성) ALTER(데이터베이스 변경) DROP(데이터베이스 삭제) RENAME(데이터베이스 객체이름 변경) TRUNCATE(데이터베이스 저장 공간 삭제)

 

3. SQL 기본 기능                  → ★2021_07_16_01 참고

 

1) scott 계정 테이블 확인(dept, emp)

- sql 프로그램 열어서 상단 사람 아이콘(SQL 워크시트) 클릭해서 ‘scott’으로 접속

 

 

- 테이블 목록 검색: select * from tab;

- 테이블 구조 확인: describe dept; (= desc dept;)

- 테이블 데이터 검색: select * from dept;

 

2) 오라클의 데이터형

- 숫자 / 문자 / 날짜

 

① 숫자(Number)

- Number 데이터형은 숫자 데이터를 저장하기 위해서 제공

- 형식: number(precision, scale)  ex. number(4), number(7, 2)

- precision은 소수점을 포함한 전체 자리수를 의미하며 scale은 소수점 이하 자리수를 지정

- scale을 생략한 채 precision만 지정하면 소수점 이하는 반올림되어 정수 값만 저장

- precision과 scale을 모두 생략하면 입력한 데이터 값만큼 공간이 할당

 

② 날짜(Date)

- Date는 세기, 년, 월, 일, 시간, 분, 초의 날짜 및 시간 데이터를 저장하기 위한 데이터형

- 날짜 타입 안에는 세기, 년, 월, 일, 시, 분, 초, 요일 등 여러 가지 정보가 들어 있지만 별다른 설정이 없으면 년, 월, 일만 출력

- 기본 날짜 형식은 "YY/MM/DD"형식으로 “년/월/일”로 출력

- 2005년 12월 14일은 “05/12/14”로 출력

 

③ 문자(Char / Varchar)

• Char

- 문자 데이터를 저장하기 위한 자료형으로, 고정 길이 문자 데이터를 저장

- 입력된 자료의 길이와는 상관없이 정해진 길이만큼 저장 영역을 차지하며 최소 크기는 1

- char(20)에 ‘seoul’이라는 5바이트만큼의 데이터만 저장되었어도 나머지 공간을 다른 용도로 쓸 수 없음

- CHAR는 주어진 크기만큼 저장공간이 할당되므로 편차가 심한 데이터를 입력할 경우 위의 예와 같이 저장공간이 낭비됨

 

• Varchar2

- Varchar2 데이터형은 가변적인 길이의 문자열을 저장하기 위해서 사용됨

- varchar(20)으로 설정하고 'seoul'이란 데이터를 저장한 경우, varchar2는 저장되는 데이터에 의해서 저장공간이 할당되므로 메모리 낭비를 줄일 수 있음

 

3) select SQL문

• Select: 데이터를 조회하기 위한 SQL 명령어

형식: SELECT [DISTINCT] {*, column[Alias], . . .} FROM table_name;

 

- SQL 명령어는 하나의 문장으로 구성되어야 하며 반드시 세미콜론(;)으로 마쳐야 함

- select문은 반드시 select와 from이라는 2개의 키워드로 구성되어야 함

- select절은 출력하고자 하는 칼럼 이름을 기술

- 특정 컬럼 이름 대신 *를 적으면 테이블 내의 모든 컬럼을 출력

- from절 뒤에는 조회하고자 하는 테이블명

- SQL문에서 사용하는 명령어들은 대문자와 소문자를 구분하지 않음

 

ex)

select * from dept;                                           // dept 테이블 전체 컬럼 조회

select dname from dept;                                    // dept 테이블에서 dname 컬럼 조회

select loc, dname, deptno from dept;                    // 순서대로 loc, dname, deptno 컬럼 조회

 

4) 오라클의 산술 연산자

• 산술 연산자 : 더하기(+), 빼기(-), 곱하기(-), 나누기(/)

- 숫자형(number 타입) 컬럼만 산술 연산 가능

 

 

select ename, sal, sal*12 from emp;

select sal + comm from emp;

select sal - 100 from emp;

select sal * 12 from emp;

select sal / 12 from emp;

select ename, job, sal, comm, sal*12+comm from emp;

// null은 산술연산이 되지 않기에 문제 발생

→ null 값을 다른 값으로 변환해주는 nvl 함수를 이용하여 계산해야 함

 

• NULL

- 정해지지 않은 값

- null 값은 산술 연산을 할 수 없다.

- null 값의 예    ex) EMP 테이블 : MGR 컬럼, COMM 컬럼

 

• NVL() 함수

- null 값을 다른 값(0)으로 변환해주는 함수 (NVL: Null Value)

- 형식: NVL(컬럼명, 변환될 값)

- NVL(comm, 0) : comm 컬럼의 null 값을 0으로 변환

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

 

5) 컬럼 이름에 별칭 지정하기

- SQL에서 쿼리문의 결과가 출력될 때, 컬럼 이름이 컬럼에 대한 헤딩(heading)으로 출력됨

- 컬럼 이름 대신 별칭을 출력하고자 하면 ‘컬럼 as 키워드’로 별칭을 기술

 ex) select ename, sal*12+nvl(comm,0) as "Annsal" from emp;

→ “sal*12+nvl(comm,0)” 대신 “Annsal”로 컬럼 이름 설정됨

 

<영문 별칭>

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;           → " " 생략 가능

select ename, sal*12+nvl(comm,0) "Ann sal" from emp;        → " " 안에서 띄어쓰기 가능

 

<한글 별칭>

select ename, sal*12+nvl(comm,0) as "연봉" from emp;

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;           → " " 안에서 띄어쓰기 가능

 

<오류 발생>

- 외따옴표(' '): select ename, sal*12+nvl(comm,0) 'Annsal' from emp;  → 오류 발생

- 띄어쓰기: select ename, sal*12+nvl(comm,0) 연 봉 from emp;         → 오류 발생(" " 없이 띄어쓰기 불가)

 

6) Concatenation 연산자(||)

- ‘Concatenation’의 사전적인 의미는 ‘연결’

- 기호: ||

- 역할: 여러 개의 컬럼을 연결할 때 사용

- ex) select ename || ' is a ' || job from emp;

 

7) Distinct 키워드

- distinct : 중복 행을 제거하고 한 번만 출력

- ex) select distinct deptno from emp;

→ 중복을 제거하고 3개의 부서 번호만 출력: 10, 20, 30

 

8) Count() 함수

- 데이터의 개수를 구할 때 사용

- 형식: count(컬럼명)

- ex) select count(*) from dept;                → *는 전체 컬럼 조회

     select count(job) from emp; -- 14      → ‘job’ 컬럼 조회

     select count(distinct job) from emp;   → 중복을 제외한 job의 개수(5개)

 

4. Select로 특정 데이터를 추출하기            → ★2021_07_16_01 참고

 

1) Where 조건과 비교 연산자

- 원하는 로우만 얻으려면 다음과 같이 로우를 제한하는 조건을 select문에 where절을 추가하여 제시해야 함

형식	select * [column1, column2, .. ,columnn] from 테이블명 where 조건절;

 

2) 비교 연산자

 

 

3) 문자 데이터 조회

- SQL에서 문자열이나 날짜는 반드시 외따옴표(single quotation) 안에 표시해야 함

- SQL문에 사용되는 키워드인 select나 from이나 where 등은 대소문자를 구별하지 않지만 테이블 내에 저장된 데이터 값은 대소문자를 구분

- ex) Q. 사원 테이블에서 사원명이 FORD인 사원의 정보를 검색하는 SQL문 작성

          select * from emp where ename = 'FORD';

          select * from emp where ename = 'ford';    → 검색 결과 없음(FORD != ford)

          select * from emp where ename = "FORD"; → 오류 발생

          select * from emp where ename = FORD;   → 오류 발생

 

4) 날짜 데이터 조회

- 날짜 데이터를 검색할 때 날짜 좌우에 외따옴표(' ')를 붙여야 함

- 날짜 데이터를 비교할 때 비교 연산자를 사용함

- ex) Q. 1982년 1월 1일 이후에 입사한 사원을 검색하는 SQL문 작성

           select * from emp where hiredate >= '82/01/01';

           select * from emp where hiredate >= '1982/01/01';

         * select * from emp where hiredate >= 82/01/01;  → 오류 발생(외따옴표 있어야 함)

 

5) 논리 연산자

- 논리 연산자: and, or, not

① AND 연산자

- 두 조건식을 모두 만족하는 데이터를 검색

- ex)

      Q. 사원 테이블에서 부서 번호가 10번이고, JOB이 MANAGER인 사원을 검색하는 SQL문 작성

      select * from emp where deptno=10 and job='MANAGER';

 

② OR 연산자

- 두 조건식 중에서 한 가지만 만족해도 데이터를 검색

- ex)

      Q. 사원 테이블에서 부서 번호가 10 이거나 JOB이 MANAGER인 사원을 검색하는 SQL문 작성

      select * from emp where deptno=10 or job='MANAGER';

 

③ NOT 연산자

- 논리값을 반대로 바꿔주는 역할

- ex)

      Q. 사원 테이블에서 부서 번호가 10이 아닌 사원을 검색하는 SQL문 작성

      select * from emp where not deptno=10;

 

6) Between and 연산자

- 특정 범위 내에 속하는 데이터를 구하는 데 사용되는 연산자

- 형식: where 컬럼명 between 작은 값 and 큰 값

- ex) Q1. 2000에서 3000 사이의 급여를 받는 사원을 검색하는 SQL문 작성

       select * from emp where sal between 2000 and 3000;

       Q2. 급여가 2000 미만이거나 3000 초과인 사원을 검색하는 SQL문 작성

       select * from emp where sal not between 2000 and 3000;