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 studentsGROUP 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`