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', '[e-mail]');

レコードの編集

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

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

INSERT INTO users (name, email) VALUES ('TIGER', '[e-mail]') 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  
);