数据库实例

# 表结构部分

drop database if exists `SchoolExam`;
create database if not exists `SchoolExam`  default character set utf8 collate utf8_unicode_ci;
use SchoolExam;

# 创建Student表

drop table if exists `Student`;
create table if not exists `Student`(
    ID                INT(10)     not null    auto_increment comment '学生学号',
    `Name`         varchar(20) not null                      comment '学生姓名',
    Sex            char(4)                                 comment '性别',
    Birth        year                                    comment '出生日期',
    Department     varchar(20) not null                       comment '院系',
    Address        varchar(50)                            comment '家庭住址',
    primary key(ID),
    unique(ID)
)engine=InnoDB charset=utf8;

# 创建Score表

drop table if exists `Score`;
create table if not exists `Score`(
    ID         int(10)        not null    auto_increment    comment '成绩编号',
    Stu_ID    int(10)        not null                    comment '学生学号',
    C_name     VARCHAR(20)                                comment '科目编号',
    Grade    int(10)                                    comment '考试成绩',
    primary key(ID),
    unique(ID)
)engine=InnoDB charset=utf8;

# 数据部分

insert into Student values(901, '张老大',     '男',  1985,  '计算机系',  '北京市海淀区');
insert into Student values(902, '张老二',     '男',  1986,  '中文系',  '北京市昌平区');
insert into Student values(903, '张三',     '女',  1990,  '中文系',  '湖南省永州市');
insert into Student values(904, '李四',     '男',  1990,  '英语系',  '辽宁省阜新市');
insert into Student values(905, '王五',     '女',  1991,  '英语系',  '福建省厦门市');
insert into Student values(906, '王六',     '男',  1988,  '计算机系',  '湖南省衡阳市');

insert into Score values(null, 901,  '计算机', 98);
insert into Score values(null, 901,  '英语', 80);
insert into Score values(null, 902,  '计算机', 65);
insert into Score values(null, 902,  '中文', 88);
insert into Score values(null, 903,  '中文', 95);
insert into Score values(null, 904,  '计算机', 70);
insert into Score values(null, 904,  '英语', 92);
insert into Score values(null, 905,  '英语', 94);
insert into Score values(null, 906,  '计算机', 90);
insert into Score values(null, 906,  '英语', 85);

#3.查询student表的所有记录

select * from Student;

#4.查询student表的第2条到4条记录

#limit 1,3--第2条到4条记录
select * from Student limit 1,3;

#5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

select `id`,`name`,`department` from Student;

#6.从student表中查询计算机系和英语系的学生的信息

select * from Student where `department` in ('计算机系','英语系');

#7.从student表中查询年龄18~22岁的学生信息

select * from Student where `Birth`<='1995' and `Birth`>='1991';
select id,`name`,sex,(2013-birth) AS age,department,address from Student where (2013-birth) between 18 and 22;
#select id,(date_format(from_days(to_days(now()) - to_days(birth)),'%Y') + 0) as 年龄 from Student;

#8.从student表中查询每个院系有多少人

select `department`院系, count(`id`)总人数 from Student group by `department`; 

#9.从score表中查询每个科目的最高分

select `C_name`科目, max(`Grade`)最高分 from Score group by `C_name`;

#10.查询李四的考试科目（c_name）和考试成绩（grade）

select `Name`,`C_name`, `Grade` from Score sc, Student st where sc.Stu_id=st.`Id` and st.`Name`='李四';
#select`C_name`, `Grade` from Score where Stu_id=(select id from Student where `name`='李四');

#11.用连接的方式查询所有学生的信息和考试信息

/*select * from Student inner join Score
on (Student.`Id`=Score.Stu_id);*/
select student.id,name,sex,birth,department,address,c_name,grade from Student,Score where Student.`Id`=Score.Stu_id;

#12.计算每个学生的总成绩

#select `Stu_id`,sum(`Grade`) from Score group by `Stu_id`;
select `Stu_id`,`name`,sum(`Grade`) from Student,Score where Student.`Id`=Score.Stu_id group by `Stu_id` ;

#13.计算每个考试科目的平均成绩

select `C_name`科目, avg(`Grade`)平均分 from Score group by `C_name`;

#14.查询计算机成绩低于95的学生信息

select * from Student where `id` in (select `Stu_id` from Score where `C_name`='计算机' and Grade<95);

#15.查询同时参加计算机和英语考试的学生的信息

select * from Student where `id` in (select `Stu_id` from Score where `Stu_id` in (select `Stu_id` from Score where `C_name`='计算机') and `C_name`='英语');
/*select a.* from Student a,Score b,Score c
where a.id=b.stu_id and b.c_name='计算机' and a.id=c.stu_id and c.c_name='英语';*/

#16.将计算机考试成绩按从高到低进行排序

select * from Score where `C_name`='计算机' order by Grade desc;

#17.从student表和score表中查询出学生的学号，然后合并查询结果

#union--合并字段，去掉重复
/*select * from Student
left join Score
on(Student.`id`=Score.Stu_id);*/
select id from Student union select stu_id from Score;

#18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

/*select `Name`姓名, Department 院系,C_name 考试科目,Grade 成绩 from Student
left join Score
on(Student.`id`=Score.Stu_id)
where `Name` like '张%' or `Name` like '王%';*/
select `Name`姓名, Department 院系,C_name 考试科目,Grade 成绩 from Student,Score
where (`Name` like '张%' or `Name` like '王%') and Student.`id`=Score.Stu_id;

#19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

/*select `Name`姓名, Department 院系,C_name 考试科目,Grade 成绩 from Student
left join Score
on(Student.`id`=Score.Stu_id)
where `Address` like '湖南%' ;*/
select `Name`姓名,(select timestampdiff(year,Birth,now())) as 年龄, Department 院系,C_name 考试科目,Grade 成绩 from Student,Score
where `Address` like '湖南%' and Student.`id`=Score.Stu_id;

#20.统计分数低于60分，60分——70分，70分——80分，大于80分的学生人数。

#select stu_id,count(stu_id) from Score where Grade between 70 and 80 group by stu_id;
select
count(case when Grade<60 then 1 end ) as '60分以下人数',
count(case when Grade>=60 and Grade<70 then 1 end ) as '60~70分人数',
count(case when Grade>=70 and Grade<80 then 1 end ) as '70~80分人数',
count(case when Grade>=80 then 1 end ) as '80分以上人数'
from Score;

#21.统计平均成绩大于90分的学生，显示学号、姓名、总平均成绩。

select `Stu_id`,`name`,avg(Grade) from Score,Student where Student.`id`=Score.Stu_id group by `Stu_id`,`name` having avg(Grade)>90;
/*SELECT `Stu_id`,`name`,avg(Grade)
FROM Score,Student
WHERE Student.`id`=Score.Stu_id
GROUP BY `Stu_id`,`name`
HAVING AVG(GRADE)>=90;*/