澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:mysql数据库操作,MySQL多表

2019-09-22 作者:数据库网络   |   浏览(107)

 

mysql数据库操作(3),mysql数据库操作

 

1.在查询结果中不显示重复记录

查询时不显示重复记录主要应用了 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。

(2)IS [NOT] 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 只包括一个表达式或列名。在外层语句的 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   

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

备注:内连接与外连接区别?

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

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

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

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

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

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

 

////end

1.在查询结果中不显示重复记录 查询时不显示重复记录主要应用了 DISTINCT 关键字,该关键字用于删除...

T-SQL笔记1:SELECT及SELECT高级应用

一使用SELECT子句进行多表查询

1.在查询结果中不显示重复记录

 

SELECT 字段名
FROM 表1,表2 …
WHERE 表1.字段 = 表2.字段 AND 其它查询条件

SELECT 
a.id,
a.name,
a.address,
a.date,
b.math,
b.english,
b.chinese 
FROM 
tb_demo065_tel AS b,
tb_demo065 AS a 
WHERE a.id=b.id

查询时不显示重复记录主要应用了 DISTINCT 关键字,该关键字用于删除重复记录。

本章摘要

注:在上面的的代码中,以两张表的id字段信息相同作为条件建立两表关联,但在实际开发中不应该这样使用,最好用主外键约束来实现

在实现查询操作时,如果查询的选择列表中包含一个表的主键,那么每个查询中的记录都将是唯一的(因为主键在每一条记录中有一个不同的值);如果主键不包含在查询结果中,就可能出现重复记录。使用 DISTINCT 关键字以后即可删除重复记录。

1:安装AdventureWorks

二使用表的别名进行多表查询

DISTINCT 的语法如下:

2:基本运算符和表达式

如:

SELECT 
a.id,
a.name,
a.address,
b.math,
b.english,
b.chinese 
FROM 
tb_demo065  a,
tb_demo065_tel  b 
WHERE 
a.id=b.id AND b.id='$_POST[textid]'
SELECT DISTINCT select_list;

3:between

SQL语言中,可以通过两种方式为表指定别名 

 注意:DISTINCT 关键字并不是指某一行,而是指不重复 SELECT 输出的所有列。这一点十分重要,其作用是防止相同的行出现在一个查询结果的输出中。

4:like

第一种是通过关键字AS指定,如

例如:

5:escape

SELECT 
a.id,
a.name,
a.address,
b.math,
b.english,
b.chinese 
FROM 
tb_demo065 AS a,
tb_demo065_tel AS b 
WHERE a.id=b.id
select distinct name,price,date,address,quality from tb;

6:TOP

第二种是在表名后直接加表的别名实现

 

7:GROUP BY

SELECT 
a.id,
a.name,
a.address,
b.math,
b.english,
b.chinese 
FROM 
tb_demo065  a,
tb_demo065_tel  b 
WHERE a.id=b.id 

2.使用 NOT 查询不满足条件的记录

   7.1:GROUP BY ALL

使用表的别名应注意几下几点

使用 NOT 与谓词进行组合所形成的条件进行查询。

   7.2:HAVING

(1)别名通常是一个缩短了的表名,用于在连接中引用表中的特定列,如果连接中的多个表中有相同的名称列存在,必须用表名或表的别名限定列名

NOT 与谓词进行组合所形成的表达式分别是 [NOT] BETWEEN、IS [NOT] NULL 和 [NOT] IN 。

8:SELECT字句技术

(2)如果定义了表的别名就不能再使用表名

(1)[NOT] BETWEEN

   8.1:使用DISTINCT消除重复值

三合并多个结果集

该条件指定值的包含范围,使用 AND 将开始值和结束值分开。

   8.2:返回拼接的结果

SQL语言中,可以通过UNION 或 ALL将多个SELECT语句的查询结果合并输出,这两个关键字的使用说明如下:

其语法如下:

   8.3使用INTO字句

UNION:利用该关键字可以将多个SELECT 语句的查询结果合并输出,并删除重复行

test_expression [NOT] BETWEEN begin_expression AND end_expression

9:子查询

