MySQL must know will-retrieve data
Select statement Retrieval single column Retrieval multiple column Retrieval all columns Retrieval different Row restrictions result in fully qualified Table name
1.select statement
Using select to retrieve table data, you must give at least two pieces of information-what you want to choose and where to choose.
two。 Retrieve a single column
MariaDB [test] > select age from user
+-+
| | age |
+-+
| | 22 |
| | 25 |
| | 56 |
| | 14 |
| | 36 |
| | 68 |
| | 45 |
| | 17 |
| | 33 |
| | 27 |
| | 29 |
| | 70 |
| | 24 |
+-+
Note: query results are not sorted by default, ending SQL statements must be separated by semicolons (;), and SQL statements are not case-sensitive.
Suggestion: it is recommended to branch the sql statement in order to facilitate reading and debugging.
3. Retrieve 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 |
+-- +
13 rows in set (0.00 sec)
Retrieve all columns
Tip: the wildcard character (*) indicates 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 |
+-- +
13 rows in set (0.00 sec)
5. Retrieve different rows
Pay attention to keywords (DISTINCT)
MariaDB [test] > select distinct age from user
+-+
| | age |
+-+
| | 22 |
| | 25 |
| | 56 |
| | 14 |
| | 36 |
| | 68 |
| | 45 |
| | 17 |
| | 33 |
| | 27 |
| | 29 |
| | 70 |
| | 24 |
+-+
13 rows in set (0.00 sec)
Tip: to use the distinct keyword, it must be placed directly before the column name.
6. Limit the result
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
+-+
| | age |
+-+
| | 68 |
| | 45 |
| | 17 |
| | 33 |
| | 27 |
+-+
5 rows in set (0.00 sec)
Tip: the first behavior of the retrieval result is line 0 instead of line 1, while limit 5, 5, is the 5 lines starting from line 5!
7. Use a fully qualified table name
MariaDB [test] > select user.age
-> from user
-> limit 3pr 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 which table or database you are operating on.
