数据库2.0

2022/08/06
   
单表查询
select * from 表名;：查看表中所有数据
select name,class from 表名;：取多个字段值
select id from dcs20 where name='dayuan';：查询name等于dayuan的数据
select * from dcs20 where name !='dayuan';：查询name不等于dayuan的数据
select * from dcs20 where name <>'dayuan';查询name不等于dayuan的数据
select * from dcs20 where class=1001 and yuwen=74;：查询同时满足多个条件的数据
select * from dcs20 where class=1001 or yuwen=77;查询满足至少一个数据
select * from dcs20 where id>4;：查询id大于4 的数据
select * from dcs20 where id>2 and id<6;：查询id大于2且id小于6的数据
select * from dcs20 where id between 2 and 4;：查询id大于大于2且小于等于6的数据
   
select * from dcs20 where class in(1001,1002);
select * from dcs20 where class=1001 or class=1002;
select * from dcs20 where id not in(1,2);：查询id不为2和1的数据
select * from dcs20 where class is null;：查询class为空的数据，不能直接用字段等于null
select * from dcs20 where class is not null;：查询class不为空的数据
select * from dcs20 where name like "%yuan%";：查询name字段里面包含yuan的数据
select * from dcs20 where name like "%an";：查询name字段以an结尾的所有数据
select * from dcs20 where name like "xi%";：查询name字段以xi开头的所有数据
select * from dcs20 id limit 1,3;：查询2到5行的数据
limit m,n(m为下标值，n为查询行数)   下标值从零开始

排序
从小到大：升序 asc
select * from +表名 order by +字段 asc;
select * from dcs20 order by phone asc;
从大到小：降序desc
select * from +表名 order by +字段 desc;
select * from dcs20 order by phone desc;
   
select * from dcs20 group by class;：通过class字段进行分组
select class,count(*) from dcs20 group by class; ：通过class字段进行分组然后求出每一组有多少人
select class,count(*) from dcs20 group by class having class is not null;：通过class字段进行分组然后加条件class不为null
   
注意：
1.group by分组之后，只能使用having进行条件筛选
2.使用了group by分组后，仅有分组字段和函数可以放到from前面
   
聚合函数：
count：统计
select count(*) from dcs20;：统计dcs20表中数据条数
sum：求和
select sum(yuwen)from dcs20;：计算yuwen之和，可以接条件
avg：求平均值
select avg(shuxue) from dcs20;求shuxue的平均值
max：求最大值
select max(yingyu) from dcs20;：取yingyu的最大值
min：求最小值
select min(yingyu) from dcs20;：取yingyu的最小值
distinct：去重
select distinct(phone) from dcs20;：去除phone字段里重复的数据