ALL:利用该关键字可以将多个SELECT 语句的查询结果合并输出,但不会删除重复行

 结果类型为 boolean ,返回值为:如果 test_expression 的值小于等于 begin_expression 的值或者大于等于 end_expression 的值,则 NOT BETWEEN 返回 true。

   9.1:子查询类型

在使用UNION或ALL关键字将多个表合并输出时,查询结果必须具有相同的结构并且数据类型必须兼容,另外使用UNION时两张表的字段数量也必须相同,否则会提示SQL语句有错误。

注意:若要指定排除范围,还可以使用大于(>)和小于(<)运算符代替 BETWEEN。

   9.2:代替表达式的查询

SELECT 
id,name,pwd 
FROM tb_demo067 
UNION 
SELECT  
uid,price,date 
FROM tb_demo067_tel

(2)IS [NOT] NULL

  9.3:多层嵌套

四简单嵌套查询

根据所使用的关键字指定对空值或非空值进行查询,如果有任何操作数是 null, 表达式取值为 null 。

10:比较使用 EXISTS 和 IN 的查询

子查询:子查询是一个SELECT查询,返回单个值且嵌套在SELECT、INSERT、UPDATE和DELETE语句或其它查询语句中,任何可以使用表达式的地方都可以使用子查询.

(3)[NOT] IN

11:联接

  11.1:使用衍生表
SELECT 
id,name,sex,date 
FROM tb_demo068 
WHERE 
id in(SELECT id FROM tb_demo068 WHERE id='$_POST[test]')

根据所使用的关键字是包含在列表内还是排除在列表外,指定对表达式进行查询。查询表达式可以使用常亮或列名,而列表可以是一组常亮或者子查询(更多的情况下)。如果列表为一组常量,则应该放置在一对圆括号内。

   11.2:UNION

内连接:把查询结果作为WHERE子句的查询条件即称为内连接

其语法如下:

12:TABLESAMPLE

五复杂的嵌套查询

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

参数说明: 
①test_expression:SQL 表达式

13:公共表表达式common_table_expression

多表之间的嵌套查询可以通过谓词IN实现,语法格式如下:

   ②subquery:包含某列结果集的子查询,该列必须与 test_expression  具有相同的数据类型。

 

test_expression[NOT] IN{
 subquery
}

   ③expression[,...n]:一个表达式列表,用来测试是否匹配。所有的表达式必须和 test_expression  j具有相同的数据类型。

1:安装AdventureWorks

参数说明:test_expression指SQL表达式,subquery包含某结果集的子查询

例如:

     本系列笔记均基于AdventureWorks数据库,有关AdventureWorks的安装帮助如下:

多表嵌套查询的原理:无论是多少张表进行嵌套,表与表之间一定存在某种关联,通过WHERE子句建立此种关联实现查询

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

澳门新萄京官方网站:mysql数据库操作,MySQL多表查询。    在 Management Studio 工具栏上,单击“文件”,指向“打开”,然后单击“文件”

六嵌套查询在查询统计中的应用

 

    浏览到文件 instawdb.sql,并单击“打开”。该文件的默认位置为 C:Program FilesMicrosoft SQL Server90ToolsSamplesAdventureWorks OLTP。

实现多表查询时,可以同时使用谓词ANY、SOME、ALL,这些谓词被称为定量比较谓词,可以和比较运算符联合使用,判断是否全部返回值都满足搜索条件.SOME和ANY谓词是存在量的,只注重是否有返回值满足搜索条件,这两个谓词的含义相同,可以替换使用;ALL谓词称为通用谓词,它只关心是否有谓词满足搜索要求.

 3.将子查询作为表达式

    运行脚本之前,在脚本中找到语句 SET @data_path = @sql_path 'AWDB';,并更改该语句使其指向 instawdb.sql 脚本的位置。例如,SET @data_path = 'C:Program FilesMicrosoft SQL Server90ToolsSamplesAdventureWorks OLTP';

SELECT * 
FROM tb_demo069_people 
WHERE 
uid IN(SELECT deptID FROM tb_demo069_dept WHERE deptName='$_POST[select]')

