DataBase (MariaDB/MySQL)

DataBase?

DataBase: Storage of Data

DBMS : Software manage/operate DataBase

Relational Database (a.k.a RDBMS) :

database that organizes data into tables with rows and columns, and establishes relationships between tables based on common fields, called keys

Features of DataBase

· Real Time Accessibility:

Databases provide immediate and constant access to data, allowing users to retrieve and modify data quickly and efficiently.

· Continuous Change:

Databases are designed to handle continuous changes to the data they store, such as updates, insertions, and deletions.

· Concurrent Sharing:

Databases allow multiple users or applications to access and manipulate data simultaneously, without interfering with each other.

· Reference By Content:

Databases use a unique identifier, such as a primary key, to reference data. This ensures that data is accessed accurately and consistently, regardless of its physical location within the database.

· Data Independence:

Database systems provide a layer of abstraction between the physical storage of data and the applications that use it, allowing for flexibility and easier maintenance. This means that changes to the database structure or organization can be made without affecting the applications that use it.

Setting MariaDB (MacOS)

[Mac Os]

1. Download MariaDB thru homeBrew

[homebrew install code]

$ /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

2.1 search & install mariadb in brew

$ brew search mariadb
$ brew install mariadb
$ brew search mariadb # the version i've installed will be marked

2.2 Move to dir Mariadb placed

$ cd /usr/local/Cellar/mariadb
$ cd /usr/local/Cellar/mariadb/ls #list it
$ cd 10.7.3/bin
$ sudo -i # admin mode
$ sudo ./mariadb-secure-installation   #./ <- current path

3. service status in brew

$ brew services info mariadb    #checking status
$ brew services stop mariadb    #stoping the service
$ brew services start mariadb   #starting the service
$ brew services restart mariadb #re-starting the service

4. Server access

$ cd /usr/local/var/mysql
$ mysql -u root -p 
$ select version();
$ show databases;
$ use 'dbname';
$ show tables;
$ select 'Host', 'User', 'Password' from 'tableName';

Setting MariaDB (Window)

[Window]

1. Download it a.zip file over(https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.1.0 ) and decompress it.

2. Open CMD w Admin (shift + CMD click)

Service Registration

> cd ./path where the .zip is decompressed/
> cd mariadb-10.6.7/bin
> mariadb-install-db.exe -S 'MariaDB' -p 'password'

Service Start/Stop

> sc start MariaDB
> sc stop MariaDB

> select version();
> show databases;
> use 'dbname';
> show tables;
> select 'Host', 'User', 'Password' from 'tableName';

Create DataBase Instance

· SQL is generally used to manage DBMS data

DDL: Data Definition Language
CREATE: create a new database, table, view, index, or others
ALTER: modify the structure of an existing database object
DROP: delete an existing database object
TRUNCATE: remove all data from a table, but keep the table structure

DML: Data Manupulation Language
INSERT: add new rows to a table
UPDATE: modify existing rows in a table
DELETE: remove existing rows from a table
SELECT: retrieve data from one or more tables based on specified criteria

DCL: Data Control Language
GRANT: grant privileges (e.g., SELECT, INSERT, UPDATE, DELETE) to a user or role
REVOKE: revoke privileges from a user or role
TRANSACTION: start a new transaction
COMMIT: commit changes made within a transaction to the db
ROLLBACK: undo changes made within a transaction
…

Data Type

Data type	Oracle	MySQL	SQL Server	PostgreSQL
Numeric	NUMBER FLOAT	INT FLOAT DOUBLE …	INT FLOAT …	INTEGER SMALLINT DOUBLE …
String	CHAR VARCHAR2	CHAR VARCHAR	CHAR VARCHAR	CHAR VARCHAR
Boolean	-	BIT	BIT	BOOLEAN
Date/Time	DATE TIMESTAMP	DATE TIME DATETIME TIMESTAMP YEAR	DATE TIME DATETIME DATETIME2 SMALLDATETIME	DATE TIME TIMESTAMP
Big Objects	LONG CLOB BLOB	TEXT MEDIUMTEXT LONGTEXT BLOB	TEXT BINARYM IMAGE	TEST

Table Join?

1. Inner Join:

Returns only the matching rows from both tables.

2. Full Join:

Returns all the rows from both tables, and where there are no matches, NULL values are returned.

3. Left Join:

Returns all the rows from the left table, and the matching rows from the right table. If there is no match, NULL values are returned for the right table columns.

