[DB #1 / 250327-0331] DB, SQL

temi·2025년 3월 31일

db mac oracle sql

SQL

목록 보기

1/4

학원에서 학습한 내용을 개인정리한 글입니다.

수업

DB DataBase

한 조직에 필요한 정보를 공용할 수 있도록 중복되는 데이터를 최소화해서 구조적으로 통합/저장한 것

DB 정의

운영 데이터

조직의 목적을 위해 사용되는 데이터

공용 데이터

공동으로 사용되는 데이터

통합 데이터

중복 최소화로 중복으로 인한 데이터 불일치 제거

저장 데이터

컴퓨터 저장장치에 저장된 데이터

특징

실시간 접근성

사용자가 데이터 요청시 실시간으로 결과 서비스

계속되는 변화

데이터 값은 시간에 따라 항상 바뀜

동시공유

서로 다른 업무 또는 여러 사용자에게 동시 공유됨

내용에 따른 참조

데이터 물리적 위치가 아닌 데이터 값에 따라 참조

DBMS

데이터베이스를 관리하고 운영하는 소프트웨어
데이터베이스에 저장된 데이터를 정의, 조작, 제어하는 기능을 제공

기능

데이터 추출
- 사용자가 조회하는 데이터 혹은 추출
데이터 조작
- 소프트웨어가 요청하는 데이터 삽입, 수정, 삭제 작업 지원
데이터 정의
- 구조를 정의하고 구조에 대한 삭제 변경 기능 수행
데이터 제어
- 사용자를 생성하고 모니터링하며 접근 제거 백업과 회복, 동시성 제어 등의 기능 지원

이점

데이터 독립화
- 데이터와 응용 프로그램을 분리시킴으로써 상호영향을 줄일수있음
데이터 중복 최소화, 무결성 보장
- 중복되는 데이터를 최소화시키면 데이터 무결성 손상 가능성이 줄어들고 공간의 낭비를 줄일 수 있음
데이터 보안 향상
- 권한에 맞게 데이터 접근을 제한하거나 데이터를 암호화시켜 저장 가능
관리 편의성 향상
- 다양한 방법으로 데이터 백업 가능, 장애 발생시 데이터 복구 가능

맥북 DB 세팅

맥북 m2을 사용하고 있어서 아래 링크를 참고하여 DB 세팅 완료
- https://velog.io/@ccorgi1997/Mac-M1-맥북에서-Oracle-11-사용하기
Oracle, Docker, Oracle SQL Developer 다운로드 받음!
귀여워서 디비버도 다운받았지만 학원에서 하는 동안은 안할것같다 아마도..
Docker 실행 후 application 내에서 열리지않아서 백그라운드 종료 후 이 페이지대로 따라했다. 이후 이슈는 발생하지 않았다.
- 맥에서 자주 발생하는 문제라고 한다.
- https://blog.developersung.com/blog/cannot-open-docker-in-mac-os/

SQL Developer

디버그 단축키: 신기하게 커맨드가 아님.. ctrl + enter
해당 줄마다 가서 디버그를 해줘야한다

데이터 베이스 사용하기

사용할 계정을 생성해야함. 관리자 계정에서만 가능

계정 생성

윈도우는 이걸 켜줘야 바로 계정이 생성된다고 한다

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

CREATE USER 사용자명 IDENTIFIED BY 비밀번호 DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

접속 권한 부여

GRANT CONNECT TO NAME;

저장소 생성

CREATE TABLE TEST(
    TEST_NAME VARCHAR2(20),
    TEST_AGE NUMBER
);

저장소 생성할 수 있는 권한 부여

GRANT RESOURCE TO NAME;

계정 생성 및 권한 부여후 접속시

💡

QNA

Temi: Null값은 보통 언제 사용하는지? 일부러 넣는 경우가 있는지? 아예 지양하는 편이라면 다른 값을 넣는 편인지?

Teacher : 디비 규칙에서도 지양으로 뜨긴하는데, 없는값은 없다고 널값을 아예 테이블을 갈라버림.

SQL Structured Query Language

Select

데이터를 조회한 결과는 result set이라고함
select구문에 의해 반환된 행들의 집함을 의미
특정 기준에 의해 정렬 가능한 테이블의 특정 컬럼, 행 등 조회 가능

as 문자열

질의 결과에 나오는 이름을 원하는대로 바꿀 수 있다

SELECT EMP_NAME as NAME

WHERE()

Where 조건식
--비교연산
--동등비교 =, !=, <>, ^=
--대소비교 >= <= > <
--대소비교 AND OR
--BETWEEN AND: 범위설정
--LIKE / NOT LIKE 문자열 패턴 비교
--IS NULL/ IS NOT NULL: NULL값 비교
--IN / NOT IN: 다수값을 한번에 비교

--부서가 d5인 사원의 전체 칼럼 조회하기 
SELECT * FROM EMPLOYEE WHERE DEPT_CODE = 'D5';

LENGTH()

문자열 길이를 알려주는 함수

SELECT LENGTH('오늘은 월요일') FROM DUAL; --7글자

INSTR()

-INSTR(문자열 또는 컬럼명, 찾을문자, 시작위치 찾을 횟수)
지정된 문자열에서 특정문자의 인덱스 번호를 반환해주는 함수
문자열의 인덱스 번호가 1부터 시작
INSTR(문자열 또는 컬럼명, 찾을 문자) -> JAVA indexOf() 메소드와 유사

SELECT INSTR('ㅇㅇ교육', 'ㅇㅇ'), INSTR('ㅇㅇ교육', '교') FROM DUAL;
SELECT INSTR('ㅇㅇIT교육센터 ㅇㅇ교육 ㅇㅇ개발부 ㅇㅇ교육본사', 'ㅇㅇ'),
    INSTR('ㅇㅇIT교육센터 ㅇㅇ교육 ㅇㅇ개발부 ㅇㅇ교육본사', 'ㅇㅇ',2),
    INSTR('ㅇㅇIT교육센터 ㅇㅇ교육 ㅇㅇ개발부 ㅇㅇ교육본사', 'ㅇㅇ',2,2),
    INSTR('ㅇㅇIT교육센터 ㅇㅇ교육 ㅇㅇ개발부 ㅇㅇ교육본사', 'ㅇㅇ',-2,2)
    FROM DUAL;

LPAD/ RPAD(대상 문자열, 전체공간, 대체문자열)

특정 공간을 정하고 그 공간에 데이터가 없을 때 지정한 값으로 채우는 함수

SELECT LPAD('오늘 월요일', 14, 'X'),
RPAD('오늘 월요일', 14, 'X')
FROM DUAL;

LTRIM/RTRIM(대상문자열[,문자])

문자열의 좌우에 있는 특정문구, 공백을 제거해주는 함수
두번째 매개변수가 없으면 공백 제거

SELECT '        아자',
LTRIM('        아자잣'),
RTRIM('월요병은 무서워             '),
LTRIM('AAABBBCCCC화이팅!', 'ABC') -- or
FROM DUAL;

TRIM(LEADING / TRAILING / BOTH '문자' FROM 대상문자열)

특정문자를 왼쪽, 오른쪽 양쪽에서 제거
양쪽의 공백, 문자를 제거하는 함수

SELECT TRIM('   hi ') AS A,
TRIM (LEADING 'z' FROM 'zzzzzhi') AS B
FROM DUAL;

SUBSTR(대상 문자열, 시작인덱스[,길이])

문자열을 잘라내는 함수

SELECT SUBSTR('오늘 너무너무 행복해 DB가 너무 쉽잖아', 13),
SUBSTR('오늘 너무너무 행복해 DB가 너무 쉽잖아', 13, 2),
SUBSTR('오늘 너무너무 행복해 DB가 너무 쉽잖아', -3, 3),
SUBSTR('오늘 너무너무 행복해 DB가 너무 쉽잖아', INSTR('오늘 너무너무 행복해 DB가 너무 쉽잖아', '행'),3)
FROM DUAL;

UPPER/LOWER()

영문자를 대문자/소문자로 변경해주는 함수

UPPER('Welcome to oRACLE word'),
LOWER('Welcome to oRACLE word'),

INITCAP()

첫글자만 대문자로 변경해주는 함수

INITCAP('Welcome to oRACLE word')

CONCAT()

문자열 결합해주는 함수
CONCAT()== ||

SELECT CONCAT('이번시간 끝나면', ' 점심시간'),
'이번시간 끝나면' || ' 점심시간'
FROM DUAL;

REPLACE(대상문자열, 찾을문자, 대체문자)

문자열을 변경해주는 함수

SELECT 'DB는 너무 어려워', REPLACE('DB는 너무 어려워', '어려워', '쉽다')
FROM DUAL;

REVERSE()

문자열을 거꾸로 만들어주는 함수

SELECT EMAIL, REVERSE(EMAIL)
FROM EMPLOYEE;

ABS()

절대값을 출력하는 함수

SELECT ABS(10), ABS(-10)
FROM DUAL;

--10, 10

MOD()

나머지 값을 구하는 함수

 --나머지 값을 구하는 함수: MOD() -> JAVA % 연산자
SELECT MOD(3, 2) FROM DUAL;

--1

소수점 처리하는 함수

ROUND: 소수점 반올림

SELECT 125.567, ROUND(125.567), ROUND(125.367), ROUND(125.567, 2), ROUND(125.567, 3)
FROM DUAL

CEIL: 무조건 올림

SELECT 125.123, CEIL(125.123), CEIL(125.83)
FROM DUAL

FLOOR: 소수점 버림

SELECT 125.123, FLOOR(125.123), FLOOR(125.83)
FROM DUAL

TRUNC: 소수점 버림, 자릿수 지정가능

SELECT 125.123, TRUNC(125.123), TRUNC(125.123, 2)
FROM DUAL;

DBMS_RANDOM.VALUE()

오라클에서 제공하는 랜덤함수

SELECT DBMS_RANDOM.VALUE(), 
DBMS_RANDOM.VALUE()*10,
FLOOR(DBMS_RANDOM.VALUE()), 
FLOOR(DBMS_RANDOM.VALUE()*10)
FROM DUAL;

DBMS_RANDOM.STRING('기호', 갯수)

문자열을 랜덤으로 출력
'X': 숫자 대문자 랜덤 출력
'P': 숫자 영문자 특수기호 출력
'U': 대문자
'L': 소문자

SELECT DBMS_RANDOM.STRING('X', 10), 
DBMS_RANDOM.STRING('P', 10), 
DBMS_RANDOM.STRING('U', 10), 
DBMS_RANDOM.STRING('L', 10)
FROM DUAL;

날짜 처리 함수

SYSDATE

DATE: 년, 월, 일 저장하는 타입 -> JAVA의 java.sql.Date 객체와 매핑

SYSTIMESTAMP

TIMESTAMP:년, 월, 일, 시, 분, 초 저장타입 -> java의 java.sqlTimestamp객체와 매핑

SELECT SYSDATE, SYSTIMESTAMP
FROM DUAL;

날짜에 산술연산 처리하기

-- +, -: 날짜의 일수가 증가하거나 차감되는 연산처리
SELECT SYSDATE, SYSDATE + 1, SYSDATE - 30
FROM DUAL;

NEXT_DAY(요일)

가장 가까운 요일 날짜를 출력하는 함수

SELECT NEXT_DAY(SYSDATE, '월')
FROM DUAL;

LAST_DAY()

그 달의 마지막 날 출력하는 함수

SELECT LAST_DAY(SYSDATE), LAST_DAY('00/04/04') 
FROM DUAL;

ADD_MONTHS()

개월 수를 더하는 함수
12월이 넘어갈 시 자동으로 다음 년도로 넘어간다

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 10)
FROM DUAL;

