MySql 语句收集

=与:=区别

  • = 只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用。鉴于此,用变量实现行号时,必须用:=
  • := 不只在set和update时时赋值的作用,在select也是赋值的作用。

序列号:

两种创建变量并赋值的方式
SET @变量名 = 值;
SELECT 值 INTO @变量名;

select (@rowNO := @rowNo+1) AS rowno from blog,(select @rowNO :=0) b 

必须要有select @rowNo:=0,不然查询出来的全是null

或者这样

set @rowNo=0;
select (@rowNO := @rowNo+1) AS rowno from blog;

分组:

一个user表,有id,name字段,name有重复,求id最小的不同name的记录表。

复杂的:

select * from user a where a.id=
(select id from user b where a.name=b.name limit 1) 

group by:

select * from user where id in (select  min(id) from user group by name having count(1)>0)

使用

select ta.* from hs_workflow_log as ta,
(select max(process_instance_id) as process_instance_id,business_no from hs_workflow_log
  group by business_no) as tb
where ta.business_no=tb.business_no and ta.process_instance_id=tb.process_instance_id
and  ta.business_no in  
 (  
      'FD188189405673029642'
       , 
        'FD188189208538644488'
       , 
        'FD188188780774162444'
       ) 
  order by ta.create_time desc

子查询分组:

select * from (select * from `test` order by `date` desc) `temp` 
group by category_id order by `date` desc

虽然没有理解,但是测下来是对的

上面三种,测试下来,还是groupby与子查询分组效率差不多,最上面的效率最低,不管是否有索引的情况,都最慢。

上面的方式mysql5.7以后无效,修改为:

select * from (select * from `test` order by `date` desc limit 10000) `temp` 
group by category_id order by `date` desc

可能是因为子查询大多数是作为一个结果给主查询使用,所以子查询不需要排序的原因。

对子查询的排序进行limit限制,此时子查询就不光是排序,所以此时排序会生效,但是限制条数却只能尽可能的设置大些。

分组数量查询:

现在希望取整个表中business_no中的process_instance_id不同的数量

select business_no,count(distinct process_instance_id) as num from hs_workflow_log  group by business_no having num>1

分组,组合拼接字段

CREATE TABLE `USERS`(
`Id` INT(4) NOT NULL,
`Name` VARCHAR(30) DEFAULT ''
);
INSERT INTO USERS(`Id`,`Name`) VALUES(1,'A'),(1,'B'),(2,'C');

SELECT Id,GROUP_CONCAT(`Name` SEPARATOR ',') NAMES FROM `USERS` GROUP BY Id;
#不使用分隔符
SELECT Id,GROUP_CONCAT(Name order by Name) NAMES FROM `USERS` GROUP BY Id;

GROUP_CONCAT()函数的分隔符可以自定义,根据你的需求适当选择即可

用group_concat的时候请注意,连接起来的字段如果是int型,一定要转换成char再拼起来,否则返回的是byte[]

该问题当你在SQLyog等一些工具中是体现不出来的,所以很难发现。

select group_concat(ipaddress) from t_ip 返回逗号隔开的串
select group_concat(id) from t_ip 返回byte[]
select group_concat(CAST(id as char)) from t_dep 返回逗号隔开的串
select group_concat(Convert(id , char)) from t_dep 返回逗号隔开的串

group_concat连接字段的时候是有长度限制的,并不是有多少连多少。但可以设置一下

同数据库表数据迁移

-- 迁移统一账户数据
update hs_issue_info a ,hs_draw_info b set 
a.add_person_p2p_account_draw=1,
a.add_person_p2p_account_draw_archive_id=b.unify_loan_account,
a.add_person_p2p_account_draw_p2p_account_id=b.p2p_account_id,
a.add_person_p2p_account_draw_p2p_account_name=b.p2p_account_name,
a.add_person_p2p_account_draw_p2p_account_tel=b.phone_number
where a.draw_id=b.draw_id and b.unify_loan_account is not null;

存储过程

delimiter $$
drop procedure if exists pro;
create procedure pro()
begin
   declare num int default 0;
	 select count(1) from hs_draw_info where unify_loan_account is not null into num;
	 select num;
end 
$$
delimiter ;
call pro();

delimiter 是分隔符,其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。
默认情况下,delimiter是分号;命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令

最后一个$$就是告诉命令行,语句可以执行了。

案例,迁移数据

创建临时表,遍历插入数据

-- 创建临时表
CREATE TEMPORARY TABLE IF NOT EXISTS temp
(
	id INT NOT NULL DEFAULT 0,
	role_full_name VARCHAR(100) NOT NULL,
	right_full_name VARCHAR(100) NOT NULL
)

