[Database] DBMS 정의와 종류, SQL 문법(DDL, DML, TCL)

DBMS의 정의

- DBMS : DataBase Management System

 

- 종류

관계형 DataBase  : MySQL, MariaDB, Oracle, Postgres

NoSQL  : MongoDB, Redis

Redis는 로그인 시 인증에 사용되어 MySQL+Redis, MariaDB+Redis, MongoDB+Redis 혼합해서 사용

 

- 관계형 데이터베이스(RDB)

서로 관계를 갖는 데이터들의 집합

데이터, 행, 열 등의 구조로 데이터가 구조화

 

- MariaDB

MySQL의 포크, MySQL의 기본적인  특성 및 구조를 그대로 유지 + 추가 기능

완전한 오픈소스 -> 지속적인 발전 가능성, 사용자 입장에서 무료

뛰어난 성능 및 최적화

 

- 관련 개념 정리

DBMS : DB를 관리하는 프로그램 ex) mariaDB

스키마 = 데이터베이스

스키마와 테이블은 1:N 관계

 

 

 

SQL

- DBMS에서 사용하는 프로그래밍 언어

 

- CRUD

Create(생성), Read(조회), Update(수정), Delete(삭제)의 약어

 

- SQL 구문

1. DDL(Data Definition Language)

데이터베이스나 테이블, 뷰 등을 생성, 삭제하거나 구조를 변경

CREATE, ALTER, DROP

 

2. DML(Data Manipulaiont Language)

데이터베이스에 저장된 데이터를 처리하거나 조회

INSERT, UPDATE, DELETE, SELECT

 

3. DCL(Data Control Language)

사용자 권한 부여

GRANT, REVOKE

 

 

 

테이블 관련 용어

출처 : https://ppiyo5.tistory.com/11

(1) 열=필드=속성

컬럼의 구체적인 데이터 하나하나의 개별 데이터 항목을 의미

 

(2) 행=튜플=레코드

 

(3) 값=데이터

 

(4) 키

행의 식별자로 이용되는 열

기본키(Primary Key)  : 테이블에 저장된 레코드를 고유하게 식별하는 키

외래키(Foreign Key) : 두 테이블을 서로 연결하는데 사용되는 키

 

- 인덱스

조회 성능을 높이기 위한 별도의 페이지

제약조건 정보조회와 인덱스 조회는 상당수 일치

제약조건이 생기면 주요 키들에 대한 인덱스가 생성된다

INDEX가 빠른 이유 : B-Tree 기반이기 때문에

 

 

 

mariaDB 및 workbench 설치

https://dev.mysql.com/downloads/workbench/

MySQL :: Download MySQL Workbench

 

 mariadb 환경변수 설정하는 방법

(1) mairadb가 설치된 bin 파일 경로 찾기

C:\Program Files\MariaDB 11.3\bin

 

(2) 왼쪽 하단 돋보기 [찾기]에 "시스템 환경 변수 편집" 검색

(3) "환경 변수" -> 하단 시스템 변수(S)에서 Path 누르고 "편집"

(4) "새로 만들기" -> mairadb가 설치된 bin 파일 경로 붙여넣기 -> "확인"

 

 

환경변수 설정해주는 이유 !!

mariadb/bin 파일 안에 실행파일이 있기 때문에,

원래는 mariadb/bin으로 이동해서 접속(mariadb -u root -p)해야 하는데

환경변수 설정을 통해 실행파일을 아무데서나 접근 가능하게 golbal화 시켜주는 것

그래서 환경변수 설정 이후 그냥 cmd 켜서 바로 mariadb에 접속할 수 있음

 

 

 

Store in Vault ... 눌러서 Password를 작성해줘야 오류없이 사용할 수 있다

 

 

- cmd mariadb 사용법

mariadb -u root -p로 실행

show databases;

use board;

다양한 SQL 명령어 실행

exit

 

- workbench 사용법

한 줄 실행 : Ctrl + Enter

전체 실행 : Ctrl + Shift + Enter  OR 번개 모양 누르기

