Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve form query in SqlServer, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Elements of the SELECT statement

2.1 General query clauses and logical processing order

When retrieving a data table, the query statement generally includes clauses such as FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY,TOP,OVER. Please consider the logical processing order of the following example.

USE TSQLFundamentals2008SELECT empid,YEAR (orderdate) AS orderyear,COUNT (*) numordersFROM Sales.OrdersWHERE custid=71GROUP BY empid,YEAR (orderdate) HAVING COUNT (*) > 1ORDER BY empid,orderyear

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

USE TSQLFundamentals2008FROM Sales.OrdersWHERE custid=71GROUP BY empid,YEAR (orderdate) HAVING COUNT (*) > 1SELECT empid,YEAR (orderdate) AS orderyear,COUNT (*) numordersORDER BY empid,orderyear

The logical processing order can be summed up as follows:

Note:

a. In conventional programming languages, such as cymbals, SELECT, etc., the program is executed step by step "from top to bottom". However, in SQL, the position of the Java statement is not the first to execute.

b. The result set returned by each step of the logical processing order is the result set to be executed by the next step statement of that step.

C.FROM acquires the data source (or data table), WHERE filters the conditions on the basis of FROM, GROUP BY groups the collection according to at least one column on the basis of WHERE, HAVING filters the grouped collection on the basis of GROUP BY, SELECT statement searches on the basis of HAVING, and ORDER BY sorts on the basis of SELECT according to certain conditions.

2.2 explanation of partial query clause

2.2.1 FROM clause

a. Using the object name of the database schema in the qualified code, even without the database schema qualification, Sql Server will implicitly parse it, which is more expensive, except for the first time, if the object name is the same and there is no schema qualification, there will be ambiguity.

B.FROM * performance is lower than FROM conum_name performance.

The order of result sets obtained by c.FROM query is random.

2.2.2 WHERE clause

a. Filter rows returned by the FROM phase

B.WHERE predicate or logical expression

The c.WHERE clause has an important impact on query performance. Based on the filter expression, Sql Server calculates what index to use to access the requested data.

d. Scan the whole table and return all possible rows. Filtering on the client side is expensive, such as generating a large amount of network traffic.

E.T-SQL uses ternary predicate logic (true,false,unknown)

2.2.3 GROUP BY clause

The a.GROUP BY phase combines the rows returned by the previous stage of logical query processing into "groups", each group determined by the elements specified in the GROUP BY clause

b. If grouping is involved in the query statement, then the operands for all phases (including HAVING, SELECT, and ORDER BY) after the GROUP BY phase will be groups, not individual rows. Each group is ultimately represented as a row in the query result set

All expressions specified in clauses processed after the c.GROUP BY phase must be guaranteed to return only one scalar (single value) for each group. Expressions based on elements in the GROUP BY list meet this requirement because, by definition, GROUP BY elements appear only once in each group

d. Aggregate functions return only one value for each group, so if an element no longer appears in the GROUP BY list, it can only be used as input to aggregate functions (COUNT, SUM, AVG, MIN, and MAX). (note: if there is a GROUP BY clause, the aggregate function operates on only specific groups, not all groups.)

e. All aggregate functions ignore NULL, except for COUNT (*)

f. In aggregate functions, you can use distinct to handle non-repeating numbers, such as count (distinct vary)

2.2.4 HAVING clause

The a.HAVING clause is used to specify predicates or logical expressions that filter groups, which corresponds to individual row filtering in the WHERE phase

b. Because the HAVING clause is post-processed after grouping rows, aggregate functions, such as HAVING COUNT (*) > 1, can be referenced in logical expressions, meaning that the HAVING phase filter retains only groups that contain multiple lines

2.2.5 SELECT clause

The a.SELECT clause is used to specify the attributes (columns) to be included in the result set returned by the query.

The b.SELECT clause returns the name type of the column:

Aliases are defined directly based on the columns of the table being queried in three ways, and AS is recommended. AS;= (alias equal sign expression); (expression space alias) columns without names

c. In the relational model, all operations and relationships are based on relational algebra and the results in relational (sets), but in SQL, the situation is slightly different, because SELECT queries are not guaranteed to return a true set (that is, an unordered set of unique rows). First of all, SQL does not require tables to meet the collection 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 or bag. However, even if the table being queried has a primary key, it meets the criteria of the collection, and a SELECT query against that table may still return results that contain duplicates. The result set is often used when describing the output of a SELECT query. However, the result set does not have to strictly satisfy the set condition in the mathematical sense.

D.DISTINCT constraint to ensure the uniqueness of rows and delete duplicate rows

e. Try not to query all columns in the form of SELECT *, but try to specify

2.2.6 ORDER BY

a. The most important thing to understand SQL is to understand that tables are not guaranteed to be ordered, because tables represent a collection (or multiple sets if there are duplicates), and collections are unordered. This means that if you do not specify an ORDER BY clause when querying the table, SQL Server is free to sort the rows of the result sheet in any order, although the query can return a result table.

b. In ORDRTB BY, ASC is used for ascending order, DESC for descending order, and the default is ascending order

c. A query with an ORDER BY clause produces a result that ANSI calls a cursor (a non-relational result in which rows are in a fixed order). Some language elements and operations in SQL are expected to process only the table results of the query, not cursors, such as table expressions and set operations

3 answers to questions

Q1:KEY

-- method 1select distinct studentName from StudentScoreswhere studentName not in (select distinct studentName from StudentScoreswhere courseGrades80

Q2:KEY

DELETE DEMO_DELTE WHERE ID NOT IN (SELECT min (ID) FROM DEMO_DELTE_2 GROUP BY xuehao,XM,kcbh,kcmc,fs)

Q3:KEY

SELECT team1.TeamName,team2.TeamName FROM Team team1,Team team2 WHERE team1.TeamName

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.