DB 과목평가 대비

sang0 2019. 10. 20. 05:13

반드시 요약해서 정리할 부분
1. 데이타베이스 구성요소 ★
- Entity(개체) : vo화 시킬 수 있는 요소
- Attribute(속성) : 개체의 성질
- Relationship(관계) : 개체와 개체 or 개체와 속성간의 관계

2. DDL, DML 아닌거, 맞는거
- DDL(Data Definition Language , 정의 기능) : CREATE, DROP, TRUNCATE, ALTER
- DML(Data Manipulating Language, 조작 기능) : (SELECT) INSERT, UPDATE, DELETE, MERGE
- DCL(Data Control Language, 제어 기능) : GRANT, REVOKE
- Transaction : COMMIT, ROLLBACK, SAVEPOINT
- SELECT는 DML이 아니라고 말했지만 책에는 DML로 되어있습니다...문제풀떄는 DML로 생각하세요★★★★★★

3. SQL : LIKE 연산자 ★★★
- 특정한 이름이 들어간 데이터 검색
- WHERE some LIKE '%철' : '철'자로 끝나는 이름
- WHERE start_date LIKE '%-05-%' "
- WHERE name LIKE '_심%' : '심'자가 가운데 이름으로 들어간 사원의 이름
(_ : 한글자, % : 여러글자)

4. NULL검색 :ifnull(), null과 그룹함수, null값 최소값
1) IFNULL(m.ename, 'b')
- 사용법 : IFNULL(컬럼명,'컬럼이 널일경우 대체할 데이터')
- m.ename가 null이면 'b', not null이면 m.ename .반환

2) null과 그룹 함수
- 그룹 함수는 null을 제외하고 연산
- where는 group by로 묶이기 전에 동작
- group by로 묶인 것은 having으로 걸러냄
- avg() : 숫자에만 적용
- sum() : 숫자에만 적용
- min() : 숫자, 문자, 날짜 다 적용
- max() :  숫자, 문자, 날짜 다 적용
- count() ★★★
- 테이블 행의 개수 리턴
- 숫자 넣으면 모든 열의 행 수 중 가장 큰 값이 리턴
- 컬럼 명을 넣으면 null을 제외한 행의 수 리턴

3) null값 최소값
- NULL 은 가장 작은 값
- 비교 연산자 불가 -> is | is not 써야함
- IFNULL(A, B) : A가 NULL이 아니면 A, NULL이면 B 반환 ★★★★★★

5. curdate(), year(), month()
1) curdate()
- CURDATE() or CURRENT_DATE() : 현재 날짜 출력
- CURTIME() or CURRENT_TIME() : 현재 시간 출력

2) ★YEAR(날짜) -날짜의 연도 출력

3) ★MONTH(날짜) -날짜의 월 출력

6. group by절과 having절과 where절에 관한 쿼리문 정리
group by - 데이터의 그룹별 처리를 할 때 사용
               (ex. row의 수, 특정 칼럼 중 가장 큰 수, 작은 수, 평균 값, 합계)
-- 부서별 평균 급여를 출력
select deptno, round(avg(sal)) from emp group by deptno;

-- 10번 부서를 제외한 부서별 평균 급여를 출력
select deptno, round(avg(sal)) AvgSal from emp
where deptno!=10 group by deptno;

-- 10번 부서를 제외한 부서별 평균 급여를 AvgSal(알리아스) 내림차순 정렬 후 출력
select deptno, round(avg(sal)) AvgSal from emp
where deptno!=10 group by deptno
order by AvgSal desc;

-- 입사년도 별 사원의 인원 수 출력
select left(hiredate, 4) '입사년도', count(*) '인원수' from emp
group by left(hiredate, 4);

select date_format(hiredate, "%Y"), count(*) from emp
group by date_format(hiredate, "%Y");
date_format : 날짜, 시간 표시 방식 지정
date_format(hiredate, "%Y") : 년도만 뽑아냄

-- 부서별로 평균 급여가 2000달러 이상인 부서의 번호와 평균 급여를 출력
select deptno, round(avg(sal)) from emp
group by deptno having round(avg(sal)) >= 2000;

