mysql使用笔记

联表on多个条件

update `table_a` inner join `table_b` on (`table_a`.`mark_no`=`table_b`.`mark_no` && `table_b`.`status`=16) set `table_b`.`modify_no`=`table_a`.`modify_no`;

 

where group by having

select `table_b`.`mark_no`,sum(`table_b`.money),`table_a`.total from `table_b` left join `table_a` on `table_b`.`mark_no`=`table_a`.`mark_no` where `table_b`.`status` in (11,12,14,16) group by `table_b`.`mark_no` having sum(`table_b`.`money`) >`table_a`.`total`;

group

select `field_name`,count(`field_name`) as `count` from `table_name` where `status` in (1,2) group by `field_name` order by `count` desc limit 0,3;

innodb单条记录过大

单条记录超过8k
[err] 1030 - got error 139 from storage engine

更改数据库引擎

alter table `table_name` type = innodb;

建库sql

1 gbk: create database test2 default character set gbk collate gbk_chinese_ci;
2 
3 utf8: create database `test2` default character set utf8 collate utf8_general_ci

区分大小写

alter table `table_name`
 modify column `field_name` varchar(30) binary character set utf8 collate utf8_bin  not null default '' comment '注释',
 modify column `field_name2` varchar(30) binary character set utf8 collate utf8_bin  not null default '' comment '注释2';

 

mysql正则regexp

regexp 
"^(({$curuname})|({$curuname}_[^,]+)|([^,]+_{$curuname}_[^,]+)|([^,]+_{$curuname}))(,.+)?"

sql正则查询

1 $regexpstr = "({$curuname})|({$curuname}/[^;]+)|({$curuname},[^;]+)|([^;]+/{$curuname})|([^;]+,{$curuname})|([^;]+/{$curuname}/[^;]+)|([^;]+,{$curuname},[^;]+)|([^;]+/{$curuname},[^;]+)|([^;]+,{$curuname}/[^;]+)";
2 foreach ($namearr as $eachname)
3 {
4     $regexpstr .= "|({$eachname})|({$eachname}/[^;]+)|({$eachname},[^;]+)|([^;]+/{$eachname})|([^;]+,{$eachname})|([^;]+/{$eachname}/[^;]+)|([^;]+,{$eachname},[^;]+)|([^;]+/{$eachname},[^;]+)|([^;]+,{$eachname}/[^;]+)";
5 }
6 $paramarr['uname_reg'] = "^({$regexpstr})(;.+)?$";

 

mysql字符串左右截取

update `table_name` set `field_name`=concat(left(`field_name`,10),' ',right(`field_name`,8)) where length(`field_name`)=18;

substring_index截取与if

1 #截取`table_a`.`field_a`字段中第一个'|@|'之前的部分给`table_b`的`field_a`字段。
2 update `table_a` inner join `table_b` on `table_a`.`mark_no`=`table_b`.`mark_no` set `table_b`.`field_a`=substring_index(`table_a`.`field_a`, '|@|', 1);
3 
4 #如果`table_a`的`field_a`存在，就把`table_a`的`field_a`给`table_b`.`feild_a`;
5 #如果`table_a`的`field_a`不存在，就把`table_a`.`field_b`字段中第一个'|@|'之前的部分给`table_b`的`field_a`字段。
6 update `table_a` inner join `table_b` on `table_a`.mark_no=`table_b`.mark_no set `table_b`.`feild_a`=if(`table_a`.`field_a`, `table_a`.`field_a`, substring_index(`table_a`.`field_b`, '|@|', 1));

字段中字符替换

update `table_name` set `step_a`=replace(replace(replace(replace(`step_a`,char(13),''),char(10),''),char(9),''),' ',''),`step_b`=replace(replace(replace(replace(`step_b`,char(13),''),char(10),''),char(9),''),' ','');

#note:
char(9) 水平制表符
char(10) 换行(lf)
char(13) 回车

字符计数

select * from `table_name` where (length(`field_a`)-length(replace(`field_a`,'a',''))) = 5;
查field_a字段有5个a的记录

 

update.set自增

update `table_name` set `use_count`=`use_count`+1 where `id` in (12,13,15);

update多个字段

update `table_name` set `field_a`=2, `field_b`=`field_c` where `status`='6';

插入_用select结果

и┤ок
insert into  `table_name` (`field_a`,`field_b`,`field_c`)
select `field_a`,'л┬ох',`field_c` from `table_name` where `field_b` like '%й╔ои%';

视图创建修改

create or replace algorithm=undefined definer=`username`@`192.168.%` sql security definer view `view_name` as
select 
(case ifnull(`p`.`mark_no`,'') when '' then 1 else 0 end) as `selector`,
`p`.`id` as `pid`,
`o`.`money` as `money` 
from 
(`table_o` `o` left join `table_p` `p` on((`o`.`mark_no` = `p`.`mark_no` and `p`.status=16 ))) 
where (`o`.`status` = 6 || `o`.`status` = 7)

索引创建删除

删除索引
drop index `records_unique` on `fol_acc_checksheet`

创建唯一索引
create unique index `records_unique` on `fol_acc_checksheet` (`bank_acc_id`, `year_month`, `sheet_type`) using btree;

索引的创建删除

create [unique] index idx_name on tbl_name (filed1, filed1) using btree;

alter table tbl_name add [unique] index idx_name (filed1, filed1) using btree;

----------------------------------------------------------------

drop index index_name on tbl_name;

alter table tbl_name drop index idx_name;

索引删除与新建

alter table `table_name` drop index `index_name`;
create index `index_name` on `table_name`(`mark_no`);

唯一索引删除记录

create table `table_name` 
  `id` int(11) not null auto_increment comment '主键id',
  `pid` int(11) not null default 0 comment '',
  `year` int(11) not null default 0 comment '年',
  `month` int(11) not null default 0 comment '月',
  `cre_time` timestamp not null default current_timestamp comment '创建时间',
  `up_time` varchar(20) not null default '' comment '修改时间',
  `opt_uid` int(11) not null default 0 comment '操作者uid',
  `opt_username` varchar(50) not null default '' comment '操作者名称',
  `del_time` int(11) not null default 0 comment '删除时间戳',
  primary key (`id`),
  unique key `year_month_pid_del_time` (`year`,`month`,`pid`, `del_time`) using btree
) engine=innodb auto_increment=1 default charset=utf8 comment='数据记录';

自定义排序

order by (case when status=12 then 1 when status=14 then 2 else 3 end),id desc;