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;