数据库任务

建表语句

 

 

-- 年级表
CREATE TABLE `grade` (
  `grade_id` tinyint(5) unsigned NOT NULL,
  `grade_name` varchar(15) NOT NULL,
  PRIMARY KEY (`grade_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
-- 成绩表
CREATE TABLE `score` (
  `score_id` int(10) NOT NULL COMMENT '成绩id',
  `student_id` char(10) NOT NULL COMMENT '学员编号',
  `course_id` int(11) NOT NULL COMMENT '科目id',
  `score` tinyint(4) NOT NULL COMMENT '成绩',
  `exam_time` date DEFAULT NULL,
  PRIMARY KEY (`score_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 学生表
CREATE TABLE `student` (
  `student_id` char(10) NOT NULL,
  `student_name` varchar(20) NOT NULL,
  `pwd` varchar(20) NOT NULL,
  `sex` char(5) DEFAULT NULL,
  `grade_id` tinyint(5) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`student_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- 科目表
CREATE TABLE `course` (
  `course_id` int(11) NOT NULL,
  `course_name` varchar(50) DEFAULT NULL,
  `period` tinyint(4) DEFAULT NULL,
  `grade_id` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`course_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

 

查询语句

-- 1.grade 表增加一个阶段，“就业期” 
insert into grade (grade_id, grade_name) values (4,"就业期");
-- 2.将第三阶段的学生的 gradeid 改为就业期的 id 
update student set grade_id=4 where grade_id=3;
-- 3.查询所有得了 100 分的学号 
select distinct student_id from score where score=100;
-- 4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1） 
select student_id,student_name,birthday from student 
where birthday>'1989-01-01' and birthday<'1990-01-01';
-- 5.查询学生姓名为“金蝶”的全部信息 
select * from student where student_name='金蝶';
-- 6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩 
select student_id,score from score where course_id=8 and score<60;
-- 7.查询第 3 阶段课时大于 50 的课程全部信息
select * from course where grade_id=3 and period>50;
-- 8.查询 S1101001 学生的考试信息 
select * from score where student_id='S1101001';
-- 9.查询所有第二阶段的女生信息 
select * from student where grade_id=2 and sex='女';
-- 10.“基于.NET 平台的软件系统分层开发”需要多少课时 
select period from course where course_name='基于.NET 平台的软件系统分层开发';
-- 11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in) 
select period from course where course_name in ('设计 MySchool 数据库','面向对象程序设计');
-- 12 查询所有地址在山东的学生信息 
select * from student where address='山东';
-- 13 查询所有姓凌的单名同学  通配符下划线表示匹配任意单个字符。
select * from student where student_name like '凌_';
-- 14.查询 gradeid 为 1 的学生信息，按出生日期升序排序 
select * from student where grade_id=1 order by birthday;
-- 15.查询 subjectid 为 3 的考试的成绩信息，用降序排序 
select * from score where course_id=3 order by score desc;
-- 16.查询 gradeid 为 2 的课程中课时最多的课程信息 
select * 
from course 
where period=(
    select max(period) 
    from course);
-- 17.查询北京的学生有多少个 
select count(*) from student where address='北京';
-- 18.查询有多少个科目学时小于 50 
select count(*) from course where period<50;
-- 19.查询 gradeid 为 2 的阶段总课时是多少
select sum(period) as sum_period from course where grade_id=2;
-- 20.查询 subjectid 为 8 的课程学生平均分 
select avg(score) from score where course_id=8;
-- 21.查询 gradeid 为 3 的课程中最多的学时和最少的学时 
select max(period),min(period) from course where grade_id=3;
-- 22.查询每个科目有多少人次考试 
select course_id,count(*) from score group by course_id;
-- 23.每个阶段课程的平均课时 
select grade_id,avg(period) from course group by grade_id;
-- 24.查询每个阶段的男生和女生个数（group by 两列）
select grade_id,sex,count(*) from student group by grade_id,sex;