SELECT a.id,a.name FROM tb_demo067 AS a WHERE id<3

将子查询应用在 SELECT 子句中,其查询结构就可以以表达式的形式出现。在应用子查询有一些控制规则,了解这些规则有助于更好的掌握子查询的应用。

    执行脚本。

>ANY 大于子查询中的某个值 

①由比较运算符引入的内层查询 SELECT 列表或 IN 只包括一个表达式或列名。在外层语句的 WHERE 子句中命名的列必须能与查询 SELECT 列表中命名的列连接兼容。

 

 

②由不可更改的比较运算符引入的子查询 (比较运算符后面不跟关键字ANY 和 ALL)不能包括 GROUP BY 子句或 HAVING 子句,除非预先确定了组或单个的值。

2:基本运算符和表达式

>=ANY 大于等于子查询中的某个值  
<=ANY 小于等于子查询中的某个值 
=ANY 等于子查询中的某个值  
!=ANY或<>ANY 不等于子查询中的某个值  
>ALL 大于子查询中的所有值 

③由 EXISTS 引入的SELECT 列表一般都由星号(*)组成,而不必指定具体的列名,也可以嵌套子查询 WHERE 子句中限定行。

运算符 描述
!=
!>
!<
<
<=
<>
=
>
>=
ALL 比较标量值和单列集中的值。
ANY 比较标量值和单列集中的值。SOME 和 ANY 是等效的
BETWEEN 自动根据SQL的型别进行取值
CONTAINS 为单词或短语执行模糊搜索
ESCAPE 指定要以字面值形式搜索,而不是被解释为通配符
EXISTS 指定一个子查询,测试行是否存在
FREETEXT 根据意思,而不是字面值来搜索数据中的单词
IN WHERE color in (‘red’,‘blue’)
IS NOT NULL
IS NULL 检测NULL值
LIKE 根据通配符进行模式匹配
NOT BETWEEN
NOT IN
NOT LIKE
SOME 比较标量值和单列集中的值。SOME 和 ANY 是等效的

 

④子查询不能在内部处理它们的结果,也就是说,子查询不能包括 ORDER BY 子句。可选择的 DISTINCT 关键字可有效的对子查询结果进行排序,因为一些系统会通过首先将结果排序来消除重复记录。

 

>=ALL 大于等于子查询中的所有值 
<=ALL 小于等于子查询中的所有值 
=ALL 等于子查询中的所有值 
!=ALL或<>ALL 不等于子查询中的所有值

例如:显示全部学生总成绩及学生总成绩与全校平均总成绩之差。

3:between

 

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;

    使用:

七.使用子查询作派生的表

 

select SalesOrderID, ShipDate from Sales.SalesOrderHeader
where ShipDate between '7/28/2002' and '7/29/2002'

在实际项目开发过程中经常用到从一个信息较为完善的表中派生出一个只含有几个关键字段的信息表,通过子查询就可以来实现这一目标,如

4.用子查询作为派生表

    结果:将会返回17条语句。

SELECT 
people.name,people.chinese,people.math,people.english 
FROM 
(SELECT name,chinese,math,english FROM tb_demo071) AS people

在实际 应用中,经常使用子查询作为派生表,就是将查询的结果集作为一个表使用。

    也许有人会用,使用:

注:子查询应遵循以下规则:

子查询是一个用于处理多表操作的附加方法。语法结构如下:

where'7/28/2002' < ShipDate and ShipDate < '7/29/2002'

(1)由比较运算符引入的内层子查询只包含一个表达式或列名,在外层语句中的WHERE子句内命名的列必须与内层子查询命名的列兼容

(SELECT [ALL|DISTINCT]<select item list> From <table list>

[WHERE <search condition>]   [GROUP BY<group item list>  [HAVING <group by search condition>]]   )

    不行吗?答案是不行。结果会返回0。

(2)由不可更改的比较运算符引入的子查询(比较运算符后面不跟关键字ANY或ALL)不包括GROUP BY 或 HAVING子句,除非预先确定了成组或单个的值

例如:

   

