初学者，继续学习SQL-Day12

表关联： 从多个表中获取数据;

获得的是两个表关联字段值的交集： 速度慢

内连接：两个表关联字段值的交集

表1 [inner] join 表2 on 关联条件;

select * from emp inner join dep on dep_id = dep.id;

外连接： 左关联，右关联

左关联：语法： 表1 left  join 表2 on 关联条件;

获取左表(join 前边的表)的全部(记录)数据,右表(join 后边的表)做补充(能关联就显示数据，不能关联就为null);

右关联：语法： 表1 right  join 表2 on 关联条件;

获取右表(join 后边的表)的全部(记录)数据,

左表(join 前边的表)做补充(能关联就显示数据，不能关联就为null);

先过滤后关联 (1. 子查询，2.inner join 时 过滤条件到on) ;

Where Having on 区别

where     从数据源过滤数据

having   对统计后的数据过滤

on 建立关联条件 ，inner join 时可加过滤条件

Count(*),count(1),count(字段名）区别

Count(*),count(1):统计查询结果的记录数

count(字段名):对字段的非空单元格计数

      

Group by 使用的注意事项

group by 后详情被覆盖，select 后只能是分组字段名或聚合函数

自定义函数：

系统自带函数：

curdate(),concat("a","b"),year(curdate()); #通用性强

自定义函数：补充系统函数的补足（含有业务特性），减少代码的重复度，提高开发效率

语法：

create function 函数名([参数名1 参数类型1,参数名2 参数类型2,....])

returns 返回值类型 #函数有且仅有一个返回值

begin

       函数体

       return 要返回值;

end;

调用： select 函数名([实际参数值1,实际参数值2,...])

如：用自定义函数add10实现为指定数加10，并调用为books表每本书加10元;

create function add10(shu float)

returns float

begin

       #定义个变量，用来装计算结果

       declare he float;

    #把计算结果放到变量中

       set he = shu+10;

    #把计算结果返回给调用者

       return he;

end;

执行： 从create 到 end 全选，点第一个按钮执行；

#把创建自定义函数开关打开

set GLOBAL log_bin_trust_function_creators=true;

调用

select add10(10.5);

其他指令：

删除自定义函数： drop function 函数名

查看指定数据库里自定义函数列表：;

show function status where Db="数据库名";

查看指定自定函数的代码;

show create function 自定函数名; #create function 的内容

日期计算

给某个日期加减指定的时间单位

date_add(日期,interval n 时间单位)

date_sub(日期,interval n 时间单位)

简写 +代替date_add, -代替date_sub

日期 +|- interval n 时间单位

存储过程：把业务步骤封装简化操作

语法：

       定义:

              create procedure 存储过程名([参数1 类型1,参数2 类型2,....])

              begin

                     过程体

              end;

调用：

 call 存储过程名([实际参数])

删除：

drop procedure 存储过程名;

查看：

show procedure status where Db="exercise"; #查看指定数据库有哪些存储过程

show create procedure get_stu; #查看指定存储过程的创建代码

自定义函数和存储过程的区别：

  1.作用：自定义函数封装通用性小功能。存储过程封装业务逻辑(步骤，过程)。

  2.参数：自定义函数只有普通参数。

存储过程有三类参数 in：调用时向过程内传递值，默认值，out 调用后过程向外传递值

inout:可入可出。

  3.返回值：自定义函数有且仅有一个返回值，存储过程没有返回值select输出。

  4.调用：自定义函数用select , 存储过程用call。

局部变量赋值：自定义函数和存储过程内declare 声明的变量，自定义函数和存储过程的定义时的参数名。

作用范围：只能在定义的自定义函数和存储过程内使用。

SET 变量名=具体的值

select 字段名,[字段名2,..] into 变量名,[变量名2,..] #查询结果赋值给变量

用户变量：

定义：set @变量名=具体的值;

set @test=CURRENT_DATE() ;

              select @test;

窗口函数：解决统计与详情并存的问题;

语法分析函数 + over(窗口子句)

分析函数：对数据如何处理

1.统计 sum,max,min,avg,count

窗口子句：划分分析函数的使用范围

over():所有查询结果为一个数据窗口

over(partition by 字段名):根据指定字段的不同取值划分数据窗口

over(rows between 开始位置 and 结束位置):根据指定行的位置划分数据窗口

开始位置|结束位置：

当前行： current ROW

第一行： unbounded preceding

最后一行：unbounded following

当前行的前n 行：  n preceding

当前行的后n 行： n following

可以用order by 对窗口内数据进行排序

注意：partition by  order by  rows between 3个关键字可以少任何一组，但是位置不能错。

         

聚合函数：把查询结果显示为一行

select max(price) from books;

select press,max(price) from books

group by press;

窗口函数：为每个查询行返回一个结果;

select *, max(price) over()  from books;

窗口函数：解决统计与详情并存的问题;

语法: 分析函数 + over(窗口子句)

分析函数：对数据如何处理

1.统计 sum,max,min,avg,count

2.偏移 lead(字段名,n) #把当前行后n行的字段值往当前行拉，

lag(字段名,n) #把当前行前n行的字段值往当前行拉，

first_value(字段名) #把第一行的字段值往当前行拉

3.排序(打名次)

row_number():给数据表行号

rank():根据orderby 的字段值，有重复，有空位的打名次

dense_rank():根据orderby 的字段值，有重复，无空位的打名次

窗口子句：划分分析函数的使用范围

over():所有查询结果为一个数据窗口

over(partition by 字段名):根据指定字段的不同取值划分数据窗口

over(rows between 开始位置 and 结束位置):根据指定行的位置划分数据窗口

开始位置|结束位置：

前行： current ROW

第一行： unbounded preceding

最后一行：unbounded following

当前行的前n 行：  n preceding

当前行的后n 行： n following       

可以用order by 对窗口内数据进行排序，

注意：partition by  order by  rows between 3个关键字可以少任何一组，但是位置不能错.

ntile(n):根据orderby 的字段值等频划分为n组;

重启select：

1. Group by  和  窗口函数 不能存在一层

2. 用select 计算出来的列进行过滤时,要重启动select;