오라클 데이터베이스 란, 미국 오라클(Oracle)사에서 판매하는 관계형 데이터베이스 관리 시스템이다.
오라클 SQL을 시작하기에 앞서 이 언어를 배워야하는 이유가 궁금했었다.
보통의 사람들은 데이터를 저장하거나 관리를 할때는 엑셀을 이용하고 사용한다. 하지만 기업으로 넘어가 생각해 보면 수십만 혹은 그 이상의 모든 데이터를 엑셀만을 이용하여 관리를 하기에는 벅차지 않을까?
이러한 이유로 우리는 대용량의 데이터를 저장, 검색, 수정 등 체계적인 관리를 하기 위해서 SQL (Structured Query Language) 이라는 데이터베이스 관리 프로그래밍 언어를 배워야 한다.
오늘은 간단하게 오라클 초기작업, SQL의 기초적인 부분 그리고 서브쿼리까지만 정리하였다.
오라클을 처음 시작할 때 초기작업을 잘 해주어야 접속과 관리가 편안하게 진행된다. 다음은 Oracle 초기작업을 하는 방법을 정리한 내용이다. 나는 오라클 21c xe를 설치하고, 실행하였다.
1) 오라클의 초기작업
oracle 초기작업
1. cmd를 실행하고 sqlplus 입력
2. 관리자 계정인 sys /as sysdba를 사용자이름으로 입력
3. 비밀번호는 오라클 21c xe 설치할 때 지정한 비밀번호 입력
4. 유저 생성
- create user 사용할 아이디 identified by "비밀번호";
5. 유저에 커넥션 권한을 주지 않으면 접속 자체가 불가능
6. 생성한 유저에 권한 부여
- grant 권한 1, 권한 2, .... to 유저아이디;
- grant connect(DB접속 권한), dba(관리자 권한), resource(자원 사용 권한) to 유저아이디;
- grant select, delete, insert, update on 테이블명 to 유저아이디; (해당 테이블에 대한 조회 삭제, 저장,수정 숸한 부여)
- grant all on 테이블명 to 유저 아이디; (해당 테이블에 대한 모든 권한 부여)
- grant all on 테이블명 to public; (해당 테이블에 대한 모든권한을 모든유저에게 부여)
7. 권한을 삭제하는 기능(명령)
- revoke 권한1, 권한2..... to 유저 아이디; (해당 사용자로부터 지정한 권한을 삭제)
8. 사용자 삭제명령
- drop user 사용자 아이디;
9. 사용자에게 부여된 역할 확인
- select * from dba_role_privs
where grantee = 사용자아이디;
10. 역할에 부여된 권한목록 확인
- select * sys_role_privs
where role = 역할(CONNECT, DBA, RESOURCE....);
JAVA나 HTML 같은 프로그램들과는 달리 처음에 설치하고 초기작업을 하는 과정이 조금은 더 복잡한 것 같아서 자꾸만 실수한건 없는지 불안하기도, 어렵기도 했던 것 같다.
2) SQL이란?
- SQL이나 씨퀄 또는 쿼리 라고 부른다.
- 데이터 베이스에서 데이터를 다루고 관리하는데 사용하는 질의 언어(명령)
- 쿼리를 작성해서 실행하게 되면 데이터 베이스에서 실행 결과를 알려주게 된다
- SQL의 분류
1. DQL(Data Query Language) : 데이터를 원하는 형태로 조회하는 명령(SELECT)
2. DML(Data Manipulation Language) : 데이터를 저장, 수정, 삭제하는 명령(INSERT, UPDATE, DELETE)
3. DDL(Data Definition Langauge) : 데이터 관리를 위한 테이블이나 다른 캑체들을 생성,수정,삭제하는 명령 (CREATE, DROP, ALTER)
4. TCL(Transaction Control Language) : 트랜잭션 종료나 취소를 실행하는 명령(COMMIT, ROLLBACK)
5. DCL(Data Control Language) : 데이터 사용권환과 관련된 명령(GRANT, REVOKE)
SQL의 분류의 내용들은 너무나도 중요하기 때문에 좀 더 자세한 내용은 다음번에 차례차례 다룰 예정이다.
3) 오라클의 객체
오라클의 객체란, " 오라클 데이터베이스 내에서 데이터를 저장하고 관리하기 위한 논리 구조를 가진 구성요소 " 를 말한다.
다음은 오라클의 객체 종류들과 그 설명들을 표로 정리하였다.
4) 실습데이터 작성 (예시) 및 조회쿼리
테이블을 생성 -> ( ) 안에 테이블에 포함될 컬럼의 정보를 정의 -> 테이블에 데이터를 저장하기!
--dept 테이블 생성
1. CREATE TABLE dept ( //dept테이블을 만듬
--dno 컬럼 생성 : 저장될 데이터는 varcher2(문자) + 최대길이는 2바이트인 문자만 저장 가능.
dno varchar2(2),
--dname 컬럼 생성 : 저장될 데이터는 varcher2(문자) + 최대길이는 2바이트인 문자만 저장 가능.
dname varchar2(20),
--loc 컬럼 생성 : 저장될 데이터는 varcher2(문자) + 최대길이는 8바이트인 문자만 저장 가능.
loc varchar2(8) ,
--directer 컬럼 생성 : 저장될 데이터는 varcher2(문자) + 최대길이는 4바이트인 문자만 저장 가능.
director varchar2(4)
);
- CREATE TABLE : 테이블을 생성해라.
- () 안의 내용: 테이블에 포함될 컬럼에 대한 정보(저장될 데이터의 타입,길이)를 정의
- varcher2 (길이) : oracle에서 사용하는 문자 타입 , 길이 2자리 컬럼명
-- -> dno. / dname / 매개변수는 데이터로 저장될 수 있는 최대 바이트를 의미.
- date : oracle에서 사용하는 날짜 와 시간타입.
- number(길이) : 최대 길이 만큼의 십진수를 넣을 수 있음
- number(정수,정수) : 앞의 정수는 정수부 + 소수부의 길이를 지정
-- (뒤의 정수는 소수점 뒷자리를 몇자리까지 표출할건지 결정.)
2. INSERT INTO EMP VALUES ('0201','안영숙','지원','0001','1991-02-01:19:13:24',3900,2200,'01');
- INSERT INTO 테이블명 : 해당 테이블에 데이터를 저장라라.
- INSERT INTO 테이블명(데이터가 저장될 컬럼들.....)
VALUSE(값들,,,,)
- 테이블의 모든 컬럼의 값을 저장할 떄는 저장될 컬럼들을 생략할 수 있다.
- 저장될 컬럼이 지정된 순서와 매칭된 값의 순서가 같아야 한다.
ex) INSERT INTO STUDENT(SNO) VALUES('이영은'); ----> 오류 발생 ===> 숫자밖에 못넣는데 문자가 들어가니까 오류/ 값과 순서가 맞아야 오류가 발생하지 않는다.
즉, INSERT INTO STUDENT(SNO) VALUES(1); 과 같이 숫자면 숫자 동일하게 할 것.
- 해당컬럼들이 모두 있으면 생략 가능
3. DESC 테이블명; ---> 해당 테이블의 구조를 확인할 수 있는 명령실습 테이블의 구조
1. 직원 관리 테이블
- DEPT(부서테이블) : DNO(부서번호)
DNAME(부서이름)
LOC(뿌서의 지역)
DIRECTOR(팀장의 사원번호)
- EMP(사원테이블) : ENO(사원번호)
ENAME(사원이름)
JOB(업무)
MGR(사수의 사원번호)
HDATE(고용일시)
SAL(급여)
COMM(보너스)
DNO(부서번호)
- SAL_GRADE(급여등급테이블) : GRADE(등급)
LOSAL(등급당 최저급여)
HISAL(등급당 최고급여)
2. 대학교 관련 테이블
- Course(과목테이블) : CNO(과목번호)
CNAME(고목이름)
ST_NUM(학점)
PNO(담당교수)
- Professer(교수테이블) : PNUM(교수번호)
PNAME(교수이름)
SECTION(교수전공)
ORDERS(직위)
HIREDATE(교수임용일시)
- STUDENT(학생테이블) : SNO(학생번호)
SNAME(학생이름)
SEX(성별)
SYEAR(학년)
MAJOR(전공)
AVG(평점)
- SCORE(기말성적테아블) : SNO(학생번호)
CNO(과목번호)
RESULT(결과점수)
- SCGRADE(기말성적등급테이블) : GRADE(등급)
HISCORE(등급당 최고점수)
LOSCORE(등급당 최저점수)
## 조회 쿼리 작성요령
1. 어떤 테이블에서 어떤데이터를 가져와서 보여줄건지 정하고 쿼리를 작성해야 한다.
ex) 기말고사 성적을 조회하는데 학생 이름과 과목이름을 동시에 조회하고 싶다.
SCORE.SNO / SCORE.CNO / COURSE.CNAME / SCORE.RESULT
- SELECT SCORE.SNO
, STUDENT.SNAME
, SCORE.CNO
, COURSE.CNAME
, SCORE.RESULT
FROM SCORE
INNER JOIN STUDENT ON SCORE.SNO = STUDENT.SNO
INNER JOIN COURSE ON SCORE.CNO = COURSE.CNO;
2. 조회 쿼리는 원하는 데이터들만 뽑아서 새로운 가상테이블을 만들어서 사용한다라고 생각.
위의 실습테이블은 앞으로 나올 모든 예제의 바탕이 되는 테이블이다.
실무에 나가서도 테이블의 구조를 잘 볼줄 알아야 하는 것이다 보니 반드시 연습을 해보면 좋을 것 같다.
실습 테이블의 밑부분에 조회쿼리라는 것이 나온다.
조회쿼리란, SQL 에서 데이터를 검색하고 조회하기위한 명령어로 가장 많이 사용되는 명령어 중 하나이다.
조회쿼리는 SELECT문을 사용하여 데이터 베이스에서 원하는 데이터를 검색하고 선택할수 있다. 조회쿼리를 잘 사용하면 데이터를 효율적으로 검색할 수 있어, 데이터 분석 및 보고서 작성 등에 매우 유용하다.
>>조회 쿼리를 사용할 때 유의할 사항<<
5) 서브쿼리 SUBQUERY
서브쿼리는 다수의 쿼리를 결합하여 하나의 쿼리로 표현한다. 이를 가상의 테이블처럼 사용하는데, Select, From, Where 절에 사용 할 수 있다.
서브쿼리의 경우, 단일 행 서브쿼리 , 다중행 서브쿼리, 다중 열 서브쿼리 3개로 분류 된다.
마지막으로 다음은 3개로 분류한 서브쿼리에 대한 내용과 예시이다.
< 서브쿼리 예시 1 >
--1. 서브쿼리
--단일 행
SELECT *
FROM PROFESSOR
WHERE PNAME = '송강';
-- 단일 행 서브쿼리
-- 송강보다 부임일시가 빠른 교수들의 목록 조회
SELECT P.*
FROM PROFESSOR P
WHERE P.HIREDATE < (
SELECT HIREDATE
FROM PROFESSOR
WHERE PNAME = '송강'
);
-- 손하늘 사원보다 급여가 높은 사원목록 조회
SELECT E.*
FROM EMP E
WHERE E.SAL > (
SELECT SAL
FROM EMP
WHERE ENAME = '손하늘'
);
--노육학생의 정보
SELECT *
FROM STUDENT
WHERE SNAME = '노육';
-- 노육과 학점 같은 학생들
SELECT ST.*
FROM STUDENT ST
WHERE AVR IN (
SELECT AVR
FROM STUDENT
WHERE SNAME = '노육'
);
SELECT ST.*
FROM STUDENT ST
JOIN(
SELECT *
FROM STUDENT
WHERE SNAME = '노육'
)B
ON ST.AVR = B.AVR;
--기말고사 성적이 95점 이상인 학번 과목번호 과목명 성적
--학생테이블과 조인
SELECT SC.SNO
, SC.CNO
, C.CNAME
, SC.RESULT
FROM SCORE SC
JOIN COURSE C
ON SC.CNO = C.CNO
AND RESULT >=95;
SELECT B.SNO
, B.CNO
, B.CNAME
, B.RESULT
, ST.AVR
, ST.MAJOR
FROM STUDENT ST
JOIN ( SELECT SC.SNO
, SC.CNO
, C.CNAME
, SC.RESULT
FROM SCORE SC
JOIN COURSE C
ON SC.CNO = C.CNO
AND RESULT >= 95
) B
ON ST.SNO = B.SNO;
< 서브쿼리 예시 2 >
--SCORE SCGRADE STUDENT => 하나의 서브쿼리
--COURSE PROFESSOR => 하나의 서브쿼리(담당교수가 없는 과목도 조회)
SELECT ST.SNAME
, ST.SNO
, SC.RESULT
, SC.SNO
, SC.CNO
, SG.GRADE
FROM SCORE SC
JOIN STUDENT ST
ON ST.SNO = SC.SNO
JOIN SCGRADE SG
ON SC.RESULT BETWEEN SG.LOSCORE AND SG.HISCORE;
SELECT C.CNO
, C.CNAME
, C.PNO
, P.PNAME
FROM COURSE C
LEFT JOIN PROFESSOR P
ON P.PNO = C.PNO;
--서브쿼리 2개다시 조인
--기말고사 성적을 조회, 담당교수가 없는 과목도 나올 수 있도록
-- 과목이름 교수이름 학생이름 점수등금과 함께 과목번호순서로 정렬
SELECT A.SNO
, A.SNAME
, B.CNO
, B.CNAME
, B.PNO
, B.PNAME
, A.RESULT
, A.GRADE
FROM(
SELECT ST.SNAME
, SC.RESULT
, SC.SNO
, SC.CNO
, SG.GRADE
FROM SCORE SC
JOIN STUDENT ST
ON ST.SNO = SC.SNO
JOIN SCGRADE SG
ON SC.RESULT BETWEEN SG.LOSCORE AND SG.HISCORE
)A
RIGHT JOIN(
SELECT C.CNO
, C.CNAME
, C.PNO
, P.PNAME
FROM COURSE C
LEFT JOIN PROFESSOR P
ON P.PNO = C.PNO
)B
ON A.CNO = B.CNO
ORDER BY B.CNO;
'IT Programming > Oracle' 카테고리의 다른 글
| [Oracle] SQL 지정한 컬럼을 기준으로 정렬해주는 ODERE BY (0) | 2023.04.20 |
|---|---|
| [Oracle] SQL 집합 연산자와 SELECT 구문 / 절 zip. (WHERE, IN, AND, OR, BETWEEN-AND) (0) | 2023.04.19 |
| [Oracle] 쿼리의 조인 VOL.3) Multiple Join 다중 테이블 조인? (0) | 2023.04.19 |
| [Oracle] 쿼리의 조인 VOL.2) NATURAL JOIN , CROSS/SELF JOIN (2) | 2023.04.19 |
| [Oracle] 쿼리의 조인 VOL.1) INNER JOIN , OUTER JOIN (0) | 2023.04.19 |
댓글