Database Statement (ii)--Query

Data query We are single-table queries, connection queries, nested queries, collection queries, and understanding the general form of SELECT statements.

Well, first of all, the single-table query , which is divided into five content, in order to select a number of columns in the table, select a number of tuples in the table (re-and non-re-issue), ORDER by clause, aggregation function, GROUP by clause, the previous two is relatively simple, so a simple introduction, Select a number of columns in the table, you have to pay more attention to the column may have to calculate the column and change the content of the name, and select a number of tuples in the table, you have to understand the problem of re-and-no, and clearly know the common query conditions and query conditions of special, character matching applications, multi-criteria query (and)

The first two are so simple, OK, now let's talk about the ORDER by clause ,

V ORDER by clause

n can be sorted by one or more attribute columns

N Ascending:ASC; descending:DESC; default value Ascending

V when the row sequence contains null values

n ASC: Sort column with null value last display

n DESC: A tuple with the sort column null is displayed first

Example:

[Example 24] Query the number of students taking the 3rd course and their achievements, the results of the query sorted in descending order of fractions.

   SELECT Sno,grade        from  SC        WHERE  cno= ' 3 '        ORDER by Grade DESC;

[Example 25] query all students, the results of the query according to the Department of the line number in ascending order, the students in the same department in descending order by age.

SELECT  *        from  Student        

Then go into our focus function (which is handled in record units):

Example:

[Example 26] Check the total number of students.

SELECT COUNT (*)

From Student;

[Example 27] The number of students enrolled in the course was queried.

SELECT COUNT (DISTINCT Sno)

From SC;

[Example 28] Calculation 1 average grade of the students in the course .

SELECT AVG (Grade)

From SC

WHERE cno= ' 1 ';

Finally enter our GROUP BY clause

The V GROUP BY clauses are grouped:

function object of refinement aggregation function

• The query results are not grouped, and the aggregate function acts on the entire query result
• Once the query results are grouped, the aggregation functions are used for each group individually
• The action object is the intermediate result table of the query
• GROUP by a specified column or columns of values, with values equal to a set of

Example:

[practice] Ask each classmate and the corresponding number of elective courses.

SELECT Sno,count (Cno)

From SC

GROUP by Sno;

Note the point:

V Having a phrase differs from the WHERE clause:

• Different objects of action
• WHERE clause acts on the base table or view, from which you select a tuple that satisfies the criteria, having a phrase acts on a group from which you select a group that satisfies the criteria

Example:

[Example 32] The enquiry number of students enrolled in more than 3 courses.

SELECT Sno

From SC

GROUP by Sno

Having COUNT (*) >3;

The above is all the contents of a single-table query, and now we are going to talk about multi-table query, first of all, say that the first content of multi-table query, is the connection query, the meaning of the connection query is related to the query of multiple tables, and to want our table to join together, must have the connection parameters, So we have to use the condition of the connection parameter, and often we need the general format of the condition when we join the table:

Because we pay particular attention to the fact that the connection fields in the join condition must be comparable, but the names do not have to be the same, and in the case of our condition operation (specifically the condition of the table, other conditions are based on the operation of this condition), there are three methods,

The first is nested through the bad method, which is to find a tuple from the table first, and then scan table two from the beginning, one by one to find the tuples that meet the conditions of the Union, found after the first tuple in table 1 and the tuple together, forming a tuple in the result table, table II all finished, Look for the second tuple in table 1, and then follow similar steps until you have finished processing all the tuples in table 1. But this method has a big drawback is that regardless, he will be all the tuple of table 2 will be swept after the next tuple of table 1 query, so that the efficiency is very slow, so in order to improve efficiency, then proposed a second method and a third method

The second is the sort merge method , which is commonly used in the case of the = connection, the step is to sort table 1 and table 2 by Connection property, the first tuple of table 1, scan table 2 from the beginning, and sequentially find the tuple that satisfies the join condition, and then stitch the first tuple of table 1 with the tuple. form a tuple in the result table. When you encounter the first tuple in table 2 that is greater than the value of table 1 connection fields, the query to table 2 no longer continues, and then look for the second tuple in table 1, repeating the above steps.

The third type is the index connection, the step is to index Table 2 by the Join field, and then to each tuple in table 1, and then query the index of table 2 according to its connection field value, find the tuple that satisfies the condition, find the first tuple with table 1 can be joined with the tuple together, form a tuple in the table, repeat the above steps.

