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

結果の型はブール値で、戻り値は次のとおりです: test_expression の値が begin_expression の値以下、または end_expression の値以上の場合、NOT BETWEEN は true を返します。

注: 除外範囲を指定するには、BETWEEN の代わりに大なり (>) および小なり (

(2) IS [NOT] NULL

指定されたキーワードに従って null 値または非 null 値をクエリします。いずれかのオペランドが null の場合、式は null と評価されます。

(3) [NOT] IN

使用されるキーワードがリストに含まれるかリストから除外されるかに基づいて、クエリする式を指定します。クエリ式では順列または列名を使用でき、リストでは順列またはサブクエリのセットを使用できます (多くの場合)。リストが定数のセットである場合は、一対の括弧内に置く必要があります。

構文は次のとおりです:

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

パラメータの説明:
①test_expression: SQL 式

②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には、式または列名が1つだけ含まれます。外側のステートメントの 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 を超える 3 番目の射撃スコアをクエリします。 リング戦士の情報 (あるクエリの結果を別のクエリで操作されるテーブルとして使用します)

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

注: 派生テーブルにはエイリアスを付ける必要があります。

5. サブクエリを通じてデータを関連付ける

EXISTS 述語を使用してサブクエリを導入します。場合によっては、サブクエリが true または false の値を返す限り、述語条件が満たされているかどうかのみが考慮され、データの内容自体は重要ではありません。この時点で、EXISTS 述語を使用してサブクエリを定義できます。 EXISTS 述語は、サブクエリが 1 つ以上の行を返す場合は true、それ以外の場合は false です。 EXISTS 述語が機能するには、サブクエリで結合された 2 つのテーブルの値と一致するクエリ条件がサブクエリで確立される必要があります。

構文は次のとおりです:

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

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