よく使うSQL文と、コマンドラインから使うSQLユーティリティについてご紹介します。
よく使うSQLコマンド
各SQLコマンドは、DBMS（データベース管理システム）ごとに一部、差異があります。下記は、MySQLを使った場合のサンプルコードです。
データベースとして、MySQLのサンプルデータベースEmployeesを使っています。
select データ取得
select emp_no,first_name,last_name from employees where hire_date > '1990/01/01' order by emp_no
selectはデータを取得するSQLです。検索条件指定での絞り込み、ソート、グルーピングによる小計の出力等が可能です。
- where 検索条件を指定
- order by ソート条件を指定
- group by グルーピング条件を指定
- limit 1クエリの取得件数を指定
- ofset データ取得の開始位置を指定
insert データ追加
INSERT INTO employees ( emp_no, birth_date, first_name, last_name, gender, hire_date ) VALUES ( '500001', '1980/01/01', 'Akira', 'Sato', 'M', '2002/04/08' );
insertはデータを追加する命令です。
- ON DUPLICATE KEY UPDATE すでに同一プライマリキーのレコードが存在する場合、updateを実行
- LOW_PRIORITY テーブルロックの優先順位を低くする
- HIGH_PRIORITY テーブルロックの優先順位を高くする
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文
update データ更新
UPDATE employees SET last_name = 'Satoh' WHERE employees.emp_no = 500001;
updateはデータを更新する命令です。
- where 更新するレコードを指定するための条件
- order by ソート条件
- limit 1度のクエリの最大件数
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.11 UPDATE 構文
delete データ削除
DELETE FROM employees WHERE employees.emp_no = 500001
deleteはデータを削除する命令です。
- where 削除するレコードの条件。
where句には、select文と同様の結合（inner join、left joinなど）が使えます。
管理者向けのSQLコマンド
管理者向けのSQLコマンドは、DBMSによって大きくことなります。下記は、MySQLを前提としています。
create table テーブル作成
CREATE TABLE testtable ( no CHAR(4) NOT NULL, name CHAR(20) NOT NULL, desc TEXT NOT NULL, PRIMARY KEY (no) )
テーブルを構成するカラムとデータ型、プライマリキーなどを指定します。必要に応じて、いかの
- CHARACTER SET 文字セット
- COLLATE 照合順序
- COMMENT テーブルに対する注釈
- ENGINE データベースエンジン名
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文
truncate table テーブルを高速に空にする
truncate table testtable
トランザクションログを作らずにデータを高速削除します。
一般に、delete文で大量削除を行う場合は、処理速度が遅くなるため、メンテナンス目的で大量削除を行う場合はtruncateを使います。
drop table テーブルを構造ごと削除
drop table testtable
- RESTRICT 何もしない。
- CASCADE 何もしない。
RESTRICTとCASCADEオプションは、postgresqlのSQLとの互換用に用意されています。
analyze table オプティマイザ用にデータを分析
analyze table testtable
- NO_WRITE_TO_BINLOG ログ出力の抑制
- LOCAL ログ出力の抑制
merge データが存在すれば更新、存在しなければ追加
mergeはoracleで利用可能なSQLコマンド。
データが存在すればupdate、存在しなければinsertを行います。
例えば、MySQLでは、mergeは使えませんが、代わりにinsert文にon duplicate key udpate を指定することで、同様の動作が可能です。
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.1 ANALYZE TABLE 構文
optimize talbe テーブルの最適化
optimize table testtable
データベース領域のデフラグと、テーブルの最適化（Analyze）を行います。
断片化したデータを最適化して、空き容量を可能な限り空けるために使います。断片化が起こると、テーブルフルスキャン（全件検索）する検索で性能が落ちます。
具体的には、テーブルデータと関連するインデックスのデータ領域を再編成します。
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.2.4 OPTIMIZE TABLE 構文
通常は、自動的に実行されますが、即時実行したい場合に使います。
なお、実行中はテーブルがロックされる点に注意。
MySQLの場合は、データベース領域がInnoDBか、MyISAMかによって処理内容が異なります。
ユーティリティコマンド
コマンドラインから実行できる、データベース関連のユーティリティコマンドです。
バックアップ
DBMSごとにコマンド、仕様とも異なります。SQL Serverはbcp、Oracleはexp、MySQLはmysqldumpというコマンドが用意されています。
例えばMySQLのmysqldumpの場合は、全テーブルまたは指定テーブルのデータをエクスポートする機能があります。
フォーマットはSQLやCSV、JSONなどが指定可能で、zip形式で圧縮できるため、ストレージ領域を節約できます。
リストアを考慮すると、バックアップ用にはSQL形式＆zip圧縮形式が使い勝手が良いでしょう。
エクスポートしたSQLには、テーブル作成用のcarete table文、データを挿入するinsert文、インデックス追加用のSQLが含まれます。
--
-- テーブルの構造 `departments`
--
CREATE TABLE IF NOT EXISTS `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- テーブルのデータのダンプ `departments`
--
INSERT INTO `departments` (`dept_no`, `dept_name`) VALUES
('d009', 'Customer Service'),
('d005', 'Development'),
('d002', 'Finance'),
('d003', 'Human Resources'),
('d001', 'Marketing'),
('d010', 'New Section'),
('d004', 'Production'),
('d006', 'Quality Management'),
('d008', 'Research'),
('d007', 'Sales');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `departments`
--
ALTER TABLE `departments`
ADD PRIMARY KEY (`dept_no`),
ADD UNIQUE KEY `dept_name` (`dept_name`);
リストア
エクスポートしたデータをインポートするコマンド。DBMSごとにコマンド、仕様とも異なります。SQL Serverはbcp、Oracleはimp、MySQLはmysqldumpで出力したSQLをmysqlコマンドで実行することでリストアを行います。
例えば、MySQLでは、mysqlの–sql_mode（SQL互換モード指定）で、OracleやSQL Server、DB2など他のDBMSと互換性のあるSQLをインポート可能です。
SQLコマンドのまとめ
SQLの基本コマンドは、select,insert,update,delete
SQLの管理者向けコマンドはDBMSによって差異が大きい
データベースのバックアップは、コマンドラインで実行可能なユーティリティコマンドが用意されている。