mysql 查询笔记

//数据库保存的为时间戳
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)