2022/10/10课件作业

脚本1

drop database if exists studentmananger;
create database studentmanager DEFAULT CHAR SET UTF8;
use studentmanager;
-- 创建系部表
drop table if exists department;
create table department
(
    dep_id char(10) primary key comment '系部编号',
    dep_name varchar(20) not null unique comment '系部名称',
    dep_head varchar(10) comment '系主任'
);
-- 向系部表department中添加数据
insert into department values('01','电气与信息工程系','刘明');
insert into department values('02','机械工程系','于明');
insert into department values('03','建筑工程系','王天');
insert into department values('04','材料工程系','李大可');
-- 创建教师表
drop table if exists teacher;
create table teacher
(
    t_id char(10) primary key,
    t_name varchar(10) not null,
    t_sex ENUM('男','女'),
    t_entrydate TIMESTAMP  DEFAULT CURRENT_TIMESTAMP(),                        -- 入职日期
    t_professor varchar(10) default '助教',
    t_salary decimal(10,2) unsigned,                                -- 基本工资
    dep_id char(10)  references department(dep_id)
) ;
-- 向教师表teacher中添加数据
insert into teacher values('0101','陈平','女','2003-07-15','讲师',5000,'01');
insert into teacher values('0102','陈扬','女','2002-07-15','讲师',5000,'01');
insert into teacher values('0103','杨欣','男','1995-07-15','副教授',6000,'01');
insert into teacher values('0104','蒋固安','男','1990-07-15','教授',8000,'01');
insert into teacher values('0105','张振','女','1995-07-15','讲师',5000,'01');
insert into teacher values('0201','王丽娜','女','1998-07-15','副教授',6000,'02');
insert into teacher values('0202','于林','男','1989-07-15','教授',8000,'02');
-- 创建班级表
drop table if exists class;
create table class
(
    c_id char(10) primary key,
    c_name varchar(20) not null,
    c_mentor varchar(10),            -- 班导师
    dep_id char(10) references department(dep_id)
);
-- 向班级表class中添加数据
insert into class values('20160101','计算机16-1','张振','01');
insert into class values('20160102','计算机16-2','王丽娜','01');
insert into class values('20160103','自动化16-1','于林','01');
insert into class values('20160104','自动化16-2','王伟','01');
insert into class values('20160201','机制16-1','张静','02');
insert into class values('20160202','机制16-2','李超','02');
insert into class values('20160203','机制16-3','赛飞','02');
insert into class values('20160204','机制16-4','户康','02');
insert into class values('20160301','土木16-1','朱明','03');
-- 创建学生表
drop table if exists student;
create table student
(
    s_id char(10) primary key,
    s_name varchar(10) not null unique,
    s_sex ENUM('男','女'),
    s_borndate datetime,-- 出生日期
    s_enrolldate TIMESTAMP default CURRENT_TIMESTAMP(),-- 入学日期
    s_telephone char(11),
    s_address varchar(30),-- 家庭住址
    c_id char(10)  references class(c_id)
);
-- 向学生表student中添加数据
insert into student values('2016010101','白沧明','男','1995-4-16','2016-9-1','13478098447','山东省济南市','20160101');
insert into student values('2016010102','孔亚薇','女','1996-4-3','2016-9-1','13804961254','山东省青岛市','20160101');
insert into student values('2016010201','王丽','女','1989-7-8','2016-9-1','13079452444','山东省烟台市','20160102');
insert into student values('2016010202','田园','女','1985-6-9','2016-9-1','13897256641','山东省潍坊市','20160102');
insert into student values('2016010203','刘晓','男','1993-12-4','2016-9-1','15874526365','山东省济宁市','20160102');
insert into student values('2016020101','张孝文','男','1996-9-7','2016-9-1','18945623120','山东省枣庄市','20160201');
insert into student values('2016020201','孙天方','男','1992-5-6','2016-9-1','13940931222','山东省德州市','20160202');
insert into student values('201020201','李鹏飞','男','1992-10-6','2016-9-1',null,null,'20160202');
-- 创建课程表
drop table if exists course;
create table course
(
    course_id char(10) primary key,
    course_name varchar(20) unique,
    course_credit tinyint unsigned ,-- 学分1-10
    course_type char(10)                -- 课程类型
);
-- 向课程表course中添加数据
insert into course values('0001','计算机应用基础',4,'公共基础课');
insert into course values('0002','大学英语',6,'公共基础课');
insert into course values('0003','数据库原理与应用',4,'专业基础课');
insert into course values('0004','C语言程序设计',4,'专业基础课');
insert into course values('0005','机械设计原理',4,'专业基础课');
insert into course values('0006','web程序设计',6,'专业课');
insert into course values('0007','计算机网络',5,'专业课');
insert into course values('0008','数控编程及应用',3,'专业课');
-- 创建授课表
drop table if exists t_c;
create table t_c
(
    t_id char(10)  references teacher(t_id),
    course_id char(10)  references course(course_id),
    term tinyint unsigned,-- 开课学期
    primary key(t_id,course_id)
);
-- 向授课表t_c中添加数据
insert into t_c values('0101','0001',1);
insert into t_c values('0102','0001',1);
insert into t_c values('0103','0002',1);
insert into t_c values('0104','0002',1);
insert into t_c values('0101','0003',3);
insert into t_c values('0102','0004',2);
insert into t_c values('0105','0007',4);
insert into t_c values('0105','0006',5);
insert into t_c values('0201','0005',2);
insert into t_c values('0202','0008',3);
-- 创建选课表
drop table if exists s_c;
create table s_c
(
    s_id char(10),
    course_id char(10),
    grade decimal(4,1) unsigned ,
    primary key(s_id,course_id),
    foreign key(s_id) references student(s_id),
    foreign key(course_id) references course(course_id)
);
-- 向选课表s_c中添加数据
insert into s_c values('2016010101','0001',90);
insert into s_c values('2016010201','0001',92);
insert into s_c values('2016020101','0001',88);
insert into s_c values('2016020201','0001',56);
insert into s_c values('2016010101','0002',85);
insert into s_c values('2016010201','0002',88);
insert into s_c values('2016010202','0002',59);
insert into s_c values('2016020201','0002',78);
insert into s_c values('2016010101','0003',89);
insert into s_c values('2016010102','0003',89);
insert into s_c values('2016010201','0003',78);
insert into s_c values('2016010202','0004',66);
insert into s_c values('2016020101','0004',99);

 

 

