SQL查询技巧与实践-优快云博客

本文介绍了几种实用的SQL查询技巧，包括如何统计特定条件下的男女学员比例、如何同步学生表和用户表的数据以及如何获取树型结构中所有子节点的ID。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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');

方法

SET GLOBAL log_bin_trust_function_creators = 1;
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)));

mysql 常用查询