What’s more

山东大学 2020级数据库系统 实验一
山东大学 2020级数据库系统 实验二
山东大学 2020级数据库系统 实验三
山东大学 2020级数据库系统 实验四
山东大学 2020级数据库系统 实验五
山东大学 2020级数据库系统 实验六
山东大学 2020级数据库系统 实验七
山东大学 2020级数据库系统 实验八、九

写在前面

做数据库实验一定要静得下心来，才能发现其中的错误然后进行改正。同时，如果发现 SQL 语句总是报错，“一定是你错了，只是不知道错在哪里！”

其次，SQL 语句中较为复杂的点博主都进行了注释，希望大家一定要看懂思路后自己写一遍，而不是盲目的 Ctrl+C，Ctrl+V，切记切记！！

实验四

实验四主要考察的内容如下：
对于 alter 语句的掌握程度以及是否能够使用它来对表中的属性进行操作；

对于 update … set … where 子句的使用；

对字符串的处理以及删除字符串中相应的字符；

• 4-1 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_01 中，使用 alter table 语句为表增加列： “总成绩:sum_score”。
  使用 update 语句,利用 pub.student_course，统计 “总成绩”；
  思路：
  1. 先指用 alter table test4_01 add sum_score int 来添加对应的属性列。需要注意的是 add 子句后面的属性列还有跟上该列的类型说明；
  2. 使用 update … set … 结构计算每个学生的总成绩然后填入 sum_score 列中即可；

alter table test4_01
	add sum_score int

update test4_01 S
set sum_score =
	(select sum(score)
	from pub.student_course T
	where S.sid = T.sid
	group by sid)

• 4-2 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_02 中，使用 alter table 语句为表增加列 “平均成绩:avg_score” （小数点后保留 1 位）。
  利用 pub.student_course，统计“平均成绩”，四舍五入到小数点后 1 位
  思路：

  1. 思路其实和 4-1 相似，添加对应列，然后分别进行平均成绩的计算即可；
  2. 保留小数点后 1 位使用 round() 函数即可；

  需要注意的是：在对 avg_score 属性列进行定义的时候，由于保留一位小数，因此需要使用 numeric(3, 1) 来设置哦~~

alter table test4_02
	add avg_score numeric(3, 1)

update test4_02 S
set avg_score = 
	(select round(avg(score), 1)
	from pub.student_course T
	where S.sid = T.sid
	group by sid)

• 4-3 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_03 中，使用 alter table 语句为表增加列： “总学分:sum_credit”。
  使用 update 语句,利用 pub.student_course、pub.course，统计 “总学分”；
  这时需要注意：成绩及格才能够计算所得学分,一门课多个成绩都及格只计一次学分。
  思路：
  1. 使用 alter … add … 来添加对应的属性列，同时注意列属性的定义为 int 即可；
  2. 注意题目中的提示“一门课多个成绩都及格只计一次学分”，因此我们不能简单地使用 score > 60 然后 sum(credit) ；正确的方法应该是得到每个学生每门课的最高成绩，用这个最高成绩来判断是否 > 60，若大于 60 ，则计一次学分；由于每个学生的最高分是唯一的，因此我们在 sum(credit) 时相当于只计算了一次，而不是像前者一样计算多次。

alter table test4_03
	add sum_credit int

update test4_03 t0
set sum_credit = 
	(select sum(credit)
	from pub.course natural join 
		(select sid, cid, max(score) max_score		-- 得到每个学生每门课的最高分
		from pub.student_course
		group by sid, cid) t1
	where t0.sid = sid
	and t1.max_score >= 60				-- 利用最高分来进行判断
	group by sid)

• 4-4 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_04 中。 根据列院系名称 dname 到 pub.department 找到对应院系编号 did，将对应的院系编号回填到院系名称列 dname 中，如果表中没有对应的院系名称，则列 dname 中内容不变仍然是原来的内容。
  思路：
  1. 首先使用 create table … as select * … 来将 pub.student_41 中的所有数据 copy 到 test4_04 中；
  2. 然后使用 dname 去判断这个 dname 是否在 pub.department 中；
  3. 若是，则将 dname 更新为相应的 did；反之则不更新；

create table test4_04 as
select *
from pub.student_41

update test4_04 t0
set dname =
	(select did
	from pub.department
	where dname = t0.dname)
where t0.dname in
	(select dname
	from pub.department)

• 4-5 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_05 中，使用 alter table 语句为表增加 4 个列：“总成绩:sum_score”、 “平均成绩:avg_score”、“总学分:sum_credit”、“院系编号:did varchar(2) ”。
  （1） 利用 pub.student_course、pub.course，统计 “总成绩”；
  （2） 利用 pub.student_course、pub.course，统计“平均成绩”，四舍五入到小数点后 1 位；
  （3） 利用 pub.student_course、pub.course，统计 “总学分”；
  （4） 根据院系名称到 pub.department 和 pub.department_41 中，找到对应编号，填写到院系编号中， 如果都没有对应的院系，则填写为 00。
  说明：执行 update 后，在查询表中数据，可能出现顺序变化，这是正常，因为数据在表中是无序。需要 顺序的时候可以通过 orderby 实现。
  思路：
  1. 首先使用 create 语句进行表和数据的复制；
  2. 然后使用 alter table 语句在复制得到的表中添加对应列；注意 alter 似乎不能一起添加，因此需要一列一列地进行属性列地添加；
  3. 最后使用 update … set … 子句来对表中的属性列进行更新即可（set 可以同时更新多列哦~~）

需要注意的是：对于院系编号地更新时，现在是从两个表中寻找对应的院系编号而不是一个表了（当时卡了好久/(ㄒoㄒ)/~~），这两个表可以通过 union 来连接。（由于是前面问题的综合，因此代码较长）

create table test4_05 as
select *
from pub.student_41

alter test4_05
	add sum_score int
-----------------分开哦-----------------------
alter test4_05
	add avg_score numeric(3, 1)
-----------------分开哦-----------------------
alter test4_05
	add sum_credit int
-----------------分开哦-----------------------
alter test4_05
	add did varchar(2)

update test4_05 t0
set
sum_score = 
	(select sum(score)
	from pub.student_course t1
	where t0.sid = t1.sid
	group by sid), 
avg_score =
	(select round(avg(score), 1)
	from pub.student_course t2
	where t0.sid = t2.sid
	group by sid),
sum_credit = 
	(select sum(credit)
	from pub.course natural join 
		(select sid, cid, max(score) max_score
		from pub.student_course
		group by sid, cid) t1
	where t0.sid = sid
	and t1.max_score >= 60
	group by sid),
did = 
case
	when dname in
	(	(select dname
		from pub.department)
		union
		(select dname
		from pub.department_41)	)
	then
	(select did
	from 
	(	(select dname, did
		from pub.department)
		union
		(select dname, did
		from pub.department_41)	)
	where dname = t0.dname)
else '00'
end

• 4-6 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_06 中,对表中的数据进行整理，修复那些不规范的数据： 剔除姓名列中的所有空格；
  思路：

  1. 使用 create 来将表和数据进行复制；

  2. 为了删除空格，我们可以使用函数 replace(string, target_str, replace_str) 来进行；

     第一个参数 string 表示需要修改的字符串；第二个参数 target_str 表示在该字符串中需要修改的字符（为了查找到该字符）；第三个参数 replace_str 表示需要将 target_str 替换为什么字符；

     在本题中，我们可以这样使用 replace 函数：replace(name, ’ ', ‘’)，找到空格并将它删去；（translate()函数也有类似的功能！）

create table test4_06 as
select *
from pub.student_42

update test4_06
set name = replace(name, ' ', '')

• 4-7 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_07 中,对表中的数据进行整理，修复那些不规范的数据： 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
  思路：
  1. 使用 create 子句来对表和表中的数据进行复制；
  2. 为了看到哪些才是不规范的数据，我们可以首先使用：
     select distinct sex, count(sex) from test4_07 group by sex
     来查看所谓的“不规范数据”。发现：不规范的数据是加上了“性”字的 sex 值，同时有些 sex 值的前后可能还有空格等；
  3. 具体的删除方法我们还是可以使用 replace() 函数；

create table test4_07 as
select *
from pub.student_42

update test4_07
set sex = 
case
	when sex like '%男%' then '男'		-- %表示省略一个或多个字符
	when sex like '%女%' then '女'
	else sex
End

• 4-8 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_08 中,对表中的数据进行整理，修复那些不规范的数据： 对班级列进行规范（需要先确定哪些班级不规范）。
  思路：
  1. 首先使用 create 来对表以及表中的数据进行复制；
  2. 然后使用
     select distinct class, count(class) from test4_08 group by class
     来看看哪些班级数据是不规范的数据。发现，不规范的班级数据后面又“级”字，我们只需要使用 replace() 函数来将它去掉即可；

create table test4_08 as
select *
from pub.student_42

update test4_08
set class = replace(class, '级', '')

• 4-9 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_09 中,对表中的数据进行整理，修复那些不规范的数据： 年龄为空值的根据出生日期设置学生年龄（截止到 2012 年的年龄，即年龄=2012-出生年份），年龄不 为空值的不要改变。
  思路：
  1. 首先使用 create 来对表以及表中的数据进行复制；
  2. 然后使用 update 和 where 判定语句来对 age 列进行更新即可；
  3. 期间还是用到了实验三中提到的 extract() 函数哦~~

create table test4_09 as
select *
from pub.student_42

update test4_09
set age = 2012 - extract(year from birthday)
where age is null

• 4-10 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_10 中,对表中的数据进行整理，修复那些不规范的数据：
  （1） 剔除姓名列中的所有空格；
  （2） 剔除院系名称列中的所有空格；
  （3） 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
  （4） 对班级列进行规范（需要先确定哪些班级不规范）。
  （5） 年龄为空值的根据出生日期设置学生年龄（截止到 2012 年的年龄，即年龄=2012-出生年份）， 年龄不为空值的不要改变。
  思路：
  1. 就是对前面所有的问题的综合，由于 set 中可以同时设置多个列，因此将它们连接起来即可；

create table test4_10 as
select *
from pub.student_42

update test4_10
set name = replace(name, ' ', ''),
dname = replace(dname, ' ', ''), 
sex = 
case
	when sex like '%男%' then '男'
	when sex like '%女%' then '女'
	else sex
end,
class = replace(class, '级%', ''),
age =
case 
	when age is null then 2012 - extract(year from birthday)
	else age
end

再次强调：一定是看懂思路之后自己实践哈~~
有问题还请斧正！