Maria DB
마리아 DB 설정
마리아 DB 11.0.2 설치
- Port 번호는 3306
Enter password: ****
MariaDB [(none)]> show databases;
****MariaDB [(none)]> create database test1 환경변수 설정
MySQL
- mysql 설치
- mysql workbench 설치
- mysql workbench를 통해 역공학(Reverse Engineer)하여, 물리적 ERD를 생성할 수 있다.
- 가상 ERD(논리적 테이블): 일반적 정보 데이터를 추상화한 데이터의 구조적 개념을 DBMS로 생성할 수 있도록 조금더 추상화한 것.
- 물리적 ERD: 데이터가 저장될 수 있도록 논리적 데이터 모델을 물리적 설계로 실제 DBMS의 형태로 표현한 것.
- 파란색은 not null
- 노란색은 key
- 노란 탭은 가상 뷰
기초
DB
- 데이터베이스(DB): 테이블들이 모여 이루는 데이터 단위
- 관계형 데이터베이스(RDB, Relationship DataBase): 서로 관련성 있는 데이터를 테이블 형태로 저장하고 있는 데이터베이스
- 데이터베이스의 종류
- 계층적 데이터베이스: 계층적 데이터베이스는 검색 쿼리가 일상적이지만 고속 처리가 필요한 경우에 유용함. 계층적 데이터베이스는 은행, 항공 및 호텔 산업에서 선호되며, 고객과 관련된 모든 정보는 마스터 레코드에 저장되므로 다른 데이터 소스와 상호 참조할 필요가 없음. 가계도와 유사한 나무 구조로 구성되어 계층 구조의 다른 레벨을 부모/자식 관계로 언급하기도 함.
- 역 데이터베이스: 계층적 데이터베이스의 속도와 예기치 않은 질문에 응답할 수 있는 유연성을 갖추고 있기 때문에 다이렉트 마케팅 응용 프로그램에 적합. 업데이트 된 정보가 발생했을 때 역 데이터베이스에 새로운 요소를 추가하는 것도 쉬움. 일반적으로 사용되는 역 파일 시스템은 Model 204, Adabas, 그리고 Computer Associates 의 Datacom/DB이며, 구현 환경에 따라 처리 속도와 유연성이 다양함.
- 관계형 데이터베이스: 간단한 테이블들로 구성되며 사용자는 이러한 테이블에서 정보를 추출하여 재조합하는 쿼리를 작성할 수 있음. 유연성이 가장 높으나, 처리 속도는 다소 느림
DBMS
- DBMS(DataBase Management System): 데이터베이스를 관리하는 시스템
- 종류: 계층형, 네트워크형, 관계형(RDBMS), 객체지향, 객체관계형(ORDBMS) 데이터베이스 와 NoSQL(Not Only SQL)
- 예시: 오라클, 마이크로소프트 SQL 서버, MySQL, MariaDB, PostgreSQL, SQLite는 RDBMS 종류다. 몽고DB는 NoSQL 종류이다.
- DBMS의 특성
- 실시간 접근성 (Real-time-processing) 데이터베이스는 컴퓨터를 이용하여 접근하는 저장장치에 지속적이고 비정형적인 질의에 대하여 실시간 처리가 가능해야 함
- 계속적인 변화 (Continuous evolution) 데이터베이스는 데이터 삽입(Insert), 기존의 데이터 삭제(Delete), 갱신(Update) 등의 변화를 주어 정확한 데이터를 유지해야 함
- 동시 공용 (Concurrent sharing) 데이터베이스는 사용자가 동시에 접속하더라도, 원하는 데이터에 접근하여 이용할 수 있어야 함
- 내용에 의한 참조 (Contents reference) 데이터베이스 환경에서 사용자 요구하는 데이터 내용으로, 데이터가 가지고 있는 값에 따라 참조됨
데이터 모델링
- 데이터 모델링: 고객의 요구에 따라 데이터를 구조화하고 조직화하여 데이터베이스에 반영하는 작업
- 데이터 모델링의 특징
- 추상화 (Abstraction): 공통적 특징을 갖고 간략화하여 표현
- 단순화 (Simplification): 복잡한 문제를 누구나 알기 쉽게 단순화하여 표현
- 명확성 (Clarity): 이중성 없이, 반드시 하나의 의미를 띄도록 표현
- 데이터 독립성: 정규화 등을 이용하여 데이터 중복을 단계 별로 제거하는 것
- 데이터베이스의 에러가 생기는 주된 이유는 중복성, 종속성으로, 정규화(Normalization)이 해결방안이다. 제1~제5정규화가 있으며, 주로 제1~제3정규화까지만 활용한다.
- 스키마: 데이터베이스의 정보를 저장하는 구조(현재 데이터베이스의 정체성을 나타내는 정보)와 제약조건을 정의한 . 외부, 개념, 내부 스키마의 3층 구조로 데이터 독립성을 향상시킬 수 있다.
- 외부 스키마: 사용자 개개인이 보는 자료와 관점에 대한 사용자적 스키마로 사용자에게 필요한 데이터베이스에 대한 다양한 정의
- 내부 스키마: 전체 데이터베이스의 논리적 구조로 데이터의 종류, 위치, 관계, 접근 권한 등을 정의. DBA 또는 설계자의 관점.
- 개념 스키마: 물리적 저장장치 측면에서 데이터의 내부(필드 이름, 필드의 종류, 필드의 크기 등) 실제로 저장되는 방법을 정의. 개발자의 관점.
- 객체관계모델(Entity Relationship Model): entity > tuple > attribute로 이루어진 데이터베이스 설계도이다.
- ERD(Entity Relationship Diagram): 개체와 개체가 어떠한 관계로 이루어져 있는지 나타내는 그림으로 entity, tuple, attribute로 이루어져 있다.
데이터베이스 구조
- 데이터베이스 > 테이블 > 레코드 > 필드
- 뷰(View): Select의 결과로 보여지는 테이블. 검색문이나 연산식만 저장되는 논리적인 가상 테이블. 물리적인 실제 테이블이 아니고, 논리적인 연산식 또는 검색문으로 이루어진 것. 참조하는 물리적 테이블의 값이 변경되었을 때 자동으로 재연산되어 값이 변경됨.
- 테이블(Tabe, Entity, Relation, 개체): 관계형 데이터베이스에서 표와 같은 형태의 데이터 집합. 서로 관련 있는 필드를 하나의 레코드로 묶어 관리함.
- 로우(record, tuple, row, instance): 서로 관련 있는 필드를 묶은 하나의 줄, 행 단위
- 자바 프로그래밍 상에서는 레코드와 필드라고 한다. DB 설계하는 입장에서는 튜플과 속성이라 한다. sql 상에서는 로우와 컬럼이라 부른다.
- ERD(Entity Relationship Diagram): 개체와 개체가 어떠한 관계로 이루어져 있는지 나타내는 그림으로 entity, tuple, attribute로 이루어져 있다.
- 차수(degree): 한 로우의 컬럼 수로, 모든 로우는 최소 하나의 차수를 가진다.
- 컬럼(field, attribute, column): 데이터의 최소 단위로, 항목을 나타내는 한 칸.
- 기수(cardinality): 튜플의 수로, 데이터가 삽입되지 않았다면 0의 값을 가질 수 있다.
- 도메인(Domain): 컬럼이 가질 수 있는 원자값들의 집합 또는 범위. 컬럼에 제약조건(Constraint)에 의해 입력되어야 하는 값이 정해진 경우 정해진 값이 아니면, 입력되면 안 됨(도메인 무결성)
- 관계(Relationship): 엔티티의 인스턴스 간 논리적 연관성으로, 존재나 행위에 있어 상호 연관성이 부여된 상태를 말한다.
키 Key
- 기본키(Primary Key): 레코드 간의 서로를 각각 구분할 수 있는 특정 필드. 레코드 간 중복 및 레코드의 null 값이 허용되지 않는다. 이를 개체 무결성이라 한다.
- create table 테이블명(컬럼명 타입 primary key); # 해당 컬럼을 기본 키로 지정
- create table 테이블명(컬럼명1 타입, 컬렴명2 타입) [제약조건] primary key (컬럼명1, 컬럼명2); #여러 컬럼을 하나의 항목으로 묶어 기본 키로 지정
- 무결성(integrity): 완전한 수명 주기를 거치며 데이터의 정확성과 일관성을 유지하고 보증하는 것.
- 후보키: 중복 불가. 언제든지 기본키가 될 수 있는 것. 기본키를 정하기 전 키.
- 대체키: 중복 불가. 기본키를 대체할 수 있는 컬럼으로 기본키가 정해지면 기존의 나머지 후보키가 대체키가 된다.
- 외래키(Foreign Key): 한 테이블의 필드 중 다른 테이블의 행을 식별할 수 있는 기본키를 말한다.
- 참조 무결성(Referential Integrity): 하나 또는 다수의 다른 테이블의 기본 키로, 참조 테이블에 해당 컬럼의 값이 없거나 중복되거나 널값이면 안된다
- 참조 테이블(참조되는 테이블)과 연결하는 현재 테이블의 컬럼을 외래키라 한다. 외래키 컬럼은 참조테이블과 현재 테이블 모두 존재하지만, 참조테이블에서는 기본키여야 한다.
- 요즘에는 복잡성 문제로 잘 사용하지 않고, Join을 사용한다.
-- 회원(Member) 테이블 생성
CREATE TABLE MEMBER(
id VARCHAR(16) NOT NULL,
pw VARCHAR(330) NOT NULL,
NAME VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
tel VARCHAR(13),
regdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
POINT INT DEFAULT 0,
PRIMARY KEY (id));
SHOW TABLES;
desc member;
INSERT INTO 테이블(컬럼명, ...) VALUES(값, ...);
INSERT INTO member(id, pw, NAME, email, tel) VALUES ('admin', '1234', '관리자', 'admin@edu.com', '010-1004-1004');
INSERT INTO member(id, pw, NAME, email, tel) VALUES ('teacher', '1234', '선생님', 'teacher@edu.com', '010-1004-1005');
INSERT INTO member(id, pw, NAME, email, tel) VALUES ('sam', '1234', '쌤', 'sam@edu.com', '010-1004-1006');
INSERT INTO member(id, pw, NAME, email, tel) VALUES ('student', '1234', '학생', 'student@edu.com', '010-1004-1007');
SELECT * FROM member;-- board table 생성board
CREATE TABLE board(
bno INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
contents VARCHAR(1000),
author VARCHAR(16) NOT NULL,
regdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
cnt INT DEFAULT 0);
INSERT INTO board(title, contents, author) VALUES ('금요일', '네', 'teacher');
INSERT INTO board(title, contents, author) VALUES ('7월 28일', '내', 'sam');
INSERT INTO board(title, contents, author) VALUES ('더미쓰', '냉', 'admin');
INSERT INTO board(title, contents, author) VALUES ('더미쓰1', '냉', 'admin');
INSERT INTO board(title, contents, author) VALUES ('더미쓰2', '냉', 'admin');
INSERT INTO board(title, contents, author) VALUES ('더미쓰3', '냉', 'admin');
INSERT INTO board(title, contents, author) VALUES ('더미쓰4', '냉', 'admin');
INSERT INTO board(title, contents, author) VALUES ('더미쓰5', '냉', 'admin');
COMMIT;DDL(Data Define Language)
- DDL(Data Define Language): 데이터 정의어로 구조를 만드는 명령어이다. dba가 많이 함.
- create, alter, drop 명령이 있다.
create 생성 명령
CREATE database 데이터베이스명;
CREATE table 테이블명 (컬러명 타입 [제약조건], ... );
CREATE index 인덱스명 as 테이블명 컬럼명;alter 수정 명령
ALTER TABLE 테이블명 add 컬럼명 컬럼타입 [제약조건]; -- 컬럼 추가
ALTER TABLE 테이블명 modify 컬럼명 컬럼타입 [제약조건]; -- 기존 컬럼 타입, 제약조건 변경
ALTER TABLE 테이블명 drop column 컬럼명; -- 컬럼을 없앰
ALTER TABLE 테이블명 rename 구_컬럼명 신_컬럼명; -- 컬럼명 변경
ALTER TABLE 테이블명 edit 구_컬럼명 신_컬럼명; -- 컬럼명 변경
ALTER TABLE 테이블명 change 구_컬럼명 신_컬럼명; -- 컬럼명 변경 (일반적, 마리아DB, MySQL)drop 제거 명령
Drop database 데이터베이스명;
Drop table 테이블명;DML(Data Management Language)
- DML(Data Management, Manipulation Language): 데이터 조작어, 운영어로 CRUD(Insert, Select, Update, Delete)가 여기에 속한다.
Insert(Create)
INSERT INTO 테이블 VALUES(컬럼값1, 컬럼값2, ...);
INSERT INTO 테이블 컬럼 VALUES(컬럼값);
INSERT INTO customer VALUES('AK-100880', 'Illill Kim', 'Consumer', 'South Korea', 'Seoul', 'Seoul', 18517, 'West');Select(Read)
- 가장 많이 사용
SELECT * FROM 테이블; -- 테이블에서 모든 컬럼 선택 출력
SELECT 컬럼1, 컬럼2, ... FROM 테이블; -- 테이블에서 일부 컬럼들만 선택하여 출력
SELECT * FROM member;
SELECT id, name FROM member;Update
-- 업데이트
UPDATE 테이블 SET 컬럼='새 컬럼값' WHERE 조건;
UPDATE board SET author='sam' WHERE bno=6; -- 글번호 6의 author를 sam으로 바꿈;Delete
DELETE FROM 테이블 WHERE 조건절;
DELETE FROM member WHERE id='dummy'; -- 아이디가 'dummy'인 멤버 삭제기타 키워드
마리아 DB 데이터형 🔗
WHERE 조건절
AS (Alias)별칭
- 테이블 또는 컬럼을 별칭으로 부른다.
- AS는 생략이 가능하다.
- AS 뒤의 별칭(닉네임)은 영어 대소문자로만 이루어진 경우 따옴표를 생략 가능하지만 띄어쓰기나 한글 등이 포함된 경우에는 반드시 따옴표 안에 써주어야 한다.
SELECT 컬럼1 as 닉네임 FROM 테이블; -- 테이블의 컬럼1을 닉네임 이름으로 가져온다.
SELECT a.컬럼1 FROM 테이블 as a; -- 테이블을 a라는 닉네임으로 하여 a의 컬럼1을 가져온다.JOIN
SELECT a.id, a.name, a.email, b.bno, b.title FROM member a INNER JOIN board b ON a.id=b.author; -- 내부 조인ORDER BY
- 다른 일반적인 dbms는 명령어 끝에 order by가 와야 함(mariadby와 mysql은 like가 마지막에 옴)
SELECT * FROM board ORDER BY author ASC;
SELECT * FROM board ORDER BY author ASC, cnt DESC;GROUP BY
- Group by 집계
- count, sum, avg, max, min, stdev(표준 편차)
- HAVING 조건절: Group by의 조건은 where가 아니라 having이다.
SELECT author, COUNT(author) FROM board GROUP BY author HAVING 조건;연관 쿼리
SELECT * FROM member a, board b; -- 4(memeber의 row)*8(board의 row) = 32건, 13개 항목
SELECT a.id, a.name, a.email, b.bno, b.title FROM member a, board b; -- 32건, cardinality가 5개
SELECT a.id AS 'pid', a.name AS 'pname', a.email, b.bno, b.title FROM member a, board b WHERE a.id=b.author; -- 컬럼 이름은 as 'alias(별칭)'으로 불러옴. 특정 게시판에 글을 올린 회원 정보와 글 정보를 모두 표시한 것.집합 명령어
- union(합집합): 중복 제거하여 합집합
- union all: 중복 포함하여 합집합
- intersect(교집합)
- except(차집합): 오라클에서는 minus
-- 합집합 CREATE VIEW 뷰이름 AS (SELECT 열 FROM 테이블 UNION SELECT 열2 FROM 테이블2)
CREATE VIEW uni_table1 AS (SELECT productid, price FROM product2 UNION SELECT productid, price FROM product3);
-- 교집합 CREATE VIEW 뷰이름 AS (SELECT 열 FROM 테이블 INTERSECT SELECT 열2 FROM 테이블2)
CREATE VIEW int_table1 AS (SELECT productid, price FROM product2 intersect SELECT productid, price FROM product);
-- 차집합 CREATE VIEW 뷰이름 AS (SELECT 열 FROM 테이블 EXCEPT SELECT 열2 FROM 테이블2)
CREATE VIEW exc_table1 AS (SELECT productid, price FROM product EXCEPT SELECT productid, price FROM product2);함수와 연산자
- SQL에서도 기본적인 함수와 연산자를 쓸 수 있다.
- ROUND, IF, case when else end문이 있다.
ROUND(반올림)
IF(조건, 참일 때 값, 거짓일 때 값)
case when 조건1 than 값1 when 조건2 than 값2 ... else 값n end [as 별칭]
SELECT sname AS '이름',
(kor+eng+mat) AS 'tot',
((kor+eng+mat)/3)AS 'ave',
if(((kor+eng+mat)/3)>=80, '합격', '불합격') AS 'pan',
case
when ((kor+eng+mat)/3)>90 then 'A'
when ((kor+eng+mat)/3)>80 then 'B'
when ((kor+eng+mat)/3)>70 then 'C'
when ((kor+eng+mat)/3)>60 then 'D'
ELSE 'F'
END AS 'hak' FROM student;DCL
- DCL(Data Control Language): 권한 및 역할 설정하는 언어. 때로 어떤 DB는 DCL에 TCL을 포함시키기도 한다.
- TCL(Transaction Control Language): commit, roll-back, savepoint.
- 권한 명령: grant, revoke. 권한 명령 후에는 flush 키워드를 통해 권한을 확정해야 한다. 루트 계정에서만 사용 가능하다.
grant
- grant: 권한 부여.
- 호스트: 사용자 접속 주소/위치(로컬접속/원격접속/LAN 접속 등)
- localhost, 대역대(192.168.00.%), 인터넷 전체(%)를 호스트 값으로 부여
create user '사용자명'@'호스트' identified by '비밀번호' ;
create user 'test1'@'%' identified by '1234'; -- 계정 추가, %는 외부자 속성
grant [all/CRUD(테이블의 컬럼명)] on [*/DB명].[*/테이블명] to '사용자명'@'호스트';
grant all privileges on *.* to 'test1'@'%'; -- *.*은 모든 Db와 테이블에 권한
flush privileges; -- commit과 같이 권한 확정
select user(); -- 현재 사용자 식별
select distinct User from mysql.user; -- 모든 계정보기
show grants for 'test2'@'%'; -- 계정에 대한 권한 정보 열람
revoke
- revoke: 사용자 권한 회수.
revoke all on *.* from 'test2'@'%'; -- 권한 회수
flush privileges; -- 반드시 권한 반영 필요
사용자 삭제: 삭제할 사용자 이름으로 만든 테이블이나 DB를 모두 삭제 후에 사용자를 삭제할 수 있다.
drop user 'test2'@'%';
flush privileges;
select distinct User from mysql.user;
TCL 트랜잭션
트랜잭션(Transaction): 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위. 그냥 보기엔 두 가지 이상의 일이지만, 같이 처리되어야 할 데이터베이스의 작업이다.
- 트랜잭션 처리가 되지 않으면, 재고 처리 시스템에 문제가 발생한다. 간혹 이를 차집합으로 연산하는 경우가 있으나, 테이블의 변화를 예측하기 힘든 현상이 발생하기 때문에 사용하지 않는 것이 좋다. 차집합은 분기별 집계 등에 쓰인다.
트랜잭션 특징 ACID 🔗
- 원자성(Atomicity): 트랜잭션이 데이터베이스에 모두 반영되거나, 전혀 반영되지 않아야 한다. 트랜잭션은 사람이 설계한 논리적 작업 단위로서 일처리는 작업 단위 별로 이루어져야 사람이 다루는 데 무리가 없다. 만약 트랜잭션 단위로 데이터가 처리되지 않는다면, 설계한 사람은 데이터 처리 시스템을 이해하기 힘들 뿐만 아니라, 오작동 했을 시 원인을 찾기 매우 힘들어진다.
- 일관성(Consistency): 일관성은 트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 한다는 것이다. 트랜잭션이 진행되는 동안에 데이터베이스가 변경 되더라도 업데이트된 데이터베이스로 트랜잭션이 진행되는것이 아니라, 처음에 트랜잭션을 진행 하기 위해 참조한 데이터베이스로 진행된다. 이렇게 함으로써 각 사용자는 일관성 있는 데이터를 볼 수 있는 것이다.
- 독립성(Isolation): 독립성은 둘 이상의 트랜잭션이 동시에 실행되고 있을 경우 어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에 끼어들 수 없다는 점을 가리킨다. 하나의 특정 트랜잭션이 완료될때까지, 다른 트랜잭션이 특정 트랜잭션의 결과를 참조할 수 없다.
- 지속성(Durability): 지속성은 트랜잭션이 성공적으로 완료됬을 경우, 결과는 영구적으로 반영되어야 한다는 점이다.
트랜잭션 명령어
- 트랜잭션 명령어에는 transaction, commit, rollback, savepoint가 있다.
START TRANSACTION;
병렬 처리문1;
병렬 처리문2;
COMMIT;
START TRANSACTION;
1
ROLLBACK;
-- (가장 최근 커밋 이후)start transaction으로 돌아감. 1 사라짐
2
SAVEPOINT a;
3
ROLLBACK TO a;
-- savepoint a로 돌아감. 2만 남마이그레이션(Migration)
파일
- 파일 불러오기
LOAD DATA LOCAL INFILE 'customer.csv' INTO TABLE customer FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';- 파일 내보내기: [우클릭] - [격자 행 내보내기]
sql 명령어
source 파일경로_및_파일명;
source pro_02.sql;MVC
- Model: 데이터베이스를 접근해서 데이터를 가지고 오는 것이 목적.
- View: 사용자에게 보여지는 html, jsp 문서를 view라고 한다.
- Controller: 사용자가 요청한 정보를 접수하고 dao에 던져서 데이터를 처리하고, 제어하는 controller. 서블릿이 보통 컨트롤러.
- 모델: 데이터와 비즈니스 로직을 관리합니다.
- 뷰: 레이아웃과 화면을 처리합니다.
- 컨트롤러: 명령을 모델과 뷰 부분으로 라우팅합니다.
- 목적: 데이터 처리의 병렬성을 위하여 분업하는 것. View에서 요청을 받고,
- MVC2 패턴. DAO가 따로따로 있음.
상민