4. Right Join:

Returns all the rows from the right table, and the matching rows from the left table. If there is no match, NULL values are returned for the left table columns.

# InnerJoin               # FullJoin
_______________          _______________
|left |*|right|          |left |*|right|
|table|*|table|          |table|*|table|
|     |*|     |          |*****|*|*****|
|     |*|     |          |*****|*|*****|
+-------------+          +-------------+

# LeftJoin               # RightJoin
_______________          _______________
|left |*|right|          |left |*|right|
|table|*|table|          |table|*|table|
|*****|*|     |          |     |*|*****|
|*****|*|     |          |     |*|*****|
+-------------+          +-------------+
                         

SQL Example

Table Creation Sample

|----------------|  CREATE TABLE MEMBER ( 
|   User-Name    |     USR_NM VARCHAR(50),  
|   User-Id      |     USR_ID VARCHAR(50),
|   User-Pw      |     USR_PW VARCHAR(50),
|   User-Age     |->   USR_AGE INT,
|   User-Dob     |     USR_DOB DATE,
|   Send-SMS     |     SMS_YN BIT,
| Registered-Date|     RG_DATE DATETIME
|________________|    )

|----------------|  CREATE TABLE PROMO_INFO ( 
| Promo-Start-DT |     START_DT DATETIME,  
|  Promo-End-DT  |->   END_DT DATETIME,  
|  Promo-Name    |     PROMO_NM VARCHAR(50),
|  Promo-Content |     PROMO_CONTENT TEXT,
|________________|    )

SQL Sample

#DATABASE DELETION
$ DROP DATABASE 'INSTANCE_NAME';

#USER CREATION
$ CREATE USER 'USER_NAME'@'LOCALHOST' IDENTIFIED BY 'PW';
$ CREATE USER 'USER_NAME'@'%' IDENTIFIED BY 'PW'; 

#GRANT 
$ USE 'DBNAME'
$ GRANT ALL PRIVILEGES ON 'DBNAME'.* TO 'USER_NAME'@'LOCALHOST' IDENTIFIED BY 'PW';
$ GRANT ALL PRIVILEGES ON 'DBNAME'.* TO 'USER_NAME'@'%' IDENTIFIED BY 'PW';

#FLUSH (SAVE)
$ FLUSH PRIVILEGES;

#DATA INSERTION 
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

#DATA INQUIRY
SELECT column1, column2, ...
FROM table_name
WHERE condition;

#DATA UPDATE (condition required)
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; #e.g-> email = 'bzhs@icloud.com';

#DATA DELETION (condition required)
DELETE FROM table_name
WHERE condition; #e.g-> email = 'bzhs@icloud.com'

#SET PRIMARY KEY
ALTER TABLE MEMBER ADD PRIMARY KEY (USR_ID);  

#TABLE INNER JOIN
SELECT M1.*,M2.*
FROM MEMBER AS M1
    JOIN MEMBER_2 AS M2 
        ON M2.MEMEBR_TYPE = M1.MEMBER_TYPE 
           AND M2.USR_ID =M1.USR.ID
;

#TABLE LEFT JOIN
SELECT M1.*,M2.*
FROM MEMBER AS M1
    LEFT JOIN MEMBER_2 AS M2 
             ON M2.MEMEBR_TYPE = M1.MEMBER_TYPE 
                AND M2.USR_ID =M1.USR.ID 
;

#TABLE RIGHT JOIN
SELECT M1.*,M2.*
FROM MEMBER AS M1
    LEFT JOIN MEMBER_2 AS M2 
             ON M2.MEMEBR_TYPE = M1.MEMBER_TYPE 
                AND M2.USR_ID =M1.USR.ID 
;

#TABLE FULL JOIN
SELECT M1.*,M2.*
FROM MEMBER AS M1
     JOIN MEMBER_2 AS M2 
    
;

DBMS Functions

1. Built-In Functions

# String Func

SELECT MEMEBER_TYPE,USER_ID,PASSWORD,NAME
    
    ,CONCAT(SUBSTRING(PASSWORD,1,2),'**') AS PASSWORD_MASKED

    ,LENGTH(PASSWORD) AS PASSWORD_LENGTH;

    ,CASE
        WHEN LENGTH(PASSWORD)>2 
            THEN CONCAT(SUBSTRING(PASSWORD,1,2),'**') ELSE ''
     END AS PASSWORD_MASKED

     ,DATE_ADD(STR_TO_DATE('20220321','%Y%M%D'),INTERVAL 1 MONTH)AS DT_DATE2

