6. Basic query operations

In database operations, a developer or user needs to retrieve information in a data table to obtain data records. In SQL statements, the SELECT statement is used to retrieve data records. The SELECT statement can retrieve data in a data table or view and display the queried data as a result set.
1. query records in all columns
In SQL statements, if you want to retrieve records of all columns in a data table, you need to query all columns in the data table. In SQL statements, a method is provided to query all columns in a data table or view. The syntax format is as follows:

 
Select *FromTable name or view name[, Table name or view]
 

The "*" sign after the SELECT statement is used to query all columns in the data table. The table name or view name after the from clause is used to specify the name of the data table or view to be queried. [] The specified table name or view is optional. That is to say, the from clause can be followed by multiple table or view names. Separate multiple tables or views with commas.

Note: The names of a maximum of 256 tables or views can be specified after the from clause.

2. query the specified columns in the table.

In actual development and application scenarios, developers or users do not want to see all the records in the data table, but are only interested in the data of one or more columns in the data table, in this case, you need to query the information of the specified columns in the data table. The syntax format for querying a specified column in a data table or view in a select statement is as follows:

SelectTarget Column[, Target column ,...]FromTable name or view name[, Table name or view]
 

The target column after the SELECT statement indicates the name of the specified column to be queried. . That is to say, the target column specified in the SELECT statement can be one or multiple columns. When multiple columns are specified, multiple column names must be separated by commas. The table name or view name after the from clause is used to specify the name of the data table or view to be queried. [] The specified table name or view is optional. That is to say, the ROM statement can be followed by multiple table or view names. Separate multiple tables or views with commas.

3. query non-Repeated Records in the table

When you use the SELECT statement to perform a query, all rows that meet the conditions in the data table are retrieved. If there are duplicate rows in the data table, they are also queried. In practice, you do not want to see duplicate record rows in the results. To prevent duplicate record rows from being displayed in the query results,In the SELECT statement, you need to add a distinct keyword to exclude duplicate Row Records in the query results.The format is as follows:

Select DistinctTarget row[, Target row ,...]FromTable name or view name[, Table name or view]
 

4. query by column alias

In the query result displayed in the preceding SELECT statement, the column name displayed in the query result is the name of the column defined in the data table specified in the SELECT statement. The names of the columns defined in the data table are generally in English. Sometimes, to better understand the information displayed in a column, you can use the column alias in the SELECT statement to change the name of the column displayed in the query results. The syntax format is as follows:

Select target column [as] column alias [, target column [as] column alias…]

From table name or view name [, table name or view]

The target column after the SELECT statement indicates the name of the specified column to be queried. The as keyword is followed by the column alias to be used. The as keyword is optional.

SelectStuidAsStudent ID, stunameAsStudent name, ageAsAge, SexAsGender, birthAsDate of birthFromT_student
 

In the query result, the column title is changed to an alias. Note:In the SELECT statement, you can use a space to replace the keyword as. The space is followed by the name of the alias in the preceding column.

If a column alias contains spaces or special characters, such as periods (.), commas, semicolons, and colons, the alias must be enclosed by single quotation marks or double quotation marks.

5. perform arithmetic operations on the queried records

You can also use Arithmetic Operators in select statements to perform arithmetic operations on specified columns. Arithmetic Operators include addition (+), subtraction (-), multiplication (×), and Division (Division ). The priority of the SELECT statement is higher than that of the addition or subtraction operator. You can use arithmetic operations to obtain the specific results of the required columns.

SelectTeaidAsInstructor ID, teanameAsInstructor name, salary*12 AsAnnual incomeFromT_teacher
 

Note: When performing arithmetic operations in a select statement, only the displayed query results are changed and the original values queried in the data table are not changed.

6. Use the connector (|) to connect Fields

When using the SELECT statement to query, you sometimes need to connect two or more fields to display a more meaningful result. To connect multiple fields in the SELECT statement, you can use the "|" connector in the Oracle database. The following is an example of using a "|" connector.

MySQL Databases and Microsoft SQL Server databases do not support "|" connector connection fields. To connect multiple fields, you can use the Concat function in MySQL; use the plus sign (+) operator directly in SQL Server.

7. About null values

In the database, if the column is not assigned a value and the column has no default value, the query result is null, that is, null. Null does not represent space or 0.