查网站,app开发平台软件,织梦网站反应速度慢,清溪网站仿做数据库多表查询作业 创建数据库 插入数据
mysql insert into student values(901,张老大,男,1985,计算机系,北京市海淀区),- (902,张老二,男,1986,中文系,北京市昌平市),- (903,张三,女,1990,中文系,湖南省永州市), -…数据库多表查询作业 创建数据库 插入数据
mysql insert into student values(901,张老大,男,1985,计算机系,北京市海淀区),- (902,张老二,男,1986,中文系,北京市昌平市),- (903,张三,女,1990,中文系,湖南省永州市), - (904,李四,男,1990,英语系,辽宁省阜新市), - (905,王五,女,1991,英语系,福建省厦门市),- (906,王六,男,1988,计算机系,湖南省衡阳市);mysql insert into score values(null,901,计算机,98),- (null,901,英语,80),- (null,902,计算机,65),- (null,902,中文,88),- (null,903,中文,95),- (null,904,计算机,70),- (null,904,英语,92),- (null,905,英语,94),- (null,906,计算机,90),- (null,906,英语,85);1.查询student表的所有记录
mysql select * from student;2.查询student表的第2条到4条记录
mysql select * from student limit 1,3;3.从student表查询所有学生的学号id、姓名 name和院系department的信息
mysql select id as 学号,name as 姓名,department as 院系 from student;4.从student表中查询计算机系和英语系的学生的信息
mysql select * from student where department计算机系 or department英语系;5.从student表中查询年龄18~22岁的学生信息
mysql select *,year(curdate())-birth as age from student where year(curdate())-birth between 18 and 22;6.从student表中查询每个院系有多少人
mysql select department as 院系,count(*) as 人数 from student group by department;7.从score表中查询每个科目的最高分
mysql select c_name as 科目,max(grade) as 最高分 from score group by c_name;8.查询李四的考试科目c_name和考试成绩grade
mysql select name as 姓名,c_name as 科目,grade as 成绩 from student- inner join score on student.idscore.stu_id- where name李四;9.用连接的方式查询所有学生的信息和考试信息
mysql select * from student iudent inner join score on student.idscore.stu_id;10.计算每个学生的总成绩
mysql select name as 姓名,sum(grade) as 总成绩 from student- inner join score on student.idscore.stu_id- group by name;11.计算每个考试科目的平均成绩
mysql select avg(grade) from score group by c_name;12.查询计算机成绩低于95的学生信息
mysql select * from student- inner join (select stu_id,c_name,grade from score where grade95 and c_name计算机) as stu- on student.idstu.stu_id;13.查询同时参加计算机和英语考试的学生的信息
获取同时考计算机和英语的学生ID
select * from score where c_name英语) as sc2 where sc1.stu_idsc2.stu_id根据ID获取学生信息
mysql select * from student inner join (select sc1.stu_id from (select * from score where c_name计算机) as sc1,(select * from score where c_name英语) as sc2 where sc1.stu_idsc2.stu_id) as sc on student.idsc.stu_id;14.将计算机考试成绩按从高到低进行排序
mysql select * from score where c_name计算机 order by grade desc;15.从student表和score表中查询出学生的学号然后合并查询结果
mysql select student.id,score.stu_id from student inner join (select distinct stu_id from score) as score on student.idscore.stu_id;16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql select stu.name as 姓名, stu.department as 院系, score.c_name as 科目,score.grade as 成绩- from score inner join- (select * from student where name like 张% or name like 王) as stu- on score.stu_idstu.id;17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
mysql select stu.name as 姓名,year(curdate())-stu.birth as age,stu.department as 院系,score.c_name as 科目,score.grade as 成绩- from score- inner join- (select * from student where address like %湖南%) as stu- on score.stu_idstu.id;
