MySQL数据库技术及试验指导★★★试验参考

1、MySQL 数据库技术与实验指导（第 1 版）钱 雪忠 、王燕 玲、张 平 编著清华大 学出 版社2011.0921实验 1 数据库系统基础操作进 入 MySQL 的 官 方 下 载 页 面 ： 如果想找 旧的发 布版本，可进 入页 面： 例 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 &l

2、t;c:test.sql或 mysql> 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 在 命 令 行 环 境 中 ， 创 建 和 删 除 数 据 库

3、 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 ,'eno' CHAR(2) NOT NULL ,'grade' INT NULL ,PRIMARY

4、 KEY ('sno','eno'),INDEX'se_ibfk_1'('sno'ASC),INDEX'se_ibfk_2'('eno'ASC) , CONSTRAINT 'se_ibfk_1' FOREIGN KEY('sno') REFERENCES'jxgl'. 'student'('sno') ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT 'se_ibf

5、k_2' FOREIGN KEY('eno') REFERENCES 'jxgl'.'eourse'('eno')ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB;例 3-1 列出 jxgl 数据库 中所 有表。解 ： mysql> use jxgl;mysql> show tables;或 ： C:>mysqlshow -h loealhost -u root -p jxgl例 3-2 列 出 jxgl 数 据 库 中 表 student 的

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

7、3-4 列出 jxgl 数据库 中表 se 的索引。解 ： mysql> use jxgl;mysql> show index from se;或 ： mysql> show index from jxgl.se;例3-5使用SQL语句创建示例数据库(jxgl ):其中，学生表要求学号为主键，性别默 认为男，取值必须为男或女，年龄取值在 15 到 45之间。课程表( eourse )要 求主键为课程编号，外 键为先修课号，参照课程表的主键( eno)。 选修表(se )要求主键为(学号，课程编号)，学号为外键，参照学生表中的学号，课 程编号为外键， 参照课程表中的课程编号；成

8、绩不为空时必须在 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;Create Table COURSE( Cno CHAR(2) NOT NULL

9、 ,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) referen

10、ces 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) 。解 ： alt

11、er table student add unique sn(sname);例 3-9 删 除 表 sc 。解 ： DROP TABLE sc;例3-10 在数据库jxgl中创建视图v ,查询学生姓名，课程名及其所学课程的成绩。 解 ： mysql>use jxgl -先 选 择 jxgl 数 据 库 为 当 前 数 据 库Database changedmysql> create view v(sname,cname, grade) as select sname,cname,grade from student,course,sc-> where student.sno=

12、sc.sno and o=o;例 3-11 显 示 数 据 库 jxgl 中 视 图 v 创 建 的 信 息 。解 ：mysql> SHOW CREATE VIEW v;实验4 SQL语言 SELECT查询操作例 4-1 查 询 考 试 成 绩 大 于 等 于 90 的 学 生 学 号 。 解 ： SELECT DISTINCT SNOFROM SCWHERE GRADE>=90;例 4-2 查 年 龄 大 于 18 ， 并 且 不 是 信 息 系 （ IS ） 与 数 学 系 （ MA） 的 学 生 姓 名 和 性 别 。 解 ： SELECT SNAME, SSEXFROM S

13、TUDENT 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.SNOFR

14、OM STUDENT, SCWHERE SDEPT='CS' AND STUDENT.SNO=SC.SNOGROUP 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.

15、SNO;例 4-7 查 询 student 表 与 sc 表 基 于 学 号 sno 的 自 然 连 接 。解 ： SELECT STUDENT.*, SC.CNO, SC.GRADEFROM STUDENT, SCWHERE STUDENT.SNO=SC.SNO;例 4-8 查 询 课 程 号 的 间 接 先 修 课 程 号 。解 ： SELECT FIRST.CNO, SECOND.CNOFROM COURSE FIRST, COURSE SECONDWHERE FIRST.CPNO=SECOND.CNO;例 4-9 查 询 学 生 及 其 课 程 、 成 绩 等 情 况 （ 不 管 是

