Mysql 常用sql语句汇总

[b]一、引用参考[/b]
--MySQL统计函数记录——时间段统计
[url]http://blog.csdn.net/tbkken/article/details/8173020[/url]
--mysql多个字段拼接 CONCAT、CONCAT_WS
[url]https://www.cnblogs.com/crystaltu/p/6699004.html[/url]
--多行记录合并成一条记录 GROUP_CONCAT
[url]https://www.cnblogs.com/guaiguaipaizz/p/6501676.html[/url]
--MySql 建表、添加字段、修改字段、添加索引SQL语句写法
[url]https://www.cnblogs.com/wenlj/p/5258102.html[/url]
--MySql数据库的优化（二） MySql中is NULL、ISNULL()和IFNULL()运行速度的比较
[url]https://www.cnblogs.com/fynfuxiaoxia/p/7670375.html[/url]
--Mysql 字符串截取函数
[url]https://www.cnblogs.com/wjm956/p/7724244.html[/url]
--mysql使用between and处理时间区间不包括右边界问题
[url]https://blog.csdn.net/qq_17011423/article/details/69220231[/url]
-- MySQL模糊查询之instr
[url]https://www.cnblogs.com/mr-wuxiansheng/p/6531221.html[/url]

[b]二、工作SQL[/b]

select CONCAT_WS(":",t.user_id,t.user_name) from t_user t;
select GROUP_CONCAT(CONCAT_WS(":",t.user_id,t.user_name) SEPARATOR ";") from t_user t;
//分组
select t.me_id, GROUP_CONCAT(t.user_name SEPARATOR ";") from t_meet_joiner t group by t.me_id;
select t.me_id, GROUP_CONCAT(CONCAT_WS(":",t.user_id,t.user_name) SEPARATOR ";") meJoiner from t_me_joiner t group by t.me_id;
select t.me_id, GROUP_CONCAT(t.user_name SEPARATOR ";"), GROUP_CONCAT(CONCAT_WS(":",t.user_id,t.user_name) SEPARATOR ";") meJoiner from t_me_joiner t group by t.me_id;

--分组、单行多字段拼接、多行单字段拼接
select 
t.s_id, 
GROUP_CONCAT(t.user_name SEPARATOR ";") sNames, 
GROUP_CONCAT(CONCAT_WS(":",t.user_id,t.user_name) SEPARATOR ";") sJoiners 
from t_s_joiner t 
group by t.s_id
--44 \\ 输出：小梅;阿辉;小斌  \\ mei.xiao:刘梅;hui.a:阿辉;bin.xiao:小斌

c.stu_name like '%${stuName}%'
或者
instr(c.stu_name, #{stuName}) > 0

--统计sql范例demo:

select u.uname '姓名', c.cname '名称1',
left(t.begin_time,16) '开始时间', left(t.end_time, 16) '结束时间', 
t.con '内容', 
case c.status 
when '1' then '状态1'
when '2' then '状态2'
when '3' then '状态3'
when '4' then '状态4'
when '5' then '状态5'
when '6' then '状态6'
else '未知状态' end
'状态',
t.create_time '创建时间'
from t_crm_order t
left join t_crm_user u 
on t.uid=u.uid
left join t_crm_customer c
on t.cid=c.id
where 1=1
order by t.create_time asc;

=====================================================================
[b]基础SQL[/b]

select SYSDATE() from dual;

按年汇总，统计：
select sum(mymoney) as totalmoney, count(*) as sheets from table group by date_format(col, '%Y');
按月汇总，统计： 
select sum(mymoney) as totalmoney, count(*) as sheets from table group by date_format(col, '%Y-%m');
按季度汇总，统计： 
select sum(mymoney) as totalmoney,count(*) as sheets from table group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3)); 
select sum(mymoney) as totalmoney,count(*) as sheets from table group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
按小时： 
select sum(mymoney) as totalmoney,count(*) as sheets from table group by date_format(col, '%Y-%m-%d %H ');
查询 本年度的数据:
SELECT * FROM table WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())
查询数据附带季度数:
SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM table;
查询 本季度的数据:
SELECT * FROM table WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());
本月统计:
select * from table where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())
本周统计:
select * from table where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())
N天内记录:
WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N 

<!-- 查询30以内的下一期记录-->
<select id="sqlId" resultMap="BaseResultMap" parameterType="java.lang.Long">
select * from tableName
where sid = 88888888
and status=1 
and DATE_FORMAT(date_add(now(),interval 1 month),'%Y-%m-%d') >= DATE_FORMAT(duDate,'%Y-%m-%d')  
and now() < duDate
</select>

[b]三、修改表结构[/b]

1、禁止调整MySQL表字段的位置，添加字段禁止使用after属性 ; 
2、不要加COLLATE=utf8_general_ci和ROW_FORMAT=COMPACT ;
3、SQL的前后需要加上use xxx ; begin ; commit ; 其中xxx为数据库的schema名;

--【表】
-- 创建表
use student;
begin;
--  新建学生信息表 t_student_info
CREATE TABLE `t_student_info` (
`id`  bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id' ,
`course_id`  bigint(20) NULL DEFAULT NULL COMMENT '注释内容XXX' ,
`ff_id`  bigint(20) NULL DEFAULT NULL COMMENT '注释内容222XXX' ,
`status`  char(1) CHARACTER SET utf8  NOT NULL DEFAULT '1' COMMENT '状态：1-正常;' ,
`create_time`  datetime NOT NULL COMMENT '记录创建时间' ,
`update_time`  datetime NOT NULL COMMENT '记录修改时间' ,
PRIMARY KEY (`id`),
INDEX `IDX_COURSE_ID` (`course_id`) USING BTREE ,
INDEX `IDX_FF_ID` (`ff_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8
COMMENT='学生信息表'
AUTO_INCREMENT=1
;
--  修改成绩信息表t_score_info, 增加字段student_id
alter table student.t_score_info add COLUMN student_id bigint(20) NULL DEFAULT NULL COMMENT '学生Id';
-- commit
commit;

-- 回滚语句
use student;
begin;
-- 删除学生信息表t_student_info
drop table if exists student.t_student_info;
-- 删除成绩信息表t_score_info的字段plan_id
alter table student.t_score_info drop COLUMN student_id;
-- commit
commit;

--01
use demo;
begin;
update demo.t_stu_info set begin_time='2018-08-16 10:00',end_time='2018-08-16 12:00' where id=100;
commit;

-- 02 
insert into demo.t_sql_info(col2, col3, col4, col5, col6)
    values('111', 2, 33333,  sysdate() , 'file');
commit;

--【字段】

--修改字段长度
alter table studentDB.t_stu_comments modify content varchar(1000) NOT NULL COMMENT '内容';
--去掉stu_id 的非空属性
alter table studentDB.t_stu_info modify stu_id BIGINT(10) NULL;
alter table studentDB.t_stu_info add COLUMN book_id int(20) NULL DEFAULT NULL COMMENT '字段注释内容' AFTER id;
--回滚
alter table studentDB.t_stu_info modify stu_id BIGINT(10) NOT NULL;
alter table studentDB.t_stu_info drop COLUMN book_id;
--【索引】
--创建唯一约束
[code="java"]alter table A add unique index unique_sltId_loanNo (SLT_ACCOUNT_ID, LOAN_PERIOD_NO);

--创建普通索引

alter table A add index  FK_FK_INSTALLMENT_INFO_SLT_ACCOUNT_ID (SLT_ACCOUNT_ID); 
alter table A add index  IDX (SLT_ACCOUNT_ID,REPAY_STATUS);