实验四 数据更新操作

一. 实验目的

掌握INSERT/UPDATE/DELETE语句的使用。

体会数据更新操作对完整性约束的影响。

二. 实验准备

1. 准备基本表中的数据。

2. 了解INSERT、UPDATE及DELETE语句。

三. 实验要求

实验内容中要求的数据必须插入到相关基本表中，在此基础上每个表需另插入不少于3个元组。

注意操作结果的截图与保存，供撰写实验报告使用。

四. 实验内容

1. 数据插入操作

（1）向表（Class）中插入数据


			
ClsNO
			
			

			
ClsName
			
			

			
Director
			
			

			
Specialty
			
		

			
CS01
			
			

			
计算机一班
			
			

			
张宁
			
			

			
计算机应用
			
		

			
CS02
			
			

			
计算机二班
			
			

			
王宁
			
			

			
计算机应用
			
		

			
MT04
			
			

			
数学四班
			
			

			
陈晨
			
			

			
数学
			
		

			
PH08
			
			

			
物理八班
			
			

			
葛格
			
			

			
物理
			
		

			
GL01
			
			

			
地理一班
			
			

			
张四
			
			

			
应用地理
			
		
 

插入数据之后使用命令：Select * from Class; 检查插入数据的正确性

 

(2) 向表（Student）中插入数据

 

 

 


			
Sno
			
			

			
Sname
			
			

			
Ssex
			
			

			
ClsNO
			
			

			
Saddr
			
			

			
Sage
			
			

			
Height
			
		

			
20130101
			
			

			
王军
			
			

			
男
			
			

			
CS01
			
			

			
下关40#
			
			

			
20
			
			

			
1.76
			
		

			
20130102
			
			

			
李杰
			
			

			
男
			
			

			
CS01
			
			

			
江边路96#
			
			

			
22
			
			

			
1.72
			
		

			
20130306
			
			

			
王彤
			
			

			
女
			
			

			
MT04
			
			

			
中央路94#
			
			

			
19
			
			

			
1.65
			
		

			
20130107
			
			

			
吴杪
			
			

			
女
			
			

			
PH08
			
			

			
莲化小区74#
			
			

			
18
			
			

			
1.60
			
		
插入数据之后使用命令：Select * from Student; 检查插入数据的正确性

（3）向表（Course ）中插入数据


			
Cno
			
			

			
Cname
			
			

			
Cpno
			
			

			
Credit
			
		

			
0001
			
			

			
高等数学
			
			

			
Null
			
			

			
6
			
		

			
0003
			
			

			
计算机基础
			
			

			
0001
			
			

			
3
			
		

			
0007
			
			

			
物理
			
			

			
0001
			
			

			
4
			
		
插入数据之后使用命令：Select * from Course; 检查插入数据的正确性

（4）向表（SC ）中插入数据


			
SNO
			
			

			
CNO
			
			

			
Grade
			
		

			
20130101
			
			

			
0001
			
			

			
90
			
		

			
20130101
			
			

			
0007
			
			

			
86
			
		

			
20130102
			
			

			
0001
			
			

			
87
			
		

			
20130102
			
			

			
0003
			
			

			
76
			
		

			
20130306
			
			

			
0001
			
			

			
87
			
		

			
20130306
			
			

			
0003
			
			

			
93
			
		

			
20130107
			
			

			
0007
			
			

			
85
			
		

			
20130306
			
			

			
0007
			
			

			
90
			
		
2.数据修改与删除操作

首先在student表中插入一条新记录：学号：20131101、姓名：张三、性别：男、年龄：19、班级编号：‘CS01’。

对于student表，将所有班级号为‘CS01’的，并且年龄小于20岁的学生的班级号改为‘CS02’。
	
对于student表，删掉所有年龄小于20岁，并且专业号为‘CS02’的学生的记录。
五、实验步骤：

在查询分析器中完成实验内容。

--向class中插入数据
insert into class values('CS01', '计算机一班', '张宁', '计算机应用')
insert into class values('CS02', '计算机一班', '王宁', '计算机应用')
insert into class values('MT04', '数学四班', '陈晨', '数学')
insert into class values('PH08', '物理八班', '葛格', '物理')
insert into class values('GL01', '地理一班', '张四', '应用地理')

--检验插入效果
select * from class

--向student中插入数据
insert into student values('20130101', '王军', '男', 'CS01', '下关40#', 20, 1.76)
insert into student values('20130102', '李杰', '男', 'CS01', '江边路96#', 22, 1.72)
insert into student values('20130306', '王彤', '女', 'MT04', '中央路94#', 19, 1.65)
insert into student values('20130107', '吴杪', '女', 'PH08', '莲化小区74#', 18, 1.60)

--检验插入效果
select * from student

