MySQL数据库技术与实验指导

（第1版）

钱雪忠、王燕玲、张平 编著

清华大学出版社

2011.09

1

实验1 数据库系统基础操作

进入MySQL的官方下载页面：http://www.mysql.com/downloads/

如果想找旧的发布版本，可进入页面：http://downloads.mysql.com/archives.php

例1-1 有一个脚本文件（test.sql），文件内容：

Show databases; Create database test; Use test;

Create table table_1( I int ) ENGINE = MyISAM;

执行之。

解：C:\\> mysql -h localhost -u root -p source c:\\test.sql

实验2 MySQL数据库基础操作

例2-1 创建jxgl数据库。

解：mysql>create database jxgl; 例2-2 查看本机服务器上数据库。 解：mysql>show databases;

或：mysql>show databases like 'my%'; 例2-3 进入jxgl数据库。 解：mysql> USE jxgl;

例2-4 在命令行环境中，创建和删除数据库jxgl： 解：创建数据库：

C:\\> mysqladmin -h localhost -u root -p create jxgl 删除数据库：

C:\\> mysqladmin -h localhost -u root -p drop jxgl

实验3 表、ER图、索引与视图的基础操作

CREATE TABLE IF NOT EXISTS `jxgl`.`sc`( `sno` CHAR(7) NOT NULL ,

2

`cno` CHAR(2) NOT NULL , `grade` INT NULL , PRIMARY

KEY

(`sno`,`cno`),INDEX

`sc_ibfk_1`(`sno`

ASC),INDEX

`sc_ibfk_2`(`cno` ASC) , CONSTRAINT `sc_ibfk_1` FOREIGN KEY(`sno`) REFERENCES `jxgl`. `student`(`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT,

CONSTRAINT `sc_ibfk_2` FOREIGN KEY(`cno`) REFERENCES `jxgl`.`course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB;

例3-1 列出jxgl数据库中所有表。 解：mysql> use jxgl; mysql> show tables;

或：C:\\>mysqlshow -h localhost -u root -p jxgl 例3-2 列出jxgl数据库中表student的列。 解：mysql> use jxgl;

mysql> show columns from student;

或：mysql> show columns from jxgl.student;

或：C:\\>mysqlshow -h localhost -u root -p jxgl student 例3-3 列出jxgl数据库中表的详细信息。 解：mysql> use jxgl; mysql> show table status;

或：C:\\>mysqlshow --status -h localhost -u root -p jxgl 例3-4 列出jxgl数据库中表sc的索引。 解：mysql> use jxgl; mysql> show index from sc;

或：mysql> show index from jxgl.sc;

例3-5使用SQL语句创建示例数据库（jxgl）：其中，学生表要求学号为主键，性别默认为男，取值必须为男或女，年龄取值在15到45之间。

课程表（course）要求主键为课程编号，外键为先修课号，参照课程表的主键（cno）。 选修表（sc）要求主键为（学号，课程编号），学号为外键，参照学生表中的学号，课程编号为外键，参照课程表中的课程编号；成绩不为空时必须在0到100之间。

解：

Create Table Student

( Sno CHAR(7) NOT NULL ,

Sname VARCHAR(16),

Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'), Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Sdept CHAR(2), PRIMARY KEY(Sno) ) ENGINE = InnoDB;

3

Create Table COURSE

( Cno CHAR(2) NOT NULL ,

Cname VARCHAR(20), Cpno CHAR(2), Credit SMALLINT, PRIMARY KEY(Cno),

foreign key(cpno) references course(cno) ) ENGINE = InnoDB; Create table sc

( sno char(7) not null,

cno char(2) not null,

grade smallint null check(grade is null or (grade between 0 and 100)), Primary key(sno,cno),

Foreign key(sno) references student(sno), Foreign key(cno) references course(cno) ) ENGINE = InnoDB;

例3-6 在表student中增加属性生日（birthday）。 解：ALTER TABLE student ADD birthday datetime; 例3-7删除例3-6中增加的属性生日（birthday）。 解：ALTER TABLE student DROP birthday;

例3-8 在表student中属性sname上建立索引(sn)。 解：alter table student add unique sn(sname); 例3-9 删除表sc。 解：DROP TABLE sc;

例3-10 在数据库jxgl中创建视图v，查询学生姓名，课程名及其所学课程的成绩。 解：mysql>use jxgl --先选择jxgl数据库为当前数据库 Database changed

mysql> create view v(sname,cname, grade) as select sname,cname,grade from

student,course,sc

-> where student.sno=sc.sno and sc.cno=ccourse.cno; 例3-11 显示数据库jxgl中视图v创建的信息。 解：mysql> SHOW CREATE VIEW v;

实验4 SQL语言——SELECT查询操作

例4-1 查询考试成绩大于等于90的学生学号。 解：SELECT DISTINCT SNO

4

FROM SC

WHERE GRADE>=90;

例4-2 查年龄大于18，并且不是信息系（IS）与数学系（MA）的学生姓名和性别。 解：SELECT SNAME, SSEX

FROM STUDENT WHERE SAGE>18 AND SDEPT NOT IN ('IS', 'MA');

例4-3 查以“MIS_”开头，且倒数第二个汉字为“导”字的课程的详细信息。 解：SELECT * FROM COURSE WHERE CNAME LIKE 'MIS#_%导_' ESCAPE '#'; 例4-4 查询选修计算机系（CS）选修了2门及以上课程的学生学号。 解：SELECT STUDENT.SNO

FROM STUDENT, SC

WHERE SDEPT='CS' AND STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNO HAVING COUNT(*)>=2; 例4-5 查询student表与sc表的广义笛卡尔积。 解：SELECT STUDENT.*, SC.*

FROM STUDENT CROSS JOIN SC;

例4-6 查询student表与sc表基于学号sno的等值连接。 解：SELECT * FROM STUDENT, SC WHERE STUDENT.SNO=SC.SNO; 例4-7 查询student表与sc表基于学号sno的自然连接。 解：SELECT STUDENT.*, SC.CNO, SC.GRADE

FROM STUDENT, SC

WHERE STUDENT.SNO=SC.SNO; 例4-8 查询课程号的间接先修课程号。 解：SELECT FIRST.CNO, SECOND.CNO

FROM COURSE FIRST, COURSE SECOND WHERE FIRST.CPNO=SECOND.CNO;

例4-9 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。 解：SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT, CNO, GRADE

FROM STUDENT LEFT OUTER JOIN SC ON STUDENT.SNO=SC.SNO;

例4-10 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均全部列出）。

解：SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT,

COURSE.CNO, GRADE, CNAME, CPNO, CCREDIT FROM STUDENT LEFT OUTER JOIN SC

ON STUDENT.SNO=SC.SNO FULL OUTER JOIN COURSE ON SC.CNO=COURSE.CNO; 说明：因MySQL不支持“FULL OUTER JOIN”，为此上命令运行会出错的。可以把“FULL OUTER JOIN”用“?LEFT OUTER JOIN ? UNION ? RIGHT OUTER JOIN?”来变通实现，为此，查询命令可改为：

SELECT a.SNO, a.SNAME, a.SSEX, a.SAGE, a.SDEPT, C.CNO, b.GRADE, c.CNAME, c.CPNO,

5

c.CREDIT FROM STUDENT a LEFT OUTER JOIN SC b ON a.SNO=b.SNO LEFT OUTER JOIN COURSE c ON b.CNO=C.CNO

UNION

SELECT a2.SNO, a2.SNAME, a2.SSEX, a2.SAGE, a2.SDEPT, c2.CNO, b2.GRADE,

c2.CNAME, c2.CPNO, c2.CREDIT

FROM STUDENT a2 LEFT OUTER JOIN SC b2 ON a2.SNO=b2.SNO RIGHT OUTER JOIN

COURSE c2 ON b2.CNO=C2.CNO;

例4-11 查询性别为男、课程成绩及格的学生信息及课程号、成绩。 解：SELECT STUDENT.* , CNO, GRADE

FROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO WHERE SSEX='男' AND GRADE>=60;

例4-12 查询与“钱横”在同一系学习的学生信息。 解：SELECT * FROM STUDENT

WHERE SDEPT IN (SELECT SDEPT FROM STUDENT WHERE SNAME='钱横')； 例4-13 找出同系、同年龄、同性别的学生。 解：SELECT T.* FROM STUDENT AS T

WHERE (T.sdept, T.SAGE, T.SSEX) IN

( SELECT SDEPT, SAGE, SSEX FROM STUDENT AS S WHERE S.SNO<>T.SNO);

例4-14 查询选修了课程名为“数据库系统”的学生学号，姓名和所在系。 解：

SELECT SNO, SNAME, SDEPT FROM STUDENT WHERE SNO IN

( SELECT SNO FROM SC

WHERE CNO IN (SELECT CNO FROM COURSE WHERE CNAME='数据库系统')); 或

SELECT STUDENT.SNO, SNAME, SDEPT

FROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNO INNER JOIN COURSE ON SC.CNO=COURSE.CNO;

例4-15 检索至少不学2和4课程的学生学号和姓名。 解：

SELECT SNO, SNAME FROM STUDENT

WHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO IN ('2', '4'));

例4-16 查询其他系中比信息系IS所有学生年龄均大的学生名单，并排序输出。 解：

SELECT SNAME FROM STUDENT

WHERE SAGE>ALL(SELECT SAGE FROM STUDENT WHERE SDEPT='IS') AND SDEPT<>'IS'

6

ORDER BY SNAME;

例4-17 查询选修了全部课程的学生姓名（为了有查询结果，自己可以调整表的内容）。 解：

SELECT SNAME FROM STUDENT WHERE NOT EXISTS

( SELECT * FROM COURSE WHERE NOT EXISTS

( SELECT * FROM SC WHERE SNO=SC.SNO AND CNO=COURSE.CNO));

例4-18 查询至少选修了学生“2005001”选修的全部课程的学生号码。 解：

SELECT SNO FROM STUDENT SX WHERE NOT EXISTS

( SELECT * FROM SC SCY

WHERE SCY.SNO='2005001' AND NOT EXISTS

( SELECT * FROM SC SCZ

WHERE SCZ.SNO=SX.SNO AND SCZ.CNO=SCY.CNO));

例4-19 查询平均成绩大于85分的学号，姓名和平均成绩。 解：

SELECT STUDENT.SNO, SNAME, AVG(GRADE) FROM STUDENT, SC

WHERE STUDENT.SNO=SC.SNO

GROUP BY STUDENT.SNO, SNAME HAVING AVG(GRADE)>85;

实验5 SQL语言——数据更新操作

例5-1 向jxgl数据库中表student添加数据 ('2005007','李涛','男',19,'IS')。 解：mysql> use jxgl;

mysql> insert into student values ('2005007','李涛','男',19,'IS'); 或：mysql>insert into student set sno='2005007', >sname='李涛',ssex='男',sage=19,sdept='IS'; 例解

5-2 向：

jxgl

数据库中表

into

student

添加数据('2005008','陈高','女

('2005008','

陈

高

','

女

',21,'AT'),('2005009','张杰','男',17,'AT')。

Mysql>insert

student

values

',21,'AT'),('2005009','张杰','男',17,'AT');

例5-3 在数据库中先创建表：tbl_name1（sn,sex,dept），现从student表把数据转入tb1_name1。

解：

7

mysql>create table tbl_name1(sn,sex,dept) select sname sn,ssex sex,sdept dept from where 1=2; --先创建表tbl_name1；

mysql>insert into tbl_name1(sn,sex,dept) select sname,ssex,sdept from student;

例5-4 向jxgl数据库中表sc添加数据('2005001','5',80)。 解：mysql>replace sc values ('2005001','5',80);

注意这些规则意味着一个像“./myfile.txt”给出的文件是从服务器的数据目录读取，而作为“myfile.txt”给出的一个文件是从当前数据库的数据库目录下读取。也要注意，对于下列那些语句，对db1文件从数据库目录读取，而不是db2：

mysql> USE db1;

mysql> LOAD DATA INFILE \

例5-5 在student表中，我们发现陈高的性别没有指定，因此我们可以这样修改这个记录。

解：mysql> update student set ssex='女' where sname='陈高'; 例5-6 在sc表中，删除陈高选修课程信息。

解：mysql> delete from sc where sno=(select sno from student where sname='陈高');

例5-7 删除所有学生选课记录 解：mysql>delete from sc;

实验6 嵌入式SQL应用

表名与属性名对应由英文表示，则关系模式为： 1) student(sno、sname、ssex、sage、sdept) 2) course(cno、cname、cpno、ccredit) 3) sc(sno、cno、grade)

4) users(uno、uname、upassword、uclass) 创建数据库及其表结构的SQL命令： CREATE TABLE student (

sno char(5) NOT null primary key, sname char(6) null ,

ssex char(2) null , sage int null ,

sdept char(2) null) ENGINE = MyISAM/InnoDB;--MyISAM/InnoDB选其一 CREATE TABLE sc (sno char(5) NOT null,cno char(1) NOT null,grade int null,primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno)) ENGINE = MyISAM/InnoDB;

