数据库SQL语言实战（六）

内容重点

本次实战的重点就在于对表格本身的一些处理，包括复制表格、修改表格结构、修改表格数据

写此类SQL统一思考方式：

1、三个操作选哪个（创建表、修改表结构、修改表内容）

2、每个操作下具体要选择哪个（修改表结构——删除列还是增加列；创建表——要复制其他表内容还是仅仅复制其他表结构；修改表数据——怎么修改）

练习题

题目一

1、将pub用户下表student_41及数据复制到主用户的表test4_01中

2、使用alter table语句为表增加列：“总成绩:sum_score”。

3、使用update语句,利用pub.student_course，统计 “总成绩”；

create table test4_01 as
  select * from pub.student_41
 
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
  )

关键点：

1、三条语句必须要分开运行

2、alter、create后面除了跟table还可以跟view，所以必须指定；update后面只有table，所以可以省略

3、复制表格分为：

• 复制全表：创建表+全部复制表数据

create table table_name_new as (select * from table_name_old);

• 复制表结构：创建表+复制表结构

create table table_name_new like table_name_old;

题目二

1、将pub用户下表student_41及数据复制到主用户的表test4_02中

2、使用alter table语句为表增加列“平均成绩:avg_score” （小数点后保留1位）

3、利用pub.student_course，统计“平均成绩”，四舍五入到小数点后1位

create table test4_02 as
  select * from pub.student_41
 
alter table test4_02
  add avg_score numeric(3,1)
 
update test4_02 S
  set avg_score=(
    select avg(score)
    from pub.student_course T
    where S.sid=T.sid
  )

关键点：

1、三条语句必须要分开运行

2、保留小数点数据类型：numeric

题目三

1、将pub用户下表student_41及数据复制到主用户的表test4_03中

2、使用alter table语句为表增加列：“总学分:sum_credit”。

3、使用update语句,利用pub.student_course、pub.course，统计 “总学分”；

（这是需要注意：成绩及格才能够计算所得学分,一门课多个成绩都及格只计一次学分）

create table test4_03 as
  select * from pub.student_41
 
alter table test4_03
  add sum_credit int
 
update test4_03 S
  set sum_credit=(
    select sum(credit)
    from pub.course
    where cid in(
      select distinct cid
      from pub.student_course SC
      where S.sid=SC.sid
      and SC.score>=60
    )
  )

关键点：

1、三条语句必须要分开运行

2、一门成绩都及格只计一次学分说明在pub.student_course中有记录是同学生、同课程的，所以最内层的嵌套先利用distinct把同课程的记录去除，只保留不同的课程，再进行后续处理

题目四

1、将pub用户下表student_41及数据复制到主用户的表test4_04中。

2、根据列院系名称dname到pub.department找到对应院系编号did，将对应的院系编号回填到院系名称列dname中，如果表中没有对应的院系名称，则列dname中内容不变仍然是原来的内容。

create table test4_04 as
  select * from pub.student_41
 
update test4_04 T
set dname=(
  select did
  from pub.department D
  where T.dname=D.dname
)
where T.dname in(
  select dname
  from pub.department
)

关键点：

1、三条语句必须要分开运行

2、第二个问题本质是有条件的修改数据。找到——修改，没找到——保留原样。这意味着update、set后面一定要加上where作为条件

题目五

将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实现。）

create table test4_05 as
  select * from pub.student_41
 
alter table test4_05 
  add sum_score int
 
alter table test4_05 
  add avg_score numeric(3, 1)
 
alter table test4_05 
  add sum_credit int
 
alter table test4_05 
  add did varchar(2)

update test4_05 T
set 
  sum_score=(
    select sum(score)
    from pub.student_course SC
    where T.sid = SC.sid
  ),
  avg_score=(
    select avg(score)
    from pub.student_course SC
    where T.sid=SC.sid
  ),
  sum_credit=(
    select sum(credit)
    from pub.course
    where cid in(
      select distinct cid
      from pub.student_course SC
      where T.sid=SC.sid
      and SC.score>=60
      )
   )
 
update test4_05 T
set did=(
    select did
    from (
      select did,dname
      from pub.department
    union
      select did,dname
      from pub.department_41
    ) department
    where T.dname=department.dname
  ）
 
update test4_05
set did='00'
where did is null;

关键点：

1、中间有空格的语句必须要分开运行

2、到pub.department和pub.department_41中找意味着需要用到union将两个表求并集。

3、如果没有院系则填‘00’，本质也就是取出来的did为NULL则填‘00‘

题目六

1、将pub用户下的Student_42及数据复制到主用户的表test4_06中,对表中的数据进行整理，修复那些不规范的数据：

2、剔除姓名列中的所有空格；

create table test4_06 as
  select * from pub.student_42

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

关键点：

1、update中replace函数的应用

题目七

将pub用户下的Student_42及数据复制到主用户的表test4_07中,对表中的数据进行整理，修复那些不规范的数据：

对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；

update test4_07
set sex= replace( sex,' ','')
 
update test4_07
set sex= replace( sex,'性','')

关键点：

1、如何确定性别中哪些数据是不规范的：

select sex
from test4_06
group by sex

2、group by后，select语句后面只能是：

• 1、通过 GROUP BY 子句指定的聚合键
• 2、聚合函数（SUM 、AVG 等）
• 3、常量

题目八

将pub用户下的Student_42及数据复制到主用户的表test4_08中,对表中的数据进行整理，修复那些不规范的数据：

对班级列进行规范（需要先确定哪些班级不规范）。

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

题目九

将pub用户下的Student_42及数据复制到主用户的表test4_09中,对表中的数据进行整理，修复那些不规范的数据：

年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

update test4_09 T
set age=(
  select 2012-extract (year from birthday)
  from test4_09 S
  where T.sid=S.sid
  )
where T.age is null;

题目十

将pub用户下的Student_42及数据复制到主用户的表test4_10中,对表中的数据进行整理，修复那些不规范的数据：

1. 剔除姓名列中的所有空格；
2. 剔除院系名称列中的所有空格；
3. 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
4. 对班级列进行规范（需要先确定哪些班级不规范）。
5. 年龄为空值的根据出生日期设置学生年龄（截止到2012年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

create table test4_10 as
select *
from pub.student_42;
 
update test4_10
set name=replace(name,' ','');
 
update test4_10
set dname=replace(dname,' ','');
 
update test4_10
 set age=(
select (2012-extract (year from birthday))
from pub.student_42
where test4_10.sid=sid
)
where test4_10.age is null;
 
update test4_10
set sex= replace( sex,' ','');
update test4_07
set sex= replace( sex,'性','');
 
update test4_10
set class=replace(class,'级','');

总结

如果能帮助到大家，大家可以点点赞、收收藏呀~