14주차 월 1120(데이터베이스, MariaDB CRUD)

DBMS는 데이터베이스 관리 시스템(Database Management System)의 약어로, 데이터베이스를 관리하고 조작하는 소프트웨어 시스템입니다. DBMS는 데이터베이스를 효과적으로 관리하여 데이터의 안전성, 일관성, 공유성 등을 제공하고, 사용자와 응용 프로그램이 데이터에 접근할 수 있도록 인터페이스를 제공합니다.

다양한 DBMS가 있지만, 주로 사용되는 몇 가지 유형은 다음과 같습니다:

1. 관계형 데이터베이스 관리 시스템 (RDBMS):
   • 예시: MySQL, PostgreSQL, Oracle, Microsoft SQL Server
   • 데이터를 테이블로 구성하고, 테이블 간의 관계를 이용하여 데이터를 조직하는 방식을 채택합니다.
2. 비관계형 데이터베이스 관리 시스템 (NoSQL DBMS):
   • 예시: MongoDB, Cassandra, Redis
   • 관계형 데이터베이스 모델을 사용하지 않고, 다양한 형태로 데이터를 저장하는 방식을 채택합니다. 주로 대용량 및 분산 시스템에서 사용됩니다.
3. 계층형 데이터베이스 관리 시스템:
   • 예시: IMS (Information Management System)
   • 데이터를 트리 구조로 표현하며, 각 레코드가 다른 레코드에 대한 포인터를 가지고 있는 형태입니다.
4. 네트워크형 데이터베이스 관리 시스템:
   • 예시: IDMS (Integrated Database Management System)
   • 데이터 간의 네트워크 구조를 이용하여 관계를 표현합니다.

DBMS의 주요 기능은 다음과 같습니다:

• 데이터 정의 언어 (DDL): 데이터베이스의 구조를 정의하는 언어로, 테이블, 인덱스, 뷰 등을 정의합니다.
• 데이터 조작 언어 (DML): 데이터를 쿼리하고 조작하는데 사용되는 언어로, SELECT, INSERT, UPDATE, DELETE 등의 명령어를 포함합니다.
• 데이터 제어 언어 (DCL): 데이터에 대한 액세스 권한을 제어하는 언어로, GRANT, REVOKE 등의 명령어를 사용합니다.
• 데이터베이스 트랜잭션 관리: 여러 연산이 하나의 논리적 단위로 처리되도록 하는 트랜잭션을 관리합니다.
• 데이터 무결성 관리: 데이터베이스 내의 데이터가 정확하고 일관성 있게 유지되도록 하는 기능을 제공합니다.

DBMS는 데이터베이스를 효율적으로 관리하여 데이터의 무결성, 보안, 효율성을 유지하고, 응용 프로그램이 쉽게 데이터에 접근할 수 있도록 도와줍니다.

 

 

RDBMS(관계형 데이터베이스 관리 시스템)는 데이터를 테이블로 구성하고, 이 테이블 간의 관계를 이용하여 데이터를 조직하는 데이터베이스 시스템의 한 유형입니다. RDBMS는 데이터를 구조화하고 관리하기 위한 강력하고 유연한 방법을 제공하여 다양한 응용 프로그램에서 사용됩니다. 여기에서 RDBMS의 주요 특징과 개념을 설명하겠습니다:

1. 테이블:
   • RDBMS에서 데이터는 하나 이상의 테이블에 저장됩니다. 각 테이블은 특정 유형의 데이터를 저장하는 데 사용됩니다.
   • 테이블은 행(row)과 열(column)의 조합으로 구성되며, 각 행은 레코드(데이터 한 조각)를 나타냅니다.
2. 키(Key):
   • 각 테이블은 하나 이상의 열을 기반으로 하는 키를 가지고 있습니다. 기본 키(primary key)는 각 레코드를 고유하게 식별하는 역할을 합니다.
   • 외래 키(foreign key)는 두 테이블 간의 관계를 정의하는 데 사용됩니다.
3. 관계:
   • RDBMS는 여러 테이블 간의 관계를 지원합니다. 이러한 관계는 공통된 열을 기반으로 합니다.
   • 예를 들어, 한 테이블의 기본 키가 다른 테이블의 외래 키와 일치할 때, 두 테이블 간의 관계가 형성됩니다.
