.Do not display duplicate records in query results

Do not display duplicate records when querying mainly applies the DISTINCT keyword, which is used to delete duplicate records.

When implementing query operations, if the query's select list contains the primary key of a table, then the records in each query will be unique (because the primary key has a different value in each record); if the primary key does not Included in the query results, duplicate records may appear. Use the DISTINCT keyword to delete duplicate records. The syntax of

DISTINCT is as follows:

SELECT DISTINCT select_list;

Note: The DISTINCT keyword does not refer to a certain row, but refers to all columns output by SELECT without duplication. This is important because it prevents the same rows from appearing in the output of a query.

For example:

select distinct name,price,date,address,quality from tb;

2. Use NOT to query records that do not meet the conditions

Use the condition formed by combining NOT with the predicate to query. The expressions formed by combining

NOT with predicates are [NOT] BETWEEN, IS [NOT] NULL and [NOT] IN respectively.

(1)[NOT] BETWEEN

This condition specifies the inclusive range of values, using AND to separate the start value and the end value.

The syntax is as follows:

test_expression [NOT] BETWEEN begin_expression AND end_expression

The result type is boolean and the return value is: If the value of test_expression is less than or equal to the value of begin_expression or greater than or equal to the value of end_expression, then NOT BETWEEN returns true.

Note: To specify an exclusion range, you can also use the greater than (>) and less than (<) operators instead of BETWEEN. <）運算符代替 BETWEEN。

(2)IS [NOT] NULL

Queries for null or non-null values ??according to the keyword specified. If any operand is null, the expression evaluates to null.

(3) [NOT] IN

Specifies the expression to be queried based on whether the keyword used is included in the list or excluded from the list. Query expressions can use permutations or column names, and lists can be a set of permutations or subqueries (more often). If the list is a set of constants, it should be placed within a pair of parentheses.

The syntax is as follows:

test_expression [NOT] in( subquery   expression[,...n] )

Parameter description:
①test_expression: SQL expression

②subquery: A subquery containing a certain column result set, which must have the same data type as test_expression.

③expression[,...n]: A list of expressions used to test whether they match. All expressions must be of the same data type as test_expression j.

For example:

select * from tb where selldate not between '2016-10-30' and '2016-12-12';

3. Use the subquery as an expression

Apply the subquery in the SELECT clause, and the query structure can appear in the form of an expression. There are some control rules when applying subqueries. Understanding these rules will help you better master the application of subqueries.

①The inner query SELECT list or IN introduced by the comparison operator contains only one expression or column name. Columns named in the WHERE clause of the outer statement must be join-compatible with columns named in the query's SELECT list.

②Subqueries introduced by immutable comparison operators (comparison operators not followed by the keywords ANY and ALL) cannot include a GROUP BY clause or a HAVING clause unless the group or individual values ??are predetermined.

③The SELECT list introduced by EXISTS generally consists of asterisks (*), without specifying specific column names, and you can also nest subqueries to limit rows in the WHERE clause.

④Subqueries cannot process their results internally, that is, subqueries cannot include an ORDER BY clause. The optional DISTINCT keyword is useful for sorting subquery results, since some systems eliminate duplicate records by sorting the results first.

For example: display the total scores of all students and the difference between the students’ total scores and the school’s average score.

select stuId , stuName, (Math+Language+English)  Total , round((select avg(Math+Language+English) from tb),0)  Averages,
 round(（(Math+Language+English)-(select avg(Math+Language+English) from tb)),0) Average  from tb;

4. Use subqueries as derived tables

In practical applications, subqueries are often used as derived tables, that is, the result set of the query is used as a table.

Subquery is an additional method for handling multi-table operations. The syntax structure is as follows:

(SELECT [ALL|DISTINCT]<select item list> From <table list>
[WHERE <search condition>]   [GROUP BY<group item list>  [HAVING <group by search condition>]]   )

For example:

Group the sales orders according to the product name statistics and query the products with sales quantity greater than 14 (use the group statistics as a derived table)

