728x90
반응형
오전에는 무난하게 진도를 나가는 듯 했으나 자바 연동하기 시작하면서 또 이제 새로운것들을 배우고 나니
아 역시 빡세구나.. 다시 상기되면서..
하여튼 열심히 해야지
일단 오전에는 오라클을 했기 때문에 그것부터 먼저 정리하도록 하겠다.
/*
JOIN : 한개 이상의 테이불에서 원하는 데이터 추출
========
= 조인 조건
1. 같은 데이터를 가지고 있어야 한다
영화정보 = 맛집정보 (지역)
2. 항상 컬럼명이 동일하지 않아도 된다.
(기본 : 중복이 안된 상태-primary key, 참조하는 상태-foreign key)
3. JOIN은 데이터 여러개의 테이블에서 검색 : SELECT문장에서 사용이 가능
= 조인 종류 => 이차원 FOR
1. INNER JOIN (NULL일 경우 허용하지 않는다) - 교집합 (일반 USER)
= EQUI_JOIN : 연산자 (=)
형식)
ORACLE JOIN : 오라클에서만 사용하는 JOIN 명령어
=> SELECT A.col1, A.col2, B.col1, B.col2
FROM A,B
WHERE A.col3=B.col3
===
기본은 컬럼명 앞에 테이블명, 테이블 별칭을 사용한다
예외) 테이블에서 컬럼명이 다르면 생략이 가능, 같은 컬럼명이면 별칭 혹은 테이블명 꼭 붙여여함
=> 테이블명.컬럼명
=> 테이블별칭.컬럼명
=> 테이블별팅
FROM A a, B b
ANSI JOIN : 표준회된 조인 (다른 데이터베이스에서 사용이 가능)
=> SELECT A.col1, A.col2, B.col1, B.col2
FROM A JOIN B => , 대신 (INNER) JOIN => INNER는 생략이 가능
ON A.col3=B.col3 => WHERE 대신 ON
===
기본은 컬럼명 앞에 테이블명, 테이블 별칭을 사용한다
예외) 테이블에서 컬럼명이 다르면 생략이 가능, 같은 컬럼명이면 별칭 혹은 테이블명 꼭 붙여여함
=> 테이블명.컬럼명
=> 테이블별칭.컬럼명
=> 테이블별팅
FROM A a, B b
= NON_EQUI_JOIN : 연산자 (=가 아닌 연산자) >= AND <=, BETWEEN ~ AND (포함이 되어 있는 상태)
2. OUTER JOIN (INNER JOIN + 알파) => NULL값일 경우에 결과값을 가지고 온다 - 관리자 모드
= LEFT OUTER JOIN => INTERSECT + MINUS (A-B)
1. ORACLE JOIN
SELECT A.col1, B.col1, B.col2, A.col2
FROM A, B
WHERE A.col3=B.col3(+);
2. ANSI JOIN
SELECT A.col1, B.col1, B.col2, A.col2
FROM A LEFT OUTER JOIN B
WHERE A.col3=B.col3;
= RIGHT OUTER JOIN => INTERSECT + MINUS (B-A)
1. ORACLE JOIN
SELECT A.col1, B.col1, B.col2, A.col2
FROM A, B
WHERE A.col3(+)=B.col3;
2. ANSI JOIN
SELECT A.col1, B.col1, B.col2, A.col2
FROM A RIGHT OUTER JOIN B
WHERE A.col3=B.col3;
= FULL OUTER JOIN => UNION ALL (사용빈도 낮음)
1. ANSI JOIN
SELECT A.col1, B.col1, B.col2, A.col2
FROM A FULL OUTER JOIN B
WHERE A.col3=B.col3;
*/
--사원명, 부서명, 급여등급을 출력하라.
SELECT ename, dname, grade
FROM emp, dept, salgrade
WHERE emp.deptno=dept.deptno
AND sal BETWEEN losal AND hisal;
/*
급여 1000이상인 사원을 대상으로 조사!
부서번호별, job별 평균 급여를 구하되
평균급여가 2000 이상인
부서의 부서no, job, 평균 급여를 출력하시오
*/
SELECT deptno, job, AVG(sal)
FROM emp
WHERE sal>=1000
GROUP BY deptno, job
HAVING AVG(sal)>2000;
-- 사원들의 이름, 부서 번호, 부서 이름을 출력하라?
SELECT ename, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno;
-- 30번 부서의 사원들의 이름, 직업, 부서위치를 출력하라?
SELECT ename, job, loc
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND emp.deptno = 30;
--수당을 받는 사원의 이름, 직업, 부서위치를 출력하라?
SELECT ename, job, loc
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND emp.comm IS NOT NULL AND emp.comm>0;
--DALLAS에서 근무하는 사원의 이름, 직업, 부서번호, 부서이름을 출력하라.
SELECT ename, job, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND dept.loc LIKE '%DALLAS%';
--이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라?
SELECT ename, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND ename LIKE '%A%';
-- 사원이름과 그 사원의 관리자 이름을 출력하라
SELECT e1.ename "employee", e2.ename "manager"
FROM emp e1, emp e2
WHERE e1.mgr=e2.empno(+);
--사원이름과 직업, 급여, 급여등급을 출력하라?
SELECT ename, job, sal, grade
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal;
-- 사원이름과 부서명과 월급을 출력하는데 월급이 3000이상인 사원을 출력하라?
SELECT ename, dname, sal
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND sal>=3000;
-- BLAKE이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라?
SELECT ename, hiredate
FROM emp
WHERE hiredate> (SELECT hiredate FROM emp WHERE ename='BLAKE');
-- 부서명이 SALES 부서사원 중 가장 많은 급여를 받는 사원의 급여는?
SELECT MAX(sal)
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND dname LIKE 'SALES%';
--
SELECT DISTINCT *
FROM emp, dept
WHERE emp.deptno=dept.deptno;
-- emp(ename,job), dept(전체)
SELECT ename, job, dept.*
FROM emp, dept
WHERE emp.deptno=dept.deptno;
어제 했던 부분 복습 INNER JOIN과 OUTER 조인 활용해서 SELECT문으로 데이터 검색해올 수 있다. 자세한 부분은 어제 포스팅에 해뒀다
-- 20210206 DDL
/*
DQL : SELECT => ROW단위
DDL : 생성(정의언어) = CREATE, DROP, TRUNCATE(데이터만 지우고 테이블 형식은 남김), ALTER, RENAME => COLUMN단위
1) 데이터 저장 공간 (메모리공간) : 테이블
2) 가상 저장공간 (보안) : VIEW (SELECT 문장)
3) 자동증가번호 : 중복없는 데이터 - SEQUENCE
4) 찾기 (최적) : INDEX
5) PL/SQL (FUNCTION, PROCEDURE, TRIGGER) : 재사용
1. 오라클 데이터형
문자저장 => String
= CHAR : 고정 바이트, 1~2000byte (한글은 1000 byte까지만 가능)
==========
CHAR(10) => 숫자, 알파벳 (1글자~10글자까지 사용이 가능)
한글 (1글자~5글자까지 사용이 가능)
CHAR(10) => 'A' => 메모리 크기 10BYTE
'AB' => 메모리 크기 10BYTE
데이터 크기가 고정된 경우
= VARCHAR2 : 가변 바이크, 1~4000byte
==========
VARCHAR2(10) => 'A' => 메모리 크기 1byte
'AB' => 메모리 크기 2byte
= CLOB : 가변 바이트, 4GB (2GB:LONG)
============
2000자를 넘었을 때 사용 EX) 영화줄거리, 자기소개, 회사소개...
숫자저장
= NUMBER (p,s) p : 1~38 , s:-84~127
== ==
숫자 자리수 소수점 이하 자리수
NUMBER => 14자리수 // 자바로 받을때는 int가 아니라 long으로 받아야함
NUMBER(4) => 0~9999
NUMBER(7,2) => 정수 (7자리까지 사용이 가능), 실수 (5자리까지 사용, 2자리는 소수점으로 사용)
========================= ========
int double
날짜저장
= DATE : default, java.util.Date
= TIMESTAMP : 기록경주 (Date를 보완)
기타저장 (동영상, 이미지, 사진) InputStream
= BLOB : 2진파일로 저장
= BFILE : 파일 형식으로 저장
2. 제약조건 (정형화된 데이터) => 이상현상 방지 (수정,삭제,추가) => 데이터는 중복
= PRIMARY KEY : UNIQUE+NOT NULL
= UNIQUE : 중복이 없는 데이터 저장 (NULL 값을 허용)
= NOT NULL : NULL값을 허용하지 않는다
= FORIGN KEY : 참조 키
= CHECK : 지정된 데이터 첨부
= DEFAULT : INSERT시 데이터가 추가가 없는 경우
DML : INSERT, UPDATE, DELETE, MERGE
*/
-- 생성 (테이블 : 데이터를 저장하는 공간)
/*
문법 사항
= 알파벳이나 한글로 시작한다 (한글은 윈도우, 리눅스가 한글 표기법이 다르다)
= 테이블명 (컬럼)은 30byte사용이 가능 (한글 15자)
= 같은 데이터베이스안에서 (XE) 같은 테이블명을 사용 할 수 없다
========== ======
폴더 파일
= 숫자는 사용이 가능 (앞에 사용 금지)
= 특수문자 사용이 가능 ( _ , $) => 단어가 2개이상일떄 _ 사용
= 키워드는 사용할 수 없다 (사용하지마 말라고 권장하는 것)
생성 과정
CREATE TABLE Table명(
컬럼명 데이터형, name VARCHAR(34) 제약조건
컬럼명 데이터형 sex CHAR(4) 제약조건
)
*/
-- emp (empno(정수), ename(문자), job(문자), mgr(정수), hiredate(날짜), sal(실수), comm(실수), deptno(정수)
/*CREATE TABLE emp2 (
empno NUMBER(4),
ename VARCHAR2(34),
job VARCHAR2(20),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);*/
--DROP TABLE emp2;
--SELECT LENGTHB('홍길동') FROM DUAL;
--CATS => 기존의 테이블을 복사하는 방법
CREATE TABLE emp3
AS
SELECT * FROM emp WHERE 1=2; -- 조건이 FALSE 이기 때문에 데이터는 카피가 안되고 테이블 형태만 복사됨
SELECT * FROM emp3;
DESC emp3;
CREATE TABLE emp4
AS
SELECT * FROM emp; -- 조건이 FALSE 이기 때문에 데이터는 카피가 안되고 테이블 형태만 복사됨
SELECT * FROM emp4;
DESC emp4;
-- 데이터가 커서 들어가지 않음, 오류 발생코드
INSERT INTO emp4 VALUES(8000, '안녕하세요', 'MANAGER', 7788, SYSDATE, 3000, 100, 10);
-- 테이블의 데이터형 바꿔줌
ALTER TABLE emp4 MODIFY ename VARCHAR(20);
ALTER TABLE emp4 ADD regdate DATE DEFAULT SYSDATE;
ALTER TABLE emp4 DROP COLUMN regdate;
-- 전체 데이터를 삭제 (테이블 유지)
TRUNCATE TABLE emp4;
-- DDL (COMMIT포함)
-- 테이블 이름 변경
RENAME emp4 TO emp5;
ALTER TABLE emp5 RENAME COLUMN hiredate TO regdate;
DROP TABLE emp5;
DROP TABLE emp3;
SELECT 문은 ROW단위로 데이터를 검색하는 DQL 언어였다면
COLUMN단위로 데이터를 조작하는 DDL언어를 오늘 새로 배웠다.
데이터 정의언어로, 테이블 단위로 생성하거나 삭제, 컬럼추가 변경 등의 조작을 할 수 있는 언어이다.
-- 2021-02-16 DDL
/*
DDL 명령어 => column단위, COMMIT => 복구가 어렵다
= CREATE
생성 (데이터 저장 공간 : 테이블)
1. 다른 테이블 복사
= 테이블 형식만 복사
CREATE TABLE table명
AS
SELECT * FROM table_name
WHERE 100=200; (false 조건) ==> JOIN을 걸어서 테이블 여러개의 데이터를 모아서 관리
= 테이블 데이터까지 복사
= 사용자 정의 (새로운 테이블을 만든다)
= ALTER
= DROP
= RENAME
= TRUNCATE
*/
/*CREATE TABLE emp_dept
AS
SELECT emp.*,dname,loc
FROM emp, dept
WHERE emp.deptno=dept.deptno
AND 1=0;
SELECT *
FROM emp_dept;
DESC emp_dept;
*/
-- 테이블 => 데이터 복사
CREATE TABLE emp_dept_grade
AS
SELECT emp.*, dname, loc, grade
FROM emp, dept, salgrade
WHERE emp.deptno=dept.deptno
AND sal BETWEEN losal AND hisal;
SELECT * FROM emp_dept_grade;
-- 컬럼변경
/*
이름 널? 유형
-------- -------- ------------
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)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
GRADE NUMBER
*/
--INSERT INTO test VALUES('2');
DESC emp_dept_grade;
/*
컬럼변경 =>
형식) ALTER TABLE table_name MODIFY 변경할 컬럼명 데이터형 [제약조건]
컬럼추가 =>
형식) ALTER TABLE table_name ADD 추가할 컬럼명 데이터형 [제약조건]
등록일 추가 => DATE regdate
이메일 추가 => email VARCHAR2(260)
컬럼삭제
컬럼이름변경
*/
ALTER TABLE emp_dept_grade MODIFY ename VARCHAR2(51);
ALTER TABLE emp_dept_grade MODIFY deptno NUMBER(3);
ALTER TABLE emp_dept_grade ADD regdate DATE DEFAULT SYSDATE;
ALTER TABLE emp_dept_grade ADD email VARCHAR(260) DEFAULT 'http:\\';
/*
컬럼삭제
형식) ALTER TABE table
*/
ALTER TABLE emp_dept_grade DROP COLUMN regdate;
ALTER TABLE emp_dept_grade RENAME COLUMN enmail TO homepgae;
/*
테이블 이름 변경
RENAME emp_dept_grade TO edg;
*/
/*
데이터 잘라내기
TRUNCATE TABLE table_name
*/
TRUNCATE TABLE edg;
select * from edg;
/*
테이블 삭제
DROP TABLE table_name;
*/
DROP TABLE edg;
-- 266 page
DELETE FROM emp;
select * from emp;
ROLLBACK;
-- 273 page 테이블 복사
DESC genie_music;
conn hr/happy
SELECT * FROM genie_music;
DDL 언어의 경우 COMMIT도 포함되어 있어서 한번 실행하면 ROLLBACK으로 되돌릴수가 없다. 주의해서 사용해야한다.
그 다음시간에는 응용으로 자바와 오라클을 연결하는 걸 배웠다. 어려웠다.
package com.sist.dao;
// 오라클 연결하는 프로그램
import java.util.*;// ArrayList
import java.sql.*;
public class MusicDAO {
// 연결하는 클래스
private Connection conn;//오라클 연결
// SQL문장으르 전송하는 클래스
private PreparedStatement ps;
private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
//1. 연결 드라이버 설치 => 한번만 사용 (생성자)
public MusicDAO()
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch(Exception ex){}
}
// 2. 연결
public void getConnection()
{
try
{
conn=DriverManager.getConnection(URL,"hr","happy");
// 오라클 => conn hr/happy
}catch(Exception ex) {}
}
// 3. 해제
public void disConnection()
{
try
{
if(ps!=null) ps.close();// 통신(송신:OutputStream,수신:BufferReader)
if(conn!=null) conn.close();// 연결기기: Socket
}catch(Exception ex) {}
}
// 4. 기능 => 뮤직데이터 50개를 받아서 저장 ==> 저장된 데이터 브라우저에서 읽어서 출력
public ArrayList<MusicVO> musicListData()
{
ArrayList<MusicVO> list=new ArrayList<MusicVO>();
try
{
// 1. 연결
getConnection();
// 2. SQL문장을 만든다
String sql="SELECT no,title,poster,singer,album,state,idcrement "
+"FROM genie_music "
+"ORDER BY 1";
ps=conn.prepareStatement(sql);// 전송준비 오라클에 문장을 써준것
// 3. SQL문장 전송
ResultSet rs=ps.executeQuery(); // run으로 실행시킨 것
// 4. 결과값을 가지고 온다
// 5. ArrayList에 담는다
/*
* =================
* no title...
* =================
* 1 == | next()
* =================
* 2 == | next()
* =================
* 3 == | next()
* =================
* 4 == | next()
* =================
* | next() => false
*
*/
while(rs.next())
{
MusicVO vo=new MusicVO();
vo.setNo(rs.getInt(1));
vo.setTitle(rs.getString(2));
vo.setPoster(rs.getString(3));
vo.setSinger(rs.getString(4));
vo.setAlbum(rs.getString(5));
vo.setState(rs.getString(6));
vo.setIdcrement(rs.getInt(7));
list.add(vo);
}
rs.close();
// 6. 대기상태
}catch(Exception ex)
{
ex.printStackTrace();
}
finally
{
disConnection(); // 닫기 (오류,정상 => 무조건 오라클을 닫는다)
}
return list;
}
}
728x90
반응형
'programming > Gukbi' 카테고리의 다른 글
국비 교육 38일차 - 제약조건 & 자바연동 (0) | 2021.02.19 |
---|---|
국비 교육 37일차 - DML, 제약조건 (0) | 2021.02.17 |
국비 교육 35일차 - GROUP BY, ORDER BY, JOIN (0) | 2021.02.15 |
국비 교육 34일차 - ORACLE (0) | 2021.02.10 |
국비 교육 33일차 - DB (0) | 2021.02.09 |