SQL 정리 / SQLD 시험보면서 정리했던것 - 2
*Select 문 실행 순서 주의사항
+ From 에 정의되지 않은 테이블의 칼럼을 where, group by, having, order by, select 에 사용하면 오류
+ ODER By 절에는 Select 절에 나타나지 않은 From 절의 테이블 칼럼을 불러와 사용할 수 있다.
단, Distinct 를 지정하거나 Group By 절이 있거나 UNION 연산자가 있으면 열의 정의가 select 절에 표시되어야 한다.
+ 인라인뷰에 정의된 select의 칼럼을 메인쿼리에 사용가능하다
—> 미정의된 것은 에러남
—> 서브쿼리에 사용된 칼럼을 사용하면 계속 유지되는 것이 아니라 범위를 벗어나면 사용 불가능 (인라인뷰도 동일하다.)
+ GROP BY 절에 그룹핑 기준을 정하면
-> 그룹핑된 컬럼과 집계함수에 사용될 숫자형 데이터 칼럼을 새로만든다.
-> 개별데이터는 필요없으니 새로 만들지 않는다
-> GROUP BY 이후 수행되는 select와 order by 절에 개별데이터 쓰면 오류.
그룹핑 기준 외의 문자형식 칼럼은 정할 수 없음
* ROWMUM —> 오라클
+ 오라클은 순위의 N개의 ROW를 출력하려고 order과 where에 사용하는 경우가 많은데 오라클에서는 데이터를 추출한 뒤 정렬을 하기 때문에 순위에 따른 N개를 얻으려면 where 절에 ROWNUM 을 가져오는 것으로만은 안되고 inline View를 사용해서 from에 먼저 추출과 함께 정렬을 한 뒤 where 절에 rownum을 사용하면 순위의 N개 추출이 가능하다.
—> select name, sal from (select name, SAL FROM 테이블 ORDER By SAL DESC) WHERE ROWNUM <4 ;
* TOP() —> SQLserver
—> select TOP(2) namem, sal FROM 테이블 ORDER BY sal DESC,
상위 2개 내림차순으로 같은 조건 있어도 내림차순으로 자른다.
—> select TOP(2) with ties ename, sal FROM 테이블 ORDER BY sal DESC;
상위 2개 같은 순위가 있으면 같은 순위로 출력해보여준다 (공동1위, 공동2위…)
* CREATE 제약조건 생성시
CREATE Table 테이블명 (~~);
alter tavle__ADD constraint PK제약조건 (칼럼명);
(~, constraint PK이름 제약조건(칼럼명));
* JOIN (INNER JOIN, NATURAL JOIN, USING(조건절), ON 조건절, CROSS JOIN, Outer JOIN)
+ INNER JOIN
Select * from a INNER JOIN B ON A no = B no;
+ WHERE 절에 사용하는. 조인은 집합 연산자로 조인한건
+ 일반집합연산자
UNION 합집합(중복은1개만)
UNION ALL 합집합(중복도 표시)
INTERSECT 교집합
EXCEPT, MINUS 차집합
CROSS JOIN 공집합
+ 순수관계 연산자 : 관계 DB를 새롭게 구현
1. Select -> where
2. Project -> select
3,. Natural join -> 다양한 join
(Divide 안씀)
+FROM 절 JOIN 형태
+INNER JOIN
—> 조인조건에 동일한 값이 있는 행만 반환
—> USING, ON절 같은 조건절 필수 (SQL서버는 ON만)
+ USING(조건절)
—> 같은 칼럼명, 같은 데이터타입이어야 함
—> AS 별칭 사용불가, USING(S_ID)
+ ON 조건절
—> AS 별칭 반드시 사용
—> ON절과 where 절 분리
+ Natural join —> oracle(O) SQLs(X)
—> 두 테이블 간의 동일 이름 ‘모든칼럼’에 대해 EQUI JOIN 수행
—> USOING, ON, Where 절에 조인정의 할 수 없음.
+ CROSS JOIN
—> M*N건의 데이터 조합 ( where조건 사용이 가능하지면 그러면 inner join이 된다)
+ Outer join
—> Join 조건에 동일한 행이 없는 행도 반환 가능
—> USING, ON 반드시 사용
+ Left outer join / Right outer join / Full outer join (중복삭제 UNOIN과 동일 ->NULL사용)
+ EQUI JOIN ( ‘=’에 의해서만 수행) : 2개의. 테이블 간에 칼럼값들이 서로 정확하게 일치하는 경우 (PK FK기반 자주씀)
NON EQUI JOIN : 2개 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우 —>’=‘이 아닌 BETWEEN, >, <=등 연산자 사용
* SQL JOIN 작성된 것
Select * from a inner join b using(no);
Select * from a natural join b;
*select ENO, DNO FROM E, D ORDER BY ENO; (cartesian 카테시안곱 발생)
* JOIN 할 때 쓰는 집합연산자
—> select 절의 칼럼수가 동일해야 한다
—> select 동일 위치에 존재하는 칼럼의 데이터 타입이 상호화환 가능해야함
* select Action : cascade, set Null, set Default, Restrict
Cascad : 부모 삭제 -= 자식 같이 삭제
Restrict : 자식 테이블에 PK 값이 없을 경우만 부모 삭제
* insert Action : Automatic, set null, default, dependent
Automatic : 부모 PK 값이 없으면 자동 PK 생성 후 자식 입력
Set null : 부모 PK 가 없으면 자심 외부키 NULL처리
Default : 부모 PK가 없으면 자식 외부키 Default처리
Dependent : 부모 PK가 존재 할 때만 자식 입력 허용.
* 나장함수 설명
1. 함수의 입력 행수에 따라 단일행, 다중행으로 나눈다
2. 단일행 함수는 select, where order by, update 의 set 절에 사용 가능.
3. 1:M 관계에 조인될 경우 M 쪽에 단일행 함수 사용 가능.
4. 단일행 함수와 다중행 함수는 단일 값만을 반환함
* UNION, UNION ALL, INTERSECT, EXCEPT
UNION : 합집합, 중복은 하나의 행으로 -> 정렬발생
UNOIN ALL : 합집합이지만 중복핼도 그대로. 표시 -> 정렬발생안함 UNION. UNION ALL 중에 결과가. 같다면 정렬발생이 없는 UNOIN ALL 사용 권장
INTERSECT : 교집합 중복행은 하나로.
EXCEPT(MINUS) : 차집합
* ORDER BY 절에 계층형
—> ORDER BY SIBLINGS BY 칼럼명 DESC; —> siblings는 계층 형제끼리만 정렬하라고…
* 계층형 [oracle]
Start with 시작지점 지정 , 순방향 prior부모 —> 자식, 역방향 자식 —> prior부모, 루트 노드 레벨은 1
*ORDER BY 절에서의 NULL값의 크기
[오라클]
NULL이 가장 큰 값 DESC; 제일 앞에 위치
[SQLserver]
NULL이 가장 작은값 DESC; 제일 뒤에 위치
*계층형
+ SQL서버 계층형 질의문 CTE를 재귀 호출해 계층 구조 전개
+ SQL서버 계층형 질의문은 앵커 멤버를 실행 -> 기본결과 집합 만듬 -> 재귀멤버 지속실행
+ 오라클 계층형 질의문에서 where절을 모든 전개 이후 필터로 작용 (조건에 맞는 데이터만 추출) -> 추출하는데만 활용됨
+ 오라클의 계층형 질의문에서 PRIOR 키워드는 CONNECT BY와 SELECT WHERE 절에 사용가능하다
+ PRIOR 자식 = 부모 형태면 순방향 전개 가능.
*SELF JOIN
- 한 테이블 내에 두 컬럼의 연관관계가 있다.
- 식별을 위한 별칭을 사용해야 한다.
*서브쿼리 —> 서브쿼리에는 ORDER BY 사용하지 못함
+ 연관 서브쿼리는 서브쿼리 내에 메인쿼리의 컬럼이 사용된 서브쿼리
+ 서브쿼리는 단일행 ( = > >= < <= ) 또는 복수행 (IN Between 등) 비교 연산자와 사용
+ 서브쿼리는 SELECT, FROM, HAVING, ORDER BY 등에서 사용됨.
+ 서브쿼리의 결과가 복수행을 반환하면 IN,ALL,ANY 등 복수행 연산자와 사용(단일X)
—> 단 결과가 단일행 반환이면 쓸 수 있음.
+ 비연관 서브쿼리가 주로 메인쿼리에 값을 제공하기 위해 사용
+ 메인이 —> 서브쿼리에 결과 제공 / 서브가 —> 메인에 결과 제공 / 둘다 가능 실행 순서는 상황에 따라 다름.
*뷰 View
+ 독립성 : 테이블이 변경되어도 사용하는 응용 프로그램은 변하지 않음 (변경 안해도 됨)
+ 편리성 : 복잡한 질의를 뷰로 생성해 단순하게 작성함
+ 보안성 : 직원 급여 같은 숨기고 싶은 칼럼은 제거한채 생성 (보안 강화 목적)
+ 실제 데이터를 저장하고 있는 뷰를 생성하는 DBMS도 있다.
*그룹함수 ROLLUP, CUBE, GROPING SETS
+ 일반 그룹 함수로 동일한 결과 추출 가능
+ ROLLUP은 함수 인자로 주어진 칼럼 순서에 따라 다른 결과를 추출한다.
—>GROUPING SETS는 나열된 칼럼에 대해 계층구조 집계출력
+ CUBE, ROLLUP, GROPING SETS는 집계된 칼럼 이외의 GROP칼럼의 값으로 NULL반환
*ROLE
많은 DBMS 사용자에게 개별적으로 많은 권한을 부여하는 번거로움 해소용도
—> 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한 그룹
*FULL OUTER JOIN = A와 B가 있을때 = LEFT& RIGHT OUTER JOIN = UNION (중복제거)
FULL JOIN으로 사용가능하다.
*서브쿼리의 상호 연관 서브쿼리
+Correlated Subquery
메인쿼리의 값을 서브쿼리가 사용하고 서브쿼리의 값을 받아서 메인쿼리가 계산됨
Ex) Select 칼럼, 칼럼 FROM 테이블A WHERE A.칼럼 = (SELECT 1 FROM 테이블B WHERE A칼럼 = B칼럼);
—> 서브쿼리가 메인쿼리의 칼럼 A.칼럼을 사용했고 (메인쿼리 값 사용)
서브쿼리의 값을 받아서 메인쿼리가 계산되었다 WHERE절