MySQL学习（五）

最新推荐文章于 2022-10-22 22:14:34 发布

Remoa

最新推荐文章于 2022-10-22 22:14:34 发布

阅读量2k

点赞数 4

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

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

版权

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

MySQL学习（五）

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

目录：

1、实现内容

2、代码示例

3、运行结果

1、实现内容：

图1.1 实验内容

2、代码示例：

#1、求计算机学院学生的总数。
select department_name, count(student_id) as student_number
from v_student, v_department
where v_student.department_id = v_department.department_id
and v_department.department_name = '计算机学院';

#2、查询年龄为工龄若干倍（可以为小数）的教师信息。
select teacher_id, teacher_name,
(extract(year from current_date()) - extract(year from teacher_birthday)) / 
(extract(year from current_date()) - extract(year from teacher_workyear)) as times
from v_teacher;

#3、查询任课数超过2门的教师的编号。
select *
from (
	select teacher_id, count(teacher_id) as task_number 
	from v_teacher_course_task
	group by teacher_id
)t
where t.task_number > 2;

#4、查询院系为计算机学院或经济与工商管理学院且年龄大于等于21的学生的学号、院系名称和姓名
select student_id, department_name, student_name
from v_student, v_department
where (extract(year from current_date()) - extract(year from student_birthday)) >= 21
and v_department.department_id = v_student.department_id
and v_department.department_name in('计算机学院', '工商管理学院');

#5、查询年龄为20或21的学生的学号、院系编号、姓名、年龄。
select student_id, department_id, student_name,
(extract(year from current_date()) - extract(year from student_birthday)) as age 
from v_student
where (extract(year from current_date()) - extract(year from student_birthday)) between 20 and 21;

#6、查询学号为3114005833，3114005849，3214005848，3114005847的学生的平均年龄。
select avg(t.a) as avg_age
from (
	select student_id, (extract(year from current_date()) - extract(year from student_birthday)) as a
	from v_student
	where student_id in('3114005833', '3114005849', '3214005848', '3114005847')
)t;

#7、查询最便宜的名字带“教材”二字的教材的信息。
select textbook_id, textbook_name, press, unit_price, publish_date
from v_textbook, (
	select min(unit_price) as price
	from v_textbook
	where textbook_name like '%教材%'
)t
where t.price = unit_price;

#8、把教师表中年龄小于等于60的计算机教师的年龄提高10%。
#首先根据教师表创建包含年龄项的新表
create table t(
	select teacher_id, teacher_name,
	(extract(year from current_date()) - extract(year from teacher_birthday)) as age
	from v_teacher
	where (extract(year from current_date()) - extract(year from teacher_birthday))< 60
);
#更新年龄
update t
set age = age * 1.1;
#查看更新后的结果
select *
from t;

3、运行结果：

答：（1）求计算机学院学生的总数。