반응형
계정
접속 계정 확인
SQL> SHOW USER
USER is "SYSTEM"
모든 계정 정보 조회
SQL> SELECT * FROM ALL_USERS;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
XS$NULL 2147483638 28-AUG-11
APEX_040000 47 28-AUG-11
APEX_PUBLIC_USER 45 28-AUG-11
FLOWS_FILES 44 28-AUG-11
HR 43 28-AUG-11
MDSYS 42 28-AUG-11
ANONYMOUS 35 28-AUG-11
XDB 34 28-AUG-11
CTXSYS 32 28-AUG-11
OUTLN 9 28-AUG-11
SYSTEM 5 28-AUG-11
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 0 28-AUG-11
계정 생성
SQL> CREATE USER ubuntu IDENTIFIED BY 123456;
User created.
계성 삭제
SQL> DROP USER ubuntu CASCADE;
User dropped.
계정 비밀번호 변경
SQL> ALTER USER ubuntu IDENTIFIED BY 1234;
User altered.
계정 권한 부여
- CREATE user : 유저 생성 권한
- CREATE table : 테이블 생성 권한
- CREATE view : View 생성 권한
- CREATE sequence : 시퀀스 생성 권한
- RESOURCE : 개체를 생성, 변경, 제거할 수 있는 권한(DDL, DML 사용 가능)
- CONNECT : 데이터베이스에 연결할 수 있는 권한
SQL> GRANT CREATE user, CREATE table, CREATE view, CREATE session, CREATE sequence TO ubuntu;
Grant succeeded.
SQL> GRANT RESOURCE, CONNECT TO ubuntu;
Grant succeeded.
계정 권한 확인
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'UBUNTU';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
UBUNTU UNLIMITED TABLESPACE NO
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'UBUNTU';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
UBUNTU CONNECT NO YES
UBUNTU RESOURCE NO YES
생성한 계정으로 로그인
root@72a15344021a:/# sqlplus ubuntu/123456
SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 1 04:51:24 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> SHOW USER;
USER is "UBUNTU"
테이블
접속 계정 테이블 조회
SELECT TABLE_NAME FROM TABS;
SELECT TABLE_NAME FROM USER_TABLES;
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
테이블 생성
CREATE TABLE MEMBER
(
ID VARCHAR2(50),
PWD VARCHAR2(50),
NAME VARCHAR2(50),
GENDER VARCHAR2(50),
AGE NUMBER,
BIRTHDAY VARCHAR2(50),
PHONE VARCHAR2(50),
REGDATE DATE
);
테이블 삭제
DROP TABLE MEMBER;
테이블 스키마 조회
SELECT * FROM COLS WHERE TABLE_NAME = 'MEMBER';
컬럼 추가/수정/삭제
ALTER TABLE MEMBER ADD EMAIL VARCHAR2(100);
ALTER TABLE MEMBER MODIFY EMAIL VARCHAR2(200);
ALTER TABLE MEMBER DROP COLUMN EMAIL;
CRUD
데이터 추가
INSERT INTO MEMBER (ID, PWD) VALUES ('my-id', 'my-pwd');
데이터 수정
UPDATE MEMBER SET PWD = '1234', NAME = 'john' WHERE ID = 'my-id';
데이터 삭제
DELETE MEMBER WHERE ID = 'my-id';
DELETE FROM MEMBER WHERE ID = 'my-id';
데이터 조회
SELECT * FROM MEMBER;
SELECT ID, PWD FROM MEMBER;
SELECT ID AS "id", PWD AS "password" FROM MEMBER;
연산자
같다
SELECT * FROM MEMBER WHERE AGE = 25;
다르다
-- ANSI 표준
SELECT * FROM MEMBER WHERE AGE <> 25;
-- 여러 DBMS에서 지원
SELECT * FROM MEMBER WHERE AGE != 25;
-- OracleDB Only
SELECT * FROM MEMBER WHERE AGE ^= 25;
범위 조회
SELECT * FROM MEMBER WHERE AGE > 25;
SELECT * FROM MEMBER WHERE AGE >= 25;
SELECT * FROM MEMBER WHERE AGE < 25;
SELECT * FROM MEMBER WHERE AGE <= 25;
LIKE 조회
-- 이름이 j로 시작하는 데이터 조회
SELECT * FROM MEMBER WHERE NAME LIKE 'j%';
-- 이름이 j 다음 한글자가 존재하는 데이터 조회
SELECT * FROM MEMBER WHERE NAME LIKE 'j_';
NULL / NOT NULL 조회
SELECT * FROM MEMBER WHERE AGE IS NULL;
SELECT * FROM MEMBER WHERE AGE IS NOT NULL;
정규식으로 조회
-- 이름이 j로 시작하고 n으로 끝나는 데이터 조회
SELECT * FROM MEMBER WHERE REGEXP_LIKE(NAME, '^j(.*?)n$');
ROWNUM 활용하여 조회
SELECT *
FROM (
SELECT ROWNUM NUM, MEMBER.*
FROM MEMBER
)
WHERE NUM BETWEEN 2 AND 3;
덧셈
SELECT 1 + '3' FROM DUAL;
문자열 합치기
SELECT 1 || '3' FROM DUAL;
문자열
문자열 길이 조회
바이트 조회SELECT LENGTH('한글') FROM DUAL;
SELECT LENGTHB('한글') FROM DUAL;
참고
반응형
'Development > OracleDB' 카테고리의 다른 글
| [OracleDB] Data Type (0) | 2021.03.02 |
|---|---|
| [OracleDB] 설치하기 (0) | 2021.03.01 |