Mysql学习笔记--高级

DQL高级查询

1， [掌握]limit分页查询

语法
select 字段名1,字段名2,… from 表名 limit M, N;
M: 整数，表示从第几条索引开始，计算方式:(当前页-1) * 每页显示条数;
N: 整数，表示查询多少条数据.
例如：select * from product limit 5,5;

2, [掌握]分组查询

语法：select 字段名1,字段名2,… from 表名 group by 分组字段名 [having 分 组条件];
说明：
（1）分组字段名：表示按照指定字段的值进行分组；
（2）group by的作用是实现分组，默认可以实现去重操作；
（3）having分组条件是用来过滤分组后的数据。
例如：select gender from person group by gender;

2.1，使用group by + 聚合函数

语法：select 字段名1,字段名2,聚合函数(…) from 表名 group by 分组字段名;
例如：select gender,count(*) gd_count from person group by gender;

2.2，group by + having的使用

语法：select 字段名1,字段名2,… from 表名 group by 分组字段名 having 分组 条件;
注意：having子句放在结尾处。
例如：select gender,count() as gd_count from person group by gender having count()>2; # having

3，外键和关系

1，添加外键约束的语法
语法：

create table 表名(
 ... 
 字段名 数据类型(长度) [约束], 
 foreign key (字段名) references 另外表名(主键列名)
  ) engine = InnoDB default charset utf8;

注意：（1）在设置外键约束时，需要使用到foreign key关键字；
（2）通常情况下，建议设定外键约束放在定义所有字段的结尾处。

2，表与表之间的关系
（1）一对一关系(1:1)
（2）一对多关系(1:N) [常见] （3）多对多关系(M:N) [常见]
3，通过外部添加外键
alter table 表A add constraint xxxfk foreign key (外键字段名) references 另外表名(主键字段名);
注意：要添加外键约束时，先记得新增一个字段，然后再给这个字段设定外键约束条件。
4，当数据表之间产生关联后，需要注意：
（1）不能给表B（外键）添加一个表A不存在的主键字段值；
（2）不能删除表B（外键）已使用表A存在的主键字段值。

6，子查询

子查询指的是将一条select语句的结果，作为另一条select语句的一部分（如：查询
条件、查询结果、表等），来进行数据查询。
语法：

select *|字段名1,字段名2,.. from 表名 where 字段名 = (另外select语句 的结果);

注意：当作表处理时，要记得设定别名
2，建议把子查询的语句使用（）引起来
例如：

# 仅查询分类为"化妆品"下的所有商品名称。 select
pname 
from product 
where category_id=( 
select cid 
from category where cname='化妆品' );

4，多表查询

多表查询，也可称为连接查询，可用于实现多个数据表之间的数据查询。
注意：当查询的字段数据来自不同的数据表时，就可以使用多表查询来完成。
多表查询可以分为4类：
（1）交叉连接查询
（2）内连接查询
（3）外连接查询
（4）自连接查询

4.1交叉查询

通常地，交叉连接查询的结果是：两个数据表的并集，也称为笛卡尔积

笛卡尔积是指在数学中，两个集合X和Y的笛卡尔乘积，又称直积。 表示为X × Y，第一个对象是X的成员，而第二个对象是Y的所有可能有序对的其中一个 成员。
语法：

select 字段名 from A表名, B表名;
或者
select 字段名 from A表名 cross join B表名;

例如
# 注意: 一般的, 对于连接查询, 会给表名设定别名 select * from table_category c cross join table_product p;

4.2 内连接查询

内连接查询指的是查询两个数据表中，符合条件的共有记录，即交集。
（1）作为了解，隐式内连接是指没有inner join关键字，而条件直接使用where来指
定，
语法：select 字段名 from A表名, B表名 where 条件;
（2）显式内连接是指需要使用inner join … on语句，可以省略inner，语法：

select 字段名 from A表名 别名 inner join B表名 别名 on 条件 [where 查询条件];

select 字段名 from A表名 别名 join B表名 别名 on 条件 [where 查询条 件];

例如：
select * from table_category tc inner join table_product tp on tc.cid = tp.category_id;

select cid from table_category tc join table_product tp on tc.cid = tp.category_id;

4.3左连接查询

左外连接也简称为左连接，是以左表为主并根据条件来查询右表数据。（如果根据条件查询，当右表数据不存在时，则会使用null值来填充。）
左外连接查询需要使用left join关键字，语法：

