MySQL – テーブルの追加・確認・変更・複製・削除

2016-10-29 / tau / コメントする

追加

テーブルの追加

テーブルの追加はCREATE TABLEコマンドで行う。

CREATE TABLE [テーブル名]([カラム名] [型], [カラム名] [型], ...);

1	CREATE TABLE [テーブル名]([カラム名] [型], [カラム名] [型], ...);

以下、実行例。

mysql> create table test(
    -> name varchar(10),
    -> age tinyint
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> create table test(

-> name varchar(10),

-> age tinyint

-> );

Query OK, 0 rows affected (0.01 sec)

mysql>

カラムの追加

テーブルにカラムを追加する場合はALTER TABLE ... ADDを使う。

確認

テーブル一覧の確認

テーブル一覧の確認はSHOW TABLESコマンド。

mysql> show tables;
+---------------------------+
| Tables_in_??????????_test |
+---------------------------+
| test                      |
+---------------------------+
1 row in set (0.00 sec)

mysql>

mysql> show tables;

+---------------------------+

| Tables_in_??????????_test |

+---------------------------+

| test |

+---------------------------+

1 row in set (0.00 sec)

mysql>

テーブル構造の確認

テーブル構造の確認はDESCまたはDESCRIBEコマンド。

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | tinyint(4) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql>

変更

テーブル名の変更

ALTER TABLE [テーブル名] RENAME TO [新しいテーブル名];

1	ALTER TABLE [テーブル名] RENAME TO [新しいテーブル名];

テーブルのカラム名・カラムの型の変更

テーブルのカラム名やカラムの型の変更はALTER TABLE ... CHANGE、ALTER TABLE ... MODIFYで行う。

複製

テーブルの複製

テーブルの複製は、MySQLの場合、CREATE TABLEとSSELECT文を組み合わせる。

CREATE TABLE [コピー先テーブル名] (SELECT * FROM [コピー元テーブル名]);

1	CREATE TABLE [コピー先テーブル名] (SELECT * FROM [コピー元テーブル名]);

削除

テーブルの削除

テーブルの削除はDROP TABLEコマンド。

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>

mysql> drop table test;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql>

テーブルのカラムの削除

テーブルのカラムを削除する場合はALTER TABLE ... DROP COLUMNとする。

MySQL – データベースの作成・権限付与・削除

2016-10-29 / tau / コメントする

データベースの作成

基本コマンド

データベースの作成はCREATE DATABASEコマンドを使う。

mysql> create database ??????????_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_test    |
| ??????????_wp1     |
+--------------------+<code></code>
4 rows in set (0.10 sec)

mysql>

mysql> create database ??????????_test;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ??????????_main |

| ??????????_test |

| ??????????_wp1 |

+--------------------+<code></code>

4 rows in set (0.10 sec)

mysql>

文字コードと照合順序

データベースの文字コードと照合順序(COLLATE～照合順序)を指定して作成するコマンド。

CREATE DATABALSE db_name CHARACTER SET char_set COLLATE collation_name;

たとえばutf8mb4の場合の例

CREATE DATABALSE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1	CREATE DATABALSE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

さくらレンタルサーバー

さくらレンタルサーバーの場合はwebのコントロールパネルでデータベースを作成・削除する方法のみが示されていて、コンソールでのコマンド操作はエラーとなる。

mysql> create database taustation_temp;                                                     
ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_temp'

1 2	mysql> create database taustation_temp; ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_temp'

データベースアクセスの権限設定

必要に応じて、ユーザーへのデータベースアクセスの権限を設定する。

GRANT ALL PRIVILEGES ON dbname.* TO dbuser@'localhost'

データベースの削除

データベースの削除にはDROP DATABASEコマンドを使う。

mysql> drop database ??????????_test;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_wp1     |
+--------------------+
3 rows in set (0.11 sec)

mysql>

mysql> drop database ??????????_test;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ??????????_main |

| ??????????_wp1 |

+--------------------+

3 rows in set (0.11 sec)

mysql>

存在しないデータベースを削除しようとするとエラーになるが、DROP DATABASE IF EXISTSコマンドを使うと、データベースが存在しない場合もエラーにならない。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_test    |
| ??????????_wp1     |
+--------------------+
4 rows in set (0.10 sec)

mysql> drop database if exists ??????????_test;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ??????????_main    |
| ??????????_wp1     |
+--------------------+
3 rows in set (0.11 sec)

mysql> drop database if exists ??????????_test;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop database ??????????_test;
ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_foo'

mysql>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ??????????_main |

| ??????????_test |

| ??????????_wp1 |

+--------------------+

4 rows in set (0.10 sec)

mysql> drop database if exists ??????????_test;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ??????????_main |

| ??????????_wp1 |

+--------------------+

3 rows in set (0.11 sec)

mysql> drop database if exists ??????????_test;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop database ??????????_test;

ERROR 1044 (42000): Access denied for user 'taustation'@'%' to database 'taustation_foo'

mysql>

QGIS – 属性テーブルの操作

2016-10-26 / tau / コメントする

テーブル構成の操作

テーブル構成を操作するときはメニューから「レイヤ」→「プロパティ」で開いたダイアログで「フィールド」を選択し、テーブル構成のペインを開いて操作する。

フィールドの追加・削除

編集モード切替ボタンを押して編集モードに切り替え
フィールド追加の場合は「新規フィールドボタン」
フィールド削除の場合はフィールドを選択して「フィールド削除」ボタン

フィールド計算機

他のフィールドの値を使った計算結果を表示させる。

属性テーブルのダイアログでフィールド計算機のボタンを押してダイアログ表示
新規フィールドをつくるか既存フィールドを使うかを選択
計算式を入力
Okボタンで実行

項目名変更

項目名変更はテーブル構成操作のダイアログで別名を定義する方法と、プロセッシングツールから行う方法がある。

データの操作

テーブルのデータを操作するときは、「属性テーブルを開く」ボタンを押すか、レイヤパネルのレイヤ名を右クリックして「属性テーブルを開く」でテーブルを表示させる。

さくらレンタルサーバーのタイムアウト対策

2016-10-26 / tau / コメントする

さくらレンタルサーバーにSSH接続すると、少し時間がたって(2分程度)強制的にサーバ側で接続が切られてしまう。

これを回避するには、ターミナルソフトのSSHの設定でKeepAliveパケットを一定時間ごとに送信するようにするとよい。

この設定にチェックを入れ、値を60(秒)に設定すると、強制切断はされなくなった。

MySQL – データベースの一覧表示と接続・確認

2016-10-25 / tau / コメントする

既存のデータベースを確認するコマンドはSHOW DATABASES;。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ????????_????      |
| ????????_???       |
+--------------------+
3 rows in set (0.10 sec)

mysql>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ????????_???? |

| ????????_??? |

+--------------------+

3 rows in set (0.10 sec)

mysql>

データベースに接続するにはuseコマンドを使う。useコマンドは最後にセミコロン(;)を入れなくてもよい。

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
<code></code>
Database changed
mysql>

mysql> use information_schema

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

以降、useコマンドで接続するデータベースを切り替えることができる。

接続中のデータベースの確認

接続中のデータベースの確認方法は、以下の二通り。

SELECT database();

1	SELECT database();

または

SHOW PROCESSLIST;

1	SHOW PROCESSLIST;

以下、実行例。

mysql> select database();
+-----------------+
| database()      |
+-----------------+
| taustation_test |
+-----------------+
1 row in set (0.00 sec)

mysql> show processlist;
+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+
| Id        | User       | Host                | db              | Command | Time | State | Info             |
+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+
| 601690174 | taustation | 59.106.171.51:37572 | taustation_test | Query   |    0 | NULL  | show processlist |
+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql>

mysql> select database();

+-----------------+

| database() |

+-----------------+

| taustation_test |

+-----------------+

1 row in set (0.00 sec)

mysql> show processlist;

+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+

+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+

+-----------+------------+---------------------+-----------------+---------+------+-------+------------------+

1 row in set (0.00 sec)

mysql>

MySQLが強制終了されて起動できない

2016-10-25 / tau / コメントする

MySQL使用中に接続が切れたりすると、ロックがかかって使えなくなる。

そのような場合は、psコマンドでプロセスID(PID)を調べて、killコマンドで強制終了させる。

Linuxコマンド – alias

2016-10-25 / tau / コメントする

さくらサーバで用いられているaliasコマンドの書き方。

alias エイリアス名 "実行コマンド"

1	alias エイリアス名 "実行コマンド"

たとえばlsaという名前でls -aを実行させたい時は以下のように入力。

alias lsa "ls -a"

1	alias lsa "ls -a"

登録済みのエイリアスの一覧は引数なしでaliasコマンド。

% alias
h       (history 25)
j       (jobs -l)
la      (ls -a)
lf      (ls -FA)
ll      (ls -lA)
lsa     ls -a
%

% alias

h (history 25)

j (jobs -l)

la (ls -a)

lf (ls -FA)

ll (ls -lA)

lsa ls -a

エイリアスを削除するにはunaliasコマンド。

unalias エイリアス名

1	unalias エイリアス名

さくらサーバのデフォルトでは、.cshrcファイルにエイリアスが登録されている。

alias h         history 25
alias j         jobs -l
alias la        ls -a
alias lf        ls -FA
alias ll        ls -lA

# A righteous umask
.....

alias h history 25

alias j jobs -l

alias la ls -a

alias lf ls -FA

alias ll ls -lA

# A righteous umask

.....

このファイルに新たなaliasを追加することができる。

MySQL – 状態の確認とコマンド一覧

2016-10-25 / tau / コメントする

statusコマンドでMySQLのバージョンや文字セット、ポート番号などを状態を確認できる。

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.32, for FreeBSD9.1 (amd64) using  5.2

Connection id:          594729209
Current database: 
Current user:           ????@??.??.??.??
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38-log Source distribution
Protocol version:       10
Connection:             mysql???.db.sakura.ne.jp via TCP/IP
Server characterset:    ujis
Db     characterset:    ujis
Client characterset:    ujis
Conn.  characterset:    ujis
TCP port:               3306
Uptime:                 589 days 5 hours 28 min 37 sec

Threads: 16  Questions: 27954918985  Slow queries: 1391539  Opens: 21787261  Flush tables: 590  Open tables: 19200  Queries per second avg: 549.112
--------------

mysql>

mysql> status

--------------

mysql Ver 14.14 Distrib 5.5.32, for FreeBSD9.1 (amd64) using 5.2

Connection id: 594729209

Current database:

Current user: ????@??.??.??.??

SSL: Not in use

Current pager: more

Using outfile: ''

Using delimiter: ;

Server version: 5.5.38-log Source distribution

Protocol version: 10

Connection: mysql???.db.sakura.ne.jp via TCP/IP

Server characterset: ujis

Db characterset: ujis

Client characterset: ujis

Conn. characterset: ujis

TCP port: 3306

Uptime: 589 days 5 hours 28 min 37 sec

Threads: 16 Questions: 27954918985 Slow queries: 1391539 Opens: 21787261 Flush tables: 590 Open tables: 19200 Queries per second avg: 549.112

--------------

mysql>

また、helpコマンドでコマンド一覧を表示できる。

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

mysql> help

For information about MySQL products and services, visit:

http://www.mysql.com/

For developer information, including the MySQL Reference Manual, visit:

http://dev.mysql.com/

To buy MySQL Enterprise support, training, or other products, visit:

https://shop.mysql.com/

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

? (\?) Synonym for `help'.

clear (\c) Clear the current input statement.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter.

edit (\e) Edit command with $EDITOR.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

help (\h) Display this help.

nopager (\n) Disable pager, print to stdout.

notee (\t) Don't write into outfile.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print (\p) Print current command.

prompt (\R) Change your mysql prompt.

quit (\q) Quit mysql.

rehash (\#) Rebuild completion hash.

source (\.) Execute an SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

system (\!) Execute a system shell command.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql>

MySQL – コンソールからの接続

2016-10-25 / tau / コメントする

MySQLへのログイン

ターミナルソフトでサーバにログイン後、mysqlコマンドでMySQLに接続する。

mysql -u ユーザ名 -h サーバ名 -p

1	mysql -u ユーザ名 -h サーバ名 -p

-uでユーザ名を指定し、-hでホスト名(サーバ名)を指定する。-pオプションだけつけると、パスワードの入力を求められる。さくらサーバでMySQLをインストール済みの場合は以下の通り。

mysql -u ユーザ名 -h mysql???.db.sakura.ne.jp -p

1	mysql -u ユーザ名 -h mysql???.db.sakura.ne.jp -p

パスワード入力に成功すると、mysql>プロンプトが表示される。

% mysql -u [ユーザ名] -h mysql???.db.sakura.ne.jp -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 593501130
Server version: 5.5.38-log Source distribution

Copyright (c) 2000, 2013, 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 -u [ユーザ名] -h mysql???.db.sakura.ne.jp -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 593501130

Server version: 5.5.38-log Source distribution

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>プロンプトに対してquitまたはexitで終了。

mysql> quit
Bye
%

mysql> quit

Bye

mysql> exit
Bye
%

mysql> exit

Bye

quitの代わりに\qでも終了できる。

RLoginでさくらサーバーにssh接続

2016-10-25 / tau / コメントする

RLoginのインストール

Poderosaを使っていたが、Windows10で動かそうとすると.NET3.5が必要だと言われた上に、そのインストールが固まって動かなくなってしまった。Poderosaは開発も止まっているらしく、この機にRLoginを試してみた。

ダウンロードサイトからrlogin.zipをダウンロードして解凍すると、ファイルはrlogin.exeの一つだけなので、これを実行できるように配置すればよい。

最初にRLoginを立ち上げるとサーバーを登録するダイアログが立ち上がる。RLoginでは接続するサーバーを「エントリ」として登録する。

エントリの登録

RLogin起動時に”Serever Select”ダイアログが立ち上がるが、一旦接続を切った後などのRLoginのメインウィンドウからは、「ファイル」→「サーバーに接続」メニューか左端の「サーバー接続」ボタンで同じダイアログが立ち上がる。

「エントリー/コメント」欄にサーバー名などを入力
プロトコルはssh
Server Addressはさくらサーバーの場合、[ユーザ名].sakura.ne.jp
User NameとPasswordは空欄にしておいて、「接続時にユーザ名・パスワードの入力を求める」にチェック
デフォルト文字セットは「UTF-8」

これでエントリ名を選択してOKを押すとユーザ名とパスワードの入力ダイアログが立ち上がり、接続後は通常のターミナル画面となる。

タイムアウト対策

デフォルト設定のままだと2分程度でサーバー側から接続が切られてしまうので、タイムアウト対策が必要。

RLoginの場合は、

Server Selectダイアログからさくらサーバーのエントリを選んで「編集」
「プロトコル」を選ぶ
SSHフィールドの「KeepAliveパケット」を設定
- チェックボックスをOn
- 送信間隔を60(秒)に