FROM MEMBER;

# Date Func
SELECT REGISTER_DATE
    .DATE_FORMAT(REGISTER_DATE,'%Y.%M.%D') AS DT_FORMAT
FROM MEMBER;

#2022-03-20 00:00:00 => 2022-03-01 , 2022-03-31
SELECT NOW()
    ,DATE_FORMAT(NOW(),'%Y.%M.01') AS START_DATE
    ,DATE_ADD(DATE_ADD(DATE_FORMAT(NOW(),'%Y.%M.%D'),INTERVAL 1 MONTH),INTERVAL -1 DAY) AS END_DATE
FROM DUAL;

2. CREATING FUNCTIONS

#SIMPLE MASKING FUNC
CREATE FUNCTION SF_PASSWORD(PASSWORD VARVHAR(255))
    RETURNS VARCHAR(255)
BEGIN
    RETURN '*****'
END;

# RETURN WILL BE -> *****
SELECT SF_PASSWORD(PASSWORD:'1234');

#REAL MASKING
CREATE FUNCTION SF_PASSWORD(PASSWORD VARVHAR(255))
    RETURNS VARCHAR(255)
BEGIN
    WHEN LENGTH(PASSWORD)>2 THEN CONCAT(SUBSTRING(PASSWORD,1,2).'**')
                            ELSE '****'
                        END;
END;

PROCEDURE

[kor]

기능	함수(Function)	프로시저(Procedure)
반환 값	반드시 반환 값이 존재	반환 값이 없을 수 있음(OUT 매개 변수로 반환 가능)
호출 방법	SELECT 문 또는 다른 함수 내에서 사용	명시적으로 호출
매개 변수	입력 매개 변수와 반환 값을 반환할 수 있는 OUTPUT 매개 변수를 가질 수 있음	입력 매개 변수와 OUTPUT 매개 변수를 가질 수 있음
사용 용도	값 계산, 검색 등의 작업에 사용	데이터베이스에서 일련의 작업을 수행하기 위해 사용
데이터 변경	데이터를 읽기만 하며, 일반적으로 데이터를 변경하지 않음	데이터를 읽거나 변경할 수 있음
트랜잭션	자동으로 트랜잭션을 커밋하지 않음	명시적으로 트랜잭션을 커밋 또는 롤백할 수 있음
예시	ABS(), CONCAT(), NOW()	INSERT, UPDATE, DELETE 작업 등

[eng]

Feature	Function	Procedure
Return Value	Must have a return value	May not have a return value (can return OUT parameter)
Invocation	Used in SELECT statement or other functions	Explicitly called
Parameters	Can have input parameters and output values returned through OUTPUT parameter	Can have input parameters and OUTPUT parameters
Usage	Used for tasks such as value calculation, searching, etc.	Used for performing a series of tasks in a database
Data Modification	Only reads data and typically does not modify data	Can read or modify data
Transaction	Does not automatically commit transactions	Can explicitly commit or rollback transactions
Examples	ABS(), CONCAT(), NOW()	INSERT, UPDATE, DELETE operations, etc.

#SIMPLE PROCEDURE CALLS SELECT

DELIMITER $$

CREATE PROCEDURE SP_SELECT_MEMBER()
BEGIN
    SELECT *
    FROM MEMBER;
END $$

DELIMITER ;

CALL SP_SELECT_MEMBER();

Trigger

Trigger :

a set of instructions that automatically executes in response to a specific event, such as inserting, updating or deleting data from a table

Event	OLD	NEW
INSERT	-	O
UPDATE	O	O
DELETE	O	-

#MEMBER_DETAIL_HISTORY TABLE CREATION
CREATE TABLE MEMBER_DETAIL_HISTORY
(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    MEMBER_TYPE VARCHAR(10),
    USER_ID VARCHAR(10),
    MOBILE_NO VARCHAR(20),
    UPDATE_DATE DATETIME,
);

UPDATE MEMBER_DETAIL
SET
    MOBILE_NO = '1111'
WHERE MEMBER_TYPE = 'EMAIL'
    AND USER_ID = 'TEST@NAVER.COM'
;

#INQUIRY
SELECT *
FROM MEMBER_DETAIL;