脚本2

drop database if exists studentmananger;
create database studentmanager;
use studentmanager;
-- 创建系部表
drop table if exists department;
create table department
(
    dep_id char(10) primary key comment '系部编号',
    dep_name varchar(20) not null unique comment '系部名称',
    dep_head varchar(10) comment '系主任'
)charset=utf8;
-- 向系部表department中添加数据
insert into department values('01','电气与信息工程系','刘明');
insert into department values('02','机械工程系','于明');
insert into department values('03','建筑工程系','王天');
insert into department values('04','材料工程系','李大可');
-- 创建教师表
drop table if exists teacher;
create table teacher
(
    t_id char(10) primary key,
    t_name varchar(10) not null,
    t_sex ENUM('男','女'),
    t_entrydate DATE,                        -- 入职日期
    t_professor varchar(10) default '助教',
    t_salary decimal(10,2) unsigned,                                -- 基本工资
    dep_id char(10)  references department(dep_id)
)charset=utf8 ;
-- 向教师表teacher中添加数据
insert into teacher values('0101','陈平','女','2003-07-15','讲师',5000,'01');
insert into teacher values('0102','陈扬','女','2002-07-15','讲师',5000,'01');
insert into teacher values('0103','杨欣','男','1995-07-15','副教授',6000,'01');
insert into teacher values('0104','蒋固安','男','1990-07-15','教授',8000,'01');
insert into teacher values('0105','张振','女','1995-07-15','讲师',5000,'01');
insert into teacher values('0201','王丽娜','女','1998-07-15','副教授',6000,'02');
insert into teacher values('0202','于林','男','1989-07-15','教授',8000,'02');
-- 创建班级表
drop table if exists class;
create table class
(
    c_id char(10) primary key,
    c_name varchar(20) not null,
    c_mentor varchar(10),            -- 班导师
    dep_id char(10) references department(dep_id)
)charset=utf8 ;
-- 向班级表class中添加数据
insert into class values('20160101','计算机16-1','张振','01');
insert into class values('20160102','计算机16-2','王丽娜','01');
insert into class values('20160103','自动化16-1','于林','01');
insert into class values('20160104','自动化16-2','王伟','01');
insert into class values('20160201','机制16-1','张静','02');
insert into class values('20160202','机制16-2','李超','02');
insert into class values('20160203','机制16-3','赛飞','02');
insert into class values('20160204','机制16-4','户康','02');
insert into class values('20160301','土木16-1','朱明','03');
-- 创建学生表
drop table if exists student;
create table student
(
    s_id char(10) primary key,
    s_name varchar(10) not null unique,
    s_sex ENUM('男','女'),
    s_borndate datetime,-- 出生日期
    s_enrolldate datetime,-- 入学日期
    s_telephone char(11),
    s_address varchar(30),-- 家庭住址
    c_id char(10)  references class(c_id)
)charset=utf8 ;
-- 向学生表student中添加数据
insert into student values('2016010101','白沧明','男','1995-4-16','2016-9-1','13478098447','山东省济南市','20160101');
insert into student values('2016010102','孔亚薇','女','1996-4-3','2016-9-1','13804961254','山东省青岛市','20160101');
insert into student values('2016010201','王丽','女','1989-7-8','2016-9-1','13079452444','山东省烟台市','20160102');
insert into student values('2016010202','田园','女','1985-6-9','2016-9-1','13897256641','山东省潍坊市','20160102');
insert into student values('2016010203','刘晓','男','1993-12-4','2016-9-1','15874526365','山东省济宁市','20160102');
insert into student values('2016020101','张孝文','男','1996-9-7','2016-9-1','18945623120','山东省枣庄市','20160201');
insert into student values('2016020201','孙天方','男','1992-5-6','2016-9-1','13940931222','山东省德州市','20160202');
insert into student values('201020201','李鹏飞','男','1992-10-6','2016-9-1',null,null,'20160202');
-- 创建课程表
drop table if exists course;
create table course
(
    course_id char(10) primary key,
    course_name varchar(20) unique,
    course_credit tinyint unsigned ,-- 学分1-10
    course_type char(10)                -- 课程类型
)charset=utf8 ;
-- 向课程表course中添加数据
insert into course values('0001','计算机应用基础',4,'公共基础课');
insert into course values('0002','大学英语',6,'公共基础课');
insert into course values('0003','数据库原理与应用',4,'专业基础课');
insert into course values('0004','C语言程序设计',4,'专业基础课');
insert into course values('0005','机械设计原理',4,'专业基础课');
insert into course values('0006','web程序设计',6,'专业课');
insert into course values('0007','计算机网络',5,'专业课');
insert into course values('0008','数控编程及应用',3,'专业课');
-- 创建授课表
drop table if exists t_c;
create table t_c
(
    t_id char(10)  references teacher(t_id),
    course_id char(10)  references course(course_id),
    term tinyint unsigned,-- 开课学期
    primary key(t_id,course_id)
)charset=utf8 ;
-- 向授课表t_c中添加数据
insert into t_c values('0101','0001',1);
insert into t_c values('0102','0001',1);
insert into t_c values('0103','0002',1);
insert into t_c values('0104','0002',1);
insert into t_c values('0101','0003',3);
insert into t_c values('0102','0004',2);
insert into t_c values('0105','0007',4);
insert into t_c values('0105','0006',5);
insert into t_c values('0201','0005',2);
insert into t_c values('0202','0008',3);
-- 创建选课表
drop table if exists s_c;
create table s_c
(
    s_id char(10),
    course_id char(10),
    grade decimal(4,1) unsigned ,
    primary key(s_id,course_id),
    foreign key(s_id) references student(s_id),
    foreign key(course_id) references course(course_id)
)charset=utf8 ;
-- 向选课表s_c中添加数据
insert into s_c values('2016010101','0001',90);
insert into s_c values('2016010201','0001',92);
insert into s_c values('2016020101','0001',88);
insert into s_c values('2016020201','0001',56);
insert into s_c values('2016010101','0002',85);
insert into s_c values('2016010201','0002',88);
insert into s_c values('2016010202','0002',59);
insert into s_c values('2016020201','0002',78);
insert into s_c values('2016010101','0003',89);
insert into s_c values('2016010102','0003',89);
insert into s_c values('2016010201','0003',78);
insert into s_c values('2016010202','0004',66);
insert into s_c values('2016020101','0004',99);

