2024-03-27 AI 코딩 TIL

SQLD자격증을 위해서 공부를 살살 배보는게 좋을 것 같습니다..

인터넷 사이트의 정리 요약 본은 보고 학습을 하는 방향으로 해보겠습니다.

[국가공인] SQL 개발자(SQLD) 가이드(2020.08.25.) : 네이버 카페 (naver.com)

[국가공인] SQL 개발자(SQLD) 가이드(2020.08.25.)

위에 블로그에서 좋은 데이터를 받을 수 있었습니다.

1. 데이터 모델링의 이해

데이터 모델링의 중요성 및 유의점

• 중복 -  같은시간 같은 데이터를 제공한다.
• 비유연성 - 사소한 업무변화에 데이터모델이 쉽게 변경이 되면 안됨
• 비일관성 - 신용 상태에 대한 갱신 없이 고객의 납부 이력 정보 갱신이 안됨

데이터 모델링은 개념적, 논리적, 물리적인 모델링을 지원?

데이터의 독립성 요소

• 외부 스키마 - 개개 사용자가 보는 개인적인 데이터 베이스 스키마
• 개념 스키마 -  모든 사용자 관점을 통합한 전체 DB
• 내부 스키마 - 물리적 장치에서 데이터가 실제적으로 저장됨

데이터의 독립성

• 논리적 독립성 - 개념 스키마가 변경되어도 외부 스키마에는 영향이 가지 않습니다.
• 물리적 독립성 - 내부 스키마가 변경되어도 외부/ 내부 스키마에는 영향이 가지 않습니다.

Mapping(맵핑, 사상) - 상호 독립적인 개념을 연결시켜주는 다리

데이터 모델링의 3요소 - 물건(things), 성격(attributes), 관계(relationships)

엔터티 - 집합
인스턴스 - 단수

데이터 모델 표기법 - 1976 피터첸이 ERM 개발

모델링의 특징으로 추상화, 단순화, 정확화가 있습니다.

ERD 작업순서 6가지 - 엔터티 그림 > 엔터티 배치 > 엔터티 관계설정 > 관계명 기술 > 관계의 참여도 기술 > 관계필수여부

좋은 데이터 모델의 요소

• 완전성 - 업무에 필요한 모든데이터가 정의됨
• 중복배제 - 하나의 DB내에 동일한 사실 하나씩만
• 업무규칙 - 많은 규칙을 사용자가 공유하도록 제공함
• 데이터 재사용 - 데이터가 독립적으로 설계됨
• 의사소통 - 엔터티, 서브타입, 속성, 관계등의 형태로 표현
• 통합성 - 동일한 데이터를 한번만 정의

엔터티 란? - 엄무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것, 보이지 않는 개념 포함

엔터티의 특징

• 반드시 해당 없무에서 필요하고 관리하고자 함
• 유일한 식별자에 의해 식별 가능
• 두 개 이상의 인스턴스의 집합
• 업무 프로세스에 의해 이용되어야 함
• 반드시 속성이 있어야 함
• 다른 엔터티와 최소 1개 이상의 관계가 있어야 함

엔터티의 분류

• 유형 - 물리적 형태 : 사원, 개념, 사건 엔터티
• 개념 - 개념적 정보 : 조직, 보험상품
• 사건 - 업무 수행시 발생 : 주문, 청구, 미납

발생 시점에 따른 분류 

• 기본 - 업무에 원래 존재하는 정보
• 중심 - 기본 엔터니로 부터 발생
• 행위 - 2개 이상의 부모엔터티로 부터 발생

엔터티의 명명 : 현업업무에서 사용하는 용어 사용, 약어 x, 단수명사 사용, 고유한 이름 사용, 생성의미대로 부여

2. SQL기본

DB - 특정 기업이나 조직 또는 개인의 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것

DBMS - 효율적인 데이터 관리 뿐만 아니라 예기치 데이터 손상을 피하고, 필요시 데이터 복구를 하기위한 SW

DB 발전 과정

• 1960 - 플로우차트 중심의 개발, 파일구조
• 1970 - DB 관리기법이 처음 태동, 계층-망형 DB등장
• 1980 - 관계형 DB상용화, ORACLE, SYBASE 등장
• 1990 - 객체 관계형 DB로 발전

SQL : DB 데이터 정의, 조작, 제어를 위한 언어

