MySQL数据更新

MySQL数据更新:

导读:

  1. 该练习是本人课程学习进行整理的,若有不对,欢迎指出!
  2. 该练习没有配套的sql文件,如果需要可以看之前的文章有student表等(MySQL查询练习);
  3. 这是最后一部分练习题,如果后面有更新,会补上。

第一部分:

1、插入数据

(1)在学生表Student中插入数据:
Sno:9512102 Sname:刘晨 Ssex:男 Sage:20 Sdept:计算机系

Insert into student (sno,sname,ssex,sage,sdept) values('9512102','刘晨','男',20,'计算机系');

(2)在课程表Course中插入数据:
Cno:C06 Cname:数据结构 Ccredit:5 Semster:4

insert into course (cno, cname, ccredit, semster) 
values ('C06', '数据结构', '5', '4');
#或
前提是该属性列允许为NULL
insert into course values ('c06','数据结构',5,4,null);
insert into course values ('c06','数据结构',5,4,'');

(3)在选课表SC中插入95211班学生选修C04的选课信息。
提示:
多行数据插入,插入数据中的sno从student表中查询而来,插入的cno为“C04”

insert into sc(sno, cno) select sno, 'C04' from student
where sno like '95211%';

2、修改数据

(1)将所有学生的年龄增加1岁。

UPDATE student SET sage=sage+1;

(2)修改“9512101”的“C01”课程成绩为85。

UPDATE sc SET grade=85 WHERE cno='c01' AND sno='9512101';

(3)修改“王大力”的“计算机导论”课程成绩为70。

UPDATE sc SET grade=70 WHERE sno in (SELECT sno FROM student WHERE sname='王大力') AND cno in (SELECT cno from course WHERE cname='计算机导论');

或者:

UPDATE student,sc,course set grade=70 WHERE student.sno=sc.sno AND sc.cno=course.cno and sname='王大力' AND cname='计算机导论';

(4)将所有平均分为75分以上的学生的各门课成绩在原来基础上加5分。
select的结果再通过一个中间表select多一次,就可以避免这个错误

ERROR 1093 (HY000): You can’t specify target table ‘message’ for update in FROM clause

派生表必须使用别名

update sc 
set grade = grade + 5
where sno in 
	(
		select sno 
		from 
		(		
			select sno
			from sc
			group by sno 
			having avg(grade) >= 75 
		) as A
	) ;

(5)修改“高等数学”课程倒数三名成绩,在原来分数上减5分。

update sc set grade=grade-5 where cno in
(select cno from course where cname='高等数学')
and grade is not null order by grade limit 3;

更通用,去除成绩重复值和为空的情况:

update sc set grade=grade-5 where cno in 
(select sno from couse where cname='高等数学')
and grade in 
(select grade from(
select distinct grade from sc,course where sc.cno=course.cno and 
cname='高等数学' and grade is not null order by grade ase limit 3
) as newtable);

3、删除数据

(1)删除“9531102”学生“C05”课程的成绩记录

DELETE FROM sc WHERE sno='9531102' AND cno='c05';

(2)删除“张海”的所有成绩记录

DELETE FROM sc WHERE sno IN(SELECT sno FROM student where sname='张海');

(3)删除“数据库基础”的全部记录(包括课程信息,成绩信息)

DELETE from sc WHERE cno=(SELECT cno from course WHERE cname='数据库基础');
DELETE from course WHERE cname='数据库基础';

4、创建索引

导入rental表数据,在customer_id上建立普通索引(通过语句或表设计器均可)
查询customer_id=367的记录,记录查询时间:

select * 
from rental 
where customer_id = 367 ;
create index ix_ct_id on rental(customer_id);
drop index ix_ct_id on rental ;

有索引情况下的执行时间: 0.001ms
无索引情况下的执行时间: 0.005ms

第二部分:

1.插入图书信息:

将图书信息插入到book表中,其中书号 7, 书名 组合数学, 作者 刘迪, 价格 36.70, 数量 37。

表结构如下:

book(图书) 表:bno 书号,bname 书名,author 作者,price 单价,quantity 库存数

insert into book values('7','组合数学','刘迪','36.70','37');

2.删除“数据库基础”的全部记录(包括课程信息,成绩信息)

学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:

1、student(学生表):

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course(课程表)

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc(选课表)

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

delete from sc where cno =(select cno from course where sc.cno=course.cno and cname='数据库基础' ) ;
delete from course where cno='数据库基础';

3.删除图书信息

从BOOK表中删除当前无人借阅的图书记录。

表结构如下:

book(图书) 表:bno 书号,bname 书名,author 作者,price 单价,quantity 库存数

borrow(借书记录)表 :cno 借书卡号,bno 书号,rdate 还书日期

delete from book
where bno not in (
    select bno
  from borrow
  where borrow.bno = book.bno
);

4.修改计算机系李勇的VB课程成绩为60

修改计算机系李勇的VB课程成绩为60。

表结构如下

course表:

student表:

sc表:

update sc set grade=60
where sno in 
(select sno from student where sname='李勇' and sdept='计算机系') 
and
  cno in 
(select cno from course where cname='VB');

5.将所有平均分为75分以上的学生的各门课成绩在原来基础上加5分

将所有平均分为75分以上的学生的各门课成绩在原来基础上加5分。

