[Oracle] 쿼리 및 명령어

niz w·2024년 10월 22일
dboracle

SQL

목록 보기
4/17

1. DB 접속

sqlplus [username]/[password]@[database]
=> sqlplus [username]/[password]@[port]/[service_name]



2. DB 생성

CREATE DATABASE [database 명];
CREATE USER [user 명] IDENTIFIED BY [password];
GRANT CONNECT, RESOURCE TO [user 명];

DB 생성 후, user 계정을 만들어 권한을 부여했다.



3. Table 생성

예시
CREATE TABLE users (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    email VARCHAR2(100)
);

테이블 안에 넣을 컬럼에 대해서는
[컬럼명] 타입 추가설정(기본키, 외래키, 디폴트, null 여부 등)의 순서로 작성해주면 된다.

  • 기본키 : [column name] [type] PRIMARY KEY
  • Null 불가 : [column name] [type] NOT NULL
  • Null 허용 : [column name] [type] NULL
  • 기본값 : [column name] [type] DEFAULT [기본값]
  • 외래키 :
    [외래키 column name] [type],
    FOREIGN KEY ([외래키 column name]) REFERENCES [기본키 table]([기본키 column name])


4. 기본 CRUD

1) 명령어 / 2) 예시 순으로 작성을 해보려고 한다.

1) INSERT

명령어
INSERT INTO [테이블명] ([컬럼1], [컬럼2], ...) VALUES ([컬럼1 넣을 값], [컬럼2 넣을 값], ...);
예시
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');

여기서 해당 테이블의 전체 값을 넣을 경우,
INSERT INTO [테이블명] VALUES ([컬럼1 넣을 값], [컬럼2 넣을 값], ...);으로 작성할 수도 있다!
하지만 실제 컬럼 순서와 동일해야 하고, 간혹 필수 값이 아닌 경우 제외시킬 수 있기 때문에
위의 예시처럼 작성하는 것을 추천한다!


2) SELECT

명령어
전체 컬럼 => SELECT * FROM [테이블명] WHERE [조건];
일부 컬럼 => SELECT [컬럼1], [컬럼2], [컬럼3], ... FROM [테이블명] WHERE [조건];
예시
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT name FROM users WHERE email = 'alice@example.com';


3) UPDATE

명령어
UPDATE [테이블명] SET [변경할 컬럼1] = [변경할 값], [변경할 컬럼2] = [변경할 값], ... WHERE [조건];
예시
UPDATE users SET name = 'Alicia' WHERE id = 1;


4) DELETE

명령어
DELETE FROM [테이블명] WHERE [조건];
예시
DELETE FROM users WHERE id = 1;



5. Table 구조 조회 및 관리


1) 테이블 목록 조회

접속한 사용자가 생성한 테이블
SELECT [테이블명] FROM user_tables;
접속한 사용자가 접근 권한을 가진 테이블
SELECT [테이블명] FROM all_tables;
데이터베이스에 존재하는 모든 테이블
SELECT [테이블명] FROM dba_tables;


2) 테이블 스키마 조회

DESCRIBE users;



6. Transaction 관리

1) Transaction이란?

  • 여러 쿼리의 집합을 하나의 작업으로 묶어 실행한다.
  • COMMIT : 트랜잭션이 성공적으로 완료되었을 때, 변경된 데이터를 영구적으로 반영
  • ROLLBACK : 트랜잭션 중 오류가 발생했을 때, 작업 취소 후 이전 상태로 돌림

2) 주요 특징

  • Atomicity(원자성) : 트랜잭션 내의 모든 작업이 모두 완료되거나 모두 실패해야 한다.
  • Consistemcy(일관성) : 트랜잭션이 완료되면 데이터베이스는 일관된 상태를 유지해야 한다.
  • Isolation(고립성) : 트랜잭션 중에 수행되는 작업은 다른 트랜잭션에 영향을 받지 않아야 한다.
  • Durability(지속성) : 트랜잭션이 성공적으로 완료되면, 그 변경 내용은 영구적으로 저장된다.

3) 관련 명령어

BEGIN;


INSERT INTO employees (employee_id, name) VALUES (101, 'John');


SAVEPOINT savepoint1;


INSERT INTO employees (employee_id, name) VALUES (102, 'Jane');


ROLLBACK TO savepoint1;


INSERT INTO employees (employee_id, name) VALUES (103, 'Mike');


COMMIT;

  • 위의 예시로 설명해보면, BEGIN으로 트랜잭션을 시작했다.
  • INSERT 작업을 진행한 뒤, COMMIT이 아닌 SAVEPOINT를 지정해주었다. 이를 통해 저장점을 설정한 것이다.
  • 다시 INSERT 시행 후 SAVEPOINT1로 돌아가는 ROLLBACK을 실행했다.
  • 그러면 두 번째 'Jane'에 대한 INSERT는 취소되고, 'John'을 저장한 상태로 돌아간다.
  • 세 번째로 'Mike'에 대해 INSERT 진행 후, COMMIT으로 데이터를 영구 저정했다.
  • 결론적으로 employees라는 테이블에는 'John'과 'Mike'만 저장된 것이다.