select 字段名 from A表名 别名 left [outer] join B表名 别名 on 条件;

说明：（1）left join指的是左外连接查询关键字；
（2）on后面的是连接查询条件；
（3）A表名是指左表，B表名是指右表。

4.4右连接查询

右外连接也简称为右连接，以右表为主并根据条件来查询左表数据。（如果根据条件查询，当左表数据不存在时，则会使用null填充值）
右外连接查询需要使用right join关键字，语法：

select 字段名 from A表名 别名 right [outer] join B表名 别名 on 条件;

说明：
（1）right join指的是右外连接查询关键字；
（2）on后面的是连接查询条件；
（3）A表名是指左表，B表名是指右表。

4.5左右连接的区别

（1）当左表有数据为null时，左外连接查询的结果中，右表的数据结果都为null值；
（2）当右表有数据为null时，右外连接查询的结果中，左表的数据结果都为null值。

5，自连接查询

自连接查询，顾名思义，就是数据表自己和自己连接，然后进行数据比较。
其实，自连接查询指的是：左表和右表都是同一个表，根据连接查询条件来查询两个表中的数据。（在进行连接查询操作时，必须为数据表定义别名！）

窗口函数

1，窗口函数介绍

窗口函数，也称为OLAP（OnLine Anallytical Processing），意思是对数据库数据
进行实时分析处理。
也就是说窗口函数就是：为了实现实时分析处理，而添加的标准SQL功能。
窗口函数是类似于可以返回聚合值的函数，例如sum()、count()、max()等。
但又与普通的聚合函数不同，因为它不会对结果进行分组，这使得输出与输入的行数
相同。
语法：

select 窗口函数() over([partition by xx order by xx]) from 表名;

注意：（1）聚合功能：窗口函数可以使用聚合函数sum()、count()、avg()之类的计算统计功能；
（2）partition by：只需要把他看成是group by子句，但在窗口函数中，要写成partition by；
（3）order by：order by和普通查询语句中的order by没什么不同，但要注意输出顺序。

2，基本语法与优点

优点：
①窗口函数更易于使用，语法也简单。
②使用窗口函数比使用替代方法要快得多，当你处理成百上千个、千兆字节的数据时，这非常有用。
计算结果公式为：
最终的数据记录结果 = 每行的数据记录 + 窗口函数值
基本语法：

窗口函数名(字段名) over([partition by 分区字段名] [order by 分组内排 序])

说明：
（1）窗口函数名可以是聚合函数，例如sum()、count()、avg()等，也可以是分析函数[后面介绍]；
（2）聚合函数有count()、sum()、avg()、min()、max()；
（3）分析函数有row_number、rank、dense_rank等。

3，窗口函数over()子句

（1）over()意思是所有的数据都在窗口中；
（2）这里的SQL语句并不复杂，主要是看over()是在谁的前面部分，就代表它的含义。

selectlast_name, first_name, years_worked, (years_worked - avg(years_worked) over ()) as def_years
from employee

3.1over()与where配合使用

注意：窗口函数是在where条件语句后再执行。

# 窗口函数 
select* 
from 
(select *,avg(salary) over () avg_sy 
from employee) temp_emp
 where temp_emp.salary>avg_sy;

3.2over(partition by 字段)的使用

partition by可用于在窗口函数中的分组处理。

窗口函数名(字段名) over(partition by 字段名1[,字段名2,...]) [as 别名]

说明：
（1）partition by的作用于group by类似，就是将数据按照传入的字段进行分
组；
（2）partition by叫做分析函数；
（3）关键字执行顺序的优先级是：from > where > group by > having > order
by > partition by； （4）注意：partition by应用在所有关键字之后，可以简单理解为就是在执行完
select查询的整个语句后，在所得结果集的基础上再进行partition by分组处
理。

3.3voer(order by)字段的使用

ranking函数名() over([partition by 字段名1] order by 字段名 [asc | desc]) [as 别名]

说明：
（1）ranking函数名()可以是row_number()、rank()、dense_rank()这三个函数中的某一个，都用于返回结果集的分组内每行排名；
（2）row_number()不管排名是否有相同的，都会加序号，且按照1、2、 3、…、n顺序；
（3）rank()是当排名相同时，名次一样，且同一排名有几个，后面排名就会跳过几次；
（4）dense_rank()是当排名相同的名次一样，且后面名次不跳过；
（5）order by就是将数据按照传入的字段从小到大或从大到小进行排序。

最后总结的xmid图片