mysql 常用查询

shangban

　　1,查询学员中，年龄在20以上的男女生比例
　　数据表如下：

--
-- 表的结构 `students`
--
CREATE TABLE IF NOT EXISTS `students` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`sex` tinyint(4) NOT NULL DEFAULT '1',
`age` int(4) NOT NULL DEFAULT '20',
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
--
-- 转存表中的数据 `students`
--
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(1, 1, 18);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(2, 0, 36);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(3, 1, 15);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(4, 0, 16);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(5, 0, 23);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(6, 1, 26);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(7, 0, 23);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(8, 1, 30);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(9, 0, 19);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(10, 1, 26);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(11, 1, 30);
INSERT INTO `students` (`id`, `sex`, `age`) VALUES(12, 1, 19);
　　

--
--查询语句
--
SELECT sex AS `性别` , cast( sum( IF( age >20, 1, 0 ) ) / count( * ) *100 AS decimal( 5, 2 ) ) AS `比例`
FROM students
GROUP BY sex
　　2,从学生表中，同步用户表数据
　　数据表如下：

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` tinyint(1) DEFAULT NULL,
`class` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '002', 'lifang', '12', 'A3');
INSERT INTO `student` VALUES ('2', '003', 'zhangqi', '23', 'A2');
INSERT INTO `student` VALUES ('3', '012', 'libing', '34', 'S2');
INSERT INTO `student` VALUES ('4', '014', 'zhangbin', '32', 'S2');
INSERT INTO `student` VALUES ('5', '323', 'lili', '23', 'B3');
INSERT INTO `student` VALUES ('6', '056', 'ali', '34', 'B4');
INSERT INTO `student` VALUES ('7', '331', 'wangfang', '33', 'S3');
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`loginname` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`code` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'lifang', '123456', '002');
INSERT INTO `user` VALUES ('2', 'zhangqi', '123456', '003');
INSERT INTO `user` VALUES ('3', 'libing', '123456', '012');
　　插入新学生数据到用户表：

INSERT INTO `user`(`code`,`loginname`,`password`) SELECT  code,name,'123456' FROM `student` WHERE name NOT IN(SELECT loginname FROM `user`)
　　更新用户表登陆名：

UPDATE `user` AS a LEFT JOIN `student` b ON a.`code` = b.`code` SET a.`loginname` = b.`name` WHERE a.`code` = b.`code`

　　
3,查询树型中所有子结点id
　　表及数据

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `folder`
-- ----------------------------
DROP TABLE IF EXISTS `folder`;
CREATE TABLE `folder` (
`folder_id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) DEFAULT NULL,
`folder_name` varchar(100) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
`level` tinyint(1) DEFAULT '1' COMMENT '文件夹层级',
`del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标记 1=删除， 0=未删除',
`updated` int(10) DEFAULT NULL,
`created` int(10) DEFAULT NULL,
PRIMARY KEY (`folder_id`),
KEY `pid` (`pid`),
KEY `userid` (`userid`,`del_flag`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COMMENT='资源文件夹表';
-- ----------------------------
-- Records of folder
-- ----------------------------
INSERT INTO `folder` VALUES ('1', '3', 'jjjjj', '33609', '4', '0', '1450663708', '1450663708');
INSERT INTO `folder` VALUES ('2', '3', 'jjjooiooo', '33609', '4', '0', '1450663712', '1450663712');
INSERT INTO `folder` VALUES ('3', '45', 'oiuiouoiuio', '33609', '3', '0', '1450663715', '1450663715');
INSERT INTO `folder` VALUES ('4', '16', 'ssss', '33635', '2', '0', '1450664103', '1450664103');
INSERT INTO `folder` VALUES ('5', '4', 'ddddd', '33635', '3', '0', '1450664107', '1450664107');
INSERT INTO `folder` VALUES ('6', '0', 'jhk', '288523', '1', '1', '1450664630', '1450664265');
INSERT INTO `folder` VALUES ('7', '1', 'jkhjkhjkh', '33609', '5', '0', '1450664780', '1450664780');
INSERT INTO `folder` VALUES ('8', '0', 'fdsf', '288523', '1', '1', '1450683058', '1450665908');
INSERT INTO `folder` VALUES ('9', '0', 'aaaaaaa', '33635', '1', '0', '1450666624', '1450666624');
INSERT INTO `folder` VALUES ('10', '9', 'a-b', '33635', '2', '0', '1450666641', '1450666641');
INSERT INTO `folder` VALUES ('11', '10', 'a-b-c', '33635', '5', '0', '1450666655', '1450666655');
INSERT INTO `folder` VALUES ('13', '9', 'a---b', '33635', '2', '0', '1450667744', '1450667744');
INSERT INTO `folder` VALUES ('14', '13', 'a---c', '33635', '5', '0', '1450667756', '1450667756');
INSERT INTO `folder` VALUES ('15', '15', 'vvvvvvvvvv', '33635', '1', '0', '1450667829', '1450667829');
INSERT INTO `folder` VALUES ('16', '0', 'qqqqq', '33635', '1', '0', '1450668448', '1450668448');
INSERT INTO `folder` VALUES ('17', '16', 'wwwwwwwwww', '33635', '2', '0', '1450668456', '1450668456');
INSERT INTO `folder` VALUES ('18', '0', 'hh', '287621', '1', '0', '1450839598', '1450669229');
INSERT INTO `folder` VALUES ('19', '0', 'fdsffsfd', '288523', '1', '1', '1450684428', '1450683188');
INSERT INTO `folder` VALUES ('20', '0', 'fsdf', '288523', '1', '1', '1450751544', '1450684434');
INSERT INTO `folder` VALUES ('21', '0', 'dsf', '288523', '1', '1', '1450684513', '1450684440');
INSERT INTO `folder` VALUES ('22', '0', 'dsfds', '288523', '1', '1', '1450751547', '1450684529');
INSERT INTO `folder` VALUES ('23', '0', 'uyg', '33609', '1', '1', '1450687847', '1450685531');
INSERT INTO `folder` VALUES ('24', '0', 'yuguyyu', '33609', '1', '1', '1450687847', '1450685533');
INSERT INTO `folder` VALUES ('25', '0', 'uygygugyug', '33609', '1', '1', '1450687786', '1450685537');
INSERT INTO `folder` VALUES ('26', '0', 'ugyuuyyu', '33609', '1', '1', '1450685548', '1450685540');
　　方法

CREATE  FUNCTION `getFolderChildrenIds`(p int) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sTempChd VARCHAR(4000);
DECLARE sTemp VARCHAR(4000);
set sTemp = '';
SET sTempChd = cast(p as char);
WHILE sTempChd is not NULL DO
if sTemp  != '' then
set sTemp = CONCAT(sTemp,',',sTempChd);
ELSE
set sTemp = sTempChd;
end if;
SELECT group_concat(folder_id) INTO sTempChd FROM tbl_resource_folder where FIND_IN_SET(pid,sTempChd) > 0;
END WHILE;
return sTemp;
END
　　
　　sql中使用

select * from tbl_resource_folder where FIND_IN_SET(folder_id,(select getFolderChildrenIds(9)));