select *  from (select proname ,COUNT(*) as sl  from td GROUP BY proname) WHERE (sl > 14) ;

Perform the top 100 sales quantity in the product sales table Group statistics (use filtered data as a derived table)

select sl,count(*)  from ( select * from tb ORDER BY zdbh LIMIT 0,100) GROUP BY sl;

Statistics of the amount owed by unsettled customers in the customer relationship table (use filtered data as a derived table)

select name,sum(xsje) from  (select * from tb  where NOT pay) GROUP BY name;

Query all warrior training information and query the third shooting score greater than 8 Ring warrior information (use the results of one query as the table operated by another query)

select T.soldId, T.soldName, T.FrirstGun, T.SecondGun, T.ArtideGun from (select * from tb where ArtideGun>8) as T;

Note: The derived table must be aliased.

5. Relate data through subqueries

Use EXISTS predicate to introduce subqueries. In some cases, as long as the subquery returns a true or false value, only whether the predicate condition is met is considered, and the data content itself is not important. At this point you can use the EXISTS predicate to define a subquery. The EXISTS predicate is true if the subquery returns one or more rows, false otherwise. For the EXISTS predicate to work, the query conditions should be established in the subquery to match the values ??in the two tables joined by the subquery.

The syntax is as follows:

EXISTS subquery

Parameter description:

subquery：一個受限的 SQL 語句（不允許有 COMPUTE 子句和 INTO 關鍵字） 。

例如：獲取英語成績大于90分的學生信息

select name,college,address from tb_Stu where exists (select name from tb_grades M where M.name=I.name and English>90) ;

備注：EXISTS 謂詞子查詢中的 SELECT 子句中可使用任何列名，也可以使用任何多個列。這種謂詞值只注重是否返回行，而不注重行的內(nèi)容，用戶可以指定列名或者只使用一個“*”。

6.實現(xiàn)笛卡爾乘積查詢

笛卡爾乘積查詢實現(xiàn)了兩張表之間的交叉連接，在查詢語句中沒有 WHERE 查詢條件，返回到結(jié)果集中的數(shù)據(jù)行數(shù)等于第一個表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個表中符合條件的數(shù)據(jù)行數(shù)。

笛卡爾乘積的關鍵字是 CROSS JOIN 。例如，用戶信息表中有2條數(shù)據(jù)，職工信息表中有4條數(shù)據(jù)，當這兩張表應用笛卡爾乘積進行查詢時，查詢的結(jié)果就是2×4=8條。

例如：

select EmpId,EmpName,Depatment,JobTitle,Wages from tb_employees a cross join tb_position b;

備注：在進行多表查詢時需要主注意，由于多表可能會出現(xiàn)相同的字段，因此在指定查詢字段時，最好為重復的字段起別名，以方便區(qū)分。

7.使用 UNION 并運算

UINON 指的是并運算，即從兩個或多個類似的結(jié)果集中選擇行，并將其組合在一起形成一個單獨的結(jié)果集。

UINON 運算符主要用于將兩個或更多查詢結(jié)果組合為單個結(jié)果集，該結(jié)果集包含聯(lián)合查詢中所有查詢的全部行。在使用 UNION 運算符時應遵循以下準則：

①在使用 UNION 運算符組合的語句中，所有選擇列表的表達式數(shù)目必須相同（列名、算術表達式、聚集函數(shù)等）。

②在使用 UNION 運算符組合的結(jié)果集中的相應列或個別查詢中使用的任意列的子集必須具有相同的數(shù)據(jù)類型，并且兩者數(shù)據(jù)類型之間必須存在可能的隱性轉(zhuǎn)換或提供了顯式轉(zhuǎn)換。

③利用 UNION 運算符組合的各語句中對應的結(jié)果集列出現(xiàn)的順序必須相同，因為 UNION 運算符是按照各個查詢給定的順序逐個比較各列。

④ UNION 運算符組合不同的數(shù)據(jù)類型時，這些數(shù)據(jù)類型將使用數(shù)據(jù)類型優(yōu)先級的規(guī)則進行轉(zhuǎn)換。

