MySQL条件查询练习题（含答案）

创建学生表：
根据图创建数据表，进行查询练习


1.查询性别为女，并且年龄50的记录

2.查询学号为S_1001，或者姓名为liSi的记录

3.查询学号为S_1001，S_1002，S_1003的记录

4.查询学号不是S_1001，S_1002，S_1003的记录

5.查询年龄为null的记录

6.查询年龄在20到40之间的学生记录

7.查询性别非男的学生记录

8.查询姓名不为null的学生记录

创建语句答案：

CREATE TABLE stu (
sidCHAR(6),
snameVARCHAR(50),
ageINT,
genderVARCHAR(50)
);

添加语句答案：

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

查询答案：

1.查询性别为女，并且年龄50的记录

SELECT * FROM stu 
WHERE gender='female' AND ge<50;

2.查询学号为S_1001，或者姓名为liSi的记录
SELECT * FROM stu 
WHERE sid ='S_1001' OR sname='liSi';

3.查询学号为S_1001，S_1002，S_1003的记录
SELECT * FROM stu 
WHERE sid IN ('S_1001','S_1002','S_1003');

4.查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM tab_student 
WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

5.查询年龄为null的记录
SELECT * FROM stu
WHERE age IS NULL;

6.查询年龄在20到40之间的学生记录
SELECT * 
FROM stu
WHERE age>=20 AND age<=40;
或者
SELECT * 
FROM stu 
WHERE age BETWEEN 20 AND 40;   

7.查询性别非男的学生记录
SELECT * 
FROM stu
WHERE gender!='male';
或者
SELECT * 
FROM stu
WHERE gender<>'male';
或者
SELECT * 
FROM stu
WHERE NOT gender='male';

8.查询姓名不为null的学生记录
SELECT * 
FROM stu
WHERE sname IS NOT NULL;
或者
SELECT * 
FROM stu
WHERE NOT sname IS NULL;

希望大家对大家学习MySQL有帮助