MySQL Admin - 01

 

현재 MySQL에 대해 제공되는 매뉴얼을 통해 공부해 나가는 중이다.

아래는 매뉴얼을 통해 익혀 나가는 부분을 정리 및 캡쳐한 내용이다.

필요로 하는 분들에게 도움이 되었으면 한다.

 

MySQL에 접속하는 방법

 

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -h localhost -u root -p
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

 

MySQL 인스턴스의 상태정보를 확인하는 방법

mysql> status
--------------
mysql  Ver 8.0.17 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          20
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         8.0.17 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    euckr
Conn.  characterset:    euckr
TCP port:               3306
Uptime:                 2 hours 35 min 31 sec

Threads: 6  Questions: 3259  Slow queries: 0  Opens: 222  Flush tables: 3  Open tables: 142  Queries per second avg: 0.349
--------------

mysql> 

 

유저, 시간, database 확인

 

mysql> select version(), current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 8.0.17    | 2019-10-08   |
+-----------+--------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-10-08 16:10:43 |
+---------------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.02 sec)

mysql>

 

database 생성 및 사용

 

mysql> create database mpdbdiag;
Query OK, 1 row affected (0.04 sec)

mysql> use mpdbdiag;
Database changed
mysql>

 

테이블 생성

 

mysql> CREATE TABLE pet
    -> (name VARCHAR(20)
    -> ,owner VARCHAR(20)
    -> ,species VARCHAR(20)
    -> ,sex CHAR(1)
    -> ,birth DATE
    -> ,death DATE);
Query OK, 0 rows affected (0.05 sec)

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

mysql>

 

NULL 값에 대한 연산 결과

 

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+
1 row in set (0.00 sec)

mysql>

 

MySQL에서 0과 NULL은 false를 의미하고 그 이외의 값은 true를 의미한다. 기본적으로 boolean operation의 true 값은 1이다.