16、否 选 课 ， 均 需 列 出 学 生 信 息 ）。解 ： SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT, CNO, GRADEFROM STUDENT LEFT OUTER JOIN SC ON STUDENT.SNO=SC.SNO;例 4-10 查 询 学 生 及 其 课 程 成 绩 与 课 程 及 其 学 生 选 修 成 绩 的 明 细 情 况 （ 要 求 学 生 与 课 程均全 部列出）。解 ： SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT,COURSE.CNO, GRADE, CNAME, CPN

17、O, CCREDIT FROM STUDENT LEFT OUTER JOIN SCON 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

18、, b.GRADE, c.CNAME, c.CPNO,c.CREDIT FROM STUDENT a LEFT OUTER JOIN SC b ON a.SNO=b.SNOLEFT OUTER JOIN COURSEc ON b.CNO=C.CNOUNIONSELECT a2.SNO, a2.SNAME, a2.SSEX, a2.SAGE, a2.SDEPT, c2.CNO, b2.GRADE, c2.CNAME, c2.CPNO, c2.CREDITFROM STUDENT a2 LEFT OUTER JOIN SC b2 ON a2.SNO=b2.SNORIGHT OUTER JOINCO

19、URSE c2 ON b2.CNO=C2.CNO;例 4-11 查 询 性 别 为 男 、 课 程 成 绩 及 格 的 学 生 信 息 及 课 程 号 、 成 绩 。解 ： SELECT STUDENT.* , CNO, GRADEFROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNOWHERE SSEX=' 男 ' AND GRADE>=60;例 4-12 查 询 与 “ 钱 横 ” 在 同 一 系 学 习 的 学 生 信 息 。解 ： SELECT * FROM STUDENTWHERE SDEPT IN (SELECT SD

20、EPT FROM STUDENT WHERE SNAME=' 钱 横 ') ；例 4-13 找 出 同 系 、 同 年 龄 、 同 性 别 的 学 生 。解 ： SELECT T.* FROM STUDENT AS TWHERE (T.sdept, T.SAGE, T.SSEX) IN( SELECT SDEPT, SAGE, SSEXFROM STUDENT AS SWHERE S.SNO<>T.SNO);例 4-14 查 询 选 修 了 课 程 名 为 “ 数 据 库 系 统 ” 的 学 生 学 号 ， 姓 名 和 所 在 系 。 解：SELECT SNO, S