(3)用EXISTS引入的SELECT列表一般都由*组成,不必指定列名

将销售单按商品名称统计分组后查询销售数量大于14的商品(将分组统计数据作为派生表)

4:like

(4)子查询不能在内部处理其结果

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

     存在如下通配符,

八使用子查询作表达式

 对商品销售表中销售数量前100名进行分组统计(将过滤数据作为派生表)

     %:0~N个任意字符;

SELECT 
(SELECT AVG(chinese)FROM tb_demo071),
(SELECT AVG(english)FROM tb_demo071),
(SELECT AVG(math)FROM tb_demo071) 
FROM tb_demo071
select sl,count(*)  from ( select * from tb ORDER BY zdbh LIMIT 0,100) GROUP BY sl;

     _:1个字符;

注:在使用子查询时最好为列表项取个别名,这样可以方便用户在使用mysql_fetch_array()函数时为表项赋值,如

 统计客户关系表中未结账客户的欠款金额(将过滤数据作为派生表)

     []:指定范围或列表中的任何单个字符;

SELECT 
(SELECT AVG(chinese) FROM tb_demo071) AS yuwen ,
(SELECT AVG(english) FROM tb_demo071) AS yingyu,
(SELECT AVG(math) FROM tb_demo071) AS shuxue 
FROM tb_demo071
select name,sum(xsje) from  (select * from tb  where NOT pay) GROUP BY name;

     [^]澳门新萄京官方网站:mysql数据库操作,MySQL多表查询。:指定不再范围中的任何单个字符;

九使用子查询关联数据

 查询所有战士训练信息和查询第三次射击成绩大于8环的战士信息(将一个查询结果作为另一个查询所操作的表)

 

SELECT * 
FROM tb_demo072_student 
WHERE id=(SELECT id FROM tb_demo072_class WHERE className = '$_POST[text]')
select T.soldId, T.soldName, T.FrirstGun, T.SecondGun, T.ArtideGun from (select * from tb where ArtideGun>8) as T;

5:escape

十多表联合查询

 备注:必须为派生表起别名。

    where name like ‘b/B%’ escape ‘/’

利用SQL语句中的UNION,可以将不同表中符合条件的数据信息显示在同一列中。

 

    解析:表示全部以‘b/B’开头的name,其中/不理解为通配符。

SELECT * 
FROM tb_demo074_student 
UNION 
SELECT * 
FROM tb_demo074_fasten

5.通过子查询关联数据

 

注:使用UNION时应注意以下两点:

利用 EXISTS 谓词引入子查询。在某些情况下,只要子查询返回一个真值或假值,只考虑是否满足谓词条件,数据内容本身并不重要。此时可以使用 EXISTS 谓词来定义子查询。如果子查询返回一行或多行,EXISTS 谓词为真,否则为假。要使 EXISTS 谓词起作用,应该在子查询中建立查询条件以匹配子查询连接起来的两个表中的值。

6:TOP

(1)在使用UNION运算符组合的语句中,所有选择列表的表达式数目必须相同,如列名、算术表达式及聚合函数等

语法如下:

    top允许根据定义的行的数量或者百分比查询出开始的N行。如:

(2)在每个查询表中,对应列的数据结构必须一样。

EXISTS subquery

    select top 10 from …

十一对联合后的结果进行排序

 参数说明:

    或者:

为了UNION的运算兼容,要求所有SELECT语句都不能有ORDER BY语句,但有一种情况例外,那就是在最后一个SELECT语句中放置ORDER BY 子句实现结果的最终排序输出。

subquery:一个受限的 SQL 语句(不允许有 COMPUTE 子句和 INTO 关键字) 。

    declare @percentage float

SELECT * 
FROM tb_demo074_student 
UNION 
SELECT * FROM tb_demo074_fasten 
ORDER BY id

例如:获取英语成绩大于90分的学生信息

    set @percentage =1

使用UNION条件上相对比较苛刻,所以使用此语句时一定要注意两个表项数目和字段类型是否相同

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

    select top (@percentage)  percent * from Sales.SalesOrderHeader

