MariaDB 기본(설치) 및 기본 SQL 연습

Dev/Mysql(MariaDB)

MariaDB 기본(설치) 및 기본 SQL 연습

OK-가자 2021. 9. 30. 14:07

About MySQL

1) 세계에서 가장 인기 있는 Open source DB (하루 50,000번 이상 다운로드)
2) 1995년 첫 공식 버젼 발표(MySQL AB)
3) 2001년 GNU GPL 등록
4) 2008년 1월 SUN에서 인수(2010년 오라클 SUN인수로 오라클에서 현재 소유)
5) Open source LAMP/SAMP stack으로 급성장
6) 1000여개 SW 및 HW 회사에 번들로 설치
7) 오라클 인수 후, MySQL를 다른 DBMS에서 교체하는 움직임이 있다)
8) DB관리 TCO의 획기적인 감소
9) OracleDB, MS SQL과 함께 2019년 현재 3대 DBMS 로 많이 사용되고 있다.
10) 주요 버젼(Community Server)
- 5.1(2008년, InnoDB 1.0)
- 5.5(2010년, InnoDB 1.1)
- 5.6(2011년, 퀄리 옵티마이저 성능 향상)
- 현재(2019년) 8.0

MYSQL 구성

1) Monty Program AB라는 회사에서 만든 독립적인 RDBMS
2) MySQL 커뮤니티 코드 베이스를 이용해서 탄생(MySQL 커뮤니티 버젼과 상당부분 호환)
3) MariaDB는 오픈소스이다.
4) MySQL과의 공통점
- Binary Drop in replacement MySQL (데이터 파일들이 호환)
- 실행 프로그램과 유틸리티는 모두 MySQL과 이름이 동일하며 호환된다.
- 모든 클라이언트 API와 프로토콜은 호환된다.
- 모든 파일과 포트및 파일의 경로가 동일
- MySQL Connector(Java, c) 모두 변경 없이 사용 가능
- MySQL 클라이언트 프로그램은 변경없이 MariaDB 서버의 연경에 사용할 수 있다.
5) MySQL과의 차이점
- 라이선스
- 옵티마이저(디스크 읽기/쓰기, 조인, 서브쿼리, 임시테이블 & 뷰) 에서 차이가 있다.
- 내부 스토리지 엔진(메모리 스토리지, 임시 테이블 스토리지, 트랜잭션, NoSQL지원)에서 MariaDB 10.x 부터 차이를 보인다.

설치

1) 의존 라이브러리 및 유틸리티 설치

# yum install -y gcc gcc-c++ libtermcap-devel gdbm-devel zlib* libxml* freetype* libpng* libjpeg* iconv flex gmp ncurses-devel cmake.x86_64 libaio

2) 계정 생성

# groupadd mysql 
# useradd -M -g mysql mysql

3) 소스받기

# wget https://downloads.mariadb.org/interstitial/mariadb-10.1.38/source/mariadb-10.1.38.tar.gz # tar xvfz mariadb-10.1.38.tar.gz 
# cd mariadb-10.1.38

4) 컴파일 환경 설정

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/cafe24/mariadb\ -DMYSQL_USER=mysql\ -DMYSQL_TCP_PORT=3307\ -DMYSQL_DATADIR=/usr/local/cafe24/mariadb/data\ -DMYSQL_UNIX_ADDR=/usr/local/cafe24/mariadb/tmp/mariadb.sock\ -DINSTALL_SYSCONFDIR=/usr/local/cafe24/mariadb/etc\ -DINSTALL_SYSCONF2DIR=/usr/local/cafe24/mariadb/etc/my.cnf.d\ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci\ -DWITH_EXTRA_CHARSETS=all\ -DWITH_ARIA_STORAGE_ENGINE=1\ -DWITH_XTRADB_STORAGE_ENGINE=1\ -DWITH_ARCHIVE_STORAGE_ENGINE=1\ -DWITH_INNOBASE_STORAGE_ENGINE=1\ -DWITH_PARTITION_STORAGE_ENGINE=1\ -DWITH_BLACKHOLE_STORAGE_ENGINE=1\ -DWITH_FEDERATEDX_STORAGE_ENGINE=1\ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1\ -DWITH_READLINE=1\ -DWITH_SSL=bundled\ -DWITH_ZLIB=system

