Go to the Interview Classic Database basics: Second, SQL Advanced case, subquery, paging, join and view

The two usages of case 1.1 equivalent judgment

(1) Specific usage Template:

Case Expression

When value1 then returnvalue1

When value2 then returnvalue2

When value3 then Returnvalue3

ELSE Defaultreturnvalue

END

(2) Examples of specific use:

Let's say we have a forum site with a user table {Uid,name,level},level is an int type that represents the types of users, for example: 1 for Ashes, 2 for prawns, etc. we can use case to judge the equivalence:

SelectName,rank=(     Case  Level         when 1  Then 'Ashes'         when 2  Then 'Prawn'         when 3  Then 'Rookie'    End)     from User

1.2 Item judgment--equivalent if else if else

(1) Specific usage Template:

Case

When condition1 then returnvalue1

When condition2 then returnvalue2

When Condition3 then Returnvalue3

ELSE Defaultreturnvalue

END

Note:The data types returned after then are consistent, and the data types for returnvalue1, returnvalue2, and returnvalue3 must be identical.

(2) Examples of specific use:

Suppose we have a score score table, which records there are students of the results, at this time we want to have a rating of all grades, such as grades if >=90 then rated a class, >=80 and <90 is rated B, here we how to write it?

SelectStudentid,rank=(         Case         when中文版between  -  and  -  Then 'A'         when中文版between  the  and  the  Then 'B'         when中文版between  -  and  -  Then 'C'         when中文版between  -  and  the  Then 'D'         when中文版<  -  Then 'E'        Else 'Lack of test'    End) fromScore

Second, the use of sub-query 2.1 sub-query preliminary

Just as with a normal table, a query statement that is treated as a result set is called a subquery. Almost all places where you can use a table can use subqueries instead. For example, if we want to find all of the students in the class, we can first find the ID of the ITCSC class through the T_class table, and then find all the rows in the T_student table that ClassID is the ITCSC class ID.

Select *  from where ClassId=(    Selectfromwhere Name='  ITCSC one class ')

2.2 Single-valued sub-query

Only subqueries that return and return only one row or column of data can be considered as single-valued subqueries. For example, as we mentioned above, only one classid is returned in the subquery, which is the single-valued subquery. Only single-valued subqueries can be used when subqueries follow after =,! =, <, <=, >, >=,<>, or a subquery is used as an expression.

2.3 Multivalued subqueries

If the subquery is a multi-row, single-column subquery, the result set of such a subquery is actually a collection, then the IN keyword can be used instead of the = sign. For example: if we want to quickly delete all student records from the t_student and ITCSC classes, we can use the IN keyword:

Delete  from where inch (    Selectfromwhere Name=' ITCSC class ' or Name=' ITCSC Second class ')

2.4 exists-you exist in my deep mind

exists is used to determine if there is a result when the query in the EXISTS query returns TRUE, otherwise it returns false. Not exists is the opposite.

When the exists is a where condition, it is to query the main query before the where, and then use the result of the main query to judge the exists query , if it is true then output the result of the current main query, otherwise it will not be output.

The query behind exists is called a correlated subquery, that is, the subquery 's query condition relies on a property value in the outer parent query , which is handled in the following way: Take the first tuple in the outer query, and process the inner query based on its related property values in the inner query. If the WHERE clause returns TRUE, the tuple is placed in the result table, followed by the next tuple in the outer query, repeating the process until all the checks are complete.

For example: We have a personnel information table with a Person type ID field (Ptypeid), which is a foreign key that corresponds to the primary key ptid of the person type table. If we have the following SQL statement, using the EXISTS keyword can be understood as follows:

Select *  from where exists (Select*fromwhere e.ptypeid=et.ptid)

So, in the execution of this SQL, we can understand it as a double for loop, the outside is the main table loop traversal, and then put it into a temp variable, and then into a for loop from the table, and with the items from the table to one of the following matching rules (this is e.ptypeid= Et.ptid) is matched and returns true if a match succeeds, otherwise false.

Third, the handwritten page of SQL code

This assumes that the page size for each page is 10 records

3.1 Using top n for a simple paging

(1) If we want to get the first page of data, that is, the top 10:

Select Top Ten  from Account

(2) Now we want to get the first page after the data, that is, the 20th ~ the last one:

