MySQLコマンド

①ユーザ関係

ユーザの確認

mysql> select host,user,Grant_priv from mysql.user;
+---------------------+-------------+------------------+------------+
| host                | user        | password         | Grant_priv |
+---------------------+-------------+------------------+------------+
| localhost           | root        | 284e59d71f64e3ec | Y          | 
| cloud.flateight.com | root        |                  | Y          | 
| 127.0.0.1           | root        |                  | Y          | 
| localhost           |             |                  | N          | 
| cloud.flateight.com |             |                  | N          | 
| localhost           | postfix     | 3ec7dddf23d89d39 | N          | 
| localhost           | matsukaze   | 7f9f27246ddab376 | N          | 
| localhost           | stats       | 3cf31278467575ed | N          | 
| localhost           | nagata      | 423da20727393e52 | N          | 
| localhost           | cacti       | 6d43317a3705f19e | N          | 
| localhost           | iline8      | 6e4d585d01239c27 | N          | 
| localhost           | eccube_user | 284e59d71f64e3ec | N          | 
+---------------------+-------------+------------------+------------+
12 rows in set (0.00 sec)

権限の確認

mysql> SHOW GRANTS FOR testdb_user@localhost;
+----------------------------------------------------------------------------------------------------+
| Grants for testdb_user@localhost                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'testdb_user'@'localhost' IDENTIFIED BY PASSWORD '370f8eb673c9467f' | 
+----------------------------------------------------------------------------------------------------+

権限の追加

グローバルレベル
GRANT ALL ON *.* TO user;
GRANT SELECT, INSERT ON *.* TO user;

データベースレベル
GRANT ALL ON db_name.* TO user;
GRANT SELECT, INSERT ON mydb.* TO user;

権限の削除

mysql> revoke all on *.* from  'testdb_user'@'localhost';

DBに設定されたユーザの確認

mysql> select host, db, user from mysql.db;
+-------------+-----------+-----------+
| host        | db        | user      |
+-------------+-----------+-----------+
| %           | test      |           | 
| %           | test\_%   |           | 
| 172.16.0.3  | mc_portal | mc_portal | 
| 172.20.0.35 | mc_diem   | mc_portal | 
+-------------+-----------+-----------+
4 rows in set (0.00 sec)

ユーザ作成

CREATE USER 'matsui'@'localhost' IDENTIFIED BY 'pass';

ユーザに権限を付ける

testデータベースに対して全ての権限を持ったmatsuiユーザ作成、パスワードはpass

  mysql> grant all privileges on test.* to 'matsui'@'localhost' identified by 'pass';

ユーザに権限を付ける（ユーザ作成権限付き）

※CREATE USER はユーザーの作成に使用されます。

  mysql> grant all privileges on test.* to matsui@localhost identified by 'pass' with grant option;

ユーザ追加（read only）

  mysql> GRANT SELECT ON *.* TO 'readonly_user'@'localhost' IDENTIFIED BY 'pass';

ユーザ削除

matuiユーザを削除

  mysql> delete from mysql.user where user='matsui' and host='localhost';

こっちの方が簡単

mysql> DROP USER 'matsui'@'localhost';

ユーザ名＆ホストの変更

mysql> RENAME USER 'FROM_USER'@'192.168.0.%' TO 'TO_USER'@'192.168.1.%';

②データベース関連

データベース作成

  mysql> create database test;

データベース削除

  mysql> create table test;

データベースのコピー

ダンプしたものを、コピーしたいDBにパイプで繋げてあげる。

  # mysqldump sourceDB | mysql destinationDB

データベース名の変更

※データベース名の変更はできないので、create databaseして、データベースをコピーする事で対応する。

  mysql> create database 新データベース名 character set utf8;
  
  # mysqldump 旧データベース名 | mysql 新データベース名

③テーブル関連

テーブル作成

CREATE TABLE hoge_tbl (
id int PRIMARY KEY AUTO_INCREMENT,
name text
);

テーブルのコピー

/* hogeテーブルのスキーマをコピーしてテーブル作成 */
> CREATE TABLE hoge_tbl LIKE hoge;
/* hogeテーブルのデータをINSERT */
> INSERT INTO hoge_tbl SELECT * FROM hoge;

テーブル名変更

ALTER TABLE tbl_name RENAME [TO] new_tbl_name;

テーブル情報を表示(DESCRIBE)

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)

カラムのデータ型を変更する

mysql> alter table テーブル名 modify カラム名　データ型;

例: test_tbl の type カラムをchar(6)へ変更

mysql> desc test_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       | 
| type  | text    | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> alter table test_tbl modify type char(6);


mysql> desc test_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       | 
| type  | char(6) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

カラム追加

ALTER TABLE テーブル名 ADD 追加するカラム名 型 AFTER カラム名;
ALTER TABLE テーブル名 ADD 追加するカラム名 型 FIRST;

カラム名変更

ALTER TABLE テーブル名 CHANGE COLUMN 旧カラム名 新カラム名 新カラム型;

インデックスを追加

ALTER TABLE テーブル名 ADD INDEX (カラム名);

④文字コード関係

現在の環境確認

SHOW VARIABLES LIKE 'character%';

データベースの文字コード設定を調べる

mysql> show create database データベース名;

文字コードを指定してDB作成

CREATE DATABASE hoge CHARACTER SET SJIS;

テーブルの文字コード設定を調べる

mysql> show create table テーブル名 \G

文字コードを変更する。

SET NAMES 'sjis';

使える文字コード
sjis
ujis
utf8

文字コードなど、ステータス確認

 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
 --------------

⑤照会順序

データベースの照会順序確認

SELECT * from information_schema.schemata WHERE schema_name like [DB_NAME];

データベースの照会順序変更

ALTER  DATABASE [DB_NAME] CHARACTER SET [CHARACTER_NAME] COLLATE [COLLATION_NAME];

※CHARACTER_NAMEだけを指定した場合の照会順序は、「show charset」で見た「Default collation」がセットされます。

テーブルの照会順序確認

SHOW TABLE STATUS FROM [DB_NAME];

テーブルの照会順序変更

ALTER TABLE `テーブル名` COLLATE utf8_unicode_ci

カラムの照会順序確認

SHOW FULL COLUMNS FROM [TABLE_NAME];

カラムの照会順序変更

ALTER TABLE [TABLE_NAME] MODIFY COLUMN [COLUMN_NAME] text COLLATE `utf8_unicode_ci`;

⑥INSERT

INSERT INTO hoge_tbl VALUES(1,'aaaa');

INSERT INTO hoge_tbl(id,name) VALUES(1,'aaaa');

INSERT INTO hoge_tbl(name) VALUES('aaaa');

⑦View

0.ビューの一覧

SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW';

1.ビューの作成方法

create view dbname.hogetable as ( select * from footable )

2.ビューの確認

show create view dbname.hogetable

3.ビューの削除

drop view dbname.hogetable

4.ビューの更新

alter view dbname.hogetable as ( select * from footable )

MySQL役立つコマンド

http://qiita.com/sawarame/items/aa99408ce24930ad7def

リフレッシュ

ユーザのパスワード変更した後などは、リフレッシュしないと適応されてない可能性があります。

  mysql> FLUSH PRIVILEGES;
  Query OK, 0 rows affected (0.05 sec)

MySQL

fl8 Wiki

ユーザ用ツール

サイト用ツール

サイドバー

Tag Cloud

目次