5) 컴파일 및 설치

# make && make install

6) 설치 디렉토리 권한 변경

# chown –R mysql:mysql /usr/local/cafe24/mariadb

7) 기본 데이터베이스 생성

# /usr/local/cafe24/mariadb/scripts/mysql_install_db \ 
--user=mysql \
--basedir=/usr/local/cafe24/mariadb/ \
--defaults-file=/usr/local/cafe24/mariadb/etc/my.cnf \
--datadir=/usr/local/cafe24/mariadb/data \

8) tmp 디렉토리 생성

# mkdir /usr/local/cafe24/mariadb/tmp
# chown mysql:mysql /usr/local/cafe24/mariadb/tmp

9) 서버 구동해보기

# /usr/local/cafe24/mariadb/bin/mysqld_safe &

10) root 패스워드 설정

# /usr/local/cafe24/mariadb/bin/mysqladmin -u root password '비밀번호'

11) root 접속

# /usr/local/cafe24/mariadb/bin/mysql -p

Basic Queries

연결

Enter password:  *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 241 to server version: 3.23.49

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

MySQL Shell 에서 빠져나오기( 연결 끊기)

mysql> QUIT
mysql> exit

로긴 후, 간단한 쿼리 실행

실습:

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.1.67    | 2013-01-05   |
+-----------+--------------+
1 row in set (0.00 sec)

대부분 MySQL commands semicolon (;)으로 끝난다.
MySQL 은 찾은 전체 row를 출력하고 마지막에 전체 row 수와 쿼리 실행에 걸린 시간을 표시한다.
키워드는 대소문자 구별이 없다.

다음 쿼리들은 모두 같다:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

MySQL은 계산기로도 사용할 수 있다
함수 및 수식 사용 예제:
```
mysql> SELECT SIN(PI()/4), (4+1)*5;
-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
-------------+---------+
| 0.707107 | 25 |
-------------+---------+
여러 문장을 한 줄에 연속으로 붙혀 쿼리 실행이 가능하다.
각 문장에 semicolon(;)만 붙혀 주면 된다.

mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| VERSION()    |
+--------------+
| 3.22.20a-log |
+--------------+
+---------------------+
| NOW()               |
+---------------------+
| 2004 00:15:33 |
+---------------------+

Multi-Line Commands

MySQL은 문장의 끝을 라인으로 구분하는 것이 아니라 semicolon(;)으로 구분하기 때문에 여러줄에 거쳐 문장을 쓰는 것도 가능하다.

예제:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+--------------------+--------------+
| USER()             | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18   |
+--------------------+--------------+

Command 취소

긴 쿼리를 작성하다가 중간에 취소해야하는 경우에는 즉시. \c를 붙혀주면 된다.

mysql> SELECT -> USER() -> \c mysql>

Database 사용

작업하기 위한 데이터베이스를 선택하기 위해서는 어떤 데이터베이스가 존재하는 지 알아보아야 한다.
현재 서버에 존재하는 데이터베이스를 찾아보기 위해서 SHOW statement을 사용한다.

mysql> show databases;  
+----------+  
| Database |  
+----------+  
| mysql |  
| test |  
+----------+

2 rows in set (0.01 sec)

새 database를 생성하기 위해 “create database” command 사용:
mysql> create database webdb;
Database을 선택하기 위해, “use” command 사용:

mysql> use webdb;

Table 생성

Database를 선택 후, Database의 전체 테이블 목록을 출력:

mysql> show tables;  
Empty set (0.02 sec)

“empty set” 은 데이터베이스에 어떤 테이블도 아직 생성되지 않았다는 것을 알려주는 것이다.
애완동물 정보를 저장하기 위한 테이블 생성
Table: pets

    name: VARCHAR(20)  
    owner: VARCHAR(20)  
    species: VARCHAR(20)  
    gender: CHAR(1)  
    birth: DATE  
    death: DATE

Table 생성을 위해, CREATE TABLE command 사용:  
    mysql> CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    gender CHAR(1),
    birth DATE, 
    death DATE );

Query OK, 0 rows affected (0.04 sec)

Describing Table

table 구조를 확인하기 위해, DESCRIBE command 사용:

mysql> describe pet;  
+---------+-------------+------+-----+---------+-------+  
| Field | Type | Null | Key | Default | Extra |  
+---------+-------------+------+-----+---------+-------+  
| name | varchar(20) | YES | | NULL | |  
| owner | varchar(20) | YES | | NULL | |  
| species | varchar(20) | YES | | NULL | |  
| gender | char(1) | YES | | NULL | |  
| birth | date | YES | | NULL | |  
| death | date | YES | | NULL | |  
+---------+-------------+------+-----+---------+-------+  
6 rows in set (0.02 sec)

Table 삭제

table 전체를 삭제하기 위해 DROP TABLE command 사용:
mysql> drop table pet; Query OK, 0 rows affected (0.02 sec)

Loading Data

INSERT statement를 사용해서 table에 데이터를 입력한다.
예제:
INSERT INTO pet VALUES ('Fluffy','Harold','cat','f', '1999-02-04',NULL);

더 많은 애완동물 Data

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1998-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

Loading Sample Pet Data

한 줄당 한 레코드 정보를 담고 있는 `pet.txt' 라는 텍스트 파일을 생성한다.
한 레코드의 값들은 탭(tab)으로 구분되어야 한다. 그리고 순서는 테이블을 생성할 때의 column순서대로 되어 있어야 한다.
그리고 data를 테이블에 load하기 위해 LOAD DATA Command를 사용한다.