MONTHS_BETWEEN()

두 날짜의 개월 수 차이를 계산

SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, '00/01/01')) FROM DUAL;

EXTRACT()

년, 월, 일을 따로 출력하는 함수

--EXTRACT(YEAR FROM 날짜): 년도를 숫자로 변환
--EXTRACT(MONTH FROM 날짜): 월을 숫자로 변환
--EXTRACT(DATE FROM 날짜): 일을 숫자로 변환

SELECT EXTRACT(YEAR FROM SYSDATE) AS YEAR, 
EXTRACT(MONTH FROM SYSDATE)AS MONTH, 
EXTRACT(DAY FROM SYSDATE) AS DAY 
FROM DUAL;

SELECT EMP_NAME, HIRE_DATE, EXTRACT(MONTH FROM HIRE_DATE) AS 입사12월
FROM EMPLOYEE
WHERE EXTRACT(MONTH FROM HIRE_DATE) = 12;

TO_CHAR()

숫자, 날짜 타입을 문자열타입으로 변경해주는 함수
변경문자열은 패턴으로 표시
숫자를 문자로 변경하기
단위별 쉼표로 구분, 화폐를 표시할 때
-- 0: 해당 자리에 숫자가 없으면 0으로 표시
-- 9: 해당 자리에 숫자가 없으면 생략
-- L: 화폐를 표시할 때 설정* Locale을 기준으로 표시

