MySQL 基本SQL

MySQL関連基礎(主にLinux)

外部から接続したい場合、
hostの変更とconfファイルにbind-addressを追加する。

保存場所確認
show variables like ‘%dir%’;

my.cnfの位置
mysql –help | grep /my.cnf
左から順番。開いていくと/etc/mysql/my.cnf以外は空
my.cnfを開くと
/etc/mysq/conf.d/
/etc/mysql/mysql.conf.d/
の2つのディレクトリをインクルードしている。
ディレクトリの中に適当にファイルを作っていれておけばOK

ユーザー

ユーザーの作成
CREATE USER ‘test_usr’@’192.168.102.%’ IDENTIFIED BY  ‘passw0rd’;

ホスト名の変更
RENAME USER ‘root’@’localhost’ to ‘root’@’192.168.102.%’;

ユーザー一覧
select User,Host from mysql.user;

パスワード変更(ユーザー指定)
SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘56789’);

パスワード変更(ログインユーザー)
SET PASSWORD = PASSWORD(‘5678’);

データベース

現状の状況
STATUS;

データベース一覧
SHOW DATABASES;

データベースの作成
CREATE DATABASE test_db2 CHARACTER SET sjis;

データベース選択
USE test_db2

データベース削除
DROP DATABASE test_db;

テーブル

テーブル一覧
SHOW TABLES;

エンジンの確認
SHOW TABLE STATUS;

テーブルの作成
CREATE TABLE tbl (
id int PRIMARY KEY AUTO_INCREMENT NOT NULL
) DEFAULT CHARACTER SET sjis;

テーブルのリネーム
ALTER TABLE tbl RENAME tbl;

テーブルの複製
CREATE TABLE to_tbl LIKE from_tbl

カラム

列の追加
ALTER TABLE tbl ADD col VARCHAR

列の情報
SHOW COLUMNS FROM tbl;

列の変更
ALTER TABLE tbl CHANGE col col2 int;

列の削除
ALTER TABLE tbl DROP col2;

SELECT式の評価順

FROM
ON – JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP(LIMIT)

文字コード

有効な文字コード
SHOW CHARACTER SET;

データベースの文字コード
SHOW CREATE DATABASE test_db2;

文字コード(接続中のデータベース)
SHOW VARIABLES LIKE ‘char%’;

作成済みデータベースの文字コード変更
ALTER DATABASE db_name CHARACTER SET utf8;

或いは以下のようなコードで確認

文字コードの修正方法

例えば以下のような状態

コマンド、PHPから見るとエンコが合っていないのが分かる。

[mysqld]
character-set-server = utf8
を記述して一旦再起動。

sudo /etc/mysql/init.d/mysql restart

これで問題ないはず。

以前はmy.cnfには以下のように書いていた。

[mysqld]
character-set-server=utf8 //バージョンよって違う(以前はdefault-character-set)

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8
//全クライアントで共通の設定と書かれていたが、ここは書かなくても大丈夫のようだけで書いておいた。

[client]
default-character-set=utf8

character_set_client : クライアント側で発行したsql文はこの文字コードになる
character_set_connection : クライアントから受け取った文字をこの文字コードへ変換する
character_set_database : 現在参照しているDBの文字コード
character_set_results : クライアントへ送信する検索結果はこの文字コードになる
character_set_server : DB作成時のデフォルトの文字コード
character_set_system : システムの使用する文字セットで常にutf8が使用されている

ちなみに、
PDO(‘mysql:host=localhost;dbname=db;charset=utf8’, ‘root’, ‘5678’);
として、DSNでcharset=utf8と指定して、逆にmy.cnfに何も書かないと、

となる。

つまり、データベース作成、テーブル作成、DSNでも字コードを指定しても
結局serverは変わらないので、my.cnfは必要。
逆にmy.cnfを1行入れれば、PDOのDSNはなくてもOK。

Windowsの文字コード

create database時に、sjis指定すると、
character_set_client :utf8
character_set_connection:utf8
character_set_database:sjis
character_set_filesystem:binary
character_set_results:utf8
character_set_server:utf8
character_set_system:utf8

sjis指定なしでデータベースを作成すると、
character_set_client :utf8
character_set_connection:utf8
character_set_database:utf8
character_set_filesystem:binary
character_set_results:utf8
character_set_server:utf8
character_set_system:utf8
となる。

my.iniは
C:\ProgramData\MySQL\MySQL Server 5.7
にある。

ちなみに、インストール時は

[client]
指定なし

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8

[mysqldump]
指定なし

となっていた。

ちなみに、㈱など文字化けする場合、
[mysqld]
character-set-server=sjis
とすると直る。

Winでの再起動は
sevices.msc
から実行

制約の種類

NOT NULL
CHECK
UNIQUE KEY
PRIMARY KEY
FOREIGN KEY

追加方法

ALTER TABLE tbl ADD UNIQUE (key);

ちなみに、primaryとuniqueは
複数指定すると、複合になるのでuniqueをそれぞれ複数の列の場合、
create table tbl (id int ,nm varchar(5) , unique(id) , unique(nm));
のように指定。