mysql-常用表达式(空条件、between..and、in、exist)用例

 

 1、创建表且插入数据

create table t1 (
id int,
name varchar(100),
age int,
sex varchar(20),
address varchar(100),
math int,
english int
)engine=innodb;

insert into t1 values(1,"马云",50,"男","北京",66,78),(2,"马化腾",45,"男","北京",80,91),(3,"刘青",40,"女","上海",60,91),(4,"王石",60,"男","深圳",88,58),(5,"马花",67,"男","北京",57,91),(6,"尚杰",50,"男","北京",66,null);

2、比较运算和逻辑运算符符（between..and,in）

SELECT * FROM t1 WHERE age >= 20 && age <=60;
SELECT * FROM t1 WHERE age >= 20 AND age <=60;
SELECT * FROM t1 WHERE age BETWEEN 20 AND 60;

SELECT * FROM t1 WHERE age = 45 OR age = 50 OR age = 90;
SELECT * FROM t1 WHERE age IN (45,50,90);

 

 3、列表运算符（in）

select * from t1 where address in("北京","上海");

 

 4、空值判断（null）

SELECT * FROM t1 WHERE english IS NULL;

 

 5、存在条件（exists）

SELECT age FROM t1 where EXISTS (SELECT age FROM t1 where age BETWEEN 20 AND 60);