--向course中插入数据
insert into course values('0001', '高等数学', Null, 6)
insert into course values('0003', '计算机基础', '0001', 3)
insert into course values('0007', '物理', '0001', 4)

--检验插入效果
select * from course

--向sc中插入数据
insert into sc values('20130101', '0001', 90)
insert into sc values('20130101', '0007', 86)
insert into sc values('20130102', '0001', 87)
insert into sc values('20130102', '0003', 76)
insert into sc values('20130306', '0001', 87)
insert into sc values('20130306', '0003', 93)
insert into sc values('20130107', '0007', 85)
insert into sc values('20130306', '0007', 90)

--检验插入效果
select * from sc

--向student中插入新数据
insert into student(sno, sname, ssex, sage, clsNO) values('20131101', '张三', '男', 19, 'CS01')

--修改student数据
update student set clsNo='CS02' where clsNO='CS01' and sage<20 

--删除student数据
delete from student where sage<20 and clsNO='CS02'

--大吉大利，今晚AC

 

目录

 

MongoDB 更新文档

update() 方法

实例

各种更新操作符

"$set"

"$set" 与 "." 操作

"$unset"

"$inc"

"$rename"

"$pop"

"$push"

"$addToSet"

$pull

findAndModify()

附：

MongoDB 更新文档

MongoDB 使用 update() 和 save() 方法来更新集合中的文档。

update() 方法

update() 方法用于更新已存在的文档。语法格式如下：

db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>
   }
)

参数说明：

query : update的查询条件，类似sql update查询内where后面的。
	
update : update的对象和一些更新的操作符（如$,$inc...）等，也可以理解为sql update查询内set后面的
	
upsert : 可选，这个参数的意思是，如果不存在update的记录，是否插入objNew,true为插入，默认是false，不插入。
	
multi : 可选，mongodb 默认是false,只更新找到的第一条记录，如果这个参数为true,就把按条件查出来多条记录全部更新。
	
writeConcern :可选，抛出异常的级别。
官方连接连接地址：https://docs.mongodb.com/manual/reference/update-methods/

实例

我们先向数据库插入点数据（集合名称为 products）：

{
  _id: 100,
  sku: "abc123",
  quantity: 250,
  instock: true,
  reorder: false,
  details: { model: "14Q2", make: "xyz" },
  tags: [ "apparel", "clothing" ],
  ratings: [ { by: "ijk", rating: 4 } ]
}

查询截图：



各种更新操作符

"$set"

"$set" 用来指定一个键的值。如果这个键不存在，则创建它。

示例：更新 quantity 字段、更新 details 的内容以及更新 tags 的值。

db.products.update(
   { _id: 100 },
   { $set:
      {
        quantity: 500,
        details: { model: "14Q3", make: "xyz" },
        tags: [ "coats", "outerwear", "clothing" ]
      }
   }
)

查询结果（可以看到对应的值已经被修改）：



"$set" 与 "." 操作

示例：使用 "." 符号更新数据内容

db.products.update(
   { _id: 100 },
   { $set: { "details.make": "zzz" } }
)

 



同样可以使用 "." 符号操作数组内容

db.products.update(
   { _id: 100 },
   { $set:
      {
        "tags.1": "rain gear",
        "ratings.0.rating": 2
      }
   }
)

示例：创建不存在的 key 值

db.products.update(
   { _id: 100 },
   { $set:
      { "name": "not the name" }
   }
);



"$unset"

从文档中移除指定的键。若要完全删除键 “name” ,使用 “$unset” 即可：

db.products.update(
   { _id: 100 },
   { $unset:
      { "name": 1 }
   }
);



"$inc"

"$inc" 修改器用来增加已有键的值，或者在键不存在时创建一个键。inc就是专门来增加（和减少）数字的。

"$inc"只能用于整数、长整数或双精度浮点数。要是用在其他类型的数据上就会导致操作失败。

"$inc" 可以接收正数或者负数的值。

示例：将 "quantity" 值减少 1

db.products.update(
   { _id: 100 },
   { $inc :
      { "quantity": -1 }
   }
);



"$rename"

$rename 操作符可以重命名字段名称，新的字段名称不能和文档中现有的字段名相同。

// 语法: 
{ $rename: { <old name1>: <new name1>, <old name2>: <new name2>, ... } }

示例：修改 "sku" 为 "skuName"

db.products.update(
   { _id: 100 },
   { $rename :
      { "sku": "skuName" }
   }
);



"$pop"

只能用于对 数组（array）进行操作。用于移除数组第一个元素，或者用于移除数组的最后一个元素。

// 语法 -1 表示移除数组第一个元素， 1 表示移除数组最后一个元素
{ $pop: { <field>: <-1 | 1>, ... } }

首先插入数据（集合名称：students）：

// 插入数据 
{ _id: 1, scores: [ 8, 9, 10 ] }

