MySql 数据库 比较运算符

1、数值比较

运算符

>  >=  
<  <=  
=  != 

实例

'1.查询成绩及格的学生'
select * from students where score > 60; 
'2.删除成绩不及格的学生'
delete from students where score < 60;
'3.把id为3的学生的姓名改为 周芷若'
update students set name='周芷若' where id=3;

2、逻辑比较

运算符

and 
or 

实例

'查询成绩不及格 或 是男生的学生'
  select * from students where score <60 or gender='male';
'查询成绩在60-70之间的学生'
  select * from students where score >=60 and score <= 70;

3、范围内

运算符

between value1 and value2 
in() 
not in()

实例

'查询不及格的学生姓名及成绩'
 select name, score from students where score between 0 and 59;
'查询AID19和AID18班的学生姓名及成绩'
 select name,score from students where class  in('AID19', 'AID18');

4、模糊比较

运算符

where 字段名 like 表达式(%_)

实例

'查询姓赵的学生信息'
 select * from students where name like '赵%'

5、NULL判断

运算符

is NULL
is not NULL

实例

'查询姓名字段值为NULL的学生信息'
select * from students where name is NULL;

查询 order by 和 limit

order by 排序

格式

order by 字段名 ASC/DESC 升序/降序

实例

'查询成绩从高到低排列'
select * from students order by score DESC;

limit 输出个数

格式

limit n 显示前n条
limit m, n 从第(m+1)条记录开始，显示n条

'查询成绩从高到低排列  前三名'
select * from students order by score DESC limit 3;