-- 구문 순서 : SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY
-- 실행 순서 : FROM - WHERE - GROUP BY - AS - HAVING - SELECT - ORDER BY★★★★★★
1. 해당 데이터가 있는 곳을 찾아가서(테이블 참조)             (FROM)
2. 조건에 맞는 데이터만 가져와서                                  (WHERE)
3. 원하는 데이터로 가공                                              (GROUP BY)
4. 가공한 데이터에서 조건에 맞는 것만                           (HAVING)
5. 뽑아내서                                                              (SELECT)
6. 정렬                                                                    (ORDER BY)

select deptno DNumber, round(avg(sal)) AvgSal from emp
where deptno!=10
group by deptno having round(avg(sal)) >= 2000
order by DNumber desc;

7. 테이블 생성시 제약조건 처리
https://metelwiki.tistory.com/57 : [ORACLE] 테이블, 테이블 생성, 데이터 타입, 제약조건
1) 테이블 생성
CREATE TABLE member(
id varchar(20) primary key,
name varchar(20) not null,
address varchar(50) not null);

2) 제약 조건
- NOT NULL : 해당 컬럼에는 반드시 데이터를 입력해야 한다.
- UNIQUE : 해당 컬럼에 들어가는 값이 유일해야 한다는 의미이다. 즉, 중복 값을 허용하지 않는다.
- PRIMARY KEY : 기본키는 UNIQUE와 NOT NULL 속성을 동시에 가진 제약조건이므로, 테이블 당 1개의 기본키만 생성할 수 있다.
- FOREIGN KEY : 테이블 간의 참조 데이터 무결성을 위한 제약조건
- CONSTRAINT 외래키명 FOREIGN KEY(컬럼명,…)
REFERENCES 참조 테이블명(참조 테이블 컬럼명, …)
- ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk
FOREIGN KEY(deptno) REFERENCES dept(deptno); ★★★★★★★★★
- CHECK : 컬럼에 입력되는 데이터를 체크해 특정 조건에 맞는 데이터만 입력 받고 그렇지 않으면 오류를 뱉어 낸다.
CREATE TABLE EX2_9(
NUM1 NUMBER CONSTRAINTS CHECK1 CHECK (NUM1 BETWEEN 1 AND 9),
GENDER VARCHAR2(10) CONSTRAINTS CHECK2 CHECK (GENDER IN ('MALE', 'FEMALE'))
);

8. PK, FK사용이유
1) PRIMARY KEY : '데이터 무결성'
오라클을 비롯한 여러 DBMS는 데이터 무결성을 보장하고 있는데, 데이터 무결성이란 데이터의 정확성과 일관성을 유지한다는 뜻이다.
예를 들어, 사원 테이블(employees)에는 사번(employee_id)과 사원명(employee_name) 컬럼이 있는데 특정한 사람을 식별하려면
사번은 반드시 한 개이어야 한다. 만약 홍길동이란 사원이 사번이 두 개이거나 사번 자체가 들어가지 않고 데이터가 생성됐다면,
이 홍길동이란 사람을 정확히 식별할 수 있는 방법은 없다.
이런 식으로 데이터 무결성을 DBMS에서 실질적으로 구현한 것이 바로 기본키(UNIQUE + NOT NULL)이다.

2) FOREIGN KEY : '테이블 간의 참조 데이터 무결성'을 위한 제약조건
예를 들어 사원 테이블에는 부서번호(department_id) 컬럼이 있고, 부서정보는 부서 테이블(department)에 있다.
만약 신규 사원이 입사해 데이터를 입력하는 와중에 부서 테이블에 없는 부서번호를 입력한다면 논리적으로 봐도 합당하지 않고
이 신입사원에 대한 부서정보를 제대로 찾을 수 없다. 즉, 참조 무결성이 깨진다. 하지만 외래키를 생성해 놓으면 부서정보에
없는 부서번호를 입력할 때 오라클은 오류를 발생시켜 잘못된 데이터가 입력되는 것을 방지한다. 참조 무결성을 보장하는 것이다.
- 다른 테이블의 기본 키를 가져와 쓰는 키
- 외래키를 통해 클래스간의 association(연결)이 이루어짐 ★★★
- 외래키 생성으로 RDBMS(관계형 데이터베이스) 설계규칙을 따를 ★★★

