mysql 学习记录

mysql常用方法 总结：

---limit---
1、当 limit后面跟一个参数的时候，该参数表示要取的数据的数量
例如 select* from user limit 3 表示直接取前三条数据
2、当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如
select * from user limit 1,3;
3、当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。
例如select * from user limit 3 offset 1;表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
LIMIT 有一个参数和两个参数的情况：一个参数表示限制返回记录的条数,两个参数，第一个表示数据偏移量，第二个表示返回记录的条数

----日期比较----
select * from table_name where date(start_time) between '2022-03-21' and '2022-03-22'
2. 时间比较
SELECT * FROM table_name WHERE start_time <= '2022-03-22 00:00:00' and start_time >= '2022-03-21 00:00:00'
select  sysdate() + interval  1 day ,now(); 
now()返回一个常量，语句开始时候的值，sysdate()返回当前时间，执行时的时间;
 日期筛选： date_add(sysdate(),interval -1 month) / date_add(now(),interval -1 day) ;
 
-----case用法-----
 case key 
 when 条件 then '结果'
 when 条件 then '结果'
 when 条件 then '结果'
 else '结果'
 end
 
 
----if 用法----
if(expr1,expr2,expr3) 
expr1为条件返回一个bool值，成立则返回expr2,否则返回 expr3
例如：
if(sex=0,'男','女') 
类似于C# 三元表达式  expr1==true?"true":"false"


 --创建临时表 --
create temporary table -tableName as (select * from xxx);
drop temporary table if exists -tableName;

-- 链接字符串--
相当于 string.join(",",arr)
select GROUP_CONCAT(a.Name separator ',') as PartsName from tableName

 .cast(Id as char) as id 转换字段类型。
 .concat('a','b','c') 拼接字符串print abc 。
 .GROUP_CONCAT(a.Name separator ',')  分组后的组合，指定分割符号的。

-- 存储过程 --
drop  procedure if exists  my_procedure; -- 删除存储过程，清空环境
delimiter // -- 执行相当于 ;
create procedure  my_procedure (time datetime) -- 创建存储过程，相当于函数，参数+类型
    begin -- 开启一个事务
        select * from `log`; -- 具体逻辑
    end -- 事务结束
call  my_procedure(now());-- 调用存储过程，并传递参数
drop  procedure if exists  my_procedure; -- 删除存储过程，如果是临时的话需要删除

-- 事务 trancaction
begin 或者 START TRANSACTION 开启事务 end , rollback 回滚;


----join----

left join  on  and还是会查出来左表所有的值，对左表的筛选没用 ，对右表筛选才有用
and放到 where后 是进行最后的过滤

left join中关于where和on条件的几个知识点：
多表left join是会生成一张临时表，并返回给用户
where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录
on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL
on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。
1.对左表添加条件限制需在where条件中添加，不能放到on后面
2.对右表添加条件限制需放到on后面，在where条件中添加会有数据行数差异出现问题。


left join 会查出左表所有的数据
on 后面的and条件会影响到右表相关行的数据展示，筛选右表，但不影响两表交际的数据行数
WHERE 后面的条件会对两表的交集进行再次筛选
如果想要展示左表的所有行则用left join  B_Table on and 处理B表的数据展示
如果要少选最终的数据，条件需要放到where后面


  
  datediff(date1.date2); 相差的天数
  TIMESTAMPDIFF(时间单位，date1,date2);其中时间单位可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND）
  
  null 值和 空字符串和 0 都不相同，null 只有两种操作 is null, is not null 
  无法比较null和0 他们是不等价的.
  
  
  group by 配合使用 聚合函数 count(条件 要加上 or null) 不然不起作用，加上or null 后不符合条件的数量均为0；
  
  
  查看表结构和备注 show full columns from vacuummaterial;
  
  添加索引 ALTER TABLE user ADD INDEX name_index(name);
  查看索引 SHOW INDEX FROM 
  CREATE INDEX t_device_prop_UI_name_IDX USING BTREE ON smooremeshmdata.t_device_prop (UI_name);

-- function

-- 1.function的创建语法

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

-- 调用
select 函数名();

-- 例子：
CREATE FUNCTION test.func_getsum(n int)
RETURNS INT
BEGIN
	declare total int default 0;
	while n>0 do
	  set total:=total+n;
	  set n:=n-1;
	end while;
	return total;
END

select  func_getsum(10); -- 返回55


-- 存储过程
-- 语法：

-- 1、创建存储过程
create procedure 存储过程名称(in/out/inout 参数名 参数类型(长度))
begin
     SQL语句;
end;
 
 
-- 说明：
-- in：该类型参数作为输入，也就是需要调用时传入值
-- out：该类型参数作为输出，也就是该参数可以作为返回值
-- inout：既可以作为输入参数，也可以作为输出参数
-- 参数类型长度：不指定长度时mysql会默认一个长度，如int会默认int(11)，为什么是11，因为int的有符号类型的最大长度就是-2147483648，是11位的。

 -- 例子：
 
 CREATE PROCEDURE test.GetSum(in  n int)
BEGIN
    declare total int default 0;
	while n>0 do
	  set total:=total+n;
	  set n:=n-1;
	end while;
	SELECT total;
END

-- 执行的话要用 
CALL test.GetSum(10);

-- 触发器 trigger 可以在某个表的增删改操作动作前后执行下一个动作， insert update delete ，before after
-- 例子：
-- 新增触发器
CREATE DEFINER=`root`@`%` TRIGGER trigger1
AFTER INSERT
ON Test_Cap01 FOR EACH ROW
INSERT INTO test.Sys_Logs
(ID, Logger, `Level`, Url, Host, `Method`, UserAgent, Cookie, QueryString, Body, Message, Elapsed, CreateTime, IPAddress)
VALUES(uuid(), '触发器', '', '', '', '', '', '', '', concat('Test_Cap01新增一条数据->',new.ID), '', 0, now(), '')
-- 更新触发器
CREATE DEFINER=`root`@`%` TRIGGER trigger_update
AFTER UPDATE 
ON Test_Cap01 FOR EACH ROW
INSERT INTO test.Sys_Logs
(ID, Logger, `Level`, Url, Host, `Method`, UserAgent, Cookie, QueryString, Body, Message, Elapsed, CreateTime, IPAddress)
VALUES(uuid(), '触发器', '', '', '', '', '', '', '', concat('Test_Cap01更新一条数据旧数据：',old.ID,'新数据：',new.ID), '', 0, now(), '')
-- 查看触发器
show triggers;