SQL 정리 / SQLD 시험보면서 정리했던것 - 1

순서대로 되어있지 않음. 나는 컬럼이라고 쓰는게 좋은데 자꾸 칼럼이라고 고치라고해서 컬럼과 칼럼이 섞여 써져있음ㅠ

 

* 관계형 데이트베이스 관계 연산

선택연산 Selection	조건에 맞는 행(튜플) 조회	선택-행
투영연산 Projection	조건에 맞는 컬럼(속성) 조회	투영-칼럼
결합연산 Join	공통 속성을 사용해 새 릴레이션 생성
나누기연산 Division	공통요소 추출 분모 릴레이션의 속성 삭제 후 중복행 제거

*ROW(로우 가로, 행) COLUMN(칼럼 세로, 열)

 

*On Delete Cascade -> 테이블 생성시 맨 뒤에 작성

사용 예) 테이블 A의 A1컬럼을 B테이블에서 참조하고 있다.

A1칼럼의 데이터 삭제 시에 연속적으로 B 테이블 데이터도 삭제하고 싶다

 

*DELET 문

DELETE FROM TABLE 테이블; 

조건을 입력하지 않으면 테이블 데이터 모두 삭제, 용량 초기화되지 X

 

*TRUNCATE  TABLE 테이블;

테이블 모두 삭제, 용량 초기화됨 (저장공간 재사용)

 

*DROP TABLE 테이블(Cascade constraint);

(cascade constraint)-> 참조되는 제약조건도 삭제

테이블을 삭제함 (구조,데이터)

SQL server에서는 Cascade constraint 사용 못함 참조되는 제약조건이 걸린 테이블부터 삭제해야 함

 

*DML 

DELETE, INSERT, SELECT, UPDATE,  (DISU-> 난 이렇게 외움 보는 사람도 맘대로 잘 읽히는 대로 외우기)

오라클은 COMMIT 입력해야 함, SQLserver는 AutoCOMMIT

*DDL

ALTER, CREATE, DROP, RENAME (ACDR)

*DCL

GRANT, REVOKE

*TCL

COMMIT, ROLLBACK

 

*테이블 구조 확인

오라클 : DESC 테이블명; 

SQLserver : exec sp_help 'dbo. 테이블명';

 

*와일드카드

 " * " (별) 모든, % 모든, - 한 글자

 

*문자와 문자 연결

오라클 || : "문자1" || "문자2" -> 문자1문자2

SQLserver + : "문자1" + "문자2" -> 문자1문자2

 

*문자와 문자 연결 함수

CONCAT

select concat('문자123', '문자321') FROM ~;

->문자123문자321

 

*데이터 유형

CHAR(s) 고정길이 문자열 정보 'AA' = 'AA ' ->>빈칸을 보지 않음

VARCHAR(s) 가변 길이 문자열. 정보 'AA' != 'AA '  ->>빈칸을 하나로 봄

NUMERIC : 정수, 실수 등 숫자 정보 DATE 날짜, 시각 정보

 

*CHAR를 이용한 문제

테이블 데이터 유형 CHAR(10)

COL1	COL2
1	' '
2	' '
3	' '

insert into T1 VALUES('1',' '); 이런식으로 입력된 값들

여기서 표 안에 ' '은 따옴표를 제외하고 빈칸

