MySQL数据库技术及实验指导★★★(实验参考).doc

MySQL数据库技术与实验指导（第1版）钱雪忠、王燕玲、张平 编著清华大学出版社2011.09实验1 数据库系统基础操作进入MySQL的官方下载页面：/downloads/如果想找旧的发布版本，可进入页面：/archives.php例11 有一个脚本文件（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数据库基础操作例21 创建jxgl数据库。解：mysqlcreate database jxgl;例22 查看本机服务器上数据库。解：mysqlshow databases;或：mysqlshow databases like my%;例23 进入jxgl数据库。 解：mysql USE jxgl;例24 在命令行环境中，创建和删除数据库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 , 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;例31 列出jxgl数据库中所有表。解：mysql use jxgl;mysql show tables;或：C:mysqlshow -h localhost -u root -p jxgl例32 列出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例33 列出jxgl数据库中表的详细信息。解：mysql use jxgl;mysql show table status;或：C:mysqlshow -status -h localhost -u root -p jxgl例34 列出jxgl数据库中表sc的索引。解：mysql use jxgl;mysql show index from sc;或：mysql show index from jxgl.sc;例35使用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 Sageuse jxgl -先选择jxgl数据库为当前数据库 Database changedmysql create view v(sname,cname, grade) as select sname,cname,grade from student,course,sc - where student.sno=sc.sno and o=o;例311 显示数据库jxgl中视图v创建的信息。解：mysql SHOW CREATE VIEW v;实验4 SQL语言SELECT查询操作例41 查询考试成绩大于等于90的学生学号。解：SELECT DISTINCT SNOFROM SCWHERE GRADE=90;例42 查年龄大于18，并且不是信息系（IS）与数学系（MA）的学生姓名和性别。解：SELECT SNAME, SSEXFROM STUDENT WHERE SAGE18 AND SDEPT NOT IN (IS, MA);例43 查以“MIS_”开头，且倒数第二个汉字为“导”字的课程的详细信息。解：SELECT * FROM COURSE WHERE CNAME LIKE MIS#_%导_ ESCAPE #;例44 查询选修计算机系（CS）选修了2门及以上课程的学生学号。解：SELECT STUDENT.SNOFROM STUDENT, SCWHERE SDEPT=CS AND STUDENT.SNO=SC.SNOGROUP BY STUDENT.SNO HAVING COUNT(*)=2;例45 查询student表与sc表的广义笛卡尔积。解：SELECT STUDENT.*, SC.*FROM STUDENT CROSS JOIN SC;例46 查询student表与sc表基于学号sno的等值连接。解：SELECT * FROM STUDENT, SC WHERE STUDENT.SNO=SC.SNO;例47 查询student表与sc表基于学号sno的自然连接。解：SELECT STUDENT.*, SC.CNO, SC.GRADEFROM STUDENT, SCWHERE STUDENT.SNO=SC.SNO;例48 查询课程号的间接先修课程号。解：SELECT FIRST.CNO, SECOND.CNOFROM COURSE FIRST, COURSE SECONDWHERE FIRST.CPNO=SECOND.CNO;例49 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。解：SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT, CNO, GRADEFROM STUDENT LEFT OUTER JOIN SC ON STUDENT.SNO=SC.SNO;例410 查询学生及其课程成绩与课程及其学生选修成绩的明细情况（要求学生与课程均全部列出）。解：SELECT STUDENT.SNO, SNAME, SSEX, SAGE, SDEPT, COURSE.CNO, GRADE, CNAME, CPNO, 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, b.GRADE, c.CNAME, c.CPNO, 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;例411 查询性别为男、课程成绩及格的学生信息及课程号、成绩。解：SELECT STUDENT.* , CNO, GRADEFROM STUDENT INNER JOIN SC ON STUDENT.SNO=SC.SNOWHERE SSEX=男 AND GRADE=60;例412 查询与“钱横”在同一系学习的学生信息。解：SELECT * FROM STUDENTWHERE SDEPT IN (SELECT SDEPT FROM STUDENT WHERE SNAME=钱横)；例413 找出同系、同年龄、同性别的学生。解：SELECT T.* FROM STUDENT AS TWHERE (T.sdept, T.SAGE, T.SSEX) IN( SELECT SDEPT, SAGE, SSEXFROM STUDENT AS SWHERE S.SNOT.SNO);例414 查询选修了课程名为“数据库系统”的学生学号，姓名和所在系。解：SELECT SNO, SNAME, SDEPT FROM STUDENT WHERE 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; 例415 检索至少不学2和4课程的学生学号和姓名。解：SELECT SNO, SNAME FROM STUDENTWHERE SNO NOT IN (SELECT SNO FROM SC WHERE CNO IN (2, 4);例416 查询其他系中比信息系IS所有学生年龄均大的学生名单，并排序输出。解：SELECT SNAME FROM STUDENTWHERE SAGEALL(SELECT SAGE FROM STUDENT WHERE SDEPT=IS) AND SDEPTISORDER BY SNAME;例417 查询选修了全部课程的学生姓名（为了有查询结果，自己可以调整表的内容）。解：SELECT SNAME FROM STUDENTWHERE NOT EXISTS( SELECT * FROM COURSEWHERE NOT EXISTS( SELECT * FROM SC WHERE SNO=SC.SNO AND CNO=COURSE.CNO);例418 查询至少选修了学生“2005001”选修的全部课程的学生号码。解：SELECT SNO FROM STUDENT SXWHERE 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);例419 查询平均成绩大于85分的学号，姓名和平均成绩。解：SELECT STUDENT.SNO, SNAME, AVG(GRADE)FROM STUDENT, SCWHERE STUDENT.SNO=SC.SNOGROUP BY STUDENT.SNO, SNAME HAVING AVG(GRADE)85;实验5 SQL语言数据更新操作例51 向jxgl数据库中表student添加数据 (2005007,李涛,男,19,IS)。解：mysql use jxgl;mysql insert into student values (2005007,李涛,男,19,IS);或：mysqlinsert into student set sno=2005007, sname=李涛,ssex=男,sage=19,sdept=IS;例52 向jxgl数据库中表student添加数据(2005008,陈高,女,21,AT),(2005009,张杰,男,17,AT)。解：Mysqlinsert into student values (2005008,陈高,女,21,AT),(2005009,张杰,男,17,AT);例53 在数据库中先创建表：tbl_name1（sn,sex,dept），现从student表把数据转入tb1_name1。解：mysqlcreate table tbl_name1(sn,sex,dept) select sname sn,ssex sex,sdept dept from where 1=2; -先创建表tbl_name1；mysqlinsert into tbl_name1(sn,sex,dept) select sname,ssex,sdept from student;例54 向jxgl数据库中表sc添加数据(2005001,5,80)。解：mysqlreplace sc values (2005001,5,80);注意这些规则意味着一个像“./myfile.txt”给出的文件是从服务器的数据目录读取，而作为“myfile.txt”给出的一个文件是从当前数据库的数据库目录下读取。也要注意，对于下列那些语句，对db1文件从数据库目录读取，而不是db2： mysql USE db1;mysql LOAD DATA INFILE ./data.txt INTO TABLE db2.my_table; 例55 在student表中，我们发现陈高的性别没有指定，因此我们可以这样修改这个记录。解：mysql update student set ssex=女 where sname=陈高;例56 在sc表中，删除陈高选修课程信息。解：mysql delete from sc where sno=(select sno from student where sname=陈高);例57 删除所有学生选课记录解：mysqldelete 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;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) ENGINE = 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-mysqlC:cdesqlc-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 offecho Use SETENV to set up the appropriate environment forecho building Embedded SQL for C programsset path=C:Program FilesMySQLMySQL Server 5.5bin;c:vc98binset INCLUDE=C:Program FilesMySQLMySQL Server 5.1Include;c:VC98Include;%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_ /Zi /od /D_DEBUG %1.c 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,SET NAMES latin1;); /支持处理汉字SET NAMES GBK|Gb2312|utf8|latin1; 可根据具体要求选择不同字符集以支持汉字的显示。实验7 数据库存储和优化例71 多表连接查询分析，及其改进。解：mysqlEXPLAIN SELECT student.sname, ame ,grade From 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,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()beginset i=1;WHILE i delimiter /mysql CREATE PROCEDURE simpleproc(OUT param1 INT)-BEGIN- SELECT COUNT(*) INTO param1 FROM student;-END/Query OK, 0 rows affected (0.00 sec)例82创建带输入参数的存储过程，根据学生学号（sno）查询该学生所学课程的课程编号（cno）和成绩（grade）。解：mysql delimiter /mysqlCREATE PROCEDURE proc_sc_findById(in n int)-BEGIN- SELECT sno,cno,grade FROM sc where sno=n;-END/例83 删除例82创建的存储过程。解：mysqldrop PROCEDURE IF EXISTS proc_sc_findById;例84 查看例81创建的存储过程。解：mysqlshow create PROCEDURE simpleproc;例85 查看在jxgl中创建的所有存储过程。解：mysqlshow PROCEDURE status;例86 调用在例81中创建的simpleproc存储过程（带输出参数）。解：mysqlcall simpleproc(count);实验9 触发器的基本操作例如，下述语句将创建1个表和1个INSERT触发程序。触发程序将插入表中某一列的值加在一起：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;例91在表sc上定义1个UPDATE触发程序，用于检查更新每一行时，grade位于0100的范围内，否则回退。解：mysql delimiter /mysql CREATE TRIGGER upd_check BEFORE UPDATE ON sc-FOR EACH ROW-BEGIN- IF NEW.grade 100 THEN- Set NEW.grade=OLD.grade;- END IF;-END;/mysql delimiter ;调用触发器：Mysqlupdate sc set grade=110 where sno=2005001 and cno=1;实验10 数据库安全性例101 在MySQL数据库中新建用户“dba”，密码为：“sqlstudy”。解：mysqlCREATE USER dba IDENTIFIED BY sqlstudy; 例102把用户dba改名为hello。mysqlrename user dba to hello;例103把用户hello的密码改为1234。mysqlset password for hello = password(1234);例104删除 MySQL 数据库用户hello，也最好显式指定 hostname。mysqldrop user hello;等价于：drop user hello%例105 显示一个用户admin的权限：mysqlSHOW GRANTS FOR adminlocalhost;其显示结果为当时创建该用户的GRANT授权语句：GRANT RELOAD, SHUTDOWN, PROCESS ON *.* TO adminlocalhost IDENTIFIED BY PASSWORD 28e89ebc62d6e19a上面命令中密码是加密后的形式。例106 先把数据库jxgl的所有权限授予给用户kitelocalhost，接着再把权限从用户kitelocalhost处收回。解：授权：mysqlGRANT ALL ON jxgl.* TO kitelocalhost IDENTIFIED BY ruby;删除数据库授权：mysqlREVOKE ALL ON jxgl.* FROM kitelocalhost;但是，kitelocalhost用户仍旧留在user表中，可以查看：mysqlSELECT * FROM mysql.user;例107 将jxgl数据库的变更权限赋给def用户，并显示所授权限。解：授权语句如下：mysql GRANT ALTER ON jxgl.* TO deflocalhost;进入test数据库，显示授权信息：Mysql SHOW GRANTS FOR deflocalhost;例108 将jxgl数据库的删除表结构权限赋给def用户，并显示所授权限。解：mysql USE jxgl;Database changedmysql GRANT DROP ON * TO deflocalhost;mysql SHOW GRANTS FOR deflocalhost;例109 将jxgl数据库的创建表权限赋给def和abc用户，并显示所授权限。解：mysql grant create on jxgl.* to abclocalhost,deflocalhost;mysql SHOW GRANTS FOR deflocalhost;mysql SHOW GRANTS FOR abclocalhost;例1010 把在jxgl数据库的sc表上建立索引权限授权给abc用户。解：mysql GRANT INDEX ON jxgl.sc TO abclocalhost;例1011 把在student表的sno和sname的选择权限赋给abc用户。解：mysql GRANT SELECT(sno,sname) ON jxgl.student TO abclocalhost ;mysql SHOW GRANTS FOR abclocalhost;例1012 把在jxgl数据库执行存储过程权限赋给abc用户。解：mysql GRANT EXECUTE ON jxgl.* to abclocalhost;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 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=0 or igrade 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;/例122 带保存点的事务示例。解：mysqlselect * 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 ;mysqlselect * from sc where sno= 2005001 and (cno=1 or cno=2);CREATE DEFINER=rootlocalhost PROCEDURE modi_a()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; ENDCREATE DEFINER=rootlocalhost 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