Navicat转储为sql的代码:
/*
Navicat MySQL Data TransferSource Server : localhost
Source Server Version : 50045
Source Host : localhost:3306
Source Database : demoTarget Server Type : MYSQL
Target Server Version : 50045
File Encoding : 65001Date: 2013-10-28 19:12:23
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `tab_prop`
-- ----------------------------
DROP TABLE IF EXISTS `tab_prop`;
CREATE TABLE `tab_prop` (
`prop_id` int(11) NOT NULL auto_increment COMMENT '道具id',
`tp_name` varchar(16) default NULL COMMENT '道具名称',
PRIMARY KEY (`prop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100005 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tab_prop
-- ----------------------------
INSERT INTO `tab_prop` VALUES ('1001', '道具一');
INSERT INTO `tab_prop` VALUES ('1002', '道具二');
INSERT INTO `tab_prop` VALUES ('1003', '道具三');
INSERT INTO `tab_prop` VALUES ('1004', '道具四');-- ----------------------------
-- Table structure for `tab_user_from_prop`
-- ----------------------------
DROP TABLE IF EXISTS `tab_user_from_prop`;
CREATE TABLE `tab_user_from_prop` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL COMMENT '用户id',
`prop_id` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `FK639E5F38625FF48` (`prop_id`),
KEY `FK639E5F38923868FB` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=944 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tab_user_from_prop
-- ----------------------------
INSERT INTO `tab_user_from_prop` VALUES ('1', '1', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('2', '1', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('3', '1', '1002');
INSERT INTO `tab_user_from_prop` VALUES ('4', '2', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('5', '3', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('6', '3', '1002');-- ----------------------------
-- Table structure for `tab_use_prop`
-- ----------------------------
DROP TABLE IF EXISTS `tab_use_prop`;
CREATE TABLE `tab_use_prop` (
`us_id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL COMMENT '用户ID',
`prop_id` int(11) default NULL COMMENT '道具id',
`use_type` tinyint(4) default NULL COMMENT '道具使用方式,0代表打怪,1代表升级',
PRIMARY KEY (`us_id`),
KEY `INDEX_PLAYERID` (`user_id`),
KEY `INDEX_ITEMID` (`prop_id`),
KEY `INDEX_USE_TYPE` (`use_type`)
) ENGINE=InnoDB AUTO_INCREMENT=734 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tab_use_prop
-- ----------------------------
INSERT INTO `tab_use_prop` VALUES ('1', '1', '1002', '1');
INSERT INTO `tab_use_prop` VALUES ('2', '1', '1002', '1');
INSERT INTO `tab_use_prop` VALUES ('3', '1', '1002', '0');
INSERT INTO `tab_use_prop` VALUES ('4', '4', '1002', '0');
INSERT INTO `tab_use_prop` VALUES ('5', '5', '1002', '0');
INSERT INTO `tab_use_prop` VALUES ('6', '6', '1001', '0');
INSERT INTO `tab_use_prop` VALUES ('7', '2', '1002', '1');
INSERT INTO `tab_use_prop` VALUES ('8', '2', '1001', '0');
INSERT INTO `tab_use_prop` VALUES ('9', '3', '1003', '0');
mysql
/*
Navicat MySQL Data TransferSource Server : localhost
Source Server Version : 50045
Source Host : localhost:3306
Source Database : demoTarget Server Type : MYSQL
Target Server Version : 50045
File Encoding : 65001Date: 2013-10-28 19:12:23
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `tab_prop`
-- ----------------------------
DROP TABLE IF EXISTS `tab_prop`;
CREATE TABLE `tab_prop` (
`prop_id` int(11) NOT NULL auto_increment COMMENT '道具id',
`tp_name` varchar(16) default NULL COMMENT '道具名称',
PRIMARY KEY (`prop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=100005 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tab_prop
-- ----------------------------
INSERT INTO `tab_prop` VALUES ('1001', '道具一');
INSERT INTO `tab_prop` VALUES ('1002', '道具二');
INSERT INTO `tab_prop` VALUES ('1003', '道具三');
INSERT INTO `tab_prop` VALUES ('1004', '道具四');-- ----------------------------
-- Table structure for `tab_user_from_prop`
-- ----------------------------
DROP TABLE IF EXISTS `tab_user_from_prop`;
CREATE TABLE `tab_user_from_prop` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL COMMENT '用户id',
`prop_id` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `FK639E5F38625FF48` (`prop_id`),
KEY `FK639E5F38923868FB` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=944 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tab_user_from_prop
-- ----------------------------
INSERT INTO `tab_user_from_prop` VALUES ('1', '1', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('2', '1', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('3', '1', '1002');
INSERT INTO `tab_user_from_prop` VALUES ('4', '2', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('5', '3', '1001');
INSERT INTO `tab_user_from_prop` VALUES ('6', '3', '1002');-- ----------------------------
-- Table structure for `tab_use_prop`
-- ----------------------------
DROP TABLE IF EXISTS `tab_use_prop`;
CREATE TABLE `tab_use_prop` (
`us_id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL COMMENT '用户ID',
`prop_id` int(11) default NULL COMMENT '道具id',
`use_type` tinyint(4) default NULL COMMENT '道具使用方式,0代表打怪,1代表升级',
PRIMARY KEY (`us_id`),
KEY `INDEX_PLAYERID` (`user_id`),
KEY `INDEX_ITEMID` (`prop_id`),
KEY `INDEX_USE_TYPE` (`use_type`)
) ENGINE=InnoDB AUTO_INCREMENT=734 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of tab_use_prop
-- ----------------------------
INSERT INTO `tab_use_prop` VALUES ('1', '1', '1002', '1');
INSERT INTO `tab_use_prop` VALUES ('2', '1', '1002', '1');
INSERT INTO `tab_use_prop` VALUES ('3', '1', '1002', '0');
INSERT INTO `tab_use_prop` VALUES ('4', '4', '1002', '0');
INSERT INTO `tab_use_prop` VALUES ('5', '5', '1002', '0');
INSERT INTO `tab_use_prop` VALUES ('6', '6', '1001', '0');
INSERT INTO `tab_use_prop` VALUES ('7', '2', '1002', '1');
INSERT INTO `tab_use_prop` VALUES ('8', '2', '1001', '0');
INSERT INTO `tab_use_prop` VALUES ('9', '3', '1003', '0');
mysql
解决方案 »
- 数据库设计思路!!!
- 做完了主从复制功能,现在每天要如何维护呢?
- 内外网的数据库同步
- windows客户端如何通过程序访问unix/linux下的数据库(如mysql,postgresql,oracle)
- 请教前辈们... 关于 数据表 utf8 字符集的问题
- POSTGRESQL 中如何数据备份?怎么总出现下面的问题,请高手解决...
- 在那里设置MYSQL可使用的最大内存,我的MYSQL闲着内在不用去用虚拟内存
- 请帮我解释一下mysql中:
- 请问使用C语言通过ODBC链接mysql在非VC6的环境中该怎么做?
- 查看 mysql优化器 优化之后的sql语句用什么命令
- 请教表结构设计
- mysql表分区怎样设计性能才好?
SELECT ifs.prop_id,si.tp_name,COUNT(ifs.prop_id),
(SELECT COUNT(*) FROM tab_use_prop lui WHERE lui.user_id=ifs.user_id AND lui.use_type=0 GROUP BY ifs.prop_id),
(SELECT COUNT(*) FROM tab_use_prop lui WHERE lui.user_id=ifs.user_id AND lui.use_type=1 GROUP BY ifs.prop_id)
FROM tab_user_from_prop ifs,tab_prop si
where 1=1
AND si.prop_id=ifs.prop_id
AND ifs.user_id=1
GROUP BY ifs.prop_id