SELECT 1234567,
TO_CHAR(1234567,'000,000,000'),
TO_CHAR(1234567,'999,999,999')
FROM DUAL;

소수점 표시

SELECT 180.5, 
TO_CHAR(180.5, 'FM000'),
TO_CHAR(180.5, 'FM000,000.00'),
TO_CHAR(180.5, 'FM999.99')
FROM DUAL;

날짜를 문자열로 변경하기

Y/R: 연도
M: 월
D: 일
H: 시
MI: 분
S: 초

SELECT SYSDATE,
TO_CHAR(SYSDATE, 'YYYY.MM.DD'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
--, . 같은 경우 자동으로 변경이 되지만 한글은 아니어서 "" 해줘야함
TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"')
FROM DUAL;

실습

-- 사원의 급여를 화폐단위로 포함하고, 단위별로 , 로 구분해서 출력
-- 입사일은 0000년00월00일로 출력
-- 사원명 급여 입사일
SELECT EMP_NAME as 사원명, 
TO_CHAR(SALARY, 'L999,999,999') as 월급, 
TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') as 입사일
FROM EMPLOYEE;

TO_NUMBER(대상문자열, 패턴)

문자열을 숫자로 변경하기

SELECT 1000 + '1000' FROM DUAL;
SELECT 1000 + '1,000' FROM DUAL; --X
SELECT 1000 + TO_NUMBER('1,000', '9,999') FROM DUAL;

TO_DATE(대상문자열, 대상숫자, '날짜패턴')

문자열을 날짜형으로 변경
숫자를 날짜형으로 변경

SELECT TO_DATE('99/01/08', 'YY/MM/DD') - 10,
TO_DATE('99-05-05', 'YY-MM-DD'),
TO_DATE(250227, 'YYMMDD'),
TO_DATE(TO_CHAR(000227,'000000'), 'YYMMDD')
FROM DUAL;

NVL() NVL2()

지정된 컬럼에 NULL이 있으면 대체값으로 출력하는 함수

--NULL 처리 함수
--NVL(컬럼명, 대체값) 지정된 컬럼에 NULL이 있으면 대체값으로 출력하는 함수
SELECT EMP_NAME, 
BONUS, 
NVL(BONUS, 0),
NVL(DEPT_CODE, '인턴')
FROM EMPLOYEE;

--NVL2(): NULL값일 때 출력할 값, NULL이 아닐 때 출력할 값을 설정하는 함수
SELECT EMP_NAME, DEPT_CODE, NVL2(DEPT_CODE, '정규직', '인턴') AS DIV
FROM EMPLOYEE;

선택 함수

DECODE(컬럼명, 비교값, 출력값, 비교값1, 출력값1…)

java의 스위치 문과 유사

SELECT EMP_NAME,
DECODE (DEPT_CODE, 'D9', '총무부', 'D6', '영업부') as 부서명
FROM EMPLOYEE;

CASE WHEN THAN

--CASE
-- WHEN 조건문
--  THEN 출력할 값
-- WHEN 조건문2
--  THEN 출력할 값2

실습

사원 테이블에서 현재 나이 구하기
연도 RR YY 관련 이슈 해결
- 만약 주민번호 1, 2 면 1900년대로, 3, 4면 2000년대로 계산

--RR로 연도 출력
--현재  입력   계산
--00~49 00~49 현세기
--00~49 50~00 전세기
--50~99 00~49 다음세기
--50~99 50~99 현세기

--
SELECT 

EXTRACT(YEAR FROM SYSDATE) -  EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1, 2),'RR')) AS 나이,
EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1, 2),'YY')),
EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO, 1, 2),'RR')),
EXTRACT(YEAR FROM SYSDATE) - 
(SUBSTR(EMP_NO, 1, 2) +
CASE
    WHEN SUBSTR(EMP_NO, 8, 1) IN ('1','2')
        THEN 1900
    WHEN SUBSTR(EMP_NO, 8, 1) IN ('3', '4')
        THEN 2000
END) as age
FROM EMPLOYEE;