제약조건으로 실행 안되면 mariadb -> Edit -> Preferences -> SQL Editer -> 하단 Safe Updates (rejects UPDATEs and DELETEs with no restrictions) 체크 해제

 

 

<DDL 기본 명령어>

DDL 기본 명령어 실습

 

 

 

(+) ALTER 명령어 정리

출처 :&nbsp;https://hoonmaro.tistory.com/29

--테이블 이름 변경(RENAME)

ALTER TABLE 테이블명 RENAME 새로운 테이블명;

ALTER TABLE posts RENAME post;

--컬럼 추가(Add)

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 자료형 [NULL | NOT NULL];

ALTER TABLE author ADD COLUMN role VARCHAR(50);

--필드 타입 변경(Modify) => 덮어쓰기이기 때문에 이전의 제약조건을 같이 적어줘야 함!

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 타입 [제약조건];

ALTER TABLE author MODIFY COLUMN name VARCHAR(100) NOT NULL;

--컬럼 이름변경(CHANGE)

ALTER TABLE 테이블명 CHANGE COLUMN 기존컬럼명 새로운컬럼명 타입 [제약조건];

ALTER TABLE post CHANGE COLUMN content contents VARCHAR(255);

--컬럼 삭제(DROP)

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

ALTER TABLE author DROP test1;

 

 

<DDL 기본 명령어 실습과제>

변경 전 post 테이블

변경 전 author 테이블

 

 

1. post 테이블의 contents 컬럼 글자수 3000으로 변경

ALTER TABLE post MODIFY COLUMN contents VARCHAR(3000);

 

2. author 테이블에 address 컬럼 varchar 255로 추가

ALTER TABLE author ADD COLUMN address VARCHAR(255);

 

3. post 테이블 생성문 조회 -> post 테이블 삭제 -> post 테이블 다시 생성

SHOW CREATE TABLE post;

DROP TABLE post;

