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='用户信息表';