csjoz11

导航

sql语句

 

 
_______________________________________________________________查询每月单日数据
SELECT SQL_CALC_FOUND_ROWS date_format(FROM_UNIXTIME(a.dates),'%Y-%m-%d') AS time,count(DISTINCT u.id) AS users
            FROM admin_date a LEFT JOIN user u ON date_format(FROM_UNIXTIME(a.dates),'%Y-%m-%d') = date_format(FROM_UNIXTIME(u.posttime),'%Y-%m-%d') 
            where 1=1 
            GROUP BY time order by a.dates desc LIMIT 100
______________________________________________________________
 
insert into bank (bank_name,position,logo,bank_type,api) select bank_name,position,logo,bank_type,api from bank where id = 19
 
________________________________________________
 
SELECT COUNT(*) AS n,FROM_UNIXTIME(`posttime`,'%y-%m-%d') AS d FROM `user_bet_log` GROUP BY d ORDER BY d
SELECT COUNT(*) AS n,FROM_UNIXTIME(`posttime`,'%y-%m') AS d FROM `user_bet_log` GROUP BY d ORDER BY d
 
________________________
CREATE TABLE `iwebshop_member` (
  `user_id` int(11) unsigned NOT NULL COMMENT '用户ID',
  `true_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `telephone` varchar(50) DEFAULT NULL COMMENT '联系电话',
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机',
  `area` varchar(255) DEFAULT NULL COMMENT '地区',
  `contact_addr` varchar(250) DEFAULT NULL COMMENT '联系地址',
  `qq` varchar(15) DEFAULT NULL COMMENT 'QQ',
  `sex` tinyint(1) NOT NULL DEFAULT '1' COMMENT '性别1男2女',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `group_id` int(11) DEFAULT NULL COMMENT '分组',
  `exp` int(11) NOT NULL DEFAULT '0' COMMENT '经验值',
  `point` int(11) NOT NULL DEFAULT '0' COMMENT '积分',
  `message_ids` text COMMENT '消息ID',
  `time` datetime DEFAULT NULL COMMENT '注册日期时间',
  `zip` varchar(10) DEFAULT NULL COMMENT '邮政编码',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '用户状态 1正常状态 2 删除至回收站 3锁定',
  `prop` text COMMENT '用户拥有的工具',
  `balance` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '用户余额',
  `last_login` datetime DEFAULT NULL COMMENT '最后一次登录时间',
  `custom` varchar(255) DEFAULT NULL COMMENT '用户习惯方式,配送和支付方式等信息',
  `email` varchar(255) DEFAULT NULL COMMENT 'Email',
  PRIMARY KEY (`user_id`),
  KEY `group_id` (`group_id`),
  KEY `mobile` (`mobile`),
  KEY `email` (`email`),
  KEY `status` (`status`,`true_name`),
  CONSTRAINT `iwebshop_member_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `iwebshop_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

posted on 2017-07-27 11:16  csjoz11  阅读(131)  评论(0编辑  收藏  举报