is not null不为空的数据命令查询 输入数据 create table t1(id int, name varchar(10)); insert into t1(id) values(1),(2); insert into t1 values(3,'tongyao');
select * from t1 where name is not null; 表中names不为空的数据导出 select * from t1 where name is null; 表中names为空的数据导出 ```
MySQl数据的逻辑判断
```mysql and or not 按照条件来判断: select * from students where 16<=age and age<=21; select * from students where age<=16 or age >=22; select * from students where not age=16;
```
MySQL数据的排序
```mysql order by
正序select * from students order by age; 倒序select * from students order by age desc; ```
MySQL数据库的限制
```mysql limit
展示5条数据 select * from students limit 5;
从第4条数据开始输出3条 select * from students limit 3,3; ```
MySQL数据库的去重
```mysql distinct
select distinct subject_number from grades;
```
MySQL的 模糊查询
```mysql like select * from students where name like 'xi%'; # % 任意多个 select * from students where name like 'li__'; # _ 匹配任意一个 ```
MySQL的范围查询
```mysql between # age 在 (1,16,17,22) 这里面的就全部展示 select * from students where age in (1,16,17,22);
# 一个区间内所有的数据 16到22所有的数据 select * from students where age between 16 and 22;
```
MySQL的聚合函数
```mysql select count(*) from students; select count(name) from students; select sum(age) from students; select avg(age) from students; () ```
MySQL的分组
```mysql # 通过 subject_number,grade 进行分组 然后做筛选 select subject_number,grade,count(*) from grades group by subject_number,grade having grade>=80;
where having ```
MySQL的子查询
```mysql # 每一个派生表 必须有自己的名字 as tongyao 这种方式 select * from (select * from students order by age limit 5) as tongyao where age <18;