数据库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,'级','');

总结

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

相关文章
|
3天前
|
JavaScript 关系型数据库 MySQL
Python实战:从猎聘网获取职位信息并存入数据库
Python实战:从猎聘网获取职位信息并存入数据库
|
3天前
|
SQL 关系型数据库 MySQL
mysqldiff - Golang 针对 MySQL 数据库表结构的差异 SQL 工具
Golang 针对 MySQL 数据库表结构的差异 SQL 工具。https://github.com/camry/mysqldiff
34 7
|
3天前
|
SQL 存储 安全
SQL入门与进阶:数据库查询与管理的实用指南
一、引言 在数字化时代,数据库已经成为各行各业存储、管理和分析数据的关键基础设施
|
5天前
|
SQL 安全 数据库
SQL实践指南:从基础到进阶的数据库查询与管理
一、引言 在数据驱动的时代,数据库已成为各行各业不可或缺的一部分
|
5天前
|
SQL 安全 数据库
掌握SQL:数据库操作与查询的精髓
一、引言 在数据驱动的时代,数据库是任何组织或项目不可或缺的一部分
|
5天前
|
SQL 存储 数据库
掌握SQL:数据库查询与管理的关键技能
一、引言 在信息化快速发展的今天,数据库已成为存储、处理和查询数据的重要工具
|
5天前
|
SQL 存储 安全
深入SQL:掌握数据库操作与查询的艺术
一、引言 在信息化高度发展的今天,数据库已成为企业、组织甚至个人管理数据的核心工具
|
5天前
|
SQL 存储 数据库
深入理解SQL:从基础查询到高级数据库管理
一、引言 SQL(结构化查询语言)是数据库管理系统的核心语言,用于存储、检索、更新和管理关系数据库中的数据
|
5天前
|
SQL 存储 监控
精通SQL:数据库查询与管理的全面指南
一、引言 在当今的数据驱动世界中,[数据库](https://www.iyxwzx.com/)技术无处不在,而SQL(结构化查询语言)则是数据库操作的基础
|
5天前
|
SQL 安全 数据库
精通SQL:数据库查询与管理的实战指南
一、引言 在当今数字化时代,[数据库](https://www.iyxwzx.com/)已成为企业、组织和个人不可或缺的数据[管理](https://www.iyxwzx.com/news/)工具