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

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

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正则查询

$regexpStr = "({$curUname})|({$curUname}/[^;]+)|({$curUname},[^;]+)|([^;]+/{$curUname})|([^;]+,{$curUname})|([^;]+/{$curUname}/[^;]+)|([^;]+,{$curUname},[^;]+)|([^;]+/{$curUname},[^;]+)|([^;]+,{$curUname}/[^;]+)";
foreach ($nameArr as $eachName)
{
    $regexpStr .= "|({$eachName})|({$eachName}/[^;]+)|({$eachName},[^;]+)|([^;]+/{$eachName})|([^;]+,{$eachName})|([^;]+/{$eachName}/[^;]+)|([^;]+,{$eachName},[^;]+)|([^;]+/{$eachName},[^;]+)|([^;]+,{$eachName}/[^;]+)";
}
$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

#截取`table_a`.`field_a`字段中第一个'|@|'之前的部分给`table_b`的`field_a`字段。
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);

#如果`table_a`的`field_a`存在，就把`table_a`的`field_a`给`table_b`.`feild_a`;
#如果`table_a`的`field_a`不存在，就把`table_a`.`field_b`字段中第一个'|@|'之前的部分给`table_b`的`field_a`字段。
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;