关于数据库基础的实验学习_2、参照课程表,创建一个企业项目表(字段至少有项目名称、编号、项目经理id、客户

《数据库原理实验指导书》
目录

....................................................................................................

实验规则....................................................................................................................................

基本实验方法............................................................................................................................

实验环境....................................................................................................................................

实验一 数据定义......................................................................................................................

实验二 数据更新......................................................................................................................

实验三 数据查询-简单查询和连接查询.................................................................................

实验四 数据查询-分组查询和嵌套查询.................................................................................

实验五 视图操作......................................................................................................................

实验六 数据库的完整性..........................................................................................................

实验七 存储过程与用户函数..................................................................................................

实验八 访问控制、事务处理与并发控制..............................................................................

实验规则

为了顺利完成实验教学任务，确保人身设备安全，培养严谨、踏实、实事求是的科学作风和爱护国家财产的优良品质，特制定以下实验规则：

1、实验纪律

（1）在实验室不得做和实验无关的事情。

（2）遵守纪律，不迟到，不旷课。

（3）不能拆卸硬件，更不能拿走实验设备或仪器。

（4）保持实验室安静、整洁，爱护公物。

2、实验过程

（1）预习实验内容

（2）上机操作。每执行一句记录执行情况，主要是错误信息和错误原因。

（3）检查操作结果是否正确。

（4）根据实验过程的记录书写实验作业。

基本实验方法

本实验要求熟悉MySQL环境和数据库的相关知识。

实验环境

MySQL，SQLyog

实验一 数据定义

一、实验目的：熟练掌握用SQL语句实现数据库和基本表的创建。熟练掌握MySQL的安装、客户端登录方法；熟练掌握MySQL的编码、数据类型等基础知识；

二、预习要求：实验前复习讲授过的有关创建数据库、基本表的SQL语句，预习MySQL的安装、客户端登录方法；MySQL的编码、数据类型等基础知识。

三、实验重点和难点

实验重点：创建数据库、基本表。

实验难点：创建基本表时，为不同的列选择合适的数据类型，正确创建表级和列级完整性约束，如列值是否允许为空、主码和外码等。注意：数据完整性约束，可以在创建基本表时定义，也可以先创建表然后定义完整性约束；由于完整性约束的限制，被引用的表要先创建。

四、实验内容：

（一）用SQL语句建立如下mystudent数据库，包括student、course、sc三个基本表，要求实现关系的三类完整性。

学生表student由学号Sno、姓名Sname、性别 Ssex、年龄 Sage、所在系Sdept组成。

表1-1 student表

课程表course由课程号Cno、课程名Cname、先行课Cpno、学分Ccredit组成。

表1-2 course表

选课表sc由学号Sno、课程号Cno、成绩Grade组成。

表1-3 sc表

1）创建数据库与基本表

2）用可视化的方法录入基本表中的数据

（二）用SQL语句建立如下mySPJ数据库，包括S，P，J，和SPJ四个基本表(教材第二章习题5中的四个表)，要求实现关系的三类完整性。

S(SNO,SNAME,STATUS,CITY);

P(PNO,PNAME,COLOR,WEIGHT);

J(JNO,JNAME,CITY);

SPJ(SNO,PNO,JNO,QTY)

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。

表1-4 供应商表S

零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。

表1-5 零件表P

工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。

表1-6 工程项目表J

供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商 供应某种零件 给某工程项目的数量为QTY。

表1-7 供应情况表SPJ

1）创建数据库与基本表

2）用可视化的方法录入基本表中的数据

五、实验方法和步骤：

（一）使用MySQL5.5 Command Line Client创建数据库和基本表。

1）在开始菜单中找到，运行后出现下面界面：

图1-1登录MySQL界面

或者CMD打开命令行界面，输入登录Mysql的命令（注意：如果想在任何路径下能够登录Mysql，需要把MySQl的安装路径C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin设置到Path搜索路径）：

Mysql -h 主机地址 -u username -p password -P port

例如输入：mysql -u root -p

也出现图1界面，输入密码123或root（注意密码是安装MySQL服务时设置的密码，不同电脑不一样）。出现图1-2界面表示连接成功。

图1-2成功连接MySQL界面

2）创建数据库

create database mystudent;

如果已经存在该数据库，将报错，可以用下面命令：

create database if not exists  mystudent;

3）查看数据库

查看所有数据库

show databases;

查看自己创建的数据库

show  create  database  mystudent;

图1-3 创建数据库的命令

4）删除数据库

drop database mystudent;

drop database  if exists  mystudent;

查看mystudent是否已经删除：

show databases;

5）重新创建数据库，并设置字符集

create database mystudent charset utf8;

再次查看自己创建的数据库

show  create  database  mystudent;

图1-4数据库的字符集已经修改为utf8

也可以不删除数据库，用命令修改数据库字符集

修改数据库的字符集命令

alter database mystudent character set utf8;

6）选择你要使用的数据库

use mystudent;

7）查看存储引擎

show engines;

8）创建一张学生表

CREATE TABLE Student         

      (Sno   CHAR(9)  PRIMARY  KEY,  /* 列级完整性约束条件,Sno是主码*/                 

        Sname  CHAR(20)  UNIQUE,     /* Sname取唯一值*/

        Ssex    CHAR(2),

        Sage   SMALLINT,

        Sdept  CHAR(20)

      )charset utf8;

9)修改表的字符集

alter table student  character set gbk;

10）查看创建表格的语句

show create table student;

图1-5查看创建表格的命令

11）查看数据库中有哪些表？

show tables;

12）查看表的结构

describe  Student;

desc  student;

show columns from student;

图1-6 查看表的结构

13）查看表中的数据

select * from student;

14)向student表中输入一条数据

insert into student values('201215121','李勇','男',20,'CS');

再次查看表中的数据

select * from student;

用SQLyog查看，里面的数据可能是乱码。

图1-7 中文出现乱码

15) 设置字符集

由于CMD命令行只支持系统当前编码，所以一般需要将CMD与MYSQL服务器的交互编码设置为gbk才能正常显示utf8的数据。

set names gbk;

查看与字符集有关的变量的值

show variables like 'character%';

图1-8 查看字符集

可以看到set names gbk;命令将character_set_client、character_set_connection和character_set_results变成了gbk，可以看出set names就是设置这三个参数的值。一个select执行的过程应该是：client——>connection——>server/database——>results——>connection——>client，任何一个环节的字符集不兼容都会出现乱码。

16)删除student表中的数据

delete from student;

17)再次向student表中输入一条数据

insert into student values('201215121','李勇','男',20,'CS');

再次查看表中的数据

select * from student;

用SQLyog查看，里面的数据，数据正确显示。

图1-9 汉字正确输入

（二）使用SQLyog创建数据库和基本表。

1）用SQLyog客户端连接mySQL服务器

图1-10 SQLyog登录MySQL

2）打开一个查询编辑器，在里面输入命令。

图1-11 SQLyog工作界面

3）创建一张课程表

CREATE TABLE  Course

          (Cno       CHAR(4)  PRIMARY  KEY,

         Cname  CHAR(40),           

                Cpno     CHAR(4),                                        

            Ccredit  SMALLINT,

            FOREIGN KEY (Cpno) REFERENCES  Course(Cno)

          ) charset utf8;

图1-12 输入命令并执行

4）创建一张选课表

CREATE TABLE  SC

          (Sno  CHAR(9),

           Cno  CHAR(4), 

           Grade  SMALLINT,

           PRIMARY KEY (Sno,Cno), 

   /* 主码由两个属性构成，必须作为表级完整性进行定义*/

           FOREIGN KEY (Sno) REFERENCES Student(Sno),

/* 表级完整性约束条件，Sno是外码，被参照表是Student */

           FOREIGN KEY (Cno)REFERENCES Course(Cno)

/* 表级完整性约束条件， Cno是外码，被参照表是Course*/

        ) charset utf8;

图1-13 选择输入的命令并执行

5）刷新，查看新建的表

图1-14 查看建好的表格

6）分别打开表，用可视化的方法在3个表中输入数据。

图1-15 打开表输入数据

（三）修改基本表

1）向Student表增加“入学时间”列，其数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE;

2）将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTER TABLE Student  modify COLUMN Sage INT;

3）增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);

每次修改完后要查看表的结构变化。

（四）创建数据库mySPJ，并建立S，P，J，和SPJ四个基本表：

按照实验内容给出的基本表结构在查询编辑器窗口中书写SQL语句分别建立各表，并设置主键和外键约束；

五、思考题：

（一）你在本次实验中用到了三类完整性约束，分别是什么？当在表格中输入数据时，你遇到了违反3种完整性约束的情况了吗？分别是什么？

（二）在基本表中输入数据时，注意数据与字段的数据类型和长度以及能否为空的属性是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验作业中。

六、实验作业要求：

（一）实验目的： 

（二）实验内容：把创建数据库mySPJ，并建立S，P，J，和SPJ四个基本表的命令写在作业中。

（三）实验结果：可以是运行结果截图或其他形式的结果展示

（四）问题及解决：实验中遇到的问题及解决方法。

（五）回答思考题提出的问题。

实验二 数据更新

一、实验目的：熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作；掌握Mysql数据库的备份与还原操作。

二、预习要求：实验前复习讲授过的有关INSERT、UPDATE和DELETE语法结构的各个组成成分特点，能够根据需求正确设计单条数据插入、批量数据插入、修改数据和删除数据等SQL语句。

三、实验重点和难点

实验重点：

（1）插入、修改和删除数据的SQL语句；

（2）索引的定义、修改、删除方法。

实验难点：

（1）批量数据的导入。

四、实验内容：

（一）按照实验一中mystudent数据库中列出的表1-1、1-2、1-3的基本表结构分别建立各表，并分别使用插入、删除、修改的方式更新基本表中的数据。

