mysql database operation

.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 (

(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 子句中可使用任何列名，也可以使用任何多个列。这种谓词值只注重是否返回行，而不注重行的内容，用户可以指定列名或者只使用一个“*”。

 

6.实现笛卡尔乘积查询

笛卡尔乘积查询实现了两张表之间的交叉连接，在查询语句中没有 WHERE 查询条件，返回到结果集中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合条件的数据行数。

笛卡尔乘积的关键字是 CROSS JOIN 。例如，用户信息表中有2条数据，职工信息表中有4条数据，当这两张表应用笛卡尔乘积进行查询时，查询的结果就是2×4=8条。

例如：

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

 备注：在进行多表查询时需要主注意，由于多表可能会出现相同的字段，因此在指定查询字段时，最好为重复的字段起别名，以方便区分。

 

7.使用 UNION 并运算

UINON 指的是并运算，即从两个或多个类似的结果集中选择行，并将其组合在一起形成一个单独的结果集。

UINON 运算符主要用于将两个或更多查询结果组合为单个结果集，该结果集包含联合查询中所有查询的全部行。在使用 UNION 运算符时应遵循以下准则：

①在使用 UNION 运算符组合的语句中，所有选择列表的表达式数目必须相同（列名、算术表达式、聚集函数等）。

②在使用 UNION 运算符组合的结果集中的相应列或个别查询中使用的任意列的子集必须具有相同的数据类型，并且两者数据类型之间必须存在可能的隐性转换或提供了显式转换。

③利用 UNION 运算符组合的各语句中对应的结果集列出现的顺序必须相同，因为 UNION 运算符是按照各个查询给定的顺序逐个比较各列。

④ UNION 运算符组合不同的数据类型时，这些数据类型将使用数据类型优先级的规则进行转换。

⑤通过 UNION 运算符生产的表中列名来自 UNION 语句中的第一个单独的查询。若要用新名称引用结果集中的某列，必须按第一个 SELECT 语句中的方式引用该列。

例如：

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

 

8.内外连接查询

1）内联接（典型的联接运算，使用像 =  或 之类的比较运算符）。包括相等联接和自然联接。     
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。

内连接可以分为等值连接、自然连接和不等值连接。

等值连接使用等号运算符比较被连接列的值，在查询结果中将列出连接表中的所有列，包括重复列。等值连接返回所有连接表中具有匹配值的行。

等值连接查询的语法如下：

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

 参数说明：

fildList：要查询的字段列表。   
    
2）外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     

1）LEFT  JOIN或LEFT OUTER JOIN     
左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       

2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。例如 ，表 A 右外连接表 B，结果为公共部分 C 加表 B 的结果集。如果表 A 中没有与表 B 匹配的项，就是用 NULL 进行连接。      
3）FULL  JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   
    
3）交叉联接   
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    

FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。     
    
例子：   

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

 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） 内连接   

select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       
  结果是 ：   
  1   张3                   1     23     1   
  2   李四                  2     34     2

 -------------------------------------------------
  2）左连接   

 select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  结果是     
 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       
  结果是     
  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   

  结果是     
  张3                1     23     1   
  李四              2     34     2   
     null                  3     34     4   
  王武                 nul

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

备注：内连接与外连接区别？

内连接只返回两张表相匹配的数据；而外连接是对内连接的扩展，可以使查询更具完整性，不会丢失数据。下面举例说明两者区别。

假设有两张表，分别为表A 与 表B，两张表公共部分为 C 。

内连接的连接结果是两个表都存在记录，可以说 A 内连 B 得到的是 C。

表 A 左外连接B，那么A不受影响，查询结果为公共部分C 加表A的记录集。

表A右外连接B，那么B不受影响，查询结果为公共部分C加表B的记录集。

全外连接表示两张表都不加限制。