[Mysql]数据查询语言(DQL)

select基本查询

语法形式

select [all | distinct] 字段或表达式列表 [from子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句];

select

作用是从“数据源”中，找出（取出）一定的数据，并作为该语句的返回结果（数据集）
数据源：通常，数据源就是“表”。

1. 可以没有数据源，而是使用“直接数据”（或函数执行结果）
2. 可以给其加上字段（别）名
3. 如果有表（from子句），则对字段同样可以进行“计算”
4. 可以对表中的字段和“直接值”（或函数返回值）同时并列“取出”
5. 可以使用“表名.字段名”的形式来指定某个表的某个字段（通常用于多表查询）
   不可以没有from子句而使用表的字段

[all | distinct]

用于设定所select出来的数据是否允许出现重复行（完全相同的数据行）
all：允许出现——默认不写就是All（允许的）
distinct：不允许出现——就是所谓的“消除重复行”

[from子句]

指定数据的来源，其实就是“表”，可以是一个表名，也可以是多个表——多表查询

[where子句]

一个概念：where子句，相当于php或js中的if条件语句，其最终结果就是布尔值

where中可用的运算符：
算术运算符： + - * / %
比较运算符： > >= < <= =（等于） <>（不等于）
==（等于，mysql扩展），!=（不等于，mysql扩展）
逻辑运算符： and（与） or（或） not（非）

XXshige是个ziduanm字段名

布尔值的判断方式：XX is true/false
实际应用中，布尔值判断很少用，因为可以直接使用数学大小。

空值的判断方式：XX is null/ not null

between语法：XX between value1 and value2;
含义：字段XX的值在value1和value2之间（含）
相当于：XX >=value1 and XX<=value2;

in语法：XX in (value1，value2，.......);
含义：XX等于其中所列出的任何一个值都算成立，
相当于：XX = value1 or XX = value2 or XX = value2;
注意：其中的value1通常是“直接值”，但也可以是后面要学习的“查询结果值”

like语法（模糊查找）：XX　like ‘要查找字符’;
说明：
1. like语法（模糊查找）用于对字符类型的字段进行字符匹配查找
2. 要查找的字符中，有2个特殊含义的字符：

特殊字符	含义
%	代表任意个数的任意字符
_	代表1个的任意字符

3. 字符，都是指现实中可见的一个“符号”，而不是字节。
4. 实际应用中的模糊查找，通常都是这样：like ‘%关键字%’

如果要查找的字符中包含“%”或“_”，“’”，则只要对他们进行转义就可以：

like  ‘%ab\%cd%’        // 包含 ab%cd 字符的字符 
like   ‘\_ab%’          // _ab开头的字符 
like   ‘%ab\’cd%’       //包含 ab’cd 字符的字符 

where子句前面必须有from子句。虽然他们2者都可以省略，但有from可以没有where，而有where必须有from。

[group by 分组子句]

group by 字段1 排序方式1，字段2 排序方式2， .....
通常都只进行一个字段的分组。

分组: 将数据以某个字段的值为“依据”，分到不同的“组别”里。
分组的结果通常：
1. 数据结果只能是“组”——没有数据本身的个体
2. 数据结果就可能“丢失”很多特性，比如没有性别，身高，姓名，等等
3. 实际上，结果中通常只剩下“组”作为整体的信息：
首先是该组的本身依据值，
另外，这几个可能的值：组内成员的个数，组内某些字段的最大值，最小值，平均值，总和值。
其他字段，通常就不能用了。
4. 如果是2个字段或以上分组，则其实是相当于对前一分组的组内，再进行后一依据的分组。

在分组查询中，基本都依赖于一下几个函数（聚合函数，统计函数）：
count(): 统计一组中的数量，通常用“”做参数
max(字段名)：获取该字段中在该组中的最大值。
min(字段名)：获取该字段中在该组中的最小值。
sum(字段名)：获取该字段中在该组中的总和。
avg(字段名)：获取该字段中在该组中的平均值。

