mysql 惯用查询

mysql 常用查询

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`