9. INDEX 잘못 생성한 경우(생성쿼리)
http://bitly.kr/BXx03 : 인덱스 추가, 삭제, 확인
http://bitly.kr/Y8iMG : 인덱스 옵션 설명
http://bitly.kr/1zkLu : 인덱스 변경
1) 생성
- CREATE INDEX 인덱스이름 ON 테이블이름(컬럼이름);
- CREATE INDEX eidx ON emp (ename);

2) 삭제
- ALTER TABLE 테이블이름 DROP INDEX 인덱스이름;
- ALTER TABLE emp DROP INDEX eidx ;

3) 변경
- ALTER INDEX eidx ON emp SET (
옵션 이름 = ON,
옵션 이름 = ON,
...
  ) ;

10. SELECT문의 대략적인 순서  ★★★★★★★★★
- 서브 쿼리가 먼저 수행되고 그 질의된 결과의 값이 메인 쿼리에서 사용됨
- 구문 순서 SELECT - FROM - WHERE - ORDER BY ★
- ORDER BY는 거의 모든 구문의 맨 마지막에 나오고 거의 마지막에 실행
- 예외로 LIMIT(row 수 조절))는 ORDER BY 뒤에 나오고 진짜 마지막에 실행
- where는 group by로 묶이기 전에 동작
- having은 group by로 묶인 후에 동작

11. 서브쿼리, in과 not in
- http://bitly.kr/NBr8a : NOT IN, NOT EXIST 정리
- 단일행이 리턴되면 단일행 연산자 사용 ( =, !=, <, > ...)
- 다중행이 리턴되면 다중행 연산자 사용 ( IN, ANY, ALL )
- NULL이 있는 컬럼에 비교 연산자(단일행 연산자, 다중행 연산자)를 사용하면 결과 값은 NULL이 되고,
  Empty Set이 반환 되어 아무런 값을 리턴하지 않게 됨
2) IN : 여러 개 중에서 같은 값을 찾음
- SELECT ename FROM emp WHERE ename IN ('김남희', 'ABC');
- SELECT ename FROM emp WHERE ename='김남희' OR ename='ABC';

3) NOT IN : 여러 개에 포함되지 않은 값을 찾음
- SELECT ename FROM emp WHERE ename NOT IN ('김남희', 'ABC');
- SELECT ename FROM emp WHERE ename!='김남희' AND ename!='ABC';

12. view 실행(생성쿼리)
- 가상의 테이블 ★
- 생성
CREATE OR REPLACE VIEW eview
AS
SELECT deptno, SUM(sal) ssal
FROM emp
GROUP BY deptno
ORDER BY ssal;

- 검색
SELECT * FROM eview
GROUP BY deptno
ORDER BY ssal DESC;

13. outer join 종류 ★★★
1) LEFT Outer Join : 조인 수행시 우측 테이블이 기준이 되어서 결과 생산
2) RIGHT Outer Join : left가 null인 것도 표시
3) FULL Outer Join : MySQL에서 지원 안 함, UNION 연산자 사용해야 함

14. outer join 쿼리
1) LEFT Outer Join
select concat(e.ename, ' 의 상사 이름은 ', ifnull(m.ename, '자신'), ' 입니다.') Msg from emp
e left outer join emp m
on e.mgr = m.empno ;

2) RIGHT Outer Join
SELECT e.ename, d.deptno, d.dname FROM emp e
RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

3) FULL Outer Join : MySQL에서 지원 안 함, UNION 연산자 사용해야 함
SELECT * FROM outera
UNION
SELECT * FROM outerb;

15. database object(객체) 는?★★★
- TABLE
- VIEW
- INDEX
- SYNONYM(데이터베이스 객체에 대한 별칭을 부여한 객체)

16. 테이블 통합시 고려해야 할 점은?
- JOIN : ★하나 이상★의 테이블에서 서로 연관된 데이터를 찾아 질의를 던지는 것
- A, B 두 개의 테이블을 조인할 경우, 조인 조건에 맞지 않는 데이터도 표시하고 싶을 때 사용
- 하나 이상의 테이블에서 같은 이름의 컬럼이 나타나면, 반드시 컬럼이름 앞에 테이블 이름을 밝혀 주어야 함 ★
- join을 사용 안 했을 때의 잘못된 예
SELECT * FROM emp, dept; -- 두 테이블이 수평적으로 결합하여 Cartesian Product 발생
SELECT * FROM emp, dept