21、NAME, SDEPT FROM STUDENTWHERE SNO IN( SELECT SNO FROM SCWHERE CNO IN (SELECT CNO FROM COURSE WHERE CNAME= ' 数 据 库 系 统 '); 或SELECT STUDENT.SNO, SNAME, SDEPTFROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNOINNER JOIN COURSE ON SC.CNO=COURSE.CNO;例 4-15 检 索 至 少 不 学 2 和 4 课 程 的 学 生 学 号 和 姓 名 。 解：S

22、ELECT SNO, SNAME FROM STUDENTWHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO IN ('2', '4');例 4-16 查 询 其 他 系 中 比 信 息 系 IS 所 有 学 生 年 龄 均 大 的 学 生 名 单 ， 并 排 序 输 出 。 解：SELECT SNAME FROM STUDENTWHERE SAGE>ALL(SELECT SAGE FROM STUDENT WHERE SDEPT= 'IS') AND SDEPT<>'IS

23、9;ORDER BY SNAME;例 4-17 查 询 选 修 了 全 部 课 程 的 学 生 姓 名( 为 了 有 查 询 结 果 ，自 己 可 以 调 整 表 的 内 容 )。 解：SELECT SNAME FROM STUDENT WHERE NOT EXISTS( SELECT * FROM COURSEWHERE NOT EXISTS( SELECT * FROM SC WHERE SNO=SC.SNO AND CNO=COURSE.CNO); 例 4-18 查 询 至 少 选 修 了 学 生 “ 2005001 ” 选 修 的 全 部 课 程 的 学 生 号 码 。 解：SELEC

24、T SNO FROM STUDENT SX WHERE NOT EXISTS ( SELECT * FROM SC SCYWHERE SCY.SNO='2005001' AND NOT EXISTS( SELECT * FROM SC SCZWHERE SCZ.SNO=SX.SNO AND SCZ.CNO=SCY.CNO);例 4-19 查 询 平 均 成 绩 大 于 85 分 的 学 号 ， 姓 名 和 平 均 成 绩 。 解：SELECT STUDENT.SNO, SNAME, AVG(GRADE) FROM STUDENT, SCWHERE STUDENT.SNO=SC.

25、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&#

26、39;);或 ： mysql>insert into student set sno='2005007',>sname=' 李 涛 ',ssex='男 ',sage=19,sdept='IS'例 5-2 向 jxgl数据库中 表 student添 加 数 据 ('2005008','陈 高 ','女',21,'AT'),('2005009','张 杰 ','男 ',17,'AT')。解 ： M

27、ysql>insertintostudentvalues ('2005008',' 陈高 ','女',21,'AT'),('2005009','张 杰 ','男 ',17,'AT');例 5-3 在 数 据 库 中 先 创 建 表： tbl_name1 (sn,sex,dept )， 现 从 student表把数据转入 tb1_name1 。解：mysql>createtable tbl_name1(sn,sex,dept)select sname sn,

28、ssex sex,sdeptdeptfrom where 1=2; -先创 建 表 tbl_name1 ；mysql>insertinto tbl_name1(sn,sex,dept)select sname,ssex,sdeptfromstudent;例 5-4 向 jxgl数据库中 表 sc 添 加数据('2005001','5',80) 。解 ： mysql>replace sc values ('2005001','5',80);注意这些 规则意味着一 个像“ ./myfile.txt ”给出的 文件是从服务

29、器的 数据目录读取 ， 而作为“ myfile.txt ”给出 的一个文件 是从当前数据 库的数 据库目录下读 取。 也要注意， 对于下列那些 语句，对 db1 文件从数据 库目录读取， 而不是 db2：mysql> USE db1;mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;例 5-5 在 student 表 中 ，我 们 发 现 陈 高 的 性 别 没 有 指 定 ，因 此 我 们 可 以 这 样 修 改 这 个 记录。解 ： mysql> update student se

30、t 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 、 sag

31、e 、 sdept)2) course(cno3) sc(sno 、 cname 、 cpno 、 ccredit) 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;-MyIS

