レコードの検索
順番がテレコになってしまいましたが、これまでにも何度も出てきたように、レコードを検索するためにはselectコマンドを使います。
完全一致検索の場合の基本構文は以下の通りです。
select フィールド名 from テーブル名 where フィールド名='値';
一方、部分一致検索については、
select フィールド名 from テーブル名 where フィールド名 like '%値%';
となります。
このほか、MySQLでは、より高度な検索を行うために、様々な演算子や関数が用意されています。
ここではそれらのうち主なものを列挙していきます。
演算子について
where節の中では、以下のような比較演算子を用いて、条件を設定することができます。
比較演算子 |
内容 |
where フィールド名 = '値' |
レコードが値と等しい場合 |
where フィールド名 <> '値' |
レコードが値と等しくない場合 |
where フィールド名 > '値' |
レコードが値よりも大きい場合 |
where フィールド名 >= '値' |
レコードが値以上である場合 |
where フィールド名 < '値' |
レコードが値より小さい場合 |
where フィールド名 <= '値' |
レコードが値以下である場合 |
また、等号や大小関係だけでなく、以下のような多様な照合を行うことも可能です。
その他の演算子 |
内容 |
where フィールド名 between '値1' and '値2' |
レコードが値1と値2の間である場合 値1と値2も含む |
where フィールド名 in ('値1', '値2'...) |
レコードがいずれかの値である場合 複数の値を指定できる |
where フィールド名 is null |
レコードがnull値である場合 |
where フィールド名 is not null |
レコードがnull値でない場合 |
where フィールド名 like '値%' |
値から始まるレコードを検索 前方一致 |
where フィールド名 like '%値%' |
値を含むレコードを検索 中間一致 |
where フィールド名 like '%値' |
値で終わるレコードを検索 後方一致 |
以上のような条件文を複数指定する場合は、論理演算子(ブール演算子)を用います。
論理演算子 |
内容 |
where 条件1 and 条件2 |
条件1でありかつまた条件2である場合(論理積) |
where 条件1 or 条件2 |
条件1あるいは条件2である場合(論理和) |
where not (条件) |
条件でない場合(論理否定) |
例えば、フィールド1が値1であり、かつまた、フィールド2が値2でなく、かつまた、フィールド3が値3よりも大きい場合は、
select * where フィールド1 = '値1' and フィールド2 <> '値2' and フィールド3 > '値3';
となりますが、同じことをnot演算子を使って表すと以下のようになります。
select * where not (フィールド1 <> '値1' or フィールド2 = '値2' or フィールド3 <= '値3');
すなわち、(フィールド1が値1でないか、もしくは、フィールド2が値2であるか、もしくは、フィールド3が値3以下であるもの)ではないものを検索せよという意味です。
ややこしいですね。
主要な関数について
MySQLには様々な関数が用意されていますが(詳しくはこちら)、ここでは、その一部を紹介します。
集計などのために、検索されたデータの件数をカウントしたい場合は、count関数を使用します。
基本構文は、
select count(フィールド名) from テーブル名 where 節;
です。
count関数では、null値であるレコードはカウントされません。
また、特定フィールドについて、テーブル全体の件数を知りたい場合は、where節を省略して、
select count(フィールド名) from テーブル名;
とします。
さらに、テーブルにどれだけレコードがあるかを知りたければ、フィールド名を*とすればOKです。
select count(*) from テーブル名;
例えば、bib_tbテーブルにおいて、タイトルに日本を含むレコードの件数をカウントすると、以下のようになります。
mysql> select count(*) from bib_tb where title like '%日本%';[Enter Key]
+----------+
| count(*) |
+----------+
| 641 |
+----------+
1 rows in set (0.00 sec)
|
ここまでに紹介した関数は以下の通りです。
関数 |
内容 |
password('パスワード') |
引数(パスワード)を暗号化する |
count(フィールド名) |
フィールド内のレコード件数のカウント |
次に、主要な関数の一部を示しておきます。
now関数以下は引数をとりません。
関数 |
内容 |
sum(フィールド名) |
フィールド内のレコードの合計値を返す |
avg(フィールド名) |
フィールド内のレコード平均値を返す |
max(フィールド名) |
フィールド内のレコードの最大値を返す |
min(フィールド名) |
フィールド内のレコードの最小値を返す |
length(フィールド名) |
フィールド内のレコードの長さ(バイト数)を返す |
benchmark(カウンタ, 式) |
式をカウンタ回繰り返すのに要した時間を返す |
now() |
現在の時間を返す |
user() |
ログオン中のユーザ名を返す |
database() |
使用中のデータベース名を返す |
version() |
MySQLのバージョンを返す |
benchmark関数はデータベース・サーバの高速性を比較するために用いられます。
例えば、1+1を1億回繰り返すのに、MySQLサーバがどのくらいの時間を要したのかを確認するためには、
select benchmark(100000000, 1+1);
とします。
すると、↓下図のように、7.88秒で完了したことが分かります(CPUはCeleron700Mhz.を使用)。
mysql> select benchmark(100000000, 1+1);[Enter Key]
+---------------------------+
| benchmark(100000000, 1+1) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (7.88 sec)
|
select文のオプション
select文にオプションをつけて、検索方法をコントロールすることもできます。
distinct
異なりデータのみを検索したいという場合は、distinctキーワードを使用します。
基本構文は、
select distinct (フィールド名) from テーブル名 where 節;
となります。
例えば、idが30以下のレコードの出版者(publisher)の異なりデータのみを出力するには、
select distinct (publisher) from bib_tb where id <= 30;
とします。
すると、30件のレコードに対して、10件の異なり出版者が表示されました(↓下図)。
mysql> select distinct (publisher) from bib_tb where id <= 30;[Enter Key]
+------------------+
| publisher |
+------------------+
| 未來社 |
| 未来社 |
| 明治書院 |
| 商事法務研究会 |
| 角川書店 |
| 吉川弘文館 |
| 続群書類従完成会 |
| 高科書店 |
| 岩波書店 |
| 笠間書院 |
+------------------+
10 rows in set (0.00 sec)
|
さらに、異なりデータの件数をカウントしたければ、count関数を併用して、
select count(distinct (フィールド名)) from テーブル名 where 節;
とすればよいことになります。
order by
また、特定のフィールドの値の昇順で検索結果をソートして出力するには、select文の末尾にorder by節をつけて、
select 文 order by フィールド名;
と入力します。
昇順ではなく、降順に出力したければ、フィールド名の後にさらにdescキーワードを付与して、
select 文 order by フィールド名 desc;
とすればOKです。
例えば、bib_tbテーブルにおいて、著者名が池内で始まるレコードをidの降順にソートして出力するには、以下のように入力します。
select id, author from bib_tb where author like '池内%' order by id desc;
mysql> select id, author from bib_tb where author like '%池内%' order by id desc;[Enter Key]
+------+------------+
| id | author |
+------+------------+
| 5701 | 池内紀著 |
| 3392 | 池内恵著 |
| 2752 | 池内俊彦著 |
| 2548 | 池内健次著 |
| 1167 | 池内紀著 |
+------+------------+
5 rows in set (0.00 sec)
|
limit
また、出力件数が膨大になり過ぎる場合などのために、一度に表示する件数を制限することもできます。
その場合、limit節を用いて、
select 文 limit 制限数;
とします。
あるいは、既に読み込んだ件数を入力して、その次のレコードから何件を表示させるのかを指定することも可能です。
この方がより実用的です。
もちろん、実際に読み込んでいなくても、いきなり10件目からとしても構いません。
select 文 limit 既読数, 制限数;
limit節は、通常、order by節と併用されます。
例えば、著者名が池内から始まるレコードを、idの昇順にソートして、3件目(既読2件)から2件だけ表示させたいならば、
select id, author from bib_tb where author like '池内%' order by id limit 2,2;
とします。
mysql> select id, author from bib_tb where author like '池内%' order by id limit 2,2;[Enter Key]
+------+------------+
| id | author |
+------+------------+
| 2752 | 池内俊彦著 |
| 3392 | 池内恵著 |
+------+------------+
2 rows in set (0.00 sec)
|
group by
さて、bib_tbテーブルのpublisherフィールドは、一意のレコードではなく、重複しています。
例えば、岩波書店の資料だけでも301件のレコードがあります。
mysql> select count(id) from bib_tb where publisher='岩波書店';[Enter Key]
+-----------+
| count(id) |
+-----------+
| 301 |
+-----------+
1 rows in set (0.00 sec)
|
そこで、出版者ごとのレコード件数をカウントしたい場合などは、group by節を用います。
基本構文は、
select 文 group by フィールド名;
です。
以下では、出版者名が4文字(8バイト)である出版者ごとのレコード件数を検索します。
select count(publisher), publisher from bib_tb where length(publisher)=8 group by publisher;
mysql> select count(publisher), publisher from bib_tb where length(publisher)=8 group by publisher;[Enter Key]
+------------------+-----------+
| count(publisher) | publisher |
+------------------+-----------+
| 3 | 黎明書房 |
+------------------+-----------+
〜中略〜
| 3 | 梨の木舎 |
| 1 | 理工図書 |
| 1 | 立花書房 |
| 4 | 立風書房 |
| 1 | 龍溪書舎 |
| 2 | 連合出版 |
| 30 | 和泉書院 |
| 1 | 労働大学 |
| 2 | 労働法令 |
| 38 | 勁草書房 |
+------------------+-----------+
217 rows in set (0.15 sec)
|
having
where節では、レコード全体に対して条件指定をしますが、having節を用いれば、レコード全体ではなく、検索されたデータ集合に対して、さらに、条件指定を行うことができます。
基本構文は以下の通りです。
select 文 having 条件;
having節は、通常、集合関数およびgroup by節とともに用いられます。
例えば、出版者ごとのレコード件数が50件以上であるものを、件数の多い順に出力したい場合は、
select count(publisher) as con_pub, publisher from bib_tb group by publisher having con_pub >= 50 order by con_pub desc;
とします。
ここでは、count(publisher)にcon_pubという名前を付け、その後、having節とorder by節では、con_pubフィールドを参照していることに注意して下さい。
フィールド名の後にasを付けると、フィールド名を変更して表示・参照することができます。
また、group by節とhaving節とorder by節が同時に用いられていますが、これらの順序は、必ず、group by→having→order byの順でなければなりません。
mysql> select count(publisher) as con_pub, publisher from bib_tb group by publisher having con_pub >= 50 order by con_pub desc;[Enter Key]
+---------+------------------+
| con_pub | publisher |
+---------+------------------+
| 305 | 平凡社 |
| 301 | 岩波書店 |
| 130 | 講談社 |
| 95 | ゆまに書房 |
| 91 | 有斐閣 |
| 83 | 二玄社 |
| 82 | 日本図書センター |
| 77 | 新潮社 |
| 67 | 勉誠出版 |
| 65 | 明治書院 |
| 65 | 海南出版社 |
| 63 | 東京大学出版会 |
| 63 | 中央公論新社 |
| 60 | 集英社 |
| 58 | 筑摩書房 |
| 53 | 日本評論社 |
| 52 | 明石書店 |
| 52 | ミネルヴァ書房 |
| 50 | 商事法務研究会 |
| 50 | 角川書店 |
| 50 | 日本経済新聞社 |
+---------+------------------+
21 rows in set (0.46 sec)
|
以下にselect文のオプションの一覧を示しておきます。
オプション |
内容 |
select distinct ([field]) ...; |
フィールド内の異なりレコードのみ検索 |
select ... order by [field]; |
フィールドのレコードを昇順に検索 |
select ... order by [field] desc; |
フィールドのレコードを降順に検索 |
select ... limit [count]; |
出力件数の制限 |
select ... limit [offset, count]; |
出力件数の制限 offsetの次のレコードから |
select ... group by [field]; |
同じレコードをグループ化する |
select ... having ...; |
既にある集合に対して条件指定して検索 |
|