1、新建数据库表DROP TABLE 诔罨租磊IF EXISTS `study_1`;CREATE TABLE `study_1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` varchar(5) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;数据库表中插入测试数据INSERT INTO study_1 VALUES ('1', '张三', '20', '男');INSERT INTO study_1 VALUES ('2', '李四', '21', '女');INSERT INTO study_1 VALUES ('3', '王五', '22', '男');INSERT INTO study_1 VALUES ('4', '赵六', '23', '女');INSERT INTO study_1 VALUES ('5', '吴七', '24', '女');INSERT INTO study_1 VALUES ('6', '钱八', '25', '男');INSERT INTO study_1 VALUES ('7', '孙九', '26', '男');
2、查询表中所有字段的数据（示例结果如图按顺序）以通配符 * 查询示例SELECT * FROM study_1;以表中所有字段查询示例SELECT id,name,age,sex FROM study_1;
3、查询单个或多个字段（示例结果如图按顺序）查询单个字段示例SELECT name FROM study_1;查询多个字段示例SELECT name,age FROM study_1;
4、使用WHERE条件查询指定记录（示例结果如图按顺序）示例1SELECT * FROM study_1 WHERE age>22;示例2SELECT name,sex FROM study_1 WHERE sex='男';
5、带IN关键字的查询（示例结果如图按顺序）查询字段值在指定范围内的数据示例SELECT * FROM study_1 WHERE age IN (20,21,22);查询字段值不在指定范围内的数据SELECT * FROM study_1 WHERE age NOT IN (20,22,24);
6、使用BETWEEN AND查询摸个范围内的值（示例结果如图按顺序）示例SELECT * FROM study_1 WHERE age BETWEEN 20 AND 25;
7、使用LIKE进行字符串匹配查询（示例结果如图按顺序）通配符 % 匹配示例一，向后匹配SELECT * FROM study_1 WHERE name LIKE '张%';示例二，向前匹配SELECT * FROM study_1 WHERE name LIKE '%四';示例三，前后匹配SELECT * FROM study_1 WHERE name LIKE '%五%';下划线匹配SELECT * FROM study_1 WHERE name LIKE '张_';
8、查询空数据（示例结果如图按顺序）往表中再插入一条sex值为控制的数据INSERT INTO `study_1` VALUES ('8', '周十', '27', null);示例SELECT * FROM study_1 WHERE sex IS NULL;示例2SELECT * FROM study_1 WHERE sex IS NOT NULL;
9、AND多条件与查询，OR多条件或查询（示例结果如图按顺序）注意1：AND优先级大于OR注意2：IN查询能实现OR查询的效果，而且执行熟度更快AND查询示例SELECT * FROM study_1 WHERE age > 22 AND sex IS NOT NULL;OR查询示例SELECT * FROM study_1 WHERE age < 23 OR age > 26;
10、使用DISTINCT查询不重复的数据（示例结果如图按顺序）插入一条与id=1数据内容一致的数据I鲍伊酷雪NSERT INTO `study_1` VALUES (&#泌驾台佐39;9', '张三', '20', '男');普通查询能查询到两条相同的数据示例SELECT name FROM study_1 WHERE name='张三';使用DISTINCT剔除相同的数据示例SELECT DISTINCT name FROM study_1 WHERE name='张三';
11、对查询出来的数据按列值进行排序（示例结果如图按顺序）不指明顺序默认按升序排序SELECT * FROM study_1 WHERE age > 21 ORDER BY id;按升序排序SELECT * FROM study_1 WHERE age > 21 ORDER BY id ASC;按倒序排序SELECT * FROM study_1 WHERE age > 21 ORDER BY id DESC;
12、使用 [GROUP BY <字段>] [HAVING <查询表达式>] 旅邯佤践进行分组查询（示例结果如掬研疔缎图按顺序）新增分组字段ALTER TABLE study_1 ADD study_group int(11);为原有的数据分组UPDATE study_1 set study_group=1 where id<3;UPDATE study_1 set study_group=2 where id>=3 AND id<7;UPDATE study_1 set study_group=3 where id>=7;示例1.把各分组的姓名按分组查询并组合起来。SELECT GROUP_CONCAT(name) AS names,study_group FROM study_1 GROUP BY study_group;示例2.把各分组的姓名按分组查询并组合起来,并使用HAVING限制查询分组人数在2人以上的数据。SELECT GROUP_CONCAT(name) AS names,study_group,sex FROM study_1 GROUP BY study_group HAVING COUNT(name)>2;示例3.把各分组的姓名按分组查询并组合起来,WITH ROLLUP在最后一条显示所有记录。SELECT GROUP_CONCAT(name) AS names,study_group FROM study_1 GROUP BY study_group WITH ROLLUP;示例4.多字段分组SELECT GROUP_CONCAT(name) AS names,study_group,sex FROM study_1 GROUP BY study_group,sex;
13、使用 [LIMIT [<从第几行开始>],<查询数据行数>] 查询指定条数的数据示例1.不指定开始位置默认从第一条开始(位置偏移量是0)SELECT * FROM study_1 LIMIT 3;示例2.指定开始位置SELECT * FROM study_1 LIMIT 1,3;