SQL 문장들의 종류

• DML 조작어- SELECT, INSERT, UPDATE, DELETE
• DDL 정의어- CREATE, ALTER, DROP, RENAME 
• DCL 데이터 제어어 (=TCL 트랜잭션 제어어)  - GRANT, REVOKE, COMMIT, ROLLBACK

테이블 - 데이터를 저장하는 객체이며 ROW(가로, 행), COLUMN(세로, 열)으로 구성되어 있으면 엑셀 방식과 비슷

정규화 - 데이터의 정합성 확보 및 데이터 입력, 수정, 삭제시의 이상현상을 방지하기 위함

기본키 - 테이블에 존제하는 각 행을 한 가지의 의미로 특정 할 수 있는 칼럼

외부키 - 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼

데이터 유형

• CHAR(s) : 고정 길이 문자열 정보 ‘AA’ = ‘AA ’
• VARCHAR(s) : 가변 길이 문자열 정보 ‘AA’ != ‘AA ’
• NUMERIC : 정수, 실수 등 숫자 정보
• DATE : 날짜와 시각 정보

CREATE TABLE 테이블이름 설정시 주의점

• 다른 테이블과 중복 X
• 테이블 내의 칼럼도 중복X
• 각 칼럼은 , 로 구분하고 ; 로 끝낸다.
• 각 칼럼은 데이터 유형을 지정해야 한다.
• 테이블명과 칼럼명은 반드시 문자로 시작해야한다. (A-Z,a-z,0-9,_,$,#만 사용 가능)
• DATETIME 데이터 유형은 별도로 크기 지정 X

제약 조건 넣기

• PRIMARY KEY(기본키) - 키본키 정의하기
• UNIQUE KEY(고유키) - 고유키 정의하기
• NOT NULL - NULL 값 사용 X
• CHECK - 입력 값 범위 제한하기
• FOREIGN KEY(외례키) - 외래키 정의하기

테이블 구조 변경하는 DDL 명령어

• ALTER TABLE: 테이블의 구조를 변경합니다. 열을 추가, 제거 또는 수정할 수 있습니다.
• ADD COLUMN: 테이블에 새로운 열을 추가합니다.
• DROP COLUMN: 테이블에서 열을 삭제합니다.
• MODIFY (Oracle) / ALTER (SQL Server): 특정 열의 데이터 유형, 기본값, NULL 허용 여부 등을 수정합니다.
• RENAME COLUMN: 테이블의 열 이름을 변경합니다.
• DROP CONSTRAINT: 테이블의 제약 조건을 삭제합니다.
• ADD CONSTRAINT: 테이블에 새로운 제약 조건을 추가합니다.
• RENAME: 테이블 이름을 변경합니다.
• DROP TABLE: 테이블을 삭제합니다. CASCADE CONSTRAINT 옵션을 사용하면 해당 테이블을 참조하는 제약 조건들도 함께 삭제됩니다.
• TRUNCATE TABLE: 테이블의 모든 행을 제거하고 저장 공간을 재사용합니다.ALTER TABLE "테이블명"

테이블 구조 변경하는 DML 명령어

DDL 명령어의 경우 실행시 AUTO COMMIT 하지만 
DML의 경우 COMMIT을 입력해야 한다. SQL Server의 경우 DML도 AUTO COMMIT

• INSERT INTO PLAYER (PLAYER) VALUES ('PJS');
  
  이 문장은 'PLAYER' 테이블에 새로운 행을 삽입합니다.
  이 삽입되는 행은 'PLAYER' 열에 'PJS'라는 값을 가지는 새로운 플레이어를 나타냅니다.
• UPDATE PLAYER SET BACK_NO = 60;
  
  이 문장은 'PLAYER' 테이블의 모든 행의 'BACK_NO' 열 값을 60으로 업데이트합니다.
  'BACK_NO' 열은 플레이어의 등번호를 나타내는 것으로 가정합니다.
  
  
• DELETE FROM PLAYER;
  
  이 문장은 'PLAYER' 테이블의 모든 행을 삭제합니다.
  즉, 플레이어 테이블의 모든 데이터가 제거됩니다.
  
  
• SELECT PLAYER_ID FROM PLAYER;
  
  이 문장은 'PLAYER' 테이블에서 모든 플레이어의 ID를 선택합니다.
  'PLAYER_ID' 열은 각 플레이어의 고유한 식별자를 나타내는 것으로 가정합니다.
  
  
• SELECT DISTINCT POSITION 시 구분값만 출력
  
  이 문장은 'PLAYER' 테이블에서 중복되지 않는 모든 포지션(시 구분값)을 선택합니다.
  'POSITION' 열은 플레이어의 포지션을 나타내는 것으로 가정합니다.
  
  
• SELECT PLAYER AS "선수명" FROM PLAYER;
  
  이 문장은 'PLAYER' 테이블에서 모든 플레이어의 이름을 선택하고, 선택된 열의 제목을 "선수명"으로 변경합니다.
  'PLAYER' 열은 플레이어의 이름을 나타내는 것으로 가정합니다.

와일드카드

각 특수기호가 의미하는 뜻이 있습니다 예를 들어 COUNT()괄호안에 들어가게 되면 나타나는 기능

• * : 모든
• % : 모든
• - : 한 글자

합성 연산자

문자와 문자 연결 : ||(Oracle), +(SQL Server)
SELECT PLAYER_NAME + ‘선수’ “정보”

테이블 구조 변경하는 TCL 명령어

트랜잭션 - 밀접히 관련되어 분리될 수 없는 1개 이상의 DB조작

• COMMIT - 올바르게 반영된 데이터를 DB에 반영시키는것 GIT COMMIT이랑 비슷하다.
• ROLLBACK - 트랜잭션 시작 이전의 상태로 되돌리기
• SAVEPOINT - 저장 지점 만들기

트랜잭션의 특성

• 원자성 - 트랜잭션으로 정의된 연산들은 모두 실행이되던가 실행이 되지 않아야 함
• 일관성 - 트랜잭션 실행 전 DB 내용이 잘못 되지 않는다면 실행 후에도 잘못 되지 않아야 함
• 고립성 - 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 결과가 달라져서는 안된다.
• 지속성 - 트랜잭션이 성공적으로 수행되면 DB내용은 영구적으로 보관이 된다.

연산자의 종류

• BETWEEN a AND b - a와 b사이의 값 인 경우
• a IN (list) - a 값이 리스트에 포함이 되어 있는 경우
• a NOT IN (list) - a 값이 리스트에 포함이 되어 있지 않은 경우
• 내용 LIKE '%비교 문자%' - 해당 내용이 포함이 되어있는 문자인 경우
• a IS NULL - a값이  NULL인 경우
• a IS NOT NULL - a값이  NULL이 아닌 경우

연산자의 우선 순위 - () > NOT > 비교연산자 > AND > OR

예시 )

