MySQLの備忘録


##ログイン・ログアウト
###MySQLサーバーへ接続
```
# ログインユーザーでlocalhostに接続
$ mysql

# ユーザー名"root"で、パスワード付きでlocalhostに接続
$　mysql -u root -p
```

###MySQLサーバーと接続の解除
```
$ exit
$ quit
$ CTRL-D
```

##データベースに関するコマンド
###データベース一覧確認

```
> SHOW DATABASES;
```

###データベースの作製

```
# database_nameという名前ででデータベース作製
> CREATE DATABASE database_name;
```


###データベースの削除
```
> DROP DATABASE database_name;
```

###設定の確認
```
> status
```

###データベースの文字コード変更
####Centos7
```
$ sudo cp /etc/my.cnf.d/server.cnf{,.origin}      #バックアップの作製
$ sudo vi /etc/my.cnf.d/server.cnf			       #編集
$ sudo diff -u /etc/my.cnf.d/server.cnf{.origin,} #差分の表示
@@ -10,6 +10,7 @@
 # this is only for the mysqld standalone daemon
 [mysqld]
+character-set-server=utf8

$ sudo systemctrl restart mysql
```

####Centos6
```/etc/my.cnf
[mysqld]
...
character-set-server=utf8 #mysqldセクションの末尾に追加

[client]
default-character-set=utf8 #clientセクションを追加
```
```
$ /etc/init.d/mysqld restart
```

###ユーザーにデータベースの権限付
```
# データベース(database_name)における全ての権限をパスワード(user_password)でユーザー(user_name)に付与
> GRANT ALL PRIVILEGES ON database_name.* to user_name@localhost identified by 'user_password';
```

##テーブルの操作に関するコマンド

###指定したデータベースを使う
```
> USE database_name;
```

###テーブル一覧の表示
```
> SHOW TABLES;
```

###テーブルの作製

####データ型一覧

|型|説明|指定形式|別名|
|:-:|:-:|:-:|:-:|
|tinyint|8bit整数|tinyint(n)[unsigned][zerofill]|
|smallint|16bit整数|smallint(n)[unsigned][zerofill]|
|mediumint|24bit整数|mediumint(n)[unsigned][zerofill]|
|int|32bit整数|int(n)[unsigned][zerofill]|integer|
|bigint|64bit整数|bigint(n)[unsigned][zerofill]|
|decimal|パック10進数|decimal(n,d)[zerofill]|dec, numeric|
|float|浮動小数点数, 4または8バイト|float(n)[zerofill]|
|double|浮動小数点|double(n)[zerofill]|
|double precision|倍精度浮動小数点|double precision(n)[zerofill]|real|
|char|固定長文字列型|char(n)[binary]|
|varchar|可変長文字列型|varchar(n)[binary]|
|tinytext|可変長文字列型(255バイト以下)|tinytext|
|text|可変長文字列型(65535バイト以下)|text|
|mediumtext|可変長文字列型(1677215バイト以下)|mediumtext|
|longtext|可変長文字列型(4294967295バイト以下)|logtext|
|date|日付型|date|
|datetime|日付時刻型|datetime|
|timestamp|日付型時刻n桁|timestamp(n)|
|time|時刻型|time|
|year|年、2または4桁|year(n)|
|enum|列挙型|enum('文字列1', '文字列2', ...)|
|set|文字列セット型|set('文字列1', 文字列2", ...)|

####制約一覧

|制約の種類|意味|使用文字|
|:-:|:-:|:-:|
|NOT NULL制約|列の値がNULでないこと。列に必ず値を入力しなければならない|NOT NULL|
|一意キー制約|列のすべての値が一意であること。ただし、NULLが複数存在することは許される|UNIQUE|
|主キー制約|その列が行を一意に識別できること。主キーは重複が許されず、NULLも許可されない|PRIMARY KEY|
|参照整合性制約|関連付けられた表の共通する列の関係が保たれていること。|REFERENCES|
|CHECK制約|列の値が指定した条件をみたすこと|CHECK(condition)|


```
# usersというテーブル名でのフィールド作製サンプル
> CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255) unique,
password CHAR(32),
score DOUBLE,
sex ENUM('male', 'female') DEFAULT 'male',
memo TEXT,
created DATETIME,
key score (score)
);
```

###テーブルの削除
```
> DROP TABLES users;
```

###テーブルの構造を表示
```
> DESCRIBE users;
```

###テーブルにカラムの追加

```
# usersテーブルにageのカラムを追加
> ALTER TABLE users ADD age INT;
```

##データの操作に関するコマンド

###テーブルの中身を表示
```
SELECT * FROM users;
```

###レコードの追加

```
INSERT INTO users (name, email) VALUES('GENNKI', 'b0941015@gmail.com');
```

###レコードの編集

```
UPDATE users SET name='GENKI' WHERE id=1;
```

###レコードが存在したらレコードをアップデート、無かったらレコードを追加

```
INSERT INTO users (name, email) VALUES ('TIGER', 'BUNNY@gmail.com') ON DUPLICATE KEY UPDATE WHERE id=2;
```

###指定したフィールドのレコードを削除

```
DELETE FROM users WHERE id=1;
```

###スクリプト(*.sql)ファイルの実行

```
# bashから実行
$ mysql ファイル名 < ***.sql

-- mysqlで実行
> SOURCE ファイル名 ***.sql;
```

###文字コードの確認

```
>show variables like "%char%";
```

###MAMPのmy.cnfの参照場所

```
$ /Applications/MAMP/Library/bin/mysql --verbose --help |grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
```

##文字コードをUTF-8にする

```
[client]
...
default-character-set=utf8 #clientセクションを追加

[mysqld]
...
character-set-server=utf8 #mysqldセクションの末尾に追加
```

##日時の自動補完

```
CREATE TABLE testTable (  
  id INTEGER,  
  name VARCHAR(20),  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  
);  
```