[Java 풀스택 과정 강의] 2월 12일

※ TIL와는 별개로 적는 개인 개발 일지라서 말은 좀 편하게하는 페이지입니다.
일지이기 때문에 일기의 성격이 더 강합니다.

 

TITANIC 데이터 사용

TITANIC 데이터를 사용한 실전이었다. 관련하여 중요한 부분만 추가한다.

 

인라인뷰로 가공 컬럼 사용하기

SELECT AGEBAND, COUNT(passengerID) 탑승자수,
       ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) "생존율(%)"
FROM (SELECT FLOOR(AGE/10) * 10 AGEBAND, age, survived, passengerID
      FROM titanic)
GROUP BY AGEBAND
ORDER BY AGEBAND;

 

Oracle은 SELECT에서 만든 식을 GROUP BY에 사용하지 못하므로, 이렇게 인라인뷰 서브쿼리를 사용한다.

 

 

생존자 세기

SELECT sex "성별", COUNT(passengerID) AS "탑승자 수",
       SUM(survived) AS "생존자 수",
       ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) AS "생존율(%)"
FROM titanic
GROUP BY sex;

 

survived 컬럼이 0 / 1로 나뉘어 있어 SUM을 사용하여 셀 수 있다.

 

 

HAVING 사용한 그룹 단위 조건 설정

SELECT hometown,
       COUNT(passengerID) 탑승객수,
       ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) "생존율"
FROM titanic2
GROUP BY hometown
HAVING COUNT(passengerID) >= 10 AND ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) >= 50
ORDER BY 생존율;

 

HAVING을 복합으로 사용하여 10명 이상이고 생존율 50% 이상인 그룹을 필터링했다.

 

인라인뷰 2개를 JOIN하여 남/녀 생존율 차이 보이기

SELECT A.연령대, A.생존율 "남성 생존율(%)", B.생존율 "여성 생존율(%)",
       A.생존율 - B.생존율 "연령대별 생존율 차이"
FROM (
    SELECT AGEBAND 연령대,
           ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) "생존율"
    FROM (SELECT FLOOR(age/10) * 10 AGEBAND, age, survived, sex, passengerID FROM titanic)
    GROUP BY AGEBAND, sex
    HAVING SEX = 'male'
) A
LEFT JOIN (
    SELECT AGEBAND 연령대,
           ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) "생존율"
    FROM (SELECT FLOOR(age/10) * 10 AGEBAND, age, survived, sex, passengerID FROM titanic)
    GROUP BY AGEBAND, sex
    HAVING SEX = 'female'
) B ON A.연령대 = B.연령대
ORDER BY A.연령대;

 

 

각각 생존율을 나타내고 생존율 차이를 사용한다.

OUTER JOIN인 이유는 서로 null이 있기 때문이다.

 

 

성별·연령대·객실등급 조합의 소계 및 총계 나타내기

SELECT sex 성별, AGEBAND 연령대, pClass 객실등급,
       COUNT(passengerID) 승객수,
       SUM(survived) 생존자수,
       ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) "생존율"
FROM (SELECT FLOOR(AGE/10) * 10 AGEBAND, sex, pClass, age, survived, passengerID FROM titanic)
GROUP BY CUBE(AGEBAND, pClass, sex)
ORDER BY 객실등급, 성별, 연령대;

 

이를 통하여 성별/연령대/객실등급 차에 따라 무엇이 더 생존율에 영향을 주는지 파악하기 쉽다.

 

 

별칭 정렬

SELECT sex "성별", COUNT(passengerID) AS "탑승자 수",
       ROUND(SUM(survived) / COUNT(passengerID) * 100, 2) AS "생존율(%)"
FROM titanic
GROUP BY sex
ORDER BY "생존율(%)" DESC;

 

ORACLE에서는 별칭을 기준으로한 정렬이 가능하다.

 

DCL문

DCL이란 데이터 관리 질의어를 의미한다.

 

트랜잭션

COMMIT

트랜잭션 처리를 정상적으로 종료시켜 변경된 내용을 데이터베이스에 영구 반영시키는 연산이다. 세션이 종료되면 자동으로 커밋이 일어난다. DDL은 실행 시 자동 커밋 된다.

COMMIT;으로 사용한다.

 

ROLLBACK

트랜잭션이 행한 모든 변경 작업을 취소하고 이전 상태로 되돌리는 연산. COMMIT 전까지 되돌린다.

ROLLBACK;으로 사용한다.

 

 

권한·역할

주체는 DBA(DataBase Admin)가 된다. 혹은 관리자 계정(예: System)이다.

 

GRANT TO

권한을 할당(부여)한다. GRANT 권한 종류 TO 계정명; 처럼 사용한다.

 

 

REVOKE FROM

권한을 회수한다. REVOKE 권한종류 FROM 계정명; 처럼 사용한다.

 

 

시스템 권한 (사용자/DB 전체)

사용자(계정)을 생성 및 삭제하거나하는 시스템적인 권한이다. 보통은 DBA의 권한이며, DBA에서 생성한 계정만 부여받는다.

 

• CREATE SESSION — 접속
• CREATE TABLE — 테이블 생성
• CREATE VIEW — 뷰 생성
• CREATE SEQUENCE — 시퀀스 생성
• CREATE PROCEDURE — 프로시저 생성
• ALTER xxx — 객체 변경
• DROP xxx — 객체 삭제
• ALTER SESSION — 세션 설정 변경

 

객체 권한

특정 객체를 조작할 수 있는 권한을 이야기한다.

• SELECT — 조회
• INSERT — 삽입
• UPDATE — 수정
• DELETE — 삭제
• ALTER — 구조 변경
• DROP — 해당 객체 삭제 (소유자만 가능한 경우 많음)

 

ROLE

사용자에게 효율적 권한을 부여하도록 여러개의 권한을 묶어놓은것을 의미한다. 위에서 말한 시스템/객체 권한의 모음이라고 보면 된다.

• CONNECT — 접속·세션 관련
• RESOURCE — 테이블/시퀀스 등 생성·사용
• DBA — 관리자용 전체 권한

 

 

계정 관리

CREATE/ALTER/DROP USER

유저를 생성/수정/삭제할 때 사용한다.

// 생성 관련
CREATE USER 계정명 IDENTIFIED BY "비밀번호";
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

// 수정 관련
//ORACLE은 USERS가 테이블 스페이스라 이런 식으로 수정이 가능하다
ALTER USER 계정명 수정 권한 수정컬럼 ON USERS;

// 삭제 관련
DROP USER 계정명;

//제약까지 삭제
DROP USER 계정명 CASCADE;

 

 

TABLESPACE

오라클에서 데이터가 실제로 저장되는 공간(파일)이다. 위에서 보였던 USERS도 TABLESPACE의 하나이다.

 

 

QUOTA

할당량에 대해서이다. 이것을 설정하면 해당 TABLESPACE(실제 용량)에서 얼마나 데이터를 저장할 수 있는가를 부여받게 된다.

 

 

 

그 외

다른 계정의 테이블등을 건드리기 위해서는 해당 테이블의 권한을 가지고 있는 계정에서 접근 권한을 부여해줘야 한다.