学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:

1、student(学生表):

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course(课程表)

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc(选课表)

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

update sc
set grade = grade + 5
where sno in 
(
    select sno
    from 
  (
    select sno 
    from sc
       group by sno
      having avg(grade) >75    
  ) as A
);

注意:

派生子查询需要设置别名。

6.修改“高等数学”课程倒数三名成绩,在原来分数上减5分

修改“高等数学”课程倒数三名成绩,在原来分数上减5分。

学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:

1、student(学生表):

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course(课程表)

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc(选课表)

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

UPDATE sc
SET grade=grade-5
WHERE cno IN(
SELECT cno FROM course WHERE cname='高等数学')
ORDER BY grade ASC LIMIT 3;

7.修改borrow表增加一列;修改日期数据(两条语句完成)

修改borrow表增加借书日期bdate列,列类型为datetime;

将机械系的同学的借书日期值修改为还书日期的前两个月的时间。

用两条语句完成,日期的修改可以用date_add( )或adddate( )。

原表结构如下:

card(借书卡) 表:cno 卡号,name 姓名,class 班级

borrow(借书记录)表 :cno 借书卡号,bno 书号,rdate 还书日期

alter table borrow 
add column bdate datetime;
update borrow 
set bdate = date_add(rdate,interval - 2 month)
where cno in 
(select cno 
 from card 
 where deptName = '机械系');

注释:

定义和用法
DATE_ADD() 函数向日期添加指定的时间间隔。
DATE_SUB() 函数向日期减少指定的时间间隔。
语法
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)

date :参数是合法的日期表达式。

expr:参数是您希望添加的时间间隔。

type :参数可以是下列值,具体参数及用法请查下文章最后的博客链接

8.在选课表SC中插入95211班学生选修C04的选课信息。

在选课表SC中插入95211班学生选修C04的选课信息。

提示:多行数据插入,插入的数据的sno从student表中查询而来,插入的cno为“C04”

学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:

1、student(学生表):

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course(课程表)

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc(选课表)

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

INSERT INTO	sc(sno,cno) select sno,'c04' from student where sno like '95211%';

9.在课程表Course中插入数据:

在课程表Course中插入数据:

Cno:C06 Cname:数据结构 Ccredit:5 Semster:4

表结构如下:course(课程表)

列名 说明 数据类型 约束
CNO 课程号 CHAR(10) 主码
CNAME 课程名 VARCHAR(20) NOT NULL
CCREDIT 学分 SMALLINT
SEMSTER 学期 SMALLINT
PERIOD 学时 SMALLINT
insert into course (cno,cname,ccredit,semster) values('c06','数据结构','5','4');

10.在学生表Student中插入数据

在学生表Student中插入数据:

Sno:9512102 Sname:刘晨 Ssex:男 Sage:20 Sdept:计算机系

表结构如下:student(学生表)

列名 说明 数据类型 约束
SNO 学号 CHAR(7) 主码
SNAME 姓名 CHAR(10) NOT NULL
SSEX 性别 CHAR(2) 取“男”或“女”
SAGE 年龄 SMALLINT
SDEPT 所在系 VARCHAR(20) 默认“计算机系”
insert into student values('9512102','男','20','计算机系');

11.使用insert set 语句向sc表中插入数据。

使用insert set 向sc表中插入一条选课记录,姓名为周璐的学生,课程名为数据库原理与应用的课程的选课记录。

student表:

course表:

sc表:

用法:

INSERT INTO tablename SET column_name1 = value1, column_name2 = value2,…;
insert sc
set sno=(
  select sno 
  from student 
  where sname='周璐'),
cno=(
  select cno 
  from course 
  where cname='数据库原理与应用');

12.插入计算机系学生C01课程的选课记录。

在sc表中插入计算机系所有学生C01课程的选课记录。

student表结构:

sc表结构:

insert into sc(sno,cno) select sno,'c01' from student where sdept='计算机系';

13.一次向student表中插入多条数据

一次向student表中插入两条记录,其中王大力的系别用缺省值赋值。
两行数据的学号,姓名,性别,年龄和系别分别如下:
9520103,王敏,女,20,信息系;
9520104,王大力,男,19。

student表结构:

insert into student values('9520103','敏','女','20','信息系'),('9520104','王大力','男','19');

14.向course中插入数据

在course表中插入一新记录,课程号为c06,课程名为软件测试,学分为4。

course表结构:

insert into course('c06','软甲测试',null,'4');

15.删除“9531102”学生“C05”课程的成绩记录。

删除“9531102”学生“C05”课程的成绩记录。

学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:

1、student(学生表):

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course(课程表)

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc(选课表)

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

delete from sc where sno='9531102' and cno='c05';

结束:

DATE_ADD函数相关用法:https://blog.csdn.net/l1028386804/article/details/87790243

第一部分:MySQL查询练习 https://www.cnblogs.com/xbhog/p/13971373.html

第二部分:MySQL查询练习2 https://www.cnblogs.com/xbhog/p/14021013.html

理论部分:

第一部分:MySQL必知必会(1-12章) https://www.cnblogs.com/xbhog/p/13721359.html

.....持续更新.....有时间更新

感谢各位看到最后!

posted @ 2020-11-28 22:40  xbhog  阅读(1076)  评论(1编辑  收藏  举报