1）创建数据库与基本表

2）用SQL命令录入基本表中的数据

3）用update命令修改基本表中的数据

4）用delete命令删除基本表中的数据

5）为Student，Course，SC表分别建立索引

6）分别修改、删除Student，Course，SC表上的索引

（二）使用Mysql数据库的备份与还原操作，实现对mystudent数据库中数据的备份，并能够成功还原数据库。

（三）按照实验一中mySPJ数据库中列出的表1-4、1-5、1-6、1-7的基本表结构分别建立各表，并分别使用插入、删除、修改的方式更新基本表中的数据。

1）创建数据库与基本表

2）用SQL命令录入基本表中的数据

3）用update命令修改基本表中的数据

4）用delete命令删除基本表中的数据

5）为S，P，J，和SPJ表分别建立索引

五、实验方法和步骤：

（一）按照实验一中mystudent数据库中列出的表1-1、1-2、1-3的基本表结构分别建立各表，并分别使用插入、删除、修改的方式更新基本表中的数据。

1、创建学生数据库

create database mystudent charset utf8;

2、分别输入创建表命令

1）创建Student表

CREATE TABLE Student         

      (Sno   CHAR(9)  PRIMARY  KEY,  /* 列级完整性约束条件,Sno是主码*/                 

        Sname  CHAR(20)  UNIQUE,     /* Sname取唯一值*/

        Ssex    CHAR(2),

        Sage   SMALLINT,

        Sdept  CHAR(20)

      )charset utf8;

2）创建Course表

CREATE TABLE  Course

          (Cno       CHAR(4)  PRIMARY KEY,

         Cname  CHAR(40),           

                Cpno     CHAR(4),                                        

            Ccredit  SMALLINT,

            FOREIGN KEY (Cpno) REFERENCES  Course(Cno)

          ) charset utf8;

3）创建SC表

CREATE TABLE  SC

          (Sno  CHAR(9),

           Cno  CHAR(4), 

           Grade  SMALLINT,

           PRIMARY KEY (Sno,Cno), 

   /* 主码由两个属性构成，必须作为表级完整性进行定义*/

           FOREIGN KEY (Sno) REFERENCES Student(Sno),

/* 表级完整性约束条件，Sno是外码，被参照表是Student */

           FOREIGN KEY (Cno) REFERENCES Course(Cno)

/* 表级完整性约束条件， Cno是外码，被参照表是Course*/

        ) charset utf8;

3、输入表中的数据

 1）在student表中输入1条数据

insert into student (sno,sname) values ('201215121','李勇');

1. values前面的(sno,sname)能省略吗？

2）查看student表中的数据

select * from student;

3）在student表中输入4条数据

insert  into student values('201215121','李勇','男',20,'CS'),('201215122','刘晨','女',19,'CS'),('201215125','张立','男',19,'IS'),('201215123','王敏','女',18,'MA');

1. 上面语句执行时会报下图错误，分析出错的原因并纠正错误。

图2-1 主码重复错误

1. values前省略了(Sno,Sname, Ssex, Sage, Sdept)字段名列表，这里为什么能够省略？

4）给每个学生的年龄增加1

UPDATE Student SET Sage=Sage+1

1. 查看运行上面个命令后student表中数据的变化。

5）在course表中一次输入7条数据

insert  into course(cno,cname,cpno,Ccredit) values('1','数据库',5,4),('2','数学',null,2),('3','信息系统',1,4),('4','操作系统',6,3),('5','数据结构',7,4),('6','数据处理',null,2),('7','PASCAL语言',6,4);

1. 该命令执行时报错的原因是什么？如何修改？

图2-2 违反参照完整性错误

1. 改为下面命令可以吗？

insert  into course(cno,cname,Ccredit) values('1','数据库',4),('2','数学',2),('3','信息系统',4),('4','操作系统',3),('5','数据结构',4),('6','数据处理',2),('7','PASCAL语言',4);

1. values前面的(cno,cname,Ccredit)能省略吗？
2. 分别用update命令输入Cpno列的数据

UPDATE course SET cpno=5 WHERE cno=1;

UPDATE course SET cpno=1 WHERE cno=3;

UPDATE course SET cpno=6 WHERE cno=4;

UPDATE course SET cpno=7 WHERE cno=5;

UPDATE course SET cpno=6 WHERE cno=7;

1. 现在用命令删除course表中的全部数据，能否成功？

DELETE FROM course

1. 如果不成功，如何实现把course表中的全部数据删除呢？

6）在sc表中输入5条数据

insert into sc values ('201215121','1',92), ('201215121','2',85), ('201215121','3',88), ('201215122','2',90),('201215122','3',80);

1. 同学们可以自己设计一些数据，并把数据用insert命令输入到sc表中。
2. 用update命令修改学号为'201215121'的学生的课程号为'1'的课程成绩为95分。

4、 删除Student表

DROP TABLE  Student;

1. 上面命令能成功吗？怎么才能删除student表？

5、索引的查看、定义、修改、删除

1）查看索引：

Show indexes from 表名;

例如查看student表上的索引

Show indexes from student;

图2-3 查看student表中的索引

也可以在对象浏览器窗口展开表下面的索引，看到该表上已经建立好的索引。

图2-4 对象浏览器窗口中student表的索引

注意：在表中定义主键约束或者唯一性键约束时，同时也创建了对应的索引。

2）建立索引

为学生-课程数据库中的Student，Course，SC三个表建立索引。Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引

create unique index  Stusno  on Student(Sno);

create unique index  Coucno ON Course(Cno);

create unique index  SCno ON SC(Sno ASC,Cno DESC);

1. 查看上述命令执行后各表中索引的变化。

3）删除索引

删除Student表的sname索引

drop index sname on student;

4）修改索引

将SC表的SCno索引名改为SCSno

Mysql5.7以上：alter  table   sc  rename  index  SCno TO SCSno

Mysql5.7以下：删掉索引，重新建立索引

drop index scno ON sc;

create unique index  SCSno  on  sc(Sno ASC,Cno DESC);

（二）使用Mysql数据库的备份与还原操作，实现对mystudent数据库中数据的备份，并能够成功还原数据库。

1、备份数据库命令

mysqldump -uroot -p --databases mystudent >c:\mystudent.sql

回车，按提示输入正确密码即可备份成功。

如果同学们的c盘受保护，操作失败，可以改变备份文件的存储位置，比如：d:\mystudent.sql。

图2-5 在命令窗口备份数据操作

备份成功后，删除数据库mystudent，然后在用命令回复。

DROP DATABASE mystudent;

2、sql文件还原方法:

1）先创建数据库mystudent，然后输入下面命令：

mysql -uroot -p -f mystudent < c:\mystudent.sql （-f 参数表示在导出过程中忽略出现的SQL错误）

图2-6 恢复数据库的操作方法1

重新登录mysql数据库，用Show databases;查看数据库恢复结果。

图2-7 查看数据库恢复结果

如果不创建数据库，直接输入上面命令会报错，如图2-8所示。

图2-8 未创建对应数据库恢复数据库操作失败

再次删除数据库，用下面方法恢复数据库。

2）mysql -uroot -p 先登录到mysql数据库，创建mystudent数据库，用use mystudent; 切换到需要导入的库中，然后执行

source c:\mystudent.sql;

如图2-9所示。

图2-9 恢复数据库的操作方法2

3）、利用SQLyog客户端图形化界面创建和还原mystudent数据库。

图2-10 sqlyog中数据库的备份方法

在弹出的对话框中进行设置，如图2-11所示。

图2-11 设置数据库的备份参数

现在删除mystudent数据库，然后恢复它。

图2-12 数据库的恢复

在弹出的对话框中输入备份的sql文件的位置及名称，如下图所示。单击执行即可。

图2-13 数据库的恢复过程

（三）按照实验一中mySPJ数据库中列出的表1-4、1-5、1-6、1-7的基本表结构分别建立各表，并分别使用插入、删除、修改的方式更新基本表中的数据。

1、创建mySPJ数据库

2、分别输入创建S，P，J，和SPJ表命令

3、利用Insert 语句表1-4、1-5、1-6、1-7给出的数据记录插入各表。

4.利用Update更新表中的记录：

①将p表中的所有红色零件的重量增加5。

②将spj表中所有天津供应商的QTY属性值减少10。用子查询。

5.利用Delete语句删除p表中的所有红色零件的记录。

五、思考题：

（一）当在表格中输入数据时，你遇到了违反3种完整性约束的情况了吗？分别是什么？

（二）Mysql创建索引的方法有哪些，建立索引有什么优缺点？

六、实验作业要求：

• 实验目的： 
• 实验内容：把实验步骤（三）中对S，P，J，和SPJ四个基本表输入、更新、删除的命令写在作业中。
• 实验结果：如果是查询语句，将查询结果记录列出来，其它可以不写。
• 问题及解决：首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。
• 回答思考题提出的问题，

实验三 数据查询-简单查询和连接查询

一、实验目的：熟练掌握用SQL语句实现的简单查询和多个数据表连接查询。

二、预习要求：实验前复习讲授过的有关简单查询与多表查询的知识，编写相应的SQL语句。

三、实验重点和难点

实验重点：select语句的基本用法、单表自身连接查询、多表连接查询。

实验难点：连接查询：确定连接属性，正确设计连接条件。

四、实验内容：

（一）在实验二的建立的mystudent数据库及数据基础上完成如下简单查询。

练习书上例3.16-3.38，并分析查询结果。

[例3.16]  查询全体学生的学号与姓名。

             SELECT Sno,Sname

             FROM Student; 

[例3.17]  查询全体学生的姓名、学号、所在系。

             SELECT Sname,Sno,Sdept

             FROM Student;

[例3.18]  查询全体学生的详细记录

SELECT  Sno,Sname,Ssex,Sage,Sdept

FROM Student;

或

SELECT  *