十二条件联合语句

 备注:EXISTS 谓词子查询中的 SELECT 子句中可使用任何列名,也可以使用任何多个列。这种谓词值只注重是否返回行,而不注重行的内容,用户可以指定列名或者只使用一个“*”。

 

SELECT * 
FROM tb_demo076_BEIJING 
GROUP BY name 
HAVING name='人民邮电出版社' OR name='机械工业出版社' 
UNION 
SELECT * 
FROM tb_demo076_BEIJING 
GROUP BY name 
HAVING name <>'人民邮电出版社' AND name <>'机械工业再版社' ORDER BY id

 

7:GROUP BY

上面语句应用了GROUP BY分组语句和HAVING语句实现条件联合查询。其实现目的是先保证将'人民邮电出版社'和'机械工业出版社'始终位于名单最前列,然后再输出其它的出版社

6.实现笛卡尔乘积查询

    指定用来放置输出行的组。如果 SELECT 子句 <select list> 中包含聚合函数,则 GROUP BY 将计算每组的汇总值。

十三简单内连接查询

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

    上面这句话不太好理解,更好的理解应该解释为:

SELECT filedlist 
FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column1

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

    “由于在SELECT字句中使用了聚合函数,未聚合的列必须出现在GROUP BY子句中。”

其中,filedlist是要显示的字段,INNER表示表之间的连接方式为内连接,table1.column1=table2.column1用于指明两表间的连接条件,如:

例如:

select OrderDate, sum(totalDue) TotalDueByOrderDate from Sales.SalesOrderHeader
where ShipDate between '7/28/2002' and '7/29/2002'
group by OrderDate

SELECT 
a.name,
a.address,
a.date,
b.chinese,
b.math,
b.english 
FROM 
tb_demo065 AS a 
INNER JOIN 
tb_demo065_tel AS b 
on a.id=b.id
select EmpId,EmpName,Depatment,JobTitle,Wages from  tb_employees   a cross join tb_position b;

    结果:

十四复杂内连接查询

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

    (2 行受影响)

复杂的内连接查询是在基本的内连接查询的基础上再附加一些查询条件,如:

 

   

SELECT 
a.name,
a.address,
a.date,
b.chinese,
b.math,
b.english 
FROM 
tb_demo065 AS a 
INNER JOIN 
tb_demo065_tel AS b 
on a.id=b.id 
WHERE 
b.id=(SELECT id FROM  tb_demo065 
WHERE 
tb_demo065.name='$_POST[text]')

7.使用 UNION 并运算

7.1:GROUP BY ALL

总之,实现表与表之间的关联的本质是两表之间存在共同的数据项或者相同的数据项,通过WHERE 子句或内连接INNER JOIN … ON 语句将两表连接起来,实现查询

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

     在上面的代码中,加入ALL,即:

十五使用外连接实现多表联合查询

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

select OrderDate, sum(totalDue) TotalDueByOrderDate from Sales.SalesOrderHeader
where ShipDate between '7/28/2002' and '7/29/2002'
--group by OrderDate
group by all OrderDate

(1)LEFT OUTER JOIN表示表之间通过左连接方式相互连接,也可简写成LEFT JOIN,它是以左侧的表为基准故称左连接,左侧表中所有信息将被全部输出,而右侧表信息则只会输出符合条件的信息,对不符合条件的信息则返回NULL

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

     结果:

SELECT 
a.name,
a.address,
b.math,
b.english 
FROM 
tb_demo065 AS A 
LEFT OUTER JOIN 
tb_demo065_tel AS b 
ON a.id=b.id

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

Warning: Null value is eliminated by an aggregate or other SET operation.

(2)RIGHT OUTER JOIN表示表之间通过右连接方式相互连接,也可简写成RIGHT JOIN,它是以右侧的表为基准故称右连接,右侧表中所有信息将被全部输出,而左侧表信息则只会输出符合条件的信息,对不符合条件的信息则返回NULL

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

(1124 行受影响)

