mysql資料庫操作

.在查詢結果中不顯示重複記錄

查詢時不顯示重複記錄主要應用了 DISTINCT 關鍵字，該關鍵字用於刪除重複記錄。

在實現查詢操作時，如果查詢的選擇清單中包含一個表的主鍵，那麼每個查詢中的記錄都將是唯一的（因為主鍵在每一筆記錄中有一個不同的值）；如果主鍵不包含在查詢結果中，就可能出現重複記錄。使用 DISTINCT 關鍵字以後即可刪除重複記錄。

DISTINCT 的語法如下：

SELECT DISTINCT select_list;

 注意：DISTINCT 關鍵字並不是指某一行，而是指不重複 SELECT 輸出的所有欄位。這點十分重要，其作用是防止相同的行出現在一個查詢結果的輸出中。

例如：

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

 

2.使用 NOT 查詢不符合條件的記錄

使用 NOT 與謂詞組合所形成的條件進行查詢。

NOT 與謂詞組合所形成的表達式分別是 [NOT] BETWEEN、IS [NOT] NULL 和 [NOT] IN 。

（1）[NOT] BETWEEN

此條件指定值的包含範圍，使用 AND 將開始值和結束值分開。

其語法如下：

test_expression [NOT] BETWEEN begin_expression AND end_expression

 結果型別為 boolean ，傳回值為：若 test_expression 的值小於等於 begin_expression 的值或大於等於 end_expression 的值，則 NOT BETWEEN 傳回 true。

注意：若要指定排除範圍，也可以使用大於（>）和小於（<）運算子來取代 BETWEEN。 <）运算符代替 BETWEEN。

（2）IS [NOT] NULL

根據所使用的關鍵字指定對空值或非空值進行查詢，如果有任何操作數是 null， 表達式取值為 null 。

（3）[NOT] IN

根據所使用的關鍵字是包含在清單內還是排除在清單外，指定對表達式進行查詢。查詢表達式可以使用常亮或列名，而列表可以是一組常亮或子查詢（更多的情況下）。如果列表為一組常數，則應放置在一對圓括號內。

其語法如下：

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

參數說明：
①test_expression：SQL 表達式

   ②subquery：包含某列結果集的子查詢，該列必須與 test_expression ②subquery：包含某列結果集的子查詢，該列必須與 test_expression。

   ③expression[,...n]：一個表達式列表，用來測試是否符合。所有的表達式必須和 test_expression  j有相同的資料型別。

例如：

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

 

 3.將子查詢作為表達式

將子查詢應用在 SELECT 子句中，其查詢結構就可以以表達式的形式出現。在應用子查詢有一些控制規則，了解這些規則有助於更好的掌握子查詢的應用。

①由比較運算子引入的內層查詢 SELECT 清單或 IN 只包括一個表達式或列名。在外層語句的 WHERE 子句中所命名的資料列必須能與查詢 SELECT 清單中命名的資料列連線相容。

②由不可更改的比較運算子引入的子查詢 （比較運算子後面不跟關鍵字ANY 和 ALL）不能包括 GROUP BY 子句或 HAVING 子句，除非預先確定了群組或單一的值。

③由 EXISTS 引入的SELECT 列表一般都由星號（*）組成，而不必指定特定的列名，也可以嵌套子查詢 WHERE 子句中限定行。

④子查詢不能在內部處理它們的結果，也就是說，子查詢不能包括 ORDER BY 子句。可選擇的 DISTINCT 關鍵字可有效的對子查詢結果進行排序，因為某些系統會透過先將結果排序來消除重複記錄。

例如：顯示全部學生總成績及學生總成績與全校平均總成績之差。

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.用子查詢作為派生表

在實際 應用中，經常使用子查詢作為派生表，就是將查詢的結果集作為一個表使用。

子查詢是一個用來處理多表運算的附加方法。語法架構如下：

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

例如：

將銷售單按商品名稱統計分組後查詢銷售數量大於14的商品（將分組統計數據作為派生表）

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

 對商品銷售表中銷售數量前100名進行分組統計（將過濾資料作為派生表）

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

 統計客戶關係表中未結帳客戶的欠款金額（將過濾資料作為派生表）

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

 查詢所有戰士訓練資訊和查詢第三次射擊成績大於8環的戰士資訊（將一個查詢結果作為另一個查詢所操作的表）

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

 備註：必須為派生表起別名。

 

5.透過子查詢關聯資料

利用 EXISTS 謂詞引入子查詢。在某些情況下，只要子查詢傳回一個真值或假值，只考慮是否滿足謂詞條件，資料內容本身並不重要。此時可以使用 EXISTS 謂詞來定義子查詢。如果子查詢傳回一行或多行，EXISTS 謂詞為真，否則為假。要使 EXISTS 謂詞起作用，應該在子查詢中建立查詢條件以符合子查詢連接起來的兩個表中的值。

文法如下：

EXISTS subquery

 參數說明：

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的记录集。

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