4) 격리 수준(Isolation Levels)

  • 동시에 실행되는 여러 트랜잭션 간의 간섭 정도를 설정하는 것이다. 고립성에 따라 동시성 제어가 이루어진다.

    READ UNCOMMITTED : 커밋되지 않은 데이터도 읽을 수 있는 가장 낮은 고립성 수준
    READ COMMITTED : 다른 트랜잭션에서 커밋된 데이터만 읽는 수준으로 Oracle 기본값
    REPEATABLE READ : 트랜잭션이 완료될 때까지 같은 데이터를 반복해서 읽을 수 있도록 보장
    SERIALIZABLE : 가장 엄격한 고립성 수준으로, 트랜잭션 간 데이터베이스가 완전히 고립되도록 보장


    설정 예시
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;



7. DB 및 USER 관리

1) 사용자 생성

명령어
CREATE USER [사용자명] IDENTIFIED BY [비밀번호];
GRANT [부여권한1], [부여권한2], ... TO [사용자명];
예시
CREATE USER my_user IDENTIFIED BY my_password;
GRANT CONNECT, RESOURCE TO my_user;


2) 사용자 권한 부여

명령어
GRANT [부여권한1], [부여권한2], ... ON [테이블명] TO [사용자명];
예시
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO my_user;



8. INDEX 생성

명령어
CREATE INDEX [인덱스명] ON [테이블명]([대상 컬럼명]);
예시
CREATE INDEX idx_users_email ON users(email);



9. JOIN

1) INNER JOIN

예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
INNER JOIN Profiles p
ON u.user_id = p.user_id;

  • 두 테이블에서 공통된 값을 기준으로 매칭된 데이터를 조회한다
    => Users 테이블과 Profiles 테이블에서 user_id가 일치하는 값들을 출력

2) LEFT (OUTER) JOIN

예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
LEFT JOIN Profiles p
ON u.user_id = p.user_id;

  • 왼쪽 테이블을 기준으로 데이터를 매칭한다.
  • 오른쪽 테이블에 일치하는 데이터가 없는 경우, 해당 부분은 Null로 표시한다.
    => Users 테이블의 모든 데이터를 가져오고, Profiles와 일치하지 않는 경우에는 Null로 표시

3) RIGTH (OUTER) JOIN

예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
RIGHT JOIN Profiles p
ON u.user_id = p.user_id;

  • 오른쪽 테이블을 기준으로 데이터를 매칭한다.
  • 왼쪽 테이블에 일치하는 데이터가 없는 경우, 해당 부분은 Null로 표시한다.
    => Profiles 테이블의 모든 데이터를 가져오고, Users와 일치하지 않는 경우에는 Null로 표시

4) FULL (OUTER) JOIN

예시
SELECT u.user_id, u.name, p.profile_id, p.age
FROM Users u
FULL OUTER JOIN Profiles p
ON u.user_id = p.user_id;

  • 양쪽 테이블의 모든 행을 가져오며, 일치하는 않는 부분은 Null로 표시
  • MySQL은 지원하지 않으므로 UNION을 통해 LEFT JOIN, RIGHT JOIN 값을 합치면 된다.
    => 양쪽 테이블에서 모든 데이터를 가져오고, 일치하지 않는 데이터는 NULL로 표시

5) CROSS JOIN

예시
SELECT u.user_id, p.profile_id
FROM Users u
CROSS JOIN Profiles p;

  • 두 테이블의 모든 행을 조합하여 카티전 곱(Cartesian Product)을 생성한다.
    => Users 테이블의 각 행과 Profiles 테이블의 각 행을 조합하여 결과 조회

6) SELF JOIN

예시
SELECT e1.employee_id, e1.name, e2.employee_id, e2.name
FROM Employees e1
INNER JOIN Employees e2
ON e1.manager_id = e2.employee_id;

  • 동일한 테이블을 두 번 사용해서 테이블 안의 데이터끼리 조인할 때 사용한다.
    => Employees 테이블에서 관리자의 employee_id와 직원의 manager_id를 매칭하여 결과 조회


10. DB 백업 및 복구

1) 백업

expdp [username]/[password]@[service_name] DIRECTORY=[backup_dir] DUMPFILE=[dump_filename]

  • DIRECTORY=[backup_dir] : 백업 파일을 저장할 폴더로, 실제 파일 시스템의 경로가 아닌 Oracle 디렉터리 객체이다. DBA가 정해놓은 경로라고 볼 수 있고, 특정 운영체제의 실제 경로와 연결된다.
  • DUMPFILE=[dump_filename] : 백업 데이터가 저장될 덤프 파일의 이름을 지정하는 것으로, backup.dmp나 원하는 명칭.dmp 형식으로 지으면 된다.

2) 복구

impdp [username]/[password]@[service_name] DIRECTORY=[backup_dir] DUMPFILE=[dump_filename]

profile
niz w
이전 포스트

[MySQL, MariaDB] MariaDB와 MySQL 차이

다음 포스트

[MySQL] 쿼리 및 명령어

0개의 댓글