Record_Ing

CONCAT_WS()

둘 이상의 문자열이나 컬럼 값을 반복되는 구분자를 넣고 순서대로 합쳐서 반환해준다. (NULL을 포함해도 NULL로 반환하지 않는다. 

SELECT CONCAT_WS(구분자, 문자열1,문자열2,문자열3,,,)FROM 테이블명

# SELECT CONCAT_WS(',','AAA','BBB','CCC')
# 출력 : AAA,BBB,CCC

RPAD, LPAD

오른쪽, 왼쪽에 특정 문자를 원하는 자리수 만큼 넣기

SELECT LPAD('원본문자열','원하는자리수','채울문자열')

# SELECT LPAD('ABC',5,'0')
# 결과 : 00000ABC

COLLATE

Charater set은 텍스트 데이터를 어떤 방식으로 인코딩하여 저장할것인지 결정하는 값이다
collation은 정해진 character set에 의해 저장된 데이터들이 어떤 방식으로 정렬될지를 결정하는 옵션이다.
utf8mb4_general_ci : 정렬속도 향상을 위해 일반적으로 잘 사용하지 않는 문자를 정렬기준에서 제외한방법

ROUND

반올림

ROUND(수,반올림자리)

# ROUND(123.4567,2)
# 출력 : 123.46

IFNULL

검사할 필드가 null이라면 대체할 값을 반환하고, 아니면 필드 값 자체를 반환한다.

IFNULL(검사할컬럼명, 대체할값('null이다!')

# IFNULL(AGE, 대체할값('null이다!')
# AGE컬럼이 null일 경우 'null이다!' 반환

UNION ALL

조인처럼 데이터를 병합시킨다. 중복데이터도 포함하여 같이 출력한다. 
단, 조인은 컬럼이 추가되는 형식이고(옆으로연결), union은 데이터가 추가되는 형식이다(밑으로 연결)

GROUP BY : 같은 컬럼을 묶는다.

HAVING절 : GROUP BY절에서 조건을 줄 때 사용한다.

SELECT *
FROM SAMPLE_TABLE
WHERE ID = 'recording'
GROUP BY AGE,JOIN_DATE
HAVING COUNT(AGE)>=2; # 나이가 2이상인 컬럼의 수량을 보여준다.

ORDER BY : 기준으로 정렬한다. (오름차순)

WITH 

서브쿼리

with 임의이름 as (
	select 컬럼명
	from 테이블명
	where 조건
)
select 컬럼명
from 임의이름 
where 조건

SUBSTR

문자열을 추출하거나 자른다

SUBSTR(문자열str, 시작지점, 길이) 

# SUBSTR('안녕하십니까',1,3) 
# 결과: 안녕하

WHERE 1=1

항상 참이다, 아무런 조건 없이 모든 레코드를 선택한 것과 같은 결과를 반환한다.

주로 추가되는 조건절에서 AND나 OR 조건을 쉽게 추가하기 위해 사용한다.

IF : 조건문 (MYSQL사용)

**오라클에선 DECODE로 사용한다. DECODE(컬럼, 값, '조건이 일치할 경우의 VALUE','조건이 일치하지 않을 경우의 VALUE')

IF(조건,'조건이 일치할 경우의 VALUE','조건이 일치하지 않을 경우의 VALUE')
# IF(A.DDLN_YN = 'Y',B.EXPND_RSLTN_AMT,A.EXPND_RPT_AMT)

STR_TO_DATE()

형식 문자열에 날짜 및 시간부분이 모두 포함된 경우 DATETIME 값을 반환함

STR_TO_DATE('20240501', '%Y%m%d%H%i%s'); # 반환값 : 2024-05-01 10:35:02

MYSQL에서  NVL대신 사용할 수 있는 함수 : COALESCE 또는 IFNULL
COALESCE(A, 'A의 값이 없는 경우의 값')
IFNULL(A, 'A값이 NULL인 경우의 값')

ORACLE에선 TO_CHAR(ADD_MONTHS(TO_DATE(IN_INQIRE_STDR_YYMM, 'YYYY-MM'), -1), 'YYYY-MM')
MYSQL에선  DATE_FORMAT(DATE_SUB('2024-01',INTERVAL -1 MONTH),'%Y-%m')이렇게 사용 가능하다

DATE_FORMAT('날짜',형식)
DATE_SUB("날짜",INTERVAL -1(이전달) OR 1(다음달) MONTH)

OREDER BY 1,2

컬럼의 순서대로 정렬한다는 의미

SELECT AGE, NAME, GENDER
FROM ~~
ORDER BY 1,2 # =AGE,NAME이랑 같은 뜻이다.

DECODE('컬럼','값','TRUE값','ELSE IF값','TRUE값','ELSE값')으로도 사용할 수 있다.

이걸 MYSQL에서 사용시에는CASE WHEN문으로 사용 할 수 있다.

CASE
    WHEN '조건' THEN 'TRUE값'
    WHEN '조건' THEN 'TRUE값'
    ELSE 'ELSE값'
END AS 이름

셀렉트 옵션 선택시 값 (if문 활용)

<if test="searchSearch != null and !searchSearch.equals('')">   
	AND(#{searchSearch} = '1' AND SUBSTR(BG_YMD,1,4) LIKE CONCAT('%',#{searchVal},'%')   
	OR #{searchSearch} = '2' AND BIZ_NM LIKE CONCAT('%',#{searchVal},'%'))
</if>

-- <if test =만약 searchSearch가 null이 아니거나 값이 비어있지 않다면 밑에 sql문을 실행해라>
-- 만약 searchSearch값이 1이면 SUBSTR(BG_YMD,1,4) LIKE CONCAT('%',#{searchVal},'%') 실행
-- 만약 searchSearch값이 2이면 AND BIZ_NM LIKE CONCAT('%',#{searchVal},'%'))실행

기본키 생성 : 

ALTER TABLE 테이블명 add primary key (열이름);

기본키 삭제 :

ALTER TABLE 테이블명 DROP PRIMARY KEY;

Auto-increament 추가 :

ALTER TABEL 테이블명 MODIFY num INT NOT NULL AUTO_INCREMENT;

외래키 추가 :

alter table 추가할 테이블명 add constraint 왜래키 이름 foreign key (열 이름) REFERENCES 가져올 테이블(가져올열);

외래키 삭제 :

ALTER TABLE 테이블이름 DROP FOREIGN KEY 외래키이름;

ON DELETE CASCADE :

Alter table 자식테이블
Add foreign key(자식테이블 외래키 컬럼)
References 부모테이블(부모테이블 참조할 컬럼)
On delete cascade;

버전 확인 :

select version();

테이블 생성 : 

create table db명.table명 (
	컬럼1 int primary key auto_increment, #기본키+자동 증가 설정
    컬럼2 varchar(40) not null,
    컬럼3 date,
    
    primary key(컬럼1),
    foreign key(컬럼2) references 다른테이블명(컬럼a) #다른 테이블 외래키 참조
);

테이블 조회 : 

select * from 테이블명  # *은 전체를 나타낸다.
where 조건 설정  #조건에 맞는 데이터 출력
group by 컬럼명  #공통된 데이터를 묶어준다.
order by 컬럼명 desc  #데이터 정렬 desc는 내림차순 정렬.

테이블 이름 변경 : 

alter table 기존테이블명 rename 바꿀테이블명;

필드 추가 : 

alter table 테이블명 add 필드명 필드타입;

필드 삭제 :

alter table 테이블명 drop 필드명;

조인 :

select 컬럼명 from 테이블1 join 테이블2 on 테이블1.컬럼 = 테이블2.컬럼 where 조건

//예제
select r.email,h.hotel_num,h.hotel_title,r.check_in,r.check_out
 from hotel_inform h
 join reserve_inform r
 on r.hotel_num = h.hotel_num
 where email="recorder@gmail.com";

데이터 베이스 설계 과정 중 현실 세계의 개체(Entity)와 그들간의 관계를 추상화 하여 데이터 베이스 구조를 설계 하는 과정

데이터 베이스 생명 주기 

1. 요구사항 수집 및 분석 : 사용자들의 요구사항을 듣고 분석하여 데이터 베이스 구축의 범위를 정한다.

2. 설계 : 분석된 요구사항을 기초로 주요 개념과 업무 프로세스 등을 식별하고 (1.개념적 설계)

              사용하는 종류에 맞게 변환(2.논리적 설계) 후에,

              데이터 베이스 스키마를 도출 (3.물리적 설계)한다.

3. 구현 : 설계 단계에서 생성한 스키마를 실제 DBMS에 적용하여 테이블 및 관련 객체를 만든다.

4. 운영 : 구현된 데이터 베이스를 기반으로 소프트 웨어를 구축하여 서비스를 제공한다.

5. 감시 및 개선 : 데이터베이스 운영에 따른 시스템의 문제를 관찰하고 데이터베이스 자체의 문제점을 파악하여 개선한다.

개념적 모델링 (conceptual modeling)

- 요구 사항을 수집하고 분석한 결과를 토대로 업무의 핵심적인 개념을 구분하고 전체적인 뼈대를 만드는 과정 

논리적 모델링 (logical modeling)

- 개념적 모델링에서 만든 ER다이어그램을 사용하려는 DBMS에 맞게 매핑하여 실제 데이터베이스를 구현하기 위한 모델을 만드는 과정

물리적 모델링 (physical modeling)

- 작성된 논리적 모델을 실제 컴퓨터의 저장 장치에 저장하기 위한 물리적 구조를 정의하고 구현하는 과정

DBMS에서 데이터를 다루는 논리적인 작업의 단위

• 트랜잭션을 정의하는 이유
  • DB에서 데이터를 다룰때 장애가 일어날 때 데이터를 복구하는 작업의 단위가 된다.
  • DB에서 여러 작업이 동시에 같은 데이터를 다룰 때 작업을 서로 분리하는 단위가 된다.

• 트랜잭션은 전체가 수행되거나 또는 전혀 수행되지 않아야 한다. (all or nothing)
  ex) 은행 이체 업무

BEGIN  
1\. A계좌에서 10,000원을 인출하는 UPDATE문  
2\. B계좌에서 10,000원을 입금하는 UPDATE문  
END  

• 트랜잭션의 수행 과정

1. 트랜잭션 1을 생성한다
2. 트랜잭션 2를  생성한다
3. T1의 값을 DB에서 주기억장치 버퍼로 읽어온다
4. T2의 값을 DB에서 주기억장치 버퍼로 읽어온다
5. T1에서 도서 가경을 7100원으로 업데이트 한다
6. T2에서 도서 가격 +100원 값으로 업데이트 한다
7. T1값을 주기억장치 버퍼에서 DB에 기록한다 (COMMIT) 
8. T2값을 주기억장치 버퍼에서 DB에 기록한다 (COMMIT)

• 트랜잭션의 성질 (ACID)
  • 원자성 (Atomicity) : 트랜잭션에 포함된 작업은 전부 수행되거나 전부 수행되지 않아야한다. all or nothing
  • 일관성 (Consistency) : 트랜잭션을 수행하기 전이나 후나 데이터베이스는 항상 일관된 상태를 유지해야한다.
  • 고립성 (Isolation) : 수행 중인 트랜잭션에 다른 트랜잭션이 끼어들어 변경 중인 데이터값을 훼손하는 일이 없어야한다.
  • 지속성 (Durability) : 수행을 성공적으로 완료한 트랜잭션은 변경한 데이터를 영구히 저장해야한다.

• 동시성 제어 (concurrency control)
  • 트랜잭션이 동시에 수행될 때, 일관성을 해치지 않도록 트랜잭션의 데이터 접근을 제어하는 DBMS의 기능 

• 갱신 손실 (lost update)
  • 두개의 트랜잭션이 한 개의 데이터를 동시에 갱신(update)할 때 발생
  • 데이터베이스에서 절대 발생하면 안되는 현상
  • T2에서 잘못 된 결과를 만든 다음 T1의 갱신 작업을 무효화 하고 덧쓰기 작업을 수행하게 될 수 있다.
    이때 T1에서 갱신손실 문제 가 발생한다.

• 락(Lock)
  • 갱신손실 문제를 해결하려면 상대방 트랜잭션이 데이터를 사용하는지 여부를 알 수 있는 규칙이 필요하다
  • 데이터를 수정 중이라는 사실을 알리는 방법의 잠금장치이다
  • 공유락 (LS, shared lock) : 읽기
    배타락 (LX, exclusive lock) : 읽고 쓰기