联表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` ASselect (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;