mysql查询

数据表:学生表(student)和学生分数表(score)

一.查询 不重复记录 distinct

语法：select distinct field from tablename [where condition];

1.1.查询学生表(student),获取所有的班级(去重)。

sql：select distinct class from student;

1.2.这个distinct可以配合聚合函数使用,获取班级数量(去重)。

sql：select count(distinct class) from student;

二.排序 order by

语法：select * from tablename order by [field1 [asc/desc],field2 [asc/desc],...fieldn [asc/desc]];

2.1查询分数表(score) ,首先用总分(fraction)排,如果总分相同，再按照最高分(highest)排，都按从大到小排序

sql：select * from score order by fraction desc,highest desc;

三.获取部分数据 limit

语法：select * from tablename [limit offset_start,row_count];

3.1 查询分数表(score),获取数据表中第六条至第十条的记录。

sql:select * from score limit 5,5;

四.聚合函数

语法：select * fun_name from tablename from 
       [where condition]
       [group by field1,field2,...fieldn]
       [with rollup]
       [having condition];

4.1.统计学生表(student)中的总人数 count

sql：select count(*) from student;

4.2.统计学生表(student)每个班级的人数 count ,group by

sql：select class,count(class) from student group by class;

4.3.更详细一点，统计学生表(student)每个班级的人数，并且统计总人数 count,group by,with rollup

sql：select class,count(class) from student group by class with rollup;

4.4.统计学生表(student)每个班级人数大于二的班级 count,group by,having

sql：select class,count(class) from student group by class having count(class) >2;

剩下几个聚合函数就不一一写了。

五.表连接

语法:
   内连接： ... from tab1 inner join  tab2 on condition ...
   外连接：
     左连接：... from tab1 left  join tab2 on condition ...
     右连接：... from tab1 right join tab2 on condition ...``

正在更新…