MySQLコマンド
をテンプレートにして作成
[
トップ
] [
新規
|
一覧
|
検索
|
最終更新
|
ヘルプ
|
ログイン
]
開始行:
#author("2020-04-07T16:00:50+00:00","","")
[[hidekiwiki]]
[[hidekiwiki]]
#html{{
<meta name="description" content="https://fukumoto.tokyo/...
<meta name="keywords" content="MySQL,データベース,コマン...
<link rel="canonical" href="https://fukumoto.tokyo/wiki/"...
<br>
<!-- admax -->
<script src="http://adm.shinobi.jp/s/1a0429ab1f292195e9a4...
<!-- 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ユーザ作...
mysql> grant all privileges on test.* to matsui@localhos...
**ユーザ削除 [#v0f26f9a]
matuiユーザを削除
mysql> delete from mysql.user where user='matsui' and ho...
**データベース作成 [#m21f2b82]
mysql> create database test;
**データベース削除 [#x1f28f1e]
mysql> create table test;
**テーブル情報を表示(DESCRIBE) [#mfd04952]
DESCRIBE データベース名.テーブル名 ;
または
DESC データベース名.テーブル名 ;
-例
mysql> DESCRIBE mysql.user;
+-----------------------+-------------------------------...
| Field | Type ...
+-----------------------+-------------------------------...
| Host | varchar(60) ...
| User | varchar(16) ...
| Password | varchar(16) ...
| Select_priv | enum('N','Y') ...
| Insert_priv | enum('N','Y') ...
| Update_priv | enum('N','Y') ...
| Delete_priv | enum('N','Y') ...
| Create_priv | enum('N','Y') ...
| Drop_priv | enum('N','Y') ...
| Reload_priv | enum('N','Y') ...
| Shutdown_priv | enum('N','Y') ...
| Process_priv | enum('N','Y') ...
| File_priv | enum('N','Y') ...
| Grant_priv | enum('N','Y') ...
| References_priv | enum('N','Y') ...
| Index_priv | enum('N','Y') ...
| Alter_priv | enum('N','Y') ...
| Show_db_priv | enum('N','Y') ...
| Super_priv | enum('N','Y') ...
| Create_tmp_table_priv | enum('N','Y') ...
| Lock_tables_priv | enum('N','Y') ...
| Execute_priv | enum('N','Y') ...
| Repl_slave_priv | enum('N','Y') ...
| Repl_client_priv | enum('N','Y') ...
| ssl_type | enum('','ANY','X509','SPECIFIE...
| ssl_cipher | blob ...
| x509_issuer | blob ...
| x509_subject | blob ...
| max_questions | int(11) unsigned ...
| max_updates | int(11) unsigned ...
| max_connections | int(11) unsigned ...
+-----------------------+-------------------------------...
31 rows in set (0.01 sec)
**文字コードなど、ステータス確認 [#w7261e70]
mysql> STATUS
--------------
mysql Ver 14.14 Distrib 5.1.28-rc, for portbld-freebsd6...
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: mysq...
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: ...
--------------
**文字コード関係 [#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)
終了行:
#author("2020-04-07T16:00:50+00:00","","")
[[hidekiwiki]]
[[hidekiwiki]]
#html{{
<meta name="description" content="https://fukumoto.tokyo/...
<meta name="keywords" content="MySQL,データベース,コマン...
<link rel="canonical" href="https://fukumoto.tokyo/wiki/"...
<br>
<!-- admax -->
<script src="http://adm.shinobi.jp/s/1a0429ab1f292195e9a4...
<!-- 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ユーザ作...
mysql> grant all privileges on test.* to matsui@localhos...
**ユーザ削除 [#v0f26f9a]
matuiユーザを削除
mysql> delete from mysql.user where user='matsui' and ho...
**データベース作成 [#m21f2b82]
mysql> create database test;
**データベース削除 [#x1f28f1e]
mysql> create table test;
**テーブル情報を表示(DESCRIBE) [#mfd04952]
DESCRIBE データベース名.テーブル名 ;
または
DESC データベース名.テーブル名 ;
-例
mysql> DESCRIBE mysql.user;
+-----------------------+-------------------------------...
| Field | Type ...
+-----------------------+-------------------------------...
| Host | varchar(60) ...
| User | varchar(16) ...
| Password | varchar(16) ...
| Select_priv | enum('N','Y') ...
| Insert_priv | enum('N','Y') ...
| Update_priv | enum('N','Y') ...
| Delete_priv | enum('N','Y') ...
| Create_priv | enum('N','Y') ...
| Drop_priv | enum('N','Y') ...
| Reload_priv | enum('N','Y') ...
| Shutdown_priv | enum('N','Y') ...
| Process_priv | enum('N','Y') ...
| File_priv | enum('N','Y') ...
| Grant_priv | enum('N','Y') ...
| References_priv | enum('N','Y') ...
| Index_priv | enum('N','Y') ...
| Alter_priv | enum('N','Y') ...
| Show_db_priv | enum('N','Y') ...
| Super_priv | enum('N','Y') ...
| Create_tmp_table_priv | enum('N','Y') ...
| Lock_tables_priv | enum('N','Y') ...
| Execute_priv | enum('N','Y') ...
| Repl_slave_priv | enum('N','Y') ...
| Repl_client_priv | enum('N','Y') ...
| ssl_type | enum('','ANY','X509','SPECIFIE...
| ssl_cipher | blob ...
| x509_issuer | blob ...
| x509_subject | blob ...
| max_questions | int(11) unsigned ...
| max_updates | int(11) unsigned ...
| max_connections | int(11) unsigned ...
+-----------------------+-------------------------------...
31 rows in set (0.01 sec)
**文字コードなど、ステータス確認 [#w7261e70]
mysql> STATUS
--------------
mysql Ver 14.14 Distrib 5.1.28-rc, for portbld-freebsd6...
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: mysq...
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: ...
--------------
**文字コード関係 [#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)
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
