MySQL学习（六）

最新推荐文章于 2024-04-19 14:47:05 发布

Remoa

最新推荐文章于 2024-04-19 14:47:05 发布

阅读量2.9k

点赞数 5

分类专栏：数据库文章标签： MySQL 查询更新增加外键

本文链接：https://blog.csdn.net/Remoa_Dengqinyi/article/details/55003790

版权

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

MySQL学习（六）

本MySQL学习系列博客共七篇，此为第六篇。

目录：

1、实现内容

2、代码示例

3、运行结果

1、实现内容：

图1.1 实现内容

2、代码示例：

#1、查询计算机学院学生的最高年龄、最低年龄及之间相差的年龄数。
select max(t.age) as max_age, min(t.age) as min_age, max(t.age) - min(t.age) as D_value
from (
	select student_id, v_student.department_id,
	(extract(year from current_date()) - extract(year from student_birthday)) as age
	from v_student, v_department
	where v_student.department_id = v_department.department_id
	and department_name = '计算机学院'
)t;

#2、查询总成绩在200分以上（含200分）的学生编号和总成绩。
select student_id, sum(grade) as sum_grade
from v_student_score
group by student_id
having sum(grade) >= 200;

#3、查询选修两门以上的课程的学生学号和选课门数。
select student_id, count(student_id) as num
from v_student_score
group by student_id
having count(*) > 2;

#4、求选课在2门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。
select student_id, sum(grade) as sum_grade 
from v_student_score
where grade >= 60
group by student_id
having count(*) >= 2
order by sum(grade) desc;

#5、查询至少有一门课程在80分以上的学生。
select v_student_score.student_id, student_name
from v_student, v_student_score
where grade > 80
and v_student.student_id = v_student_score.student_id
group by v_student_score.student_id
having count(*) >= 1;

#6、查询教材名称中不包含“Database”、“SQL”、“数据库”等字样的教材信息。
select * 
from v_textbook
where textbook_name not like '%Database%'
and textbook_name not like '%SQL%'
and textbook_name not like '%数据库%';

#7、为院系表的院系负责人增加外键约束。
#首先根据院系表创建一个新表v_admin
create table v_admin(
	select department_administrator, department_phone
	from v_department
);
alter table v_admin
add primary key(department_administrator);
#增加外键约束
alter table v_department
	add constraint fk_admin
	foreign key(department_administrator)
	references v_admin(department_administrator);
#查看外键
select * 
from information_schema.TABLE_CONSTRAINTS as t
where t.table_name = 'v_department';

#8、把讲授数据库的教师的工龄增加1年。
update v_teacher
left join (
	select v_teacher_course_task.teacher_id
	from v_teacher_course_task, v_course
	where v_course.course_id = v_teacher_course_task.course_id
	and v_course.course_name = '数据库'
)t
on v_teacher.teacher_id = t.teacher_id
set teacher_workyear = date_add(teacher_workyear, interval -1 year)
where v_teacher.teacher_id = t.teacher_id;

select * from v_teacher;

#9、将课程名称为“数据库”的成绩增加1分。
update v_student_score
left join(
	select v_student_score.course_id
	from v_student_score, v_course
	where v_course.course_name = '数据库'
	and v_course.course_id = v_student_score.course_id
)t
on v_student_score.course_id = t.course_id
set grade = grade + 1
where v_student_score.course_id = t.course_id;

3、运行结果：

（1）查询计算机学院学生的最高年龄、最低年龄及之间相差的年龄数。