오라클 SELECT SUM(Length(COL1),SUM(Length(COl2));

SQLserver SELECT SUM(Length(COL1),SUM(Length(COl2));

답 : 오라클 (30, null) SQLserver (3, null)

오라클은  ' '빈칸을. 1로 인식한다

SQLserver 에서는 ' '빈칸을 0으로 인식한다

Length 함수가 스페이스를 1-0으로 인식한다.

-> 여기서 주의할 점 SUM은 COL1에 대한 값 합계가 아니라 Length와 Len에 대한 각 길이를 구하고 합친 것

 

*DEFAULT 관련 문제

DEFAULT는 설정한 시점부터 적용된다

테이블 유형 COL1 VARCHAR2(30) COL2 NUMBER

테이블에 입력

INSERT into TBL(COL1, COL2) VALUES('ABCE', NULL); -> ABCD null

INSERT into TBL(COL1, COL2) VALUES('BC', NULL); ->BC null

ALTER Table TBL Modfy COL2 DEFAULT 10;

INSERT into TBL(COL1, COL2) VALUES('XY', NULL); -> XY null

INSERT into TBL(COL1, COL2) VALUES('EXD') -> EXD 10

select sum(COL2) From TBL; ???

답 : 10

입력의 결괏값은 다 옆에 써둠

 

*ALTER TABLE 테이블명

[오라클] MODIFY (칼럼명 데이터유형 DEFAULT식  NOT NULL);

[SQLs] Alter (컬럼명 데이터유형 DEFAULT식 NOT NULL);

             ADD 컬럼명 데이터 유형;

 

---

* 칼럼 추가 변경 삭제 Alter table

 

+ 칼럼 추가

Alter table 테이블명 add ( 칼럼명 varchar(10) defult 'aaa');

  -> 맨뒤에 not null 추가 가능 (칼럼명 varchar(10 defult 'aaa' not null);

 

+ 칼럼 변경

- 칼럼의 데이터 타입 변경

[오라클]

alter table 테이블명 MODIFY (칼럼명 데이터타입(10));

[SQLserver]

alter table 테이블명 alter column 칼럼명 데이터타입(20) not null;

- 칼럼명 변경

alter table 테이블명 rename column 변경전칼럼명 to 새칼럼명;

- alter 이용한 테이블명 변경

alter table 변경전 테이블명 rename to 새테이블명;

 

+칼럼 삭제

alter table 테이블명 drop column 컬럼명;

alter table 테이블명 drop 컬럼명;      ->오라클

 

+칼럼에 제약조건 추가 constraint 

alter table 테이블명 add constraint 컬럼제약조건이름 제약조건;

사용 예시) PK FK 추가 예시

alter table TA add constraint PK_이름 primary key (컬럼1);

alter table TB add constraint FK_이름 Foreign key (컬럼2) references TA (컬럼1);

제약조건이 붙은 칼럼의 not null은 add가 아닌 modify 사용.

alter table 테이블명 modify(칼럼명 constraint 칼럼제약조건이름 not null);

 

+칼럼의 제약조건 삭제

alter table 테이블B drop constraint FK_이름;

 

*제약조건

primary key 기본키, unique key 고유키, not null null금지, check 입력 값 범위 제한, foreign key 외래키

 

---

 

*데이터 모델링 : 일정한 표기법에 의해 규칙을 가지고 표기하는 것 (모델을 만들어 가는것)

 + 데이터 모델링의 특징 : 현실세계를 추상화, 단순화, 명확화 하기 위해 일정한 표기법에 의해 표현하는 기법

추상화 : 현실 세계를 일정한 형식에 맞춰 표기법에 의해 표현

단순화 : 복잡한 현실세계를 제한된 표기법으로 쉽게 이해할 수 있게 하는 것

명확화 : 누구나 이해하기 쉽게 대상에 대한 애매모호함 제거, 정확하게 현상을 기술하는 것

 + 데이터 모델링을 하는 주요 이유

 -> 정보 시스템 구축의 대상이 되는 업무를 분석하고 설계한다. (모델링을 계획, 분석, 설계 할때)

 -> 구축/운영 단계에서 변경과 관리의 목적 (실제 DB를 생성해 개발 및 DB관리에 사용한다)

 

*select 문을 사용해 테이블 생성

[오라클]

Create table 생성테이블명 as select * from TBLa;

[SQLserver]

Create table 생성테이블명 from TBLa;

-> 컬럼별 데이터 유형 재정의 안해도 됨

-> 컬럼에 identity 적용했으면 identity 속성도 같이 적용됨

-> not null 을 제외한 제약조건은 사라짐

 

*칼럼 변경시 주의 사항

- 해당 칼럼의 크기를 늘릴 수는 있지만 줄일 수 는 없다.( null 행이 없으면 가능)

- 해당 칼럼의 null 값만 가지고 있으면 데이터 유형 변경할 수 있다.

- 해당 칼럼의 default 값을 변경하면 변경 시점 이후부터 적용

- not noull 제약조건은 null 값이 없을 때만 추가 할 수 있다.

 

* 테이블명변경

[오라클]

Rename 변경전테이블명 to 변경후 테이블명;

[sql]

Sp_rename ‘dbo.tableA’, ‘dbo.tableB’;

 

*테이블 삭제시

Drop table 테이블 구조 완전 삭제

Truncate table 구조유지 데이터 삭제

-> 이 둘은 auto commit 롤백 불가능

Delete 는 데이터만 삭제

-> 롤백 가능, 테이블데이터의 전체삭제 시 delete보다 truncate의 시스템 부하가 적다

단 truncate는 복구가 불가능하니 주의!

 

---

 

*Insert (DML)

+ Insert into 테이블명 (칼럼명); values(값들);

-> 생성기준칼럼순을 맞출 필요도 없음, notnull은 꼭 써야함, 정의하지 않으면 null 들어감

+ Insert into 테이블명 VALUES (전체 칼럼 값들… 값1, 값2, 값3)

-> columnlist를 쓰지 않아도 되지만 컬럼 순서대로 빠짐 없이 데이터 입력해야 함

+ 한번에 한건만 insert 할 수 있다.

+ CHAR, VARCHAR2 같은 문자 유형은 ‘’을 붙이고 숫자는 붙이지 않아야 한다.

 

*update 테이블명 set 수정할칼럼명 = 수정할 값

Ex) update table set no = 1;

 

*select (DML)

+DIstinct : 중복된 데이터 1건으로 처리함 -> select Distinct Position from 테이블며이

+칼럼 별칭 AS : ALIAS(별칭)을 이용해 컬럼명 변경해서 불러올 수 있음

-> select player_name AS 선수명, Position AS 위치 FROM 테이블명;

하면 player_name 칼럼과 Position 칼럼명은 선수명과 위치로 불러옴

 

*산술연자 () * % + -

*합성연자 문자와 문자연결

+concat(‘str1’,’str2’) —> ‘str1str2’

+컬럼과 문자 문자와문자 컬럼과 다른컬럼 연결한다.

오라클은 || 수직바

Sqlserver는 + 로 사용가능

 

*트랜잭션 (update, insert, delete)-> select update 등 배타적 lock요구는 대상이됨

+ 원자성 atomicity 모두 실행 성공이던지 실패던지

+ 일관성 consistency 실행전 내용이 잘못되지 않았다면 실행 후 잘못되면 안됨

+ 고립성 isolation 실행 도중 다른 트랜잭션 영향 받으면 안됨

+ 지속성 durability 성공적으로 실행되면 DB내용은 영구히 저장

 

* TCL commit, rollback

오라클, sqlserver DDL 오토커밋

오라클 -> DML 사용자 커밋

SQLserver -> DML 오토커밋

 

*where 연산자

+ 비교연산자 = > => < <=

+SQL 연산자

Between a and b —> A 와 B값 사이에있으면 됨 (a,b 포함)

In —> 리스트의 값중 하나랃 일치하면 됨

Like ‘비교문자열’ —> (%__) 문자열 형태가 일치하면됨

+ 부정 SQL 연산자

NOT Between a and b —> A와 B값 사이에 없다 (a,b 미포함)

NOT in, IS NOT NULL

+ 부정 비교연산자 != ^= <>

NOT 칼럼명 = —> ~와 같지 않다

NOT 칼럼명 > —> ~보다 크지 않다

+ 논리 연산자

AND —> 앞 조건 뒤 조건이 참이면 참 (동시에 만족)

OR —> 앞 뒤 조건 둘 중 하나만 참이면 참

NOT —> 뒤에오는 조건의 반대되는 결과를 돌려줌

+ 연산자 우선순위 (중요

1. ()괄호 2. 부정연산자 NOT 3. 비교, SQL 비교연산자 = <> 4. AND 5. OR

—> () NOT = AND OR 순

 

*in 연산자

Ex) JOB이 A이면서 20번부서에 속하거나 JOB이 B면서 30번부서에 속한다

Where (JOB, no) in ((‘a’,20), (‘b’,30));

Ex)JOB이 A이면서 20번 부서에 속하거나 부서가 20번 30번인 사람만 조회