FROM Student;

[例3.19]  查全体学生的姓名及其出生年份。

SELECT Sname,2014-Sage          /*假设当时为2014年*/

FROM Student;

[例3.20] 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)

FROM Student;

使用列别名改变查询结果的列标题:

     SELECT Sname NAME,'Year of Birth:'  BIRTH,

       2014-Sage  BIRTHDAY,LOWER(Sdept)  DEPARTMENT

FROM Student;

[例3.21]  查询选修了课程的学生学号。

    SELECT Sno   FROM SC;

指定DISTINCT关键词，去掉表中重复的行

  

   SELECT DISTINCT Sno

    FROM SC;

[例3.22] 查询计算机科学系全体学生的名单。

    SELECT Sname

    FROM     Student

    WHERE  Sdept=‘CS’;

[例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。

     SELECT Sname,Sage

     FROM     Student   

     WHERE  Sage < 20;

[例3.24]查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sn

FROM  SC

WHERE Grade<60;

[例3.25] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

     SELECT Sname, Sdept, Sage

FROM     Student

WHERE   Sage BETWEEN 20 AND 23;

[例3.26]  查询年龄不在20~23岁之间的学生姓名、系别和年龄

       SELECT Sname, Sdept, Sage

       FROM    Student

       WHERE Sage NOT BETWEEN 20 AND 23;

[例3.27]查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

SELECT Sname, Ssex

FROM  Student

WHERE Sdept IN ('CS','MA’,'IS' );

可改写为：

SELECT Sname, Ssex

FROM     Student

WHERE  Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';

 [例3.28]查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。

SELECT Sname, Ssex

FROM Student

      WHERE Sdept NOT IN ('IS','MA’,'CS' );

[例3.29]  查询学号为201215121的学生的详细情况。

      SELECT *   

     FROM  Student 

     WHERE  Sno LIKE ‘201215121';

等价于：

      SELECT  *

      FROM  Student

      WHERE Sno = ' 201215121 ';

[例3.30]  查询所有姓刘学生的姓名、学号和性别。

      SELECT Sname, Sno, Ssex

      FROM Student

      WHERE  Sname LIKE '刘%';

[例3.31]  查询姓"欧阳"且全名为三个汉字的学生的姓名。

       SELECT Sname

      FROM   Student

      WHERE  Sname LIKE '欧阳__';

[例3.32]  查询名字中第2个字为"阳"字的学生的姓名和学号。

      SELECT Sname，Sno

      FROM     Student

      WHERE  Sname LIKE '__阳%';

[例3.33]  查询所有不姓刘的学生姓名、学号和性别。

      SELECT Sname, Sno, Ssex

      FROM     Student

      WHERE  Sname NOT LIKE '刘%';

[例3.34]  查询DB_Design课程的课程号和学分。

      SELECT Cno，Ccredit

      FROM     Course

      WHERE  Cname LIKE 'DB$_Design'  ESCAPE '$ ' ;

[例3.35]  查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

      SELECT  *

      FROM    Course

      WHERE  Cname LIKE  'DB$_%i_ _' ESCAPE '$ ' ;

 [例3.36]  某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

  SELECT Sno，Cno

      FROM    SC

      WHERE  Grade IS NULL

[例3.37]  查所有有成绩的学生学号和课程号。

      SELECT Sno，Cno

      FROM     SC

      WHERE  Grade IS NOT NULL;

[例3.38]  查询计算机系年龄在20岁以下的学生姓名。

      SELECT Sname

       FROM  Student

       WHERE Sdept= 'CS' AND Sage<20;

（二）在实验二的建立的mystudent数据库及数据基础上完成如下连接查询。

练习书上例3.49-3.54，并分析查询结果。

[例 3.49]  查询每个学生及其选修课程的情况

               SELECT  Student.*, SC.*

               FROM     Student, SC

               WHERE  Student.Sno = SC.Sno;

[例 3.50]  对[例 3.49]用自然连接完成。

 SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

 FROM     Student,SC

 WHERE  Student.Sno = SC.Sno;

[例 3.51 ]查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。

    SELECT Student.Sno, Sname

    FROM     Student, SC

    WHERE  Student.Sno=SC.Sno  AND                            

                   SC.Cno=' 2 ' AND SC.Grade>90;

[例 3.52]查询每一门课的间接先修课（即先修课的先修课）

    SELECT  FIRST.Cno, SECOND.Cpno

     FROM  Course  FIRST, Course  SECOND

     WHERE FIRST.Cpno = SECOND.Cno;

[例 3. 53] 外连接改写[例 3.49]

    SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

    FROM  Student  LEFT OUTER JOIN SC ON   

                 (Student.Sno=SC.Sno);

多表连接：两个以上的表进行连接

[例3.54]查询每个学生的学号、姓名、选修的课程名及成绩

  SELECT Student.Sno, Sname, Cname, Grade

   FROM    Student, SC, Course    /*多表连接*/

   WHERE Student.Sno = SC.Sno

                  AND SC.Cno = Course.Cno;

（三）在实验二建立的mySPJ数据库及数据基础上完成如下简单查询。

（1）查询所有“天津”的供应商明细；

（2）查询所有“红色”的14公斤以上的零件。

（3）查询工程名称中含有“厂”字的工程明细。

（四）在实验二建立的mySPJ数据库及数据基础上完成如下连接查询。

（1）等值连接：求s表和j表的相同城市的等值连接。

（2）自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应工程零件排序。

（3）笛卡尔积：求s和p表的笛卡尔积

（4）左连接：求j表和spj表的左连接。

（5）右连接：求spj表和j表的右连接。

五、实验方法和步骤：

用SQLyog客户端完成实验内容。在完成实验二的基础上，分别在mystudent和mySPJ数据库中将实验内容中所要求的查询项目依次用SQL语句实现，并记录执行结果。

1.检查实验一中的mystudent和mySPJ数据库及各表已创建，并完成表中原始数据的添加。

2.在查询编辑器中输入查询语句并执行和查看结果。

图3-1 查询操作界面

3.分析查询结果是否正确，如果不正确，分析原因并修改SQL语句重新执行查询。

六、思考题：

查看实验内容（四）中左连接和右连接的执行结果是否一致，为什么？

七、实验作业要求：

• 实验目的：
• 实验内容：把实验内容中与mySPJ数据库相关的（三）和（四）的内容写在实验报告中。
• 实验结果：可以是运行结果截图或其他形式的结果展示。
• 问题及解决：实验中遇到的问题及解决方法。
• 回答思考题提出的问题。

实验四 数据查询-分组查询和嵌套查询

一、实验目的：熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。

二、预习要求：实验前复习讲授过的有关分组查询和嵌套查询的知识，编写相应的SQL语句。

三、实验重点和难点

实验重点：分组查询、嵌套查询

实验难点：区分元组过滤条件和分组过滤条件；相关子查询、多层EXIST嵌套查询。

四、实验内容：

（一）在实验二的建立的mystudent数据库及数据基础上完成如下分组查询（书上例3.46-3.48）。练习书上例3.39-3.48，并分析查询结果。

[例3.39]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

        SELECT Sno, Grade

        FROM    SC

        WHERE  Cno= ' 3 '

        ORDER BY Grade DESC;

[例3.40]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

        SELECT  *

        FROM  Student

        ORDER BY Sdept, Sage DESC; 

[例3.41]  查询学生总人数。

    SELECT COUNT(*)

    FROM  Student; 

[例3.42]  查询选修了课程的学生人数。

     SELECT COUNT(DISTINCT Sno)

     FROM SC;

[例3.43]  计算1号课程的学生平均成绩。

          SELECT AVG(Grade)

          FROM    SC

          WHERE Cno= ' 1 ';

[例3.44]  查询选修1号课程的学生最高分数。

    SELECT MAX(Grade)

    FROM SC

    WHERE Cno='1';

[例3.45 ] 查询学生201215012选修课程的总学分数。

    SELECT SUM(Ccredit)

    FROM  SC,Course

    WHERE Sno='201215012' AND SC.Cno=Course.Cno;

[例3.46]  求各个课程号及相应的选课人数。

     SELECT Cno，COUNT(Sno)

     FROM    SC

     GROUP BY Cno;

[例3.47]  查询选修了3门以上课程的学生学号。

     SELECT Sno

     FROM  SC

     GROUP BY Sno

     HAVING  COUNT(*) >3;     

[例3.48 ]查询平均成绩大于等于90分的学生学号和平均成绩

     SELECT  Sno, AVG(Grade)

     FROM  SC

     GROUP BY Sno

     HAVING AVG(Grade)>=90;

（二）在实验二的建立的mystudent数据库及数据基础上完成如下嵌套查询。

练习书上例3.55-3.63，并分析查询结果。

[例 3.55]  查询与“刘晨”在同一个系学习的学生。

    SELECT Sno, Sname, Sdept

         FROM Student

          WHERE Sdept  IN

                  (SELECT Sdept

                   FROM Student

                   WHERE Sname= ' 刘晨 ');

可以用带EXISTS谓词的子查询替换：

SELECT Sno,Sname,Sdept

     FROM Student S1

      WHERE EXISTS

             　   (SELECT *

                     FROM Student S2

                     WHERE S2.Sdept = S1.Sdept AND

                     S2.Sname = '刘晨');

[例 3.56]查询选修了课程名为“信息系统”的学生学号和姓名

     SELECT Sno,Sname        ③ 最后在Student关系中

    FROM    Student            取出Sno和Sname

     WHERE Sno  IN

          (SELECT Sno       ② 然后在SC关系中找出选

           FROM    SC        修了3号课程的学生学号

           WHERE  Cno IN

             (SELECT Cno   ① 首先在Course关系中找出

              FROM Course “信息系统”的课程号，为3号

              WHERE Cname= '信息系统'                     

               )

           );

[例 3.57 ]找出每个学生超过他选修课程平均成绩的课程号。

方法1  

SELECT Sno, Cno

    FROM    SC  x

    WHERE Grade >=(SELECT AVG（Grade）

                              FROM  SC y

                                   WHERE y.Sno=x.Sno);

方法2

SELECT Sno, Cno

    FROM SC, (SELECT   Sno, Avg(Grade)

               FROM    SC

                  GROUP BY Sno)

       AS   Avg_sc(avg_sno,avg_grade)

    WHERE SC.Sno = Avg_sc.avg_sno

      and SC.Grade >=Avg_sc.avg_grade

[例 3.58]  查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

    SELECT Sname,Sage

    FROM    Student

    WHERE Sage < ANY (SELECT  Sage

       FROM    Student

       WHERE Sdept= ' CS ')

     AND Sdept <> ‘CS '; /*父查询块中的条件 */

[例 3.59]  查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

方法一：用ALL谓词

    SELECT Sname,Sage

    FROM Student

    WHERE Sage < ALL

                (SELECT Sage

                   FROM Student

                  WHERE Sdept= ' CS ')

      AND Sdept <> ' CS ’;

       方法二：用聚集函数

        SELECT Sname,Sage

        FROM Student

        WHERE Sage <

                     (SELECT MIN(Sage)

                        FROM Student

                        WHERE Sdept= ' CS ')

          AND Sdept <>' CS ';

[例 3.60]查询所有选修了1号课程的学生姓名。

 思路分析：

本查询涉及Student和SC关系

在Student中依次取每个元组的Sno值，用此值去检查SC表

若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取此Student.Sname送入结果表

    方法1：

     SELECT Sname

     FROM Student

     WHERE EXISTS

            (SELECT *

               FROM SC

              WHERE Sno=Student.Sno AND Cno= ' 1 ');

方法2：

    SELECT Sname

    FROM     Student, 

                   (SELECT Sno FROM SC WHERE Cno=' 1 ') AS SC1

    WHERE  Student.Sno=SC1.Sno;

[例 3.61]  查询没有选修1号课程的学生姓名。

     SELECT Sname

     FROM     Student

     WHERE NOT EXISTS

            (SELECT *

               FROM SC

              WHERE Sno = Student.Sno AND Cno='1');

[例 3.62] 查询选修了全部课程的学生姓名。

        SELECT Sname

        FROM Student

        WHERE NOT EXISTS         /*where */

                      (SELECT *                /*子查询1*/

                        FROM Course

                        WHERE NOT EXISTS             /*where 1*/

                                      (SELECT *                   /*子查询2*/

                                       FROM SC

                                       WHERE Sno= Student.Sno

                                             AND Cno= Course.Cno

                                      )

                       );

[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。

用NOT EXISTS谓词表示：    

       SELECT DISTINCT Sno

       FROM SC SCX

       WHERE NOT EXISTS

                     (SELECT *

                      FROM SC SCY

                      WHERE SCY.Sno = ' 201215122 '  AND

                                    NOT EXISTS

                                    (SELECT *

                                     FROM SC SCZ

                                     WHERE SCZ.Sno=SCX.Sno AND

                                                   SCZ.Cno=SCY.Cno));

书上例3.64-3.68为集合查询，请自行练习。

（三）在实验二的建立的mySPJ数据库及数据基础上完成如下分组查询。

1. 求各种颜色零件的平均重量。
2. 求北京供应商和天津供应商的总个数。
3. 求各供应商供应的零件总数。
4. 求各供应商供应给各工程的零件总数。
5. 求使用了100个以上P1零件的工程名称。
6. 求各工程使用的各城市供应的零件总数。

（四）在实验二的建立的mySPJ数据库及数据基础上完成如下嵌套查询。

1. 1. in连接谓词查询：

① 查询没有使用天津供应商供应的红色零件的工程名称。

② 查询供应了1000个以上零件的供应商名称。(having)

• 1. 比较运算符：求重量大于所有零件平均重量的零件名称。
  2. Exists连接谓词：
•  查询供应J1的所有的零件都是红色的供应商名称。
• 至少用了供应商S1所供应的全部零件的工程号JNO。

五、实验方法和步骤：

用SQLyog客户端完成实验内容。在完成实验二的基础上，分别在mystudent和mySPJ数据库中将实验内容中所要求的查询项目依次用SQL语句实现，并记录执行结果。

1.检查实验一中的mystudent和mySPJ数据库及各表已创建，并完成表中原始数据的添加。

2.在查询编辑器中输入查询语句并执行和查看结果。

图4-1 查询操作界面

3.分析查询结果是否正确，如果不正确，分析原因并修改SQL语句重新执行查询。

六、思考题：

（一）嵌套查询中的in连接谓词查询，关键字in可以直接用any代替么？什么情况下in和any可以互相代替？

（二）嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？还是物理数据集合？

七、实验作业要求：

（一）实验目的：

（二）实验内容：把实验内容中与mySPJ数据库相关的（三）和（四）的内容写在实验报告中。

（三）实验结果：可以是运行结果截图或其他形式的结果展示。

（四）问题及解决：实验中遇到的问题及解决方法。

（五）回答思考题提出的问题。

实验五 视图操作 

一、实验目的：熟悉SQL语言有关视图的操作，能够熟练使用SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。

二、预习要求：实验前复习讲授过的有关视图操作的知识，编写相应的SQL语句。针对给定的数据库模式，以及相应的应用需求，创建视图、创建带WITH CHECK OPTION的视图，并验证视图WITH CHECK OPTION选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

三、实验重点和难点：

实验重点：创建视图。

实验难点：可更新的视图和不可更新的视图之区别，WITH CHECK OPTION的验证。

四、实验内容：

（一）在实验二的建立的mystudent数据库及数据基础上完成如下视图的创建和查询、删除。练习书上例3.84-3.96，并分析结果。

[例3.84]  建立信息系学生的视图。

        CREATE VIEW IS_Student

        AS

        SELECT Sno,Sname,Sage

        FROM     Student

        WHERE  Sdept= 'IS';

 [例3.85]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。

         CREATE VIEW IS_Student1

        AS

        SELECT Sno,Sname,Sage

        FROM  Student

        WHERE  Sdept= 'IS'

        WITH CHECK OPTION;

 [例3.86]  建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。

        CREATE VIEW IS_S1(Sno,Sname,Grade)

        AS

        SELECT Student.Sno,Sname,Grade

        FROM  Student,SC

        WHERE  Sdept= 'IS' AND

                       Student.Sno=SC.Sno AND

                       SC.Cno= '1';

      [例3.87]  建立信息系选修了1号课程且成绩在90分以上的学生的视图。

        CREATE VIEW IS_S2

        AS

        SELECT Sno,Sname,Grade

        FROM  IS_S1

        WHERE  Grade>=90;

   带表达式的视图

[例3.88]  定义一个反映学生出生年份的视图。

        CREATE  VIEW BT_S(Sno,Sname,Sbirth)

        AS

        SELECT Sno,Sname,2014-Sage

        FROM  Student;

   分组视图

[例3.89]  将学生的学号及平均成绩定义为一个视图

             CREAT  VIEW S_G(Sno,Gavg)

             AS 

             SELECT Sno,AVG(Grade)

             FROM  SC

             GROUP BY Sno;

[例3.90]将Student表中所有女生记录定义为一个视图

      CREATE VIEW F_Student(F_Sno,name,sex,age,dept)

      AS

      SELECT  *                        /*没有不指定属性列*/

      FROM  Student

      WHERE Ssex=‘女’;

     缺点：

    修改基表Student的结构后，Student表与F_Student视图  的映象关系被破坏，导致该视图不能正确工作。

[例3.91 ] 删除视图BT_S和IS_S1

             DROP VIEW BT_S;  /*成功执行*/

             DROP VIEW IS_S1;  /*拒绝执行*/

           

    要删除IS_S1，需使用级联删除：

DROP VIEW IS_S1 CASCADE;            

[例3.92]  在信息系学生的视图中找出年龄小于20岁的学生。

       SELECT   Sno,Sage

       FROM      IS_Student

       WHERE   Sage<20;

[例3.93]  查询选修了1号课程的信息系学生

SELECT  IS_Student.Sno,Sname

FROM     IS_Student,SC

WHERE  IS_Student.Sno =SC.Sno AND SC.Cno= '1';

[例3.94]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT *

FROM   S_G

WHERE  Gavg>=90;

[例3.95]  将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。

UPDATE  IS_Student

SET  Sname= '刘辰'

WHERE  Sno= ' 201215122 ';

[例3.96]  分别向信息系学生视图IS_Student和IS_Student1中插入一个新的学生记录，查看区别：

 INSERT

INTO IS_Student

VALUES(‘201215129’,’赵新’,20);

INSERT

INTO IS_Student1

VALUES(‘201215130’,’赵力’,18);

MySql中视图建好后可以用

Show create view 视图名；

查看视图创建命令。

视图的修改:

方式1:

create or replace view 视图名 as 查询语句;

方法2:

alter view 视图名 as 查询语句;

（二）在实验二的建立的mySPJ数据库及数据基础上完成如下视图的创建和查询并分析结果。

      （1）查询北京的供应商的编号、名称和城市。

   （2）查询S1供应商的所有供应明细。

   （3）查询各工程名称使用的各种颜色零件的个数。

   查询上面定义的视图。

（三）医院数据库（hospital）包括医生表 doctor和科室表 department。医生表包含医生工号（doc_id），医生姓名（doc_name），医生性别（doc_sex），医生年龄（doc_age），医生所在科室（doc_dep）；科室表包含科室编号（dep_id），科室名称（dep_name），科室地址（dep_addr）。在该数据库上完成如下视图操作。

（1）创建数据表 doctor。

表5-1 doctor表结构

这次doc_id设置为自增字段，它的语法规则是:属性名 属性类型 auto_increment

(2) 向 doctor 表中插入数据。

表5-2 doctor表的数据

对自增字段数据插入时，对应位置填上null，那么该字段自动编号。

例如：

Insert into doctor values（null，‘aa’，‘male’，35，‘aaa’）；

(3) 创建数据表 department。

表5-3 department表结构

(4) 向 department 表中插入数据。

表5-4 department表数据

(5) 在 doctor 表上创建一个名为 doc_view 的视图，视图只包括 doc_id 和 doc_name 两个字段，并给这两个字段取名为 doid 和 doname；创建完后，查看该视图。

(6) 创建视图 dd_view，视图包括医生工号、医生姓名、医生所在科室和科室的地址；创建完后，查看该视图。

 (7)在 doctor 表上创建一个名为 doc_view2 的视图，要求只显示女性医生的信息；

向该视图中插入数据 doc_id=5,doc_name=’ee’，观察能否插入成功并展示执行后视图 doc_view2 和表 doctor 内的数据；若在创建视图时加上 with check option 的约束呢？还能成功插入吗？请说明原因（说明：新建视图 doc_view3，插入数据改为 doc_id=6,doc_name=’ff’，其余不变）。

(8) 删除所有视图。

五、实验方法和步骤：

用SQLyog客户端完成实验内容。在完成实验二的基础上，分别在mySPJ数据库和hospital数据库中将实验内容中所要求的项目依次用SQL语句实现，并记录执行结果。

1.检查实验一中的mySPJ数据库和该实验中的hospital数据库及各表已创建，并完成表中原始数据的添加。

2.在查询编辑器中输入视图创建查询语句并执行。

图5-1 创建视图

3.用select 语句查询视图。

图5-2 查看视图

4.分析查询结果是否正确，如果不正确，分析原因并修改SQL语句重新执行查询。

六、思考题：

实验内容中的哪些视图可以用来更新记录？

七、实验作业要求：

（一）实验目的：

（二）实验内容：把实验内容中与的（二）和与（三）的内容写在实验报告中。

（三）实验结果：可以是运行结果截图或其他形式的结果展示。

（四）问题及解决：实验中遇到的问题及解决方法。

（五）回答思考题提出的问题。

实验六 数据库的完整性

一、实验目的：掌握实体完整性的定义和维护方法、掌握参照完整性的定义和维护方法；掌握用户自定义完整性的定义和维护方法；掌握数据库触发器的设计和使用方法。

二、预习要求：实验前复习书本第5章相关知识。

三、实验重点和难点

实验重点：实体完整性定义；参照完整性定义，触发器的定义。

实验难点：有多个候选码时实体完整性的定义；参照完整性的违约处理定义。利用触发器实现较为复杂的用户自定义完整性。

• 实验内容：

1、定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的SQL语句：创建表时定义实体完整性、创建表后定义实体完整性。设计SQL语句验证完整性约束是否起作用。

2、定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的SQL语句：创建表时定义参照完整性、创建表后定义参照完整性。

3、针对具体应用语义，选择NULL/NOT NULL、DEFAULT，UNIQUE等，定义属性上的约束条件。

4、定义BEFORE触发器和AFTER触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

• 实验步骤。

（一）创建一个新的数据库mystudent2

 Create database  mystudent2 charset utf8;

（二）在mystudent2数据库中分别创建如下表格

（1）sdept专业表

CREATE TABLE sdept(

       Deptno numeric(2, 0) NOT NULL,

       Dname char(9) NOT NULL,

       Location char(10) NULL,

primary key (Deptno) /* 表级完整性约束条件, Deptno是主码*/

) charset utf8;

在sdept表中插入下面数据：

INSERT INTO sdept VALUES(11,'计算机网络','9-301'),(12,'计算机软件','9-302'),(13,'计算机应用','9-303')

（2）student专业表

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/

Sname CHAR(20) UNIQUE,

Ssex CHAR(2),

Sage SMALLINT,

Sdept numeric(2,0),

FOREIGN KEY(sdept) REFERENCES sdept(Deptno)

/*在表级定义参照完整性*/

)charset utf8;

执行下面语句，看运行是否出错，出错的原因是什么？

insert into student (sno,sname) values ('201215121','李勇');

insert  into student values('201215121','李勇','男',20,12),('201215122','刘晨','女',19,11),

('201215125','张立','男',19,11),

('201215123','王敏','女',18,14);

（3）Course课程表

CREATE TABLE  Course

    (    Cno       CHAR(4)  PRIMARY KEY,

              Cname  CHAR(40),           

             Cpno     CHAR(4),                                        

            Ccredit  SMALLINT,

            FOREIGN KEY (Cpno) REFERENCES  Course(Cno)

          )charset utf8;

执行下面语句，看运行是否出错，出错的原因是什么？

INSERT  INTO course(cno,cname,Cpno,Ccredit) VALUES('1','数据库',5,4),('2','数学',NULL,2),('3','信息系统',NULL,4),

('4','操作系统',NULL,3),

('5','数据结构',NULL,4),

('6','数据处理',NULL,2),('7','PASCAL语言',NULL,4);

 如何改正？对照表下表，把数据输入完整

（4）SC选课表

CREATE TABLE  SC

          (Sno  CHAR(9),

           Cno  CHAR(4), 

           Grade  SMALLINT,

           PRIMARY KEY (Sno,Cno), 

   /* 主码由两个属性构成，必须作为表级完整性进行定义*/

           FOREIGN KEY (Sno) REFERENCES Student(Sno),

/* 表级完整性约束条件，Sno是外码，被参照表是Student */

           FOREIGN KEY (Cno) REFERENCES Course(Cno)

/* 表级完整性约束条件，Cno是外码，被参照表是Course*/

        )charset utf8;

insert into sc values ('201215121','1',92),('201215121','2',85),('201215121','3',88),('201215122','2',90),

('201215122','3',80);

执行下面语句：

UPDATE Student SET sno='201215141' WHERE sno='201215121'

看能否执行？

自己设计一些更改或者删除student、sc表中数据的操作，看是否能操作？

（三）删除sc表，创建SC1表，显式说明参照完整性的违约处理示例

Drop table sc;

CREATE TABLE SC1

        (  Sno   CHAR(9) NOT NULL,

           Cno   CHAR(4) NOT NULL,

           Grade  SMALLINT,

           PRIMARY KEY(Sno,Cno),                

           FOREIGN KEY (Sno) REFERENCES Student(Sno)

                    ON DELETE CASCADE        /*级联删除SC表中相应的元组*/

             ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/

           FOREIGN KEY (Cno) REFERENCES Course(Cno)                        

             ON DELETE NO ACTION    

                 /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/

             ON UPDATE CASCADE  

          /*当更新course表中的cno时，级联更新SC表中相应的元组*/

           );

插入数据：

insert into sc1 values ('201215121','1',92),('201215121','2',85),('201215121','3',88),('201215122','2',90),

('201215122','3',80);

执行下面语句：

UPDATE Student SET sno='201215141' WHERE sno='201215121'

观察cs1表中数据的变化

自己设计一些更改或者删除student、sc表中数据的操作，看是否能操作？

（四）建立部门表DEPT1，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

  CREATE TABLE DEPT1

        (   Deptno  NUMERIC(2),

            Dname  CHAR(9)  UNIQUE NOT NULL,

                 /*要求Dname列值唯一, 并且不能取空值*/

            Location  CHAR(10),

            PRIMARY KEY (Deptno)

         );

然后执行下面语句，看运行是否出错，出错的原因是什么？

insert into DEPT1 values(1,'计算机系','10-210' ),(2,'计算机系','10-410')

（五）创建user和user_history表，以及三个触发器tri_insert_user、tri_update_user、tri_delete_user，分别对应user表的增、删、改三件事；

（1）创建user表；

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,

  `account` VARCHAR(255) DEFAULT NULL,

  `name` VARCHAR(255) DEFAULT NULL,

  `address` VARCHAR(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

（2）创建对user表操作历史表；

DROP TABLE IF EXISTS `user_history`;

CREATE TABLE `user_history` (

  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,

  `user_id` BIGINT(20) NOT NULL,

  `operatetype` VARCHAR(200) NOT NULL,

  `operatetime` DATETIME NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

（3）创建user表插入事件对应的触发器tri_insert_user；

DROP TRIGGER IF EXISTS `tri_insert_user`;

DELIMITER ;;

CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW BEGIN

    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  NOW());

END

;;

DELIMITER ;

几点说明：

DELIMITER：改变输入的结束符，默认情况下输入结束符是分号;，这里把它改成了两个分号;;，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行；

new：当触发插入和更新事件时可用，指向的是被操作的记录

old： 当触发删除和更新事件时可用，指向的是被操作的记录

（4）创建user表更新事件对应的触发器tri_update_user；

DROP TRIGGER IF EXISTS `tri_update_user`;

DELIMITER ;;

CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW BEGIN

    INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', NOW());

END

;;

DELIMITER ;

（5）创建user表删除事件对应的触发器tri_delete_user；

DROP TRIGGER IF EXISTS `tri_delete_user`;

DELIMITER ;;

CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW BEGIN

    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', NOW());

END

;;

DELIMITER ;

（6）至此，全部表及触发器创建完成，开始验证结果，分别做插入、修改、删除事件，执行以下语句，观察user_history是否自动产生操作记录；

INSERT INTO USER(account, NAME, address) VALUES ('user1', 'user1', 'user1');

INSERT INTO USER(account, NAME, address) VALUES ('user2', 'user2', 'user2');

UPDATE USER SET NAME = 'user3', account = 'user3', address='user3' WHERE NAME='user1';

DELETE FROM `user` WHERE NAME = 'user2';

（六）创建触发器，当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：

     SC_U（Sno,Cno,Oldgrade,Newgrade）

其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

（1）创建SC_U表

   create table  SC_U

   (Sno  CHAR(9),

    Cno  CHAR(4),

   Oldgrade SMALLINT,

   Newgrade SMALLINT)

（2）创建触发器

DROP TRIGGER IF EXISTS `SC_T`;

DELIMITER ;;

CREATE TRIGGER  SC_T        

AFTER UPDATE ON SC

FOR EACH ROW   

BEGIN IF new.Grade >= 1.1*old.Grade

        THEN   INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) 

VALUES(old.Sno,old.Cno,old.Grade,new.Grade);

END IF;

 END;

;;

DELIMITER ;

（3）修改sc表中的某个记录的成绩，查看SC_U表中数据的变化。

UPDATE  sc  SET Grade  = 30  WHERE  Sno = '201215141' AND  Cno  = '4';

我们发现在SC_U 自动添加了一条数据。如下图：

（七）定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

（1）创建TEACHER1表

CREATE TABLE TEACHER1

              (   Eno    SMALLINT  PRIMARY KEY ,   /*在列级定义主码*/

                  Ename  CHAR(10),

                  Job     CHAR(8),

                   Sal     DECIMAL(7,2),

                   Deduct  DECIMAL(7,2),

      );

（2）创建触发器：

DROP TRIGGER IF EXISTS  Insert_TEACHER1_Sal;

DELIMITER ;;

CREATE TRIGGER Insert_Or_Update_Sal

         BEFORE INSERT  ON Teacher1  /*触发事件是插入或更新操作*/                                   

          FOR EACH ROW        /*行级触发器*/

          BEGIN                             /*定义触发动作体，*/

          IF (new.Job='教授') AND (new.Sal < 4000)

                THEN  SET new.Sal=4000;               

                  END IF;

        END;                              

;;

DELIMITER ;

在TEACHER1表中插入一些数据:

INSERT INTO `mystudent3`.`teacher1` (`Eno`, `Ename`, `Job`, `Sal`, `Deduct`) VALUES ('3', '1', '教授', '2', '2');

查看数据，发现工资自动改为4000。

六、实验作业

（一）实验数据

每当发生应急事件时，会从全国各地的医院、应急队等单位调配各个方面的应急专家到现场参与救援，比如2008 冰灾、汶川地震时都分别从二炮医院和北师大调配多位专家参与救援。应急处置数据库系统中保存各个不同应急事件中应急专家对不同应急事件的处置。其中专家表(expert)包括应急专家的专家编号(exp_id)、专家姓名(exp_name)、专家特长(exp_skill)、联系电话(exp_tel)和年龄(exp_age)；应急事件表(events_table)包括事件编号(event_id)、事件名称(event_name)和事件简要描述(event_desc)；专家救援贡献表(contribution)包括专家在不同应急事件中的贡献(contri)。一个专家可以参与多个应急事件，一个应急事件可以由不同专家参与。

（二）实验作业

(1) 新建数据库Emergency，创建数据表expert，将主键约束命名为expert_pk，设置

exp_name 和exp_tel 取值唯一, 并保证exp_age 取值为0-100 岁（在Mysql中需要触发器实现）。

(2) 使用sql 语句，向expert 中插入如下数据：

(3) 创建数据表events_table，并设置event_name 非空。

创建完成后，向events_table 表中补充声明主键，将event_id 作为主键，命名为events_pk。

(4) 使用sql 语句，向events_table 中插入如下数据：

(5) 创建数据表contribution，并定义主键约束和外键约束，约束命名自定义（如果没约束名，后面无法单独删除）。

(6) 使用sql 语句，向contribution 中插入如下数据：

(7) 向contribution 表中添加如下数据，会发生什么情况，为什么？

(8) 将contribution 表中event_id 为1 的值改为5，会出现什么情况，为什么？

(9) 直接将events_table 表删除会出现什么情况，为什么？（不需要删除该表）

(10) 将expert 表1 号专家的年龄改为102 岁，会出现什么情况，为什么？

(11) 在expert 表中插入如下数据会发生什么情况？

(13) 用命令方式删除contribution 表上的所有外键。

(14) a、在contribution 表上创建触发器函数，若contribution 表中event_id 被修改，相

应的events_table 表中的event_id 也被修改。

b、 创建AFTER 触发器，在contribution 表中更新数据时启动。

(15) 在contribution 表中将exp_id=4 的event_id 改成5。展示contribution 表和events_table 表

的情况。

(16) 删除expert 表上的触发器。

实验七 存储过程与用户函数

一、实验目的：了解并掌握SQL的基本编程。掌握MySQL中变量的定义与赋值、分支结构与循环结构以及SQL中常用函数的使用方法。理解触发器的含义及使用的场景，掌握触发器的编写和使用。

二、预习要求：实验前复习讲授过的有关创建数据库、基本表的SQL语句，有关增删改查的SQL语句。

三、实验重点和难点

实验重点：存储过程与用户函数的创建与使用；触发器的创建与使用。

实验难点：用存储过程实现业务逻辑；触发器的使用场景及局限性。

四、实验内容：

（一）在SQLyog下创建Xkgl（选课管理）数据库，包括Xsxxb（学生信息表）、Kcxxb（课程信息表）、Xkjlb（选课记录表）三个基本表,一个辅助表Hzpyb（汉字拼音表），导入Hzpyb（汉字拼音表，以脚本文件的形式提供给学生）。

1、创建数据库

CREATE DATABASE IF NOT EXISTS Xkgl;

2、学生信息表Xsxxb

创建学生信息表

DROP TABLE IF EXISTS xsxxb;-- 如果表存在的话就删除表

CREATE TABLE xsxxb (

  xsid  INT AUTO_INCREMENT PRIMARY KEY,-- 约束 自增 主键

  xh varchar(20) DEFAULT NULL,

  xm varchar(50) DEFAULT NULL,

  xb varchar(2) DEFAULT NULL,

  zydm int(11) DEFAULT NULL,

  nj int(11) DEFAULT NULL,

  bj varchar(10) DEFAULT NULL,

  sjh varchar(20) DEFAULT NULL,

  xmsx varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET= utf8;

表7-1 Xsxxb结构

3、课程信息表Kcxxb

DROP TABLE IF EXISTS kcxxb;-- 如果表存在的话就删除表

CREATE TABLE kcxxb (

  kcid  INT AUTO_INCREMENT PRIMARY KEY ,-- 约束 自增 主键

  kcdm varchar(10) DEFAULT NULL,

  kcmc varchar(50) DEFAULT NULL,

  xf double DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET= utf8;

表7-2 kcxxb结构

1. 选课记录表Xkjlb

DROP TABLE IF EXISTS xkjlb;-- 如果表存在的话就删除表

CREATE TABLE xkjlb (

  xkid INT AUTO_INCREMENT PRIMARY KEY,-- 约束 自增 主键

  xsid INT,

kcid INT,

cj INT,

FOREIGN KEY(xsid) REFERENCES xsxxb(xsid),

FOREIGN KEY(kcid) REFERENCES kcxxb(kcid)

) ENGINE=InnoDB DEFAULT CHARSET= utf8

表7-3 xkjlb结构

5、辅助表 汉字拼音表Hzpyb

    CREATE TABLE hzpyb(

  hz CHAR(2),

  jp CHAR(1),

  py VARCHAR(10),

  INDEX (hz)

) ENGINE=INNODB DEFAULT CHARSET=utf8

输入一些学生、课程、选课数据。

• 编写存储过程XsxxWh(学生信息查询) 和XsxxWh(学生信息维护) 实现对学生信息的查询和维护

DROP PROCEDURE IF EXISTS `XsxxCx`;

DELIMITER $$

CREATE PROCEDURE `XsxxCx`(cxlb int,cxnr varchar(50))

BEGIN

  IF cxlb=0 then    /* 查询所有学生信息*/

    SELECT * FROM Xsxxb;

  ELSEIF cxlb=1 then  /* 查询指定学号的学生信息*/

    SELECT * FROM Xsxxb WHERE xh=cxnr;

  ELSE  /* 按姓名或姓名缩写模糊查询*/

    SELECT * FROM Xsxxb WHERE xm LIKE concat(cxnr,'%') OR xmsx LIKE CONCAT(cxnr,'%');  /* CONCAT字符串拼接函数 */

  END IF;

END$$

DELIMITER ;

该存储过程有两个参数，cxlb（查询类别）和cxnr（查询内容（条件））可以实现查询所有学生信息或指定学号的学生信息或按姓名或姓名的拼音缩写模糊查询，配套的用户界面如下（仅供参考）单击“确定”按钮时开发工具调用该存储过程

图7-1学生信息查询页面

DROP PROCEDURE IF EXISTS `Xsxxwh`;

DELIMITER $$

CREATE PROCEDURE `Xsxxwh`(xsid INT,xh VARCHAR(10),xm VARCHAR(50),xb CHAR(2),zydm INT,nj INT,bj VARCHAR(10),sjh VARCHAR(20))

BEGIN

IF xsid=0 THEN

INSERT Xsxxb (xh,xm,xb,zydm,nj,bj,sjh) VALUES (xh,xm,xb,zydm,nj,bj,sjh);

ELSEIF xsid<0 THEN

       DELETE FROM xsxxb WHERE `xsxxb`.`xsid`=-xsid;

  ELSE

       UPDATE Xsxxb SET `xsxxb`.`xh`=xh, `xsxxb`.`xm`=xm, `xsxxb`.`xb`=xb, `xsxxb`.`zydm`=zydm, `xsxxb`.`nj`=nj, `xsxxb`.`bj`=bj, `xsxxb`.`sjh`=sjh WHERE `xsxxb`.`xsid`=xsid;

END IF;

END$$

DELIMITER ;

该存储过程可以实现对学生信息的增、删、改操作。配套的用户界面如下（仅供参考）单击“保存”按钮时开发工具调用该存储过程

图7-2学生信息输入页面

（三）编写函数PysxCx(拼音缩写查询) 实现汉字字符串的拼音缩写查询

DELIMITER $$

CREATE FUNCTION `pysxcx`(zw VARCHAR(50)) RETURNS VARCHAR(10) CHARSET utf8

BEGIN

       SET @l=CHAR_LENGTH(zw);

       SET @i=1;

       SET @pysx='';

       SET @jp='';

       WHILE @i<=@l DO

         SET @hz=SUBSTRING(zw,@i,1);

         SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;

         SET @pysx=CONCAT(@pysx,@jp);

         SET @i=@i+1;

END WHILE;

 RETURN @pysx;

END

$$

DELIMITER ;

该函数中用到的一些MySQL内置函数说明：

1. CHAR_LENGTH（）：按照字符个数统计字符串长度；
2. SUBSTRING（参数1，参数2，参数3）：三个参数分别表示：参数1表示需要截取的字符串，参数2表示从字符串的那个位置开始截取（字符串下标从1开始），参数3表示要截取多少位，如果不写，表示截取从参数2指定的位置开始剩下的全部字符
3. CONCAT（）：用于将多个字符串连接成一个字符串

函数的使用1：

图7-3内置函数使用效果1

函数的使用2：

图7-4内置函数使用效果2

（四）在SQLyog下创建Update_xmsx_before_insert_Xsxxb，实现当在Xsxxb中插入记录时，自动维护该记录中的xmsx字段。

CREATE TRIGGER `Update_xmsx_before_insert_Xsxxb` BEFORE INSERT ON `xsxxb`

    FOR EACH ROW BEGIN

  SET new.xmsx=pysxcx(new.xm);

END;

验证该触发器：执行插入操作

图7-5验证该触发器页面1

      查看操作结果

图7-6验证该触发器页面2

（五）在SQLyog下创建Update_xmsx_before_ update_xsxxb_xm，实现当在Xsxxb中更新姓名时，自动维护该记录中的xmsx字段。

CREATE TRIGGER `Update_xmsx_before_update_xsxxb_xm` BEFORE UPDATE ON `xsxxb`

    FOR EACH ROW BEGIN

  IF new.xm<>old.xm THEN

    SET new.xmsx=pysxcx(new.xm);

  END IF;

END;

验证该触发器：执行更新操作

图7-4验证该触发器页面3

查看操作结果

图7-5验证该触发器页面4

五、思考题：

（一）当参数或变量名与表中的列名相同时，应该怎么处理？

（二）CHAR_LENGTH（）与LENGTH（）的区别是什么？

（三）哪些业务场景适合用触发器？

（四）触发器有哪些局限性？

六、实验作业要求：

2、参照本次实验的内容，编写两个存储过程分别实现对课程信息的查询与维护。

1、参照本次实验的内容，在Kcxxb（课程信息表）上增加mcsx（课程名称的拼音缩写），并编写两个触发器实现对该字段的维护。

实验八 访问控制、事务处理与并发控制

一、实验目的：了解组角色、角色、用户的概念及他们之间的区别；了解数据库的不同权限；掌握为用户分配权限及收回权限的方法；掌握数据库事务管理的基本原理，包括显式事务、事务提交、事务回滚、隐式事务等，以及事务的编程方法。掌握数据库并发控制的基本原理及其应用方法。

二、预习要求：预习Mysql数据库管理系统用户管理功能相关知识。

三、实验重点和难点

实验重点：定义角色，分配权限和回收权限；显式事务的编写；并发操作带来的数据的不一致性问题。

实验难点：实现权限的再分配和回收；把事务的编写和存储过程的设计与使用结合起来；设计具体的例子演示各种封锁级别。

四、实验内容：

（一）练习并掌握Mysql数据库管理系统中用户管理相关操作。

（二）练习并掌握Mysql数据库管理系统中为用户分配权限及收回权限的操作。

（三）练习显式事务、事务提交、事务回滚、隐式事务等的编程方法。

（四）验证并发操作带来的数据的不一致性问题，包括丢失修改、不可重复读和读“脏”数据等情况。

五、实验方法和步骤：

（一）练习并掌握Mysql数据库管理系统中用户管理相关操作

1、查看Mysql数据库用户管理表

用户信息保存在Mysql数据库下的user表中。查看Mysql数据库下的user表，以显示目前数据库已经存在的用户信息。

select * from mysql.user

用户信息包括：主机地址、用户名、密码、权限。由host和user共同组成主键来区分用户。

user：代表用户的用户名

host：代表的是允许访问的客户端（IP或者主机地址）。如果host使用%，代表所有用户（客户端）都可以访问。

host的表现方式：

1）. IP地址，如192.168.1.10；

2）. 主机名，如localhost；

3）. 通配符。

%：匹配任意字符

_：匹配任意单个字符

如192.168.1._(允许192.168.1.1-192.168.1.9)登录。

2、创建新用户

理论上讲可以采用两种方式创建

1）、直接使用root用户在mysql.user表中插入记录（不推荐）

2）、专门创建用户的SQL指令

基本语法：create user 用户名 identified by ‘密码（明文）’；

（1）添加新用户one，允许本机访问，密码是789

create user one@'localhost'  identified by  '789';

（2）添加新用户user1，允许外网IP访问

create user  user1@'%'  identified   by  '123456';

（3）添加新用户user2，允许外网IP访问，没有密码。

create user user2;

3.删除用户（需要root权限）

注意：MySQL中user是带着host本身的（具有唯一性）

基本语法：drop user 用户名@host；

（1）删除user2用户

drop user user2;

4.修改用户密码

MySQL中提供了多种修改的方式：基本上都必须使用一个系统函数：password()

需要靠该函数对密码进行加密处理。

1)、使用专门的修改密码的指令

基本语法：set password for 用户 = password('新的明文密码')

（1）修改user1的密码为‘123’

SET PASSWORD FOR user1@'%'=PASSWORD('123')

2)、使用更新语句update来修改表

基本语法：update mysql.user set password = password('新的明文密码') where user = ' ' and host = '';（不推荐使用）

5.root用户 密码丢失找回

如果忘记了root用户密码，就需要去找回或者重置root用户密码

1）停止服务

cmd中：net stop mysql

2）重新启动服务：mysqld.exe --skip-grant-tables   //启动服务器，但是跳过权限管理

3）当前启动的服务器没有权限概念，非常危险，任何客户端不需要任何用户信息都可以直接登陆，而且是root权限

4）在MySQL中修改root用户的密码：指定 用户名@host

5）关闭服务器（注意在任务管理器强制关闭），重启服务（net start mysql）

（二）练习并掌握Mysql数据库管理系统中为用户分配权限及收回权限的操作。

1、授予权限：grant

（1）把mystudent数据库所有表的所有操作权赋予用户user1

GRANT ALL PRIVILEGES ON  mystudent.*  TO  user1;

操作完毕后以user1身份连接mysql，看数据变化。

（2）把所有数据库所有表的所有操作权赋予用户user1

GRANT ALL PRIVILEGES ON  *.*  TO  user1;

注意user1需要重新登陆才能看到权限的变化。实际上权限已经生效，执行命令对应表的操作即可看到结果。

3、取消权限：revoke

权限回收：将权限从用户手中收回

（1）把mystudent数据库所有表的所有操作权从用户user1收回。

REVOKE ALL PRIVILEGES ON  mystudent.*  FROM user1;

（2）把所有数据库所有表的所有操作权从用户user1收回。

REVOKE ALL PRIVILEGES ON  *.*  FROM user1;

4、刷新权限：flush

刷新：将当前对用户的权限操作，进行刷新：将操作的具体内容同步到表中

基本语法：flush privileges;

（三）练习显式事务、事务提交、事务回滚、隐式事务等的编程方法。

1、准备测试数据

在 banks 数据库中创建数据表 account，用于完成转账功能：

CREATE DATABASE banks CHARSET=utf8;

USE banks;

CREATE TABLE  account (

   id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

   username  VARCHAR(32) NOT NULL UNIQUE COMMENT '账户名',

   money  DECIMAL(10,2) NOT NULL COMMENT '存款金额'

)ENGINE=INNODB CHARSET=utf8;

需要注意的是，在 MySQL 数据库中，要想对数据表进行事务处理，需要将其存储引擎设置为 InnoDB。

2、开启事务

在MySQL数据库中，使用事务之前必须先开启事务，具体SQL语句如下。

START TRANSACTION;

# 或

BEGIN;

执行上述语句后，若MySQL中没有返回警告或错误信息提示，则初始化事务成功，可以继续执行以下的操作了

3、创建事务

创建事务就是在事务开启后，执行的一系列 SQL 语句。例如，开启事务处理后，向表 account 中插入两条测试数据，具体如下。

START TRANSACTION;

INSERT INTO  account  VALUES (NULL, '张三', 5000), (NULL, '李四', 100);

上述SQL语句执行后，通过SELECT查询执行结果，如图所示。

SELECT * FROM account;

图8-1查询结果1

4、提交事务

在用户没有提交事务前，其他连接MySQL服务器的用户进程是看不到当前事务的处理结果的。

新创建一个mysql连接“连接2”，进入到banks数据库中查看数据表account中的数据，结果如图所示。

USE banks;

SELECT * FROM account;

图8-2查询结果2

在原来的连接客户端，对上述创建的事务进行提交操作，具体SQL语句如下。

COMMIT;

执行上述语句后，在“连接2”客户端再次执行SELECT查询，即可得到如下查询结果。

图8-3查询结果3

5、撤销事务

撤销事务也称事务回滚，用于创建事务时执行的SQL语句与业务逻辑不符或操作错误时，通过ROLLBACK命令撤销对数据库的所有操作，或利用ROLLBACK TO SAVEPOINT回滚到指定位置。撤销事务的操作在实际应用中，有着非常重要的作用。

① 撤销全部事务处理

假设张三转让李四一辆二手自行车，李四需要向张三支付199元，则执行的SQL语句如下。

在第一个连接窗口执行如下语句：

START TRANSACTION;

UPDATE  account  SET  money = money +199 WHERE username='张三';

UPDATE  account  SET  money = money -199 WHERE username='李四';

上述SQL语句执行后，通过SELECT查询执行结果，如图所示。

SELECT * FROM account;

图8-4查询结果4

从查询结果可知，李四的账户余额为负数，显然不符合正常的业务逻辑。此时就需要使用ROLLBACK命令进行全部事务的回滚，撤销刚才对数据表account的更新操作。具体SQL语句如下。

ROLLBACK;

执行完事务回滚后再进行SELECT查询，结果与开始创建事务查询结果相同。

SELECT * FROM account;

图8-5查询结果5

② 撤销事务到指定位置

使用SAVEPOINT可以在事务处理使用指定不同的撤销位置，在需要事务撤销时进行回滚。例如，向数据表account中逐条插入3条记录，并利用SAVEPOINT在每次插入数据后设置一个回滚位置，SQL语句如下。

START TRANSACTION;

INSERT INTO account VALUES (NULL, '王五', 500);

SAVEPOINT test1;

INSERT INTO account VALUES (NULL, '赵六', 6000);

SAVEPOINT test2;

INSERT INTO account VALUES (NULL, '李七', 70);

SAVEPOINT test3;

SELECT * FROM account;

上述SQL执行后，进行SELECT查询，就会看到account表中已经存在新插入的这3条记录。

图8-6查询结果6

假设从“李七”开始的记录有误需要撤销，就可以回滚到test2位置，具体SQL如下。

ROLLBACK TO SAVEPOINT test2;

执行上述SQL语句后，查询数据表account中的数据，就会看到新插入的记录中只有“王五”和“赵六”。由此可见，事务可以回滚到想要的位置。

图8-7查询结果7

需要注意的是，SAVEPOINT指定回滚位置的操作对于已经提交的事务并不适用，且对于定义了相同名称的回滚位置，则后面的定义会覆盖之前的定义。此外，对于不再需要使用的回滚位置标识，可以通过RELEASE SAVEPOINT命令进行删除。例如，删除回滚位置test1，SQL语句如下。

RELEASE SAVEPOINT test1;

CHAIN和RELEASE子句可以用来分别定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别；RELEASE则会断开和客户端的连接。

例如，向数据表account中添加一条数据后，提交事务并开启新事务，具体SQL语句如下。

START TRANSACTION;

INSERT INTO account VALUES (NULL, '郑七', 70);

COMMIT AND CHAIN;

执行上述语句后，在“连接2”客户端再次执行SELECT查询，即可得到如下查询结果。

图8-8查询结果8

接着在第一个连接窗口继续执行插入操作，具体SQL语句如下。

INSERT INTO `account` VALUES (NULL, '谢八', 8);

执行上述语句后，在“连接2”客户端再次执行SELECT查询，可以看到account表中新增记录只有“郑七”没有“谢八”。

接下来在第一个连接窗口提交事务处理并断开与客户端的连接，具体SQL语句如下。

COMMIT RELEASE;

此时，在查看事务处理结果。

SELECT * FROM account;

会看到数据，查看历史记录，发现实际上RELEASE已经断开与服务器的连接，但是再次执行sql语句时，客户端自动重新创建的连接。

。

需要注意的是，在同一个进程中，在未结束前一个事务而又重新打开另一个事务时，前一个事务会被自动提交，也就是说事务不支持嵌套功能。

6、事务的自动提交

MySQL中默认操作就是自动提交模式。除非显示地开启一个事务（START TRANSACTION），否则所有的SQL都会被当做单独的事务自动提交（COMMIT）。因此，如果用户想要控制事务的自动提交方式，可以通过AUTOCOMMIT来实现，具体如下。

SET AUTOCOMMIT=0;

通过上述语句即可关闭MySQL中的自动提交功能，实现了只有用户手动执行提交（COMMIT）操作，MySQL才会将事务提交到数据库系统中。否则，若不执行手动提交，而终止MySQL会话，数据库会自动执行回滚操作。

在第一个连接窗口执行如此下语句：

SET AUTOCOMMIT=0;

INSERT INTO `account` VALUES (NULL, '王丽', 45);

SELECT * FROM account;

到第2各窗口查看数据，发现语句并未提交。

执行如下语句，回复MySQL的默认自动提交模式

SET AUTOCOMMIT=1;

（四）验证并发操作带来的数据的不一致性问题，包括丢失修改、不可重复读和读“脏”数据等情况。要求通过取消查询分析器的自动提交功能，创建两个不同的用户，分别登录查询分析器，同时打开两个客户端；通过SQL语言设计具体例子展示不同的封锁级别的应用场景，验证各种封锁级别的并发控制效果，以进一步理解封锁技术是如何解决事务并发导致的问题的。

1、事务隔离级别:

表8-1事务隔离级别对应关系表

Read uncommitted：就是一个事务可以读取另一个未提交事务的数据

Read committed：一个事务要等另一个事务提交后才能读取数据

Repeatable read：就是在开始读取数据（事务开启）时，不再允许修改操作

Serializable：在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。

mysql默认的事务处理级别是'REPEATABLE-READ'，而Oracle和SQL Server是READ_COMMITED

2、查看隔离级别

select @@global.tx_isolation,@@tx_isolation;

图8-9查看隔离级别1

3、设置隔离级别

1）设置当前会话隔离级别

set session transaction isolation level read committed;

查看隔离级别

select @@global.tx_isolation,@@tx_isolation;

图8-10查看隔离级别2

2）设置全局的隔离级别:（注意只能以超级用户身份设置）

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

理解全局的隔离级别与设置当前会话隔离级别：

设置当前会话（客户端）的隔离级别，当客户端关闭后重新进入，隔离级别会恢复到系统的全局隔离状态。理解‘当前会话’：MySQL中认为，打开一个客户端就是开启一个会话。其实质是建立一个网络连接。这个网络连接是有状态性的，一旦关闭，此会话级别中设置的变量就会恢复为系统的变量。

4、并发操作带来的数据的不一致性问题

脏读:可以读取到其他事务修改但未提交的脏数据。

不可重复读:在一个事务中重复读取相同数据。在其中两次读取数据之间有另一个事务修改并提交了该数据。使得事务两次读到的数据是不一样的。

幻读: 第一个事务对一个表中的数据进行了修改，这种修改涉及 到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有 修改的数据行，就好象发生了幻觉一样。

丢失更新: 多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。

验证并发操作带来的数据的不一致性问题，包括丢失修改、不可重复读、幻读和读“脏”数据等情况。要求通过取消查询分析器的自动提交功能，创建两个不同的用户，分别登录查询分析器，同时打开两个客户端；

1）打开两个客户端（一个连接1，一个连接2），分别设置当前回话级别为UNCOMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT @@global.tx_isolation,@@tx_isolation;

2）分别关闭MySQL中的自动提交事务功能

SET AUTOCOMMIT=0;

3) 脏读验证。

在两个窗口分别执行：

窗口1

BEGIN;

SELECT * FROM account;

UPDATE account SET money=money-1000 WHERE username='张三';

Commit;

窗口2

BEGIN;

SELECT * FROM account;

在窗口1提交之前，窗口2可以读到窗口1未提交的数据。

在两个窗口分别设置

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

重新运行上面代码，发现只有当窗口1的修改提交以后，窗口2才能读到修改的数据。

（其他验证略）

（五）对于mySPJ数据库进行如下数据控制

①使用GRANT把对S表查询的权利授予WangLi。

②使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。

③使用REVOKE把LiMing对P表插入、删除的权利回收。

(六)创建下面医院数据库，按要求进行权限设置操作。

医院数据库包括医生表 doctor，医生表包含医生工号（doc_id），医生姓名

（doc_name），医生性别（doc_sex），医生年龄（doc_age），医生所在科室

（doc_dep）；科室表 department，科室表包含科室编号（dep_id），科室名称

（dep_name），科室地址（dep_addr）。

(1) 创建数据表 doctor。

表8-2 doctor表结构

这次doc_id设置为自增字段，它的语法规则是:属性名 属性类型  auto_increment

(2) 向 doctor 表中插入数据。

表8-3 doctor表的数据

对自增字段数据插入时，对应位置填上null，那么该字段自动编号。

例如：

Insert into doctor values（null，‘aa’，‘male’，35，‘aaa’）；

(3) 创建数据表 department。

表8-4 department表结构

(4) 向 department 表中插入数据。

表8-5 department表数据

(5) 创建用户 user1，密码为’12345’。

(6) 授予用户 user1 对 doctor 表的查询权限，并进行验证。

(7) 收回用户 user1 对 doctor 表的查询权限，并进行验证。

(8)

a) 创建用户 user2，密码为’12345’，授予其对 doctor 表的查询权限和修改字段doc_name 的权限，并允许将此权限授予其他用户；验证 user2 对 doctor 表的查询和更新权限（将 doc_id 为 1 的医生姓名更新为’xxx’）；

b) 创建用户 user3，密码为’12345’，授予其对 doctor 表的查询权限，并允许将此

权限授予其他用户；验证 user3 对 doctor 表的查询权限；

c) 创建用户 user4，密码为’12345’，验证 user4 对 doctor 表的查询权限；

d) 用户 user2 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；

e) 用户 user3 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；

f) 用户 user3 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有查询权限；

g) 用户 user2 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有查询权限。

六、思考题：

七、实验作业要求：

(一)实验目的：

(二)实验内容：将实验步骤中（五）（六）相关操作写出来。

(三)实验结果：

(四)问题及解决：首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。

(五)回答思考题提出的问题。