MySQL への SQL コマンドは色々あるけど毎回忘れちゃうのでメモしておきます。
MySQLログインの基本コマンド
mysql と打つだけでログインできます。
MySQLにログイン
mysql
ただし root ユーザーのパスワードなどがない場合です。
MySQLにユーザーを指定してログイン
-uオプションつけます。
MySQLにログイン
mysql -u ユーザー名
rootユーザーでログイン
rootユーザーを指定してログインします。
MySQLにログイン
mysql -u root
パスワードを入力してログイン
-pオプションつけます。
MySQLにログイン
mysql -u root -p
パスワードを要求されます。
データベースを指定してログイン
特にオプションを付けずに、データベース名を後ろにつけます。
MySQLにログイン
mysql -u ユーザー名 データベース名
以下はrootユーザーでpugi_databaseにログインする例です。
MySQLにログイン
mysql -u root pugi_database
略
MariaDB [pugi_development]>
データベースを指定すると、ログイン後に必ず実行するuse データベース名というコマンドを省略出来るので、なるべく指定したほうが良いです。
ホストを入力してログイン
-hオプションつけます。
MySQLにログイン
mysql -u root -p -h ホスト名
ホストを指定するとことができます。
ポートを指定してログイン
-Pオプションつけます。
MySQLにログイン
mysql -u root -p -P 3037
上記は3037ポートを利用する場合です。
使用するデータベースを選択する
MySQL はログイン後にデータベースの選択が必要です。
use データベース名;
use コマンドを省略したい場合は、データベースを指定してログイン
にあるように、ログイン時にデータベースを指定すると良いです。
データベース一覧を確認する
show databases;
データベース作成する
create database データベース名;
データベース削除する
drop database データベース名;
SELECTでレコードを検索する
MySQL や MariaDB で SQL を勉強したい場合は、まずは SELECT 文で覚えるといいですね。
レコードを検索
SELECT * FROM テーブル名;
*を指定すると全カラムを表示します。
レコードを検索の例
SELECT * FROM posts;
posts テーブルを検索する例です。
WHERE句での絞込み
レコードを絞込み検索
SELECT * FROM posts WHERE id = 10;
posts テーブルのidが10のレコードだけを検索します。
LIKEで文字列検索
LIKE 検索というのがとても便利です。
レコードを絞込み検索
SELECT * FROM posts WHERE post_contents LIKE('% style="font-size: 12pt;"%');
上記は WordPress の投稿本文中に、インラインスタイルであるstyle="font-size: 12pt;"が入っているレコードを検索できます。前後に%をつけるとカラム内のどこにあってもヒットしてくれます。
INSERTでレコードを追加
INSERT 文を使用してレコードを追加します。
基本構文
INSERT INTO テーブル名 (カラム名) VALUES (値);
INSERT例
INSERT INTO users (name) VALUES ('ぷぎえもん');
usersテーブルのnameというカラムにぷぎえもんという文字列を追加した例です。
SELECTの検索結果をINSERTで追加
検索結果を追加したいような場合は、INSERT文内にSELECT文を書きます。
テーブル１にテーブル２の値を移すような場合に使えます。
SELECT結果をINSERT
INSERT INTO テーブル名１ (カラム名１) SELECT カラム名２ FROM テーブル名２ WHERE テーブル名２.カラム名 = 値;
以下は、usersテーブルの名前をprofilesテーブルに移すという例です。
SELECT結果をINSERT例
INSERT INTO profiles (name) SELECT users FROM users WHERE users.id = 1;
UPDATEでカラムのデータを更新する
複数更新するときはカンマ,で区切ります。
カラムを更新
UPDATE テーブル名 SET カラム1=1, カラム2=10 WHERE id=1;
カラムを更新例
UPDATE users SET name='hoge', status=3 WHERE id=1;
REPLACEで文字列を置換して更新
REPLACE を使うと文字列を置換できます。正規表現は不要で置換できますが、一方で REPLACE は正規表現を使用できないようです。
REPLACEで置換、LIKE絞りあり
UPDATE posts SET post_content = REPLACE(post_content, ' style="font-size: 12pt;"', '') WHERE post_content LIKE('% style="font-size: 12pt;"%');
こちらは WordPress の本文からstyle="font-size: 12pt;"というインラインスタイルを削除する SQL です。
DELETEでデータを削除する
削除は DELETE です。
DELETE構文
DELETE FROM テーブル名 WHERE 条件;
DELTEの例
DELETE FROM members WHERE id=13;
バージョンの確認
MySQL のバージョンを知りたい場合は以下を使用します。
バージョンの確認
SELECT VERSION();
MySQLのシステム変数の値を見る
MySQLのシステム変数をドバっと表示します。
システム変数の値を表示
SHOW VARIABLES;
文字コード、character_set周りを確認したとき
SHOW VARIABLESにlikeを組み合わせることで、知りたい変数を限定することができます。
SHOW VARIABLES LIKE "char%";
slowquery周りを確認したいとき
SHOW VARIABLES LIKE 'slow%';
プライマリーキーの追加
プライマリーキーの追加
ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名);
カラムの名前、型を変えたい
postsテーブルのtxtというカラムをcaptionというカラムでVARCHAR型に変えたい場合です。
カラム名と型を変更
ALTER TABLE `posts` CHANGE COLUMN `txt` `caption` VARCHAR(255);
カラムの名前だけを変更する場合でも、上記のように型指定が必要です。
not null制約を追加する場合です。
カラム名の制約を追加
ALTER TABLE `posts` CHANGE COLUMN `txt` `caption` VARCHAR(255) not null;
カラムの型を変えたい
ALTER TABLE の MODIFY COLUMN を使用します。
ALTER TABLE `comments` MODIFY COLUMN `post_id` BIGINT NOT NULL;
カラムを追加したい
ALTER TABLEとADDを使用してカラムを追加します。
カラムを追加
ALTER TABLE テーブル名 ADD カラム名 型;
実際の例は以下のような感じです。
カラムを追加
ALTER TABLE `users` ADD `pugi_id` int(11) DEFAULT NULL;
usersテーブルでpugi_idを追加する例です。
特定カラムの後ろにカラムを追加したい
カラムの追加位置をコントロールしたい場合は、AFTERを使用します。
特定カラムの後ろにカラムを追加
ALTER TABLE テーブル名 ADD 追加したいカラム名 AFTER 追加したい直前のカラム名;
先程の例にAFTERを追加します。
カラムを追加
ALTER TABLE `users` ADD `pugi_id` int(11) DEFAULT NULL AFTER `password`;
上記を実行すると、passwordカラムの後ろに、pugi_idカラムが追加されます。
カラム削除
カラムを削除する場合です。
ALTER TABLE テーブル名 DROP カラム名;
テーブル削除
テーブルを削除する場合です。
ALTER TABLE テーブル名;
テーブルの中味を捨てる
TRUNCATE を使用するとtableを破棄せず、中身のデータだけを消すことができます。しかも TRUNCATE の実行は非常に早いです。
テーブルの中味を捨てる
TRUNCATE テーブル名;
以下の例では、ログテーブルを空にします。
ログテーブルの中味を捨てる
TRUNCATE logs;
ユーザーを追加
MySQLで使用するユーザーの追加です。
ユーザーを追加
CREATE USER ユーザー名;
pugiemonnユーザーを追加
CREATE USER pugiemonn;
ユーザーのパスワードを変更
ユーザーのパスワードを変更した場合は、SET PASSWORD が便利
パスワードの変更方法
SET PASSWORD FOR ユーザー名@ホスト=PASSWORD('パスワード');
以下はpugiemonnユーザーにパスワードを設定する例です。
SET PASSWORD FOR pugiemonn@localhost=PASSWORD('pugiemonn_password');
ユーザーを削除
DROP USER ユーザー名@ホスト名;
ユーザー権限を確認
ユーザーの権限を確認
SHOW GRANTS FOR ユーザー名@ホスト名;
wordpressユーザーの権限を確認します。
wordpressユーザーの権限を確認
SHOW GRANTS FOR wordpress@localhost;
pugiemonnユーザーの例
pugiemonnユーザーの権限を確認
SHOW GRANTS FOR 'pugiemonn'@'%';
権限を追加
特定のテーブルに権限を追加する場合 GRANT を使用します。
権限を追加
GRANT 権限 ON `データベース名`.`テーブル名` TO 'ユーザー名'@'ホスト'
wordpress_databaseの全てのテーブルに権限を追加する例です。
wordpressユーザーの権限を追加
GRANT ALL PRIVILEGES ON `wordpress_database`.* TO 'wordpress'@'localhost';
pugiemonnユーザーにSELECT権限を追加する例です。
pugiemonnユーザーの権限を追加
GRANT SELECT ON `pugidb`.`users` TO 'pugiemonn'@'%';
文字コードを UTF-8 にする
文字化けしているのだけど確認したい時、さくっとUTF-8にしたい場合に使えます。
UTF-8を指定
SET NAMES UTF8;