create database if not exists teaching
default character set utf8mb4
default collate utf8mb4_0900_ai_ci;
use teaching;
create table `student`(
`sno` char(10) not null comment'学号',
`sn` varchar(45) not null comment'名字',
`sex` enum('男','女') not null default'男' comment'性别',
`birthday` date not null comment'出生日期',
`maj` varchar(45) not null comment'专业',
`dep` varchar(45) not null comment'院系',
`number` varchar(45) comment'电话',
primary key(`sno`)
);
INSERT INTO `student` (`sno`, `sn`, `sex`, `birthday`, `maj`, `dep`,`number`) VALUES
('0433', '张艳', '女', '2000-9-13', '计算机', '信息学院',''),
('0496', '李越', '女', '2001-1-23', '信息', '信息学院', '13812901234'),
('0529', '赵欣', '男', '2002-2-27', '信息', '信息学院', '13502223245'),
('0531', '张志国', '女', '2002-10-10', '自动化', '工学院', '13312567892'),
('0538', '于兰兰', '男', '2002-2-1', '数学', '理学院', '13312003452'),
('0591', '王丽丽', '女', '2003-3-21', '计算机', '信息学院', '13320808765'),
('0592', '王海强', '女', '2003-9-1', '数学', '理学院','');
create table `course`(
`cno` char(10) not null comment'课程号',
`cn` varchar(45) not null comment'课程名',
`credit` varchar(45) not null comment'学分数',
`hour` varchar(45) not null comment'学时数',
`tn` varchar(45) not null comment'任课老师',
primary key(`cno`)
);
INSERT INTO `course` (`cno`, `cn`, `credit`, `hour`, `tn`) VALUES
('K001', '计算机图形学', '2.5', '40', '胡晶晶'),
('K002', '计算机应用基础', '3', '48', '任泉'),
('K006', '数据结构', '4', '64', '马跃先'),
('M001', '政治经济学', '4', '64', '孔繁新'),
('S001', '高等数学', '3', '48', '赵晓尘');
create table `score`(
`sno` char(10) not null comment'学号',
`cno` char(10) not null comment'课程号',
`choosetime` datetime not null comment'选课时间',
`usualscore` varchar(45) comment'平时成绩',
`percentscore` varchar(45) not null comment'平时成绩比重',
`finalscore` varchar(45) not null comment'考试成绩',
foreign key (`sno`) references `student`(`sno`),
foreign key (`cno`) references `course`(`cno`)
);
INSERT INTO `score`(`cno`, `sno`, `chooseTime`, `usualscore`, `percentscore`, `finalscore`) VALUES
('K001', '0529', '2017-8-24 8:15:11', '85', '0.3', '90'),
('K001', '0531', '2017-8-25 10:18:34', '57', '0.4', '75'),
('K001', '0591', '2017-8-24 15:20:24', '81.5', '0.4', '71.5'),
('K002', '0496', '2018-2-25 15:20:24', '','0.3',''),
('K002', '0529', '2018-2-24 10:15:21', '70', '0.4', '83'),
('K002', '0531', '2018-2-25 13:20:19', '75', '0.2', '81.5'),
('K002', '0538', '2018-2-24 14:20:24', '70.5', '0.4', '73'),
('K002', '0592', '2018-2-15 9:18:12', '85', '0.4',''),
('K006', '0531', '2018-8-25 19:17:25', '93', '0.3', '86'),
('K006', '0591', '2018-8-24 13:19:45', '85', '0.4', '82'),
('M001', '0496', '2019-2-23 10:8:11', '83', '0.3', '91'),
('M001', '0591', '2019-2-25 12:14:12', '92.5', '0.4', '89'),
('S001', '0531', '2019-8-26 13:15:12', '82.5', '0.4', '77'),
('S001', '0538', '2019-8-27 15:10:12', '75.2', '0.3','');
#（6）
create view score_ciew(s_no,s_name,c_no,c_name,total_score)
as select sno,sn,cno,cn,usualscore*percentscore+finalscore*(1-percentscore)
from student,score,course
where credit=3 and student.sno=score.sno and course.cno=score.cno;
#(7)
create view score_group_view(s_no,s_name,c_no,c_name,total_score)
as select sno,sn,cno,cn,usualscore*percentscore+finalscore*（1-percentscore） as totalscore
from student,score,course
where max(finalscore) and student.sno=score.sno and course.cno=score.cno;
（6）在MySQL Workbench或命令行环境下，创建学生选课成绩视图score_view，显示选修3学分（含）学生学号(s_no)、姓名(s_name)、课程号(c_no)、课程名(c_name)、总成绩(total_score)。其中，总成绩按照——平时成绩平时成绩比重+考试成绩(1-平时成绩比重)公式计算。
（7）在MySQL Workbench或命令行环境下，创建学生成绩汇总视图score_group_view，提供每位学生考试成绩最高的选课信息，要求显示的视图字段包括学生学号(s_no)、姓名(s_name)、课程号(c_no)、课程名(c_name)、考试成绩(total_score)。其中，总成绩按照——平时成绩平时成绩比重+考试成绩(1-平时成绩比重)公式计算。
（6）创建学生选课成绩视图score_view：
CREATE VIEW score_view AS
SELECT student.sno AS s_no, sn AS s_name, course.cno AS c_no, cn AS c_name,
usualscore * percentscore + finalscore * (1 - percentscore) AS total_score
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno = course.cno
WHERE credit = 3;
（7）创建学生成绩汇总视图score_group_view：
CREATE VIEW score_group_view AS
SELECT student.sno AS s_no, sn AS s_name, course.cno AS c_no, cn AS c_name,
MAX(finalscore * (1 - percentscore) + usualscore * percentscore) AS total_score
FROM student
JOIN score ON student.sno = score.sno
JOIN course ON score.cno = course.cno
GROUP BY student.sno;
抱歉，我需要具体的问题才能给出解决方案。请提供具体的问题。