navicat 单表语句之案例

1、查询1833班信息的2,4行的数据
SELECT * from student WHERE class='1833' LIMIT 1,3;
2、显示班级为空的id和姓名、和数学分数
Select id,name,math from student where class is null;(前面逗号分隔）
3、统计每个班级人数
Select count(id) from student group by class ;
4、最1833班数学成绩最大的ID年龄和姓名
Select id,age,name from student where class=1833 order by math desc limit 1;
select * from student where class=1833 and math=(select max(math) from student where class=1833)
5求数学分最小的班级 ID年龄和姓名
Select id,age,name from student where math (select min（math）from student);

6、求1833班数学分总和
Select sum(math) from student where class=’1833 ‘;
7求所有班级分数总和
Select sum(math) from student where class is not null;
8求年纪最大的班级并显示班级年龄和姓名分数
Select class,age,name,math from student where age=(select max(age) from student);
9、统计sex1和0个总数
Select count(*) from student group by sex;
10、10、求出每个班级的平均年龄
select avg(age),class from student33 group by class
11、求出1835班年纪的平均数
Select avg(age) from student where class=1835;

12、求出1833班年纪的平均数
Select avg(age) from student where class=1833;

13、将所有数据按照年纪进行降序后显示年纪姓名和班级
select age,name,class from student ORDER BY age desc;
14、将所有数据按照年纪升序显示年纪姓名班级和数学分数
select age,name,class ,mathfrom student ORDER BY age asc;

15、按照班级将进行分组
Select * from student group by class;
16、根据age字段进行降序排序；
select  *  from   student  ORDER BY  age desc;

17、根据math字段进行升序排序，并显示前5行所有数据；
Select * from student order by math asc limit 5;
18、把lisi的数学成绩改为69分
Update student set math=69 where name=”lisi”;
19、查找性别不为1的所有数据
Select * from student where sex ! =1;
select  *  from  student  where sex  not in( 1)
SELECT * from student where sex<>1

20、只显示表中姓名，且将相同的姓名名称去重
select  DISTINCT(name)  from  student;
21、统计表中行数
Select count(id) from student;
22、统计年纪在27岁的有多少
Select count(name) where age =27;

23、统计年纪大于25小于35的有多少
Select count(name) student where age >25 and <35;;

24、求数学分总和
select  sum(math)  from  student;
25、求分数最小
select  min(math)  from  student;

26、求平均分
select  avg(math)  from  student;

27、只显示3-8行的数据
select  *  from   student id  LIMIT 2,6

28、查找姓名尾号为qi的所有数据
select  *  from   student where name LIKE  "%qi";
29、查询姓名开头为xiao的所有数据
select  *  from   student where name LIKE  "xiao%”;
30、查询中间值为ao开头的所有数据
select  *  from   student2 where chinese LIKE  "%ao%‘；’