MySQL 學習筆記（二）：資料庫更新、視圖和資料控制

基礎準備：

在 school 資料庫下建立student、course、sc 三個表：

create table student(
  Sno char(9) primary key,
  Sname char(20) unique,
  Ssex char(2),
  Sage int,
  Sdept char(20));

create table Course(
  Cno char(4) primary key,
  Cname char(40),
  Cpno char(4),
  Ccredit int);

create table SC(
  Sno char(9),
  Cno char(4),
  Grade int,
  primary key(Sno,Cno),
  foreign key (sno) references Student(sno),
  foreign key (Cno) references Course(Cno));

insert into  student values ('95001','李勇','男',20,'CS');
insert into student values ('95002', '劉晨','女',19,'IS');
insert into  student values('95003', '王敏', '女',18, 'MA');
insert into student values('95004', '張立', '男',19, 'IS');

insert into Course values(1,'資料庫', 5,4);
insert into Course values(2, '數學', 3 ,2);
insert into Course values(3, '資訊系統', 1, 4);
insert into Course values('4', '作業系統', '6', 3);
insert into Course values('5', ' 資料結構', '7', 4);
insert into Course values('6', '資料處理',  '',  2);
insert into Course values('7',  'PASCAL語言', '6', 4);

insert into SC values('95001', '1', 92);
insert into SC values('95001', '2', 85);
insert into SC values('95001', '3', 88);
insert into SC values('95002', '2', 90);
insert into SC values('95002', '3', 80);      

（一）資料庫更新

1. 在school資料庫中，確定表之間已經建立關系，用企業管理器建立資料庫的關系圖(Diagrams)

2. 在course 表中删除cno=1的記錄，有什麼結果？

　　報錯：因為有外鍵限制。

3. 編輯course表和 sc表之間的關系，添加級聯删除相關記錄和級更新相關記錄

create table SC(
          Sno char(9),
          Cno char(4),
          Grade int,
          primary key(Sno,Cno),
          foreign key (sno) references Student(sno) on delete cascade on update cascade,
          foreign key (Cno) references Course(Cno);      

重複2的操作，觀察course 表和 sc表的變化，有什麼結果，為什麼？

　　沒有報錯，成功删除 cno = 1行。

 4. 将course表和sc表的課程号為2的都改為22，如何做？（提示：在Diagrams中編輯course表和 sc表之間的關系，選擇級聯更新相關記錄cascade update related fields)

update sc set cno=22 where cno=2;
update course set cno=22 where cno=2;      

5. 在SC表中，

（1）将95002同學的成績都減少10%

update sc set course=80 where sno=95002;      

（2） 課程号為3的改為33

update sc set cno=33 where cno=3;      

改不了。不能修改子表，隻能修改主表。

6.

（1） 在STUDENT表中插入一條新紀錄：　　95006   李三   男   21   IS

insert into student (sno, sname, ssex, sage, sdept) values (95006,'李三','男',21,'IS');      

2）在SC表中插入一條新紀錄： 95008       3  　　80

insert into student (sno, cno, grade) values (95008, 3, 80);      

出錯，因為不能修改子表，隻能修改主表。

（二）建立新使用者和資料控制管理

使用指令行界面：

1.建立使用者：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';       

說明:username - 你将建立的使用者名, host - 指定該使用者在哪個主機上可以登陸,如果是本地使用者可用localhost, 如果想讓該使用者可以從任意遠端主機登陸,可以使用通配符%. password - 該使用者的登陸密碼,密碼可以為空,如果為空則該使用者可以不需要密碼登陸伺服器。

2.授權: 

GRANT privileges ON databasename.tablename TO 'username'@'host'       

說明: privileges - 使用者的操作權限,如SELECT , INSERT , UPDATE 等(詳細清單見該文最後面).如果要授予所的權限則使用ALL.;databasename - 資料庫名,tablename-表名,如果要授予該使用者對所有資料庫和表的相應操作權限則可用*表示, 如*.*. 

例子：