그룹함수

--그룹 함수의 결과는 한개 ROW 출력
--SELECT문에서 그룹함수를 사용하면 컬럼을 선택하는 것이 제한된다
--합계, 평균, ROW 갯수, 최댓값, 최솟값

--SUM(컬럼): 컬럼값의 총 합계를 조회
--AVG(컬럼): 컬럼값의 평균을 조회
--COUNT(컬럼):ROW수를 조회 * 컬럼값이 NULL인 ROW제외
--MAX(컬럼): 컬럼값의 최댓값을 조회
--MIN(컬럼): 컬럼값의 최솟값을 조회

--D5 D6 D7 사원수 급여합계 평균 조회 
SELECT COUNT(*), SUM(SALARY), AVG(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE IN('D6', 'D7', 'D5');

--최고 급여 받는 사원
--그룹 함수는 where절에 사용할 수 없다
--사용하려면 다른 구문을 사용해야함
SELECT *
FROM EMPLOYEE
WHERE MAX(SALARY); --X

GROUP BY

--GROUP BY 절 이용
--동일한 컬럼 값을 묶어주는 기능
--SELECT 컬럼명
--FROM TABLENAME
--[WHERE 조건문]
--[GROUP BY 컬럼명[,컬럼명,컬럼명..]]
--[HAVING 조건문(그룹함수의 결과로 조건문생성)]

--부서별 급여합계 조회
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

실습

직책별 인원수가 3명이상인 직책조회

SELECT JOB_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY JOB_CODE
HAVING COUNT(*) >= 3;

매니저가 관리하는 사원이 2명 이상인 매니저 아이디 조회

SELECT MANAGER_ID, COUNT(*)
FROM EMPLOYEE
GROUP BY MANAGER_ID
HAVING COUNT(MANAGER_ID) >= 2;

미리 준비

sql 실습
4월 2일 자바 시험 준비

느낀점

진짜 영어 약하다… 영어 공부해야할 것 같음..

temi

250304~

다음 포스트

[DB #1 / 250327-0331] DB, SQL

SQL

수업

DB DataBase

DB 정의

운영 데이터

공용 데이터

통합 데이터

저장 데이터

특징

실시간 접근성

계속되는 변화

동시공유

내용에 따른 참조

DBMS

기능

이점

맥북 DB 세팅

SQL Developer

데이터 베이스 사용하기

계정 생성

접속 권한 부여

저장소 생성

저장소 생성할 수 있는 권한 부여

계정 생성 및 권한 부여후 접속시

QNA

SQL Structured Query Language

Select

as 문자열

WHERE()

LENGTH()

INSTR()

LPAD/ RPAD(대상 문자열, 전체공간, 대체문자열)

LTRIM/RTRIM(대상문자열[,문자])

TRIM(LEADING / TRAILING / BOTH '문자' FROM 대상문자열)

SUBSTR(대상 문자열, 시작인덱스[,길이])

UPPER/LOWER()

INITCAP()

CONCAT()

REPLACE(대상문자열, 찾을문자, 대체문자)

REVERSE()

ABS()

MOD()

소수점 처리하는 함수

DBMS_RANDOM.VALUE()

DBMS_RANDOM.STRING('기호', 갯수)

날짜 처리 함수

SYSDATE

SYSTIMESTAMP

날짜에 산술연산 처리하기

NEXT_DAY(요일)

LAST_DAY()

ADD_MONTHS()

MONTHS_BETWEEN()

EXTRACT()

TO_CHAR()

날짜를 문자열로 변경하기

실습

TO_NUMBER(대상문자열, 패턴)

TO_DATE(대상문자열, 대상숫자, '날짜패턴')

NVL() NVL2()

선택 함수

DECODE(컬럼명, 비교값, 출력값, 비교값1, 출력값1…)

CASE WHEN THAN

실습

그룹함수

GROUP BY

실습

미리 준비

느낀점

[DB #2 / 250401-0402] JOIN, SUBQUERY

0개의 댓글