ぺーぺーSEのブログ

備忘録・メモ用サイト。

トップ > SQL > SQLまとめ

SQLまとめ

SQL

SQLについてちょっとまとめる。MySQL5.5にて動確。

SQL【Structured Query Language】

DDL【Data Definition Language】

SQLの一部で、リレーショナルデータベースのテーブルを制御する言語。
テーブル全体の作成・変更・削除などを行う際に使用する。

  • CREATE DATABASE
    • データベースを作成。
    • 【構文】CREATE DATABASE データベース名;
  • DROP DATABASE
    • データベースを削除。
    • 【構文】DROP DATABASE データベース名;
  • CREATE TABLE
    • データベースに表を作成。
    • 【構文】CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 [(列名 型名 列制約, ... , 表制約 表制約...)] [table_options] [select_statement];
    • [TEMPORARY]：一時表となる。トランザクションの終了時、または、セッション終了時に切り捨てられる。
    • [IF NOT EXISTS]：同じテーブルが存在しない場合テーブルを作成する。存在した場合、エラー無く終了する。
    • 列制約：[NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [参照定義]
    • 表制約：
      • PRIMARY KEY (index_col_name,...)
      • KEY|INDEX [index_name] (index_col_name,...)
      • UNIQUE [INDEX] [index_name] (index_col_name,...)
      • FULLTEXT [INDEX] [index_name] (index_col_name,...)
      • [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) [参照定義]
      • CHECK (expr)
  • DROP TABLE
    • 表及びデータを削除。
    • 【構文】DROP TABLE [IF EXISTS] テーブル名 [, テーブル名,...] [RESTRICT | CASCADE];
    • [IF EXISTS]：テーブルが存在する場合削除する。存在しない場合エラー無く終了する。
  • ALTER TABLE
    • テーブルの定義を変更。
    • 【構文】ALTER [IGNORE] TABLE 表名 alter_specification [, alter_specification ...]
      • 【列を最後に追加】ALTER TABLE テーブル名 ADD 追加する列名 型;
      • 【列を最後に追加（複数）】ALTER TABLE テーブル名 ADD (追加する列名 型,追加する列名 型);
      • 【列を最初に追加】ALTER TABLE テーブル名 ADD 追加する列名 型 FIRST;
      • 【列を途中に追加】ALTER TABLE テーブル名 ADD 追加する列名 型 AFTER 列名;
      • 【INDEXを追加】ALTER TABLE テーブル名 ADD INDEX [インデックス名] 列名;
      • 【INDEXを追加（複合）】ALTER TABLE テーブル名 ADD INDEX [インデックス名] (列名,列名);
      • 【主キーを追加】ALTER TABLE テーブル名 ADD PRIMARY KEY (列名,...);
      • 【ユニーク制約を追加】ALTER TABLE テーブル名 ADD UNIQUE [インデックス名] (列名,...);
      • 【全文検索INDEXを追加】ALTER TABLE テーブル名 ADD FULLTEXT [インデックス名] (列名,...);
      • 【列名変更】ALTER TABLE テーブル名 CHANGE 古い列名 新しい列名;
      • 【列定義変更】ALTER TABLE テーブル名 MODIFY 列名 型 NOT NULL DEFAULT デフォルト値;
      • 【列を削除】ALTER TABLE テーブル名 DROP 削除する列名;
      • 【主キーを削除】ALTER TABLE テーブル名 DROP PRIMARY KEY;
      • 【INDEXを削除】ALTER TABLE テーブル名 DROP INDEX インデックス名;
      • 【テーブル名変更】ALTER TABLE テーブル名 RENAME [TO] 新しいテーブル名;
  • RENAME TABLE
    • テーブル名を変更。
    • 【構文】RENAME TABLE 旧テーブル名 TO 新テーブル名[,旧テーブル名2 TO 新テーブル名2,...]
  • CREATE VIEW
    • ビューを作成。
    • @TODO ビューが更新されるタイミングについて記述する。
    • 【構文】CREATE|REPLACE VIEW ビュー名 [(column_list)] AS SELECT文;
  • DROP VIEW
    • ビューを削除。
    • 【構文】DROP VIEW [IF EXISTS] ビュー名 [, ビュー名] ...;
  • CREATE TRIGGER
    • データベース・トリガーを作成。
    • トリガーとは、表に対して指定した条件を充たしたときに実行されるプログラム。
    • 【構文】CREATE TRIGGER トリガー名 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON テーブル名 FOR EACH ROW 処理
    • 【例】CREATE TRIGGER SAMPLE_TRG BEFORE UPDATE ON SAMPLE_TBL FOR EACH ROW INSERT INTO HISTORY (REMARKS) VALUES (OLD.REMARKS); END
    • トリガーでは、テーブル上の各項目の更新前の値とこれから更新する値を参照することが出来る。更新前の値を参照するには項目名の前にOLD.を付け、更新する値を参照するには項目名の前にNEW.を付ける。
      • OLD.項目名
      • NEW.項目名
  • DROP TRIGGER
    • データベース・トリガーを削除。
    • 【構文】DROP TRIGGER [スキーマ名.]トリガー名;
DML【Data Manipulation Language】

SQLの一部で、リレーショナルデータベースのレコードを制御する言語。
テーブル内のレコードの追加・検索・更新・削除などを行う際に使用する。

  • SELECT
    • 1つ以上の表からデータを取り出す。
    • 【構文】
SELECT
    [DISTINCT]
    select_expr, ...
    FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ...]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {取得開始行（一行目は0）, 取得終了行 | 取得行数}]
    [FOR UPDATE | LOCK IN SHARE MODE]
  • 内部結合(INNER JOIN)
    • 結合する列の値が一致する行を表示する。
    • 【構文】SELECT 列名,[列名,・・・] FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列;
    • 【構文】SELECT 列名,[列名,・・・] FROM 表1, 表2 WHERE 表1.列 = 表2.列;
  • 外部結合(LEFT JOIN,RIGHT JOIN)
    • 結合する列の値が一致しない行も表示する。
    • 【構文】SELECT 列名,[列名,・・・] FROM 表1 LEFT JOIN 表2 ON 表1.列 = 表2.列;
    • 【構文】SELECT 列名,[列名,・・・] FROM 表1 RIGHT JOIN 表2 ON 表1.列 = 表2.列;
  • INSERT
    • 表に行を追加。
    • 【構文】INSERT INTO テーブル名 [(col_name,...)] VALUES ((expression | DEFAULT),...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
    • 【構文】INSERT INTO テーブル名 [(col_name,...)] SELECT ...
    • 【構文】INSERT INTO テーブル名 SET col_name=(expression | DEFAULT), ... [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
  • UPDATE
    • 表の既存の値を変更。
    • 【構文】UPDATE テーブル名 SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT rows]
    • 【構文】UPDATE テーブル名 [, tbl_name ...] SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
  • DELETE
    • 表から行を削除。
    • 【構文】DELETE FROM テーブル名 [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
    • 【構文】DELETE テーブル名[.*] [, テーブル名[.*] ...] FROM table-references [WHERE where_definition]
    • 【構文】DELETE FROM テーブル名[.*] [, テーブル名[.*] ...] USING table-references [WHERE where_definition]
  • TRUNCATE
    • 表の全ての行を削除。
    • 【構文】TRUNCATE TABLE テーブル名;
ストアドプロシージャ

省略。

その他
  • カーソル
    • カーソルとは、クエリの結果集合を一時的に蓄えておくための仮想的な作業領域のこと。この中の現在位置を示すポインタと呼ばれるものが、ループ処理が実行されるたびに一行ずつ進んでいき、次の処理対象を示す。
    • 【構文】DECLARE カーソル名 CURSOR FOR SELECT文;
      • カーソルを宣言する。
      • SELECTステートメントにINTO節を含めることはできない。
    • 【構文】OPEN カーソル名;
      • カーソルを開く。
    • 【構文】FETCH カーソル名 INTO 変数名 [, 変数名] ...
      • OPENしたカーソルを使って、次の行（存在している場合)を取り込んで変数へ代入し、カーソルポインタを次に進める。
    • 【構文】CLOSE カーソル名;
      • カーソルを閉じる。
  • GRANT
    • ユーザーの作成と権限の設定。
    • 【構文】
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {テーブル名 | * | *.* | データベース名.*}
    TO ユーザID [IDENTIFIED BY 'パスワード']
                [, ユーザID [IDENTIFIED BY 'パスワード'] ...]
    [REQUIRE
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]
    ]
    [WITH GRANT OPTION]

