SQLD(SQL Developer) 정리 - 2과목 SQL 기본 및 활용

@notepad_jj2

츄르사려고 코딩하는집사입니다.

---

2과목

1. DML

- SELECT

- INSERT

- UPDATE

- DELETE

 

2. DCL

- GRANT

- REVOKE

 

3. DDL

- CREATE

- ALTER

- DROP

- RENAME

- TRUNCATE

 

4. TCL

- COMMIT

- ROLLBACK

- SAVEPOINT

 

5. 제약조건의 종류

- PRIMARY KEY

- FOREIGN KEY

- UNIQUE KEY

- NOT NULL

- CHECK

 

6. FOREIGN KEY

- NULL값을 가질 수 있음

 

7. 테이블의 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

 

8. 테이블 이름 변경

-- TABLE1에서 TABLE2로 변경
RENAME TABLE1 TO TABLE2

 

9. Delete Action

1) Cascade

- Master 삭제 시 Child 같이 삭제

 

2) Set Null

- Master 삭제 시 Child 해당 필드 Null

 

3) Set Default

- Master 삭제 시 Child 해당 필드 Default 값으로 설정

 

4) Restrict

- Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용

 

5) No Action

- 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

10. Insert Action

1) Automatic

- Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력

 

2) Set Null

- Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리

 

3) Set Default

- Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력

 

4) Dependent

- Master 테이블에 PK가 존재할 때만 Child 입력 허용

 

5) No Action

- 참조무결성을 위반하는 입력 액션을 취하지 않음

 

11. DELETE

- DELETE 문은 삭제하면 로그를 남김

- 반대적으로, TRUNCATE TABLE과 DROP TABLE은 로그를 남기지 않음

 

12. DROP과 TRUNCATE

1) DROP

- DROP 명령어는 테이블 정의 자체 삭제

 

2) TRUNCATE

- 테이블을 초기상태로 만듦.

- TRUNCATE는 UND를 위한 데이터를 생성하지 않기 때문에 DROP보다 데이터 삭제시 빠름

 

13. 데이터베이스 트랜잭션의 4가지 특성

1) 원자성(Atomicity)

- 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.

 

2) 일관성(Consistency)

- 트랜잭션이 실행 되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

 

3) 고립성(Isolation)

- 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

 

4) 지속성(Durability)

- 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

 

14. Dirty Read

- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것을 말한다.

 

15. Non-Repeatable Read

- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상

 

16. Phantom Read

- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타는 현상

 

17. Isolation

- 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

 

18. ORACLE에서는 DDL 문장 수행 후 자동으로 COMMIT 수행

 

19. SQL Server에서는 DDL 문장 수행 후 자동으로 COMMIT을 수행하지 않음

 

20. 트랜잭션

- 데이터베이스의 논리적 연산단위

- 분리될 수 없는 한 개 이상의 데이터베이스 조작

 

21. COMMIT

- 트랜잭션의 종료를 위한 대표적 명령어

- 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영

 

22. ROLLBACK

- 트랜잭션의 종료를 위한 대표적 명령어

- 데이터에 대한 변경사항을 모두 폐기하고 변경전의 상태로 되돌림.

 

23. 실제 데이터베이스 구축 시 참고되는 모델은 물리적 데이터 모델링

 

24. 개념 모델링에서 물리 모델링으로 가면서 더 구체적이며 개념 모델링이 가장 추상적이다.

 

25. 데이터 모델링의 3요소

1) Thing

2) Attributes

3) Relationship

 

26. 개념적 데이터 모델링

- 추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행

- 전사적 데이터 모델링, EA 수립 시 많이 이용됨

 

27. 하나의 속성에는 하나의 속성값을 가짐

- 하나 이상의 속성값을 가지려면 정규화가 필요

 

28. 한개의 엔티티는 두 개 이상의 인스턴스의 집합이어야 함

 

29. 한개의 엔티티는 두개 이상의 속성을 가짐

 

30. TRUNC

- 숫자를 소수 M자리에서 잘라서 리턴

- M의 DEFAULT는 0

 

31. FLOOR

- 숫자보다 작거나 같은 최대 정수를 리턴

 

32. CEIL/CEILING

- 숫자보다 크거나 같은 최소 정수를 리턴

 

33. ROUND

- 숫자를 소수 M자리에서 반올림하여 리턴

- M의 DEFAULT는 0

 

34. Hash Join

- 각 테이블에 INDEX가 반드시 필요한 것은 아님

