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이다.
