SQL Server Series form query

Series related articles

This article series belongs to: ASP. Mvc+bootstrap+jquery+sqlserver, other articles please refer to the following:

• Google Chrome browser (theory article)
• Detailed Google Chrome (action) (top)
• Detailed Google Chrome (action) (bottom)
• Build the. NET Development Environment
• Detailed ASP. NET MVC route
• Detailed ASP. NET MVC Controller
• Detailed jquery Selector
• ASP. NET program release detailed process

The next article series will bounce in the above range.

1 overview

Here are a few questions, if you can solve, please continue to look down, if not resolved, please review the basic knowledge of SQL, then read this article. This article is about medium depth.

Q1: Table Studentscores as follows, using an SQL statement to find out the names of students who are larger than each course

Q2: Table Demo_delte as follows, delete the same student redundancy information except for the automatic numbering

The Q3:team table is as follows, a four-team, a four-ball match, and a SQL statement showing all possible combinations of matches .

Q4: Consider the following SQL statements in the logical processing order in the Microsoft SQL Server engine

1  UseTSQLFundamentals20082 3 SELECTEmpid Year(OrderDate) asOrderYear,COUNT(*) Numorders4  fromsales.orders5 WHERECustID= in6 GROUP  byEmpid Year(OrderDate)7  having  COUNT(*)>18 ORDER  byEmpid,orderyear

This article will analyze the general query process, involved in the processing of logical clauses, mainly including from,where,group by,having,select,order by,top,over clauses.

2 Elements of a SELECT statement

2.1 General query clauses and logical processing order

When retrieving queries against a data table, the query statement generally includes clauses such as From,where,group By,having,select,order By,top,over, and consider the logical processing order of the following example.

1  UseTSQLFundamentals20082 3 SELECTEmpid Year(OrderDate) asOrderYear,COUNT(*) Numorders4  fromsales.orders5 WHERECustID= in6 GROUP  byEmpid Year(OrderDate)7  having  COUNT(*)>18 ORDER  byEmpid,orderyear

As in the code, the logical processing order in SQL is as follows:

 UseTSQLFundamentals2008 fromsales.ordersWHERECustID= inGROUP  byEmpid Year(OrderDate) having  COUNT(*)>1SELECTEmpid Year(OrderDate) asOrderYear,COUNT(*) NumordersORDER  byEmpid,orderyear

The logical processing order can be summed up as follows:

Comments:

A. In general programming languages, such as c++,java,c#, the program executes step-by-click in the order of "top down", whereas in SQL, the location of the SELECT statement is not executed first, although at the very beginning;

B. The result set returned in each step of the logical processing sequence is the result set to be executed immediately following the next statement of the step statement;

C.from Gets the data source (or data table), where on the basis of the From, the group by groups the collection at least one column based on the where, having to filter the grouped set based on group by, The SELECT statement is retrieved on a having basis, and order by is ordered on a select basis according to certain conditions;

2.2 Part of the query clause explanation

2.1.1 from clause

A. Using the database schema in the qualified code of the object name, even without the database schema qualification, SQL Server will also be implicitly parse it, the higher the cost, the first time, if the object name is the same, there is no schema-qualified, will produce ambiguity;
B.from * Performance is lower than from conum_name performance;
The order of result sets obtained by C.from query is random;
2.1.2 WHERE clause

A. Filter the rows returned from the from stage;
B.where predicate or logical expression;
The C.WHERE clause has a significant impact on query performance, and on the basis of filtering expressions, SQL Server calculates what index to use to access the requested data;
D. Scan the entire table, return all possible rows, filter on the client, the overhead is relatively large, such as generating a large amount of network traffic;
E.t-sql uses the three-valued predicate logic (True,false,unknown);

2.1.3 GROUP By clause

