mysql 常用查询

最新推荐文章于 2025-07-31 14:52:32 发布

原创最新推荐文章于 2025-07-31 14:52:32 发布 · 210 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 同时被 2 个专栏收录

7 篇文章

订阅专栏

数据结构

2 篇文章

订阅专栏

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