Skip to content

Instantly share code, notes, and snippets.

@kenjiskywalker

kenjiskywalker/sql-command.md

Created February 10, 2012 18:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kenjiskywalker/1791321 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kenjiskywalker/1791321 to your computer and use it in GitHub Desktop.
Download ZIP
SQLコマンド一覧
Raw
sql-command.md

データベース追加

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): における 番目の位置からの文字をすべて読み込みます。

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>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment