mysql 数据库单表练习

一、练习题
（1）建表语句
create table student2(id int primary key ,
name char(20),
sex char(10),
age int(3),
mobile char(20),
class char(10),
english int(10),
chinese int(10),
math int(10))engine=innodb default charset=utf8;

insert into student2 values(1,'小红','女',23,'13813828824','1719',77,88,98),
(2,'小明','男',23,'13713713711','1720',56,66,55),
(3,'小李','男',23,'15915913911','1719',78,64,87),
(4,'小张','男',23,'15915913912','1720',77,76,77),
(5,'小白','女',24,'15915913913','1719',90,89,98),
(6,'小陈','女',19,'15915913914','1719',84,100,81),
(7,'小钱','女',20,'15915913915',null,45,99,93);

（1.2）题目
题目1 查询1719班学生的成绩信息
select  * from  student2  where  calss=1719

题目2 查询1719班学生语文成绩大于80小于90的学生
信息结果： 学生信息  *条件：class=1719，chinese>80 and chinese<90
语句：select  * from  student2  where  calss=1719 and  chinese>80 and chinese<90

题目3 查询学生表中5-7行的数据信息
结果：* 条件：limit  4,3
语句：SELECT * FROM student2 LIMIT 4,3;

题目4 显示1719班英语成绩为90，数学成绩为98的name与mobile信息
结果：name，mobile 条件：calss=1719，english=90  ，math=98  同时满足and
语句：Select name,mobile from student2 where class=1719 and english=90 and math=98;

题目5 显示1719班学生信息并且以语文成绩降序排序
结果：学生信息  *条件：class=1719 ，order  by   chinese  desc
语句：select * from student2 where class=1719 order by chinese desc

题目6 查询1719与1720班，语文成绩与数学成绩都小于80的name与mobile信息
结果：name，mobile 条件：class=1719，class=170   chinese <80,math<80
语句：方法1:Select name,mobile from student2 where class in(1719,1720) and chinese<80 and math<80;
方法2：Select name,mobile from student2 where (class=1719 or class=1720) and chinese<80 and math<80;
方法3:Select name,mobile from student2 where ( class=1720 and chinese<80 and math<80) or( class=1719 and chinese<80 and math<80);

题目7 查询英语平均分大于80分的班级，英语平均分
结果：class ，avg（english） 条件：group by  class  ，having   avg（english）>80
语句：select avg(english),class from student2 group by class having avg(english)>80

题目8 按班级查出数学最高分
结果:max(math) 条件：group  by  class ，max(math)
语句：select  max(math)  from  student2 group by class ;

题目9 查询出每班数学最低分
select min(math) from student2 group by class

题目10 查询每班数学总分
SELECT sum(math) FROM student2 GROUP BY class;

题目11 查询每班数学平均分
Select avg(math) from student2 group by class;

题目12 查询出每班学生总数
Select count(*) from student2 group by class;Select count(name) from student2 group by class;Select count(id) from student2 group by class;

题目13 在表中插入一条小谢的成绩数据
（1）插入全部字段数据insert into student2 values(‘8’,'小谢','男',99,'197671853','1722',60,60,60) ;
（2）出入部分数据Insert into student2 (name) value (“小谢”);

题目14 把英语分数小于60的同学分数改为60分
Update student2 set english=60 where english<60;

（2）建表语句：create table student(id int(4),
age int(8),
sex int(4),
name varchar(20),
class int(4),math int(4));

INSERT into student VALUES  (1,25,1,'zhangsan',1833,90),
(2,25,1,'lisi',1833,67),
(3,28,0,'xiaowang',1835,79),
(4,35,1,'xiaoliu',1835,96),
(5,27,0,'xiaoli',1833,86),
(6,32,1,'xiaochen',1835,48),
(7,22,1,'wangwu',1834,70),
(8,31,0,'xiaoqi',1825,88),
(9,27,0,'xiaoqi',1833,74),(10,27,1,'niuqi',null,74)

（2.1）题目：
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、统计每个班级人数
结果：count（）条件：group  by  class
语句：select class,count() from student group by class;

4、求1833班数学成绩最大的ID，年龄，和姓名
结果：id,age，name 条件：class=1833 ， max（math）
语句（缺陷）：Select id,age,name from student order by math desc limit 1;
方法2：select *  from student where class=1833 and  math=(select max(math)  from student where class=1833)
id有两个相同大的，只能显示一个

5、求数学分最小的班级 ID年龄和姓名
结果：id、age、name 条件：min(math)
语句：
方法1：Select id,age,name from student WHERE math=(select min(math) from student);
方法2：SELECT class,id,age,name FROM student ORDER BY math LIMIT 1;
6、求1833班数学分总和
结果：sum（math） 条件：class=1833语句：SELECT SUM(math) from student WHERE class=1833

7、求所有班级分数总和
结果：sum（math） 条件：class is not null
语句：select sum(math) from student where class is not null;

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

9、统计sex1和0个总数
结果：count（），sex  条件：group by sex
语句：Select count(),sex from student group by sex;

10、求出每个班级的平均年龄
select avg(age),class from student 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,math from student order by age;

15、按照班级将进行分类
Select *  from student ORDER BY  class desc ;

16、根据age字段进行降序排序；
结果：* 条件： order by   age  desc
语句：select * from student order by age desc

17、根据math字段进行升序排序，并显示前5行所有数据；
结果：* 条件：order  by  math  asc    limit 5
语句：Select  * from student order by math asc limit 5;

18、把lisi的数学成绩改为69分
Update student set math=69 where name=”lisi

19、查找性别不为1的所有数据
结果：* 条件：not in  （1）， sex！=1或sex<>1
语句：select  *  from  student  where sex  not in( 1)
SELECT * from  student where sex!=1
SELECT * from  student where sex<>1

20、只显示表中姓名，且将相同的姓名名称去重
结果：name 条件：distinct
方法：select distinct(name) from student;

21、统计表中行数
结果：count（）
语句：Select count(id) from student;
Select count() from student;
select count(name) from student

22、统计年纪在27岁的有多少
结果：count（age）条件：age=27
语句：
select count(*) from student where age=27;
SELECT COUNT(name) from student where age=27;
select count(age) from student where age=27

23、统计年纪大于25小于35的有多少
结果：count（）条件：age>25  and age<35 
方法：
SELECT COUNT(name) from student where age>25 and age<35S
elect count() from student where age>25 and age<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 LIMIT 2,6;

28、查找姓名尾号为qi的所有数据
结果：* 条件：like  “%qi”
语句：ELECT * from student WHERE name LIKE "%qi"

29、查询姓名开头为xiao的所有数据
结果：* 条件：like  “xiao%”
语句：select * from student where name like ”xiao%”;

30、查询中间值为ao开头的所有数据
结果：* 条件：like ‘%a%’
语句：Select * from student where name like ”%ao%”;