我的代码：

USE studentmanager;

SHOW tables;

-- 简单查询
SELECT * FROM student; -- 1

SELECT s_id, s_name FROM student; -- 2

SELECT s_id AS 学号, s_name 姓名 FROM student; -- 3

SELECT s_id AS 学号, s_name 姓名, year(now())-year(s_borndate) AS 年龄 FROM student;

 

 

作业实验

单表查询实验

基于studentmanager数据库的7个表进行查询操作
1、简单查询
（1）检索学生的基本信息；
（2）检索学生的学号和姓名
（3）检索学生的学号、姓名，要求结果中要显示中文名字：“学号“和”姓名“
（4）检索学生的学号、姓名和年龄（注意：年龄用系统年份减去出生年份，求年份的函数为year（）)。
（5）检索选修课程的学生的学号（注意：去掉重复的行）
（6）检索所有男生的学号、姓名和年龄
（7）检索所有1994年之后出生的学生的学号、姓名
（8）检索没有填写家庭住址的学生的学号和姓名
（9）检索年龄在18-25岁之间的所有学生的学号和姓名
（10）检索所有姓张的学生的学号和姓名
（11）检索不姓张也不姓孙的学生的学号和姓名
（12）检索年龄大于22岁的男生的学号和姓名
（13）检索性别为男或者职称为副教授的教师的工号、姓名、性别、职称
（14）检索学号为2016010101、2016010201、2016020201的学生的姓名
（15）检索所有成绩不及格的学生的学号
（16）检索无考试成绩的学生的学号和课程号
（17）检索参加了1号课程考试且有考试成绩的学生的学号
（18）查询不是教授或副教授的所有教师的个人信息
（19）查询姓名中第二个字为“江“的学生个人信息
（20）查询姓“白”、姓“王”、姓“田”的学生的信息。
2、查询结果排序显示
（1）检索全体学生的情况，结果按所在班级升序排序，同一班级的按出生日期升序排列。
（2）查询0003号课程成绩排在前两名的学生的学号和成绩。
（3）查询工龄在前5位的教师的信息。
（4）查询学生的学号、姓名和出生日期，结果按照出生日期降序排序
（5）查询教师信息，结果按照教师的年龄降序排序
（6）查询0003号课程成绩排在第3-5名的学生的学号和成绩。