建表语句：

 CREATE TABLE `students`  (

  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘姓名’,

  `age` int(11) NULL DEFAULT NULL COMMENT ‘年龄’,

  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘M=男, F=女’

) ENGINE = InnoDB CHARACTER SET = utf8;

— 学生表数据：

INSERT INTO `students` VALUES (‘张一’, 12, ‘男’);

INSERT INTO `students` VALUES (‘张二’, 18, ‘男’);

INSERT INTO `students` VALUES (‘张二’, 19, ‘男’);

INSERT INTO `students` VALUES (‘张三’, 122, ‘男’);

INSERT INTO `students` VALUES (‘张四’, 123, ‘男’);

INSERT INTO `students` VALUES (‘张五’, 161, ‘男’);

INSERT INTO `students` VALUES (‘张六’, 66, ‘男’);

INSERT INTO `students` VALUES (‘张七’, 35, ‘女’);

INSERT INTO `students` VALUES (‘张八’, 55, ‘男’);

INSERT INTO `students` VALUES (‘张九’, 56, ‘男’);

INSERT INTO `students` VALUES (‘张十’, 96, ‘男’);

INSERT INTO `students` VALUES (‘张十一’, 11, ‘男’); 

1. 1)查询表中所有数据
   SELECT * FROM students

   2)显示students表中的NAME和age列

   SELECT `name`,age FROM students;

   3)相同的名字只显示一次

   SELECT * FROM students GROUP BY `name`

   4)查询表中年龄小于16岁的数据

   SELECT * FROM students WHERE age < 16

   5) 查询年龄大于18岁的数据, 并按倒序排列

   SELECT * FROM students WHERE  age > 18 ORDER BY age DESC

   6)查询年龄在18-30之间的数据(包括18, 30)

   SELECT * FROM students  WHERE  age BETWEEN 18 AND 30  

   #  查询年龄在18-30之间的数据(不包括18, 30)

   SELECT * FROM students  WHERE  age >18  AND age <30

   7)查询所有年龄不是66岁的数据

   SELECT * FROM  students WHERE age !=66

   8)查询所有姓名包括”一”的数据

   SELECT * FROM students WHERE `name` LIKE  ‘%一%’ 

   9)统计表中男, 女的数量分别是多少

   SELECT gender 性别,COUNT(*) 数量 FROM students  GROUP BY gender   

   10)查询表中所有数据平均年龄

   SELECT  AVG(age) 平均年龄 FROM  students  

   11)查询最大年龄
   SELECT MAX(age) FROM students  

   # 延伸 查询最大年龄的所有信息
   SELECT  *  FROM students   WHERE   age=(SELECT MAX(age) FROM  students)

   # 查询最小年龄 
   SELECT MIN(age) FROM students