32、AM/InnoDB选 其 一key(cno) references course(cno)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),forei gn ENGINE = MyISAM/InnoDB;CREATE TABLE course (cno char(1) NOT null primary key,cname char(10) null ,cpno char(1)

33、 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') ENGINE = MyISAM/InnoDB;三 、 MS-DOS 窗 口 中 编 译 、 连 接 与 运 行利 用 VC+6.0 C 编 译 器 直 接 在 MS-DOS 窗 口 中 编 译 、连 接 与 运 行 ，也

34、是 简 单 便 捷 的 方 法 。 设 VC+6.0 C 编 译 器 相 关 文 件 ( 如 BIN 含 可 执 行 程 序 ， INCLUDE 含 头 文 件 ， LIB 含 库 文 件 )放 在 C:VC98 目 录 中 。可 以 把 C 语 言 源 程 序( 如 CC.C )放 在 某 目 录 中 如 C:esqlc-mysql 。(1) 启 动 “ MS-DOS” 窗 口 ， 执 行 如 下 命 令 ， 使 当 前 盘 为 C， 当 前 目 录 为 esqlc-mysqlC:cdesqlc-mysql(2) 设置 系统环境变量 值，执行如 下批处理命令 ：setenv-mysql(3)

35、 编译、连接嵌入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-m

36、ysql.bat文 件内容(根据VC+6.0 安装目录及MySQL安装目录需 做相应修改的) ：echo offecho Use SETENV to set up the appropriate environment forecho building Embedded SQL for C programsset path="C:Program FilesMySQLMySQL Server 5.5bin""c:vc98bin"setINCLUDE=C:ProgramFilesMySQLMySQLServer5.1Include;c:VC98Include

37、;%include%set LIB="C:Program FilesMySQLMySQL Server 5.5libdebug"c:VC98Lib;%lib%(e) 嵌入SQL的C语言程序编译环境要求(即SETENV-mysql.BAT 文件内容):需VC安 装目录下的bin 、include 、lib 子目录；MySQL安装 后子目录binn、include、libdebug 等。 为此 SETENV-mysql.BAT 文件目 录情况应按照实际目 录情况调整。(f) run-mysql.bat文 件 内 容 为 ：cl /c /W3 /D"_x86_"

38、; /Zi /od /D"_DEBUG" %1.clink /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.

39、dll 、 libmysql.dll 等(需 要时复制它们 到编译 、运行环境中 去) 。要说明的是：解决汉子显示问题，C源程序中如下命令相关的：mysql_query(&mysql,"SETNAMES latin1;");/ 支 持 处 理 汉 字 SET NAMESGBK|Gb2312|utf8|latin1;可 根 据 具 体 要 求 选 择 不 同 字 符 集 以 支 持 汉 字 的 显 示 。实验 7 数据库存储和优化例 7-1 多 表 连 接 查 询 分 析 ， 及 其 改 进 。ame,grade From解 ： mysql>EXPLAIN SE

40、LECT student.sname,student,course,sc WHERE student.sno=sc.sno and o=o and sdept='cs'在教学管理系统(jxgl )中，创建表test ，并插入8万条记录。在mysql命令行提示 符下录入如下程序并运行之。/* 创 建 表 */Create table test(id int unique AUTO_INCREMENT,rg datetime null,srq varchar(20) null,hhsmallintnull,mm smallintnull,ss smallintnull,numnum

41、eric(12,3),primary key(id) AUTO_INCREMENT = 1 engine = MyISAM; /* 创 建 存 储 过 程 生 成 表 中 数 据 */DELIMITER /CREATE PROCEDURE、p1'()beginset i=1;WHILE i<=80000 doINSERT 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/* 调用存储过

42、程*/call p1/DELIMITER ;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 *

43、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）， 查询命令同“4、查询所有记录，按mm排序（约150ms ）,查询命令同“5、单纪录查询（约 0ms）， 查询命令同 “单纪录查询”。6、删 除

44、索引 （ 约 870ms）。Drop index indexname1 on test;三、对test表mm字段建立非聚集索引。1 、 建 索 引 耗 时 （ 约 1016ms ）。Create index indexname1 on test（mm）;2、单记录插入（约 0ms）， 插入命令同“单记录插入”。3、查询所有记录， 按 id 排序（约 160ms）， 查询命令同“4、查询所有记录，按mm排序（约150ms），查询命令同“5、单纪录查询（约 0ms）， 查询命令同 “单纪录查询”。6、删除索引（约 953ms）。Drop index indexname1 on test;四 、 对

45、 test 表 id 字 段 建 立 唯 一 索 引 。1、建立索引耗时（约 1125ms）。Create UNIQUE index indexname1 on test（id）;2、单记录插入（约 10ms）， 插入命令同“单记录插入”。3、查询所有记录， 按 id 排序（约 156ms）， 查询命令同“的， 只做 参考）。查 询 按 id 排 序 查询按mm排序查 询 按 id 排 序 查询按mm排序查 询 按 id 排 序 查询按mm排序4、查询所有记录，按mm排序（约156ms），查询命令同“5、单纪录查询（约Oms）,查询命令同“单纪录查询”。6、删 除索引 （ 约 968ms ）。

46、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, O rows affected （O.OO sec）例 8-2 创 建带输入参数 的存储 过程 ,根 据学生学号（ s

47、no ）查 询该学生 所学 课程的课程 编号（ 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 创 建 的 存 储 过 程

48、。解 ： mysql>show create PROCEDURE simpleproc;例 8-5 查 看 在 jxgl 中 创 建 的 所 有 存 储 过 程 。解 ： mysql>show PROCEDURE status;例 8-6 调 用 在例 8-1 中 创 建 的 simpleproc 存 储 过 程 （ 带 输 出 参 数 ）。解 ： mysql>call simpleproc（count）;实验 9 触发器的基本操作例 如 ，下 述 语 句 将 创 建 1 个 表 和 1 个 INSERT 触 发 程 序 。触 发 程 序 将 插 入 表 中 某 一 列 的

49、值加在一起：CREATE TABLE account (acct_num INT, amount DECIMAL(10,2);CREATE TRIGGER ins_sum BEFORE INSERT ON accountFOR 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

50、> delimiter /mysql> CREATE TRIGGER upd_check BEFORE UPDATE ONsc->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=

51、9;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

52、删 除 MySQL 数 据 库 用 户 hello ， 也 最 好 显 式 指 定 hostname mysql>drop user hello;等 价 于： drop user hello'%'例 10-5 显 示 一 个 用 户 admin 的 权 限 ： mysql>SHOW GRANTS FOR adminlocalhost;其显示结果为当时创建该用户的GRANT授权语句：GRANT RELOAD, SHUTDOWN, PROCESS ON *.* TO 'admin''localhost' IDENTIFIED BY PAS

53、SWORD '28e89ebc62d6e19a'上 面 命令 中密码 是 加密 后的 形 式。例 10-6 先 把 数 据 库 jxgl 的 所 有 权 限 授 予 给 用 户 kitelocalhost ，接 着 再 把 权 限 从 用 户 kitelocalhost 处 收 回 。解：授 权 ： mysql>GRANT ALL ON jxgl.* TO kitelocalhost IDENTIFIED BY "ruby"删 除 数 据 库 授 权 ： mysql>REVOKE ALL ON jxgl.* FROM kitelocalhost;

54、但 是 ， kitelocalhost 用 户 仍 旧 留 在 user 表 中 ， 可 以 查 看 ： mysql>SELECT * FROM mysql.user;例 10-7 将 jxgl 数 据 库 的 变 更 权 限 赋 给 def 用 户 ， 并 显 示 所 授 权 限 。解 ： 授权 语句 如 下 ：mysql> GRANT ALTER ON jxgl.* TO 'def''localhost'进 入 test 数 据 库 ， 显 示 授 权 信 息 ： Mysql> SHOW GRANTS FOR deflocalhost;例

55、10-8 将 jxgl 数 据 库 的 删 除 表 结 构 权 限 赋 给 def 用 户 ， 并 显 示 所 授 权 限 。 解 ： mysql> USE jxgl;Database changedmysql> GRANT DROP ON * TO 'def''localhost' mysql> SHOW GRANTS FOR deflocalhost;例 10-9 将 jxgl 数 据 库 的 创 建 表 权 限 赋 给 def 和 abc 用 户 ， 并 显 示 所 授 权 限 。解 ： mysql> grant create on

56、 jxgl.* to 'abc''localhost','def''localhost' mysql> SHOW GRANTS FOR deflocalhost; mysql> SHOW GRANTS FOR abclocalhost;例 10-10 把 在 jxgl 数 据 库 的 sc 表 上 建 立 索 引 权 限 授 权 给 abc 用 户 。解 ： mysql> GRANT INDEX ON jxgl.sc TO 'abc''localhost'例 10-11 把 在 s

57、tudent 表 的 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

58、9;'localhost'mysql> grant all on test.t2 to 'abc' Query OK, 0 rows affected (0.00 sec) mysql> grant all on perf.* to 'abc'Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'abc'实验 11 数据库完整性例如：多列CHECK约束可以用来约束性别与年龄的关系，命令如下：Create Table Student ( Sno C

59、HAR(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) EN

60、GINE=InnoDB;例如，在教学管理系统(jxgl )中建立一个存储过程，该存储过程先对参数做正确性 判定( 要求 成 绩 大于 等于 0，成 绩小 于等 于 100 ，并 且学 号和 课程 号都 为 数 字编 号)才 实 现 对 sc 表 的 插 入 操 作 。DELIMITER $DROP FUNCTION IF EXISTS 'IsNum' $CREATE FUNCTION 'IsNum' (str VARCHAR(25)RETURNS INT-先创建一个判断数字的函数 IsNumBEGINDECLARE iResult INT DEFAULT 0;IF ISNULL(str) THEN return 0; END IF; -NULL字符串IF str = '' THEN return 0; END IF; -空字符串SELECT str REGEXP 'A0-9*$' INTO iResult;IF iResult = 1 THENRETURN 1;ELSERETURN 0;END IF;END $DELIMITER /Creat