データ型

数値型
  • TINYINT [(M)] [UNSIGNED] [ZEROFILL]
    • 範囲：-128〜127
    • 符号なし範囲：0〜255
  • SMALLINT [(M)] [UNSIGNED] [ZEROFILL]
    • 範囲：-32768〜32767
    • 符号なし範囲：0〜65535
  • MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]
    • 範囲：-8388608〜8388607
    • 符号なし範囲：0〜16777215
  • INTEGER / INT [(M)] [UNSIGNED] [ZEROFILL]
    • 範囲：-2147483648〜2147483647
    • 符号なし範囲：0〜4294967295
  • BIGINT [(M)] [UNSIGNED] [ZEROFILL]
    • 範囲：-9223372036854775808〜9223372036854775807
    • 符号なし範囲：0〜18446744073709551615
  • FLOAT (精度) [ZEROFILL]
  • FLOAT [(M,D)] [ZEROFILL]
    • 4バイト浮動少数。
    • 範囲： -3.402823466E+38〜-1.175494351E-38、0、1.175494351E-38〜3.402823466E+38
    • DOUBLE / REAL [(M,D)] [ZEROFILL]
    • 8バイト浮動少数。
    • 範囲：-1.7976931348623157E+308〜-2.2250738585072014E-308、0、2.2250738585072014E-308〜1.7976931348623157E+308
  • DECIMAL / NUMERIC (M,D) [ZEROFILL]