示例：移除第一个元素

db.students.update( { _id: 1 }, { $pop: { scores: -1 } } )

// 查询返回
{ _id: 1, scores: [ 9, 10 ] }

示例：移除最后一个元素

db.students.update( { _id: 1 }, { $pop: { scores: 1 } } ) 
// 查询返回 
{ _id: 1, scores: [ 9 ] }

"$push"

如果指定的键已经存在，会向已有的数组末尾加入一个元素，要是没有就会创建一个新的数组。

// 插入数据
{
    "_id" : 100.0,
    "details" : {
        "model" : "14Q3",
        "make" : "zzz"
    },
    "tags" : [ 
        "coats", 
        "outerwear", 
        "clothing"
    ],
    "sku" : "abc123"
}

示例： 向 tags 中增加一个元素

db.products.update( { _id: 100}, { $push: {tags: "push"} } ); 
// 返回结果 
{..."tags" : [ "coats", "outerwear", "clothing", "push"], ...}



示例：添加一个不存在的 key “name”

db.getCollection('products').update( {_id : 100}, {$push: { name : "sku" } } );



可以看到，push 只能对数组进行操作。

"$addToSet"

向集合数据中添加数组内容，如果数据存在就做追加操作，如果数据不存在就做新增 key 与内容操作。

// 语法 
{"$addToSet" : {成员： 内容} }

示例：向数据中的 name 字段追加内容（紧接着前面"$push"操作的数据来）

db.getCollection('products').update( {_id : 100}, { $addToSet : { name : "Name" } } );



示例：追加一个不存在的 key item （此时的操作和 $push 类似）

db.getCollection('products').update( {_id : 100}, { $addToSet : { item : "Name" } } );



$pull

// 语法
{ $pull: { <field1>: <value|condition>, <field2>: <value|condition>, ... } }

 pull操作符移除指定字段值为数组，且匹配pull操作符移除指定字段值为数组，且匹配pull语句声明的查询条件的所有元素。

示例：移除 tags 中的 “push” 与 coats 值

db.products.update(
    { _id : 100 },
    { $pull: { tags: { $in: [ "push", "coats" ] } } }
)



示例：移除 name 中的 “Name”

db.products.update( { _id : 100 }, { $pull: { name : "Name" } } )



findAndModify()

另外简单介绍下这个操作：

// 语法如下
db.collection.findAndModify({
    query: <document>,
    sort: <document>,
    remove: <boolean>,
    update: <document>,
    new: <boolean>,
    fields: <document>,
    upsert: <boolean>,
    bypassDocumentValidation: <boolean>,
    writeConcern: <document>,
    collation: <document>,
    arrayFilters: [ <filterdocument1>, ... ]
});

如果我们想对 某个数据进行全部修改，就可以使用 findAndModify() 方法：

db.products.findAndModify({
   query: { _id: 100},
   update: {
    "details" : {
        "model" : "14Q3",
        "make" : "zzz"
    },
    "tags" : [ 
        "coats", 
        "outerwear", 
        "clothing", 
        "push"
    ],
    "sku" : "abc123"
   }
});

附：

官网链接：https://docs.mongodb.com/manual/reference/operator/update/

 

SQL语言-数据更新操作
1.在教学管理数据库jxgl中，使用MySQL Workbench在表中导入表5-1和表5-2的数据
将所给数据导入student表：
insert into student
values ('2005010','赵春江',18,'男','Cs'),
       ('2005011','赵丽萍',19,'女','Ch'),
       ('2005012','陈景欢',20,'男','Is'),
       ('2005013','陈婷婷',16,'女','Ph'),
       ('2005014','李军',16,'女','Ph');

导入后查看结果：

将所给数据导入sc表：
insert into sc 
values ('2005010','1',87),
	   ('2005010','2',NULL),
       ('2005010','3',80),
       ('2005010','4',87),
       ('2005010','6',85),
       ('2005011','1',52),
       ('2005011','2',47),
       ('2005011','3',53),
       ('2005011','5',45),
       ('2005012','1',84),
       ('2005012','3',NULL),
       ('2005012','4',67),
       ('2005012','5',81);

导入数据后查看结果：

2.使用SQL语句进行以下更新操作


给IS系的学生开设7号课程，建立相应的选课记录，成绩为空
insert into sc  #插入数据
select sno,'7',null    #令sno为Is系的学生的学号，cno为'7',成绩为空
from student
where sdept='Is';

查看结果：




从运行结果可以看出Is系的两门学生的选课记录中增加了7号课程且课程成绩为NULL。


