Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.