- 比较运算符
- 算术运算符
- 逻辑运算符
- 位运算符
1、比较运算符
-- 查询年龄大于12岁的所有学生
SELECT * FROM school_student_info WHERE age >= 12;
-- 查询年龄在9-12之间（包含）的学生信息
SELECT * FROM school_student_info WHERE age BETWEEN 9 AND 12;
-- 查询未设置/已设置年龄的学生信息
SELECT * FROM school_student_info WHERE age IS NULL;
SELECT * FROM school_student_info WHERE age IS NOT NULL;
-
在/不在列表内
IN (a, b, c) /NOT IN (a, b, c)
SELECT 12 IN (1, 2, 3, 4, 5);
-
最大值
GREATEST(a, b, c)
-
最小值
LEAST(a, b, c)
SELECT GREATEST(6,8,10);
SELECT LEAST(6,8,10);
SELECT GREATEST(id, age) FROM school_student_info;
-- 查找所有姓李的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '李%';
-- 查找所有姓李.名只有一个字的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '李_';
-- 查找姓名中包含“雪”字的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '%雪%';
2、算术运算符
SELECT 3 + 2;
SELECT 3 * 2;
-- 将学生的年龄加10岁进行展示
SELECT *, age + 10 FROM school_student_info;
SELECT * FROM school_student_grade WHERE course_id = 2;
-- 将数学成绩加5分
UPDATE school_student_grade SET score = score + 5 WHERE course_id = 2;
3、逻辑运算符
- 与关系/且关系（全部满足） 用 AND 或者 && 表示
- 非关系 用 NOT 或者 ! 表示
- 或关系（部分满足） 用 OR 或者 || 表示
- 异或XOR
SELECT 20 && 30;
-- 查询所有年龄在9岁到12岁之间的女生
SELECT * FROM school_student_info WHERE AGE BETWEEN 9 AND 12 AND sex = '女';
SELECT * FROM school_student_info WHERE AGE BETWEEN 9 AND 12 && sex = '女';
SELECT * FROM school_student_info WHERE age >= 9 AND age <= 12 && sex = '女';
-- 查询所有12岁以上的男生和9岁以下的女生
SELECT * FROM school_student_info WHERE age > 12 and sex = '男';
SELECT * FROM school_student_info WHERE age < 9 and sex = '女';
SELECT * FROM school_student_info WHERE (age > 12 and sex = '男') OR (age < 9 and sex = '女');
-- 查询所有“李”姓的女生中年龄不为空的学生信息
SELECT * FROM school_student_info WHERE stu_name LIKE '李%' AND sex = '女' AND age IS NOT NULL;