4. SQL(Structured Query Language):
   • SQL은 RDBMS와 상호 작용하기 위한 표준화된 언어입니다. SQL을 사용하여 데이터를 조회, 삽입, 갱신, 삭제할 수 있습니다.
   • 기본적인 SQL 명령어에는 SELECT, INSERT, UPDATE, DELETE 등이 포함됩니다.
5. 데이터 무결성:
   • RDBMS는 데이터 무결성을 지원하여 데이터의 정확성과 일관성을 보장합니다. 이를 위해 기본 키, 외래 키, 제약 조건 등을 사용합니다.
6. 트랜잭션 관리:
   • RDBMS는 트랜잭션을 관리하여 여러 작업이 하나의 논리적인 단위로 처리되도록 합니다. 트랜잭션은 일련의 데이터베이스 조작 명령어를 포함합니다.
7. 데이터 보안:
   • RDBMS는 데이터에 대한 액세스 권한을 관리하여 불법적인 데이터 접근을 방지합니다. 사용자에 대한 권한을 설정하고 제어할 수 있습니다.
8. 인덱스:
   • 인덱스는 테이블에서 특정 열을 기반으로 검색 속도를 향상시키는 데 사용됩니다. 인덱스는 데이터를 더 효율적으로 검색할 수 있게 도와줍니다.

대표적인 RDBMS에는 MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server 등이 있습니다. RDBMS는 많은 응용 프로그램 및 시스템에서 데이터를 구조화하고 관리하는 데 사용되며, 데이터베이스의 중심적인 역할을 수행합니다.

 

MariaDB의 포트는 3306이며

서버가 사용하는 포트는 일반적으로 클라이언트 애플리케이션과 데이터베이스 서버 간의 통신을 위해 할당됩니다. 포트는 네트워크 통신에서 특정 프로세스를 식별하는 데 사용됩니다.

클라이언트 애플리케이션은 이 포트를 사용하여 MariaDB 서버와 통신합니다. 포트는 컴퓨터에서 여러 네트워크 서비스 간의 충돌을 방지하기 위해 사용됩니다. 예를 들어, 하나의 서버에서 여러 개의 데이터베이스 서버가 실행 중일 때 각각의 데이터베이스 서버는 고유한 포트 번호를 사용하여 서로를 식별할 수 있습니다.

MariaDB가 사용하는 포트에 대한 이해는 네트워크 환경에서 MariaDB와 상호 작용하는 다른 응용 프로그램 및 서비스와의 통신을 관리하는 데 도움이 됩니다. 데이터베이스 서버의 포트가 열려 있어야 클라이언트가 해당 포트를 통해 서버에 접근할 수 있습니다.

 

------------------------------------------------MariaDB로 공부하기 ------------------------------------------------------------------------------

 

 

-- 주석
/* 
   문장 주석 
*/
-- db 테이블 조회해보기
SELECT *
FROM db;

SELECT *
FROM user;

-- 사용할 DB 지정하기
USE mysql;

-- 사용자 계정 및 패스워드 생성하기
-- 내부 사용자
CREATE user gjuser@localhost
IDENTIFIED BY 'dbdb';
-- 내 피시에서만 접속

-- 외부 사용자
CREATE user 'gjuser'@'%'
IDENTIFIED BY 'dbdb';
-- % 를 붙이면 외부에서도 접속가능

-- 사용자 삭제하기
-- DROP user gjuser@localhost;

-- 데이터베이스 생성하기
CREATE DATABASE gjdb;

-- 데이터베이스 삭제하기
-- DROP DATABASE gjdb;

-- 사용자 권한 부여하기
GRANT ALL privileges ON gjdb.* TO 'gjuser'@'localhost';

-- 사용자 권한 해제하기
-- REVOKE ALL PRIVILEGES ON gjdb.* FROM gjuser@'loaclhost';

-- 사용자 또는 DB 또는 권한 처리 후에는 마지막 한번 아래 실행
FLUSH PRIVILEGES;

위 처럼 작성하면

gjdb에 접속할 수 있게된다. 

 

