SQL Basics: SELECT statements, various join,union usages

First, the basic SELECT statement

1. "*" NOTE: In the SELECT statement, use * To select all the columns, this is a habit should be resisted.

While it saves time to enter column names, it also means more data is available than is really needed. Correspondingly, the performance and network performance of the application can be reduced.

A good rule is to select only the desired.

2. Join clause

join is used to define how data is picked from multiple tables and combined into a result set.

join must be because (1) all the information we want to get is not all 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.

the common denominator of join is that a record of the same or multiple other records is matched by a recorded connection column, resulting in a super record of those records.

2.1 INNER JOIN

The INNER join returns only the matching records on the joined Fields . INNER join is done in the exclusion.

Self-reference is a way to join a table back to itself for some reason.

INNER join is the default join method.

2.2 OUTER JOIN

The OUTER join has a left-right join, and the inner join has no left or right join points.

The right (left) OUTER join is a row that wants to include all of the rows in the table that are on the side, as well as matching records in the table on the side.

2.3 Full JOIN

The full join join, which is to include all rows in the table on either side of the join .

2.4 Cross JOIN

The cross join does not have an on join character and joins each record in the join side table with all 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 commonly used and less familiar operators:

Between: < columns > between NUM1 and Num2

Like:like "any%"% represents 0 or more arbitrary characters. _ Represents a single arbitrary character. [] represents any single character that is contained in parentheses. ^ Exclude the next.

Exists:exists the query statement.

4. ORDER by

you know what? The return result of a query is usually given in alphabetical or numerical order, which is accidental .

How it is given, in the absence of a specified case, usually depends on how SQL Server considers the aggregated data to be the least expensive . Therefore, the returned results are usually based on the physical order of the data in the table or an index used by SQL Server to find the data.

The default is ascending ASC, and Descending is desc.

If the order is required, it is recommended to explicitly indicate 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. Aggregating data using the GROUP BY clause

Once each column in the group By,select list is used in a query statement, it is either included in the group by list or included in the aggregation.

When aggregation is not used with group by, the aggregation can only be in the select list with other aggregates, not with the column name in the select list.

In addition to COUNT (*) , any aggregation function ignores null values.

6. HAVING clause

The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with the aggregate function.

The HAVING clause is used only when there is a GROUP BY clause in the query statement.

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

7. DISTINCT clause

distinct eliminate duplicate data . If the value is the same, the value appears once.

distinct appears at the beginning of the list, or appears in count.

Second, the basic INSERT statement

1. Basic structure

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

into can be omitted .

Table_column_list recommendations are explicitly given, one is to enhance readability, and second, even if the structure of the table changes, will not affect.

2. INSERT into ... SELECT statement

BULK INSERT data.

INSERT [into] < table name > [< list name;] <select Statement >

Third, UPDATE statement changes 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 statements

DELETE

[From] < table name >

List of [from] < tables/join joins >

WHERE < search conditions >

A small example of delete:

Films Table Actors table

Filmid |   Filmname |   Yearmade Filmid |   FirstName | LastName

1 ' host ' 1984 1 ' li ' si '

2 ' Shit ' 1999 2 ' Wang ' Wu '

3 ' Liu ' li '

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

DELETE from actors

From actors a

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 dualfrom.

V. UNION

Union allows two or more queries to produce a single result set.

Join level merges data, while union vertically merges the data.

Several points of union:

1. The number of columns in the select list to be union is the same.

2. The header returned by the merged result set takes only the first query.

3. The data type must be the same or implicitly compatible.

4. Returns the default is distinct instead of all.

"END"

SQL Basics: SELECT statements, various join,union usages