MySQL資料更新

xbhog發表於2020-11-28

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

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

感謝各位看到最後！

Mysql高效插入/更新資料
2017-10-14
MySql
Ibatis批量更新資料（mysql資料庫）
2018-04-11
BATMySql資料庫
MySQL更新資料，如何使用update
2021-03-22
MySql
MySQL 如何實現資料更新
2023-02-08
MySql
[MYSQL -20]更新和刪除資料
2017-09-18
MySql
MySQL(四) 資料表的插入、更新、刪除資料
2016-12-10
MySql
MYSQL資料插入和更新的語法
2019-03-06
MySql
動態更新資料庫指令碼——Mysql
2017-11-10
資料庫指令碼MySql
如何實現MySQL隨機查詢資料與MySQL隨機更新資料？
2014-09-04
MySql隨機
MySQL 批量更新、刪除資料shell指令碼
2019-12-26
MySql指令碼
zendesk/maxwell：MySQL的CDC資料更新捕獲者
2021-12-22
MySql
MySQL講義第8講——資料更新之 DELETE
2020-09-24
MySqldelete
MySQL NDB 批量更新100萬行資料
2016-08-05
MySql
mysql根據查詢結果批量更新多條資料（插入或更新）
2018-08-16
MySql
mysql 資料插入和更新及刪除詳情FSSH
2022-03-01
MySql
MySQL 隨機查詢資料與隨機更新資料實現程式碼
2021-09-09
MySql隨機
[資料庫] Navicat for MySQL換種思維解決插入同時更新資料
2017-03-12
資料庫MySql
indexedDB 更新資料
2019-07-21
Index
【MySQL】面試官問我：MySQL如何實現無資料插入，有資料更新？我是這樣回答的！
2020-08-27
MySql面試
MongoDB之資料更新（更新函式）
2017-08-24
MongoDB函式
MySQL更新資料時，日誌（redo log、binlog）執行流程
2022-01-29
MySql
【MySql】mysql 資料庫資料訂正
2012-02-26
MySql資料庫
如何批量更新資料
2020-07-01
pymysql批量更新資料
2020-12-29
MySql
資料庫-批次更新
2020-10-15
資料庫
資料庫-批量更新
2020-10-15
資料庫
Mysql兩種情況下更新欄位中部分資料的方法
2021-09-09
MySql
Web開發經驗（1）防止MySQL資料庫誤更新/刪除
2018-06-05
WebMySql資料庫
自動更新資料庫資料的shell
2004-12-08
資料庫
oracle批量新增更新資料
2019-05-12
Oracle
flask如何更新資料庫
2021-09-11
Flask資料庫
Hive 資料更新時間
2020-09-23
Hive
Django RESTFramework——更新資料（5）
2018-06-01
DjangoRESTFramework
ListView動態更新資料
2011-07-22
View
用SQL更新資料(轉)
2007-08-13
SQL
MySQL 資料庫技巧：批次更新隨機生成的時間戳欄位
2024-03-05
MySql資料庫隨機時間戳
MySQL資料庫資料管理
2020-10-15
MySql資料庫
Mysql資料庫-資料模型
2024-05-26
MySql資料庫模型

MySQL資料更新

MySQL資料更新：

導讀：

第一部分：

1、插入資料

2、修改資料

3、刪除資料

4、建立索引

第二部分：

結束：

相關文章