【SQL基础】【关键字大写】条件查询：比较、不等于、IN、为空、BETWEEN

〇、概述

1、内容介绍

条件查询：比较、不等于、IN、为空、BETWEEN

2、建表语句

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
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);

一、基础排序

1、查找后排序

SELECT
    device_id,
    age
FROM user_profile
ORDER BY age ASC;

2、查找后多列排序

SELECT
    device_id,
    gpa,
    age
FROM user_profile
ORDER BY 
    gpa ASC,
    age ASC;

3、查找后降序排序

SELECT
    device_id,
    gpa,
    age
FROM user_profile
ORDER BY
    gpa DESC,
    age DESC;

二、基础操作符

1、查找学校是北大的学生信息【字符串用单引号引起来】

SELECT
    device_id,
    university
FROM user_profile
where
    university='北京大学';

2、查找年龄大于24的用户信息

SELECT
    device_id,
    gender,
    age,
    university
FROM user_profile
WHERE age>24;

3、查找某个年龄段的用户信息

SELECT
    device_id,
    gender,
    age
FROM user_profile
WHERE age BETWEEN 20 and 23;

4、查找除复旦大学的用户信息

方法1：使用NOT IN

SELECT
    device_id,
    gender,
    age,
    university
FROM user_profile
WHERE university 
NOT IN ('复旦大学');

方法2：!=

SELECT
    device_id,
    gender,
    age,
    university
FROM user_profile
WHERE university !='复旦大学';

5、用where过滤空值练习【不为空使用IS NOT NULL表示】

SELECT
    device_id,
    gender,
    age,
    university
FROM user_profile
WHERE age IS NOT NULL;

三、高级操作符

1、高级操作符练习(1)

找到男性且GPA在3.5以上(不包括3.5)的用户

SELECT
    device_id,
    gender,
    age,
    university,
    gpa
FROM user_profile
WHERE 
    gender='male' 
and 
    gpa>3.5;

2、高级操作符练习(2)

学校为北大或GPA在3.7以上

SELECT
    device_id,
    gender,
    age,
    university,
    gpa
FROM user_profile
WHERE
    university='北京大学'
or
    gpa>3.7;

3、Where in 和Not in

SELECT
    device_id,
    gender,
    age,
    university,
    gpa
FROM user_profile
WHERE university IN('北京大学','复旦大学','山东大学');

4、操作符混合运用

SELECT
   device_id,
   gender,
   age,
   university,
   gpa
FROM user_profile
WHERE 
    (gpa>3.5 and university='山东大学')
    or
    (gpa>3.8 and university='复旦大学');

5、查看学校名称中含北京的用户

SELECT
    device_id,
    age,
    university
FROM user_profile
WHERE university like '%北京%';