SELECT 
a.name,
a.address,
b.math,
b.english 
FROM 
tb_demo065 AS A 
RIGHT OUTER JOIN 
tb_demo065_tel AS b 
ON a.id=b.id

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

     这说明:ALL包含所有组和结果集,甚至包含那些其中任何行都不满足 WHERE 子句指定的搜索条件的组和结果集。如果指定了 ALL,将对组中不满足搜索条件的汇总列返回空值。

十六利用IN或NOTIN关键字限定范围

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

 

SELECT * 
FROM 
tb_demo083 
WHERE code IN(SELECT code FROM tb_demo083 
WHERE code BETWEEN '$_POST[text1]' AND '$_POST[text2]')

例如:

7.2:HAVING

利用IN可指定在范围内查询,若要求在某范围外查询可以用NOT IN代替它

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

     指定组或聚合的搜索条件。HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句,则 HAVING 的行为与 WHERE 子句一样。

十七由IN引入的关联子查询

 

     相当于对GROUP之前的查询内容进行再一次的条件检索。

SELECT * 
FROM 
tb_demo083 
WHERE code 
IN(SELECT code FROM tb_demo083 WHERE code = '$_POST[text]')

8.内外连接查询

     以下示例使用简单 HAVING 子句从 SalesOrderDetail 表中检索超过 $100000.00 的每个 SalesOrderID 的总计。

十八利用HAVING语句过滤分组数据

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

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
--where ModifiedDate between '7/28/2002' and '7/29/2002'
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
--HAVING SalesOrderID = 43875
--HAVING ModifiedDate between '7/28/2002' and '7/29/2002' --error
ORDER BY SalesOrderID ;

HAVING子句用于指定组或聚合的搜索条件,HAVING通常与GROUP BY 语句一起使用,如果SQL语句中不含GROUP BY子句,则HAVING的行为与WHERE子句一样.

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

 

SELECT 
name,
math 
FROM 
tb_demo083 
GROUP BY id 
HAVING math > '95'

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

8:SELECT字句技术

 

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

        SELECT字句技术有很多,除了最简单的拼接等,下面介绍个人认为最有用的。

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

 

 参数说明:

8.1:使用DISTINCT消除重复值

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

select * FROM HumanResources.Employee

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

      结果:(290 行受影响)
select DISTINCT HireDate FROM HumanResources.Employee

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

      结果:(164 行受影响)

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 存在关系   

8.2:返回拼接的结果

 --------------------------------------------------    
 1) 内连接  

DECLARE @Shifts varchar(20)
SET @Shifts = ''
SELECT @Shifts = @Shifts s.Name ',' FROM HumanResources.Shift s
SELECT @Shifts

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

      返回的结果为:Day,Evening,Night,

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

 


8.3使用INTO字句

 3) 右连接   

    INTO字句用来创建新表(对我来说便是备份数据)。

  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   

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

备注:内连接与外连接区别?

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

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

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

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

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

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

 

////end

SELECT CustomerID, Name, SalesPersonID, Demographics
INTO  Store_Archive
FROM Sales.Store

    结果:(701 行受影响)

    说明,1:创建了新表Store_Archive,2:有701行数据被复制到了Store_Archive。

    当然,如果你仅仅想创建新表,而不想复制任何数据,有一个简洁的方法是:

SELECT CustomerID, Name, SalesPersonID, Demographics
INTO  Store_Archive
FROM Sales.Store
WHERE 1=0

 

9:子查询

    子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

    联接总是可以表示为子查询。子查询经常(但不总是)可以表示为联接。这是因为联接是对称的:无论以何种顺序联接表 A 和 B,都将得到相同的结果。而对子查询来说,情况则并非如此。

    使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于,联接使您可以在结果中显示多个表中的列。例如,如果要在结果中包括产品子类别的名称,则必须使用联接版本。

 

