preface

Retrieving data is what we use most often in our daily use of the MySQL database.

The content of this article is a summary of this part of the study before.

content

Based retrieval

The SELECT syntax is used to retrieve a specified row from one or more tables.

The FROM clause is used to specify the table to be retrieved by SELECT.

Using the above two, we have the basic skeleton of the retrieval statement.

    # column_name: The name of the column contained in the result set
    # table_name: the name of the retrieved table

    SELECT [column_name] FROM [table_name];
Copy the code

The above example is a common statement skeleton for our data retrieval, which is a retrieval of a single column of a table.

In addition, the retrieval of multiple and all columns is supported.

  • Multiple columns: Separate each column name with a comma;

  • All columns: Use the wildcard asterisk (*) to refer to all columns in the delegate;

    Retrieve multiple columns of the table
    SELECT [column_name_1], [column_name_2] FROM [table_name];

    Retrieve all columns of the table
    SELECT * FROM [table_name];
Copy the code

Use the wildcard asterisk (*) instead of just retrieving the columns you need to improve the performance of your statement.

Use the alias

The AS keyword defines an alias for the columns of the retrieved result set that replaces the column names of the result set.

    # column_alias: column alias

    Define an alias for the column
    SELECT [column_name] AS [column_alias] FROM [table_name];
Copy the code

In addition to defining column aliases, you can also define table aliases.

    # table_alias: table alias

    Alter table alias
    SELECT [table_alias].[column_name] FROM [table_name] AS [table_alias];
Copy the code

The proper use of aliases makes written statements more intuitive and semantic.

qualified

MySQL, as a DBMS, may host multiple databases on a host. There may be tables with the same table name between multiple databases. In addition, there may be columns with the same column name between the tables of the same database. Column and table names are qualified, even to deal with this ambiguity.

The qualifiers are divided into table name qualifiers and column name qualifiers. The basic format for qualifying is to add the database name preceded by a period [database_name] to the table name. Or add the table name before the column name with a period [table_name].

    # define table name
    SELECT * FROM [database_name].[table_name];
    
    # specify the column name
    SELECT [table_name].[column_name] FROM [table_name];
    
    Set table name, set column name
    SELECT [table_name_1].[column_name_1], [table_name_2].[column_name_2]
    FROM [database_name_1].[table_name_1], [database_name_2].[table_name_2];
Copy the code

Qualification can be useful when doing joins between tables in different databases.

Computed field

In most cases, we’re going to touch on static field retrieval, which is just retrieving it from a table.

However, in some cases, we may need to evaluate some columns of the table to get a new column and return it as a column in the result set. In this case, we use computed fields.

There are several ways to create computed fields:

  • Evaluate columns using functions;

  • Perform arithmetic on the column;

    Use the string splicing function LOCATE as an example
    SELECT CONCAT([column_name_1, column_name_2, colum_name_3]) AS [computed_column_alias]
    FROM [table_name];
    
    # arithmetic operation
    SELECT [column_name_1] + [column_name_2] AS [computed_column_alias] FROM [table_name];
Copy the code

The default field names for the created computed fields are expression strings (in the above example, ‘CONCAT([column_name_1, column_name_2, colum_name_3])’ and ‘[column_name_1] + [column_name_2]’), Use the AS keyword to define aliases for the created computed fields to make the column names of the returned result set clearer.

MySQL supports the use of common arithmetic operators like +-*/%.

duplicate removal

The DISTINCT keyword is used to de-duplicate rows, resulting in different rows for each. For example, compute a list of product table province columns that need to be stripped of duplicates (possibly multiple products in one province).

    SELECT DISTINCT [column_name] FROM [table_name];
Copy the code

In fact, ALL is the default keyword of this function bit. If a DISTINCT keyword is not used, ALL is added by default.

    # ALL is the default
    SELECT ALL [column_name] FROM [table_name];
    
    # is equal to
    SELECT [column_name] FROM [table_name];
Copy the code

Data reference

  • MySQL Must Know must Know

  • MySQL Documentation