Where JOB in (‘A’,’B’) AND no in (20, 30);

—> 두개의 결과값은 완전히 다르다

 

*NULL 연산자

Where 칼럼명 IS NULL;

 

---

 

* ROWNUM, TOP

+ ROWNUM 오라클에서 사용됨 원하는 만큼의 행을 가져옴

—> where rownum = 1; 또는 rownum <=1;

—> N건 가져올 때 ROWNUM <= n;

+ TOP SQLserver에서 사용됨 원하는 만큼의 행을 가져옴

—> TOP(3) 3건만 가져옴

—> TOP(3) with ties 공통순위도 가져옴 (order by 절이 지정되어야 사용이 가능함)

 

*내장함수 문자열

+ 오라클 SQL 서버 공통 함수 오라클/SQL서버

- LOWER(‘SQL Expert’) —> ‘sql expert' 소문자로

- UPPER(‘SQL Expert’)  —> ‘SQL EXPERT’ 대문자로

- ASCII(‘A’) —> 65     (ASCII 코드번호 숫자나 문자로)

- CHR(65) / CHAR(65) —> ‘A’ ASCII 문자나 숫자로

- CONCAT(‘ABC’, ‘DE’) / ‘ABC’ || ‘DE’ / ‘ABC’ + ‘DE’ —> ‘ABCDE’ 공백이 없으면 없음