- 일반적으로 작은 테이블을 메모리에 올리는 선행 테이블로 사용

- Hash Join은 Equal Join만 가능함

- 사전 소트 작업이 필요한 Join 알고리즘은 Sort Merge Join.

 

35. SQL 실행 순서

- FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

36. 결합함수

1) Oracle

- concat

- ||

 

2) SQL Server

- +

 

37. CONNECT_BY_ROOT

- ROOT 노드의 정보를 표시

 

38. SYS_CONNECT_BY_PATH

- 하위 레벨의 컬럼까지 모두 표시해줌

- 구분자 지정 가능

 

39. CONNECT_BY_ISCYCLE

- 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 존재하지 않으면 0

 

40. CONNECT_BY_ISLEAF

- 전개 과정에서 해당 데이터가 리프 데이터면 1, 아니면 0

 

41. JOIN

1) INNER JOIN

- A INNER JOIN B는 A 테이블과 B 테이블의 교집합만.

 

2) LEFT OUTER JOIN

- A LEFT OUTER JOIN B 는 A 테이블에 있는 것이 다 나와야 함

 

3) RIGHT OUTER JOIN

- A RIGHT OUTER JOIN B 는 B 테이블에 있는 것이 다 나와야 함

 

4) FULL OUTER JOIN

- A FULL OUTER JOIN B 는 A 합집합 B

 

5) CROSS JOIN

- A CROSS JOIN B 는 A 테이블의 갯수 X B테이블의 갯수

 

42. FROM 절에 사용되는 서브쿼리를 보통 inline view라고 한다.

 

43. 테이블 이름 변경

RENAME TABLE OLD_TABLE TO NEW_TABLE

 

44. INSERT ACTION

1) AUTOMATIC : MASTER 테이블에 PK가 없는 경우 MASTER PK를 생성 후 CHILD 입력

2) SET NULL : MASTER 테이블에 PK가 없는 경우 CHILD 외부키를 NULL 값으로 처리

3) SET DEFAULT : MASTER 테이블에 PK가 없는 경우 CHILD 외부키를 지정된 기본값으로 입력

4) DEPENDENT : MASTER 테이블에 PK가 존재할 때만 CHILD 입력 허용

5) NO ACTION : 참조무결성을 위반하는 입력 액션을 취하지 않음

 

45. DELETE ACTION

1) CASCADE : MASTER 삭제 시 CHILD 같이 삭제

2) SET NULL : MASTER 삭제 시 CHILD 해당 필드 NULL

3) SET DEFAULT : MASTER 삭제 시 CHILD 해당 필드 DEFAULT 값으로 설정

4) RESTRICT : CHILD 테이블에 PK가 없는 경우만 MASTER 삭제 허용

5) NO ACTION : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

46. INSERT SQL문

1) 삽입컬럼을 명시하지 않은 경우에는 VALUES에서 모든 컬럼에 대한 값을 다 입력해야 함

INSERT INTO TABLE VALUES(V1 ,V2, V3)

 

47. TRUNCATE TABLE

- 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다.

- 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 사용하면 된다.

- TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않아서 DELETE보다 빠르다.(동일 데이터 시)

- ROLLBACK 불가능

 

48. Phantom Read

- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

 

49. Non-Repeatable Read

- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상

 

50. Dirty Read

- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것을 말한다.

 

51. NL Join

