MySql

MySql

添加索引

1.添加PRIMARY KEY (主键索引)

	alter table 表名 add primary key(列名) 

2.添加UNIQUE(唯一索引)

	alter table 表名 add add unique(列名)

3.添加INDEX(普通索引)

	alter table 表名 add index index_name(列名)

4.添加FULLTEXT(全文索引)

	alter table 表名 add fulltext(列名)

5.添加多列索引

	alter table 表名 add index index_name(列名1,列名2,列名3)

查询索引

	show index from 表名;

删除索引

	drop index 索引名 on 表名

查询数据库中所有的表名

	select
	-- 表名
	table_name
	from information_schema.tables
	-- 数据库名
	where table_schema='scensus_institutionDesign_dev'

查询表中的所有列

	select 
	-- 列名
	column_name 
	-- 查询查询列
	from information_schema.columns 
	-- 查询哪张表
	where table_name = 'stats_institution_design_institution'

查询表中的列和字段注释

	select 
	-- 列名
	column_name,
	-- 字段注释
	column_comment 
	from information_schema.columns
	where table_name = '表名';

根据数据库表名和表名查询列

	select 
	-- 列名
	column_name 
	from information_schema.columns 
	-- 数据库名
	where table_schema='scensus_institutionDesign_dev' 
	-- 数据库表名
	and table_name='stats_institution_design_institution';

时间处理

查询当前时间和某个时间段距离多少天

select to_days(now())-to_days(date_format('2020-6-15','%Y-%m-%d')

四舍五入 round ( ) 函数用法（ 例：查询工龄 ）

round((to_days(now())-to_days(date_format('2020-6-15','%Y-%m-%d'))-1)/365)

计算当前日期的前一天和后一天

date_sub（）这个函数，用法举例：

比如今天是2016年08月01日

date_sub('2016-08-01',interval 1 day) 表示 2016-07-31

date_sub('2016-08-01',interval 0 day) 表示 2016-08-01

date_sub('2016-08-01',interval -1 day) 表示 2016-08-02

date_sub(curdate(),interval 1 day) 表示 2016-07-31

date_sub(curdate(),interval -1 day) 2016-08-02

date_sub(curdate(),interval 1 month) 表示 2016-07-01

date_sub(curdate(),interval -1 month) 表示 2016-09-01

date_sub(curdate(),interval 1 year) 表示 2015-08-01

date_sub(curdate(),interval -1 year) 表示 2017-08-01

MySql格式化日期

%Y：年
%m：月
%d：日
%H：时
%i：分
%s：秒

date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s')

时间间隔查询

时间间隔计算  计算当前时间和用户创建时间的间隔

1.Mysql的查询中，使用到查询两日期之间相差多少天，多少周等的情况；

可以使用mysql的内置的函数，TimeStampDiff()；

2.TimeStampDiff()的使用的语法是,TimeStampDiff(间隔类型，前一个日期，后一个日期)；

其中，间隔类型有FRAC_SECOND   表示间隔是毫秒，SECOND   秒，MINUTE   分钟，HOUR   小时，DAY   天，WEEK   星期  MONTH   月，QUARTER   季度，YEAR   年；

3.计算两个日期之间相差的天数是SELECT TimeStampDiff(DAY，'2018-08-06'，'2019-08-06')；

间隔类型

second		秒
minute		分钟
hour		小时
day			天
week		星期
month		月
quarter		季度
year		年

case when 语法

select
	case t.state
	when 0 then '已保存'
	when 1 then '审批中'
	when 2 then '已通过'
	when 3 then '已拒绝'
	when 4 then '退回修改'
	when 5 then '已完成'
	end) as state, -- 状态
from table t

去除小数点和0

select 0 + cast(num as char) as num from table

查询时，输出数据加汉字

select concat(ba.apply_days,'天') as applyDay from table

SQL语句的使用

 -- 内层sql语句

select TimeStampDiff(WEEK,user_create_date,sysdate())

-- 外层sql语句
select count(*) from (select TimeStampDiff(WEEK,user_create_date,sysdate()) value from cmfz_user) t
where t.value = 0

添加字段

语法：alter table 表名 add column 新添加的字段名 类型(长度) null comment '字段说明' after 在哪个字段后面添加;

修改字段

语法：alter table 表名 modify column 修改的字段名 类型(长度) comment '字段说明';

把同表中A字段的值添加到B字段中

语法：update 表名 set 字段B = 字段A

把A表中的字段添加到B表字段中

语法：update A表,B表 set A表字段 = B表字段 where A表id(对应B表主键id) = B表主键id

数据转换成大小写

变成小写:  update 表 set 字段 = lower(字段)
变成大写:  update 表 set 字段 = upper(字段)

处理历史数据-- 把bitint类型改为json类型后，修改数据(在每条数据前后增加[ ])

update 表名 set 字段名(要修改的) = concat('[', 字段名(要修改的), ']');

json类型连表

SELECT
bad.id as badId,
oe.id as oeId
from
org_emp oe
left join business_apply_detail bad ON JSON_CONTAINS(bad.trip_person_id,JSON_ARRAY( oe.id ),'$' )
where oe.id = 1619

处理历史数据 --> 查询历史数据，并根据行号进行一个字段排序

UPDATE upms_provilege up,
(
   SELECT
      @row_number := CASE WHEN @upms_provilege_no = s.module_id THEN @row_number + 1 ELSE 1 END AS rowNum,
      @upms_provilege_no := s.module_id AS module_id,
      s.id
   FROM
      upms_provilege s
    WHERE s.module_id = 1
   ORDER BY s.sort_order asc, s.id desc
   ) tmp
   SET up.sort_order = tmp.rowNum
WHERE
   up.id = tmp.id;


sql解释：
TODO

处理历史数据 --> 查询历史数据，并根据行号进行多个字段排序

1： select
2： id,
3： @rn := (
4： if
5： ( @tagtype = tag_type and @tenantid = tenant_id, @rn + 1, 1 )) as order_by,
6： @tagtype := tag_type as tag_type,
7： @tenantid := tenant_id as tenant_id,
8： sname 
9： from
10：( select id, tag_type, sname, tenant_id from org_tag order by tenant_id, tag_type, id desc ) a,
11：( select @rn := 0, @tagtype := '', @tenantid := 0 ) b


sql解释：
@tagtype :=	临时变量
@tenantid := 	临时变量

TODO