■基本操作 データベースの作成 CREATE DATABASE [database name]; SHOW DATABASES; USE [database name]; 現在使っているDBを表示 SELECT DATABASE(); テーブルの作成 CREATE TABLE [table name] (カラム1 データ型1, カラム2 データ型2 ...); SHOW TABLES; 文字セットを指定してテーブルを作る CREATE TABLE tb1(bang VARCHAR(10), nama VARCHAR(10), tosi INT) CHARSET=sjis; テーブルのカラム構造を確認する DESC [テーブル名]; テーブルにデータを挿入 INSERT INTO [table name] VALUES(データ1,データ2,...); テーブルのコピーを作る(table name2をtable name1にコピーする CREATE TABLE [table name1] SELECT * FROM [table name2] ■データ型 整数型 INT TINYINT SAMLLINT MEDIUMINT BIGINT FLOAT DOUBLE 文字列型 CHAR 固定長の文字列(255文字まで) VARCHAR 可変長の文字列(255文字まで) TEXT 長い文字列(65535文字まで) LONGTEXT　　とても長い文字列 日付・時刻型 DATETIME 日付と時刻 DATE 日付 YEAR 年 TIME 時刻 ※MySQLでは必ず日付は「YYYY-MM-DD」、時刻は「HH:MM:SS」の形式で入力する ■テーブルの改造 テーブルのカラム構造の変更 ALTER TABLEコマンド ・カラムの定義を変更するとき ：ALTER TABLE [table name] MODIFY カラム名 データ型; ・カラムを追加するとき ：ALTER TABLE [table name] ADD カラム名 データ型; ・カラムの名前と定義を変更するとき ：ALTER TABLE [table name] CHANGE 変更前カラム名　変更後カラム名　変更後データ型; ・カラムを削除するとき ：ALTER TABLE [table name] DROP カラム名; 主キーを設定する 主キーとは、ユニークなレコードを作る場合には、カラムに他のカラムと区別するための特別な属性を設定します。主キーはレコードを厳密に特定できる、次のようなカラムです。 ・値の重複がない ・何のデータも入力しない(NULL)ことはできない CREATE TABLE [table name] (カラム名 データ型 PRIMARY KEY ...); 一意キーの設定 「重複をしない」という制限を加える一意キー(UNIQUEキー)を設定することもできる。一意キーはカラム内での重複はできないが、NULLを入力することはできる。 CREATE TABLE [table name](カラム名 データ型 UNIQUE, ....); 連続番号付きカラムの定義 連続番号つきカラムにするには以下の条件を満たすことが必要 ・データ型は「INT」などの整数型 ・「AUTO_INCREMENT」をつける ・「PRIMARY KEY」などを設定して一意にする 例) CREATE TABLE renzoku (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)); INSERT INTO renzoku(b) VALUES('あ'); INSERT INTO renzoku(b) VALUES('い'); INSERT INTO renzoku(b) VALUES('う'); とbに値を入力していくaには自動的に番号が代入されていく 連続番号の初期値を設定する ALTER TABLE [table name] AUTO_INCREMENT=0; で0から開始されるようになる。 最初からデータが入っているカラムにする CREATE TABLE [table name] (カラム名 データ型 DEFAULT 初期値 ...); インデックスを設定する インデックスとは、テーブル内にあるデータを検索するとき、そのデータが膨大な量だと、すべてのレコードの情報を調べるのに時間がかかってしまう。このような場合にデータの索引(インデックス)をつけることで高速に検索を行えるようになる。 CREATE INDEX [index name] ON [table name] (カラム名); 例）テーブルtb1Gのカラムbangにmy_indという名前のインデックスを設定する CREATE INDEX my_ind ON tb1G(bang); SHOW INDEX FROM [table name]; DROP INDEX [index name] ON [table name]; ※インデックスが設定されているテーブルでデータ更新をすると、一度作製したインデックスの情報を作り直すことになるため速度が低下する。つまり、インデックスを作成すると検索速度は向上するが更新速度は低下する可能性が高い。 ■テーブルやレコードをコピー・削除する テーブルのカラム構造だけをコピーする CREATE TABLE [new table name] LIKE [orig table name]; 他のテーブルのレコードをコピーする INSERT INTO [table name] SELECT * FROM [original table name]; 特定のカラムだけ選択してコピーする INSERT INTO [table name] (カラム名) SELECT カラム名 FROM [orig table]; テーブルの削除 DROP TABLE [table name]; データベースの削除 DROP DATABASE [database name]; レコードの丸ごと削除 DELETE FROM [table name]; ■計算したり、文字列を処理したりして表示する SELECT uria*1000 as 売り上げ FROM tb; SELECT a/b FROM [table name]; SELECT a+b FROM [table name]; 関数 AVG() SUM() COUNT() NOW() 現在時刻の取得 例) CREAE TABLE ima(a INT AUTO_INCREMENT PRIMARY KEY, b DATETIME); INSERT INTO ima(b) VALUES(NOW()); NULLを使った条件でデータを取り出す SELECT * FROM tb1J WHERE tosi IS NULL; NULLでない場合 SELECT * FROM tb1J WHERE tosi IS NOT NULL; ■並べ替え レコードを昇順に表示させる(一番小さい値が一番上) SELECT カラム名 FROM [table name] ORDER BY キーとなるカラム; レコードを降順に表示させる(一番大きい値が一番上) SELECT カラム名 FROM [table name] ORDER BY キーとなるカラム DESC; 表示するレコードの範囲を決める SELECT カラム名 FROM [table name] LIMIT 表示するレコード数 OFFSET 表示開始レコードのシフト数; ■グループ毎に表示する グループごとの平均や個数を計測するためにGROUP BYが便利である。 SELECT カラム名 FROM [table name] GROUP BY グループ化するカラム名; グループごとの数を数える SELECT bang,COUNT(*) FROM tb GROUP BY bang; グループごとの平均 SELECT bang, AVG(uria) FROM tb GROUP BY bang; グループごとの合計 SELECT bang, SUM(uria) FROM tb GROUP BY bang; グループに分けて条件をつけて取り出す SELECT 集計したカラム FROM [table name] GROUP BY グループ化するカラム HAVING 条件; SELECT bang, SUM(uria) FROM tb GROUP BY bang HAVING SUM(uria) >= 200; 抽出してからグループ化する HAVINGはグループ化した後に条件設定をして取り出す、今度は条件設定して取り出したレコードをグループ化する。 SELECT bang,AVG(uria) FROM tb WHERE uria >=50 GROUP BY bang; グループ化してから並べ替える SELECT bang, AVG(uria) FROM tb GROUP BY bang ORDER BY AVG(uria) DESC; ■複数のテーブルを利用する 二つのテーブルのレコードを集めて表示 SELECT カラム名 FROM テーブル名1 UNION SELECT カラム名2 FROM テーブル名2; 複数の抽出結果を合わせて表示する(重複を許容する) (SELECT bang FROM tb WHERE uria >=200) UNION ALL (SELECT bang FROM tb1 WHERE tosi >=35); 複数のテーブルを結合して表示する(内部結合) tb(売り上げ情報テーブル) ---------------------- | bang | uria | tuki | ---------------------- | A103 | 101 | 4 | | A102 | 54 | 5 | | A104 | 181 | 4 | | A101 | 184 | 4 | | A103 | 17 | 5 | | A101 | 300 | 5 | | A102 | 205 | 6 | | A104 | 93 | 5 | | A103 | 12 | 6 | | A107 | 87 | 6 | ---------------------- tb1(社員情報テーブル) ---------------------- | bang | nama | tosi | ---------------------- | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | ---------------------- tbだけでは実際の名前がないのでわかりづらい。そこで、bangに社員名簿から名前を引いてきて結合して表示する。 内部結合ー>テーブルの一部のカラムと一致する値を他のテーブルから持ってきて結合する。 SELECT カラム名 FROM テーブル1 JOIN テーブル2 ON テーブル1のカラム=テーブル2のカラム 例) SELECT * FROM tb JOIN tb1 ON tb.bang = tb1.bang; SELECT tb.bang, tb1.nama, tb1.uria FROM tb JOIN tb1 ON tb.bang = tb1.bang; ON tb.bang = tb1.bangの部分はUSING(bang)ともかける。 SELECT * FROM tb JOIN tb1 USING(bang); 外部結合 内部結合の場合は複数のテーブルで結合条件に利用しているカラムの値が一致するレコードしか表示されなかった。上記のテーブルの例だと、A105の西沢やA107は、どちらか一方のテーブルにしか存在しないデータなので、表示されない。一致しないレコードも表示したい場合、外部結合を利用する。 二つのテーブルを結合させる場合、どちらのテーブルをベースにするかで右結合、左結合が決まる。 左結合は左側(テーブル1に相当)のテーブルをベースにする。右結合は右側のテーブル(テーブルに2に相当)をベースにする。 左結合 SELECT カラム名 FROM テーブル1 LEFT JOIN テーブル2 ON テーブル1のカラム = テーブル2のカラム ; 右結合 SELECT カラム名 FROM テーブル1 RIGHT JOIN テーブル2 ON テーブル1のカラム=テーブル2のカラム ; ■SELECTしたレコードからSELECTする(サブクエリ) テーブルtbで、カラム「uria」が最大値であるレコードを表示する。 通常は、 SELECT MAX(uria) FROM tb; SELECT * FROM tb WHERE uria = (一段階目の結果); or SELECT * FROM tb WHERE uria IN (一段階目の結果); であるが、これを1クエリにすると SELECT * FROM tb WHERE uria IN (SELECT MAX(uria) FROM tb)); WHERE xxx = xxxxとWHERE xxx IN (xxx)の違いは？INのほうは、複数の値を指定できる。 WEHERE xxx IN (yyyy)のyyyの部分が単一の結果しか返さないのであれば=でも正常に動作するが、複数の値を返す可能性がある場合はINのほうが適切。 ■ビューを使いこなす ビューとはSELECTした結果をあたかもテーブルのようにして残しておく仕組み。この仕組みを利用してユーザ｢好きなテーブル」の「好きなカラムだけ」「好きな条件で」データを集めれられる。 ビューの作製(ビューを定義する) CREATE VIEW ビューの名前 AS SELECT カラム名 FROM テーブル名 WHERE 条件; ビューを見る 例 CREATE VIEW v1 AS SELECT uria FROM tb WHERE uria >=200; SELECT * FROM v1; ビューの存在を確認 SHOW TABLES; ビューの詳細情報を表示 SHOW CREATE VIEW ビュー名; ビューの削除 DROP VIEW ビュー名; 複数のテーブルからビューを作成する ※未確認 AS SELECTをつなげればいいのかな？ ■ストアドプロシージャを使いこなす ストアドプロシージャとは、いくつものSQL文を1つにまとめ、それを「CALL XX」というコマンドだけで実行できるようにしたものをストアドプロシージャという。 ストアドプロシージャを作製する DELIMITER // CREATE PROCEDURE pr1() BEGIN SELECT * FROM tb; SELECT * FROM tb1; END // DELIMITER ; ストアドプロシージャの実行 CALL pr1; ストアドプロシージャに引数を与える DELIMITER // CREATE PROCEDURE pr1(d INT) BEGIN SELECT * FROM tb WHERE uria >= d; SELECT * FROM tb1; END // DELIMITER ; ■トランザクションを使いこなす ストレージエンジンとは、DBのなかで実際の検索やファイル操作などの作業を行うエンジン部分。 テーブルごとにエンジンを指定することができる。 MySQLで利用可能なストレージエンジン MyISAM もっともよく利用される。高速(デフォルト) InnoDB トランザクションが利用可能。MyISAMよりは処理が遅い MEMORY すべてをメモリ上で行う。動作が高速。 ストレージエンジンの確認 SHOW CREATE TABLE tb; ストレージエンジンの変更 ALTER TABLE tb ENGINE=InnoDB; トランザクションとは、複数の処理をまとめて行う一連の機能。トランザクションをはじめてから結果をデータベースに反映させる作業をコミット。また、反映しないで元に戻すことをロールバックという。 トランザクションの開始 START TRANSACTION; ロールバック ROLLBACK; コミット COMMIT; トランザクションが利用できる範囲 以下のコマンドは自動コミットされてしまうので注意が必要。 DROP DATABSE DROP TABLE DROP ALTER TABLE SQLの実行結果をファイルに書き出す 1 標準出力を利用した方法。 mysql -u root -p 1234 > log.txt 2 teeコマンドを利用する。 tee log.txt USE db1; SELECT * FROM tb; notee;