- 선택도가 낮은(결과 행의 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리하다.

- OLTP 목록 처리 업무에 많이 사용.

 

52. Sort Merge Join

- 비 동등 Join(Not Equi Join)에서도 사용할 수 있다.

- 동등 Join(Equi Join)에서도 사용할 수 있다.

- 조인 컬럼에 적당한 인덱스가 없어서 NL 조인이 비효율적일 떄 사용할 수 있음

- 조인 조건의 인덱스의 유무에 따라 영향을 받지 않음

- Driving Table의 개념이 중요하지 않음.

- 데이터 집계 업무에서 많이 사용되는 JOIN 기법

 

53. Hash Join

- 행의 수가 작은 테이블을 선행 테이블로 선택하는 것이 유리하다.

- Sort Merge Join보다 일반적으로 더 우수한 성능을 보이지만, Join 대상 테이블이 Join Key 컬럼으로 정렬되어 있을 때는 Sort Merge Join이 더 우수한 성능을 가질 수 있다.

- 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있다.

- 한쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적이다.

- Sort Merge Join을 하기에는 두 테이블이 너무 커서 Sort 부하가 심할 때 Hash Join을 사용

- 데이터 집계 업무에서 많이 사용되는 JOIN 기법

 

54. SEMI JOIN

- 주로 EXISTS 절이 실행계획상에 주로 나타남.

 

55. 옵티마이저와 실행계획

- ORACLE의 규칙기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 SINGLE ROW BY ROWID 액세스 기법이다.

- 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계정보를 사용하여 실행계획을 수립하기 때문에 통계정보가 변경되면 SQL 실행계획이 달라짐

- ORALCE의 실행계획에 나타나는 기본적인 JOIN 기법은 NL JOIN, HASH JOIN, SORT MERGE JOIN 등이 있음

 

56. 인덱스

- 인덱스를 이용한 조회는 테이블 전체 스캔을 하여 조회를 하는 것이 더 빠를 수도 있고 인덱스를 이용한 조회가 더 빠를 수도 있다.

- 데이터 조회 목적에는 효과적이지만, INSERT, UPDATE, DELETE 작업에는 오히려 많은 부하를 줄 수도 있음

- SQL SERVER의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사하다

- 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.

- 인덱스를 구성하는 컬럼들의 순서는 데이터 조회 시 성능적인 관점에서 매우 중요한 역할을 한다.

- 인덱스를 구성하는 컬럼 이외의 데이터가 UPDATE 될 때는 인덱스로 인한 부하가 발생하지 않는다.

 

57. B-TREE INDEX

- 일치 및 범위 검색에 적절한 구조.

- 테이블 내의 데이터 중 10%이하의 데이터를 검색할 때 유리하다.

- 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다. 일반적으로 OLTP 시스템 환경에서 가장 많이 사용된다.

 

58. CLUSTERED INDEX

- 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다.

 

59. BITMAP INDEX

- 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다.

 

60. 외래키

- 테이블 생성 시 설정할 수 있음

- 외래키 값은 널 값을 가질 수 있음

- 한 테이블에 여러 개 존재할 수 있음

- 외래키 값은 참조 무결성 제약을 받을 수 있음

 

61. 제약 조건

1) Check

데이터베이스에서 데이터의 무결성을 유지하기 위해 테이블의 특정 컬럼에 설정하는 제약

 

2) 기본키

- 반드시 테이블 당 하나의 제약만을 정의

 

3) 외래키

- 테이블간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키가 참조하도록 생성

 

62. 테이블 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

63. 트랜잭션의 특성

1) 원자성(atomicity)

- 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.

 

2) 일관성(consistency)

- 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.

 

3) 고립성(isolation)

- 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.

 

4) 지속성(durability)

- 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

 

64. 트랜잭션

- 데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킴

 

65. NULLIF

- NULLIF(EXPR1, EXPR2) EXPR1이 EXPR2와 같으면 NULL, 같지 않으면 EXPR1 리턴

 

66. COALESCE

- 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다.

- 모든 표현식이 NULL이라면 NULL 리턴

 

67. 순수 관계 연산자

- SELECT

- PROJECT

- JOIN

- DIVIDE

 

68. 크로스 조인

- 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.

- 결과는 양쪽 집합의 M*N건의 데이터 조합이 발생한다.

 

69. Oracle 계층형 질의에서 루트 노드의 LEVEL 값은 1이다.

 

70. 셀프 조인(SELF JOIN)

- 동일 테이블 사이의 조인

 

71. PERCENT_RANK

- 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로하여 값이 아닌 행의 순서별 백분율을 구하는 Window Function

 

72. Nested Loop Join

- 프로그래밍에서 사용되는 중첩된 반복문과 유사한 방식으로 조인을 수행하는 방식

 

73. FIRST_VALUE() OVER

- 특정 그룹에서 특정 컬럼으로 정렬된 결과에서 첫번째 값을 구하는 Window Function

 

74. abs

- 절대값

 

75. FLOOR

- 버림

 

76. ROUND

- 반올림

 

77. TRUNC

- 정수 반환 소수점 버림

 

78. 뷰

- 복잡한 질의를 단순하게 작성할 수 있다.

- 해당 형태의 SQL문을 자주 사용할 때 이용하면 편리하게 사용할 수 있다.

- 사용자에게 정보를 감출 수 있다.

- 실제 데이터를 가지고 있지 않다.

 

79. ALTER TABLE ALTER COLUMN

 

80. ALTER TABLE DROP COLUMN

 

81. ALTER TABLE ADD

 

82. 

---