mysql 常用SQl

获取行号

SELECT
		(@rowNum:=@rowNum+1) as rowNo ,T.*
		FROM oic_service_project T,(Select (@rowNum :=0) ) b 
where T.APPLY_ID = 1

删除三个月之前的数据

delete from table1 where date1<curdate() - interval 3 month;

 

修改数据库表自增的值

建表时：create table tname(...)AUTO_INCREMENT = 起始值

建表后：alter table tname AUTO_INCREMENT = 大于已有的自动增长的ID值

mysql默认的事务处理级别是'REPEATABLE-READ',也就是可重复读

1.查看当前会话隔离级别

select @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set session transaction isolatin level repeatable read;

4.设置系统当前隔离级别

set global transaction isolation level repeatable read;

查看一个表的字段总数

select count(*) from information_schema.COLUMNS where TABLE_SCHEMA='cxm_order_new' and table_name='dk_sale_order'

计算索引的选择性

SELECT count(DISTINCT(CREATE_DATE))/count(*) AS Selectivity FROM tpl_action_t;