-
001. 테이블스페이스~컬럼 생성, 파일 조회 , * PASING 순서, 오라클의 주요 자료형(숫자,문자,날짜) , 연산자, 코멘트SsY/Class 2023. 3. 27. 00:02728x90
2023.03.22 (수)
ADMIN 파트의 일부분...
- 개발자로서 필요한 부분만!자바와는 다르게 오라클은 파일만 옮겨온다고 해서 생성한 TABLESPACE등이 서버에 자동으로 적용되지 않는다.
즉, 옮겨온 파일을 해당 PC에서 구문들을 실행(Ctrl+Enter)해주어야 생성이 된다.- 물론 실무에서는 각자 ORACLE 을 만들어서 하는 것이 아닌, 한 곳에 물리적으로 구축해두고 접속을 하여 사용하는 방식을 사용하게된다.
- 강의장에서 각자가 본인이 오라클을 설치하여 구축한 것은, 강의의 원할한 진행을 위해서다.
(이유 : 동일한 TABLESPACE 생성 불가 등..)- sys 계정 실습
- TABLESPACE (테이블스페이스) 생성 및 계정 생성 /권한 조회,부여
(* 은 ALL을 뜻 한다)
더보기--○ 생성된 테이블스페이스(TBS_EDUA)조회
SELECT *
FROM DBA_TABLESPACES;--○ 물리적인 파일 이름 조회
SELECT *
FROM DBA_DATA_FILES;--○ 오라클 사용자 계정 생성
CREATE USER 계정명 IDENTIFIED BY 패스워드
-------- (아이디 대소문자 구분 X (내부적 데이터의 경우 소문자를 쓰는것이 바람직)
--> (계정명) (이)라는 사용자를 만들겠다. (생성하겠다.)
---------------------- --> 패스워드는 (패스워드)로 하겠다.
DEFAULT TABLESPACE TBS_EDUA;
이 계정을 통해 생성하는 오라클 객체는(세그먼트들은) 기본적으로(DEFAULT) TBS_EDUA 라는 테이블스페이스에 생성하도록 설정하겠다.
--※ 생성된 오라클 사용자 계정(JSI)를 통해 -- 오라클 접속을 시도해 보았으나... 접속 불가. -- →『CREATE SESSION』권란이 없기 때문에 로그온 거부... --○ 생성된 오라클 사용자 계정(JSI)에 -- 서버에 접속이 가능하도록 『CREATE SESSION』권한 부여 → SYS 가... GRANT CREATE SESSION TO JSI; --==>> Grant을(를) 성공했습니다. --※ 생성된 오라클 사용자 계정(JSI)을 활용하여 -- 테이블을 생성하는 과정에서 해당 권한이 없음을 확인하고 -- 이에 대한 권한을 부여하기 위해 복귀 --○ 생성된 오라클 사용자 계정(JSI)의 -- DEFAULT TABLESPACE 조회 SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS; --> DEFAULT TABLESPACE 가 제대로 설정된 것을 확인할 수 있다. --○ 생성된 오라클 사용자 계정(JSI)의 -- 시스템 관련 권한 조회 SELECT * FROM DBA_SYS_PRIVS; --==>> /* : JSI CREATE SESSION NO : */ --○ 생성된 오라클 사용자 계정(JSI)에 -- 테이블 생성이 가능하도록 『CRATE TABLE』 권한 부여 GRANT CREATE TABLE TO JSI; --==>> Grant을(를) 성공했습니다. --○ 생성된 오라클 사용자 계정(JSI)에 -- 테이블스페이스(TBS_EDUA)에서 사용할 수 있는 공간(→ 할당량) 제공(설정) ALTER USER JSI QUOTA UNLIMITED ON TBS_EDUA; -- 할당량 키워드 // 할당량 크기 // ON ~ 위치 --> 무제한으로 지정 --==>> User JSI이(가) 변경되었습니다. -- 위의 구문들을 통해서 오라클이 논리적인 프로그램임을 알 수 있다.
- JSI 계정 실습
- 접속된 사용자 확인
더보기--○ 접속된 사용자 확인 SELECT USER FROM DUAL; --==>> JSI
- 테이블 생성 - 권한부여 - 할당량 부여 - 할당량 및 어떤 테이블 스페이스에 있는지 조회
- 위의 sys 실습에서 권한부여 / 할당량 부여 쿼리문 확인 가능
- 테이블 생성을 위해서는 테이블 생성 권한이 필요
- 테이블 생성 권한만 있어서는 테이블을 생성할 수 없음 : 집 지을 권리는 있는데 집을 지을 땅이 없다!
- 즉, 할당량을 부여받아야한다
- 할당량을 부여받으면 테이블을 생성할 수 있고, 자신에게 할당받은 할당량과
해당 테이블이 어느 테이블 스페이스에 위치했는지 조회가 가능하다.
더보기--○ 테이블 생성(테이블명 : TBL_ORAUSERTEST) CREATE TABLE TBL_ORAUSERTEST ( NO NUMBER(10) --// 첫 번째 컬럼 이름 -- 어떤 데이터 유형의 컬럼을 담을 것 (데이터 타입)_숫자 형태 -- 10자리 숫자까지 사용가능 (음수~양수) , NAME VARCHAR2(30) --// 두 번째 컬럼 이름 -- 데이터 타입 문자열 형태 -- VAR가변형 + CHAR문자 + 2 // VARCHAR 을 이미 다른데서 사용하고 있어서 2를 붙인 것 -- 30 (바이트)까지 표현가능 ); --==>> 에러 발생 /* ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to perform a database operation without the necessary privileges. *Action: Ask your database administrator or designated security administrator to grant you the necessary privileges */ --> 현재 JSI 계정은 CREATE SESSION 권한만 갖고 있으며 -- 테이블을 생성할 수 있는 권한은 관리자로부터 부여받지 못한 상태이다. -- 그러므로 테이블을 생성하기 위해서는 -- 관리자로부터 테이블 생성 권한을 부여받아야 한다. --○ SYS로 부터... 『CREATE TABLE』 권한을 부여 받은 후 -- 다시 테이블 생성(테이블명 : TBL_ORAUSERTEST) CREATE TABLE TBL_ORAUSERTEST ( NO NUMBER(10) , NAME VARCHAR2(30) ); --==>> 에러 발생 /* ORA-01950: no privileges on tablespace 'TBS_EDUA' 01950. 00000 - "no privileges on tablespace '%s'" *Cause: User does not have privileges to allocate an extent in the specified tablespace. *Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace. */ --> 테이블 생성 권한(CREATE TABLE)까지 부여받은 상황이지만 -- JSI 사용자 계정의 기본 테이블스페이스(DEFAULT TABLESPACE → TBS_EDUA)에 대한 -- 할당량을 부여받지 못한 상태 -- 그러므로 이 테이블스페이스를 사용할 권한이 없다는 에러메세지를 -- 오라클이 출력해주고 있는 상황. --○ SYS로 부터... 테이블스페이스에(→TBS_EDUA) 대한 할당량을 부여 받은 이후 -- 다시 테이블 생성(테이블명 : TBL_ORAUSERTEST) CREATE TABLE TBL_ORAUSERTEST ( NO NUMBER(10) , NAME VARCHAR2(30) ); --==>> Table TBL_ORAUSERTEST이(가) 생성되었습니다. --※ 자신에게 부여된 할당량 조회 SELECT * FROM USER_TS_QUOTAS; --==>> TBS_EDUA 65536 -1 8 -1 NO --// MAX_ ~에 -1 값은 무제한 이라고 생각하면 됨 --○ 생성된 테이블(TLB_ORAUSERTEST)이 -- 어떤 테이블스페이스에 저장되어 있는지 조회 SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES; --==>> TBL_ORAUSERTEST TBS_EDUA
- SCOTT 계정 실습
- sys 계정에서 SCOTT 계정 생성 후 권한 부여
- XE버전은 간단하게, 가볍게 만들어진 만큼 SCOTT 계정이 만들어져있지 않기 때문
더보기--○ 접속된 사용자 조회 SELECT USER FROM DUAL; --==>> SYS --○ 오라클 사용자 계정 생성(SCOTT) CREATE USER SCOTT IDENTIFIED BY tiger; --==>> User SCOTT이(가) 생성되었습니다. --○ 생성된 오라클 사용자(SCOTT)에게 권한 부여 GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO SCOTT; --==>> Grant을(를) 성공했습니다. --○ 생성된 오라클 사용자(SCOTT)에게 기본 테이블스페이스 설정 ALTER USER SCOTT DEFAULT TABLESPACE USERS; --==>> User SCOTT이(가) 변경되었습니다. --○ 생성된 오라클 사용자(SCOTT)에게 임시 테이블스페이스 설정 ALTER USER SCOTT TEMPORARY TABLESPACE TEMP; --==>> User SCOTT이(가) 변경되었습니다.
- 테이블 생성 및 데이터 입력
- DEPT, EMP, SALGRADE 테이블 생성 및 데이터 입력
- BOUNUS 테이블 생성
SELECT USER FROM DUAL; --==>> SCOTT ------------------------------------------------------------------------------------------ --○ 테이블 생성(테이블명 : DEPT) CREATE TABLE DEPT ( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY , DNAME VARCHAR2(14) , LOC VARCHAR2(13) ); --==>> Table DEPT이(가) 생성되었습니다. --○ 테이블 생성(테이블명 : EMP) CREATE TABLE EMP ( EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY , ENAME VARCHAR2(10) , JOB VARCHAR2(9) , MGR NUMBER(4) , HIREDATE DATE , SAL NUMBER(7,2) , COMM NUMBER(7,2) , DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT ); --==>> Table EMP이(가) 생성되었습니다. ------------------------------------------------------------------------------------------ --○ DEPT 테이블 데이터 입력 INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); --==>> 1 행 이(가) 삽입되었습니다. * 4 --○ EMP 테이블 데이터 입력 INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),3000,NULL,20); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy'),1100,NULL,20); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); --==>> 1 행 이(가) 삽입되었습니다. * 14 ------------------------------------------------------------------------------------------ --○ 테이블 생성(테이블명 : BONOUS) CREATE TABLE BONUS ( ENAME VARCHAR2(10) , JOB VARCHAR2(9) , SAL NUMBER , COMM NUMBER ); --==>> Table BONUS이(가) 생성되었습니다. --○ 테이블 생성(테이블명 : SALGRADE) CREATE TABLE SALGRADE ( GRADE NUMBER , LOSAL NUMBER , HISAL NUMBER ); --==>> Table SALGRADE이(가) 생성되었습니다. ------------------------------------------------------------------------------------------ --○ 데이터 입력 INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999); --==>> 1 행 이(가) 삽입되었습니다. * 5 ------------------------------------------------------------------------------------------ --○ 커밋 COMMIT; --==>> 커밋 완료.
- 해당 계정이 가지고 있는 테이블 조회
--○ 현재 SCOTT 오라클 계정 사용자가 소유하고 있는 테이블 조회 SELECT * FROM TAB; --테이블의 약자 --==>> /* BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE */
- 테이블이 저장되어있는 테이블 스페이스 조회
- 테이블 생성시 테이블 스페이스 지정해주지 않으면 DEFAULT 테이블에 생성 됨
(sys 계정에서 SCOTT 계정 생성 시, DEFAULT 테이블 스페이스를 USERS 로 해두었다.)
- 테이블 스페이스 지정하는 방법
--○ 생성된 각 테이블이 어떤 테이블스페이스에 저장되어 있는지 조회 SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES; --==>> /* DEPT USERS -- 별도로 테이블스페이스 설정 없이 생성하여 DEFAULT 테이블 스페이스에 저장됨 EMP USERS BONUS USERS SALGRADE USERS */ --○ 테이블 생성(테이블명 : TBL_EXAMPLE1) CREATE TABLE TBL_EXAMPLE1 ( NO NUMBER , NAME VARCHAR2(10) , ADDR VARCHAR2(20) ); --==>> Table TBL_EXAMPLE1이(가) 생성되었습니다. --○ 테이블 생성(테이블명 : TBL_EXAMPLE2) CREATE TABLE TBL_EXAMPLE2 ( NO NUMBER , NAME VARCHAR2(10) , ADDR VARCHAR2(20) ) TABLESPACE TBS_EDUA; -- 지정된 테이블스페이스에 테이블 생성 --==>> Table TBL_EXAMPLE2이(가) 생성되었습니다. --○ TBL_EXAMPLE1 과 TBL_EXAMPLE2 테이블이 -- 어떤 테이블스페이스에 저장되어 있는지 확인 SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES; --==>> /* : TBL_EXAMPLE1 USERS TBL_EXAMPLE2 TBS_EDUA */
- 관계형 데이터베이스 // PASING 순서
- 데이터를 테이블 형태로 저장시켜두고, 이들 테이블 간의 관계를 설정 하는 것
★SELECT 문의 PASING 순서★
/*==================================== ★ SELECT 문의 처리(PARSING) 순서★ --작성된 순서대로만 작성할 수 있다. 자리가 뒤바뀌면 안됨. SELECT 컬럼명 --⑤ --// 입력한 순서대로 나열 FROM 테이블명 --① --// SELECT 와 FROM 은 꼭 붙어다닌다 WHERE 조건절 --② --// 조건을 통해 해당하는 항목을 제한 GROUP BY 절 --③ --// 컬럼명 내에서 동일한 항목들을 그룹핑 HAVING 절 --④ --// GROUP BY 의 조건절 ORDER BY 절 --⑥ --// 절에 대한 정렬 ======================================*/ --// 순번은 오라클이 내부적으로 처리하게 되는 순서를 말한다.
- 해당 계정이 소유한 테이블(TABLE), 뷰(VIEW) 목록 조회 - 각 테이블의 데이터 조회
더보기--○ 현재 접속된 오라클 사용자(SCOTT)소유의 -- 테이블(TABLE), 뷰(VIEW)의 목록을 조회 SELECT * FROM TAB; --==>> /* BONUS TABLE → 보너스 정보 테이블 DEPT TABLE → DEPARTMENTS(부서) 정보 테이블 EMP TABLE → EMPLOYEES(사원) 정보 테이블 SALGRADE TABLE → SALARY(급여) 등급(GRADE) 정보 테이블 TBL_EXAMPLE1 TABLE TBL_EXAMPLE2 TABLE */ --○ 각 테이블의 데이터 조회 SELECT * FROM BONUS; --==>> 데이터 존재하지 않음 SELECT * FROM DEPT; --==>> /* 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON */ SELECT * FROM EMP; --==>> /* 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/07/13 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/07/13 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 */ SELECT * FROM SALGRADE; --==>> /* 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 */
- 테이블에 존재하는 컬럼의 정보(구조) 조회
- DESC 테이블명;
- NULL 값 가능 여부 확인
--○ DEPT 테이블에 존재하는 컬럼의 정보(구조) 조회 --// SELECT * FROM DEPT; 로도 확인은 가능은 함 DESCRIBE DEPT; -- DESCRIBE 를 줄여 DESC 로 사용 --==>> /* //▼ 널 값이 입력이 가능한지 확인 -- NOT NULL : 값이 비어있으면 입력이 불가능하다 이름 널? 유형 ------ -------- ------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) */ --※ 우리가 흔히 웹 사이트 등에서 회원가입을 수행할 경우 -- 필수 입력 사항과 선택 입력 사항이 있다. -- 필수 입력 항목은 ID, PW, 성명, 주민번호, 전화번호, ... -- 등과 같은 컬럼이며, 이 값들은 회원 가입 절차에 따라 -- 반드시 필요한(존재해야 하는, 입력해야 하는) 값이므로 NOT NULL 로 한다. -- 선택 입력 항목은 취미, 결혼여부, 차량소유여부, 특기, ... -- 등과 같은 컬럼으로 다루어지며, 이 값들은 회원 가입 과정에서 -- 반드시 필요한 값이 아니므로(즉, 입력하지 않아도 무방하므로) -- NULL 인 상태여도 상관없는 상황이 된다. -- DEPTNO DNAME LOC -- 부서번호 부서명 부서위치 -- NOT NULL (NULL 허용) (NULL 허용) --EX) 인사부 서울 → 데이터 입력 불가 -- 80 인천 → 데이터 입력 가능 -- 90 → 데이터 입력 가능
- 오라클의 주요 자료형
1. 숫자형 : NUMBER
2. 문자형 : 고정형( CHAR, NCHAR) 가변형 ( VARCHAR2, NVARCHAR2)
3. 날짜형 : DATE
- 날짜형식에 대한 세션 설정 변경
참고) MS-SQL 의 자료형
더보기/* cf) MS-SQL 서버의 정수 표현 타입 tinyint 0~255 1Byte smallint -32,768 ~ 32,767 2Byte int -21억 ~ 21억 4Byte bigint 엄청 큽니다 8Byte MS-SQL 서버의 실수 표현 타입 float, real MS-SQL 서버의 숫자 표현 타입 decimal, numeric MS-SQL 서버의 문자 표현 타입 char, varchar, Nvarchar */
--※ ORACLE 은 숫자 표현 타입이 한 가지로 통일되어 있다. (NUMBER) /* 1. 숫자형 NUMBER → -10^38 ~ 10^38 --// 길이 명시 안하면 엄청 큰 범위까지 포함하게 됨 NUMBER(3) → -999 ~ 999 NUMBER(4) → -9999 ~ 9999 NUMBER(4,1) → -999.9 ~ 999.9 --// 4-(소수점길이)자리, 소수점 이하 ,~ 자리 */ --※ ORACLE 의 문자 표현 타입 -- CHAR, NCHAR, VARCHAR2, NVARCHAR2 /* 2. 문자형 CHAR → 고정형 크기 --// 길이 명시 안하면 1바이트 만 표현 가능 (숫자와 반대) CHAR(10) → 무조건 10Byte 소모 CHAR(10) ← '강의실' 6Byte 이지만 10Byte 를 소모 -- // 자바와 다르게 길이 지정이 가능해 문자열 담김 //6바이트 담았다고 10바이트인 그릇이 6바이트로 줄어들지 않는다! CHAR(10) ← '잠깬메타몽' 10Byte CHAR(10) ← '다시졸린메타몽' 10Byte 를 초과하므로 입력 불가 VARCHAR2 → 가변형 크기 VARCHAR2(10) ← '강의실' 6Byte VARCHAR2(10) ← '잠깬메타몽' 10Byte VARCHAR2(10) ← '정말잠깬메타몽' 10Byte 를 초과하므로 입력 불가 --// 길이가 정해져있는 타입의 데이터라면 고정형을 쓰는게 좋다. - 가변형은 해당 데이터를 처리하기 위한 헤더의 공간을 더 차지하고 있게 된다. N + CHAR NCHAR → 유니코드 기반 고정형 크기 -// 유니코드 기반 = 글자 수 기반 NCHAR(10) 10 글자 N + VARCHAR2 NVARCHAR2 → 유니코드 기반 가변형 크기 NVARCHAR2(10) 10글자 */ --// 키워드로 잡혀있다고 전부 문법적으로 사용된다고 할 수 없다. (사용될 것 같은 예약어가 많기 때문에 + 다른 곳에서 사용하는 문법과 호환하기 위해서 EX)VARCHAR ) /* 3. 날짜형 DATE */ SELECT SYSDATE FROM DUAL; --==>> 23/03/22 --※ 날짜 형식에 대한 세션 설정 변경 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; --==>> Session이(가) 변경되었습니다. SELECT SYSDATE FROM DUAL; --==>> 2023-03-22 15:25:37 --// 데이터 타입이 DATE 타입!
- WHERE 조건절 조회
- 같다는 『=』 을 사용
--○ EMP 테이블에서 부서번호가 20번인 직원들의 데이터 중 -- 사원번호, 사원명, 직종명, 급여, 부서번호 조회 SELECT * FROM EMP; SELECT 사원번호, 사원명, 직종명, 급여, 부서번호 FROM EMP WHERE 부서번호가 20; SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO가 20; SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO == 20; --==>> 에러발생 /* ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: 384행, 15열에서 오류 발생 */ SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO = 20;
- 별칭(ALIAS) 의 사용과 주의점
--※ 테이블을 조회하는 과정에서 각 컬럼에 별칭(ALIAS)을 부여할 수 있다. SELECT EMPNO AS "사원번호", ENAME "사원명", JOB 직종, SAL "급 여" , DEPTNO"부서번호" FROM EMP; --※ 테이블 조회 시 사용하는 별칭(ALIAS)의 기본 구문은 -- 『AS "별칭명"』의 형태로 작성되며 -- 이 때, 『AS』는 생략할 수 있다. -- 또한, 『""』도 생략 가능하다. -- 하지만, 『""』를 생략할 경우 별칭의 이름에 공백은 사용할 수 업다. -- 공백은 SELECT 하는 과정에서 해당 컬럼의 표현이 끝났다는 것을 의미하므로 -- 별칭의 이름 내부에 공백을 사용하게 될 경우 -- 『""』를 사용하여 별칭을 부여할 수 있도록 처리해야 한다.
- OR , IN 연산자
- 또는 이라는 최소 하나의 조건을 만족 하는 조건을 만들 때 사용한다.
--○ EMP 테이블에서 부서번호가 20번과 30번 직원들의 정보 중 -- 사원번호, 사원명, 직종명, 급여, 부서번호 항목을 조회한다. -- (단, 별칭(ALIAS)을 사용한다) SELECT * FROM EMP; SELECT 사원번호, 사원명, 직종명, 급여, 부서번호 FROM EMP WHERE 부서번호가 20번과 30번; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO가 20번과 30번; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO가 20번 과 DEPTNO가 30번; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO = 20 과 DEPTNO = 30; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO = 20 이거나 DEPTNO = 30; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO = 20 || DEPTNO = 30; --==>> 에러 발생 /* ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: 468행, 110열에서 오류 발생 */ SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO = 20 OR DEPTNO = 30; --==>> /* 7369 SMITH CLERK 800 20 7499 ALLEN SALESMAN 1600 30 7521 WARD SALESMAN 1250 30 7566 JONES MANAGER 2975 20 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MANAGER 2850 30 7788 SCOTT ANALYST 3000 20 7844 TURNER SALESMAN 1500 30 7876 ADAMS CLERK 1100 20 7900 JAMES CLERK 950 30 7902 FORD ANALYST 3000 20 */ SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP WHERE DEPTNO IN (20,30); --> IN 연산자를 활용하여 이와 같이 처리할 수 있으며 -- 위에서 처리한 구문과 같은 결과를 반환하게 된다. --==>> /* 7369 SMITH CLERK 800 20 7499 ALLEN SALESMAN 1600 30 7521 WARD SALESMAN 1250 30 7566 JONES MANAGER 2975 20 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MANAGER 2850 30 7788 SCOTT ANALYST 3000 20 7844 TURNER SALESMAN 1500 30 7876 ADAMS CLERK 1100 20 7900 JAMES CLERK 950 30 7902 FORD ANALYST 3000 20 */
- 쿼리문은 대소문자 구분을 하지 않지만, 데이터 값은 대소문자 구문을 명확하게 한다
--○ EMP 테이블에서 직종이 CLERK 인 사원들의 정보를 모두 조회한다. SELECT * FROM EMP WHERE 직종이 CLERK; SELECT * FROM EMP WHERE JOB이 CLERK; SELECT * FROM EMP WHERE JOB = CLERK; --==>> 에러 발생 /* ORA-00904: "CLERK": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: 529행, 13열에서 오류 발생 */ SELECT * FROM EMP WHERE JOB = 'CLERK'; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 */ select * from emp where job = 'clerk'; --==>> 조회결과없음 --// 에러는 아닌데 결과는 안나온다! 왜? select * from emp where job = 'CLERK'; --==>> --// 쿼리문은 대소문자 구분 안 함 /* 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 */ select * from EMP where job = 'CLERK'; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 */ --※ 오라클에서 .. 입력된 데이터(값) 만큼은 -- 반.드.시 대소문자 구분을 한다.
- AND 연산자
- 적혀진 모든 조건을 만족해야할 때 사용하는 연산자
--○ EMP 테이블에서 직종이 CLERK 인 사원들 중 -- 20번 부서에 근무하는 사원들의 -- 사원번호, 사원명, 직종명, 급여, 부서번호 항목을 조회한다. SELECT 사원번호, 사원명, 직종명, 급여, 부서번호 FROM EMP; WHERE 직종이 CLERK 부서번호가 20; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP; WHERE 직종이 CLERK 부서번호가 20; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP; WHERE JOB이 CLERK DEPTNO가 20; SELECT EMPNO "사원번호", ENAME "사원명", JOB "직종명", SAL "급여", DEPTNO "부서번호" FROM EMP; WHERE JOB = 'CLERK' DEPTNO = 20; SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE JOB ='CLERK' AND DEPTNO = 20; --==>> /* 7369 SMITH CLERK 800 20 7876 ADAMS CLERK 1100 20 */
- >= , <=
크거나 같다, 작거나 같다의 경우에는 자바와 동일하게 사용한다.
--○ EMP 테이블에서 10번 부서에 근무하는 직원들 중 -- 급여가 2500 이상인 사원들의 -- 사원명, 직종명, 급여, 부서번호 항목을 조회한다. SELECT 사원명, 직종명, 급여, 부서번호 항목 FROM EMP WHERE 부서번호 10 급여가 2500 이상; SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE JOB ='CLERK' AND DEPTNO = 20; SELECT ENAME"사원명", JOB"직종명", SAL"급여", DEPTNO"부서번호 항목" FROM EMP WHERE DEPTNO = 10 SAL가 2500이상; SELECT ENAME"사원명", JOB"직종명", SAL"급여", DEPTNO"부서번호 항목" FROM EMP WHERE DEPTNO = 10 SAL >= 2500; SELECT ENAME"사원명", JOB"직종명", SAL"급여", DEPTNO"부서번호 항목" FROM EMP WHERE DEPTNO = 10 AND SAL >= 2500; --==>> /* KING PRESIDENT 5000 10 */
- 실습용 테이블 복사하기
- TBL_EMP, TBML_DEPT
더보기--※ EMP 테이블과 같은 테이블을 생성하고자 한다. -- (내부적으로 대상 테이블 안에 있는 데이터 내용만 복사) --① EMP 테이블의 구조 확인(파악) DESC EMP; --==>> /* 이름 널? 유형 -------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) */ --② 파악한 구조를 토대로 테이블 생성 CREATE TABLE EMPCOPY ( EMPNO NUMBER(4) , ENAME VARCHAR2(10) , JOB VARCHAR2(9) : ) --③ 대상 테이블의 데이터를 확인하여 -- 새로 생성한 테이블에 데이터 입력 SELECT * FROM EMP; INSERT INTO EMPCOPY ...; SELECT * FROM EMP; --※ 날짜 관련 세션 정보 설정 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; --==>> Session이(가) 변경되었습니다. SELECT * FROM EMP; CREATE TABLE TBL_EMP AS SELECT * FROM EMP; --==>> Table TBL_EMP이(가) 생성되었습니다. --○ 복사한 테이블 확인 SELECT * FROM TBL_EMP; --==>> /* 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 2850 30 7782 CLARK MANAGER 7839 1981-06-09 2450 10 7788 SCOTT ANALYST 7566 1987-07-13 3000 20 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-07-13 1100 20 7900 JAMES CLERK 7698 1981-12-03 950 30 7902 FORD ANALYST 7566 1981-12-03 3000 20 7934 MILLER CLERK 7782 1982-01-23 1300 10 */더보기--○ 테이블 복사(DEPT → TBL_DEPT) CREATE TABLE TBL_DEPT AS SELECT * FROM DEPT; --==>> Table TBL_DEPT이(가) 생성되었습니다. --○ 복사한 테이블 확인 SELECT * FROM DEPT; SELECT * FROM TBL_DEPT;
- 코멘트 정보 확인
- 테이블 코멘트 정보 확인 / 입력
- 컬럼레벨 코멘트 정보 확인 / 입력
--○ 테이블 코멘트 정보 확인 --// 오라클 내부 장부 SELECT * FROM USER_TAB_COMMENTS; --==>> /* DEPT TABLE (null) EMP TABLE (null) BONUS TABLE (null) SALGRADE TABLE (null) TBL_EXAMPLE1 TABLE (null) TBL_EXAMPLE2 TABLE (null) TBL_EMP TABLE (null) TBL_DEPT TABLE (null) */ --○ 테이블 코멘트 정보 입력 COMMENT ON TABLE TBL_EMP IS '사원데이터'; --==>> Comment이(가) 생성되었습니다. --○ 코멘트 정보 입력 이후 다시 확인(조회) SELECT * FROM USER_TAB_COMMENTS; --==>> /* TBL_DEPT TABLE (null) TBL_EMP TABLE 사원데이터 TBL_EXAMPLE2 TABLE (null) TBL_EXAMPLE1 TABLE (null) SALGRADE TABLE (null) BONUS TABLE (null) EMP TABLE (null) DEPT TABLE (null) */ --○ 테이블 레벨의 코멘트 정보 입력(TBL_DEPT → 부서데이터) COMMENT ON TABLE TBL_DEPT IS '부서데이터'; --==>> Comment이(가) 생성되었습니다. --○ 코멘트 정보 입력 이후 다시 확인(조회) SELECT * FROM USER_TAB_COMMENTS; --==>> /* TBL_DEPT TABLE 부서데이터 TBL_EMP TABLE 사원데이터 TBL_EXAMPLE2 TABLE (null) TBL_EXAMPLE1 TABLE (null) SALGRADE TABLE (null) BONUS TABLE (null) EMP TABLE (null) DEPT TABLE (null) */
--○ 컬럼 레벨의 코멘트 정보 확인 --// COL = COLUMN SELECT * FROM USER_COL_COMMENTS; --==>> /* TBL_EMP JOB EMP JOB TBL_EXAMPLE2 NAME DEPT LOC BONUS COMM BONUS SAL EMP SAL SALGRADE LOSAL TBL_DEPT DEPTNO TBL_EMP EMPNO TBL_DEPT DNAME TBL_EMP DEPTNO TBL_EMP ENAME TBL_EMP MGR EMP DEPTNO SALGRADE HISAL EMP EMPNO TBL_EXAMPLE1 NAME DEPT DEPTNO DEPT DNAME TBL_EMP HIREDATE EMP COMM SALGRADE GRADE TBL_EXAMPLE1 ADDR TBL_EXAMPLE2 ADDR EMP HIREDATE TBL_EXAMPLE2 NO BONUS JOB TBL_EXAMPLE1 NO TBL_EMP COMM TBL_EMP SAL EMP ENAME EMP MGR TBL_DEPT LOC BONUS ENAME */ SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'TBL_DEPT'; --==>> /* TBL_DEPT DEPTNO (null) TBL_DEPT DNAME (null) TBL_DEPT LOC (null) */ --○ 테이블에 소속된(포함된) 컬럼 레벨의 코멘트 정보 등록(설정) --// '.' COMMENT ON COLUMN TBL_DEPT.DEPTNO IS '부서번호'; --==>> Comment이(가) 생성되었습니다. COMMENT ON COLUMN TBL_DEPT.DNAME IS '부서이름'; --==>> Comment이(가) 생성되었습니다. COMMENT ON COLUMN TBL_DEPT.LOC IS '부서위치'; --==>> Comment이(가) 생성되었습니다. --○ 코멘트 데이터가 입력된 테이블의 컬럼 레벨의 정보 확인 SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'TBL_DEPT'; --==>> /* TBL_DEPT DEPTNO 부서번호 TBL_DEPT DNAME 부서이름 TBL_DEPT LOC 부서위치 */더보기DESC TBL_EMP; --==>> /* 이름 널? 유형 -------- -- ------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) */ SELECT * FROM TBL_EMP; --○ TBL_EMP 테이블에 소속된(포함된) -- 컬럼에 대한 코멘트 정보를 입력(설정)한다. COMMENT ON COLUMN TBL_EMP.EMPNO IS '사원번호'; COMMENT ON COLUMN TBL_EMP.ENAME IS '사원이름'; COMMENT ON COLUMN TBL_EMP.JOB IS '직종명'; COMMENT ON COLUMN TBL_EMP.MGR IS '상사번호'; COMMENT ON COLUMN TBL_EMP.HIREDATE IS '입사일'; COMMENT ON COLUMN TBL_EMP.SAL IS '급여'; COMMENT ON COLUMN TBL_EMP.COMM IS '보너스'; COMMENT ON COLUMN TBL_EMP.DEPTNO IS '부서번호'; --○ 코멘트 데이터가 입력된 테이블 (TBL_EMP)의 컬럼 레벨의 정보 확인 SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'TBL_EMP'; /* TBL_EMP EMPNO 사원번호 TBL_EMP ENAME 사원이름 TBL_EMP JOB 직종명 TBL_EMP MGR 상사번호 TBL_EMP HIREDATE 입사일 TBL_EMP SAL 급여 TBL_EMP COMM 보너스 TBL_EMP DEPTNO 부서번호 */728x90'SsY > Class' 카테고리의 다른 글
003. 문자열 데이터 조회(와일드카드, ESCAPE), 커밋/롤백, 데이터 업데이트, 정렬, 문자열 관련 함수 (0) 2023.03.28 002. 컬럼,데이터 추가/삭제, 테이블 제거, NULL 처리/연산, 문자열 결합, WHERE 조건문, BETWEEN ⓐ AND ⓑ (0) 2023.03.28 000. Oracle 설치 및 삭제 // 데이터베이스에 대한 이해 : 데이터, 정보, 데이터베이스(+관리) (0) 2023.03.25 019. 클래스와 인스턴스 : 접근지시제어자 활용, 메소드 중복정의(오버로딩) + 만년 달력, 주민등록번호 유효성 검사 (0) 2023.03.09 018. 클래스와 인스턴스 : static(클래스) 변수/메소드 (0) 2023.03.08