WHERE emp.deptno=dept.deptno; -- 불필요한 컴럼 값이 노출될 수 있음
SELECT empno, ename, sal, deptno, dname, loc FROM emp, dept

WHERE emp.deptno=dept.deptno; -- deptno 컬럼이 두 테이블에 존재해서 모호해짐

17. JDBC 4단계 순서와 api정리★★★
1) JDBC 4단계 과정
① DRIVER 로딩
② DB 서버와 연결
③ Prepared Statement 생성
④ Query 문을 실행

2) JDBC API
- SQL을 이용해 자바로 DB 프로그램 작성 할 수 있도록 지원함
- 관계형 데이터베이스 관리 시스템(RDBMS)에 SQL을 쉽게 보낼 수 있도록 지원

3) 연결 과정
① 하나의 드라이버로부터 여러개의  Connection 객체 만들수 있다.
① DB 서버와 연결 후, getConnection 함수로 Connection 생성
( DriverManager 클래스의 getConnection(String url, String user, String password) )
② prepareStatement 함수로 Prepared Statement 생성
( Connection 인터페이스의 prepareStatement(String sql) )
③ 서비스 - Connection :: 1 대 1
DB 연결은 connection을 받아오기 때문에
서비스 1개당 connection 1개를 만들어줘야 한다.

18. executeUpdate(), executeQuery() 차이점
1) executeQuery 메소드
- SELECT 등 하나의 ResultSet을 만드는 SQL문에서 사용

2) executeUpdate 메소드
- INSERT, UPDATE, DELETE 등 (DML), CREATE, DROP 등(DDL)문들을 실행하는데 사용

19. jdbc ResultSet 사용법
- 실행문을 위한 모든 메소드들은 하나가 오픈되어있다면, 호출된 Statement 객체의 현재 ResultSet을 닫아야한다.
- 이것은 Statement 객체를 다시 실행기전에 현재 ResultSet 객체의 어떠한 처리도 완결해야 할 필요가 있다는 것을 의미한다.
- 사용법
1) ResultSet rs = ps.executeUpdate();
2) if(rs.next()) {}, while(rs.next()) {} ( rs는 바로 직전 행을 가리키기 때문에 next를 무조건 해줘야 함 )
3) dto = new MemberDTO(rs.getString(1), rs.getString("password"), ...); ( ps, rs 인덱스는 1부터 )
4) rs.close();

20. DriverConnection 방법에 대해서 ★★★
1) DriverManager
① Class.forName("jdbc.mysql.driver");
② Connection conn = DriverManager.getConnection("jdbc.mysql.url", "jdbc.mysql.user", "jdbc.mysql.pass");
③ PreparedStatement ps = conn.prepareStatement(Query);
* Query 종류
- "jdbc.sql.insert"
- "jdbc.sql.delete"
- "jdbc.sql.update"
- "jdbc.sql.select"
- "jdbc.sql.selectAll"
④ ps.setString(1, "111"); / ps.setString(2, "222"); / ps.setString(3, "333");
⑤ ps.executeUpdate(); / ps.executeQuery()

2) DataSource
① String url = "jdbc:mysql://127.0.0.1:3306/데이터베이스이름?serverTimezone=UTC&useUnicode=yes&characterEncoding=UTF-8";
    Connection conn = DriverManager.getConnection(url, "root", "1234");
② String sql = "INSERT INTO member VALUES(?, ?, ...)";
    ps = conn.prepareStatement(sql);
③ ps.setString(1, "id"); / ps.setString(2, "password"); / ...
④ ps.executeUpdate();
⑤ closeAll(ps, conn);

21. executeQuery() 와 executeUpdate() 의 리턴타입과 그 의미★★★
1) executeQuery()
- ResultSet 객체 리턴
- ResultSet 객체에 SELECT문의 결과가 담겨 있음

2) executeUpdate()
- DML(INSERT, UPDATE, DELETE)을 사용할 경우 리턴값 : 작용한 열의 개수(갱신 카운트로 간주되는)를 나타내는 정수
- DDL(CREATE, DROP)을 사용할 경우 리턴값 : 0 을 리턴(DML을 실행 후 아무런 영향을 주지 않은 경우에도 0 을 리턴한다.)