8

CREATE TABLE course (cno char(1) NOT null primary key,cname char(10) null ,cpno char(1) null ,ccredit int null) ENGINE = MyISAM/InnoDB;

CREATE TABLE users(uno char(6) NOT NULL PRIMARY KEY,uname VARCHAR(10) NOT NULL,upassword VARCHAR(10) NULL,uclass char(1) DEFAULT 'A') MyISAM/InnoDB;

三、MS-DOS窗口中编译、连接与运行

利用VC++6.0 C编译器直接在MS-DOS窗口中编译、连接与运行，也是简单便捷的方法。设VC++6.0 C编译器相关文件（如\\BIN含可执行程序，\\INCLUDE含头文件，\\LIB含库文件）放在C:\\VC98目录中。可以把C语言源程序（如CC.C）放在某目录中如C:\\esqlc-mysql。

(1)启动“MS-DOS”窗口，执行如下命令，使当前盘为C，当前目录为esqlc-mysql C:

cd\\esqlc-mysql

(2)设置系统环境变量值，执行如下批处理命令： setenv-mysql

(3)编译、连接嵌入SQL的C语言程序（例如：CC.C）,执行如下批处理命令(有语法语义错时可修改后重新运行)：

run-mysql CC

(4)运行生成的应用程序（CC.exe），输入程序名即可：（如图6-18所示） CC 说明：

(a)嵌入SQL的C语言程序的可用任意文本编辑器进行编辑修改（如记事本、WORD等）。 (b)你的数据库中应有student、sc、course等所需的表（或通过嵌入SQL C语言运行时执行创建功能）。

(c)你需要有VC++6.0的C程序编译器cl.exe及相关的动态连接库与库文件等。 (d) setenv-mysql.bat文件内容（根据VC++6.0安装目录及MySQL安装目录需做相应修改的）：

@echo off

echo Use SETENV to set up the appropriate environment for echo building Embedded SQL for C programs

set path=\\\vc98\\bin\set INCLUDE=C:\\Program Files\\MySQL\\MySQL Server

5.1\\Include;c:\\VC98\\Include;%include%

set LIB=\\\VC98\\Lib;%lib%

ENGINE =

(e)嵌入SQL的C语言程序编译环境要求(即SETENV-mysql.BAT文件内容)：

需VC安装目录下的\\bin、\\include、\\lib子目录；MySQL安装后子目录\\binn、\\include、\\lib\\debug等。为此SETENV-mysql.BAT文件目录情况应按照实际目录情况调整。

(f) run-mysql.bat文件内容为：

cl /c /W3 /D\

9

link /NOD /subsystem:console /debug:full /debugtype:cv %1.obj kernel32.lib libcmt.lib

