SQL Statements

An SQL statement is one complete instruction you give to MySQL: “create this table”, “add this row”, “show me these rows”.

1

SELECT 'one statement' AS example;

1
2
3
4
5
6

+---------------+
| example       |
+---------------+
| one statement |
+---------------+
1 row in set

The parts of a statement

Most statements follow the same pattern: a command word, then what it applies to, then optional clauses that refine it.

1
2
3

CREATE TABLE tags (id INT PRIMARY KEY, label VARCHAR(30));
INSERT INTO tags (id, label) VALUES (1, 'sql');
SELECT id, label FROM tags WHERE id = 1;

1
2
3
4
5
6

+----+-------+
| id | label |
+----+-------+
|  1 | sql   |
+----+-------+
1 row in set

The semicolon

The semicolon tells the client where one statement ends. It is not part of the statement the server runs. This matters because:

• You can put several statements on one line or many lines, and the client splits them at each ;.
• A statement without a semicolon is simply “not finished yet” in the mysql client.
• Some programming languages send one statement per call and do not want the semicolon at all. Others accept several. Your language’s documentation will say.

1
2

SELECT 1 AS a;
SELECT 2 AS b;

1
2
3
4
5
6
7
8
9
10
11
12
13

+---+
| a |
+---+
| 1 |
+---+
1 row in set

+---+
| b |
+---+
| 2 |
+---+
1 row in set

Each statement produced its own result.

Statements spread over lines

SQL ignores extra spaces and line breaks. These two statements are identical to MySQL:

1
2
3
4
5
6

SELECT name,price FROM (SELECT 'Pen' AS name, 10 AS price) AS t WHERE price>5;

SELECT name,
       price
FROM   (SELECT 'Pen' AS name, 10 AS price) AS t
WHERE  price > 5;

1
2
3
4
5
6
7
8
9
10
11
12
13

+------+-------+
| name | price |
+------+-------+
| Pen  |    10 |
+------+-------+
1 row in set

+------+-------+
| name | price |
+------+-------+
| Pen  |    10 |
+------+-------+
1 row in set

Use the second style. One clause per line makes SQL much easier to read, review and debug.

Statements you will see in this course