* 사용자, DB, 권한부여 후 할일
  - 사용자로 접속
  - Table(테이블)을 설계(DB 설계라고 칭합니다.)
     => 테이블을 어떻게 만들지 모델링
     => 데이터를 저장할 수 있는 공간 : 테이블
     => 테이블 내에 데이터를 저장하는 원소값 : 컬럼(열)
     => 테이블 하나를 만들기 위해서는 컬럼(열)이 하나 이상 존재해야 합니다.
     => 컬럼은 타입(또는 길이와 함께)으로 정의됩니다.

 

 

CHAR와 VARCHAR는 둘 다 문자열 데이터를 저장하는 데 사용되는 데이터 유형이지만 몇 가지 중요한 차이점이 있습니다.

1. 저장 방식:
   • CHAR: 고정 길이 문자열입니다. 정의된 길이만큼의 공간을 항상 차지하며, 저장하지 않은 부분은 공백으로 채워집니다. 예를 들어, CHAR(10)은 항상 10자리 공간을 차지합니다.
   • VARCHAR: 가변 길이 문자열입니다. 실제 데이터의 길이에 따라 필요한만큼의 공간을 차지하며, 저장된 데이터의 길이에 따라 저장 공간이 동적으로 조정됩니다.
2. 저장 공간 사용:
   • CHAR: 정의된 길이에 관계없이 항상 고정된 길이의 공간을 사용합니다. 따라서 공간 낭비가 발생할 수 있습니다.
   • VARCHAR: 저장된 데이터의 실제 길이에 따라 저장 공간이 동적으로 조정되므로, 길이가 가변적인 경우에는 더 효율적으로 공간을 사용할 수 있습니다.
3. 저장 및 검색 성능:
   • CHAR: 고정 길이로 인해 검색 시에는 일정한 성능을 제공할 수 있지만, 저장 및 전송할 때에는 길이가 정해진 만큼의 데이터를 전송하므로 불필요한 공간과 대역폭을 사용할 수 있습니다.
   • VARCHAR: 가변 길이로 인해 저장 및 전송 시에는 실제 데이터만큼의 공간을 사용하므로 효율적으로 대역폭을 사용할 수 있습니다. 그러나 검색 시에는 길이가 가변적이기 때문에 성능의 일정성은 CHAR에 비해 상대적으로 낮을 수 있습니다.
4. 인덱스 및 검색:
   • CHAR과 VARCHAR 모두 문자열을 인덱싱할 수 있습니다. 그러나 CHAR의 경우 길이가 일정하므로 일반적으로 더 빠른 검색 속도를 제공할 수 있습니다.

따라서 데이터의 특성과 사용 사례에 따라 CHAR 또는 VARCHAR 중 어떤 것을 선택할지 결정해야 합니다. 일반적으로 가변 길이 데이터에는 VARCHAR를 사용하는 것이 효율적입니다.

 

데이터베이스 정규화는 데이터베이스 설계를 최적화하여 데이터 중복을 최소화하고 데이터 무결성을 보장하는 프로세스입니다. 정규화는 테이블을 작은 덩어리로 나누어 각각의 테이블이 특정 종류의 데이터만 포함하도록 하는데 목적이 있습니다. 이를 통해 데이터의 일관성과 무결성을 유지하면서 중복을 줄이고 효율적인 검색을 가능케 합니다. 여러 단계로 나누어진 정규화 과정이 있습니다.

 

char : 코드성 데이터 char(4) >> 0001은 a 0002는 b라는 규칙성을 가지고 만들경우 사용

왜 int로 안쓰고 char를 썻을까?

int는 0001 > 1로 바뀜

 

프라이머리키는 대부분 int 아니면 char를 쓴다.

int는 증가값 > 생각하고싶지 않고 증가값을 올릴때 // ex) 게시글 같은 경우

char 는 생각하여 만들 때

ex)

Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (1, 'P101','컴퓨터 제품');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (2, 'P102','전자 제품');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (3, 'P201','여성 케쥬얼');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (4, 'P202','남성 케쥬얼');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (5, 'P301','피혁 잡화');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (6, 'P302','화장품');

....

1. 제 1 정규화 (1NF):

목적: 중복을 최소화하고, 각 열이 원자값(Atomic Value)만을 포함하도록 합니다.

• 각 컬럼은 원자적인 값만을 가져야 합니다. 다시 말해, 각 셀에는 하나의 값만 있어야 합니다.
• 중복된 데이터를 최소화하기 위해 테이블을 세분화합니다.