# TRIGGER
CREATE TRIGGER TG_MEMBER_MOBILE_NO_HISTORY
    BEFORE UPDATE ON MEMBER_DETAIL
    FOR EACH ROW
    BEGIN
        INSERT INTO MEMBER_DETIAL_HISTORY
        (MEMBER_TYPE,USER_ID,MOBILE_NO,UPDATE_DATE)
        VALUES
        (OLD.MEMBER_TYPE,OLD.USER_ID,OLD.MONILE_NO,NOW());
    END;

#INQUIRY
SELECT *
FROM MEMBER_DETAIL;

# MEMBER_DETAIL UPDATE
UPDATE MEMBER_DETAIL
SET
    MOBILE_NO = '010333'
WHERE MEMBER_TYPE = 'EMAIL'
    AND USER_ID = 'TEST@NAVER.COM'
;

Paging

DB
MySQL MariaDB	LIMIT
Oracle	ROWNUM
MSSQL	OFFSET FETCH

[Example]

SELECT *
FROM MEMEBR
LIMIT 0,10  ## -> 0-10
;

View Table

View Table

a virtual table in a database that is created by executing a SELECT statement and saved as a named query result. The view table acts like a regular table, allowing users to query and manipulate data as if it were stored in a physical table, but it does not store any data itself. Instead, it displays the result set of a stored SELECT statement, which can be used to simplify complex queries, provide a simplified interface to the database, or restrict access to certain data.

Pros	Description
Independence	Even if the table structure is changed, applications using the view do not need to be changed.
Convenience	Creating views for complex queries can simplify related queries.
Security	It is possible to hide columns that should not be explicitly displayed based on the user’s permissions.

CREATE VIEW V_MEMBER AS

SELECT M.MEMBER_TYPE , M.USER_ID,M.PW,M.NAME,
       MD.MOBILE_NO,MD.MARKETING_YN, MD.REGISTER_DATE
FROM MEMBER M 
    JOIN MEMBER2 MD ON MD.MEMBER_TYPE = M.MEMEBR_TYPE AND ME.USER_ID = M.USER_ID
;

SELECT *
FROM V_MEMBER
;

EROP VIEW V_MEMBER;

H2 DB

H2 DB는 간단히 활용해볼 수 있는 메모리/파일 관계형 DB이며 가볍고 빠르기때문에 개발 단계에 활용하거나 테스트코드에도 자주 활용된다!!

H2 DB 관련 설정 (필수는 아니나 편리하게 사용하기 위해)

spring:
    datasource:
        url: jdbc:h2:mem:test
        username: sa
        password:
        driverClassName: org.h2.Driver
    h2:
        console:
            enabled: true
    jpa:
        defer-datasource-initialization: true
        database-platform: H2
        hibernate:
            ddl-auto: create-drop
        open-in-view: false
        properties:
            hibernate:
                format_sql: true
                show_spl: true

옵션 설명

defer-datasource-initialization: 초기 데이터 입력을 진행 할 수 있도록 하는 옵션(이옵션이 없으면 테이블 생선 전 초기 데이터 입력을 하려고 하게 됨.) springboot 2.5.0 이상부터 필요한 옵션
hibernate.ddl-auto:create-drop:어플리케이션 기동 시점에 테이블 생성, 종료시점에 테이블 삭제(오직 개발에서만!!! 운영에선 안됨!!!)

Transaction - ACID

Atomic(원자성)

- All or Nothing, 모든 작업이 실행되거나 혹은 모두 실행되지 않아야 한다.
- 예 ) A계좌에서 B계좌로 송금 -> 함께 성공 or 함께 실패

Consistency(일관성)

- 모든 트랜잭션 종료된 후에는 DB의 제약조건을 모두 지키고 있는 상태가 되어야 한다.
- 예 ) 잔액 0원이상

Isolation(격리성)

- 트랜잭션은 다른 트랜잭션과 독립적으로 동작해야한다.
- A트랜잭션이 하는 일을 B트랜잭션은 모르게..
- READ_UNCOMMITED > READ_COMMITED > REPEATABLE_READ > SERIALIZABLE
- 일반적으로는 MYSQL InnoDB 기본값인 REPEATABLE_READ 많이 활용

Durability(지속성)

- commit을 하게 되면 지속(저장)이 된다. - DB저장이 실패하더라도 모든 로그를 남겨서 DB에 순차적으로 모두 반영되도록 한다

HyunsooZo's TIL

Et Cetera