Select *  from where  not inch (selecttopten from

(3) Now we have the first 10 in the data set for the 20th ~ the last to become the second page of data:

Select Top Ten *  from where  not inch (selecttopten from

(4) Summarize the above code as the paging code, set the page size to pagesize, the request page number is pageindex:

Select Top *  from where  not inch (selecttop (@pageIndex-1)*@ from

PS: The disadvantage of this paging method is that if you want to take a lot of pages after the data, then you need to remove many of the previous page ID, query overhead, execution efficiency is low.

It can be seen that the query overhead of Top n is smaller in comparison with the small amount of data. However, in contrast to large data volumes, the row_number approach results in higher query efficiency and lower overhead.

3.2 Efficient paging with row_number ()

(1) SQL Server 2005 adds the Row_number function to simplify the implementation of pagination code. First, Row_number () is a sort function that generates an ordered line number (if it is sorted by ID alone, there is a fault in the middle, for example, an ID row has been deleted). According to MSDN definition: Returns the serial number of the result set partition Insider, with the first row of each partition starting from 1. And what are the criteria for sorting? This is defined by the over () statement that follows. Here we can use an example to see how the line number is generated.

Select *  Over (order by as from account

(2) According to the use of row_number (), we can apply it to the paging, so we can write the following code to achieve the second page of the dataset:

 select  *    ( select  * , Row_number () over  (order  Span style= "color: #0000ff;" >by  Id) as  rownum from   account)  as   T  where  t.rownum between  11  and  20  order  by  t.id "ASC  

(3) Summarize the above code as the paging code, set the page size to pagesize, the request page number is pageindex:

Select *  from (    Select *, Row_number () Over(Order  byID) asRowNum fromAccount ) asTwhereT.rownumbetween(@pageIndex-1)*PageSize+1  and @pageSize*PageIndexOrder  byT.idASC

Iv. various connections-join4.1 Join==inner join

By default, a join is used to represent an intra-inner join, indicating that two tables are connected according to an equivalent rule. For example, the following example: Query all student's number, name and class

Select  from Join  on P.classid=c.id

4.2 Left Join

For example: To check all students (participate and not to take the test) and results, here is related to the student table and the results table, the title required to participate in and not to take the exam to be listed, so the student table as the benchmark, the results table for the left link:

Select *  from Student S  Left Join  on s.s#=SC. s#

can run the result diagram, see Wang Two small this child shoes did not take the exam, there will be no results.

4.3 Right Join

For example, to find out the names of all students who did not take the exam (students who did not exist in the score table). It is still possible to use the student table as a benchmark, but to make a right connection to the score table:

Select *  from Student S  Right Join  on s.s#=SC. s#

4.4 Cross Join

This kind of connection is not common in practical application, but it is the theoretical basis, because it represents the Cartesian product. In fact, all connections are made to the temporary Cartesian product table, and the Cartesian product is a concept in relational algebra, which means that the number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set.

Select *  from Student S  Cross Join SC SC

If there are 10 rows of records in the student table and 20 rows in the SC table, then the cross-join Cartesian product operation of the two tables will result in a 10*20=200 row record.

V. Views 5.1 view from three angles

From a user perspective, a view is viewed from a specific angle to the data in the database.

From within the database system, a view is a virtual table defined by a query that consists of SELECT statements.

From the inside of the database system, the view is composed of data from one or more tables, and from the outside of the database system, the view is like a table, and the general operation of the table can be applied to the view, such as query, insert, modify, delete operation, etc.

5.2 Creating a View

For example, we can create a student score details view that encapsulates a query that requires a three-table connection:

Create View VW_SC  as Select  from Student S Join  on s.s#=SC. s#Join on SC. C #=c.c#

Then, we make a select query for VW_SC:

5.3 Considerations for views

(1) Views do not differ in operation from data tables, but the difference is essentially different: the data table is where the record is actually stored, but the view does not save any records .

(2) The same data table, depending on the different needs of different users, you can create different views (different query statements).

(3) The purpose of the view is to facilitate the query, so In general, the view is not modified, can not be deleted。

Zhou Xurong

Source: http://edisonchou.cnblogs.com

The copyright of this article is owned by the author and the blog Park, welcome reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to give the original link.

Go to the Interview Classic Database basics: Second, SQL Advanced case, subquery, paging, join and view