日付型
  • DATE
    • 日付。
    • 書式：YYYY-MM-DD
  • DATETIME
    • 日時。
    • 書式：YYYY-MM-DD HH:MM:DD
  • TIMESTAMP [(M)]
    • 自動更新される日時
    • 書式：YYYY-MM-DD HH:MM:DD
  • TIME
    • 時刻。
    • 書式：HH:MM:DD
  • YEAR
    • 2桁または4桁形式(デフォルト)の年。
    • 書式：YYYY
文字列型
  • [NATIONAL] CHAR (M) [BINARY]
    • 固定長文字列。
    • 幅：1〜255文字

[NATIONAL] VARCHAR (M) [BINARY]

    • 可変長文字列。
    • 幅：1〜255文字
  • TINYTEXT
    • 可変長文字列。
    • 最大長 255 バイトのテキスト
  • TEXT
    • 可変長文字列。
    • 最大長 65535 バイトのテキスト
  • MEDIUMTEXT
    • 可変長文字列。
    • 最大長 16777215 バイトのテキスト
  • LONGTEXT
    • 可変長文字列。
    • 最大長 4294967295 バイトのテキスト
バイナリ型
  • BYNARY(M)
    • 最大長 255 バイトの固定長バイナリオブジェクト
  • VARBINARY(M)
    • 最大長 255 バイトの可変長バイナリオブジェクト
  • TINYBLOB
    • 最大長 255 バイトのバイナリオブジェクト
  • BLOB
    • 最大長 65535 バイトのバイナリオブジェクト
  • MEDIUMBLOB
    • 最大長 16777215 バイトのバイナリオブジェクト
  • LONGBLOB
    • 最大長 4294967295 バイトのバイナリオブジェクト
その他
  • ENUM ('value1','value2',...)
    • char データ型のリスト。
    • 最大65535個の値を持つことができます。
  • SET ('value1','value2',...)
    • 組。
    • 最大 64個の要素を持つことができます。

参考：
http://dev.mysql.com/doc/refman/5.1/ja/
http://www.bnote.net/mysql/appendix_sql.shtml