SQL basics: Select statements, various join and Union operations

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

Join is usedDefines how to select data from multiple tables and combine them into a result set.

Join requiredBecause (1) all the information we want to obtain is not in one table, or (2) the information to be returned is in one table, but the condition information set on it is in another table.

Commonalities of joinIs to match one or more other records through the join column of the record to generate a super record that is these records.

2.1 inner join

Inner joinOnly records matching the joined fields are returned.. 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 isTo include all rows in the right table and rows with matching records in the left table.

2.3 Full join

Full join is requiredContains 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, joinCartesian Product.

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. This isAccidental.

The way in which the data is collected is usually determined by SQL Server.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 value is ASC in ascending order.In descending order, DESC is used.

If you have requirements on the sequence, it is recommended to explicitly mark it in the SQL statement.

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.

BesidesCount (*)In addition, any aggregate function ignores NULL values.

 

6. Having clause

The having clause is added to SQL because the where keyword cannot be used with the aggregate function.

Only whenThe having clause is used when a 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 inOr appear 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 canLet two or more queries 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.

End