SELECT PLAYER_NAME 선수명
FROM PLAYER
WHERE TEAM_ID = ‘K2’; -> 팀ID가 K2인 사람
WHERE TEAM_ID IN (‘K2’,‘K7’); -> K2,K7인 사람
WHERE HEIGHT BETWEEN 170 AND 180; -> 키가 170 ~ 180인 사람
WHERE POSITION IS NULL; -> 포지션 없는 사람

문자형 함수

• LOWER - 소문자로 변환
• UPPER - 대문자로 변환
• ASCII - 아스키 값으로 변환
• CHR/CHAR - 아스키값을 문자로 변환
• CONCAT - 문자 합치기
• SUBSTR/ SUBSTRING - 문자열 중에 특정위치 값만 가져오기 ( SUBSTR("ABCDE",1,4 ) -> 1번 인덱스부터 4개 문자
• LENGTH/LEN - 문자열의 길이를 숫자로 변환
• TRIM -  문자열에서 지정된 문자를 양쪽으로 제거 또는 왼쪽 오른쪽으로 선택해서 고를 수 있다.
• 예시)
  CONCAT(‘RDBMS’,‘ SQL’) -> ‘RDBMS SQL’
  SUBSTR(‘SQL Expert’,5,3) -> ‘Exp’
  LTRIM(‘xxxYYZZxYZ’,‘x’) -> ‘YYZZxYZ’
  RTRIM(‘XXYYzzXYzz’,‘z’) -> ‘XXYYzzXY’
  TRIM(‘x’ FROM ‘xxYYZZxYZxx’) -> ‘YYZZxYZ’

숫자형 함수

