MySQL數據更新

2020 年 11 月 28 日
筆記
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函數相關用法：//blog.csdn.net/l1028386804/article/details/87790243

第一部分：MySQL查詢練習 //www.cnblogs.com/xbhog/p/13971373.html

第二部分：MySQL查詢練習2 //www.cnblogs.com/xbhog/p/14021013.html

理論部分：

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

…..持續更新…..有時間更新

感謝各位看到最後！

Tags: mysql系列