- SUBSTR(‘SQL Expert’, 5, 3) / SUBSTRING() —> ‘Exp’ 5번째부터 3번째까지 표시 

- LENGTH(‘SQL Expert’) / LEN() —> 공백포함 길이 알려줌 (길이 : 10)

+오라클에서만 제공

- LTRIM(‘xxxYYZZxYZx’, ‘x’) —> ‘YYZZxYZx’ 왼쪽 ‘x’ 문자열 제거

- RTRIM(‘xYYZZxYZxxx’, ‘x’) —> ‘YZxZY’ 우측 ‘x’ 제거

- TRIM(‘x’ FROM ‘xxYZxZYxx’) —> ‘YZxZY’ 양측 ‘x’ 제거

=> 공백제거에 유용하다.

=> CHAR, VARCHAR 비교에 좋음

LTRIM(‘   xxYZ’) —> ‘xxYZ’

RTRIM(‘xxYZ   ’) —> ‘xxYZ’

TRIM(‘   xxYZ   ’) —> ‘xxYZ’

 

*숫자형 내장함수

- ABS(-15) —> 15 절대값

- MOD(7,3) —> 7% 3  -> 1 나머지값 리턴

- CEIL(38,123) / CEILING() —> 39

   CEILING(-38.123) —> 38

- FLOOR(38.123) —> 38

   FLOOR(-38.123) —> -39

- SIGN(-20) —> -1 양수 음수 구분

   SIGN(0) —> 0

   SIGN(+20) —> 1

- ROUND(38.5235, 3) —> 3번째까지 4번째 반올림 38.524

                (38.5235, 1) —> 38.5

                (38.5235, 0) —> 39

                (38.5235) —> 39 0이 디폴트

- TRUNC(38.5235,  3) —> 38.523 잘라서 버림

                (38.5235, 1) —> 38.5

                (38.5235, 0) —> 38

                (38.5235) —> 38

 

 

*NULL 함수

NVL(표1,표2) —> 표1 : NULL판단대상

ISNULL(표1,표2) —> 표1이 NULL이면 표2 반환 데이터 타입이 같아야함

NULLIF(표1,표2) —> 표1 = 표2면 NULL / 표1 != 표2면 표1 반환

COALESCE(표1, 표2, 표3) —> 표1 = NULL / 표2 = NULL / 표3 = ‘123’ => NULL이 아닌 첫번째 값 반환 ‘123’ 반환

—> 표1 = NULL 표2=NULL 표3=NULL이면 NULL 반환

 

*GROUP By, Having절 —> GROUP By는 행을 소그룹화 함

집계함수

COUNT(*) —> NULL 포함 행수

COUNT(칼럼) —> NULL 제외

SUM() AVG() —> (DISTINCT | AII) NULL 제외 합, 평균

MAX() MIN() —> Distinct AII 최대, 최소(문자, 날짜도 비교가능)

STDDEV() —> 표준편자

VARIAN() —> 분산

+ SELECT [DISTINCT] 칼럼 [AS명] FROM 테이블 [WHERE 조건식][GROUP By 칼럼이나 표현식][HAVING 그룹 조건식][ORDER By 칼럼 ASC DESC]

 

*CASE WHEN LOC = ‘a’ THEN ‘b’

같은 표현식

CASE LOC WHEN ‘a’ THEN ‘b’

 

*DECODE(표현식, 기준값1, 값1, 기준값2, 값2)

—> 표현식이 기준값1이면 값1 아니면 기준값2와 같으면 값2 아니면 디폴트

—> CASE WHEN A = ‘A’ THEN ‘B’

—> DECODE(표, 기준값1, 값1)   4번째 값이 없으면 NULL이 디폴트

 

*SELECT 실행순서

FROM 대상테이블 -> WHERE ( () NOT = AND OR ) 대상 데이터가 아닌 것 제거 -> GROUP By 행들을 소그룹화 -> HAVING 그룹 값 중 조건에 맞는것만 -> SELECT 데이터 값을 출력/계산 -> order by 데이터 정렬

---