屑俺日記

☆ BETWEEN

指定された条件を「含む」らしい。

mysql> SELECT * FROM test1;
+--------+---------+
| number | content |
+--------+---------+
|      1 | candy   |
|      2 | peech   |
|      3 | apple   |
|      4 | banana  |
|      5 | cookie  |
|      6 | cake    |
+--------+---------+
6 rows in set (0.01 sec)

mysql> SELECT * FROM test1 WHERE number BETWEEN 3 AND 5 
+--------+---------+
| number | content |
+--------+---------+
|      3 | apple   |
|      4 | banana  |
|      5 | cookie  |
+--------+---------+
3 rows in set (0.02 sec)

それから、順序が逆だと駄目。

mysql> SELECT * FROM test1 WHERE number BETWEEN 5 AND 3;
Empty set (0.01 sec)

次の文と等価らしい。

mysql> SELECT * FROM test1 WHERE number >= 3 AND number <= 5;
+--------+---------+
| number | content |
+--------+---------+
|      3 | apple   |
|      4 | banana  |
|      5 | cookie  |
+--------+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE number >= 5 AND number <= 3;
Empty set (0.00 sec)
 

☆ IN と SUB QUERY

mysql> SELECT * FROM test3;
+--------+-----------+
| number | item      |
+--------+-----------+
|      1 | banana    |
|      2 | apple     |
|      3 | chocolate |
|      4 | cookie    |
|      5 | carrot    |
|      6 | potate    |
+--------+-----------+
6 rows in set (0.00 sec)
 
mysql> SELECT * FROM test4;
+--------+--------+
| number | item   |
+--------+--------+
|      1 | banana |
|      2 | apple  |
+--------+--------+
2 rows in set (0.01 sec)

別のテーブルの値を抽出キーに使える、でいいのかしら。

mysql> SELECT number,item FROM test3 
    ->  WHERE item IN
    ->  (SELECT item FROM test4);
+--------+--------+
| number | item   |
+--------+--------+
|      1 | banana |
|      2 | apple  |
+--------+--------+
2 rows in set (0.01 sec)

☆ LIKE と REGEXP

単純検索と正規表現検索。みたいだ。 LIKE は_(任意の一文字)、%(任意の文字列)程度。

mysql> SELECT * FROM test3 WHERE item LIKE 'a%';
+--------+-------+
| number | item  |
+--------+-------+
|      2 | apple |
+--------+-------+
1 row in set (0.00 sec)
 
mysql> SELECT * FROM test3 WHERE item LIKE 'c_____';
+--------+--------+
| number | item   |
+--------+--------+
|      4 | cookie |
|      5 | carrot |
+--------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test3 WHERE item REGEXP 'p.*';
+--------+--------+
| number | item   |
+--------+--------+
|      2 | apple  |
|      6 | potate |
+--------+--------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM test3 WHERE item REGEXP '^p.*';
+--------+--------+
| number | item   |
+--------+--------+
|      6 | potate |
+--------+--------+
1 row in set (0.01 sec)

大文字がデフォルトというのは少々面倒くさい。