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

傷城~ 2021-09-07 08:16 461阅读 0赞

创建学生表：  
根据图创建数据表，进行查询练习  
![在这里插入图片描述][20210413233239719.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70]  
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有帮助

[20210413233239719.png]: /images/20210813/4ef73b3a78394333a12ae0f11cfb5c9e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70]: /images/20210813/ec168e281e90437bac457fd4c6522690.png