MySQL 学习笔记（二）：数据库更新、视图和数据控制
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, sname, ssex, sage, sdept) values (95006,'李三','男',21,'IS');出错，因为不能修改子表，只能修改主表。
（二）建立新用户和数据控制管理 使用命令行界面： 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视图是基于表的，当表中的内容更改时，视图中的内容也会更改，当更改视图时，同样表中的内容也会更改，二者互相关联。