Based on the above basis, we have four kinds of connection query: equivalent to non-equivalent connection query, self-connection, external connection, compound condition connection, equivalence and non-equivalence connection query is very simple, this side does not say, and the self-connection is a table with its own connection, we need to table alias to distinguish, Since all property names are attributes of the same name, the alias prefix must be used, and the outer connection is different from the normal connection, and the normal join operation outputs only tuples that satisfy the join condition , and the outer JOIN operation outputs (null values) a tuple in the topic table that does not satisfy the join condition in the specified table as the connection topic. , and the compound conditional connection is a kind of connection that makes the judgment of multiple conditions based on the equivalent of a non-equivalent connection, which simply means that the condition of the connection is multiple .

nested queries

First of all we say what is called nested query, and understand nested query, but also first understand the concept of query block: In a select-from-where statement is called a query block . Queries that nest a query block in the conditions of another query block's WHERE clause or having phrase are called nested queries

Note: 1 Sub-query is limited, what is the limit?

• cannot use order by clauses

2) Some nested queries can be replaced with join operations

the meaning of nested queries is that layered nesting reflects the SQL the structure of language

Well, to say the relevant concepts of nested queries, first of all, we first talk about nested query four ways of a common basis, is to divide the query is clear or not related query.

Unrelated queries are sub-query conditions that do not depend on the parent query.

The principle of its work is: from the Inside Out layer by row. That is, each subquery is solved before the first-level query processing, and the result of the subquery is used to establish the lookup criteria for its parent query.

The query for the subquery is dependent on the parent query.

It works by:

• The first tuple of the table in the outer query is first taken, and the inner query is processed according to its property values related to the inner query, if the where clause returns a value of true, the tuple is placed in the result table
• And then take the next tuple of the outer table.
• Repeat this process until the outer table is all checked out

After saying the basis of the four nested queries, we will begin to learn the four nested queries, the four, that is

A subquery with an in predicate

Second, subqueries with comparison operators

Iii. subqueries with any (SOME) or all predicates

Iv. Subqueries with EXISTS predicates

First, let's talk--subqueries with in predicates

Example:

[Example 39] query with "Liu Chen" students in the same department of Learning.

SELECT sno,sname,sdept

From Student

WHERE sdept in

(SELECT sdept

From Student

WHERE sname= ' Liu Chen ');

This query is a nested query that is based on unrelated subqueries.

Summary: in means that if its left column equals any number on the right, it can return a record

And then talk about subqueries with comparison operators.

When we know the exact return value of a subquery, we can compare it with a comparison, which is a subquery with a comparison operator, which is used in conjunction with any or all predicates. and pay particular attention to the fact that subqueries must be followed by a comparer.

And then we're going to talk about the subquery with the Any (SOME) or all predicate that is used with the comparison character

Its predicate meaning is any: one value, all: all values. Its specific syntax is as follows:

Example:

Finally, we talk about the subquery with EXISTS predicate , which is the difficulty of the four nested queries, first of all, we talk about the role of exist and not exist, the role of exist is if there are records in the subquery to meet the conditions, Then this record will be returned, and not exist will be returned if one of the subquery's records does not meet the criteria.

See examples directly:

It's all about regular exist query statements, and we have some special methods for using exist, like the following,

1. Using Exists/not exists to realize Universal quantifier (difficulty)

Because we do not have a full quantifier "(for All) in our SQL statement, we can convert a predicate with a universal quantifier into an equivalent predicate with a quantifier, (" x ") P≡? ($ x (? P))

Example:

Choose a person who does not have a course and no choice

1. Use Exists/not exists to implement logical Yun (difficulty), because there is no logic operation in SQL language, so we can use predicate calculus to convert the logical implication predicate equivalence to

P? Q≡? P∨q

Example:

After the nested query, let's say that the last multi-table query module, is the collection query , which is a multi-conditional query of another expression, the types of collection queries are:

• and operate the Union
• Hand-operated Intersect
• Differential operation except

Because it is another representation of a multi-conditional query, the number of columns for each query result that participates in the collection operation must be the same, and the data type of the corresponding item must be the same

Let's look at the example below:

Finally, we go to the summary link, first summarize the general format of the SELECT statement:

Finally, a summary of the multi-table query:

1) First understand the factors involved in the data we are looking for, so that we need to use those tables in order to relate these factors.

2) After thinking clearly, we will start to think about which query method we will use to check (according to the characteristics of each query method to judge) to get the data we want

Note: We all need to understand that in the case of nested queries, we need to understand that we are not related to queries or queries when nesting, and that we make clear the following four types of nested query statements.

Database Statement (ii)--Query