create user stu1@localhost identified by 'stu1';
grant create view on school.* to stu1@localhost;      

當建立過程沒問題，提示 Can't find any matching row in the user table

記得重新整理一下： flush privileges;

3.建立存儲過程和函數 建立存儲過程sp1

mysql> delimiter $$　        #将語句的結束符号從分号;臨時改為兩個$$(可以是自定義)
mysql> create procedure sp1()    
    -> begin 
    -> grant create view on school.* to stu2@localhost;
    -> grant select,update,insert,delete on school.student to stu2@localhost;
    -> grant select,update,insert,delete on school.course to stu2@localhost;
    -> grant select,update,insert,delete on school.sc to stu2@localhost;
    -> end$$
mysql> delimiter ;        #将語句的結束符号恢複為分号          

建立存儲過程sp2

mysql> delimiter $$    #将語句的結束符号從分号;臨時改為兩個$$(可以是自定義)
mysql> create procedure sp2()
-> begin
-> grant select on school.student to stu1@localhost;
-> grant select on school.course to stu1@localhost;
-> grant select on school.sc to stu1@localhost;
-> grant insert on school.student to stu1@localhost, stu2@localhost;
-> grant update(Ccredit) on course to stu1;
-> end$$      

建立存儲過程sp3

mysql> delimiter $$        #将語句的結束符号從分号;臨時改為兩個$$(可以是自定義)
mysql> create procedure sp3()
    -> begin
    -> revoke select on school.student from stu1@localhost;
    -> revoke select on school.course from stu1@localhost;
    -> revoke select on school.sc from stu1@localhost;
    -> end$$
mysql> delimiter ;        #将語句的結束符号恢複為分号          

grant 語句賦予權限，revoke 語句取消權限。

撤銷已經賦予給 MySQL 使用者權限的權限revoke

revoke all on *.* from dba@localhost;      

和grant類似，隻需将關鍵字to換成from，同樣可以針對特定使用者特定庫撤銷特定權限。

MySQL 删除存儲過程和函數：

基本的文法格式如下：

DROP {PROCEDURE|FUNCTION} sp_name;        

其中，sp_name參數表示存儲過程或函數的名稱。

删除存儲過程test。SQL代碼如下：

drop procedure if exists test;      

執行存儲過程sp1(), sp2(), sp3()

call sp1()；
call sp2()；
call sp3();      

(三) 視圖建立和操作

1.将查詢超過18歲的學生的結果建立視圖view1，所有男同學的結果建立view2

create view view1 as select * from student where sage>18；
 create view view2 as select * from student where ssex='男';      

A.在student中,将CS 改為CC,  view1，view2 有沒有同步更改？

update student set sdept='cc' where sdept='cs';      

同步更改了。

B.在view1中将女同學改為男同學，student 及view2有沒有同步修改？

update view1 set ssex='男' where ssex='女';      

2. 将查詢成績及格（>=60)的姓名、系名、課程名和成績的結果建立視圖V1

然後對視圖V1進行update, delete和insert操作,觀察student,course和 sc三張基表的變化.

create view v1 as
select student.sname,student.sdept,course.cname,sc.grade from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
and grade>=60;      

A.在student,course和 sc三表中進行update, delete和insert操作(自己設計測試用例),觀察V1視圖的變化.    同步變化

B.在V1視圖中進行update, delete和insert操作(自己設計測試用例),觀察student,course和 sc三張基表的變化.    同步變化

3.将查詢超過平均成績的學号、課程号及成績的結果建立一個視圖V2

create view v2 as select sno, cno,grade from sc where grade >(select avg(grade) from sc)       

A.在sc表中進行update, delete和insert操作(自己設計測試用例),觀察V2視圖的變化.     同步變化

B.在V2視圖中進行update, delete和insert操作(自己設計測試用例),觀察sc基表的變化，并且分析原因.     同步變化

**結論：view視圖是基于表的，當表中的内容更改時，視圖中的内容也會更改，當更改視圖時，同樣表中的内容也會更改，二者互相關聯。