• SIGN(n) - 숫자데이터가 양수라면 1 / 음수라면 -1 / 0이라면 0을 반환하는 부호 표기 용
• MOD - 숫자 1을 2로 나누어 나머지를 반환 즉 짝수(0) 홀수(1) 표기
• CEIL/CEILING(n) - 크거나 같은 최소 정수 반환 즉 올림이라고 보면 된다.
• FLOOR(n) - 작거나 같은 최대 정수 즉 내림이다.
• ROUND(n, 소숫점 몇 번째) - 반올림 및 소수점 위치 정의
• TRUNC(n, 소숫점 몇 번쨰) - 반올림 없이 버림으로 소수점 위치 정의

날짜형 함수

• SYSDATE / GETDATE() - 현재 날짜와 시각 출력
• EXTRACT / DATEPART - 날짜에서 데이터를 출력
  SELECT EXTRACT(YEAR FROM '2022-03-27') AS extracted_year,
  SELECT DATEPART(YEAR, '2022-03-27') AS extracted_year,
• TO_NUMBER(TO_CHAR(d,'YYYY')) / YEAR(d) - 날짜 데이터 중에서 년도를 가져올 때

NULL 관련 함수

• NVL(식1, 식2) / ISNULL (식1, 식2) - 식1의 값이 NULL인 경우에 식2번으로 대신 출력
• NULLIF(식1, 식2) - 식1이 식2와 같으면 NULL을 아니면 식1번을 출력하는 함수
• COALESCE (식1, 식2) - 임의의 개수표현식에서 NULL이 아닌 값을 출력 만약에 모두 NULL이면 NULL출력
  ex)COALESCE(NULL,NULL,‘abc’) -> ‘abc’

집계 함수

여러 행들을 모아 그룹당 하나의 결과를 돌려주는 함수

• GROUP BY 절은 행들을 소그룹화 한다.
• SELECT, HAVING, ORDER BY 절에 사용가능
  • ALL - 기본 설정
  • DISTINCT - 중복 삭제
• COUNT(*) - NULL 포함 행의 수
• COUNT(표현식) - NULL 제외 행의 수
• SUM, AVG - NULL 제외 합계, 평균 연산
• STDDEV - 표준 편차
• VARIAN : 분산
• MAX, MIN - 최대 최소값

GRUOP BY, HAVING 특징

• GROUP BY 절을 통해 소그룹 기준 정한 후 SELECT 절에 집계 함수 이용
• 집계 함수의 통계정보는 NULL 제외
• GROUP BY 절에서는 ALIAS(별칭 = as) 사용불가
• 집계 함수는 WHERE절에는 올 수 없다.
• HAVING 절에는 집계 함수를 이용하여 조건 표기 가능
• HAVING 절은 일반적으로 GROUP BY 뒤에 위치

SEARCHED_CASE_EXPRESSION
   CASE WHEN LOC = ‘a’ THEN ‘b’
SIMPLE_CASE_EXPRESSION
   CASE LOC WHEN ‘a’ THEN ‘b’

이 2문장은 같은 의미이다.

ORDER BY 특징

• SQL 문장으로 조회 된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬(= 오름차순, 내림차순)
• ORDER BY 절에 칼럼면 대신 ALIAS명이나 칼럼 순서를 나타내는 정수도 가능
• DEFAULT 값으로 오름차순(ASC)이 적용 DESC를 넣어 내림차순도 가능하다.
• SQL 문장의 제일 마지막에 위치
• SELECT 절에서 정의하지 않은 칼럼도 사용가능하다.

팁 : ORACLE에서는 NULL이 가장 큰 값 SQL SERVER에서는 가장 작은 값으로 취급한다.

SELECT 문장 실행 순서

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY 순으로 동작

SELECT TOP(2) WITH TIES ENAME, SAL
FROM EMP
ORDER BY SAL DESC;
위는 급여가 높은 2명을 내림차순으로 출력하는데 같
은 급여를 받는 사원은 같이 출력한다(WITH TIES)

JOIN

두 개 이상의 테이블들을 연결 또는 결합을 하여 데이터를 출력할 때 사용
일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립, 특정 경우에는 PK,FK의 관계가 없이도 논리적인 값들의 연관만으로 성립가능 (5가지의 테이블을 합칠 때에는 총 4번의 JOIN이 필요[N-1]회)

