MySQL Database learning 02:select statement

Disclaimer: Most of the content in this article is from "MySQL must Know", only for learning reference, do not use it!

Chapter 4th Retrieving data

4.1 SELECT statement

the SELECT clause is used to retrieve table data from the database. It is almost the most commonly used statement in MySQL. We must master it well. To be able to retrieve table data, you must give at least two pieces of information-what you want to choose (table field, or column), and where to choose (which table).

4.2 Retrieving a single column

Input: Select Prod_name from Products;

Analysis:

The preceding statement uses the SELECT statement to retrieve a column named Prod_name from the Products table. Select what--prod_name column, and where to choose the--products table.

Form: SELECT column name from table name; (The column should be a column in the table)

Output:

Comments:

1 You may get a different order. It's normal. If there is no sort, the order is random. As long as the data is correct.

2 Each SQL statement should end with a semicolon (English). One for the specification, two if the command line must be so.

3 in any programming language, the symbols in the grammar are in English (half-width input method). Not to be stressed later.

4 description of the case. SQL statements in MySQL are case insensitive. However, in order to standardize, the keyword is generally capitalized, the other identifiers lowercase. such as SELECT prod_name from the products;

In order to save time in writing blog posts, I will use lowercase, please forgive me.

4.3 Retrieving multiple columns

To retrieve more than one column from a table, use the same SELECT statement. The only difference is that the SELECT keyword is followed by multiple column names, and the column names are separated by commas (in English).

Input: Select prod_id, Prod_name, prod_price from products;

Analysis: Nothing to analyze.

Output:

4.4 Retrieving all Columns

Format: SELECT * from table name; or select Column Name 1, column Name 2 ... from table name;

Input:

SELECT * from Products ;

Or

Select prod_id, vend_id, Prod_name, Prod_price, prod_desc from the products;

Analysis:

The first method uses the wildcard character * to represent all the columns. The second way is to list all the columns.

If it is not really necessary, do not use the * query for convenience. This will increase the time by retrieving the columns that are not needed.

Output:

Way One

Way Two

4.5 Different rows of the check

as you can see, the SELECT statement returns all matching rows by default. And what if we don't want each value to appear every time? Add the ID of the supplier you want to come up with in the Products table

Input: Select vend_id from Products;

Output:

we found that the same ID appeared multiple times. If we want to solve this problem, then we need to distinct the keyword. Meaning: Unique, different.

Format: SELECT DISTINCT column name from table name;

Input:

SELECT DISTINCT vend_id from products;

Analysis:

If you use the DISTINCT keyword, it must be placed directly in front of the column name.

If distinct is followed by more than one column name, it is not valid.

Output:

4.6 Limiting results

The SELECT statement returns all matching rows, and may sometimes be all rows in the specified table. to return to the first or previous rows, use the LIMIT keyword.

Format: SELECT single or multiple column names the number of result rows from the table name LIMIT displayed;

Input: Select Prod_name from the products limit 5;

Analysis: LIMIT 5 indicates that MySQL will return no more than 5 rows of results.

Output:

If you want to display the next 5 rows, you can specify the start row and the number of rows to retrieve. As shown below:

Format: SELECT single or multiple column names the index from table name LIMIT (the first row has an index of 0, the second row has an index of 1 ...), the number of result rows displayed,

Input: Mysql> Select Prod_name from Products limit 0, 5;

Analysis:

limit A, B. From Row 5, which is index 0, the 1th row of the result starts with 5 rows, limiting the number of bars to be no more than B.

Output:

Comments

1 Line 0: If it is the format of limit A, B. Then the first behavior of the retrieved row 0, that is, the index is 0, not 1. For example, limit 1, 1 retrieves the second row rather than the first row.

2 MySQL5 's limit syntax: Limit A, B's format is easily confusing, so MySQL5.0 supports another syntax for limit. LIMIT results show the number of bars OFFSET index (row n);

Offset is the meaning of the offset, that is, index, row N.

Input: Select Prod_name from the products limit 5 offset 3;

Analysis:

Limit 5 offst 3; Limits display result bar number is 5, index is 3, get from line 3 namely line fourth.

Output:

4.7 Using fully qualified names

The SQL example used so far is only referenced by column names. You may also use a fully qualified name to refer to the column (using both the table and column names). Take a look at the following example:

Format: Fully qualified column name--column name. Table name

Input: SELECT distinct products.vend_id from products;

Analysis: The results are consistent with the select DISTINCT vend_id from the products.

Output:

Table names can also be fully qualified.

Format: Database name. Table Name

Input: SELECT distinct vend_id from mysqlcrashcourse.products;

Output:

In some cases it is necessary to use a fully qualified name, which is described later in blog post. Now, we need to pay attention to this syntax so that we can learn and use it later.

4.8 Summary

This chapter learns how to use the SELECT statement to retrieve a single column, multicolumn data, and all columns of a table. The next article explains how to sort the retrieved data.

Chrysanthemum zi Yue: Professional Blog marketing Tools

MySQL Database learning 02:select statement