Edited at

MySQLの備忘録

More than 3 years have passed since last update.


ログイン・ログアウト


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