22. Transaction
https://mommoo.tistory.com/62 : 트랜잭션 정의, 특징, 연산
1) COMMIT
- 자동 커밋 여부 확인 : SELECT @@autocommit; (0 = false, 1 = true)
- 자동 커밋 지정 :
SET autocommit = FALSE;
- 자동 커밋이 FALSE이면 COMMIT; 하기 전까지  해당 데이터에 라킹이 걸려서 다른 곳에선 접근 불가

2) Rollback
- 진행 중인 Transaction을 모두 취소하고 마지막 COMMIT 직 후로 돌아감

23. 정규화, 반정규화(p163, p193 참조, 수업시간 설명 참조)★★★

24. 정규화 되지 않은 디비의 문제점★★★

25. Entity 검증방법★★★

===============================================================================================
   <<<<<<<<<<<  예제풀이 :: 기출문제입니다.   >>>>>>>>>>>>

1. Emp라는 테이블에 name 컬럼의 데이터가 K로 시작되는 모든 데이터를 검색하려고 한다.  다음 지문의 밑줄에 들어갈 sql을 완성하세요
--> select name from emp where name _____________

2. Emp라는 테이블에 comm 컬럼의 값이 null이 아닌 모든 emp 정보를 검색하려고 한다.   다음 지문의 밑줄에 들어갈 sql을 완성하세요.
-->

3. Emp라는 테이블의 hiredate 컬럼에 입사일이 저장되어 있다.  각 직원들의 이름(ename)과 근속년수를 구하고자 한다.  알맞은 SQL 쿼리를 적으세요
-->

4. Emp라는 테이블의  job 컬럼에 업무가 저장되어 있다.  같은 업무를 하는 직원의 수가 4명 이상인 업무와 인원수를 출력하는 SQL을 작성 하려고 한다.  다음 지문의 밑줄에 들어갈 sql을 완성하세요.
--> select  job, count(job) from emp group by job having _______________________

5. 제약 조건에 대한 설명이 맞는 것을 고르시오. (3)
  1) 모든 제약조건은 column 레벨 또는 테이블 레벨에서 정의할 수 있다.
  2) Unique로 설정한 column의 값은 not null이며 데이터 중복을 할 수 없다.
  3) Foreign key로 설정한 column은 부모 테이블의 부모 키로 설정된 column의 데이터와 다른 데이터를 입력할 수 없다.  (Not Null 조건일 경우)
  4) Foreign key 옵션 생략하고 설정했을 경우 참조하고 있는 부모 테이블의 데이터가 삭제될 때 자동으로 참조하고 있는데 자식 데이터도 같이 삭제 된다.

6. Emp라는 테이블의  job 컬럼에 업무가 저장되어 있다.  같은 업무를 하는 직원의 수가 4명 이상인 업무와 인원수를 출력하는 SQL을 작성 하려고 한다.  다음 지문의 밑줄에 들어갈 sql을 완성하세요.
--> select  job, count(job) from emp group by  job having _______________________(정답 count(job) >= 4)

7. PK와 FK를 설정하는 이유는 ?
--> 데이터 무결성을 위해서

8. Emp 라는 직원 테이블에 이름(ename), 업무(job), 부서(deptno) 가 있을 때,
   부서 데이터가 30인 부서에는 없는 업무를 하는 직원의 이름과 업무를 검색하고자 한다.
   SQL문을 작성하세요

9. myview 라는 이름으로 view 가 생성되어 있다. 이 myview를 이용하여 결과 데이터를 보는 방법은?

10. 다음 중 DB Object가 아닌 것은 무엇인지 고르시오
   1) table  2)index  3) view  4)cursor

11. JDBC API를 이용하여 DB를 연동하려고 한다.  프로그래밍 순서가 맞는 것을 고르시오.

12. 테이블 통합을 고려해야 하는 경우가 아닌 것을 고르시오.(1)
--> 1)슈퍼엔티티와 서브엔티티사이에 서로 다른 컬럼이 많은 경우
    2)개별적으로 참조되는 경우가 거의 없는 테이블
    3)슈퍼엔티티와 서브엔티티가 자주 함께 접근되는 경우
    4)1:1 관계로 표현된 테이블

13. 디비모델링 정규화
14. 엔터티 검증방법
15. 정규화 되지 않은 디비의 문제점
16. jdbc 프로그래밍 과정
17. 트랜젝션 관련 작업 명령문

============================================================================