HeidiSQL中一些简单mysql语句的含义（二）

一、排序

#根据年龄进行降序排序

select * from student order by age desc;

#根据年龄进行升序排序

select * from student order by age asc;

#给字段起别名，as可以省略

select no as 编号, name as 姓名,age as 年龄 from student order by age asc;

#查找前五条

select * from student limit 5;

#从第五行开始查找三条（第一行是0，所以5实际上是第六行）

select * from student limit 5,3;

二、模糊查询

#查询name中带张的数据

select * from student where name like '张_'

#查询name中为张XX的数据

select * from student where name like '张__'

#以张开头的数据

select * from student where name like '张%'

#以张结尾的数据

select * from student where name like '%张'

#查询年龄在20到21之间，包括20和21

select * from student where age between 20 and 21

#查询年龄为20和21的数据

select * from student where age in(20,21）

三、聚合函数

#统计一共有多少学生

select count(*) from student;

#查询年龄的平均值

select avg(age) from student;

#对年龄进行求和

select sum(age) from student;

#查询年龄的最大值

select max(age) from student

#查询年龄最小值

select limit(age) from student

#查询的结果里只能有分组的列和聚合函数

select major,count(*) from student2 group by major

#having用来设置分组查询的条件

select major,count(*) from student2 group by major having count(*)>2;

四、子查询

#根据年龄的子查询

select * from student2 where age>(select age from student where name ='欧阳丹丹')

五、多表查询

#把两个表中sno相同的记录关联到一起进行输出,内连接：要求左边的表和右边的表必须对应上

select * from student inner join sc on student.sno=sc.sno where sdept='is'

#左连接：把左边表的数据都展示出来

select * from student left join sc on student.sno=sc.sno where grade>95

#右连接：把右边表的数据都展示出来

select * from student right join sc on student.sno=sc.sno where sdept='is'

#展示student.sno=sc.sno and sdept='is'的数据

select * from student,sc where student.sno=sc.sno and sdept='is'

#分组查询

select sno,avg(grade) from sc group by sno