The A.group by stage combines the rows returned by the previous stage of the logical query processing by "groups", each of which is determined by the elements specified in the GROUP BY clause;
B. If grouping is involved in a query statement, then the group by phase is followed by all stages (including having, select, and order by) that are groups, not individual rows. Each group is ultimately represented as a row in the query result set;
All expressions specified in the clauses processed after the C.group by phase must ensure that only one scalar (single value) is returned for each group. An expression based on the elements in the group by list satisfies this requirement because, by definition, the group by element only appears once in each of the groups;
D. The aggregation function returns only one value for each group, so an element can only be entered as an aggregate function (COUNT, SUM, AVG, Min, and max) if it no longer appears in the group by list. (Note: If you have a GROUP BY clause, the aggregate function only operates on each group, not all groups);
E. All aggregate functions will ignore NULL, except for COUNT (*);
F. In an aggregation function, distinct can be used to handle distinct numbers, such as count (distinct vary);

2.1.4 having clause

The a.having clause is used to specify a predicate or logical expression that filters the group, which corresponds to the where phase to separate rows;
B. Because the HAVING clause is processed after grouping rows, you can reference an aggregate function in a logical expression, such as having COUNT (*) >1, meaning that the having stage filter retains only the group that contains multiple rows;

2.1.5 SELECT clause

The A.select clause is used to specify the attributes (columns) that need to be included in the result set returned by the query;
The B.select clause returns the name type of the column:

• Directly based on the individual columns of the table being queried
• Three ways to define aliases, we recommend using as. < expressions >AS< aliases >;< aliases >=< expressions > (alias equals expression);< expression > < alias > (expression space alias)
• Column with no Name

C. In the relational model, all operations and relationships are based on the results in relational algebra and Relationships (collections), but in SQL, the situation is slightly different because the select query does not guarantee that a true collection (that is, an unordered collection of unique rows) is returned. First, SQL does not require the table to conform to the set criteria. SQL tables can have no keys and rows are not necessarily unique, in which case the table is not a collection, but a multiset (multiset) or package (bag). But even if the table being queried has a primary key and meets the conditions of the collection, a select query against the table may still return a result that contains duplicates. The result set is often used when describing the output of a select query, but the result set does not necessarily have to satisfy the set conditions in mathematical sense;
D.DISTINCT constraints, to ensure the uniqueness of the row, delete duplicate rows;
E. Try not to query all the columns in the form of select *, as far as possible;

2.1.6 ORDER by

A. The most important thing to understand about SQL is to understand that the table is not guaranteed to be ordered, because the table is meant to represent a collection (if there are duplicates, it is multiset), and the collection is unordered. This means that if you do not specify an ORDER BY clause when querying a table, the query can return a result table, but SQL Server is free to sort the rows of the result sheet in any order;
B. Use ASC for ascending in ORDRTB by, Desc for descending, and ascending by default;
C. A query with an ORDER BY clause produces a result of an ANSI called a cursor (a non-relational result in which the rows have a fixed order). Some language elements and operations in SQL are expected to process only the table results of the query, not the cursors, such as table expressions and set operations;

3 Answers to questions

Q1:key

1 --Method 12 Select distinctStudentname3  fromStudentscores4 whereStudentname not inch 5 (6 Select distinctStudentname7  fromStudentscores8 whereCoursegrades<= the9 )Ten  One --Method 2 A SelectStudentname -  fromStudentscores - Group  byStudentname the  having min(Coursegrades)> the

Q2:key

1 DELETE Demo_delte 2 WHERE  3not in(4SELECT min  5  from  6GROUP by Xuehao,xm,kcbh,kcmc,fs)

Q3:key

1 SELECT team1. Teamname,team2. Teamname2 from Team team1,team team23WHERE team1. Teamname<team2. Teamname

Q4:key

Refer to Chapter 2nd analysis.

4 references

"01" Microsoft SQL Server 2008 Insider: T-SQL language Basics

"02"Microsoft SQL Server 2008 Tech Insider: T-SQL query

"03" Programmer's SQL Classic

5 Copyrights

• Thank you for your reading, if there are shortcomings, welcome advice, common learning and common progress.
• Bo main website: http://www.cnblogs.com/wangjiming/.
• A very small number of articles using reading, reference, reference, copying, copying and pasting into a variety of ways, most of the original.
• If you like, please recommend, if you have new ideas, welcome, email: [Email protected].
• The blog can be reproduced, but must be well-known from the blog source.

SQL Server Series form query