9.1:子查询类型

     可以在许多位置指定子查询(必须全部掌握):

  • 使用别名。有关详细信息,请参阅使用别名的子查询.aspx)。
  • 使用 IN 或 NOT IN。有关详细信息,请参阅使用 IN 的子查询.aspx)和使用 NOT IN 的子查询.aspx)。
  • 在 UPDATE、DELETE 和 INSERT 语句中。有关详细信息,请参阅 UPDATE、DELETE 和 INSERT 语句中的子查询.aspx)。
  • 使用比较运算符。有关详细信息,请参阅使用比较运算符的子查询.aspx)。
  • 使用 ANY、SOME 或 ALL。有关详细信息,请参阅用 ANY、SOME 或 ALL 修改的比较运算符.aspx)。
  • 使用 EXISTS 或 NOT EXISTS。有关详细信息,请参阅使用 EXISTS 的子查询.aspx)和使用 NOT EXISTS 的子查询.aspx)。
  • 代替表达式。有关详细信息,请参阅用于替代表达式的子查询.aspx)。

9.2:代替表达式的查询

     必须着重说说代替表达式的子查询。在 Transact-SQL 中,除了在 ORDER BY 列表中以外,在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何能够使用表达式的地方都可以用子查询替代。

     以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。

USE AdventureWorks;
GO
SELECT Name, ListPrice, 
(SELECT AVG(ListPrice) FROM Production.Product) AS Average, 
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1

 

9.3:多层嵌套

     子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。

     以下查询将查找作为销售人员的雇员的姓名。

Use AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact
WHERE ContactID IN
    (SELECT ContactID
     FROM HumanResources.Employee
     WHERE EmployeeID IN
        (SELECT SalesPersonID
         FROM Sales.SalesPerson)

 

10:比较使用 EXISTS 和 IN 的查询

    以下示例比较了两个语义等同的查询。第一个查询使用 EXISTS,第二个查询使用 IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT * 
 FROM HumanResources.Employee AS b
 WHERE a.ContactId = b.ContactID
 AND a.LastName = 'Johnson');
GO

    下面的查询使用 IN

USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
 FROM HumanResources.Employee AS b
 WHERE a.ContactId = b.ContactID
 AND a.LastName = 'Johnson');
GO

    以下是其中任一查询的结果集。

FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)

 

11:联接

   通过联接,可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。

   联接条件中用到的列不必具有相同的名称或相同的数据类型。但如果数据类型不相同,则必须兼容,或者是可由 SQL Server 进行隐式转换的类型。

     联接可分为以下几类:

  • 内部联接(典型的联接运算,使用类似于 = 或 <> 的比较运算符)。内部联接包括同等联接和自然联接。
    内部联接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。例如,检索 studentscourses 表中学生标识号相同的所有行。
  • 外部联接。外部联接可以是左向外部联接、右向外部联接或完整外部联接。
    在 FROM 子句中可以用下列某一组关键字来指定外部联接:
    • LEFT JOIN 或 LEFT OUTER JOIN。
      左向外部联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行,则在关联的结果集行中,来自右表的所有选择列表列均为空值。
    • RIGHT JOIN 或 RIGHT OUTER JOIN
      右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行,则将为左表返回空值。
    • FULL JOIN 或 FULL OUTER JOIN
      完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时,另一个表的选择列表列将包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
  • 交叉联接
    交叉联接将返回左表中的所有行。左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。

11.1:使用衍生表

     衍生表是指在FROM字句中作为表的SELECT语句。

SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN ( SELECT SalesOrderID
    FROM Sales.SalesOrderDetail
    WHERE UnitPrice BETWEEN 1000 AND 2000) d ON
    s.SalesOrderID = d.SalesOrderID

 

澳门新萄京官方网站,11.2:UNION

   将两个或更多查询的结果合并为单个结果集,该结果集包含联合查询中的所有查询的全部行。

     下面列出了使用 UNION 合并两个查询结果集的基本规则:

  • 所有查询中的列数和列的顺序必须相同。
  • 数据类型必须兼容。

SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name ;

 

12:TABLESAMPLE

     TABLESAMPLE 子句将从 FROM 子句中的表返回的行数限制到样本数或行数的某一百分比。例如:

SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (10 PERCENT) ;

SELECT FirstName, LastName FROM Person.Person TABLESAMPLE (100 ROWS) ;

 

13:公共表表达式common_table_expression

     指定临时命名的结果集,这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询,并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中,作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*)
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:mysql数据库操作,MySQL多表

关键词: