SQL Basic Syntaxes

dmpファイルインストール

mysql -u root -p school < school.dmp

エラーの対処法

syntax error → まずはタイポを疑う。特にエラー文で指定している文の直前の単語あたりを疑う。

DESC 文

DESC exam;

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | NO   | PRI | 0       |       |
| subject | varchar(128) | NO   | PRI |         |       |
| score   | int          | NO   |     | 0       |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.008 sec)

SELECT 文

超基本構文

SELECT id, name FROM student;

+----+---------------+
| id | name          |
+----+---------------+
|  1 | 佐藤 琢磨      |
|  2 | 大塚 愛　      |
|  3 | 藤井 隆　      |
|  4 | 福原 愛　      |
|  5 | 大黒 将志      |
+----+---------------+
5 rows in set (0.001 sec)

SELECT * FROM student;

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  1 | 佐藤 琢磨      | 1977-01-28 | CG    |
|  2 | 大塚 愛　      | 1982-09-09 | Web   |
|  3 | 藤井 隆　      | 1972-03-10 | Web   |
|  4 | 福原 愛　      | 1988-11-01 | CG    |
|  5 | 大黒 将志      | 1980-05-04 | NULL  |
+----+---------------+------------+-------+
5 rows in set (0.001 sec)

件数指定

SELECT * FROM student LIMIT 1;

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  1 | 佐藤 琢磨      | 1977-01-28 | CG    |
+----+---------------+------------+-------+
1 row in set (0.001 sec)

SELECT * FROM student LIMIT 1, 3;

+----+------------+------------+-------+
| id | name       | birthday   | class |
+----+------------+------------+-------+
|  2 | 大塚 愛     | 1982-09-09 | Web   |
|  3 | 藤井 隆     | 1972-03-10 | Web   |
|  4 | 福原 愛     | 1988-11-01 | CG    |
+----+------------+------------+-------+
3 rows in set (0.000 sec)

開始位置の指定

SELECT * FROM student LIMIT 1 OFFSET 3;

+----+------------+------------+-------+
| id | name       | birthday   | class |
+----+------------+------------+-------+
|  4 | 福原 愛     | 1988-11-01 | CG    |
+----+------------+------------+-------+
1 row in set (0.000 sec)

エイリアスをつける

SELECT name AS 名前 FROM student;

SELECT name 名前 FROM student;

+---------------+
| 名前          |
+---------------+
| 佐藤 琢磨      |
| 大塚 愛　      |
| 藤井 隆　      |
| 福原 愛　      |
| 大黒 将志      |
+---------------+
5 rows in set (0.000 sec)

重複排除

SELECT DISTINCT class FROM student;

+-------+
| class |
+-------+
| CG    |
| Web   |
| NULL  |
+-------+
3 rows in set (0.000 sec)

並び替え

指定をしないと並び順は不定となる。 そのため、複数件が該当する場合には、必ず並び替えを指定すること。

昇順（デフォルトで省略可）

SELECT * FROM exam ORDER BY score ASC;

降順

SELECT * FROM exam ORDER BY score DESC;

+----+--------------+-------+
| id | subject      | score |
+----+--------------+-------+
|  1 | PC基礎       |    92 |
|  4 | PC基礎       |    85 |
|  2 | デザイン      |    80 |
|  1 | デザイン      |    77 |
|  3 | デザイン      |    74 |
|  4 | デザイン      |    64 |
|  2 | PC基礎       |    51 |
+----+--------------+-------+
7 rows in set (0.001 sec)

複数のソートキーによる指定

SELECT * FROM exam ORDER BY id, score DESC;

+----+--------------+-------+
| id | subject      | score |
+----+--------------+-------+
|  1 | PC基礎       |    92 |
|  1 | デザイン      |    77 |
|  2 | デザイン      |    80 |
|  2 | PC基礎       |    51 |
|  3 | デザイン      |    74 |
|  4 | PC基礎       |    85 |
|  4 | デザイン      |    64 |
+----+--------------+-------+
7 rows in set (0.001 sec)

比較演算子

SELECT * FROM student
WHERE class = "CG"
ORDER BY id;

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  1 | 佐藤 琢磨       | 1977-01-28 | CG    |
|  4 | 福原 愛　       | 1988-11-01 | CG    |
+----+---------------+------------+-------+
2 rows in set (0.003 sec)

論理演算子

SELECT * FROM exam
WHERE subject = "PC基礎"
AND score >= 70
ORDER BY id;

+----+----------+-------+
| id | subject  | score |
+----+----------+-------+
|  1 | PC基礎   |    92 |
|  4 | PC基礎   |    85 |
+----+----------+-------+
2 rows in set (0.003 sec)

範囲指定

SELECT * FROM student
WHERE birthday BETWEEN "1970-01-01" AND "1979-12-31"
ORDER BY id;

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  1 | 佐藤 琢磨      | 1977-01-28 | CG    |
|  3 | 藤井 隆　      | 1972-03-10 | Web   |
+----+---------------+------------+-------+
2 rows in set (0.006 sec)

列挙

SELECT * FROM student
WHERE id IN (1, 3, 5);

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  1 | 佐藤 琢磨      | 1977-01-28 | CG    |
|  3 | 藤井 隆　      | 1972-03-10 | Web   |
|  5 | 大黒 将志      | 1980-05-04 | NULL  |
+----+---------------+------------+-------+
3 rows in set (0.002 sec)

NULL判定

SELECT * FROM student
WHERE class IS NULL;

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  5 | 大黒 将志      | 1980-05-04 | NULL  |
+----+---------------+------------+-------+
1 row in set (0.012 sec)

パターン指定

SELECT * FROM student
WHERE name LIKE "%愛";

+----+------------+------------+-------+
| id | name       | birthday   | class |
+----+------------+------------+-------+
|  2 | 大塚 愛     | 1982-09-09 | Web   |
|  4 | 福原 愛     | 1988-11-01 | CG    |
+----+------------+------------+-------+
2 rows in set (0.003 sec)

否定

SELECT * FROM student
WHERE NOT (id = 1);

+----+---------------+------------+-------+
| id | name          | birthday   | class |
+----+---------------+------------+-------+
|  2 | 大塚 愛　       | 1982-09-09 | Web   |
|  3 | 藤井 隆　       | 1972-03-10 | Web   |
|  4 | 福原 愛　       | 1988-11-01 | CG    |
|  5 | 大黒 将志       | 1980-05-04 | NULL  |
+----+---------------+------------+-------+
4 rows in set (0.001 sec)