초보자를 위한 MySQL/MariaDB 배우기 - 1부
이 문서에서는 데이터베이스(스키마라고도 함), 테이블(데이터 유형 포함)을 생성하는 방법을 보여주고 데이터 조작 언어(DML)를 수행하는 방법을 설명합니다. ) MySQL/MariaDB 서버의 데이터를 사용한 작업입니다.
이전에 1) Linux 시스템에 필요한 패키지를 설치했으며 2) mysql_secure_installation을 실행하여 데이터베이스 서버의 보안을 강화했다고 가정합니다. . 그렇지 않은 경우 아래 지침에 따라 MySQL/MariaDB 서버를 설치하십시오.
- Linux 시스템에 최신 MySQL 데이터베이스 설치
- Linux 시스템에 최신 MariaDB 데이터베이스 설치
간결함을 위해 이 문서 전체에서는 MariaDB만을 언급하지만 여기에 설명된 개념과 명령은 MySQL에도 적용됩니다.
데이터베이스, 테이블 및 승인된 사용자 생성
아시다시피 데이터베이스는 조직화된 정보 모음으로 간단한 용어로 정의할 수 있습니다. 특히 MariaDB는 관계형 데이터베이스 관리 시스템(RDBMS)이며 구조 쿼리 언어를 사용하여 데이터베이스 작업을 수행합니다. 또한 MariaDB는 데이터베이스와 스키마라는 용어를 같은 의미로 사용한다는 점을 기억하세요.
지속적인 정보를 데이터베이스에 저장하기 위해 데이터 행을 저장하는 테이블을 사용합니다. 두 개 이상의 테이블이 어떤 방식으로든 서로 관련되는 경우가 많습니다. 이는 관계형 데이터베이스의 사용을 특징짓는 조직의 일부입니다.
새 데이터베이스 생성
BooksDB라는 새 데이터베이스를 생성하려면 다음 명령을 사용하여 MariaDB 프롬프트를 입력하세요(루트 MariaDB 사용자의 비밀번호를 입력하라는 메시지가 표시됩니다).
[root@TecMint ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
데이터베이스가 생성되면 해당 데이터베이스에 최소 두 개의 테이블을 생성해야 합니다. 하지만 먼저 데이터 유형의 개념을 살펴보겠습니다.
MariaDB 데이터 유형 소개
앞서 설명했듯이 테이블은 지속적인 정보를 보관할 데이터베이스 개체입니다. 각 테이블은 해당 필드가 저장할 수 있는 특정 데이터 유형(정보 유형)의 두 개 이상의 필드(열라고도 함)로 구성됩니다.
MariaDB에서 가장 일반적인 데이터 유형은 다음과 같습니다(공식 MariaDB 온라인 문서에서 전체 목록을 참조할 수 있습니다).
숫자 :
- BOOLEAN은 0을 false로 간주하고 다른 모든 값을 true로 간주합니다.
- TINYINT를 SIGNED와 함께 사용하면 -128~127 범위를 포함하는 반면 UNSIGNED 범위는 0~255입니다.
- SMALLINT를 SIGNED와 함께 사용하는 경우 -32768부터 32767까지의 범위를 포함합니다. UNSIGNED 범위는 0부터 65535까지입니다.
- INT는 UNSIGNED와 함께 사용되는 경우 0~4294967295 범위를 포함하고 그렇지 않은 경우 -2147483648~2147483647 범위를 포함합니다.
참고: TINYINT, SMALLINT 및 INT에서는 기본 SIGNED가 가정됩니다.
DOUBLE(M, D), 여기서 M은 총 자릿수이고 D는 소수점 이하 자릿수입니다. 배정밀도 부동 소수점 수. UNSIGNED를 지정하면 음수 값이 허용되지 않습니다.
끈 :
- VARCHAR(M)은 가변 길이의 문자열을 나타냅니다. 여기서 M은 허용되는 최대 열 길이(바이트)입니다(이론적으로는 65,535바이트). 대부분의 경우 바이트 수는 최대 3바이트를 차지할 수 있는 일부 문자를 제외하고는 문자 수와 동일합니다. 예를 들어, 스페인어 문자 ñ는 한 문자를 나타내지만 2바이트를 차지합니다.
- TEXT(M)은 최대 길이가 65,535자인 열을 나타냅니다. 그러나 VARCHAR(M)의 경우와 마찬가지로 멀티바이트 문자를 저장하면 실제 최대 길이가 줄어듭니다. M을 지정하면 해당 문자 수를 저장할 수 있는 가장 작은 유형으로 컬럼이 생성됩니다.
- MEDIUMTEXT(M) 및 LONGTEXT(M)은 TEXT(M)과 유사하지만 허용되는 최대 길이는 16,777,215자 및 4,294,967,295자입니다. 각기.
날짜와 시간:
- DATE는 YYYY-MM-DD 형식의 날짜를 나타냅니다.
- TIME은 HH:MM:SS.sss 형식(시, 분, 초, 밀리초)으로 시간을 나타냅니다.
- DATETIME은 YYYY-MM-DD HH:MM:SS 형식의 DATE와 TIME의 조합입니다.
- TIMESTAMP는 행이 추가되거나 업데이트된 순간을 정의하는 데 사용됩니다.
이러한 데이터 유형을 검토한 후에는 테이블의 특정 열에 할당해야 하는 데이터 유형을 결정하는 데 더 나은 위치에 있게 됩니다.
예를 들어, 사람의 이름은 VARCHAR(50)에 쉽게 들어갈 수 있는 반면, 블로그 게시물에는 TEXT 유형이 필요합니다(M을 선택하세요). 귀하의 특정 요구에 따라).
기본 키와 외래 키를 사용하여 테이블 생성
테이블 생성에 대해 알아보기 전에 검토해야 할 관계형 데이터베이스에 대한 두 가지 기본 개념, 즉 기본 키와 외부 키가 있습니다.
기본 키에는 테이블의 각 행 또는 레코드를 고유하게 식별하는 값이 포함됩니다. 한편, 외래 키는 두 테이블의 데이터 사이를 연결하고, 외래 키가 위치한 테이블에 저장할 수 있는 데이터를 제어하는 데 사용됩니다. 기본 키와 외래 키는 모두 일반적으로 INT입니다.
설명을 위해 BookstoreDB를 사용하고 다음과 같이 AuthorsTBL 및 BooksTBL이라는 두 개의 테이블을 생성해 보겠습니다. NOT NULL 제약조건은 연결된 필드에 NULL 이외의 값이 필요함을 나타냅니다.
또한 AUTO_INCREMENT는 새 레코드가 테이블에 삽입될 때 INT 기본 키 열의 값을 1씩 증가시키는 데 사용됩니다.
MariaDB [(none)]> USE BookstoreDB;
MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);
MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
-> AuthorID INT NOT NULL AUTO_INCREMENT,
-> AuthorName VARCHAR(100),
-> PRIMARY KEY(AuthorID)
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
-> BookID INT NOT NULL AUTO_INCREMENT,
-> BookName VARCHAR(100) NOT NULL,
-> AuthorID INT NOT NULL,
-> BookPrice DECIMAL(6,2) NOT NULL,
-> BookLastUpdated TIMESTAMP,
-> BookIsAvailable BOOLEAN,
-> PRIMARY KEY(BookID),
-> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [BookstoreDB]>
이제 AuthorsTBL 및 BooksTBL에 레코드 삽입을 시작할 수 있습니다.
행 선택, 삽입, 업데이트 및 삭제
먼저 AuthorsTBL 테이블을 채웁니다. 왜? BooksTBL에 레코드를 삽입하기 전에 AuthorID에 대한 값이 필요하기 때문입니다.
MariaDB 프롬프트에서 다음 쿼리를 실행합니다.
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
그런 다음 AuthorsTBL에서 모든 레코드를 선택합니다. BooksTBL에 대한 INSERT 쿼리를 생성하려면 각 레코드에 대한 AuthorID가 필요하다는 점을 기억하세요.
한 번에 하나의 레코드를 검색하려는 경우 WHERE 절을 사용하여 행이 반환되기 위해 충족해야 하는 조건을 나타낼 수 있습니다. 예를 들어,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
또는 모든 레코드를 동시에 선택할 수 있습니다.
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName |
+----------+-----------------+
| 1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName |
+----------+-----------------+
| 1 | Agatha Christie |
| 2 | Stephen King |
| 3 | Paulo Coelho |
+----------+-----------------+
3 rows in set (0.00 sec)
MariaDB [BookstoreDB]>
이제 각 책의 저자와 일치하는 해당 AuthorID를 사용하여 BooksTBL에 대한 INSERT 쿼리를 만들어 보겠습니다. BookIsAvailable의 1 값은 책의 재고가 있음을 나타내고, 그렇지 않은 경우 0은 다음과 같습니다.
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
-> VALUES ('And Then There Were None', 1, 14.95, 1),
-> ('The Man in the Brown Suit', 1, 23.99, 1),
-> ('The Stand', 2, 35.99, 1),
-> ('Pet Sematary', 2, 17.95, 0),
-> ('The Green Mile', 2, 29.99, 1),
-> ('The Alchemist', 3, 25, 1),
-> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
이 시점에서 BooksTBL의 레코드를 보기 위해 SELECT를 수행합니다. 그런 다음 Paulo Coelho의 'The Alchemist' 가격을 업데이트하고 해당 레코드를 다시 선택해 보겠습니다.
BookLastUpdated 필드가 이제 어떻게 다른 값을 표시하는지 확인하세요. 앞서 설명했듯이 TIMESTAMP 필드에는 레코드가 삽입되거나 마지막으로 수정된 시점의 값이 표시됩니다.
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName | AuthorID | BookPrice | BookLastUpdated | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| 1 | And Then There Were None | 1 | 14.95 | 2016-10-01 23:31:41 | 1 |
| 2 | The Man in the Brown Suit | 1 | 23.99 | 2016-10-01 23:31:41 | 1 |
| 3 | The Stand | 2 | 35.99 | 2016-10-01 23:31:41 | 1 |
| 4 | Pet Sematary | 2 | 17.95 | 2016-10-01 23:31:41 | 0 |
| 5 | The Green Mile | 2 | 29.99 | 2016-10-01 23:31:41 | 1 |
| 6 | The Alchemist | 3 | 25.00 | 2016-10-01 23:31:41 | 1 |
| 7 | By the River Piedra I Sat Down and Wept | 3 | 18.95 | 2016-10-01 23:31:41 | 0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName | AuthorID | BookPrice | BookLastUpdated | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
| 6 | The Alchemist | 3 | 22.75 | 2016-10-01 23:35:00 | 1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]>
여기서는 다루지 않겠지만 더 이상 사용하지 않는 경우 레코드를 삭제할 수도 있습니다. 예를 들어 BooksTBL에서 'The Alchemist'를 삭제한다고 가정해 보겠습니다.
이를 위해 다음과 같이 DELETE 문을 사용합니다.
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
UPDATE의 경우와 마찬가지로 의 영향을 받을 가능성이 있는 레코드를 보려면 SELECT를 먼저 수행하는 것이 좋습니다. 삭제.
또한 제거할 특정 레코드를 선택하려면 WHERE 절과 조건 (BookID=6)을 추가하는 것을 잊지 마세요. 그렇지 않으면 테이블의 모든 행이 삭제될 위험이 있습니다!
두 개 이상의 필드를 연결하려면 CONCAT 문을 사용할 수 있습니다. 예를 들어, "The Alchemist (Paulo Coelho)" 형식의 책 이름과 저자가 포함된 하나의 필드와 가격이 포함된 다른 열로 구성된 결과 집합을 반환한다고 가정해 보겠습니다.
이를 위해서는 두 테이블(AuthorID)이 공유하는 공통 필드에서 AuthorsTBL과 BooksTBL 사이에 JOIN이 필요합니다.
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
보시다시피 CONCAT을 사용하면 쉼표로 구분된 여러 문자열 표현식을 결합할 수 있습니다. 또한 연결 결과 집합을 나타내기 위해 별칭 설명을 선택했음을 알 수 있습니다.
위 쿼리의 출력은 아래 이미지에 표시됩니다.
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie) | 14.95 |
| The Man in the Brown Suit (Agatha Christie) | 23.99 |
| The Stand (Stephen King) | 35.99 |
| Pet Sematary (Stephen King) | 17.95 |
| The Green Mile (Stephen King) | 29.99 |
| The Alchemist (Paulo Coelho) | 25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) | 18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)
BookstoreDB 데이터베이스에 액세스하기 위한 사용자 생성
루트를 사용하여 데이터베이스의 모든 DML 작업을 수행하는 것은 좋지 않습니다. 이를 방지하려면 새 MariaDB 사용자 계정을 만들고(이름을 bookstoreuser로 지정) BookstoreDB에 필요한 모든 권한을 할당할 수 있습니다.
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
각 데이터베이스에 별도의 전용 사용자를 지정하면 단일 계정이 손상될 경우 전체 데이터베이스가 손상되는 것을 방지할 수 있습니다.
추가 MySQL 팁
MariaDB 프롬프트를 지우려면 다음 명령을 입력하고 Enter를 누르세요.
MariaDB [BookstoreDB]> \! clear
특정 테이블의 구성을 검사하려면 다음을 수행하십시오.
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
예를 들어,
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID | int(11) | NO | PRI | NULL | auto_increment |
| BookName | varchar(100) | NO | | NULL | |
| AuthorID | int(11) | NO | MUL | NULL | |
| BookPrice | decimal(6,2) | NO | | NULL | |
| BookLastUpdated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1) | YES | | NULL | |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)
빠르게 검사해 보면 BookIsAvailable 필드가 NULL 값을 허용한다는 것을 알 수 있습니다. 이를 허용하고 싶지 않으므로 다음과 같이 테이블을 ALTER하겠습니다.
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(열을 다시 표시해도 됩니다. 위 이미지에서 강조표시된 YES는 이제 NO가 되어야 합니다.)
마지막으로 서버의 모든 데이터베이스를 보려면 다음을 수행하십시오.
MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| BookstoreDB |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database |
+--------------------+
| BookstoreDB |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
다음 이미지는 bookstoreuser로 MariaDB 프롬프트에 액세스한 후 위 명령의 결과를 보여줍니다. (이 계정은 BookstoreDB 및 < 이외의 데이터베이스를 "볼" 수 없다는 점에 유의하세요. Strong>information_schema(모든 사용자가 사용 가능):
요약
이 문서에서는 DML 작업을 실행하는 방법과 MariaDB 데이터베이스에서 데이터베이스, 테이블 및 전용 사용자를 생성하는 방법을 설명했습니다. 또한 시스템/데이터베이스 관리자로서의 삶을 더 쉽게 만들어 줄 수 있는 몇 가지 팁을 공유했습니다.
- MySQL 데이터베이스 관리 파트 – 1
- MySQL 데이터베이스 관리 파트 – 2
- MySQL 성능 조정 및 최적화 – 3부
이 기사에 대해 궁금한 점이 있으면 주저하지 말고 알려주십시오! 아래 의견 양식을 사용하여 언제든지 문의해 주세요.