//数据库保存的为时间戳
select * from 表名 where FROM_UNIXTIME(存时间字段名,'%Y-%m-%d')='2017-12-12'
//数据库保存的为日期格式时间
SELECT * FROM 表名 where DATE_FORMAT(存时间字段名,'%Y-%m-%d')='2017-12-11';
select * from 表名 where FROM_UNIXTIME(存时间字段名,'%Y-%m-%d')='2017-12-12'
//数据库保存的为日期格式时间
SELECT * FROM 表名 where DATE_FORMAT(存时间字段名,'%Y-%m-%d')='2017-12-11';
//sql语句查出时间戳转化为日期格式
select *,from_unixtime(reg_time,'%Y-%m-%d %H:%i:%s') as datetime from bn_user_tel
某个字段(serviceid)存储了逗号隔开的多个ID 如(1,2,3,4,5,6,7)
查询 这个字段包含ID 5的sql SELECT * FROM m_article WHERE FIND_IN_SET(5,serviceid)
查询 这个字段包含ID为1或者3的sql SELECT * FROM m_article WHERE serviceid REGEXP '(1|3)';
查询 这个字段同时包含ID为1和3的sql SELECT * FROM m_article WHERE serviceid REGEXP '(1,3)';
sql语句添加判断,改变字段的值
SELECT *,IF(g_id=1,"男","女") AS sex FROM m_article
或者
SELECT *,
CASE g_id
WHEN 1 THEN '男'
WHEN 2 THEN '女'
ELSE '变态'
END AS sex
FROM
m_article
测试数据表sql
/*
Navicat MySQL Data Transfer
Source Server : 本机
Source Server Version : 50714
Source Host : localhost:3306
Source Database : ceshi
Target Server Type : MYSQL
Target Server Version : 50714
File Encoding : 65001
Date: 2018-11-12 10:55:02
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for m_article
-- ----------------------------
DROP TABLE IF EXISTS `m_article`;
CREATE TABLE `m_article` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`title` varchar(30) NOT NULL,
`cid` varchar(255) NOT NULL,
`atricle` text NOT NULL,
`g_id` int(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of m_article
-- ----------------------------
INSERT INTO `m_article` VALUES ('1', '标题01', '1,2,3,4,5', 'dwaDADwaDwdawDwd', '1');
INSERT INTO `m_article` VALUES ('2', '标题02', '4,5,6,7,8', '的达瓦大D个人的违法违法', '1');
INSERT INTO `m_article` VALUES ('3', '标题03', '5,6,7,8,9', '定位到无多订单', '2');
INSERT INTO `m_article` VALUES ('4', '标题04', '2,6,7,8,3,9', '切尔奇翁32525', '8');
按某个数组的顺序查询包含的数据
SELECT * FROM kefu WHERE uid IN ('6f26291ebcc511e8b3a206d1d62a5508','24f65f8851fe11e9ba77ec0d9a433264','c3f7ec4012f511e98eceec0d9a43325c') ORDER BY FIELD (uid,'6f26291ebcc511e8b3a206d1d62a5508','24f65f8851fe11e9ba77ec0d9a433264','c3f7ec4012f511e98eceec0d9a43325c')
UPDATE 表1 t1 SET t1.字段值1 =(SELECT t2.字段值2 FROM 表2 t2 WHERE t1.关联字段1 = t2.关联字段2)