[having子句

having子句其实概念跟where子句完全一样：
where是针对表的字段的值进行“条件判断”
having是只针对groupby之后的“组”数据进行条件判断
count(字段名)>10， 或 max(price) > 2000
当然，通常也可以使用select中的有效的字段别名，比如：
select count(*) as f1 , max(f1) as f2 from tab1 group by f3 having f1 > 5 and f2 < 1000;

[orderby子句]

order by 排序字段1 [排序方式]， 排序字段2 [排序方式]， .....

对前面取得的数据（含from子句，where子句，group子句，having子句的所有结果）来指定按某个字段的大小进行排列（排序）
排序方法：
- 正序： ASC（默认值），可以省略
- 倒序： DESC
多个字段排序:先按第一个字段排，再按第二个字段排

[limit子句]

limit [起始行号 start]， 要取出的行总数num
将前面取得的数据并前面排好之后（如果有），对之指定取得“局部连续的若干条”数据。
[起始行号 start]：默认行号0
要取得的行数：if 要取出的行总数num > end，则就只取到end
主要用于网页上最常见的一个需求：分页

每页显示的条数，$pageSize = 50;
取第1页数据：select * from table limit 0, $pageSize;
取第$n页数据：select * from table limit ($n-1)*$pageSize, $pageSize;

Join连接查询

连接就是指两个或2个以上的表（数据源）“连接起来成为一个数据源”。多个表的每一行 “横向对接”后。成为“更多字段”的一个新表
注意：连接之后，并非形成了一个新的数据表，而只是一种“内存形态”

语法形式

from table1 [joinType] join table2 [on joinCondition]

连接形式

交叉连接

将两个表不设定任何条件的连接结果
from table1 [cross] join table2; //交叉连接只是没有on条件而已
cross这个词也可以省略，还可以使用inner这个词代替

内连接

找出（过滤）在交叉连接的结果表中的表1的字段1的值等于表2的字段2的值的那些行
from 表1 [inner] join 表2 on 表1.字段1=表2.字段2
内连接的结果都是“有意义”的数据，交叉连接有些数据并无意义

左外连接

from 表1 left [outer] join 表2 on 表1.字段1=表2.字段2
- left是关键字
- 连接条件跟内连接一样
- 内连接的结果基础上，加上左边表中所有不符合连接条件的数据，相应本应放右边表的字段的位置就自动补为“null”值

右外连接

from 表1 right [outer] join 表2 on 表1.字段1=表2.字段2
- 和左外连接一致
- 在内连接的结果基础上，加上右边表中所有不符合连接条件的数据，相应本应放左边表的字段的位置就自动补为“null”值

全外连接

from 表1 full [outer] join 表2 on 表1.字段1=表2.字段2
- 其实是左右连接的“并集”（消除重复项）
- mysql不支持，其他数据库存在

子查询

多个查询语句嵌套，前一个select称为“主查询”，后面的select称为“子查询”
selelct 字段或表达式或(子查询1) [as 别名] from 表名或(子查询2) where 字段或表达式或(子查询3) 的条件判断
- 子查询1应该是一个“数据结果”。
- 子查询2可以是“任意结果”，此位置的查询结果，通常作为数据源，可以给一个别名
- 子查询3可以是一个数据或一列数据甚至是一行数据

分类

结果

• 表子查询 ： 一个子查询返回的结果理论上是“多行多列”的时候。
  此时可以当做一个“表”来使用，通常是放在from后面。
• 行字查询 ： 一个子查询返回的结果理论上是“一行多列”的时候。
  此时可以当做一个“行”来使用，通常放在“行比较语法”中。
• 列子查询 ： 一个子查询返回的结果理论上是“多行一列”的时候。
  此时可以当做“多个值”使用，类似：(5, 17, 8, 22)。
• 标量子查询：一个子查询返回的结果理论上是“一行一列”的时候。 此时可以当做“一个值”使用，类似这种：
  select 2 as you;或select ...where a = 524，或select ... where b > 520;

使用场合

• 作为主查询的结果数据：
  select c1,(select f1 from tab2) as f11 from tab1; //这里子查询应该只有一个数据
• 作为主查询的条件数据：
  select c1 from tab1 where c1 in (select f1 from tab2);//这里子查询可以是多个数据
• 作为主查询的来源数据：
  select c1 from (select f1 as c1, f2 from tab2) as t2;//这里子查询可以是任意查询结果

常见子查询

比较运算符中使用子查询

判断该操作数（字段）的值是否满足该比较运算符所设定的比较结果
select * from product where price >（select avg(price) as avg_price from product）;

in子查询

where 字段值 in (列子查询）
表示该操作数（字段值） == 该子查询的其中任意一个只，就算满足条件
eg:
select * from product where protype_id in (
select protype_id from product_type where protype_name like '%电%'

any/some子查询

where 字段值 比较运算符 any/some (列子查询)
- 比较运算符就是常规的> < >= <= = <>　
- 列子查询也可以是标量子查询，都表示“若干个数据值”

where 操作数 = any (列子查询);
则其完全相当于：
where 操作数 in (列子查询);

all子查询

where 字段名 比较运算符 all (列子查询);
- like any子查询

表示该操作数的值必须跟列子查询的所有值都满足给定的比较运算，才算满足了条件。
举例：找出产品表中的价格最高的产品
分析：最高价的产品的价格会大于等于所有产品价格。
select * from product where price >= all(
select price from product );
select * from product where price = (
select max(price) from product );


exists/not exists的子查询

where exists/not exists (子查询); 子查询有结果数据(无论什么数据，只要大于等于１行)，则就是true，否则为false

举例：找出具有在售商品的那些类别

select * from product_type where exists(
select * from product where product.protype_id = product_type.protype_id
);

实际上，这种exists（或not exists）子查询，如果涉及到2个表（或以上），
其内部其实会自动进行“连接查询”，且其逻辑过程较为负责，而且还不明确，通常认为属于效率较低的子查询，尽量少用。

Union联合查询

联合查询的关键字是： union
连接查询的关键字是： join
但，在mysql手册中， join这个连接查询，往往都翻译为“联合查询”
但在绝大多数的中文书籍和文章中，join被翻译为“连接查询”

联合查询就是将两个select语句的查询结果“层叠”到一起成为一个“大结果”。
两个查询结果的能够进行“联合”的先觉条件是：结果字段数相等。

select 语句1 union [ALL | DISTINCT] select 语句2;

• 两个select语句的输出段（结果字段）一样数目一样，应用中通常类型一样才有意义。
• 结果集中的字段以第一个select语句的字段为准。
• 第一个select语句的字段可以做别名，但如果做别名，则后续的where，group，order等子句应该用该别名。
• 联合查询默认是会消除重复项的（DISTINCT）
• 如果要对整个联合结果进行排序或limit，则应该对各自的select语句加括号：
  （select 语句1）union（select 语句2）order by ... limit ...;