libmysql.lib

说明：%1.c代表C源程序，连接中用到的库文件在VC安装子目录及MySQL安装子目

录中能找到。

(g)以上实验的运行环境为Windows XP+ MySQL 5.5.9+VC++6.0，在其它环境下批处理文件内容应有变动，编译、连接、运行中可能要用到动态连接库文件如：mspdb60.dll、sqlakw32.dll、libmysql.dll等（需要时复制它们到编译、运行环境中去）。

要说明的是：解决汉子显示问题，C源程序中如下命令相关的： mysql_query(&mysql,\NAMES latin1;\ //支持处理汉字

SET NAMES

GBK|Gb2312|utf8|latin1; 可根据具体要求选择不同字符集以支持汉字的显示。

实验7 数据库存储和优化

例7-1 多表连接查询分析，及其改进。 解

：

mysql>EXPLAIN SELECT student.sname, course.cname ,grade From

student,course,sc WHERE student.sno=sc.sno and sc.cno=course.cno and sdept='cs';

在教学管理系统（jxgl）中，创建表test，并插入8万条记录。在mysql命令行提示符下录入如下程序并运行之。

/*创建表*/

Create table test(id int unique AUTO_INCREMENT,rg datetime null,srq varchar(20) null,hh

smallint

null,

mm

smallint

null,

ss

smallint

null,num

numeric(12,3),primary key(id)) AUTO_INCREMENT = 1 engine = MyISAM;

/*创建存储过程生成表中数据*/ DELIMITER //

CREATE PROCEDURE `p1`() begin

set @i=1;

WHILE @i<=80000 do

INSERT INTO TEST(RG,SRQ,HH,MM,SS,NUM) VALUES(NOW(),NOW(),HOUR(NOW()),

MINUTE(NOW()),SECOND(NOW()),RAND(@i)*100);

set @i=@i+1; END WHILE; End//

/*调用存储过程*/ call p1// DELIMITER ;

10

1、单记录插入(约30ms，给出的毫秒数在特定环境下得出的，只做参考)。 DELIMITER //

Select @i:=max(id) from test; INSERT INTO TEST(RG,SRQ,HH,MM,SS,NUM) VALUES(NOW(),NOW(),HOUR(NOW()), MINUTE(NOW()),SECOND(NOW()),RAND(@i)*100);// 2、查询所有记录，按id排序（约157ms）。 Select * from test order by id;

3、查询所有记录，按mm排序（约140ms）。 Select * from test order by mm; 4、单纪录查询（约0ms）。

Select id from test where id=51; 二、对test表id字段建立非聚集索引。 1、建立索引耗时（约980ms）。

Create index indexname1 on test(id);

2、单记录插入（约0ms），插入命令同“单记录插入”。

3、查询所有记录，按id排序（约157ms），查询命令同“查询按id排序”。 4、查询所有记录，按mm排序（约150ms），查询命令同“查询按mm排序”。 5、单纪录查询（约0ms），查询命令同“单纪录查询”。 6、删除索引（约870ms）。

Drop index indexname1 on test; 三、对test表mm字段建立非聚集索引。 1、建索引耗时（约1016ms）。

Create index indexname1 on test(mm);

2、单记录插入（约0ms），插入命令同“单记录插入”。

3、查询所有记录，按id排序（约160ms），查询命令同“查询按id排序”。 4、查询所有记录，按mm排序（约150ms），查询命令同“查询按mm排序”。 5、单纪录查询（约0ms），查询命令同“单纪录查询”。 6、删除索引（约953ms）。

Drop index indexname1 on test; 四、对test表id字段建立唯一索引。 1、建立索引耗时（约1125ms）。

Create UNIQUE index indexname1 on test(id); 2、单记录插入（约10ms），插入命令同“单记录插入”。

3、查询所有记录，按id排序（约156ms），查询命令同“查询按id排序”。 4、查询所有记录，按mm排序（约156ms），查询命令同“查询按mm排序”。

11

5、单纪录查询（约0ms），查询命令同“单纪录查询”。 6、删除索引（约968ms）。

Drop index indexname1 on test;

实验8 存储过程的基本操作

例8-1 创建带输出参数的存储过程，求学生人数。 解：mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc(OUT param1 INT)

->BEGIN

-> SELECT COUNT(*) INTO param1 FROM student; ->END//

Query OK, 0 rows affected (0.00 sec)

例8-2创建带输入参数的存储过程，根据学生学号（sno）查询该学生所学课程的课程编号（cno）和成绩（grade）。

解：mysql> delimiter //

mysql>CREATE PROCEDURE proc_sc_findById(in n int)

->BEGIN

-> SELECT sno,cno,grade FROM sc where sno=n; ->END//

例8-3 删除例8-2创建的存储过程。

解：mysql>drop PROCEDURE IF EXISTS proc_sc_findById;

例8-4 查看

例8-1创建的存储过程。

解：mysql>show create PROCEDURE simpleproc;

例8-5 查看在jxgl中创建的所有存储过程。 解：mysql>show PROCEDURE status;

例8-6 调用在

例8-1中创建的simpleproc存储过程（带输出参数）。 解：mysql>call simpleproc(@count);

12

实验9 触发器的基本操作

例如，下述语句将创建1个表和1个INSERT触发程序。触发程序将插入表中某一列的值加在一起：

CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; 要测试触发器的执行情况，可以运行如下代码： set @sum=0;

insert into account values(1,100.1); select @sum;

例9-1在表sc上定义1个UPDATE触发程序，用于检查更新每一行时，grade位于0～100的范围内，否则回退。

解：mysql> delimiter //

mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON sc

->FOR EACH ROW ->BEGIN

-> IF NEW.grade < 0 or NEW.grade >100 THEN -> Set NEW.grade=OLD.grade; -> END IF; ->END;// mysql> delimiter ; 调用触发器：

Mysql>update sc set grade=110 where sno='2005001' and cno='1';

实验10 数据库安全性

例10-1 在MySQL数据库中新建用户“dba”，密码为：“sqlstudy”。 解：mysql>CREATE USER dba IDENTIFIED BY 'sqlstudy'; 例10-2把用户dba改名为hello。 mysql>rename user dba to hello; 例10-3把用户hello的密码改为1234。

mysql>set password for hello = password('1234');

例10-4删除 MySQL 数据库用户hello，也最好显式指定 hostname。 mysql>drop user hello;

13

等价于：drop user hello@'%' 例10-5 显示一个用户admin的权限： mysql>SHOW GRANTS FOR admin@localhost; 其显示结果为当时创建该用户的GRANT授权语句：

GRANT RELOAD, SHUTDOWN, PROCESS ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '28e89ebc62d6e19a'

上面命令中密码是加密后的形式。

例10-6 先把数据库jxgl的所有权限授予给用户kite@localhost，接着再把权限从用户kite@localhost处收回。

解：

授权：mysql>GRANT ALL ON jxgl.* TO kite@localhost IDENTIFIED BY \ 删除数据库授权：mysql>REVOKE ALL ON jxgl.* FROM kite@localhost; 但是，kite@localhost用户仍旧留在user表中，可以查看： mysql>SELECT * FROM mysql.user;

例10-7 将jxgl数据库的变更权限赋给def用户，并显示所授权限。 解：授权语句如下：

mysql> GRANT ALTER ON jxgl.* TO 'def'@'localhost'; 进入test数据库，显示授权信息： Mysql> SHOW GRANTS FOR def@localhost;

例10-8 将jxgl数据库的删除表结构权限赋给def用户，并显示所授权限。 解：mysql> USE jxgl; Database changed

mysql> GRANT DROP ON * TO 'def'@'localhost'; mysql> SHOW GRANTS FOR def@localhost;

例10-9 将jxgl数据库的创建表权限赋给def和abc用户，并显示所授权限。 解：mysql> grant create on jxgl.* to 'abc'@'localhost','def'@'localhost'; mysql> SHOW GRANTS FOR def@localhost; mysql> SHOW GRANTS FOR abc@localhost;

例10-10 把在jxgl数据库的sc表上建立索引权限授权给abc用户。 解：mysql> GRANT INDEX ON jxgl.sc TO 'abc'@'localhost'; 例10-11 把在student表的sno和sname的选择权限赋给abc用户。

解：mysql> GRANT SELECT(sno,sname) ON jxgl.student TO 'abc'@'localhost' ; mysql> SHOW GRANTS FOR 'abc'@'localhost';

例10-12 把在jxgl数据库执行存储过程权限赋给abc用户。 解：mysql> GRANT EXECUTE ON jxgl.* to 'abc'@'localhost'; mysql> grant all on test.t2 to 'abc'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on perf.* to 'abc';

14

Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'abc';

实验11 数据库完整性

例如：多列CHECK约束可以用来约束性别与年龄的关系，命令如下： Create Table Student ( Sno CHAR(7) NOT NULL ,

Sname VARCHAR(16),

Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'), Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Sdept CHAR(2), PRIMARY KEY(Sno),

CONSTRAINT CHK_SEX_AGE CHECK(SSEX='男' AND SAGE<=50 OR (SSEX='女' AND

SAGE<=45))) ENGINE=InnoDB;

例如，在教学管理系统（jxgl）中建立一个存储过程，该存储过程先对参数做正确性判定（要求成绩大于等于0，成绩小于等于100，并且学号和课程号都为数字编号）才实现对sc表的插入操作。

DELIMITER $$

DROP FUNCTION IF EXISTS `IsNum` $$

CREATE FUNCTION `IsNum` (str VARCHAR(25))

RETURNS INT -- 先创建一个判断数字的函数IsNum

BEGIN

DECLARE iResult INT DEFAULT 0;

IF ISNULL(str) THEN return 0; END IF; --NULL字符串 IF str = '' THEN return 0; END IF; --空字符串 SELECT str REGEXP '^[0-9]*$' INTO iResult; IF iResult = 1 THEN

RETURN 1; ELSE

RETURN 0; END IF; END $$ DELIMITER //

Create procedure insert_to_sc(isno char(7),icno char(1),igrade int) Begin

15

If (igrade>=0 or igrade<=100 ) and (IsNum(isno)=1) and (IsNum(icno)=1) then

Insert into sc(sno,cno,grade) values(isno,icno,igrade); end if; End;//

请类似如下调用insert_to_sc来实现不同记录值的插入，看是否能实现插入操作？ Call insert_to_sc('2011001', '1',78); Call insert_to_sc('20110o1', '1',85); Call insert_to_sc('2011001', 'a',90); Call insert_to_sc('2011001', '2',120); ???

实验12 数据库并发控制

例12-1 事务示例。 解：mysql> delimiter // mysql> START TRANSACTION;

->SELECT @A:=SUM(grade) FROM sc WHERE sno='2005001'; ->UPDATE sc SET grade=40 WHERE sno='2005001' and cno='1'; ->COMMIT;//

例12-2 带保存点的事务示例。

解：mysql>select * from sc where sno= '2005001' and (cno='1' or cno='2');

mysql> delimiter // mysql> START TRANSACTION;

UPDATE sc SET grade = 100 WHERE sno = '2005001' and cno='1'; SAVEPOINT c1_sal;

UPDATE sc SET grade = 20 WHERE sno = '2005001' and cno='2'; SAVEPOINT c2_sal;

SELECT SUM(grade) FROM sc where sno= '2005001'; ROLLBACK ;

UPDATE sc SET grade = 80 WHERE sno = '2005001' and cno='2'; ROLLBACK TO SAVEPOINT c2_sal; COMMIT;//

mysql> delimiter ;

mysql>select * from sc where sno= '2005001' and (cno='1' or cno='2');

CREATE DEFINER=`root`@`localhost` PROCEDURE `modi_a`()

16

BEGIN

Set @i=200; WHILE @i > 0 DO SET @i = @i - 1; start transaction; set @grade =NULL;

select @grade:=grade from sc where sno='2005001' and cno='1'; update sc set grade=@grade + 1 where sno = '2005001' and cno='1'; select * from sc where sno='2005001' and cno='1'; commit; END WHILE; END

CREATE DEFINER=`root`@`localhost` PROCEDURE `modi_m`() BEGIN

Set @i=200; WHILE @i > 0 DO SET @i = @i - 1; start transaction; set @grade =NULL;

select @grade:=grade from sc where sno='2005001' and cno='1'; update sc set grade=@grade-1 where sno = '2005001' and cno='1'; select * from sc where sno='2005001' and cno='1'; commit; END WHILE; END

CREATE DEFINER=`root`@`localhost` PROCEDURE `modi_a2`() BEGIN

Set @i=200; WHILE @i > 0 DO SET @i = @i - 1; start transaction; set @grade =NULL;

select @grade:=grade from sc where sno='2005001' and cno='1' for update; update sc set grade=@grade + 1 where sno = '2005001' and cno='1'; select * from sc where sno='2005001' and cno='1'; commit;

17

END WHILE; END

CREATE DEFINER=`root`@`localhost` PROCEDURE `modi_m2`() BEGIN

Set @i=200; WHILE @i > 0 DO SET @i = @i - 1; start transaction; set @grade =NULL;

select @grade:=grade from sc where sno='2005001' and cno='1' for update; update sc set grade=@grade-1 where sno = '2005001' and cno='1'; select * from sc where sno='2005001' and cno='1'; commit; END WHILE; END

例12-3 创建表并举例说明死锁。 解：创建表：

CREATE TABLE parent(id INT NOT NULL,PRIMARY KEY (id)) TYPE=INNODB;

CREATE TABLE child(id INT, parent_id INT,INDEX par_ind (parent_id),FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB;

要添加一些记录，命令如：

INSERT INTO parent values(1); INSERT INTO parent values(2); INSERT INTO child values(1,1); INSERT INTO child values(1,2); 如错误！未找到引用源。所示的就是一个发生死锁的例子。

实验13 数据库备份与恢复

二、Mysql日志的关闭与开启 1.使用以下命令查看是否启用了日志 mysql> show variables like 'log_%';

使用SQL语句也可查看mysql创建的二进制的文件目录： mysql> show master logs; 查看当前二进制文件状态： mysql> show master status;

例13-1 假定表student具有一个PRIMARY KEY或UNIQUE索引，备份一个数据表的过

18

程。

1）锁定数据表，避免在备份过程中，表被更新 mysql>LOCK TABLES student READ; 2）导出数据

mysql>SELECT * INTO OUTFILE 'student.bak ' FROM student; 3）解锁表

mysql>UNLOCK TABLES;

例13-2 相应的恢复备份的数据的过程如下： 1）为表增加一个写锁定：

mysql>LOCK TABLES student WRITE; 2）恢复数据

mysql>LOAD DATA INFILE 'student.bak'

->REPLACE INTO TABLE student;

如果，指定一个LOW_PRIORITY关键字，就不必如上要对表锁定，因为数据的导入将被推迟到没有客户读表为止：

mysql>LOAD DATA LOW_PRIORITY INFILE 'student.bak'

->REPLACE INTO TABLE student;

操作中若因汉字问题出现恢复异常现象，可以执行如下把表默认的字符集和所有字符列（CHAR, VARCHAR, TEXT）改为新的字符集的语句：ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;举例如下：

ALTER TABLE student CONVERT TO CHARACTER SET gbk; 3）解锁表

mysql>UNLOCK TABLES;

一、备份

选择在系统空闲时，比如在夜间，使用mysqldump备份数据库。 例13-3 完整备份教学管理系统（jxgl）。 C:\\>mysqldump -u root -p*** jxgl > jxgl.sql 二、恢复

停掉应用，执行mysql导入备份文件。 例13-4 恢复教学管理系统（jxgl）。 C:\\>mysql -u root -p*** jxgl

一、备份

例13-5 对教学管理系统（jxgl）进行差异备份。

1）选择在系统空闲时，使用mysqldump -F(flush-logs)备份数据库。 C:\\>mysqldump -u root -p*** jxgl -F > jxglf.sql 2）备份mysqldump开始以后生成的binlog。

19

二、恢复

例13-6 从差异备份中恢复教学管理系统（jxgl）。 1）停掉应用，执行mysql导入备份文件。 C:\\>mysql -u root -p*** jxgl < jxglf.sql

2）使用mysqlbinlog 恢复自mysqldump 备份以来的binlog。

C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data>mysqlbinlog jxgl.000001 | mysql -u root -h localhost -p***

例13-7 如果上午10点发生了误操作，用备份和binglog将数据恢复到故障前。 1）C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data>mysqlbinlog --stop-date=\

mysql -u root -hlocalhost -p****

2）跳过故障时的时间点，继续执行后面的binlog，完成恢复。

C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data>mysqlbinlog --stop-date=\

mysql -u root -hlocalhost -p***

例13-8 进行位置恢复。

和时间点恢复类似，但是更精确，步骤如下：

C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data>mysqlbinlog jxgl.000001 >jxgl_temp.sql

该命令将在data目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别是368312 和368315。恢复了以前的备份文件后，应从命令行输入下面内容：

C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data>mysqlbinlog --stop-position=\jxgl.000001| mysql -u root -hlocalhost -p****

C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data>mysqlbinlog --start-position=\jxgl.000001| mysql -u root -hlocalhost -p****

实验14 数据库应用系统设计与开发

CREATE DATABASE KCGL; USE KCGL;

CREATE TABLE weborders(

ID int AUTO_INCREMENT PRIMARY KEY, userid int NOT NULL,

20

orderid varchar(20) NOT NULL, ordertime varchar(20) NOT NULL, summoney varchar(20) NULL, paymenttype varchar(50) NULL, validate bit DEFAULT 0, address varchar(50) NULL, email varchar(20) NULL, bz varchar(500) NULL

) AUTO_INCREMENT = 1 ENGINE = InnoDB;21