Select statement, join, union usage

I. Basic SELECT statements

1. Note for "*": in the SELECT statement, use * to SELECT all columns, which should be resisted.

Although saving the time for entering the column name, it also means that the data obtained is much more than the data actually needed. Correspondingly, it will also reduce the application performance and network performance.

A good rule is only required.

2. join clause

Jion is used to define how to select data from multiple tables and combine them into a result set.

Jion must be because (1) all the information we want to obtain is not in one table, or (2) the information to be returned is in one table, however, the condition information set on it is in another table.

The common feature of jion is to match one record or multiple other records through the join column of the record to generate a super record that is the record.

2.1 INNER JOIN

Inner join only returns matching records for joined fields. Inner join is used for exclusion.

Auto-reference is used to join a table to itself for some reason.

Inner join is the default JOIN method.

2.2 OUTER JOIN

An outer join can be left or right JOIN, but an inner join can be left or right JOIN.

RIGHT (LEFT) outer join is to include all rows in the RIGHT table and rows with matching records in the LEFT table.

2.3 FULL JOIN

Full join is to include all rows in the table on both sides of the JOIN.

2.4 CROSS JOIN

Cross join does not have an ON join operator, and joins each record in the table ON one side of the JOIN operation with all the records in the table ON the other side. That is, the Cartesian product in the join table.

Cross join can be used to provide sample data and scientific data.

3. WHERE clause

Some common and unfamiliar operators:

BETWEEN: <column> BETWEEN num1 AND num2

LIKE: LIKE "ANY %" % represents 0 or multiple arbitrary characters. _ Represents a single arbitrary character. [] Represents any single character in parentheses. ^ Exclude the next one.

EXISTS: EXISTS query statement.

4. ORDER

Do you know? The returned results of a query are usually given in alphabetical or numerical order, which is accidental.

Given in which way, unless specified, it usually depends on which data collection method SQL Server considers as the minimum overhead. Therefore, the returned results are generally based on the physical order of the data in the table or an index used by SQLServer to find the data.

The default descending order is DESC, and the ascending order is ASC.

The order by clause can be sorted based on any field in any table used in the query, regardless of whether the column is included in the SELECT list.

5. Use the group by clause to aggregate data

Once the group by statement is used in the query statement, each column in the SELECT list is either included in the group by list or included in the aggregation.

When clustering is not used together with group by, clustering can only be in the SELECT list together with other aggregates, but cannot be used together with column names in the SELECT list.

Except COUNT (*), any aggregate function ignores NULL values.

6. HAVING clause

The HAVING clause is used only when the query statement contains a group by clause.

The WHERE clause is applied to each row of the group, and the HAVING clause is applied to the group aggregation.

7. DISTINCT clause

DISTINCT eliminates duplicate data. If the value is the same, this value appears once.

DISTINCT appears at the beginning of the list or in COUNT.

II. Basic INSERT statements

1. Basic structure

INSERT [INTO] table_name [table_column_list] VALUES (data_value_list)

INTO can be omitted.

Table_column_list is recommended to be presented explicitly. One is to enhance readability, and the other is to change the table structure in the future.

2. insert into... SELECT statement

Insert data in batches.

INSERT [INTO] <table name> [<list name>] <SELECT statement>

3. UPDATE statement to change existing data

UPDATE <table name>

SET <column >=< value> [, <column >=< value>]

[FROM <one or more tables>]

[WHERE <constraints>]

UPDATE can generate data from a table, but only affects one table.

IV. DELETE statement

DELETE

[FROM] <table name>

[FROM] <table list/JOIN>

WHERE <search criteria>

A small example of DELETE:

Films table actors table

FilmId | filmname | yearmade filmId | firstname | lastname

1 'host' 1984 1 'Lil' 'Si'

2 'shit' 1999 2 'Wang' 'wu'

3 'liu' 'Lil'

Delete rows that do not match in the films table from the actors table:

Delete from actors

FROM actors

Left join films f on a. filmId = f. filmId

WHERE f. filmname is null;

MySQL syntax: delete a. * from actors a left join films f on a. yearmade = f. yearmade where f. yearmade is null

MySQL does not support double FROM.

V. UNION

UNION allows two or more queries to generate a single result set.

JOIN horizontal merge data, while UNION vertical merge data.

Key points of UNION:

1. The number of columns in the SELECT list for UNION operation is the same.

2. Only the first query is returned for the merged result set.

3. Data types must be the same or implicitly compatible.

4. The default return value is DISTINCT rather than ALL.