在表student中检索每门课均不及格的学生学号、姓名、年龄、性别及所在系信息，并把检索到的信息存入信标ts中。
首先创建表ts，由于其属性和student相同，故直接用create table name1 like name2的方式建表，然后再用insert语句筛选出数据插入其中
create table ts like student;  #通过create table  name  like name2 可创建属性相同的表
insert into ts
select *
from student natural join sc  #用student和sc的自然连接表示
group by sno #用学号分组
having max(grade)<60;  #max(grade)<60说明所有课均不及格

执行结果：





将学号为2005001的学生姓名修改为"李华",年龄增加1岁
update student set sname="李华" ,sage=sage+1 #将年龄加1,名字改为'李华'
where sno='2005001';

运行结果：


起初2005001对应的是‘钱横’且age=18，故可知修改成功。


把选修了"数据库系统"课程而成绩不及格的学生成绩全部改为空值
update sc set grade=NULL 
where cno=(select distinct cno from course where cname='数据库系统') and grade<60; 

运行结果：


之前2005011对应的数据库成绩为52，现在改为null，故可知正确。


学生王林在3号课程考试作弊，将该课成绩改为空值
由于原本的数据库中王林没用选修3号课程，故现增加一组数据：
insert into sc values('2005002','3',100);

然后再进行操作
update sc set grade=null
where sno=(select distinct sno from student where sname='王林') and cno='3';

运行结果：





把成绩低于总平均成绩的女同学成绩提高5%
SET SQL_SAFE_UPDATES = 0; #因为要修改非主键元素，故必须关闭safe_update
with avg_grade(the_avg_grade) as
(select avg(grade)
 from sc)
update sc set grade=grade*1.05
where sno in (select sno from student where ssex='女') and grade<(select distinct * from avg_grade);

运行结果：

从之前的结果中可以看出赵丽萍的成绩为47-53-45，故完成了更新。共两个女性，而另一位女性的成绩是较高的，高于平均成绩故不需要更新，因此更新仅对赵丽萍操作。


在基本表sc中修改课程号为"2"号课程的成绩，成绩小于等于80分的降低2%,成绩大于80分时降低1%
update sc set grade=grade*(1-0.02)
where grade<=80 and cno='2';
update sc set grade=grade*(1-0.01)
where grade>80 and cno='2';
#注意这里的顺序不可以反，否则可能导致重复更新

另一种写法:
update sc
set grade=case
when grade<=80 then grade*(1-0.02)
else grade*(1-0.01)
end
where cno='2';
#用case when then else语句

结果：



与之前对比可知减分操作完成。


把"钱横"的选课记录全部删去
delete from sc
where sno=(select sno from student where sname='钱横');

由于之前我们已经将钱横改为李华，故在此处我将删除李华的选课记录。
delete from sc
where sno=(select sno from student where sname='李华');

运行结果：



故可知删除记录成功。


能删除2005001的学生记录吗？一定要删除应该如何操作？给出操作命令
可以根据学号删除学生记录。
删除该学生的记录意味着删除该学生对应的student和sc记录，故需要删除两个表中的记录。
delete from sc
where sc.sno in(
    select sno 
    from student 
    where sno = '2005001');
delete from student where sno = '2005001'

删除后查询可知均为空：

实验目的

掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。

实验内容

1. 插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

insert into student(sno,sname,sage)
values ('95030','李莉',18)

2. 插入如下选课记录（95030，1）

insert into sc(sno,cno)
values('95030',1)

3. 计算机系学生年龄改成20

update student
set sage=20
where sdept='CS'

4. 把数学系所有学生成绩改成0

update sc
set grade=0
where 'MA'=
(select sdept
from student
where student.sno=sc.sno)

5. 把低于总平均成绩的女同学成绩提高5分

update sc 
set grade+=5
where grade<
(select avg(grade) 
from sc inner join student
on student.sno=sc.sno
where ssex='女')

6. 删除95030学生信息

delete
from student
where sno='95030'

7. 删除SC表中无成绩的记录

delete 
from sc
where grade is null;

8. 删除张娜的选课记录

delete
from sc
where sno=
(select sno from student where sname='张娜')

9. 删除不及格的学生选课记录

delete
from sc
where grade<60

10.删除数学系所有学生选课记录

delete
from sc
where sno in 
(select sno from student where sdept='MA')

11. 删除所有未被选修的课程

delete
from course
where cno not in (select cno from sc)

12. 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

create table STU 
(sno char(8), 
sname char(8), 
ssex char(2) 
)

insert into STU(sno,sname,ssex);

select distinct student.sno,sname,ssex
from student,sc 
where student.sno not in
(select sno from sc where grade<80) and student.sno=sc.sno

13. 建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade

create table sdeptgrade 
(sdept char(8) primary key, 
avggrade int
) 

insert into sdeptgrade;

select student.sdept, avg(sc.grade) 
from student inner join sc
on (student.sno = sc.sno) 
group by student.sdept;

实验总结

删除主键表数据如果有外键约束就会报错