SQL basics-write basic SQL select statements

Dear all,

The latest time is relatively idle, consolidating the basic knowledge of SQL. The following is a summary to facilitate future search:

I,SQL statements include:

1. SELECT query statement

2. DDL data definition statements (create, drop, alter, truncate)

3. DML data operation statements (insert, update, delete)

4. DCL data control statements (commit, rollback, savepoint)

II,SQL select performance

1. The SELECT statement returns information from the database. You can use a SELECT statement to do the following:

2. Column Selection: You can use the SELECT statement Column Selection Function to select columns in the table. These columns are returned by the query. When you query, you can select the columns specified in the table you query.

3. Row Selection: You can use the row selection function of the SELECT statement to select rows in the table. These rows are returned by the query. You can use different standard limits for the rows you see.

4. Connection: You can use the SELECT statement connection function to collect data. The data is stored in different tables and can be connected between them.

III,Basic select statements

Select * | {[distinct] column | expression [alias],...}

From table;

1. Select clause: defines which columns are displayed.

--- The select clause can use column names, expressions, group functions, direct quantities, and subqueries.

2. From clause: defines which specified tables are listed in the select clause.

--- The from clause can use table names, views, and subqueries.

3. In Syntax:

Select is a list of one or more fields.

* Select all columns.

Duplicate is not allowed in distinct.

Column | expression: select the specified field or expression.

Alias gives different titles to the selected columns.

From table specifies the table containing columns.

Select * From charlestest

Select the specified column: Select charles_number, charles_name from charlestest

 

Arithmetic expression: The expression can be used in select clause, where clause, group by clause, having clause, and order by clause.

Arithmetic Operator priority: Multiplication and division are given a higher priority than addition and subtraction. operators with the same priority are calculated from left to right. Parentheses are used to force Priority Calculation and clear statements.

Define a null value (null)

1. null is an unallocated, unknown, or not applicable.

2. null is neither 0 nor a space.

3. If a value in a row lacks a data value, the value is set to null, or contains an empty value.

4. null is an unallocated, unknown, or unsuitable value. Null and 0 or space are different. 0 is a number, while space is a character.

5. Columns of any data type can be empty. However, some constraints, such as not null and primary key, prevent the use of null in columns.

Null Value of the arithmetic expression:

If the value of any column in the arithmetic expression is null, the result is also null.

If a number is divided by null or null by a number, the result is null or unknown.

Define the column alias:

Column alias: Change the Column Title name. It can be used in calculation results.

Followed by the column name -- option as keyword can be available between the column name and alias

If the alias contains spaces, special characters, or case-sensitive characters, single quotation marks or brackets are required.

Column aliases can only be used in select and order by clauses. Where, group by, and having clauses are not allowed.

Duplicate row: When querying records of a table, all records of the table, including duplicate record rows, are returned by default. (Distinct)

 

Best regards,

Charles Chen