CREATE TABLE `post` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `contents` varchar(3000) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `post_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

 

 

변경 후 post 테이블

변경 후 author 테이블

과제코드

 

 

 

 

<DML 기본 명령어>

DML 기본 명령어 실습

 

1. INSERT

테이블에 새로운 레코드를 추가

INSERT INTO 테이블이름(필드이름1, 필드이름2, ... ) VALUES(데이터값1, 데이터값2, ...)

ex) INSERT INTO author(id, name, email) VALUES(1, 'kim', 'abc@naver.com');

NOT NULL 제약조건이 걸린 필드는 무조건 추가해줘야 함. 나머지는 상관없음

 

 

<INSERT 명령어 실습과제>

(1) author 데이터 5개 추가

address필드 비워서 5개 행 추가

id 값은 순차적으로 증가하게 할 것

(2) post 데이터 5개 추가

2개는 저자가 있는 데이터, 2개는 저자가 비어있는 데이터(author_id)에 NOT NULL 조건없다는 사실 확인

1개는 저자가 author 테이블에 없는 데이터 추가 -> 에러 발생 확인하기

 

 

2. UPDATE

테이블 레코드의 내용을 수정

UPDATE 테이블이름 SET 필드이름1=값1, 필드이름2=값2, ...

WHERE 필드이름=값

만약 WHERE 절을 생략하면, 해당 테이블의 모든 레코드값이 위 설정대로 수정되므로 유의

ex) UPDATE author SET email='update@naver.com' WHERE id=1;

 

 

3. DELETE

DELETE FROM 테이블이름 WHERE 필드이름=데이터값

WHERE 절을 생략하면, 해당 테이블에 저장된 모든 데이터가 삭제

ex) DELETE FROM author WHERE author_id=6;

 

 

<UPDATE, DELETE 명령어 실습과제>

(1) author 데이터 중 id가 4인 데이터를 email을 abc@naver.com, name을 abc로 변경

(2) post에 글쓴적이 없는 author 데이터 1개 삭제

(3) port에 글쓴적이 있는 author 데이터 1개 삭제 -> 에러 -> 조치 후 삭제(author에서 해당 데이터 먼저 삭제하거나 null로 세팅)

 

과제코드

 

 

 

DELETE vs TRUNCATE vs DROP

 

DELETE는 행 일부 또는 전체 데이터를 지우고,

TRUNCATE는 행 전체 데이터 뿐만 아니라 행이 저장되어있던 공간과 인덱스도 삭제하며,

DROP은 인덱스, 행 데이터 뿐만 아니라 테이블 전체를 삭제합니다.

 

 

출처 :https://chiefcoder.tistory.com/58

DELETE는 삭제하면서 백업본, 로그 등을 만들기 때문에 DROP, TRUNCATE에 비해 느리다

 

 

4. SELECT

SELECT * FROM 테이블이름 [WHERE조건]

ex) SELECT * FROM author ;

SELECT name, email FROM author WHERE id=1;

SELECT * FROM author WHERE id>1;

SELECT * FROM author WHERE id>1 AND name="kim";

 

-중복제거(DISTINCT)

SELECT DISTINCT name FROM author;

 

-정렬

ORDER BY절 사용

기본 정렬 : ASC, 생략시 ASC적용

먼저 쓴 컬럼 우선정렬
select * from author order by name, email desc;

 

-LIMIT

결과 값의 개수 지정

select * from author order by id desc limit 2;

 

-별칭(ALIAS)을 이용한 처리

테이블과 필드에 임시로 별칭을 부여하고, 해당 별칭을 SELECT 문에서 사용

SELECT 필드이름 AS 별칭 FROM 테이블 이름;

SELECT 필드이름 FROM 테이블 이름 AS 별칭 ;

 

 

<DDL, DML 전체 실습과제>

drop database;

create database board;

 

1. 아래 스키마를 보고 author, post 테이블 재생성 (FK는 default인 restrict설정으로 둠)

author 테이블

post 테이블

-author 테이블 생성

create table author(id int primary key, name varchar(100) NOT NULL , email varchar(255), password varchar(255), role varchar(50), address varchar(255))

 

-post 테이블 생성

 create table post(id int primary key, title varchar(255), contents varchar(3000), author_id int, foreign key(author_id) references author(id))

 

 

2.

(1) 게시판 회원은 총 5명이 되도록 만들고, 게시판에 실명으로 글이 쓰여진 게스글은 총 3건, 익명으로 글이 쓰여진 글은 총 2건이 있도록 데이터 insert

INSERT INTO author(id, name, email, password, role, address) VALUES(1, 'aaa', 'aaa@naver.com', 'aaa', 'manager', 'a동 a호');

INSERT INTO author(id, name, email, password, role, address) VALUES(2, 'bbb', 'bbb@naver.com', 'bbb', 'manager', 'b동 b호');

INSERT INTO author(id, name, email, password, role, address) VALUES(3, 'ccc', 'ccc@naver.com', 'ccc', 'user', 'c동 c호');

INSERT INTO author(id, name, email, password, role, address) VALUES(4, 'ddd', 'ddd@naver.com', 'ddd', 'user', 'd동 d호');

INSERT INTO author(id, name, email, password, role, address) VALUES(5, 'eee', 'eee@naver.com', 'eee', 'user', 'e동 e호');

 

INSERT INTO post(id, title, contents, author_id) VALUES(1, '귤 정보', '건강', 1);

INSERT INTO post (id, title, contents, author_id) VALUES(2, '비타민 정보', '건강', 2);

INSERT INTO post (id, title, contents) VALUES(3, '상체운동 정보', '운동');

INSERT INTO post (id, title, contents) VALUES(4, '하체운동 정보', '운동');

INSERT INTO post (id, title, contents, author_id) VALUES(5, '우유 정보', '건강', 3);

 

 

(2) 게시판의 회원을 모두 delete하되, 기존에 posting된 글은 삭제되지 않고 남아있도록 하여라

UPDATE post SET author_id=null WHERE author_id IS NOT NULL;

DELETE FROM author;

 

(3) post의 글을 3개만 조회하되, title을 기준으로 오름차순하고 만약 제목이 같은 경우 contents로 내림차순이 되도록 조회하여라.

SELECT * FROM post ORDER BY title, contents desc LIMIT 3;