MySQL must know---retrieve data

SELECT statement

Retrieving a single column

Retrieving multiple columns

Retrieving all Columns

Retrieving a different row

Limit results

Using a fully qualified table name

1.select statements
Using Select to retrieve table data, you must give at least two messages-----what to choose and where to choose from.

2. Retrieving a single column
MariaDB [test]> select age from User;
+------+
| Age |
+------+
| 22 |
| 25 |
| 56 |
| 14 |
| 36 |
| 68 |
| 45 |
| 17 |
| 33 |
| 27 |
| 29 |
| 70 |
| 24 |
+------+

Note: The query results are not sorted by default, and the end SQL statement must be separated by a semicolon (;), and the SQL statement is case insensitive.

Recommendation: To facilitate reading as well as debugging suggestions to put SQL statement branches.

3. Retrieving multiple columns
MariaDB [test]> SELECT *
from user;
+----+------+------+----------+
| ID | sex | Age | Province |
+----+------+------+----------+
| 1 | 1 | 22 | Beijing |
| 2 | 0 | 25 | Guangdong |
| 3 | 0 | 56 | Tianjin |
| 4 | 1 | 14 | Beijing |
| 5 | 0 | 36 | Guangdong |
| 6 | 1 | 68 | Hunan |
| 7 | 1 | 45 | Beijing |
| 8 | 1 | 17 | Hebei |
| 9 | 2 | 33 | Tianjin |
| 10 | 1 | 27 | Hunan |
| 11 | 1 | 29 | Beijing |
| 12 | 2 | 70 | Guangdong |
| 13 | 0 | 24 | Beijing |
+----+------+------+----------+
Rows in Set (0.00 sec)

4. Retrieving all Columns
   Hint: the wildcard character (*) denotes all columns

MariaDB [test]> SELECT *
-From user
;
+----+------+------+----------+
| ID | sex | Age | Province |
+----+------+------+----------+
| 1 | 1 | 22 | Beijing |
| 2 | 0 | 25 | Guangdong |
| 3 | 0 | 56 | Tianjin |
| 4 | 1 | 14 | Beijing |
| 5 | 0 | 36 | Guangdong |
| 6 | 1 | 68 | Hunan |
| 7 | 1 | 45 | Beijing |
| 8 | 1 | 17 | Hebei |
| 9 | 2 | 33 | Tianjin |
| 10 | 1 | 27 | Hunan |
| 11 | 1 | 29 | Beijing |
| 12 | 2 | 70 | Guangdong |
| 13 | 0 | 24 | Beijing |
+----+------+------+----------+
Rows in Set (0.00 sec)

5. Retrieving different rows

Note Keywords (DISTINCT)

MariaDB [test]> SELECT distinct age from user;
+------+
| Age |
+------+
| 22 |
| 25 |
| 56 |
| 14 |
| 36 |
| 68 |
| 45 |
| 17 |
| 33 |
| 27 |
| 29 |
| 70 |
| 24 |
+------+
Rows in Set (0.00 sec)

Tip: With the DISTINCT keyword, it must be placed directly before the column name.

6. Limiting results
Keyword (limit)

MariaDB [test]> Select age from User
, limit 5;
+------+
| Age |
+------+
| 22 |
| 25 |
| 56 |
| 14 |
| 36 |
+------+
5 rows in Set (0.00 sec)

Note the following statement:

MariaDB [test]> Select Age
-From user
, limit 5, 5;
+------+
| Age |
+------+
| 68 |
| 45 |
| 17 |
| 33 |
| 27 |
+------+
5 rows in Set (0.00 sec)

Tip: The first behavior of the retrieved result is line 0, not line 1, and limit 5,5 is the 5 line that starts at line 5th!!!

7. Using a fully qualified table name
MariaDB [test]> Select User.age
-From user
, limit 3, 4;
+------+
| Age |
+------+
| 14 |
| 36 |
| 68 |
| 45 |
+------+
4 rows in Set (0.00 sec)

Tip: Using a fully qualified table name makes it more intuitive to see whether the table is operating or the database.

MySQL must know---retrieve data