-- 初始化数据
set @rowNo=0;
insert into temp (id,role_full_name,right_full_name) 
select  (@rowNO := @rowNo+1) AS id,
a.full_name as role_full_name,
c.full_name as right_full_name  
from role a 
join role_right b 
on a.role_id=b.role_id
join `right` c
on b.right_id=c.right_id
where a.application_id=39
and c.application_id=39;

-- 修改数据-- 修改数据
update temp set role_full_name=replace(role_full_name,'huishi','huishi-pub');
update temp set right_full_name=replace(right_full_name,'huishi','huishi-pub');
update temp set role_full_name=replace(role_full_name,'huishi','huishi-pub');
update temp set right_full_name=replace(right_full_name,'huishi','huishi-pub');
-- 查看信息
select * from temp;

select distinct role_full_name,right_full_name from temp;
select @rowNo;

-- 创建存储过程,循环插入数据
delimiter $$
drop procedure if exists pro $$
create procedure pro()
begin
   declare num int default 1;
	 declare totalNum int default 0;
	 declare role_id2 int default 0;
	 declare right_id2 int default 0;
   declare role_full_name2 varchar(100) default '';
	 declare right_full_name2 varchar(100) default '';
	 set num=1;
	 while num<=@rowNo do
			 select role_full_name,right_full_name into role_full_name2,right_full_name2  from temp where id=num;
			 select role_id into role_id2 from role where full_name=role_full_name2;
			 select right_id into right_id2 from `right` where full_name=right_full_name2;
			 select count(1) into totalNum from role_right where role_id=role_id2 and right_id=right_id2;
			 if totalNum=0 then
			      INSERT INTO `role_right`(`role_id`, `right_id`, `creator`, `is_active`, `insert_time`, `update_time`) VALUES (role_id2, right_id2, NULL, 1, now(), now());
			 end if;
			 set num=num+1;
	 end while;
end $$
delimiter ;
-- 执行存储过程
call pro();

-- 删除临时表
DROP TABLE IF EXISTS temp;

迁移部分征信数据附件:

delimiter $$
drop procedure if exists pro $$
create procedure pro()
begin
   declare num,dataNum int default 0;
   declare archiveId,searchArchiveId,creditDataId bigint default 0;
   set num=1;
   
   set @rowNo=0;
   create table temp as 
   select  (@rowNO := @rowNo+1) AS id, a.* 
   from hs_credit_order a 
   join hs_loan_order b 
   on a.loan_order_id=b.loan_order_id
   join hs_workflow_log c
   on a.loan_order_id=c.business_no
   where b.loan_status=7 
   and c.workflow_key like 'HS-FKSP%' 
   and c.current_node_name='通过' 
   and c.create_time>'2020-02-18 00:00:00' 
   and c.create_time<'2020-05-19 00:00:00';
   
	 while num<=@rowNo do
			 select dataNum=count(1) from hs_cdc_credit_data a join temp b on a.credit_order_id=b.credit_order_id;
			 -- 只有不存在对应征信才新增数据
			 if dataNum=0 then
			   INSERT INTO  `hs_cdc_credit_data`(  `loan_order_id`, `credit_order_id`, `credit_category`, `business_category`, `search_name`, `search_archive_id`, `report_time`, `complete_time`, `credit_data_status`, `create_time`, `create_name`, `modify_time`, `modify_name`) 
				 select loan_order_id,credit_order_id,2,2,search_name,search_archive_id,NULL,NULL,1,now(),'admin',now(),'admin' from temp where id=num;
				 select search_archive_id into searchArchiveId from temp where id=num;
				 select LAST_INSERT_ID() into creditDataId;
				 -- 批量新增查询附件
				 INSERT INTO `hs_cdc_credit_data_file`( `credit_data_id`, `catalog_key`, `catalog_name`, `image_id`, `file_sort`, `is_image`, `is_system`, `file_status`, `create_time`, `create_name`, `modify_time`, `modify_name`)
select creditDataId,NULL,NULL,bb.img_id,bb.detail_sort,true,true,1,now(),'admin',now(),'admin' from hs_archive_attachment aa join
hs_archive_attachment_detail bb 
on aa.attachment_id=bb.attachment_id
where aa.catalog_key='rhzxbgtp' and aa.attachment_status=1 and bb.detail_status=1 and aa.archive_id=searchArchiveId;
				 	 
			 end if;
			 set num=num+1;
	 end while;
	 
	 drop table temp;
end $$
delimiter ;
-- 执行存储过程
call pro();

注意上面,创建临时表使用了

create table temp as copytable

参考:

mysql分组取最大(最小、最新、前N条)条记录

MySql存储过程及MySql常用流程控制语法

MySQL存储过程

mysql取出每个分组中最新的记录

面试官:为什么mysql不建议执行超过3表以上的多表关联查询?

posted @ 2018-12-29 18:15  hongdada  阅读(313)  评论(0编辑  收藏  举报