2. 제 2 정규화 (2NF):

목적: 부분 함수 종속성을 제거하여 기본키에 속하지 않는 속성들 간의 종속성을 제거합니다.

• 제 1 정규화를 만족하면서, 기본키에 완전히 종속되지 않는 속성들을 제거하여 테이블을 정규화합니다.
• 이를 위해 부분 함수 종속성이 없도록 테이블을 재구성합니다.

3. 제 3 정규화 (3NF):

목적: 이행적 함수 종속성을 제거하여 모든 속성이 기본키에 대해서만 종속되도록 합니다.

• 제 2 정규화를 만족하면서, 이행적 함수 종속성을 제거합니다.
• 테이블 간의 종속성을 최소화하고, 데이터 중복을 방지합니다.

추가적인 정규화 단계:

• BCNF (Boyce-Codd Normal Form): 3NF를 만족하면서 후보키가 아닌 모든 속성이 기본키에 대해 완전 함수 종속인 경우를 말합니다.
• 4NF (Fourth Normal Form): 다치 종속성을 제거합니다.
• 5NF (Fifth Normal Form): 조인 종속성을 제거합니다.
• 그 이상의 정규화 단계도 존재합니다.

 

데이터베이스 테이블 

 - 제약조건 : Constraint 

 - not null : 널을 허용하지 않겠따 ! 

 

   Create Table lprod ( 
   lprod_id int  not null, -- 순번 
   lprod_gu char(4) not null, -- 상품분류코드
   lprod_nm varchar(40) not null, -- 상품분류명
  Constraint lprod_pk Primary Key (lprod_gu)

                                                     -- 프라이머리 키로 지정
);

 

Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (1, 'P101','컴퓨터 제품');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (2, 'P102','전자 제품');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (3, 'P201','여성 케쥬얼');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (4, 'P202','남성 케쥬얼');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (5, 'P301','피혁 잡화');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (6, 'P302','화장품');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (7, 'P401','음반/CD');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (8, 'P402','도서');
Insert Into lprod (lprod_id, lprod_gu, lprod_nm ) Values (9, 'P403','문구류');

                      -- 테이블 만들때 컬럼순서를 보편적으로 따름

 

Create Table buyer
(  
   buyer_id            char(6) not null  ,     -- 거래처코드 
   buyer_name       varchar(40) not null,    -- 거래처명
   buyer_lgu           char(4)not null,        -- 취급상품대분류
   buyer_bank         varchar(40),             -- 은행
   buyer_bankno       varchar(40),             -- 계좌번호
   buyer_bankname    varchar(10),             -- 예금주
   buyer_zip           char(7),                 -- 우편번호
   buyer_add1          varchar(60),             -- 주소1
   buyer_add2          varchar(50),             -- 주소2
   buyer_comtel        varchar(14) not null,    -- 전화번호       
   buyer_fax           varchar(20) not null,     -- fax번호 
   buyer_mail varchar(40), 
   buyer_charger varchar(10),
   buyer_telext varchar(2),
   Constraint buyer_pk primary key (buyer_id),
   Constraint buyer_for1 foreign key (buyer_lgu)
                           references lprod(lprod_gu),
   Constraint check_phone 
               Check ( buyer_telext like '[0-9][0-9]')                           
);

 

Constraint 프라이머리키 buyer_id  

Constraint 포링키 buyer_lgu  

(부모값이 있으면 들어가고 없으면 안들어간다.)

                          references (참조) lprod(lprod_gu) (부모를 참조한다)

                                                       테이블(컬럼명)// PK

 

-- 자식 쪽의 컬럼은 FK라고 지정하고 부모쪽의 PK를 참조!!

 

   Constraint check_phone 
               Check ( buyer_telext like '[0-9][0-9]')     

정규식 표현

 

CREATE TABLE  cart
(
   cart_member      varchar(15)  NOT NULL,         -- 회원ID
   cart_no          char(13)     NOT NULL,            -- 주문번호
   cart_prod        varchar(30)  NOT NULL,         -- 상품코드
   cart_qty         int          NOT NULL,                -- 수량
   Constraint pk_cart Primary Key (cart_no,cart_prod),
   Constraint fr_cart_member Foreign Key (cart_member) References member(mem_id),
   Constraint fr_cart_prod Foreign Key (cart_prod)   References prod(prod_id)
);

