【MySQL_学习笔记】2021.8.10

•  where语句（筛选条件）

SELECT * FROM person_sales WHERE grade=3

SELECT * FROM person_sales WHERE grade>3

SELECT * FROM person_sales WHERE sale_amount <= 50000

SELECT * FROM person_sales WHERE grade<>3

SELECT * FROM person_sales WHERE grade!=3

备注：不等于可以用 <> 或 != 表示

SELECT * FROM person_sales WHERE staff_name='张进'

 备注：如果想表示文本内容，必须要加单引号''或双引号""，将其表示成字符串

SELECT * FROM person_sales where grade=3 ORDER BY sale_amount DESC LIMIT 10

备注：顺序——select 字段 FROM表名 where 筛选条件 order by 排序字段 Limit 记录数量

SELECT * FROM person_sales where sale_amount=0 ORDER BY grade LIMIT 1

• NULL值的处理

代表该字段为空，没有填写过任何内容（空不是0）

SELECT * FROM person_sales where sale_amount IS null

备注：判断空值不能用 =! ，需要用 IS NULL

• 逻辑操作符 and or not

SELECT * FROM gb_members WHERE grade=2 OR grade=3

SELECT * FROM gb_members WHERE grade=3 AND income>2000

SELECT * FROM gb_members 
    WHERE (grade=3 AND income>2900
                OR grade=2 AND income>2950)
            AND kill_enemy>3

备注：（1）逻辑运算优先级 括号 > not > and > or（2）必须是半角括号（3）只用圆角括号，不能使用方括号、花括号

SELECT * FROM gb_members 
    WHERE NOT((grade=3 AND income>2900
                OR grade=2 AND income>2950)
            AND kill_enemy>3)

SELECT * FROM gb_members 
    WHERE (grade=3 AND income>2900
                OR grade=2 AND income>2950)
            AND kill_enemy>3
            AND (kitchen_skill='白斩鸡' OR kitchen_skill='叫花鸡')
    ORDER BY income DESC, kill_enemy desc
    LIMIT 10

SELECT * FROM gb_members 
    WHERE (grade=3 AND income>2900
                OR grade=2 AND income>2950)
            AND kill_enemy>3
            AND kitchen_skill IN ('白斩鸡','叫花鸡')
    ORDER BY income DESC, kill_enemy desc
    LIMIT 10

备注：x in (a,b,c,…) 只要x的值等于括号中任意一项，即视为符合条件，相当于x=a or x=b or x=c