qiao.dev

常用sql

 
  • 2016年 05月14日

mysql日期和字符相互转换方法

date_format(date,'%Y-%m-%d')    -------------->oracle中的to_char();
str_to_date(date,'%Y-%m-%d')     -------------->oracle中的to_date();

%Y：代表4位的年份 %y：代表2为的年份

%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)
%k：代表小时,格式为(0……23)
%h： 代表小时,格式为(01……12)
%I： 代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)

%i： 代表分钟, 格式为(00……59)

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为24 小时(hh:mm:ss)

%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)

MySQL varchar转换为int

  1. 手动转化类型(直接+0) select server_id from cardserver where game_id = 1 order by server_id+0 desclimit 10
  2. 使用MySQL函数CAST： select server_id from cardserver where game_id = 1 order by CAST(server_id as SIGNED) desc limit 10;
  3. 使用MySQL函数CONVERT： select server_id from cardserver where game_id = 1 order by CONVERT(server_id,SIGNED)desc limit 10;

时间差计算

time_to_sec(timediff(t2, t1))
timestampdiff(second, t1, t2)
unix_timestamp(t2) -unix_timestamp(t1)

mysql 时间戳用法

ALTER TABLE tablename ADD lastupdatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP

ALTER TABLE tablename ADD lastupdatetime TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

TIMESTAMP时间戳在创建的时候可以有多重不同的特性，如：

  1. 在创建新记录和修改现有记录的时候都对这个数据列刷新： TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

  2. 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它： TIMESTAMP DEFAULT CURRENT_TIMESTAMP

  3. 在创建新记录的时候把这个字段设置为0，以后修改时刷新它： TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

  4. 在创建新记录的时候把这个字段设置为给定值，以后修改时刷新它： TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP

sql批量导入

必须在本地执行,再复制到远程 400w 数据 update 10min 23s

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]

LOAD DATA INFILE 'd:234/123.txt' INTO TABLE `tels` LINES TERMINATED BY '
';
//去重
INSERT INTO hfvast_smg_1 (id) SELECT DISTINCT tels FROM tels

INSERT INTO hfvast_smg_1 (id) SELECT tels FROM tels

INSERT INTO hfvast_smg_1 (id,serverNum,tagNum,content,createdDate,msgFormat,TYPE)
SELECT id,serverNum,tagNum,content,createdDate,msgFormat,TYPE FROM hfvast_smg_2

SELECT * FROM hfvast_smg_1 WHERE tagNum='8615234567890'
SELECT id,serverNum,tagNum,content,createdDate,msgFormat  FROM hfvast_smg_1 WHERE TYPE = 4 LIMIT 0,200
UPDATE hfvast_smg_1 SET TYPE=0 WHERE TYPE=0 LIMIT 1000

emoji数据库存储

emoji 表情 mysql数据库需要将utf8改为 utf8mb4.

truncate 与drop

truncate 保留表结构 drop 反之

索引

ALTER TABLE message_received ADD INDEX(TIME)

ALTER TABLE `student` MODIFY COLUMN `id` COMMENT '学号';
DROP INDEX savetime ON t_wt_kdxf_main_sheet  删除列索引

ALTER TABLE t_wt_kdxf_main_sheet DROP COLUMN savetime   删除列

###

去重导入
INSERT IGNORE INTO t_wt_realname_linshi (tel)SELECT tel FROM tel;

查询重复记录
SELECT sheetid FROM t_wt_financial_orde GROUP BY sheetid HAVING COUNT(*) >1

DELETE FROM t_wt_financial_orde  WHERE id IN (
SELECT * FROM t_wt_financial_orde WHERE sheetid IN (SELECT sheetid FROM t_wt_financial_orde GROUP BY sheetid HAVING COUNT(sheetid) > 1) 
AND id NOT IN (SELECT MIN(id) FROM t_wt_financial_orde GROUP BY sheetid HAVING COUNT(sheetid )>1) ) 

直接使用上面进行删除会报错,提示You can't specify target table 'wms_cabinet_form' for update in FROM clause
意思:不能先select出同一表中的某些值，再update这个表(在同一语句中)。

CREATE TABLE t_wt_financial_orde_2 AS (SELECT * FROM t_wt_financial_orde WHERE sheetid IN (SELECT sheetid FROM t_wt_financial_orde GROUP BY sheetid HAVING COUNT(sheetid) > 1) 
AND id NOT IN (SELECT MIN(id) FROM t_wt_financial_orde GROUP BY sheetid HAVING COUNT(sheetid )>1)) 

创建临时表 将待删除的记录存到临时表  表关联删除重复的记录即可
DELETE FROM t_wt_financial_orde  WHERE id IN (SELECT id FROM t_wt_financial_orde_2)
搜索