PK가 두개?

Constraint pk_cart Primary Key (cart_no,cart_prod)

복합키 : 둘다 동시에 같은게 있으면 안돼!! 

 

- COMMIT;

-- 데이터에 대한 입력/ 수정/ 삭제 시에는 commit 수행
-- 만약 입력/ 수정/ 삭제를 되돌리려면, rollback 수행
-- commit 이후에는 rollback이 안됩니다. 
-- mysql or mariadb는 자동 commit 됩니다. (tool에서)
-- oracle은 항상 commit 해야합니다.(tool에서)

-- 데이터 입력
/*
INSERT INTO 테이블명 (컬럼명1, ...gjdb컬럼명N)
  VALUES (값1, ... 값M)
*/

-- 데이터 수정
/*
Upadte 테이블명
Set 수정할컬럼명1 = 수정할값,
수정할컬럼명2 = 수정할값
Where 조건 제시...(무조건)
*/

-- 데이터 삭제
/*
Delete From 테이블명
Where 삭제 조건 제시..(무조건)
*/

-- 조회
/*
Select 조회할컬럼1, 조회할컬럼2...
From 테이블명
*/

 

-- 테이블 삭제 순서
-- buyprod > cart > prod > buyer > lprod > member
-- 자식 테이블을  먼저 삭제 후 부모 테이블을 삭제해야한다.

-- 데이터 입력
/*
INSERT INTO 테이블명 (컬럼명1, ...gjdb컬럼명N)
  VALUES (값1, ... 값M)
*/

-- 데이터 수정
/*
Upadte 테이블명
Set 수정할컬럼명1 = 수정할값,
수정할컬럼명2 = 수정할값
Where 조건 제시...(무조건)
*/

-- 데이터 삭제
/*
Delete From 테이블명
Where 삭제 조건 제시..(무조건)
*/

-- 조회
/*
Select 조회할컬럼1, 조회할컬럼2...
From 테이블명
*/

-- 회원정보 전체 조회하기
-- 회원정보 테이블 : member
-- 전체조회 : 모든 컬럼의 데이터를 조회하라는 의미
SELECT *
FROM member;
-- select 찾겠다 from 어디에서 member에서 * 전체컬럼을

-- 나머지 모든 테이블의 모든 정보 각각 조회하기
SELECT *
FROM buyer;

SELECT *
FROM buyprod;

SELECT *
FROM prod;

SELECT *
FROM cart;

-- 회원중에 아이디가 a001인 회원의 이름을 조회하기
-- 컴파일(해석)순서
-- select > from member > where 조건들 > 조회할 컬럼
SELECT mem_name
FROM member
-- 조건 처리하기
WHERE mem_id = 'a001';
-- select 찾는다 from 어디서 member에서 id가 a001의 name을

-- 상품분류정보에서 상품분류코드가 p201인 데이터 조회하기
-- 모든 데이터 조회...
SELECT *
FROM lprod
WHERE lprod_gu = 'P201';
-- 값은 대소문자를 가린다. / 컬럼명은 안 가림

-- 상품분류코드가 P201에 대해서
-- 상품분류명을 향수로 수정해주세요
-- 수정은 특정 조건에 맞는  행의 특정 값이 수정됩니다.
UPDATE lprod
SET lprod_nm = '향수'
WHERE lprod_gu = 'P201';

-- 상품분류코드가 p201인 데이터를 삭제하기
-- 삭제는 조건에 맞는 행이 삭제됨
-- 자식 테이블에서 참조를 하고 있기 때문에 삭제 불가..
DELETE From lprod
WHERE lprod_gu = 'P201';

-- 상품 판매가격이 1000원 이상인 상품들의 상품명
-- 상품 코드, 상품명을 조회하기
SELECT prod_id, prod_name
FROM prod
WHERE Prod_sale >= 1000;
-- 프로그램은 왼쪽을 기준으로 한다.

-- 상품 소비자가격이 1000원 이상이고
-- 판매가격이 2000원 이상인
-- 상품코드, 상품명을 조회해 주세요
SELECT prod_id, prod_name
FROM prod
WHERE Prod_price >= 1000 
And Prod_sale >= 2000;