참고로 여기서 말하는 PK는 주키(PRIMARY KEY) 를 뜻하고 FK는 외래키(FOREIGN KEY)를 뜻한다.

EQUI JOIN - 2개의 테이블간에 칼럼 값이 정확히 일치하는 경우에 사용 PK와 FK 의 관계를 기반으로 한다.

SELECT PLAYER.PLAYER_NAME
FROM PLAYER
이렇게 앞에 점을 찍어서 어느 테이블에 있는 칼럼인지 기술 해주어야 함 [JOIN을 했을 때]

NON EQUI JOIN - 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치 하지 않는 경우에 사용
‘=’ 연산자 처럼 특정 값이 아닌 BETWEEN, >, <= 등 연산자를 써서 범위로 표현해서 사용

SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HSAL;
위는 E의 SAL의 값을 S의 LOSAL과 HSAL 범위에서 찾는 것이다.

집합연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회 할 때 사용
SELECT절의 칼럼 수가 동일하고 동일한 위치에 존재하는 칼럼의 데이터 타입이 상호 호환이 가능할 때 사용 가능
즉... 같은 칼럼을 쓰고 있는 두 개의 테이블을 세로로 이어주는 것이라고 보면 된다.

일반 집합 연산자

(사용방법)
SELECT column1, column2 FROM table1
UNION / UNION ALL / INTERSECT / EXCEPT, MINUS
SELECT column1, column2 FROM table2;

	테이블 A		   테이블 B
| column1 | column2 | | column1 | column2 |
|---------|---------| |---------|---------|
|   A     |    1    | |   B     |    2    |
|   B     |    2    | |   C     |    3    |
|   C     |    3    | |   D     |    4    |

• UNION - 합집합[중복 행은 1개로 합치기]

| column1 | column2 |
|---------|---------|
|   A     |    1    |
|   B     |    2    |
|   C     |    3    |
|   D     |    4    |

• UNION ALL - 합집합[중복 행도 같이 표시]

| column1 | column2 |
|---------|---------|
|   A     |    1    |
|   B     |    2    |
|   C     |    3    |
|   B     |    2    |
|   C     |    3    |
|   D     |    4    |

• INTERSECT - 교집합

| column1 | column2 |
|---------|---------|
|   B     |    2    |
|   C     |    3    |

• EXCEPT, MINUS - 차집합

| column1 | column2 |
|---------|---------|
|   A     |    1    |

 

다른 방법

(사용방법)
SELECT * FROM table1
CROSS JOIN table2;

• CROSS JOIN - 곱집합 - 순열이라고 생각 하면 된다.

| table1.column1 | table1.column2 | table2.column1 | table2.column2 |
|----------------|----------------|----------------|----------------|
|       A        |       1        |       B        |       2        |
|       A        |       1        |       C        |       3        |
|       A        |       1        |       D        |       4        |
|       B        |       2        |       B        |       2        |
|       B        |       2        |       C        |       3        |
|       B        |       2        |       D        |       4        |
|       C        |       3        |       B        |       2        |
|       C        |       3        |       C        |       3        |
|       C        |       3        |       D        |       4        |

 

---

튜터님의 강의

이력서 작성 노하우

이력서에는 글을 위주로 그림을 넣고 싶다면 포트폴리오로 작성을 해서 만들어 주어야합니다.

가시적으로 보여줄 수 있는 노력을 보여주어야 합니다.

문제을 발견했을 때 해결방안과 결과를 적어서 기록을 남겨두는 것이 좋다

스킬을 설명할 때는 한번에 알 수 있게 표시

프론트 엔트, 백 엔드 상세하게 표시!

프로젝트는 계요, 기술, 스택! 표기 협업이면 어떻게 했는지?

이력서는 최대한 간략하게 만들고 부가설명은 포트폴리오에

기능을 사용했을 때에는 상세하게 표기

1번부터 4번까지는 수치로 표기가능한 내용을 넣고 마지막에 자기소개서를 넣는 경우가 많다.

팀프로젝트에서는 본인의 역할을 넣어주는것 잊지말자.

기능은 상세하게 느낀점은 마지막에 그리고 프로젝트의 서비스가 웹인지 어플리케이션인지 표기!

경력 학력은 윗 부분에 

가독성을 챙겨서 하는거 잊지말자

어떤 기술을 썻는지를 적고 어떻게 했는지는 포트폴리오에 적는다.