Sample Data 파일

Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N

pet.txt 내용 테이블에 로드하기:

mysql>  LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

SQL Select

Table에서 Data를 가져오기 위해서 SELECT 구문을 사용한다.
Format:

SELECT what_to_select
FROM which_table 
WHERE conditions_to_satisfy

전체 Data Select

SELECT를 가장 간단히 사용하게 되면 table의 모든 데이터를 가져오게 된다.

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1999-02-04 | NULL       |
| Claws    | Gwen   | cat     | f    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1999-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1998-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    |      | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

특정 Row에 대한 Select

테이블에서 특정 row만 가져올 수 있다.
예를 들어, 바우저의 생일이 변경되었는 지 확인하기 위해 다음과 같이 바우저의 레코드를 선택 할 수 있다. :
```
mysql> SELECT * FROM pet WHERE name = "Bowser";
--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1998-08-31 | 1995-07-29 |

--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

##### 특정 Row에 대한 Select
1998년 이 후에 태어난 동물을 조회할 때:
```SELECT * FROM pet WHERE birth >= "1998-1-1"; ```

암컷 강아지들을 조회 할 때, 논리 연산자 AND를 함께 사용해서:
SELECT * FROM pet WHERE species = "dog" AND sex = "f";

뱀과 새를 모두 조회할 때는 논리 연산자 OR와 함께:
SELECT * FROM pet WHERE species = "snake“ OR species = "bird";

특정 Row에 대한 Select

Row의 전체 column을 보고 싶지 않을 경우, comma(,)로 분리해서 관심있는 column를 적어주면 된다.

예를 들어, 애완동물의 생년만 알고 싶다면, name과 borth만 select하면 된다.

mysql> select name, birth from pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1999-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1999-08-27 |
| Bowser   | 1998-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
+----------+------------+
8 rows in set (0.01 sec)

Data 정렬

결과를 정렬하고 싶을 때는, ORDER BY 절을 사용한다.

예를 들어, 동물의 생일이 날짜 순으로 정렬된 결과를 원하면:

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Bowser   | 1998-08-31 |
| Chirpy   | 1998-09-11 |
| Fluffy   | 1999-02-04 |
| Fang     | 1999-08-27 |
+----------+------------+
8 rows in set (0.02 sec)

역순 정렬을 해야 할 때에는 DESC (descending keyword)를 붙혀주면 된다.

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fang     | 1999-08-27 |
| Fluffy   | 1999-02-04 |
| Chirpy   | 1998-09-11 |
| Bowser   | 1998-08-31 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
+----------+------------+
8 rows in set (0.02 sec)

NULL 다루기

NULL 이 의미하는 것은 빈 값 또는 알 수없는 값이다.
NULL인지 아닌지 확인 하기 위해, =, < 또는 <> 와 같은 산술비교 연산자를 사용할 수 없다.
대신에, IS NULL 그리고 IS NOT NULL 연산자를 사용해야 한다.

예제 - 죽은 애완동물 조회

mysql> select name
     >   from pet
     >  where death IS NOT NULL;

+--------+
| name   |
+--------+
| Bowser |
+--------+
1 row in set (0.01 sec)

패턴 매칭

MySQL 기본적으로 제공하는 것:
- 표준 SQL pattern matching
- 정규표현식 pattern matching,
  vi, grep, sed와 같은 Unix에서의 유틸리티에서의 그 것과 같다.
SQL Pattern matching:
- LIKE or NOT LIKE 비교 연산자를 사용해서 패턴매칭을 한다.
- 기본적으로 영문자인 경우 대소문자 구별을 안한다.
특수문자:

"_" 는 한문자에 대응한다.
"%" 여러문자열과 대응하게 된다.

패턴 매칭 예제

'b'로 시작하는 이름의 동물 조회:

mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

'fy'로 끝나는 동물 조회:

mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

'w'가 포함된 이름의 동물 조회:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

pattern character _ 를 사용해서 정확히 5문자 이름의 동물 조회:

mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Row 카운팅(counting)

테이블에 어떤 특정 조건의 데이터가 어떤 빈도로 나타나는 가에 대한 Databases의 응답이다.
예를 들어, 각 주인들이 몇 마리의 애완동물을 가지고 있는가를 알고 싶을 때
애완동물의 총 수는 테이블의 전체 row수이다. 왜냐하면 애완동물 한 마리당 하나의 레코드를 가지기 때문이다.
COUNT() function counts NULL이 아닌 결과의 수이다.

전체 애완동물 수를 산정하는 쿼리:

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

새로운 사용자 추가 예제

mysql>         grant all privileges 
     >            on webdb.* 
     >            to dev@localhost
     > identified by 'dev';
Query OK, 0 rows affected (0.04 sec)

관리자 추가 예제

mysql>         grant all privileges 
     >            on *.* 
     >            to admin@'%'
     > identified by 'admin';
Query OK, 0 rows affected (0.04 sec)

mysql Database의 user, db, host 각 테이블에 insert 구문 실행
flush privileges 필요

사용자 삭제

mysql>drop user dev;
Query OK, 0 rows affected (0.04 sec)

'Dev > Mysql(MariaDB)' 카테고리의 다른 글

MariaDB - 데이터 검색 (SUBQUERY) (0)	2021.10.05
MariaDB - 데이터 검색 (JOIN) (0)	2021.10.05
MariaDB - 데이터 검색 (날짜, 문자, 숫자, 집계) (0)	2021.10.01
RDBMS (0)	2021.09.30
WorkBanch(워크벤치) 설치 및 RDBMS 의 시작 DCL 해보기. (0)	2021.09.29

현재글MariaDB 기본(설치) 및 기본 SQL 연습

OK-GO

Developer git 주소 : https://github.com/stpn94

깃 허브, mvc1, 헬린이, DAO, servlet, jdbc, MySQL, json, mybatis, db연결, 어노테이션, 예외처리, JDBC연결, jsp, 데이터검색, mariadb, MVC, java, mvc2, Spring,

Today :
Yesterday :

OK-GO