数据库操作 -- 添加数据、查看表结构、多表查询、创建视图、创建存储过程、多表删除记录

创建数据表：T_student、T_course、T_score。

任务点：

• 向每个表插入3条测试数据（样本数据包含下面题目中使用的数据）；

• 查询出所有选修了“数据库原理”课程的学生学号、姓名和籍贯；

• 查询出“数据库原理”这门课的最高成绩；

• 查询有哪些课程没有被任何同学选修；

 • 查询有哪些学生没有选修“日语”课程；

• 创建视图，统计每门课程的平均分，最高分，最低分；

• 创建存储过程，查询指定课程名称的平均成绩。

• 删除所有选修“日语”的同学的成绩记录；

---

首先创建数据表：T_student、T_course、T_score。

        * auto_increment=10000  -->设置自动增长从10000开始

        * constraint 自定义主键名 primary key (主键字段名)

        * constraint 自定义外键名 foreign key (外键字段名) references 连接主键的表名(外键字段名)

create table T_student(
stud_id	varchar(10) not null comment'学号',
stud_name varchar(20) comment'姓名',
stud_sex char(2) comment'性别',
birth_date datetime comment'出生日期',
entry_Date datetime default now() comment'入学日期',
mobile varchar(11) comment'手机号码',
birth_place	varchar(30) comment'籍贯',
reserve	text comment'备注',
constraint pk_stud_id primary key (stud_id)
);

create table T_course(
course_id varchar(10) not null comment'课程编号',
course_name varchar(100) comment'课程名称',
teacher_name varchar(30) comment'教师姓名',
constraint pk_course_id primary key (course_id)
);

create table T_score(
score_id bigint auto_increment not null comment'成绩编号',
course_id varchar(10) comment'课程编号',
stud_id	varchar(10) comment'学号',
score decimal(5,2) comment'成绩',
constraint pk_score_id primary key (score_id),
constraint fk1_course_id foreign key (course_id) references T_course(course_id),
constraint fk_stud_id foreign key (stud_id) references T_student(stud_id)
)auto_increment=10000;

• 查看数据表结构

desc T_student;
desc T_course;
desc T_score;

• 向每个表插入4条测试数据（样本数据包含下面题目中使用的数据）；

insert into T_student values
('2021005','zzz','女','2003-02-22','2021-9-1','42986378351',' 湖南','无'),
('2021001','小鞠','女','2003-02-22','2021-9-1','13242384238',' 湖南','无'),
('2021002','小逗号','女','2003-05-07','2021-9-1','15692436562',' 湖南','无'),
('2021003','小明','男','2003-09-07','2022-9-1','16942695423',' 湖南','无'),
('2021004','小红','女','2003-01-06','2022-9-1','16395326423',' 湖南','无');

insert into T_course values
('C#001','数据库原理','熊老师'),
('C#002','日语','张老师'),
('C#003','Python编程开发','刘老师'),
('C#004','大学生教育','熊老师');

insert into T_score(course_id,stud_id,score) values
('C#001','2021001',95),
('C#002','2021003',90),
('C#001','2021002',98),
('C#003','2021004',93);

select * from T_student;
select * from T_course;
select * from T_score;

• 查询出所有选修了“数据库原理”课程的学生学号、姓名和籍贯；

【1.在C表中找出课程所对应的id】

select course_id from T_course where course_name='数据库原理'  

【2.把A、B表连接起来，用B表中与C表关联的主外键字段查询出id】

select a.stud_id,a.stud_name,a.birth_place    # 查询字段
from T_student a,T_score b    # 表明 别名
where a.stud_id=b.stud_id    # 条件 
and b.course_id=(select course_id from T_course   # 条件
where course_name='数据库原理');

select a.stud_id,a.stud_name,a.birth_place
from T_student a,T_score b
where a.stud_id=b.stud_id 
and b.course_id=(select course_id from T_course 
where course_name='数据库原理');

• 查询出“数据库原理”这门课的最高成绩；

select max(b.score) 最高成绩 from T_course a,T_score b
where a.course_id=b.course_id and a.course_name='数据库原理';

• 查询有哪些课程没有被任何同学选修；

select course_id,course_name from T_course where course_id not in(
select course_id from T_score);

 • 查询有哪些学生没有选修“日语”课程；

select stud_id,stud_name from T_student where stud_id 
not in(select stud_id from T_course a,T_score b where a.course_id=b.course_id and course_name!="日语");

 • 创建视图，统计每门课程的平均分，最高分，最低分；

        查看视图  -->   select * from 视图名; 

create view ss as 
select a.course_id 课程编号,a.course_name 课程名称,avg(b.score) 平均分,max(b.score) 最高分,min(b.score) 最低分
from T_course a,T_score b
where a.course_id=b.course_id
group by a.course_id

select * from ss;

• 创建存储过程，查询指定课程名称的平均成绩。

        查询存储  -->  call 存储过程名(查询内容)

create procedure cc(in mc varchar(100))
begin
	select a.course_name,avg(b.score)
	from T_course a,T_score b
	where a.course_id=b.course_id 
	and a.course_name=mc;
end

call cc('数据库原理')

• 删除所有选修“日语”的同学的成绩记录；

        【1.找出日语的课程id

        select course_id from T_course where course_name='日语' 】

        【2. 在成绩表中删除找出的课程id数据】

        delete from T_score where 条件

delete from T_score where 
course_id in (select course_id from T_course where course_name='日语');

---

完成啦，希望有帮助到大家，有疑问或问题也可在评论区留言~