SQL server--Query (ii)

Last write a query in some simple query method, if said last is the primary query, then this is the advanced query.

Today is mainly the aggregation function, the group query, the connection query, the union query. In my opinion the first three is quite simple, a little difficult to understand the point of the last one, why? Because there are multiple query statements in a query, this looks complicated and difficult.

　　Aggregation functions

In peacetime, the aggregate function of the most use of a few, and others you are interested can also be understood.

　　　　Maximum value : Max ()

　　　　Minimum value : min ()

　　　　sum : Sum ()

　　　　averaging : avg ()

　　　　number of rows : Count ()

　　usage: Select aggregate function from table name such as: SELECT COUNT (*) from stuinfo----> Find the total number of people in the Stuinfo table, because a person is a row of data, the total number of rows is total, * number is the same as the previous There are columns, of course, the * number can also be changed to the required column name; The total number of boys seeking a class: SELECT COUNT (*) from stuinfo where sex= ' man '----> here just add a WHERE clause in the back.

Note: The parentheses following the aggregation function are the required column names, and the aggregate function cannot be used with the column name, such as select Max (score), Stuid (which is a column name) from Stuinfo, which is a wrong way to write.

　　Group queries

A GROUP BY clause is required for a grouped query.

such as: SELECT COUNT (*) as number, sex as gender from Stuinfo Group by sex-----> Check out the total number of men and women in the class. To find out how many men and women are, we can divide the data into two groups of men and women, and group by is written by what group column names (that is, according to what grouping we can write its column name).

One more chestnut:

To inquire about the number of subjects passed in the class first of all, we have to know that we need the data is a score greater than 60, that is, the score is less than 60 of the data we want to filter it out. The filtered data is passed and then grouped together to sum it up.

Select COUNT (*) as pass quantity, Stuid as study number from Stuinfo

Where socre>60-Filter data, NOTE: The WHERE clause can only be written in front of the GROUP BY clause and cannot be followed by an aggregate function

GROUP BY Stuid-groups the existing data according to the number of the numbers, asking for everyone, so we need to group by number

Finally, a difficult chestnut:

We said that the WHERE clause cannot be written after the GROUP BY clause, so what if we have to group before we sum it up? For example, if the number of people to pass the class is less than 3, we must first use the WHERE clause to filter out the score of less than 60, and then according to the number of each person group, after the group we also screened out the number of less than 3. Since the WHERE clause cannot be used, we can use the HAVING clause to solve this problem specifically.

The SQL statement is: SELECT COUNT (*) as number, Stuid as study number from Stuinfo

where Score>60--filter the failed data

GROUP BY Stuid--grouping by school number

Having Count (*) <3--filtering out the number of packets with less than 3, that is, the number of passes is less than 3

Having been used for a GROUP BY clause and can be followed by an aggregate function

　　　　　The WHERE clause is used for filtering before grouping, and cannot be followed by an aggregate function; The HAVING clause is used for filtering after grouping, which can be followed by an aggregate function.

　　Connection Query

We know that a table in a database can only describe one thing, so what if we need to query the contents of multiple tables in a query? Then we can use the connection query.

Connection queries are: internal, external, cross-connect

　　　　Internal Connection : (inner join) query two Tables public section

1. Equivalent connection "="

SELECT * FROM table name inner JOIN table name two on table name one. primary KEY = table name two. FOREIGN key

Raise a chestnut, as in the following two tables, and now connect them together

　　　　　　

This is what happens after the equivalent connection.

　　　　

Unequal connections

Concept: Use operators other than equals in join conditions (>, <, <>, >=, <=,!>, and!<)

Look at the effect.

<>

　　　　

>

　　

<

　　

Here are some chestnuts, and the rest is for you to try.

As I've just said, the inner join shows the public part of the two tables, and even if it's null, it will replace the display with NULL.

　　External connection

Left join: Returns all rows in the left table, and if rows in the left table do not have matching rows in the right table, the columns in the right table in the result return null values.

The syntax is just the same, change the inner to left.

or the two tables above:

　　　　

Right connection: Exactly as opposed to left JOIN, returns all rows in the right table, and returns a null value for the column in the left table in the result if the row in the right table has no matching rows in the left table.

Just change the inner to right and I won't go into details here.

Full connection: Returns all rows in the left and right tables. When a row does not have a matching row in another table, the columns in the other table return a null value

Just change the inner to full.

The last is the union query, the union query is multiple query statements with the use, written in a SQL statement.

For example: Query age is more than 21 years old, we only need to add a WHERE clause to filter on it.

　　　　

　　　The above article perhaps you do not understand, but you can add me QQ 1289747698 and I online discussion Oh! Welcome.

SQL server--Query (ii)