高级数据查询

Mysql 数据库的高级查询

一般查询功能的扩展

（一）使用limit限制结果集

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)：为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
语法
```
select * from table limit
```
m,n其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。

select * from tablename limit 2,4

即取出第3条至第6条，4条记录

（二）使用case函数

1.目的：达到分情况显示不同类型的数据的目的。case函数是一种多分支的表达式，计算条件列表并返回多个可能的结果表达式中的一个。

2.读取方式：

计算表达式从上到下的顺序对每一个when子句的简单表达式进行计算。当一个条件被满足后，其他条件自动被忽略。选择方式与c语言中的if语句类似

3.类型

（1）简单case函数：when后面是简单表达式

select fname, (

case fname

when 'tom' then '好孩子'

when 'jack' then '坏孩子'

else '普通孩子'

end

) as isgoodchild

from T_person

（2）搜索case函数：when后面是布尔表达式

select 商品ID ,case 
when 单价>=18.6000 then '高档'
when 单价<18.6000 then  '低档'
ELSE '无'
end '档次'
from `售货单明细`

（三）将数据结果保存到新表中

1.当使用select语句查询语句时，产生的结果是保存在内存中的。如果需要保存在一个表中，直接在select语句中使用into语句。

2.新表的类型

永久表：物理创建的表，储存在磁盘上

graph LR
查询结果-->永久表

临时表：生存期是有限的，根据其范围可分为局部临时表（#temp表）和全局临时表（##temp表）。临时表的生存期与创建临时表的用户的连接生存期有关。比如局部临时表的生存期与创建此局部临时表的用户的连接生存期相同，它只能是在当前连接中使用。

graph LR
查询结果-->临时表

查询结果的并、交、差运算

（一）注意点

1.select的列的个数必须是相同的，而且对应列的语义是相同的。

2.各查询语句中每个列的数据类型必须与其他查询中对应的数据类型是隐形兼容的（类似c语言中数据类型的隐式转换一样）

3.合并后的结果采用第一个select语句的列标题

4.如果使用order by 排序应该写在最后一个查询语句之后。排序的依据列是第一个查询语句出现的列名。

（二）关键字及意义

关键字后面加all表示在结果集中包含所有查询语句产生的全部记录，包含重复的记录。如果没有指定all，则系统默认是删除合并后结果集中重复记录。

并：union

将两个或多个查询语句的结果集合并成一个结果集

交：intersect

返回两个结果集中各个列的值均相同的记录，并将这些记录构成交运算的结果。

差：except

返回第一个集合中有但是第二个集合中没有的数据。

select address from table_customer
union/intersect/except
select address from table_saleperson

其他一些查询功能

（一）开窗函数（用于分区或者分组计算的函数）

结合over子句对组内的数据进行编号，并进行求和，计算平均值等统计。sum,avg,row_number等都可以称为开窗函数。开窗函数必须放在Over之前

1.与聚合函数结合使用

select cno,sum(credit) 
over (pratition by semester) as total

2.与排名函数结合使用

（1）四个排名函数的用法

rank():并不一定返回连续整数。比如两个人的成绩相同，那么返回的名次时并列的

dense_rank():返回的是一个连续的整数值。比如两个人的成绩相同，那么返回的名次一个高一个低。

ntile():将有序分区中的行划分到指定数目的组中，每个组有一个编号，编号从1开始。对于每一行，Ntile()函数将返回此行所属的组的编号。

row_number()：返回结果集中每个分区内行的序列号，每个分区的第一行从一开始。

（二）公用表达式（CTE）

1.将查询语句产生的结果集指定一个临时命名的名字，这些命名的结果集就称为公用表达式，它可以被多次引用。可以包括对自身的引用，叫做递归公用表达式。

2.例子

with buyCount(CardId,Count)
as (select * 
  from table_saleBill
    group by cardId)
    select cardId ,counts from buyCount
     order by counts

秒客网