MySQL 查询常用操作(2) —— 条件查询 where

MySQL中常用的查询操作，首先是能直接从表中直接取出数据，接着能对查询结果做一些简单的处理，比如去重等，然后是根据条件查询数据，包括精准查询、模糊查询以及按照数据的某个范围或者指定多个指标进行查询，值得注意的是，MySQL中的一些查询会忽略空值。最后是将查询结果进行排序，主要包括升序和降序以及多个字段的先后排序处理。

第二部分主要介绍where语句——按照条件筛选数据。

一、具体方法

条件查询

select … where查询的基本语法

select * from 表名 where 条件;

条件使用where子句对表中的数据筛选，结果为true的行会出现在结果集中。

注意：

分为between、in、>、like null等

问题	类型	方法
查找学校是北大的学生信息	精准查询	where =、where in
查找除复旦大学的用户信息	非、比较运算符	<>、!=、not in
查找某个年龄段的用户信息	某个范围	>= 和 <=、between
找到学校为北大、复旦和山大的用户信息	同一列多指标查询	Where in
查看学校名称中含北京的用户	模糊查询	like
取出所有年龄值不为空的用户	过滤空值	<> ‘null’、!= ‘null’、is not null
寻找用户推荐人	对空值的处理	where 对空值的处理

二、实例

已知有用户信息表user_profile（device_id 设备ID, gender 性别, age 年龄, university 学校, gpa）

id	device_id	gender	age	university	gpa
1	2138	male	21	北京大学	3.4
2	3214	male		复旦大学	4.0
3	6543	female	20	北京大学	3.2
4	2315	female	23	浙江大学	3.6
5	5432	male	25	山东大学	3.8
6	2131	male	28	北京师范大学	3.3

题目1：取出所有北京大学的学生的数据，结果返回设备id和学校

【分类】：精准查询

【知识点】：=、in

求解代码

# 使用 =
select 
    device_id,
    university
from user_profile
where university = '北京大学';
# 使用 in
select 
    device_id,
    university
from user_profile
where university in ('北京大学');

结果

device_id	university
2138	北京大学
6543	北京大学

---

题目2：取出除复旦大学以外的所有用户明细，结果返回设备id和学校

【分类】：非、比较运算符

【知识点】：<>、!=、not in

求解代码

# 使用 <>
select 
    device_id,
    university
from user_profile
where university <> '复旦大学';
# 使用 !=
select 
    device_id,
    university
from user_profile
where university != '复旦大学';
# 使用 not in
select 
    device_id,
    university
from user_profile
where university not in('复旦大学');

结果

device_id	university
2138	北京大学
6543	北京大学
2315	浙江大学
5432	山东大学
2131	北京师范大学

---

题目3： 取出用户年龄在20岁及以上且23岁及以下的设备ID、性别、年龄的数据

【分类】：某个范围

【知识点】： >= 和 <=、between

between: 闭区间，包括左右两边的数。

求解代码

# 使用 \>= 和 <=
select
    device_id,
    gender,
    age
from user_profile
where age >= 20 and age <= 23;

# 使用 between and
select
    device_id,
    gender,
    age
from user_profile
where age between 20 and 23;

结果

device_id	gender	age
2138	male	21
6543	female	20
2315	female	23

---

题目4： 取出学校为北大、复旦和山大的同学的相关数据

【分类】：同一列多指标查询

【知识点】：Where in

求解代码

select
    device_id,
    gender,
    age,
    university,
    gpa
from user_profile
where university in('北京大学','复旦大学','山东大学');

结果

device_id	gender	age	university	gpa
2138	male	21	北京大学	3.4
3214	male		复旦大学	4.0
6543	female	20	北京大学	3.2
5432	male	25	山东大学

---

题目5：取出所有大学中带有北京的用户的device_id、age、university的信息

【分类】：模糊查询

【知识点】：like

求解代码

select
    device_id,
    age,
    university
from user_profile
where university like '%北京%';

结果

device_id	age	university
2138	21	北京大学
6543	20	北京大学
2131	28	北京师范大学

---

题目6：取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息

【分类】：过滤空值

【知识点】：<> ‘null’、!= ‘null’、is not null

求解代码

# 使用 is not null
select 
    device_id,
    gender,
    age,
    university
from user_profile
where age is not null;
# 使用 != 'null'
select 
    device_id,
    gender,
    age,
    university
from user_profile
where age != 'null';
# 使用 <> 'null'
select 
    device_id,
    gender,
    age,
    university
from user_profile
where age <> 'null';

结果

device_id	gender	age	university
2138	male	21	北京大学
6543	female	20	北京大学
2315	female	23	浙江大学
5432	male	25	山东大学
2131	male	28	北京师范大学

---

题目7：取出用户信息表中年龄 不是 20 的用户device_id、age、university信息

【分类】：对空值的处理

【知识点】：like

注意:
1. 使用in时，忽略null值，不会查询条件为null的数据；
2. 使用not in时，如果 not in后面的括号中没有null，会查询条件列中符合要求的数据，但会过滤掉条件为null的数据；
3. 使用not in时，如果 not in后面的括号中有null，直接返回false，查询结果为空。

求解代码

# 错误解法
select
	device_id,
    age,
    university
from user_profile
where age <> 20;
# 正确解法
select
	device_id,
    age,
    university
from user_profile
where ifnull(age,1) <> 20;

结果

device_id	age	university
2138	21	北京大学
3214	None	复旦大学
2315	23	浙江大学
5432	25	山东大学
2131	28	北京师范大学

---

创建 user_profile 表的代码

-- ----------------------------
-- Table structure for user_profile
-- ----------------------------
DROP TABLE IF EXISTS `user_profile`;
CREATE TABLE `user_profile`  (
  `id` int(11) NOT NULL,
  `device_id` int(11) NOT NULL,
  `gender` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `university` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `gpa` float NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_profile
-- ----------------------------
INSERT INTO `user_profile` VALUES (1, 2138, 'male', 21, '北京大学', 3.4);
INSERT INTO `user_profile` VALUES (2, 3214, 'male', NULL, '复旦大学', 4);
INSERT INTO `user_profile` VALUES (3, 6543, 'female', 20, '北京大学', 3.2);
INSERT INTO `user_profile` VALUES (4, 2315, 'female', 23, '浙江大学', 3.6);
INSERT INTO `user_profile` VALUES (5, 5432, 'male', 25, '山东大学', 3.8);
INSERT INTO `user_profile` VALUES (6, 2131, 'male', 28, '北京师范大学', 3.3);

SET FOREIGN_KEY_CHECKS = 1;