肖sir __数据库练习__001

建表语句：
create table student ( id int(4),age int(8),sex int(4),name varchar(20), class int(4), math int(4)) DEFAULT charset=utf8;
INSERT into student VALUES(1,25,1,‘zhansan’,1833,90);
INSERT into student VALUES(2,25,1,‘lisi’,1833,67);
INSERT into student VALUES(3,28,0,‘wangwu’,1835,79);
INSERT into student VALUES(4,35,1,‘xiaoliu’,1835,96);
INSERT into student VALUES(5,27,0,‘xiaoli’,1833,86);
INSERT into student VALUES(6,32,1,‘xiaochen’,1835,48);
INSERT into student VALUES(7,22,1,‘xiaowu’,1834,70);
INSERT into student VALUES(8,31,0,‘xiaoqi’,1825,88);
INSERT into student VALUES(9,27,0,‘xiaoqi’,1833,74);
INSERT into student VALUES(10,27,1,‘niuqi’,null,80);
select * from student ;

表结构：

表：

题目：
1、查询1833班信息的2,4行的数据
结果: 信息 所有 用的*
条件:class=1833 ,limit 1,3
语句:select * from student where class=1833 LIMIT 1,3 ;

2、显示班级为空的id和姓名、和数学分数
结果: id,name, math
条件:class is null
语句:select id,name,math from student where class is null ;

3、统计每个班级人数
结果: 班级class,人数 conut(name)
条件:每个班级 gourp by
语句:
SELECT class,count(name) from student group by class;
SELECT class,count(name) from student group by class;
SELECT class,count(id) from student group by class;
截图:

4、最1833班数学成绩最大的ID年龄和姓名
结果:id,age,name
条件:class=1833,max(math)
语句:
方法1:
Select id,class,name from student where class=1833 and math=(select max(math) from student where class=1833);
方法2:弊端,有重复第一名,只显示一个
Select id,class,name from student where class=1833 order by math desc LIMIT 0,1 ;
截图:

5、求数学分最小的班级 ID年龄和姓名
结果: id ,age, name
条件:数学分最小 min(math)
语句:
方法1:select class,id,name from student where math=(select min(math) from student);
方法2:select class,id,age,name from student ORDER BY math ASC limit 0,1;
截图:

6、求1833班数学分总和
结果:sum(math)
条件:class=1833
语句:select sum(math) from student where class=1833;
截图:

7、求所有班级分数总和
结果:sum(math)
条件:
语句:select sum(math) from student
截图:

8、求年纪最大的班级并显示班级年龄和姓名分数
结果:class,age,name,math
条件: max(age) 的class
语句:select class,age,name,math from student where class=(select class from student where age=(select max(age) from student ));

截图:

9、统计sex中1和0每个的总数
结果:count(sex)
条件: group by sex
语句:
方法1:select sex,count(sex) FROM student group by sex;
方法2:select count(case when sex=1 then 1 end) as sex1,count(case when sex=0 then 0 end)as sex0 from student;

截图:

10、求出所有班级年纪平均数
结果: avg(age)
条件: group by class
语句: Select class,avg(age) from student group by class;
截图:

11、求出1835班年纪的平均数
结果: avg(age)
条件: class=1835
语句: Select avg(age) from student where class=1835;
截图:

12、求出1833班年纪的平均数
结果: avg(age)
条件: class=1833
语句: Select avg(age) from student where class=1833;
截图:

13、将所有数据按照年纪进行降序后显示年纪姓名和班级
结果: age,name,class
条件: order by age desc
语句: Select age,name,class from student order by age desc;
截图:

14、将所有数据按照年纪升序显示年纪姓名班级和数学分数
结果: age,class,name,math
条件: order by age asc asc 可以省略不写
语句:
方法1:select age,name,class,math from student order by age asc;
方法2:select age,name,class,math from student order by age; 省略不写
截图:

15、按照班级将进行分组求出每个班人数
结果: age,class,name,math
条件: order by age asc asc 可以省略不写
语句: select count(*)from student GROUP BY class ;
截图:

16、根据age字段进行降序排序显示所有信息；
结果: *
条件: order by age desc
语句: select * from student ORDER BY age desc ;
截图:

17、根据math字段进行升序排序，并显示前5行所有数据；
结果: *
条件: order by math asc , limit 0,5
语句:
方法1:
Select *from student order by math asc limit 5;
方法2:
Select *from student order by math asc limit 0,5;
截图:

18、把lisi的数学成绩改为69分
条件: math=69
语句:UPDATE student set math=69 where name=‘lisi’ ;
截图:

19、查找性别不为1的所有数据
结果: *
条件: sex not in (1)或<>1 ,!=1
语句:
方法1:Select *from student where sex<>1;
方法2:select *from student where sex not in (1);
方法3:Select *from student where sex !=1;
截图:

20、只显示表中姓名，且将相同的姓名名称去重
结果:name
条件: distinct
语句:select distinct(name)from student;
截图:

21、统计表中行数
结果:count(* )
条件:
语句:
方法1:select count(id) from student;
方法2:select count(*) from student;

截图:

22、统计年纪在27岁的有多少
结果:count(* )
条件: age=27
语句:
方法1:select count(id) from student where age=27;
方法2:select count(*) from student where age=27;
截图;

23、统计年纪大于25小于35的有多少
结果:count(* )
条件: age>25 and age<35
语句:
方法1:select count(age) from student where age>25 and age<35;
截图;

24、求数学分总和
结果:sum(math)
条件:
语句:Select sum(math) from student;
方法1:
截图;

25、求分数最小
结果:min(math)
条件:
语句:Select min(math) from student;
方法1:
截图:

26、求平均分
结果:avg(math)
条件:
语句:Select avg(math) from student;
截图:

27、只显示3-8行的数据
结果:*
条件: limit 2,6
语句: Select *from student limit 2,6;
截图:

28、查找姓名尾号为qi的所有数据
结果:*
条件:like ‘%qi’
语句: select *from student where name like’%qi’;
截图:

29、查询姓名开头为xiao的所有数据
结果:*
条件:like ‘xiao%’
语句: select from student where name like’xiao%';
截图:

30、查询中间值为ao开头的所有数据
结果:
条件:like ‘%ao%’
语句:
select *from student where name like’%ao%';
截图:

批量注释：ctrl+/
批量取消注释ctrl+shift+/

=================================================