⑤通過 UNION 運算符生產(chǎn)的表中列名來自 UNION 語句中的第一個單獨的查詢。若要用新名稱引用結(jié)果集中的某列，必須按第一個 SELECT 語句中的方式引用該列。

例如：

select filenumuber,name,juior,address  from tb union select filenumuber,name,senior,address from tk;

8.內(nèi)外連接查詢

1）內(nèi)聯(lián)接（典型的聯(lián)接運算，使用像 = 或 <> 之類的比較運算符）。包括相等聯(lián)接和自然聯(lián)接。
內(nèi)聯(lián)接使用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的行。例如，檢索 students和courses表中學生標識號相同的所有行。

內(nèi)連接可以分為等值連接、自然連接和不等值連接。

等值連接使用等號運算符比較被連接列的值，在查詢結(jié)果中將列出連接表中的所有列，包括重復列。等值連接返回所有連接表中具有匹配值的行。

等值連接查詢的語法如下：

select fildList from  table1 inner join table2 on table1.column = table2.column;

參數(shù)說明：

fildList：要查詢的字段列表。

2）外聯(lián)接。外聯(lián)接可以是左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接。
在 FROM子句中指定外聯(lián)接時，可以由下列幾組關鍵字中的一組指定：

1）LEFT JOIN或LEFT OUTER JOIN
左向外聯(lián)接的結(jié)果集包括 LEFT OUTER子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。

2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。例如 ，表 A 右外連接表 B，結(jié)果為公共部分 C 加表 B 的結(jié)果集。如果表 A 中沒有與表 B 匹配的項，就是用 NULL 進行連接。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部聯(lián)接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結(jié)果集行包含基表的數(shù)據(jù)值。

3）交叉聯(lián)接
交叉聯(lián)接返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉聯(lián)接也稱作笛卡爾積。

FROM 子句中的表或視圖可通過內(nèi)聯(lián)接或完整外部聯(lián)接按任意順序指定；但是，用左或右向外聯(lián)接指定表或視圖時，表或視圖的順序很重要。有關使用左或右向外聯(lián)接排列表的更多信息，請參見使用外聯(lián)接。

例子：

-------------------------------------------------

 a表     id   name    　　 b表     id   job   parent_id   
      1   張3           　　        1     23     1   
              2   李四            　　     2     34     2   
              3   王武           　　      3     34     4       
  a.id同parent_id 存在關系

--------------------------------------------------
1） 內(nèi)連接

select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       
  結(jié)果是 ：   
  1   張3                   1     23     1   
  2   李四                  2     34     2

-------------------------------------------------
2）左連接

 select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  結(jié)果是     
 1   張3                   1     23     1   
  2   李四                  2     34     2   
  3   王武                  null

-------------------------------------------------

3） 右連接

 select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       
  結(jié)果是     
  1   張3                   1     23     1   
  2   李四                  2     34     2   
  null                     3     34     4

-------------------------------------------------
4） 完全連接

select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   

  結(jié)果是     
  張3                1     23     1   
  李四              2     34     2   
     null                  3     34     4   
  王武                 nul

-------------------------------------------------

備注：內(nèi)連接與外連接區(qū)別？

內(nèi)連接只返回兩張表相匹配的數(shù)據(jù)；而外連接是對內(nèi)連接的擴展，可以使查詢更具完整性，不會丟失數(shù)據(jù)。下面舉例說明兩者區(qū)別。

假設有兩張表，分別為表A 與 表B，兩張表公共部分為 C 。

內(nèi)連接的連接結(jié)果是兩個表都存在記錄，可以說 A 內(nèi)連 B 得到的是 C。

表 A 左外連接B，那么A不受影響，查詢結(jié)果為公共部分C 加表A的記錄集。

表A右外連接B，那么B不受影響，查詢結(jié)果為公共部分C加表B的記錄集。

全外連接表示兩張表都不加限制。