mysql常用函数

获取最后插入数据的ID
    SELECT LAST_INSERT_ID();

IN 传字符串
SELECT * FROM us_user WHERE FIND_IN_SET(userId,'6380,6381,6382,6383,6384,')

返回受影响行数
    FOUND_ROWS() : select
    ROW_COUNT() : update delete insert.

查询结果对大小写敏感
    在查询敏感字段前加 BINARY

查询结果去重
    DISTINCT

模糊查询
    Like CONCAT('%',keyword,'%')

统计数量
    -- 定义变量
    DECLARE totalCount int DEFAULT 0;
    -- 记录总数
    SELECT COUNT(TableKey) INTO totalCount FROM TableName;
    -- 返回总数
    SET _pageCount = totalCount;

随机查询数据
    ORDER BY rand() LIMIT 5

日期计算
返回天数 SELECT DATEDIFF('2015-01-01 19:00:00','1991-01-01 00:00:00');
返回秒数 SELECT UNIX_TIMESTAMP('2015-01-01 19:00:01')-UNIX_TIMESTAMP('1991-01-01 00:00:00')

1。显示表创建语句
SHOW CREATE TABLE 表名
2。C#取语句
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
    GetSQL = reader[1].ToString().Replace("
", "");
}




慢：WHERE name like "MySQL%"
快：WHERE name>="MySQL"and name<"MySQM" 

慢：WHERE Amount/7<24; 
快：WHERE Amount<24*7; 

//从别的表查询数据|给定值 插入到当前表
INSERT INTO <TableName> (collection1,collection2,,addTime) (SELECT value,collection2,NOW()FROM 表 WHERE id=_id );

//下一个
SELECT *  FROM sys_notepad WHERE sysUserId=_sysUserId AND id>_id LIMIT 1;

//上一个
SELECT MAX(id) INTO count  FROM sys_notepad WHERE sysUserId=_sysUserId AND id<_id;
SELECT *  FROM sys_notepad WHERE sysUserId=_sysUserId AND id=count;


//分组排序
select  *,ROW_NUMBER() over(partition by convert(varchar(10),SessionTime, 120) order by ID) as new_index from WC_MessageRelation

//有更新时间和添加时间的，添加记录时必须插入更新时间



//用于一次查找或删除多条(必须写两个，一个用于操作，一个用于返回操作结果)   传值类似 1,2,3
BEGIN
  SET @sel = 'DELETE FROM us_projectpipeline where Id in (';
  SET @sentence = concat(@sel,_id,')');      -- 连接字符串生成要执行的SQL语句
  prepare stmt from @sentence;                  -- 预编释一下。 “stmt”预编释变量的名称，
  execute stmt;                                 -- 执行SQL语句
  deallocate prepare stmt;                            -- 释放资源

  SET @sel1= 'select count(Id) from us_projectpipeline where Id IN (';
  SET @sentence1 = concat(@sel1,_id,')');      -- 连接字符串生成要执行的SQL语句
  prepare stmt1 from @sentence1;                  -- 预编释一下。 “stmt”预编释变量的名称，
  execute stmt1;                                 -- 执行SQL语句
  deallocate prepare stmt1;
END