Golden Code SQL notes (8)

------------------------text for easy copy------------------------

page269-301 subquery--subquery--SQL allows a query statement to be used as a result set for other SQL statements, as in the case of a normal table, a query statement that is treated as a result set is called a subquery--a subquery can be used wherever a table can be used, such as a select * The t--above can be used as a subquery instead of the SELECT * FROM (SELECT * from T2 where--age >= 30) here (SELECT * from T2 where age >= 30) is the child Query--You can consider a subquery as a temporary table, which is created at the beginning of the query and destroyed when the query is finished--to simplify the programming of complex SQL statements--build tables and test data--t_reader reader Information Fyearofbirth Reader's year of origin Fprovince Reader Province--fyearofjoin reader membership year create TABLE t_reader (Fid int not null,fname VARCHAR (), Fyearofbirth int, fcity varcha R (Fprovince VARCHAR), Fyearofjoin int)--book Information fyearpublished first edition year Fcategoryid category create TABLE t_book (Fid INT not Null,fname varchar (+), fyearpublished int, Fcategoryid int)--Classification information Create TABLE t_category (FId INT not null,fname VARCHAR ( )--t_readerfavorite Reader and category correspondence Freaderid reader primary key--fcategoryid Classification primary key create TABLE t_readerfavorite (Fcategoryid INT,  Freaderid INT)--test data insert into t_category (FID, FName) VALUES (1, ' story ')--stories insert into t_category (FID, FName) VALUES (2 , ' history ')--historical insert into t_category (FId, FName) VALUES (3, ' theory ')--theory insert INTO t_category (FID, FName) VALUES (4, ' technology ')--technology insert INTO t_category (FID, FName) VALUES (                                                     5, ' art ')--art insert INTO T_category (FId, FName) VALUES (6, ' philosophy ')--philosophy --insert into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (1, ' Tom ', 1979, ' Tangshan ', ' H Ebei ', 2003) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (2, ' Sam ', 1981, ' Langfang ', ' Hebei ', 2001) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (3, ' Jerry ' , 1966, ' Dongguan ', ' Dongguan ', 1995) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES ( 4, ' Lily ', 1972, ' Jiaxing ', ' Zhejiang ', 2005) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, fyearof Join) VALUES (5, ' Marry ', 1985, ' Beijing ', ' Beijing ', 1999) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, fprovince , Fyearofjoin) VALUES (6, ' Kelly ', 1977,' Zhuzhou ', ' Hunan ', 1995) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (7, ' Tim ', 1982, ' Yangzhou ', ' Hunan ', 2001) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (8, ' King ', 1979, ' Jinan ', ' Shandong ', 1997) INSERT into T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALU ES (9, ' John ', 1979, ' Qingdao ', ' Shandong ', 2003) INSERT into T_reader (Fi D, FName, Fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (, ' Lucy ', 1978, ' Luoyany ', ' Henan ', 1996) INSERT into T_rea Der (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (one, ' July ', 1983, ' Zhumadian ', ' Henan ', 1999) INSERT in To T_reader (Fid, FName, fyearofbirth,fcity, Fprovince, Fyearofjoin) VALUES (A, ' Fige ', 1981, ' Jincheng ', ' Shanxi ', 2003) I Nsert into T_book (FID, FName, fyearpublished, Fcategoryid) VALUES (1, ' About EE ', 2005,4) INSERT into T_book (FID, FName , fyearpublished, FcategOryid) VALUES (2, ' Learning Hibernate ', 2003,4) INSERT into T_book (Fid, FName, fyearpublished, Fcategoryid) VALUES (3, ' both Cites ', 1999,1) insert into T_book (Fid, FName, fyearpublished, Fcategoryid) VALUES (4, ' Jane Eyre ', 2001,1) insert INTO T _book (FID, FName, fyearpublished, Fcategoryid) VALUES (5, ' Oliver Twist ', 2002,1) INSERT into T_book (FID, FName, fyearpub lished, Fcategoryid) VALUES (6, ' History of the China ', 1982,2) INSERT into T_book (Fid, FName, fyearpublished, Fcategoryid) VA Lues (7, ' History of England ', 1860,2) INSERT in T_book (Fid, FName, fyearpublished, Fcategoryid) VALUES (8, ' History of A Merica ', 1700,2) INSERT into T_book (Fid, FName, fyearpublished, Fcategoryid) VALUES (9, "History of the World", 2008,2) in SERT into T_book (FID, FName, fyearpublished, Fcategoryid) VALUES ("Atom", 1930,3) INSERT into T_book (FID, FName, Fyea rpublished, Fcategoryid) VALUES (one, ' Relativity ', 1945,3) INSERT into T_book (Fid, FName, fyearpublished, Fcategoryid) VAL UES, ' Computer ', 1970, 3) INSERT into T_book (FID, FName, fyearpublished, Fcategoryid) VALUES (+, ' astronomy ', 1971,3) insert into T_book (FID, FName, fyearpublished, Fcategoryid) VALUES ("How to Singing", 1771,5) INSERT into T_book (Fid, FName, fyearpublished, F CATEGORYID) VALUES (+, ' daodejing ', 2001,6) INSERT into T_book (Fid, FName, fyearpublished, Fcategoryid) VALUES (+, ' Obedi Encetoauthority ', 1995,6) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (1, 1) insert INTO T_readerfavorite (Fcategoryid, Freaderid) VALUES (5, 2) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (2, 3) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (3, 4) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (5, 5) insert INTO T_readerfavorite ( Fcategoryid, Freaderid) VALUES (1, 6) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (1, 7) insert INTO T_ Readerfavorite (Fcategoryid, Freaderid) VALUES (4, 8) INSERT into T_readerfavorite (Fcategoryid, Freaderid) VALUES (6, 9) INSERT into T_readerfavOrite (Fcategoryid, Freaderid) VALUES (5, ten) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (2, one) insert Into T_readerfavorite (Fcategoryid, Freaderid) VALUES (2, a) INSERT into T_readerfavorite (Fcategoryid, Freaderid) values (1, 12) Insert into T_readerfavorite (Fcategoryid, Freaderid) of VALUES (3, 1) insert into T_readerfavorite (Fcategoryid, Freaderid) VALUES (1, 3) INSERT into T_readerfavorite (Fcategoryid, Freaderid) VALUES (4, 4)-subquery has two types, one is to return only one single-valued subquery, At this point it can be--used in a single value can be used in the place, you can think of it as a function that has a return value--another is to return a column of values of the subquery, when the subquery can be seen in memory--the temporary table--single-valued subquery-single-valued subquery, the return value must be only one row ,--such subqueries are called scalar quantum query scalar subquery can be used in SELECT statement--expression, and where statement medium many occasions--the example SELECT statement list, you can think of it as a function to understand the Select 1 as f1,2, (select MIN (fyearpublished) From T_book), the (select MAX (fyearpublished) from T_book) as f4--sample column value subquery SELECT t_reader. Fname,t2. fyearpublished, T2. Fnamefrom T_reader, (SELECT * from T_book WHERE fyearpublished < 1800) t2--single-valued subquery extended advanced-this subquery, you can SELECT MAX (fyearpublish ed) from T_book Select directly execute select FId, FName, (select MAX (fyearpublished) from T_book-t_category--upgrade-This subquery cannot be selected for execution alone, and the sub-query does not depend on the outer query field. So you can--select execution individually--to be able to query the latest published year for a class of books select FId, FName, (select MAX (fyearpublished) from T_book WHERE T_book. Fcategoryid = t_category.fid) scalar query in the FROM T_category--where clause------------------------not only in the list of SELECT statements, but also in the WHERE clause And--The actual subquery is often used in the select Freaderid from dbo in the WHERE clause. T_readerfavoritewhere Fcategoryid = (SELECT FId from t_category WHERE FName = ' stories ')-- Create an inner join first--you can see the intersection of two tables--and then use the subquery to find the earliest first year of the original edition, select C. fid,b, based on the year criteria. FName, B.fyearpublishedfrom T_book as B INNER JOIN t_category as CON b. Fcategoryid = c. Fidwhere B. fyearpublished = (SELECT MIN (fyearpublished) from T_book WHERE T_book. Fcategoryid = C.fid)--The combination of the set operator in,any,all,exists and the subquery--if the subquery is a multiline multi-column, such a subquery can be a temporary table-if the subquery is a multi-line column, such a subquery is actually a result set--sql The In,any,all exeists operators are provided for such result sets-demand: Retrieving reader information for all the books in the year of publication select * from T_readerwhere fyearofjoin in (select Fyearpublished from T_book)--sql the any and some use and functions are the same and the in operator is different--any must be used in conjunction with other comparison operators, and the comparison operator is placed--before any key character, the value that is comparedAlso needs to match any of the values in the subquery select * from T_readerwhere fyearofjoin =any (select fyearpublished from T_book)-- The result of this query is the same as the one in the above query-that is, ' =any ' is equivalent to the in operator--and ' <>any ' is equivalent to the ' not ' in operator--but like ' <any ' is not equivalent, there are other more detailed needs to see--tsql Technology decryption can be more in-depth-find any one of the members of the first edition of the book Select * from T_readerwhere fyearofjoin <any (select fyearpublished from T_book)-- The all operator--all operator requires that the value to be compared needs to match all the values in the subquery, the--all operator cannot be used alone, and must be used in conjunction with the comparison operator-the following SQL statement is used to retrieve books published before all members join: SELECT * from T_ Bookwhere fyearpublished <all (SELECT fyearofjoin from T_reader)--the above is not equivalent to the Min function can be used to take the minimum value of select * FROM T_bookwhere fyearpublished < (select min (fyearofjoin) from T_reader) The--all operator also cannot match a fixed set, such as the following SQL is the wrong SELECT * from T_book WHERE Fyearpublished <all (2001,2002, 2003)--However, this restriction does not hinder the implementation of the feature, because there is no need to--all match a fixed set because the fixed set can be implemented in other ways such as SELECT * From T_book WHERE fyearpublished < 2001--when using the all operator, if the set to be matched is empty, that is, the subquery does not--when any data is returned, whatever the comparison with The value returned by all is always--true. The following query statement fprovince= ' No provinces ' query out is no match-the data, but the result above the thinking, will t_book all the data query out;--the result returned as normal thinking should be null to the semantics of the all operatorThat's it--pay extra attention when using the SELECT * from T_bookwhere fyearpublished <all (select Fyearofjoin from t_reader WHERE fprovince = ' no provinces ') The--exists operator--and in, the Any,all operator--exists operator is the monocular operator, which does not match the column--so it does not require that the set to be matched is a single-column exists operator to check each row-whether or not a subquery is matched, and that exists is used to Tests whether the result of a subquery is empty--, If the result set is empty, the match result is false, otherwise the match result is true--exists is used to check if the subquery returns at least one row of data, and the subquery does not actually--returns any data, but returns a value of TRUE or false--  EXISTS Specifies a subquery that detects the existence of a row. --The following three results are identical select * from T_bookwhere EXISTS (select FName from t_reader WHERE fprovince = ' Shanxi ') SELECT * from T_BOOKW Here EXISTS (select Null) SELECT * from t_book--in the first few examples, using the EXISTS operator either matches all of the data in the returned table, or the mismatch does not return any data, as if the EXISTS operator is not too- -Big meaning in fact, the above example is not practical in practice, exists and related subqueries-the use of it only makes sense to refer to the field in an external query in the relevant subquery, so-when matching the outer subquery of each row of data, the relevant subquery will be based on the current line-information to match the judgment, This allows for very rich functionality--the difference between test and in, select * from T_bookwhere Fcategoryid in (select Fid from t_category WHERE FName = ' history ') SE Lect * from T_bookwhere EXISTS (SELECT Fid from t_category WHERE FName = ' history ' and Fid = T_book. Fcategoryid)--subqueries in other types of SQL statements--subqueries in insertApplication in--BULK insert the result of the query--the syntax field needs one by one corresponding--insert into T_readerfavorite2 (Fcategoryid,freaderid)--select Fcategoryid, Freaderid from t_readerfavorite2--There are additional conditions where the calculation of the where can only require the corresponding field type-the application of the subquery in the UPDATE statement-Example Update T_bookset fyearpublished = (SELECT MAX (fyearpublished) from T_book)--example--the first edition of all books of the same type has been changed to--update T_book B1--set fyearpublished = 2005--where (SELECT COUNT (*) from T_book2 b2--WHERE B1. Fcategoryid=b2. Fcategoryid) >3--The application of the subquery in the DELETE statement-deletes the same book over this delete t_book B1where (SELECT COUNT (*) from T_book2 B2 WHERE B1. Fcategoryid=b2. Fcategoryid) > 3

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Golden Code SQL notes (8)