MYSQL學習筆記6: DQL條件查詢（where）

HIK4RU44發表於2024-03-08
MySql筆記

DQL條件查詢（where）

查詢為空is null
#使用'is'而不是'='
select * from workers where idCard is null;
查詢非空is not null
select * from workers where idCard is not null;
!=的其他表示方法<>
select * from workers where age <> 90;
查詢資料範圍
#格式
select * from 表名 where 欄位 between 最小值 and 最大值;

如果最小值>最大值, 查詢不到結果

# BETWEEN AND
select *  from workers where age BETWEEN 15 AND 99;
# 
select *  from workers where age>=15 and age<=99;
#
select *  from workers where age>=15 && age<=99;
#BETWEEN 最小值 AND 最大值
#如果最小值<最大值,查詢不到結果
select *  from workers where age between 100 and 10;
或
select distinct * from workers where age=18 or age=20 or age=100;
# in+列表
select distinct * from workers where age in(18,20,100);
模糊查詢
#查詢名字長度為2的員工
select * from workers where name like '__';
#查詢名字長度為3的員工
select * from workers where name like '___';
#身份證最後一位是'X'的員工
select * from workers where idCard like '%X';#百分號,代表任意的字元
#寫了17個下劃線... (每個下劃線代表1個字元)
select * from workers where idCard like '_________________X';

相關文章