mysql练习

1、student的mysql练习

1:查询所有学生的信息
select * from work1;
2：查询姓名是李白的学生信息
select * from work1 where name=“李白”;
3：查询1班是上海的学生
select * from work1 where class=“1班” and homedown=“上海”;
4：查询家乡是北京或者上海的学生
select * from work1 where homedown=“北京” or homedown=“上海”;
5：查询小乔的年龄
select age from work1 where name=“小乔”;
6：查询家乡不在北京的学生
select * from work1 where homedown!=“北京”;
7：查询年龄小于20的女同学
select * from work1 where age<20;
8：查询年龄为18至20的学生
select * from work1 where age between 18 and 20;
9：查询北京学生的年龄总和
select sum(age) from work1 where homedown=“北京”;
10：查询女生的平均年龄
select avg(age) from work1 where sex=“女”;
11：查询姓名包含白的学生
select * from work1 where name like ‘%白%’;
12：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from work1 order by age desc,studentNo;
13：查询女生的最大年龄
select max(age) from work1 where sex=“女”;
14：查询北京学生的年龄总和
select sum(age) from work1 where homedown=“北京”;
15：查询各种性别的人数
select sex,count(sex) from work1 GROUP BY sex ;
16：查询男生总人数
select sex,count(sex) from work1 where sex=“男”;

上述语句都实现了,就不一一截屏了。

2、名人表,作品表,总结表

创建名人表 sid:id , name:作者, sex:性别, age:年龄
create table celebrity(
sid int PRIMARY KEY auto_increment,
name VARCHAR(255),
sex VARCHAR(255),
age int
);
创建作品表 wid:id, wwork:作品, wdynasty:朝代
create table works(
wid int PRIMARY KEY auto_increment,
wwork VARCHAR(255),
wdynasty VARCHAR(255)
)
创建总结表 sid:名人表的id, wid:作品表的id, sassess:名句
create table summary(
sid int,
wid int,
sassess VARCHAR(255)
)

要求及语句
1:查询名人表中性别是男的作者
select * from celebrity where sex=“男”;
2:查询静夜思的作者
select name,wwork from celebrity INNER JOIN summary on celebrity.sid=summary.sid INNER JOIN works on works.wid=summary.wid where wwork=“静夜思”;
3:查询李白和杜甫的年纪
select name,age from celebrity where name=“李白” or name=“杜甫”;
4:查询名人表中的男女人数
select sex,count(sex) from celebrity GROUP BY sex;
5:查询白居易写的作品名称和对于的名句
select name,wwork,sassess from summary INNER JOIN celebrity on celebrity.sid=summary.sid INNER JOIN works on works.wid=summary.wid where name=“白居易”;
6:查询年龄在25到30岁之间的个数
select count(age) from celebrity where age BETWEEN 25 and 30;
7:查询名人表中最后两条的数据信息
select * from celebrity ORDER BY sid DESC LIMIT 2;
8:查询李白的作品名称,名句和年纪
select name,wwork,sassess,age from summary INNER JOIN celebrity on celebrity.sid=summary.sid INNER JOIN works on works.wid=summary.wid where name=“李白”;
9:将作品为望岳的朝代修改为北宋
update works set wdynasty=“北宋” where wwork=“望岳”;
10:新增名人表中的一个作者王维,年级25,性别男
insert into celebrity (name,sex,age) VALUES(“王维”,“男”,25);

3、学生表,成绩表

1、创建数据库db1并选择该数据库
创建tb_scores成绩表：
字段有id（成绩编号）、score（成绩）、grade（等级）
其中此id字段设置为主键自增
score字段
grade字段

创建tb_student学生表：
字段有id（学生编号）、name（学生姓名）、sid（关联scores表中id）
其中id主键自增
name字段 非空

– 1、创建数据库db1并选择该数据库
– 创建tb_scores成绩表：
– 字段有id（成绩编号）、score（成绩）、grade（等级）
– 其中此id字段设置为主键自增
– score字段
– grade字段
create table tb_score(
id int PRIMARY KEY auto_increment,
score int,
grade VARCHAR(255)
)
create table tb_student(
id int PRIMARY KEY auto_increment,
name VARCHAR(255),
sid int
)
– 创建tb_student学生表：
– 字段有id（学生编号）、name（学生姓名）、sid（关联scores表中id）
– 其中id主键自增
– name字段 非空

– 1)查询学生表中所有学生记录。
select * from tb_student;
– 2)查询学生表中学号、姓名，显示3-8条记录
select tb_student.id,tb_student.name from tb_student GROUP BY tb_student.id LIMIT 2,6;
– 3)查询学生表中姓张的学生记录
select * from tb_student where name like “张%”;
– 4)查询学生表中学号、姓名、分数编号，按照分数编号升序。
select tb_student.id,name,sid from tb_student ORDER BY sid;
– 5)查询学生表中各分数编号对应的人数
select sid,count() from tb_student GROUP BY sid;
– 6)查询成绩表中分数80-100的成绩编号、分数、等级
select sid,score,grade from tb_student INNER JOIN tb_score on
tb_student.sid=tb_score.id where score BETWEEN 80 and 100;
– 7)查询成绩表中各等级的最低分
select grade,min(score) from tb_score GROUP BY grade;
– 8)查找出成绩在70-90之间的所有学生id、姓名、成绩、等级（内连接）
select tb_student.id,name,score,grade from tb_student INNER JOIN tb_score on
tb_student.sid=tb_score.id where score BETWEEN 70 and 90;
– 9)查找出成绩最低的学生id、姓名及成绩（子查询）
– select tb_score.id,tb_student.name,score from tb_student INNER JOIN tb_score on tb_student.sid=tb_score.id where score =(select min(score) from tb_score);
select tb_score.id,name,score from tb_score INNER JOIN tb_student on
tb_student.sid=tb_score.id ORDER BY score LIMIT 1;
– 10)查找出优秀的学生的个数（内连接）
select grade,count() from tb_score INNER JOIN tb_student on
tb_student.sid=tb_score.id where grade=“优秀”;

(￣▽￣)~*------ ٩(๑❛ᴗ❛๑)۶谢谢阅读！！！！！！！！！！！！！