データベース - MySQL - よく使うコマンド

 クラウディア
1. 概要
2. データベース一覧
3. テーブル一覧
4. カラム一覧
5. 文字コードを調べる
6. データベースの作成
7. ユーザ作成
8. ユーザ一覧

1. 概要

 これは わたしが よく使うコマンドです。お間違えのないよう。  以下、FreeBSD にインストールしている MySQL を操作するときの記述です。

2. データベース一覧


SHOW DATABASES;
 実際に見てみます。

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2778
Server version: 5.6.26 Source distribution

Copyright (c) 2000, 2015, 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> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myhome             |
| mysql              |
| performance_schema |
| test               |
| wordpress          |
| xoops              |
+--------------------+
7 rows in set (0.00 sec)

mysql> quit
Bye

3. テーブル一覧


SHOW TABLES FROM データベース名;
 実際に見てみます。

$ mysql -u root -p
Enter password:				←	パスワードを入力
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log Source distribution

Copyright (c) 2000, 2018, 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.

root@localhost [(none)]> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

4. カラム一覧


SHOW COLUMNS FROM テーブル名;
 実際に見てみます。

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21-log Source distribution

Copyright (c) 2000, 2018, 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.

root@localhost [(none)]> use mysql
Database changed
root@localhost [mysql]> SHOW COLUMNS FROM user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |

	・・・

| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

root@localhost [mysql]> quit
Bye

5. 文字コードを調べる

 mySQL が動作している状態で mysql

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.26 Source distribution

Copyright (c) 2000, 2015, 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 のプロンプトが表示されたところで status

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.26, for FreeBSD10.2 (i386) using  EditLine wrapper

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.26 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 52 min 13 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.003
--------------
 とまあこんな感じです。

6. データベースの作成

 以下、すべて mySQL のクライアントインタフェース上ですが・・・。  データベースの作成

mysql> CREATE DATABASE データベース名;
 ユーザにデータベースへの権限を与える

mysql> GRANT ALL ON データベース名.テーブル名 TO ユーザ名;

7. ユーザ作成

 単に、ユーザを作成するならば、「root」ユーザで、「mysql」を起動して

CREATE USER 'ユーザ名'@'localhost' IDENTIFIED BY 'パスワード';
 データベースの作成とともに、オーナーを設定することもできます。

CREATE DATABASE データベース名;
GRANT ALL PRIVILEGES ON データベース名.* to ユーザ名@localhost IDENTIFIED BY 'パスワード';

8. ユーザ一覧


SELECT HOST, USER, PASSWORD FROM MYSQL.USER;
 「root」で、ログインする必要があるようです。

> mysql -u root -p
Enter password:												←	パスワードを入力
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 606
Server version: 5.7.24-log Source distribution

Copyright (c) 2000, 2018, 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> select Host, User, Password from mysql.user;
+---------------+------------+-------------------------------------------+
| Host          | User       | Password                                  |
+---------------+------------+-------------------------------------------+
| localhost     | root       |                                           |
| ns.sing.ne.jp | root       |                                           |
| 127.0.0.1     | root       |                                           |
| ::1           | root       |                                           |
| localhost     |            |                                           |
| ns.sing.ne.jp |            |                                           |
| localhost     | ユーザ名   | *ちゃい                                   |
+---------------+------------+-------------------------------------------+
11 rows in set (0.13 sec)

ハイスピードプラン弁当損保との違い
 
 
カウンタバックグラウンド