mysql常用函数及sql

1、coalesce：返回参数中的第一个非空表达式（从左向右依次类推）； 

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1); 
SELECT COALESCE(NULL, NULL, NULL, NULL); 

2、ifnull： 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

SELECT IFNULL(NULL, "RUNOOB");

3、查询字符串id的最大值

select max(CAST(id AS SIGNED)) from table_name

4、日期格式化
DATE_FORMAT(date,format)：date 参数是合法的日期。format 规定日期/时间的输出格式。

5、字符串转日期

STR_TO_DATE(字符串，日期格式)

6、时间转字符串

DATE_FORMAT(日期，格式字符串)

说明符	说明
%a	工作日的缩写名称（Sun~Sat)
%b	月份的缩写名称（Jan…Dec)
%c	月份，数字形式（0~12)
%D	带有英语后缀的该月日期(0th, 2st, 3nd,…）
%d	该月日期，数字形式（00~31)
%e	该月日期，数字形式（(0~31)
%f	微秒（000000 …999999)
%H	以 2 位数表示 24 小时（00~23)
%h, %I	以 2 位数表示 12 小时（01~12)
%i	分钟，数字形式(00~59)
%j	—年中的天数（001~366)
%k	以 24 小时（0~23)表示
%l	以12小时（1~12)表示
%M	月份名称（January~December)
%m	月份，数字形式（00~12)
%p	上午（AM) 或下午（PM)
%r	时间，12小时制（小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM)
%S, %s	以 2 位数形式表示秒（00~59)
%T	时间，24 小时制（小时 (hh): 分钟 (mm): 秒数 (ss))
%U	周（00~53)，其中周日为每周的第一天
%u	周（00~53)，其中周一为每周的第一天
%V	周（01~53)，其中周日为每周的第一天，和％X同时使用
%v	周（01~53)，其中周一为每周的第一天，和%x同时使用
%W	星期标识（周日、周一、周二…周六）
%w	—周中的每日（0= 周日…6= 周六）
%X	该周的年份，其中周日为每周的第一天，数字形式，4 位数，和％V同时使用
%x	该周的年份，其中周一为每周的第一天，数字形式，4位数，和%v同时使用
%Y	4 位数形式表示年份
%y	2 位数形式表示年份
%%	%一个文字字符

7、建立索引10：

ALTER TABLE dm_alarm_info ADD INDEX idx_alarm_time(alarm_time);

8、在指定字段后增加字段并添加默认值

alter table robot_msg add column status char(1) DEFAULT '0' COMMENT '机器人状态；0：断开；1：连接' after substation_id

9、查询重复数大于2条的数据

select dept from test group by dept having count(*) >2;

select acct_no, prod_code,count(acct_no),count(prod_code) from cust_trans_log group by acct_no ,prod_code having count(acct_no)>2 and count(prod_code) >2

10、查询某一字段相同值的个数

select camera_type,count(camera_type)as count from t_device_visible where del_flg='0' and `status`='1' group by camera_type having (count(camera_type)>0);

11、floor函数：返回小于等于该值的最大整数.

select floor(1.23),floor(-1.23);

 12、字符串截取

mysql字符串截取总结：left()、right()、substring()、substring_index()

left(被截取字符串， 截取长度)

right(被截取字符串， 截取长度)

substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)

substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)

13、字符串转数字，并查询最大值

select max(CAST(id AS SIGNED)) from luneng_device_info  

14、批量更新

UPDATE device_plan.dm_device_monitor d
SET d.monitor_device_name = (
    SELECT
        t.camera_name
    FROM
        (
            SELECT
                t1.camera_name,
                t1.id
            FROM
                visiable.t_device_visible t1
            
        ) t
        WHERE t.id = d.monitor_device_id and d.monitor_device_type='1'
) where d.monitor_device_type='1'

UPDATE luneng_device_info t 
SET lenovo_device_id = (
SELECT
    t1.lenovo_device_id 
FROM
    ( SELECT t2.lenovo_device_id, t2.link_id FROM luneng_device_info_bak t2 ) t1 
WHERE
    t1.link_id = t.link_id 
    ) 
WHERE
    t.lenovo_device_id IS NULL

15、建表语句

create table test(
id int PRIMARY key AUTO_INCREMENT COMMENT '主键',
del_flg char(1) DEFAULT '0' COMMENT '删除标志，默认为零',
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
modify_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
)CHARSET=utf8 COMMENT='测试表' ;

16、批量对数据库表字段进行操作

SELECT table_name FROM information_schema.tables WHERE table_schema='edu_training' and  table_name like 'edu_%'  Order by table_name

SELECT
    concat( 'ALTER TABLE ', table_schema, '.', table_name, ' ADD COLUMN ref_id_ varchar(64) COMMENT \'外键\';' ) 
FROM
    information_schema.TABLES t
WHERE table_schema='edu_training' and  table_name like 'edu_%' and table_name not in ('edu_question_course','edu_question_info')

select table_name,column_name from information_schema.columns where table_schema='edu_training' and  table_name like 'edu_%'and column_name='ref_id_'

17、查找重复记录中最新的一条记录

select t1.* from edu_question_record t1 where not exists (select 1 from edu_question_record t2 where t1.relation_id_=t2.relation_id_ and t1.user_id_=t2.user_id_ and t1.create_time_ > t2.create_time_)

select id,name,create_date from ( select * from sys_user order by create_date  desc) a group by a.name 

18、删除重复记录保留id最大的那一条
  delete from eb_wechat
  where wechat_user_id in( select wechat_user_id from eb_wechat group by  wechat_user_id having count(wechat_user_id) > 1) and 
 id not in(select  max(id) from eb_wechat group by  wechat_user_id having count(wechat_user_id) > 1 )

19、导出特定开头的数据库表

mysqldump -uroot -p 库名 $(mysql -uroot -p 库名 -Bse "show tables like 't_%'") > "导出位置.sql"

20、导出整个数据库到指定目录

mysqldump -uroot -p123456 test > /home/dbbackup/test`date +%Y%m%d_%H`.sql

21、5.7版本授权用户远程访问

grant all privileges on *.* to root@'%' identified by "password";

flush privileges;

22、导入数据库

mysql -h localhost -u root -p123456 test < /data/dbbak/wd_fire/test.sql --default-character-set=utf8

23、B是一个以逗号分隔的字符串，A是一个特定字符串，判断B中是否包含A。

select * from sys_user where find_in_set(2,user_group_id)