MySQL数据更新

MySQL数据更新：

导读：

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

第一部分：

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

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

感谢各位看到最后！