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)