#author("2020-04-07T15:59:36+00:00","","")
[[hidekiwiki]]
#author("2020-04-07T16:00:23+00:00","","")

#html{{
<meta name="description" content="https://fukumoto.tokyo/wiki/" />&#160;
<meta name="keywords" content="MySQL,データベース,コマンド,変更,root,設定,作成,削除,追加,ユーザ,操作" />&#160;
<link rel="canonical" href="https://fukumoto.tokyo/wiki/" />
<br>
<!-- admax -->
<script src="http://adm.shinobi.jp/s/1a0429ab1f292195e9a461469817a0ca"></script>
<!-- admax -->
</html>
}}

*MySQLコマンド [#m54922fe]

#contents

**ユーザの確認 [#o3a9f6f9]
 mysql> select host,user,password from mysql.user;
 +-------------+--------------+------------------+
 | host        | user         | password         |
 +-------------+--------------+------------------+
 | localhost   | root         | 284e59d71f64e3ec |
 | localhost   | ueda         | 423da20727393e52 |
 | localhost   | vpopmailuser | 7d60b94d42e45b72 |
 | localhost   | xoops        | 3cf31278467575ed |
 | localhost   | stats        | 3cf31278467575ed |
 | localhost   | cappuccino   | 7cd2b5942be28759 |
 | localhost   | ecommerce    | 3cf31278467575ed |
 | 192.168.1.% | vpopmailuser | 7d60b94d42e45b72 |
 +-------------+--------------+------------------+
 8 rows in set (0.00 sec)

**ユーザ追加 [#da093391]
testデータベースに対して全ての権限を持ったmatsuiユーザ作成、パスワードはpass
 mysql> grant all privileges on test.* to matsui@localhost identified by 'pass';

**ユーザ削除 [#v0f26f9a]
matuiユーザを削除
 mysql> delete from mysql.user where user='matsui' and host='localhost';

**データベース作成 [#m21f2b82]
 mysql> create database test;

**データベース削除 [#x1f28f1e]
 mysql> create table test;

**テーブル情報を表示(DESCRIBE) [#mfd04952]
 DESCRIBE データベース名.テーブル名 ;
または
 DESC データベース名.テーブル名 ;
-例
 mysql> DESCRIBE mysql.user;
 +-----------------------+-----------------------------------+------+-----+---------+-------+
 | Field                 | Type                              | Null | Key | Default | Extra |
 +-----------------------+-----------------------------------+------+-----+---------+-------+
 | Host                  | varchar(60)                       | NO   | PRI |         |       | 
 | User                  | varchar(16)                       | NO   | PRI |         |       | 
 | Password              | varchar(16)                       | NO   |     |         |       | 
 | Select_priv           | enum('N','Y')                     | NO   |     | N       |       | 
 | Insert_priv           | enum('N','Y')                     | NO   |     | N       |       | 
 | Update_priv           | enum('N','Y')                     | NO   |     | N       |       | 
 | Delete_priv           | enum('N','Y')                     | NO   |     | N       |       | 
 | Create_priv           | enum('N','Y')                     | NO   |     | N       |       | 
 | Drop_priv             | enum('N','Y')                     | NO   |     | N       |       | 
 | Reload_priv           | enum('N','Y')                     | NO   |     | N       |       | 
 | Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       | 
 | Process_priv          | enum('N','Y')                     | NO   |     | N       |       | 
 | File_priv             | enum('N','Y')                     | NO   |     | N       |       | 
 | Grant_priv            | enum('N','Y')                     | NO   |     | N       |       | 
 | References_priv       | enum('N','Y')                     | NO   |     | N       |       | 
 | Index_priv            | enum('N','Y')                     | NO   |     | N       |       | 
 | Alter_priv            | enum('N','Y')                     | NO   |     | N       |       | 
 | Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       | 
 | Super_priv            | enum('N','Y')                     | NO   |     | N       |       | 
 | Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       | 
 | Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       | 
 | Execute_priv          | enum('N','Y')                     | NO   |     | N       |       | 
 | Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       | 
 | Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       | 
 | ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       | 
 | ssl_cipher            | blob                              | NO   |     | NULL    |       | 
 | x509_issuer           | blob                              | NO   |     | NULL    |       | 
 | x509_subject          | blob                              | NO   |     | NULL    |       | 
 | max_questions         | int(11) unsigned                  | NO   |     | 0       |       | 
 | max_updates           | int(11) unsigned                  | NO   |     | 0       |       | 
 | max_connections       | int(11) unsigned                  | NO   |     | 0       |       | 
 +-----------------------+-----------------------------------+------+-----+---------+-------+
 31 rows in set (0.01 sec)

**文字コードなど、ステータス確認 [#w7261e70]
 mysql> STATUS
 --------------
 mysql  Ver 14.14 Distrib 5.1.28-rc, for portbld-freebsd6.3 (i386) using  5.2
 
 Connection id:          104553
 Current database:       syslog
 Current user:           root@localhost
 SSL:                    Not in use
 Current pager:          stdout
 Using outfile:          ''
 Using delimiter:        ;
 Server version:         5.1.28-rc-log FreeBSD port: mysql-server-5.1.28
 Protocol version:       10
 Connection:             Localhost via UNIX socket
 Server characterset:    ujis
 Db     characterset:    ujis
 Client characterset:    ujis
 Conn.  characterset:    ujis
 UNIX socket:            /tmp/mysql.sock
 Uptime:                 15 days 11 hours 10 min 32 sec
 
 Threads: 2  Questions: 2293516  Slow queries: 0  Opens: 14189  Flush tables: 22  Open tables: 64  Queries per second avg: 1.716
 --------------

**文字コード関係 [#u7c1582a]

***現在の環境確認 [#m6da045e]
 SHOW VARIABLES LIKE 'character%';

***データベースの文字コード設定を調べる [#bba52507]
 mysql> show create database データベース名;

***文字コードを指定してDB作成 [#r92dae4a]
 CREATE DATABASE hoge CHARACTER SET SJIS;

***テーブルの文字コード設定を調べる [#n7cba00d]
 mysql> show create table テーブル名 \G 

***文字コードを変更する。 [#kf4c42a6]
 SET NAMES 'sjis';
 
 使える文字コード
 sjis
 ujis
 utf8

**リフレッシュ [#fbced8fb]
 mysql> FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.05 sec)


トップ   編集 差分 履歴 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS