山东大学《数据库系统》实验三：复制表、删除数据

布满荆棘的人生 2023-01-05 05:08 18阅读 0赞

## 实验题目：复制表、删除数据 ##

## 实验目的： ##

将 pub 用户的表及数据复制到主用户下，对不符合要求的数据进行删除。

## 实验原理和方法： ##

复制表结构及其数据：  
create table table\_name\_new as select \* from table\_name\_old  
删除语句：  
delete from r where p

## 实验步骤： ##

1．将pub用户下的Student\_31及数据复制到主用户的表test3\_01，删除表中的学号不全是数字的那些错误数据，学号应该是数字组成，不能够包含字母空格等非数字字符。

复制：create table test3_01 as select * from pub.student_31
    删除：delete from test3_01         
    	 	where translate(sid,'?0123456789','?') is not null

2．将 pub 用户下的 Student\_31 及数据复制到主用户的表 test3\_02，删除表中的出生日期和年龄(截止到2012 年的年龄，即年龄=2012-出生年份)不一致的那些错误数据。  
**提示：函数 extract(year from birthday)返回 birthday 的年份**

复制：create table test3_02 as select * from pub.student_31
     删除：delete from test3_02          
     	  	where age<>2012-extract(year from birthday)

3. 将 pub 用户下的 Student\_31 及数据复制到主用户的表 test3\_03，删除表中的性别有错误的那些错误数据（性别只能够是“男”、“女”或者空值）。

create table test3_03 as 
    select * from pub.student_31 
    	where sex='男' or sex='女' or sex is null

4. 将 pub 用户下的 Student\_31 及数据复制到主用户的表 test3\_04，删除表中的院系名称有空格的、院系名称为空值的或者院系名称小于 3 个字的那些错误数据。

复制：create table test3_04 as select * from pub.student_31
    删除：delete from test3_04         
    		where dname like'% %' 
    			or dname is null 
    			or length(dname)<3

5．将 pub 用户下的 Student\_31 及数据复制到主用户的表 test3\_05，删除表中的班级不规范的那些错误数据，不规范是指和大多数不一致。  
这个题知识点是学会用 SQL 找出不规范的数据，而不是用人工办法找不规范。

create table test3_05 as 
    select * from pub.student_31
        where class like'____'

6．将 pub 用户下的 Student\_31 及数据复制到主用户的表 test3\_06，删除表中的错误数据，不规范的数据也被认为是那些错误数据。  
(1)学号不全是数字；  
(2)出生日期和年龄不一致的(年龄=2012-出生年份)；  
(3)姓名有空格的或者长度小于 2 个字的；函数 length()返回字符串长度。  
(4)性别有错误的（只能够是“男”、“女”、空值）；  
(5)院系名称有空格的、院系名称为空值的；  
(6)院系名称小于 3 个字的；  
(7)班级数据有错误的（需要先找到班级里面的错误）。  
保留最后全部正确的数据。

复制：create table test3_06 as select * from pub.student_31
    删除：
    delete from test3_06 
    	where translate(sid,'?0123456789','?') is not null 
    		or age<>2012-extract(year from birthday) 
    		or name like'% %' or length(name)<2 
    		or dname like'% %' or dname is null 
    		or length(dname)<3 
    		or (sex is not null and sex not in (select distinct sex 
    					from test3_06 
    					where sex='男' or sex='女' ))
    		or class not like'____'

7.将 pub 用户下的Student\_course\_32及数据复制到主用户的表 test3\_07，删除其中的错误数据，错误指如下情况： 学号在学生信息 pub.student 中不存在的；

复制：create table test3_07 as select * from pub.student_course_32
    删除：delete from test3_07 where sid not in (select sid from pub.student)

8. 将 pub 用户下的Student\_course\_32及数据复制到主用户的表 test3\_08，删除其中的错误数据，错误指如下情况： 课程号和教师编号在教师授课表 pub.teacher\_course 中不同时存在的，即没有该教师教该课程；

复制：create table test3_08 as select * from pub.student_course_32 
    删除：delete from test3_08 a
    		where not exists (select 1 
    			from pub.teacher_course b 
    			where a.cid=b.cid and a.tid=b.tid)

9. 将 pub 用户下的Student\_course\_32 及数据复制到主用户的表 test3\_09，删除其中的错误数据，错误指如下情况： 成绩数据有错误（需要先找到成绩里面的错误）。  
这个题知识点是学会用 SQL 找出错误数据，而不是用人工办法找错误数据。  
**提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。**

复制：create table test3_09 as select * from pub.student_course_32 
    删除：delete from test3_09 where score<0 or score>100

10. 将 pub 用户下的Student\_course\_32及数据复制到主用户的表test3\_10，删除其中的错误数据，错误指如下情况：  
（1） 学号在学生信息 pub.student 中不存在的；  
（2） 课程号在课程信息 pub.course 中不存在的；  
（3） 教师编号在教师信息 pub.teacher 中不存在的；  
（4） 课程号和教师编号在教师授课表 pub.teacher\_course 中不存在的；  
（5） 成绩数据有错误（需要先找到成绩里面的错误）。  
保留最后正确的数据。

create table test3_10 as 
    select * from pub.student_course_32
    	where sid in (select sid from pub.student)
    		and cid in (select cid from pub.course)
    		and tid in (select tid from pub.teacher)
    		and (cid,tid) in (select cid,tid from pub.teacher_course)
    		and score>0 
    		and score<=100

## 结论分析与体会： ##

首先，有十足把握可以直接在复制数据中筛选出符合要求的数据，但更为有把握和保险的是先复制全部数据，再进行删除。  
其次，直接删除风险也是非常大的，可能导致多次建表，如果是真的业务数据，删错需要从备份数据中恢复数据，非常繁复。所以最好先写查询语句，然后将查询select\*改成删除delete即可。

#### 就实验过程中遇到和出现的问题，你是如何解决和处理的，自拟1－3道问答题： ####

Q1:查询1:isNumeric是SQL Server中的，那么在Oracle中如何判断学号是否全为数字？ A1:上网查询到TRANSLATE(string,from\_str,to\_str)可以将from\_str中的每个字符替换为to\_str中的相应字符。在 from\_str 中而不在 to\_str 中的额外字符将从 string 中被删除。to\_str 不能为空。  
所以利用translate(sid,’?0123456789’,’?’)，如果返回null 就表示是数字字符串，不为空则表示字符串里包含有其他字符。