SQLコマンド一覧

  
Gistからインポートできるのかと思ってた。
初めてですが、自分用SQLメモのせてみますね。  
  
```sql:SQLコマンド一覧
## データベース追加  
mysql> CREATE DATABASE hogehogee;  
  
  
## デーブル追加  
mysql> CREATE TABLE user (username varchar(10), address varchar(10));  
mysql> DESCRIBE user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
2 rows in set (0.00 sec)  
mysql>  
  
  
  
## データ追加  
mysql> INSERT INTO user VALUES ("name1","address1");  
mysql> INSERT INTO user VALUES ("name2","address3");  
  
mysql> SELECT * FROM user;  
+----------+----------+  
| username | address  |  
+----------+----------+  
| name1    | address1 |  
| name2    | address3 |  
+----------+----------+  
2 rows in set (0.00 sec)  
  
mysql> SELECT username FROM user;  
+----------+  
| username |  
+----------+  
| name1    |  
| name2    |  
+----------+  
2 rows in set (0.00 sec)  
  
mysql> SELECT address FROM user;  
+----------+  
| address  |  
+----------+  
| address1 |  
| address3 |  
+----------+  
2 rows in set (0.00 sec)  
  
  
## データ追加その2  
  
mysql> INSERT INTO user VALUES ("name3", "address333", "3333", "NULL", "NULL");  
Query OK, 1 row affected, 1 warning (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql>   
  
  
## データ追加その3  
  
mysql> INSERT INTO host (host, username) VALUES ("hello", "name1"), ("hello", "name2");  
Query OK, 2 rows affected (0.00 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
  
mysql>   
mysql> SELECT * FROM host;  
+-------+----------+  
| host  | username |  
+-------+----------+  
| hello | name1    |  
| hello | name2    |  
+-------+----------+  
2 rows in set (0.00 sec)  
  
mysql>   
  
mysql>   
mysql> INSERT INTO host (host, username) VALUES ("world", "name3"), ("world", "name3");  
Query OK, 2 rows affected (0.00 sec)  
Records: 2  Duplicates: 0  Warnings: 0  
  
mysql>   
mysql> SELECT * FROM host;  
+-------+----------+  
| host  | username |  
+-------+----------+  
| hello | name1    |  
| hello | name2    |  
| world | name1    |  
| world | name2    |  
| world | name3    |  
| world | name3    |  
+-------+----------+  
6 rows in set (0.00 sec)  
  
mysql>   
mysql>   
  
  
## 数数える  
  
mysql> SELECT COUNT(*) FROM user LIMIT 5;  
+----------+  
| COUNT(*) |  
+----------+  
|        2 |  
+----------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT COUNT(DISTINCT username) FROM user;  
+--------------------------+  
| COUNT(DISTINCT username) |  
+--------------------------+  
|                        3 |  
+--------------------------+  
1 row in set (0.00 sec)  
  
mysql> SELECT COUNT(username) FROM user;  
+-----------------+  
| COUNT(username) |  
+-----------------+  
|               4 |  
+-----------------+  
1 row in set (0.00 sec)  
  
mysql>   
  
  
  
## SELECT  
  
mysql>   
mysql> SELECT username FROM user;  
+----------+  
| username |  
+----------+  
| name1    |  
| name2    |  
| name1    |  
| name3    |  
+----------+  
4 rows in set (0.00 sec)  
  
  
## 重複は出力しないSELECT  
  
mysql>   
mysql> SELECT DISTINCT username FROM user;  
+----------+  
| username |  
+----------+  
| name1    |  
| name2    |  
| name3    |  
+----------+  
3 rows in set (0.00 sec)  
  
mysql>   
  
  
## WHERE  
  
mysql>   
mysql> SELECT username, address, price1 FROM user WHERE price1 > 5000;  
+----------+------------+--------+  
| username | address    | price1 |  
+----------+------------+--------+  
| name2    | address3   |   9999 |  
| name1    | address100 |  10000 |  
+----------+------------+--------+  
2 rows in set (0.00 sec)  
  
mysql>   
  
  
## LIKE "_"=任意の一文字 "%"=いつものとおり  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 LIKE "100%" ;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
2 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 LIKE "100_" ;  
+----------+----------+--------+--------+------+  
| username | address  | price1 | price2 | text |  
+----------+----------+--------+--------+------+  
| name1    | address1 |   1000 |   NULL | NULL |  
+----------+----------+--------+--------+------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 LIKE "100__" ;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 LIKE "100___" ;  
Empty set (0.00 sec)  
  
mysql>   
  
  
## カラムの追加  
  
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
2 rows in set (0.00 sec)  
  
mysql>   
mysql> ALTER TABLE user ADD (price varchar(30));  
Query OK, 3 rows affected (0.01 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
| price    | varchar(30) | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
3 rows in set (0.01 sec)  
  
  
## カラムの追加その2  
  
  
mysql> ALTER TABLE user ADD (price1 int(100)), ADD (text text);  
Query OK, 3 rows affected (0.01 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql>   
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
| price1   | int(100)    | YES  |     | NULL    |       |  
| text     | text        | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
4 rows in set (0.00 sec)  
  
mysql>  
  
  
## 任意の場所にカラムを追加  
  
mysql> ALTER TABLE user ADD price2 int(100) AFTER price1;  
Query OK, 3 rows affected (0.00 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql>   
mysql>   
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
| price1   | int(100)    | YES  |     | NULL    |       |  
| price2   | int(100)    | YES  |     | NULL    |       |  
| text     | text        | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
5 rows in set (0.00 sec)  
  
mysql>   
  
  
## カラムの削除  
  
mysql> ALTER TABLE user DROP price;  
Query OK, 3 rows affected (0.00 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
2 rows in set (0.00 sec)  
  
  
## 複数のカラムの削除  
  
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
| price1   | int(100)    | YES  |     | NULL    |       |  
| text     | tinytext    | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
4 rows in set (0.01 sec)  
  
mysql>   
mysql> ALTER TABLE user DROP price1, DROP text;  
Query OK, 3 rows affected (0.01 sec)  
Records: 3  Duplicates: 0  Warnings: 0  
  
mysql>   
mysql> DESC user;  
+----------+-------------+------+-----+---------+-------+  
| Field    | Type        | Null | Key | Default | Extra |  
+----------+-------------+------+-----+---------+-------+  
| username | varchar(10) | YES  |     | NULL    |       |  
| address  | varchar(10) | YES  |     | NULL    |       |  
+----------+-------------+------+-----+---------+-------+  
2 rows in set (0.00 sec)  
  
mysql>   
  
  
  
## UPDATE  
  
  
mysql>   
mysql> UPDATE user SET price1 = "10000" WHERE username = "name1" ;  
Query OK, 2 rows affected (0.00 sec)  
Rows matched: 2  Changed: 2  Warnings: 0  
  
mysql>   
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |  10000 |   NULL | NULL |  
| name2    | address3   |   NULL |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
3 rows in set (0.00 sec)  
  
mysql> UPDATE user SET price1 = "1000" WHERE username = "name1" AND address = "address1" ;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
mysql>   
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   NULL |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
3 rows in set (0.00 sec)  
  
mysql>   
  
  
## UPDATEその2  
  
mysql> UPDATE user SET price1 = "9999" WHERE username = "name2";  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
  
mysql>   
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
3 rows in set (0.00 sec)  
  
mysql>   
  
  
## ORDER BY  
  
mysql>   
mysql> SELECT * FROM user ORDER BY price1;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql>   
  
mysql>   
mysql> SELECT * FROM user ORDER BY price1 ASC;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user ORDER BY price1 DESC;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address100 |  10000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
| name1    | address1   |   1000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql>   
  
  
## OR AND BETWEEN  
  
mysql> SELECT * FROM user WHERE price1 > 5000 OR ( price1 < 9999 AND price1 > 2000 );  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
3 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 < 9999 AND price1 > 2000 ;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
1 row in set (0.01 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 > 10000;  
Empty set (0.00 sec)  
  
mysql>   
  
mysql> SELECT * FROM user WHERE price1 > 10000 OR ( price1 < 9999 AND price1 > 2000 );  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql>   
  
  
## BETWEEN  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 BETWEEN 200 AND 5000 ;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
2 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE price1 BETWEEN 5000 AND 2000 ;  
Empty set (0.00 sec)  
  
mysql>   
mysql>   
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql>   
  
  
## IN ORの結合  
  
mysql> SELECT * FROM user WHERE username IN ("name1");  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
2 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE username IN ("name2");  
+----------+----------+--------+--------+------+  
| username | address  | price1 | price2 | text |  
+----------+----------+--------+--------+------+  
| name2    | address3 |   9999 |   NULL | NULL |  
+----------+----------+--------+--------+------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user WHERE username IN ("name1","name2");  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
3 rows in set (0.00 sec)  
  
mysql>   
  
  
## SUM AVE MAX MIN COUNT  
  
mysql> SELECT AVG(price1) FROM user;  
+-------------+  
| AVG(price1) |  
+-------------+  
|   6083.0000 |  
+-------------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT MAX(price1) FROM user;  
+-------------+  
| MAX(price1) |  
+-------------+  
|       10000 |  
+-------------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT MIN(price1) FROM user;  
+-------------+  
| MIN(price1) |  
+-------------+  
|        1000 |  
+-------------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT COUNT(price1) FROM user;  
+---------------+  
| COUNT(price1) |  
+---------------+  
|             4 |  
+---------------+  
1 row in set (0.00 sec)  
  
mysql>   
  
  
  
## GROUP BY 特定の項目を足してあるレコードの重複した情報の結果を出力  
  
mysql>   
mysql> SELECT username, SUM(price1) FROM user GROUP BY username;  
+----------+-------------+  
| username | SUM(price1) |  
+----------+-------------+  
| name1    |       11000 |  
| name2    |        9999 |  
| name3    |        3333 |  
+----------+-------------+  
3 rows in set (0.00 sec)  
  
mysql>   
mysql>   
mysql> SELECT username, SUM(price2) FROM user GROUP BY username;  
+----------+-------------+  
| username | SUM(price2) |  
+----------+-------------+  
| name1    |        NULL |  
| name2    |        NULL |  
| name3    |           0 |  
+----------+-------------+  
3 rows in set (0.00 sec)  
  
mysql>  
  
  
## GROUP BY の後のWHEREみたいなHAVING  
  
mysql>   
mysql> SELECT username, SUM(price1) FROM user GROUP BY username HAVING SUM(price1) > 5000 ;  
+----------+-------------+  
| username | SUM(price1) |  
+----------+-------------+  
| name1    |       11000 |  
| name2    |        9999 |  
+----------+-------------+  
2 rows in set (0.00 sec)  
  
mysql>   
  
  
## 別名をつける  
  
mysql>   
mysql> SELECT BETSUMEI.username FROM user BETSUMEI;  
+----------+  
| username |  
+----------+  
| name1    |  
| name2    |  
| name1    |  
| name3    |  
+----------+  
4 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT BETSUMEI.username, price1 FROM user BETSUMEI;  
+----------+--------+  
| username | price1 |  
+----------+--------+  
| name1    |   1000 |  
| name2    |   9999 |  
| name1    |  10000 |  
| name3    |   3333 |  
+----------+--------+  
4 rows in set (0.00 sec)  
  
mysql>   
  
  
mysql>   
mysql> SELECT username "USERRRR" FROM user;  
+---------+  
| USERRRR |  
+---------+  
| name1   |  
| name2   |  
| name1   |  
| name3   |  
+---------+  
4 rows in set (0.00 sec)  
  
mysql>   
  
mysql>   
mysql> SELECT username "USERRRR", price1 "PRICEEEEE" FROM user;  
+---------+-----------+  
| USERRRR | PRICEEEEE |  
+---------+-----------+  
| name1   |      1000 |  
| name2   |      9999 |  
| name1   |     10000 |  
| name3   |      3333 |  
+---------+-----------+  
4 rows in set (0.01 sec)  
  
mysql>  
  
mysql>   
mysql> SELECT username "USERRRR", SUM(price1) "SUMPRICE" FROM user;  
+---------+----------+  
| USERRRR | SUMPRICE |  
+---------+----------+  
| name1   |    24332 |  
+---------+----------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql>   
mysql> SELECT username "USERRRR", SUM(price1) "SUMPRICE" FROM user GROUP BY username ;  
+---------+----------+  
| USERRRR | SUMPRICE |  
+---------+----------+  
| name1   |    11000 |  
| name2   |     9999 |  
| name3   |     3333 |  
+---------+----------+  
3 rows in set (0.00 sec)  
  
mysql>   
  
  
  
## JOIN  
  
  
mysql>   
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM host;  
+-------+----------+  
| host  | username |  
+-------+----------+  
| hello | name1    |  
| hello | name2    |  
| world | name1    |  
| world | name2    |  
| world | name3    |  
| world | name3    |  
| world | name4    |  
| world | name4    |  
+-------+----------+  
8 rows in set (0.00 sec)  
  
mysql>   
  
  
mysql> SELECT host.username, SUM(user.price1) FROM user, host WHERE user.username = host.username GROUP BY host.username;  
+----------+------------------+  
| username | SUM(user.price1) |  
+----------+------------------+  
| name1    |            22000 |  
| name2    |            19998 |  
| name3    |             6666 |  
+----------+------------------+  
3 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT B.username, SUM(A.price1) FROM user A, host B WHERE A.username = B.username GROUP BY B.username;  
+----------+---------------+  
| username | SUM(A.price1) |  
+----------+---------------+  
| name1    |         22000 |  
| name2    |         19998 |  
| name3    |          6666 |  
+----------+---------------+  
3 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT B.username "USERNAME", SUM(A.price1) "SUM" FROM user A, host B WHERE A.username = B.username GROUP BY B.username;  
+----------+-------+  
| USERNAME | SUM   |  
+----------+-------+  
| name1    | 22000 |  
| name2    | 19998 |  
| name3    |  6666 |  
+----------+-------+  
3 rows in set (0.00 sec)  
  
mysql>   
  
  
  
## JOINその2  
  
mysql>   
mysql> SELECT * FROM user CROSS JOIN host;  
+----------+------------+--------+--------+------+-------+----------+  
| username | address    | price1 | price2 | text | host  | username |  
+----------+------------+--------+--------+------+-------+----------+  
| name1    | address1   |   1000 |   NULL | NULL | hello | name1    |  
| name2    | address3   |   9999 |   NULL | NULL | hello | name1    |  
| name1    | address100 |  10000 |   NULL | NULL | hello | name1    |  
| name3    | address333 |   3333 |      0 | NULL | hello | name1    |  
| name1    | address1   |   1000 |   NULL | NULL | hello | name2    |  
| name2    | address3   |   9999 |   NULL | NULL | hello | name2    |  
| name1    | address100 |  10000 |   NULL | NULL | hello | name2    |  
| name3    | address333 |   3333 |      0 | NULL | hello | name2    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name1    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name1    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name1    |  
| name3    | address333 |   3333 |      0 | NULL | world | name1    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name2    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name2    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name2    |  
| name3    | address333 |   3333 |      0 | NULL | world | name2    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name3    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name3    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name3    |  
| name3    | address333 |   3333 |      0 | NULL | world | name3    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name3    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name3    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name3    |  
| name3    | address333 |   3333 |      0 | NULL | world | name3    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name4    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name4    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name4    |  
| name3    | address333 |   3333 |      0 | NULL | world | name4    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name4    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name4    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name4    |  
| name3    | address333 |   3333 |      0 | NULL | world | name4    |  
+----------+------------+--------+--------+------+-------+----------+  
32 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user INNER JOIN host ON user.username = host.username;  
+----------+------------+--------+--------+------+-------+----------+  
| username | address    | price1 | price2 | text | host  | username |  
+----------+------------+--------+--------+------+-------+----------+  
| name1    | address1   |   1000 |   NULL | NULL | hello | name1    |  
| name1    | address100 |  10000 |   NULL | NULL | hello | name1    |  
| name2    | address3   |   9999 |   NULL | NULL | hello | name2    |  
| name1    | address1   |   1000 |   NULL | NULL | world | name1    |  
| name1    | address100 |  10000 |   NULL | NULL | world | name1    |  
| name2    | address3   |   9999 |   NULL | NULL | world | name2    |  
| name3    | address333 |   3333 |      0 | NULL | world | name3    |  
| name3    | address333 |   3333 |      0 | NULL | world | name3    |  
+----------+------------+--------+--------+------+-------+----------+  
8 rows in set (0.00 sec)  
  
mysql>   
mysql>   
  
  
## USING  
  
mysql>   
mysql> SELECT * FROM user INNER JOIN host USING (username);  
+----------+------------+--------+--------+------+-------+  
| username | address    | price1 | price2 | text | host  |  
+----------+------------+--------+--------+------+-------+  
| name1    | address1   |   1000 |   NULL | NULL | hello |  
| name1    | address100 |  10000 |   NULL | NULL | hello |  
| name2    | address3   |   9999 |   NULL | NULL | hello |  
| name1    | address1   |   1000 |   NULL | NULL | world |  
| name1    | address100 |  10000 |   NULL | NULL | world |  
| name2    | address3   |   9999 |   NULL | NULL | world |  
| name3    | address333 |   3333 |      0 | NULL | world |  
| name3    | address333 |   3333 |      0 | NULL | world |  
+----------+------------+--------+--------+------+-------+  
8 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT * FROM user INNER JOIN host USING (price1);  
ERROR 1054 (42S22): Unknown column 'price1' in 'from clause'  
mysql>   
mysql>   
  
  
  
  
## SUBQUERY  
  
mysql>   
mysql> SELECT * FROM user WHERE username IN ("name1", "name2");  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
+----------+------------+--------+--------+------+  
3 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT SUM(price1) "SUMMMM" FROM user WHERE username IN (SELECT username FROM host WHERE host = "hello");  
+--------+  
| SUMMMM |  
+--------+  
|  20999 |  
+--------+  
1 row in set (0.00 sec)  
  
mysql>   
  
  
## UNION  
  
ysql>   
mysql> SELECT username FROM user UNION SELECT username FROM host;  
+----------+  
| username |  
+----------+  
| name1    |  
| name2    |  
| name3    |  
| name4    |  
+----------+  
4 rows in set (0.00 sec)  
  
mysql>   
mysql> SELECT username FROM user UNION ALL SELECT username FROM host;  
+----------+  
| username |  
+----------+  
| name1    |  
| name2    |  
| name1    |  
| name3    |  
| name1    |  
| name2    |  
| name1    |  
| name2    |  
| name3    |  
| name3    |  
| name4    |  
| name4    |  
+----------+  
12 rows in set (0.00 sec)  
  
mysql>   
  
  
  
## CONCAT  
  
mysql>   
mysql> SELECT CONCAT(username, price1) FROM user WHERE price1 = 1000;  
+--------------------------+  
| CONCAT(username, price1) |  
+--------------------------+  
| name11000                |  
+--------------------------+  
1 row in set (0.01 sec)  
  
mysql> SELECT * FROM user;  
+----------+------------+--------+--------+------+  
| username | address    | price1 | price2 | text |  
+----------+------------+--------+--------+------+  
| name1    | address1   |   1000 |   NULL | NULL |  
| name2    | address3   |   9999 |   NULL | NULL |  
| name1    | address100 |  10000 |   NULL | NULL |  
| name3    | address333 |   3333 |      0 | NULL |  
+----------+------------+--------+--------+------+  
4 rows in set (0.00 sec)  
  
mysql> SELECT CONCAT(username, price1) FROM user WHERE price1 = 10000;  
+--------------------------+  
| CONCAT(username, price1) |  
+--------------------------+  
| name110000               |  
+--------------------------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql>   
mysql>   
mysql> SELECT CONCAT(username, address, price1) FROM user WHERE price1 = 10000;  
+-----------------------------------+  
| CONCAT(username, address, price1) |  
+-----------------------------------+  
| name1address10010000              |  
+-----------------------------------+  
1 row in set (0.00 sec)  
  
mysql>   
  
  
## SUBSTR HEXとかおもしろい  
SUBSTR(str,pos): <str> における <pos> 番目の位置からの文字をすべて読み込みます。  
  
  
mysql>   
mysql> SELECT SUBSTR(username, 1) FROM user WHERE price1 = 10000;  
+---------------------+  
| SUBSTR(username, 1) |  
+---------------------+  
| name1               |  
+---------------------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT SUBSTR(username, 4) FROM user WHERE price1 = 10000;  
+---------------------+  
| SUBSTR(username, 4) |  
+---------------------+  
| e1                  |  
+---------------------+  
1 row in set (0.00 sec)  
  
mysql>   
  
mysql>   
mysql> SELECT BIN('2');  
+----------+  
| BIN('2') |  
+----------+  
| 10       |  
+----------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql> SELECT BIT_LENGTH('2');  
+-----------------+  
| BIT_LENGTH('2') |  
+-----------------+  
|               8 |  
+-----------------+  
1 row in set (0.00 sec)  
  
mysql> SELECT BIT_LENGTH('hoge');  
+--------------------+  
| BIT_LENGTH('hoge') |  
+--------------------+  
|                 32 |  
+--------------------+  
1 row in set (0.00 sec)  
  
mysql> SELECT BIT_LENGTH('h');  
+-----------------+  
| BIT_LENGTH('h') |  
+-----------------+  
|               8 |  
+-----------------+  
1 row in set (0.00 sec)  
  
mysql>   
  
  
## REIM  
  
mysql>   
mysql> SELECT RTRIM("  barbar   ");  
+----------------------+  
| RTRIM("  barbar   ") |  
+----------------------+  
|   barbar             |  
+----------------------+  
1 row in set (0.00 sec)  
  
mysql>   
mysql>   
mysql> SELECT LTRIM("  barbar   ");  
+----------------------+  
| LTRIM("  barbar   ") |  
+----------------------+  
| barbar               |  
+----------------------+  
1 row in set (0.00 sec)  
  
mysql>   
  
```