《MySQL数据库应用》源代码

1、MySQL数据库应用源代码工程一：部署数据库开发环境1.以w i ndows命令行方式登录MySQL服务器： mysql -h 127.0.0.1 -u root -p工程二：数据库设计.使用MySQL命令行工具创立一个“学生成绩管理”数据库，命名为XSCJ,指定字符集 为gb2312,默认校对规那么为gb2312_chinese_ci (简体中文,不区分大小写)。CREATE DATABASE IF NOT EXISTS XSCJDEFAULT CHARACTER SET gb2312DEFAULT COLLATE gb2312_chinese_ci;.在MySQL中创立一个名为XSCJ,d

2、b的用于测试的数据库。 CREATE DATABASE XSCJ_db;.查看当前服务器下有哪些数据库 show databases;.翻开数据库XSCJ_db3 use xscj_db3;.删除数据库XSCJ_db2 Drop database xscj_db2;.结构创立XSQK1表。create table XSQK1(学号 char(10) primary key,姓名 varchar(lO),性别 char(2);或：create table XSQK1(学号 char(lO),姓名 varchar(lO),性别 char(2), constraint primary key(学号)

3、；.创立CJ1表create table CJ1(学号 char(lO),课程号char(3),成绩 tinyint,where A.学号二B.学号and A.课程号B,课程号; 或：select A.学号,A.课程号A成绩B课程号,B.成绩 from CJ A inner join CJ Bon A.学号二B.学号and A.课程号B.课程号；.在学生成绩管理系统中，查看哪些学生选修了课程以及取得的成绩，同时还要查看哪些 学生还没有选修课程。select xsqk.学号，姓名，课程号成绩from xsqk left join cjon xsqk.学号二cj.学号；.在学生成绩管理系统中，要查

4、看学校开设课程以及哪些课程已有学生选修，并查看其学 号，课程号和成绩，同时还要查看哪些课程还没有学生选修，并查看其课程号和课程名。select kc.课程号开设课程号，课程名,学号,cj.课程号已选修课程号，成绩 from cj right join kcon cj.课程号二kc.课程号；.查询不及格学生的学号、姓名、课程号、授课教师和成绩信息。select xsqk.学号，姓名,kc.课程号，授课教师，成绩where xsqk.学号二cj学号and kc.课程号二cj.课程号and成绩60;.查询平均成绩及格学生的学号、姓名。select学号，姓名from xsqkwhere（select

5、avg（成绩）from cj where xsqk.学号二cj.学号）二60;.在KC表中查询课程号、课程名、授课教师、开课学期和学时，要求查询的课程必须已 有学生选修。select课程号，课程名，授课教师，开课学期，学时from kcwhere课程号in（select 课程号 from cj）;.查询CJ表中成绩高于课程号为103的任意一个学生的成绩。select *from cjwhere 成绩,any （select 成绩 from cj where 课程号=103）.查询CJ表中的记录，要求这些记录的成绩高于课程号为103的所有学生的成绩。select *from cjwhere 成绩

6、,all （select 成绩 from cj where 课程号=103）;.在KC表中查询已有学生选修的课程号和课程名。select课程号，课程名from kcwhere exists（select * from cj where kc.课程号二cj.课程号）；.使用XSQK表结合CJ表来查询不及格学生的学号、姓名和专业名。select学号，姓名，性别，专业名from xsqkwhere 学号 in（select 学号 from cj where xsqk.学号二cj.学号 and 成绩60）;.查询选修了两门及以上课程的学生学号和姓名。select学号，姓名from xsqkwhere（

7、select count（课程号）from cj where xsqk.学号二 cj.学号）=2;.在CJ表中查询成绩在75分以上的学生学号、课程号和成绩，并将查询结果保存在新表CJ1 中。create table cjlselect学号课程号成绩from cjwhere 成绩=75;.查询成绩小于60分的学生学号、课程号和成绩，并将查询结果保存到CJ1表中。insert into cjlselect学号课程号成绩from cjwhere 成绩60;.使用UNION合并查询选修了课程号为101和102的学生学号，课程号和成绩。select学号,课程号，成绩from cj where课程号=10

8、1unionselect学号,课程号，成绩from cj where课程号二101;.使用UNION ALL合并查询选修了课程号为“101”和“102”的学生学号。select学号,课程号，成绩from cj where课程号=101union allselect学号,课程号，成绩from cj where课程号=101;工程四：创立数据库对象.在测试数据库XSCJ_db中新建XSQK1表并创立普通索引，相关列为“学号”。 create table xsqkl（学号 char(lO),姓名 varchar(lO),性别 char(2),index index_xh(学号);.在XSCJ_db数

9、据库中的KC_db表的“课程名”上创立索引。create index index_kcmon kc_db(课程名)；.在XSCJ_db数据库中XSQK1表的“姓名”列上创立普通索引。alter table xsqkladd index index_xm(姓名)；.在测试数据库XSCJ_db中新建XSQK2表,创立唯一索引，相关列为“学号”。 create table xsqk2(学号 char(10),姓名 varchar(lO),性别 char(2),unique index index_xh(学号)；.在XSCJ,db数据库中的XSQK,db的姓名列上创立唯一索引。create uniqu

10、e index index_kcmon xsqk_db(姓名);.在XSCJ_db数据库中XSQK2表的“姓名”列上创立唯一索引。alter table xsqk2add unique index index_xm(姓名)；.在测试数据库XSCJ_db中新建XSQK3表,并创立全文索引,相关列为“备注”列。 create table xsqk3(学号 char(10),姓名 varchar(lO),备注 char(lOO),Fulltext index index_bz(备注)；.在备注列上创立全文索引。create fulltext index index_bzon xsqk3(备注);.新

11、建XSQK4表并创立多列索引，索引列为表中的“学号”、“姓名”歹IJ。create table xsqk4(学号int,姓名 char(10),性别 char(2),专业名 varchar（20）,index index_xh_xm（学号，姓名）;.在XSQK4表上创立多列索引，索引列为表中的“学号”“姓名”列。create index index_xh_xmon xsqk4（学号，姓名）;.通过ALTER TABLE语句在XSQK4表上创立多列索引，索引列为表中的“学号”“姓名” 列。alter table xsqk4add key index_xh_xm（学号，姓名）；.查看测试数据库XS

12、CJ-db中XSQK3表上建立的索引。show create table xsqk3G;.查看测试数据库XSCJ-db中的XSQK1表上建立的索引。show index from xsqkl;.使用DROP关键字删除表XSQK3中的“index_bz”索引。drop index index_bz on xsqk3;.使用ALTER关键字删除表XSQK1中的“index_ xmn索引。alter table xsqkl drop index index_xm;.在XSCJ数据库的学生情况表XSQK的“姓名”列上建立普通索引index_xmocreate index index_xmon xsqk

13、（姓名）；.在XSCJ数据库的学生情况表XSQK的“专业名”和“所在学院”列上建立多列索引 index_zym_xyoalter table xsqkadd index index_zym_xy（专业名,所在学院）；.在XSCJ数据库的课程表KC的“课程名”列上建立唯一索引index_kcm0create unique index index_kcmon kc（课程名）;.查看在课程表KC上建立了哪些索引。show index from kc;.以成绩表CJ为基表，创立视图view_cj,要求该视图中隐藏成绩的数值。create view view_cjasselect学号,课程号，学分fro

14、m cj;.创立视图view_xsqk_cj,要求该视图中包含不及格学生的学号、姓名、性别、专业名、 课程号、成绩。create view view_xsqk_cjasselect xsqk.学号，姓名，性别专业名，课程号，成绩from xsqkcjwhere xsqk.学号二cj.学号 and 成绩60;.通过视图view_xsqk_cj来查询不及格学生的学号、姓名、课程号、成绩。select学号，姓名，课程号，成绩from view_xsqk_cj;.使用DESC语句查看视图view_cj的字段信息。desc view_cj;.使用SHOW CREATE VIEW查看视图view_xsqk

15、_cj的定义及字符编码等信息。Show create view view_xsqk_cjG;.修改视图view_xsqk_cj,要求该视图中包含成绩大于80分学生的学号、姓名、性别、 专业名、课程号、成绩。alter view view_xsqk_cjasselect xsqk.学号，姓名，性别专业名，课程号成绩from xsqk.cjwhere xsqk.学号二cj.学号 and 成绩80;.修改视图view_cj,要求该视图中显示成绩列，隐藏学分列。create or replace view view_cjasselect学号课程号成绩from cj;.通过视图v i ew_cj向表CJ

16、添加一条新的记录。insert into view_cj(学号，课程号成绩)values(,2020110401,lll,J69);.通过视图将学号为2020110401,课程号为111的成绩改为73。update view_cjset成绩=73where 学号=2020110401 and 课程号二111;.通过视图删除成绩表CJ中的记录。delete from view_cjwhere 学号二2020110401 and 课程号二111;.由于在学生成绩管理系统的应用中，经常需要查询“学生学号、姓名、课程号、授课教 师、成绩”这几列的信息，因此需要创立一个名为view_xscj的视图，要求

17、该视图中包含选 修了课程号为“101” “102”的“学生学号、姓名、课程号、授课教师、成绩”列以便于查 询使用。create view view_xscjasselect xsqk.学号，姓名,kc.课程号，授课教师，成绩from xsqk,kc,cjwhere xsqk.学号二cj.学号 and kc.课程号二cj.课程号 and kc.课程号 in(1101,1102,);.通过视图view_xscj来查询不及格学生的学号、姓名、课程号和成绩。select学号，姓名，课程号，成绩from view_xscjwhere 成绩60;.使用DESC语句查看视图view_xscj的字段信息。de

18、sc view_xscj;.由于在学生成绩管理系统的应用中，学生选课时还需要了解该课程的开课学期，因此, 需要向视图view_xscj中添加“开课学期”列。alter view view_xscjasselect xsqk.学号姓名,kc.课程号，开课学期，授课教师，成绩from xsqk,kc,cjwhere xsqk.学号二cj.学号 and kc.课程号二cj.课程号 and kc.课程号 in(1101,11021);.删除视图 view_xscj。drop view if exists view_xscj;.编写一个存储存过程，其功能是删除数据库XSCJ的CJ1表中指定学号的学生成绩

19、。 delimiter #create procedure del_cj(in xh char(10)begindelete from cjl where 学号二xh;end #delimiter;.编写一个存储过程sum_add,其功能是完成两个整数相加。DELIMITER/create procedure sum_add(in x int,in y int) begindeclare z int default 0;set z = x+y;select z;end /delimiter;.调用存储过程proc_del_cj ()。call porc_deLcjC2020110401);.调

20、用存储过程sum_add。call sum_add( 12,24);.在存储过程proci中使用IF条件语句。delimiter /create procedure procl(in xh char(10)reads sql databeginif(select 学号 from xsqk where xsqk.学号二xh) is null thenselect 无此学生信息as学生信息； elseselect学号，姓名，性别，专业名from xsqk where xsqk.学号二xh;end if;end / delimiter;.使用CASE结构判断学生成绩等级。create procedu

21、re proc2(in xh char(10),in kch char(3)begindeclare fs tinyint;if(select 学号 from cj where 学号二xh and 课程号二kch) is null then select 无此学生成绩as学生成绩；elseselect 成绩 into fs from cj where 学号二xh and 课程号二kch;set fs=floor(fs/10);case fswhen 9 | 10 then select 优秀,as 成绩等级；when 8 then select良好，as 成绩等级；when 7 then se

22、lect中等，as 成绩等级when 6 then select 及格，as 成绩等级；elseselect 不及格as 成绩等级；end case;end if;end /.使用LOOP循环语句，用于完成输入一个正整数并求从1到该数的累加和。create procedure addsum(in x int)beginset i=l,sum=0;add_sum:loopbeginset sum=sum+i;set i=i+l;end;if ix thenleave add_sum;end if;end loop;select sum as 累加和；end /.使用WHILE循环语句求1+2+1

23、00的和.create procedure addsuml()begindeclare i int default 1;declare sum int default 0;while i = 100 doset sum=sum+i;set i=i+l;end while;select sum;end /.创立一个存储过程proc_xsqk,从数据库XSCJ的XSQK表中查询出所有专业名为“信息 平安”的人数。create procedure proc_xsqk()reads sql databeginselect count(*)信息平安专业人数from xsqkwhere专业名二信息平安财；

24、 end /.调用存储过程proc_xsqk()。call proc_xsqk();.创立一个存储过程proc_drop_cj,用于删除成绩表CJ中某个学生的所有成绩信息。 create procedure proc_drop_cj(in xh char(10)begindelete from cjwhere 学号二xh;end/.调用存储过程proc_drop_cjocall proc_drop_cj(20201104011);.创立一个带有输入输出参数的存储过程proc_count,用于统计xsqk表中某个指定专业 名的学生人数。create procedure proc_count(in

25、 ZYM varchar(20),out count_num int)reads sql databeginselect count(*) into count_num from xsqkwhere 专业名=ZYM;end /49,调用存储过程projcount,并显示指定专业名的学生人数。call projcountf信息平安,01);.创立一个存储函数func_xs,要求输入学生的学号，返回该学生的姓名。CREATE FUNCTION func_xs(xh CHAR(10)RETURNS VARCHAR(IO)READS SQL DATABEGINRETURN(SELECT 姓名FROM

26、xsqkWHERE 学号二xh);END/.调用存储函数func_xsoSelect func_xs(2020110101)姓名；.删除存储函数func_xs。drop function if exists func_xs;.创立存储函数func_cj,要求输入该学生的学号和课程号后，返回该学生这门课的成绩。CREATE FUNCTION func_cj (xh CHAR(10),kch VARCHAR)RETURNS DOUBLE(5,1)READS SQL DATABEGINRETURN(SELECT 成绩FROM cjWHERE cj.学号二xh AND cj.课程号二kch);END

27、$.调用存储函数func_cj。Select func_cj( d:mysqIbackxscj_kc.sqI.使用mysq I dump备份XSCJ数据库，备份文件为xscj. sq I,存放到D: mysq I back文件 夹中。mysqldump -u root -p xscj d :mysq I ba ckxscj ,sq I.使用mysqldump备份MySQL服务器中的所有数据库,存放到D:mysqlback文件夹中。mysqldump -u root -p -all-databasesd:mysqlbackxscj2.sql.使用MySQL命令将备份文件“D:Mysqlbackx

28、scj_kc.sql”还原到数据库XSCJ中。mysql -u root -p xscj D:Mysqlbackxscj_kc.sql.使用 source 导入备份文件 D: Mysq I backxsc j_kc. sq I osource D AMysq I backxscj_kc.sq I工程6：数据库综合应用.配置Apache支持PHPLoadModule php5_module C:PHPphp5apache2_2.dllAddType application/x- d-php ,phpPHPIniDir C:php.创立数据库Message和设计数据表info。1)创立Messag

29、e数据库：create database message ;2)在Message数据库中创立info表：create table info(id int primary key autojncrement,author varchar(20) not null,subject tinytext,content text not null, date datetime not null);.访客留言板的主程序index, php的设计doctype html访客留言表： function check_data()(if(document. my Form, author value length

30、 =0)altert(”作者字段不可以空白哦!)else if(document. my Form content value length =0) alert(内容字段不可以空白哦！)elsemyform.submit();?phprequire once(dbtools inc. php);指定每页显示几行记录$Records per_page =5;显示第几页的记录if(isset($_GETpage”)$page=$_GETpage;else$page =1;建立数据连接$link= create connection();执行SQL命令，按降序日期方式排序$sql=SELECT *F

31、ROM info ORDER BY date DESC;$Result execute sql(slink, message, $sql);获取记录数$total_records= mysqli_ num rows($result);计算总页数$total_pages= ceil(Stotal records/ Records_per_page);计算本页第一个记录的序号Started record = Records_per_page *($l);将记录指针移至本页第一个记录的序号mysqli_data_seek($Result, $tarted_record);使用$bg数组来存储表格背景

32、颜色$bg0=#D9D9FF;$bgl=#FFCAEE;$bg2=#FFFFCCn ;$bg3=n #B9EEB9” ；$bg4=#B9E9FF;echo;显示记录$j=l；while($Row=mysqli_fetch_assoc($Result) and $j= $Recordsper_ page) (echo;echotd width=120 align=center;echo”作者:,.$Row,author.;echo|5:,.$row, subject.;echo时间二 $rowldate.,;echo $rowcontent,;$j+；echo;产生导航条echo 1)echo;

33、for($i=l;$i $total_pages; $i+)(if ($i=$page)echo$i;elseecho$i;if($page $total_pages)echo下一页 echo;释放内存空间mysqli_free_result($Result);mysqli_close($link);?请在此输入新的留言 td width二作者 td width二主题 td width二内容4.表单读写程序transmit. php的设计.学生情况表XSQk的创立 create table XSQK( 学号 char(10) primary key, 姓名 varchar(lO) not nu

34、ll, 性别 char not null default男check(性别二男or 性别二女) 出生日期date not null, 专业名 varchar(20) not null, 所在学院 varchar(20) not null, 联系 char(ll), 总学分tinyint, 备注 varchar(50);.课程表KC的创立 create table kc( 课程号 char(3) primary key, 课程名 varchar(20) not null, 授课教师varchar(lO),开课学期 tinyint not null default 1 check(开课学期and

35、开课学期二6), 学时 tinyint not null, 学分 tinyint);.成绩表CJ的创立 create table CJ( 学号 char(10), 课程号char(3),成绩 tinyint check(成绩=0 and 成绩=100), 学分 tinyint,constraint primary key(学号，课程号)，constraint FK_xsqk_XH foreign key(学号)references xsqk(学号)， constraint FK_kc_KCH foreign key(课程号)references xsqk(课程号);.通过DESCRI BE查看

36、数据表CJ的基本结构 Desc cj;.通过SHOW CREATE TABLE查看表详细结构 SHOW CREATE TABLE CJG;.将XSCJ_db数据库中的数据表xsqkl改名为xsqko alter table xsqkl rename xsqk;.将XSCJ_db数据库中的CJ1表的“课程号”字段名改为“课程编号”，数据类型不变。 alter table CJ1 change 课程号课程编号 char(3);.将XSCJ_db数据库中的数据表CJ1中的学分字段的数据类型改为Int型。alter table cjl modify 学分 int;22,将XSCJ_db数据库中的数据表

37、CJ1的“课程编号”字段后新加一个名为“课程名称”的 字段，要求数据类型为varchar（20）,且不能取空值。alter table CJ1 add 课程名称 varchar（20） not null after 课程编号；.删除CJ1表中的“课程名称”字段。alter table cjl drop 课程名称；.将CJ1表中的“学号”字段排列到“课程编号”的后面。alter table cjl modify 学号 char（10） after 课程编号;.为表CJ1的学号列建立外键约束，参考的主键是xsqk表中的学号列。alter table cjl add constraint FK_cj

38、l_xh foreign key（学号）references xsqk（学号）；.删除CJ1表中的外键约束。show create table CJ1G;.删除数据表CJ1的主键约束。alter table cjl drop primary key;.向数据表CJ1添加主键约束，主键由为“学号”和“课程编号”两列联合组成。ALTER TABLE CJ1 ADD PRIMARY KEY（学号，课程编号）；.修改数据表CJ1,将其成绩列的默认值设置为Ooalter table cjl alter 成绩 set default 0;.修改数据表KC_db,要求“开课学期”列的取值在1到6之间。ALT

39、ER TABLE KC_db add CHECK（开课学期二 1 and 开课学期=6）;.修改数据表KC_db,删除“开课学期”列上设置的检查约束。show create table kc_dbG;.删除XSCJ_db数据库中的xsqk2表。drop table xsqk2;.修改XSCJ数据库中的CJ表，为“成绩”列添加默认值0。alter table cjalter成绩set default 0;.向数据库XSCJ中的XSQK表添加数据。insert into xsqk（学号，姓名，性别，出生日期,专业名，所在学院联系 ，总学分,备注） valuesC2020030101,王强 7 男,

40、19980406；云计算计算机学院；.向数据库XSCJ中的KC表添加数据。insert into kc values（101；计算机文化基础李平,基,3）;.向数据库XSCJ中的CJ表添加数据。insert into CJ（学号，课程号,成绩）values（2020030101,101,null）;.向XSQK表添加多条记录。insert into xsqk（学号，姓名，性别，出生日期,专业名，所在学院）values （2020020102,成刚，男,200202067计算机信息管理:计算机学院）（20200301037李 英：女720011011：信息平安：计算机学院）（2020030104

41、；赵林：男；20011111；网络技术； 计算机学院）；.将所有课程号为“101”的成绩加上2分。update CJ set 成绩=成绩+2 where 课程号=101;.假设学号为“2020030104”的同学退学，需要把学生情况表XSQK中的该生信息删除。 DELETE FROM XSQK WHERE 学号二202003010;.向学生情况表XSQK插入学生基本信息。insert into xsqk values （20200501027 王真男,2002-09-067 云计算计算机学院 王成：男 72002-09-061,云计算：计算机学院 Px班长）（2020110102,龙婷婷？女？

42、2002-11-05?云计算计算机学院 .向课程表KC插入学校开设的课程信息。insert into kc values （102计算机硬件基础；童华,80,5）003；程序设计基础；王印 264,4）;工程三：数据查询.查询学生成绩表CJ的全表数据。 select * from cj;.在CJ表中查询学号、课程号和成绩。select学号,课程号，成绩from cj;.查看CJ表中，有哪些课程已有学生选修了，要求显示出已有学生选修课程的课程号。 select distinct 课程号 from cj;.查询CJ表中的成绩信息，并要求对成绩增加两个更新列：一列是在原始成绩上加5分, 另一列是原成

43、绩的L1倍。select学号,课程号，成绩，成绩+5,成绩*1.1 from CJ;.把查询结果中“成绩”对应的列名改为“原成绩”，把“成绩+5”对应的列名改为“原成 绩+5”,把“成绩*1.1”对应的列名改为“原成绩SELECT学号,课程号，成绩原成绩，成绩+5源成绩+51成绩原成绩大11FROM CJ;.在课程表KC中查询课程号，课程名和开课学期。select课程号，课程名，开课学期from kc;.查询CJ表中成绩不及格的学生记录。select * from CJ where 成绩60;.使用比拟运算符BETWEEN AND查询2003年出生的学生信息，要求显示出学号、姓名、性 别、出生

44、日期和专业名字段。select学号，姓名，性别，出生日期,专业名from xsqkwhere 出生日期 between 生03期01 and20031231;.查询专业名为“云计算二性别为“男”的学生信息，要求显示出学号、姓名、性别和专 业名字段。select学号，姓名，性别专业名from xsqkwhere专业名=云计算and性别二，男；.在CJ表中查询课程号为“102” “105” “106”的学生成绩信息。select * from CJwhere课程号=102，or课程号二103, or课程号=105;.查询课程表KC中课程名中含有“计算”两个字的课程信息。select * from

45、KCwhere课程名like女计算忙.查询XSQK表中所有姓“张”的学生信息，要求显示学号、姓名、性别、出生日期和专 业名五列数据。select学号，姓名，性别，出生日期,专业名from XSQKwhere姓名like 张忙.使用IN查询，在CJ表中查询课程号为“102” “105” “106”的学生成绩信息。select * from cjwhere 课程号 in(,102,11051,106,);.在XSQK表中，查询所有班委的姓名、性别、专业名和班委职务。select姓名，性别专业名，备注 班委from xsqkwhere 备注 is not null;.查询XSQK表中所有不姓“李”和

46、“王”的学生的学号、姓名、性别、出生日期和专业 名。select学号，姓名，性别，出生日期,专业名from XSQKwhere 姓名 not like 李附 and 姓名 not like 王;.查询XSQK表中在2002年9月1日以后出生的学生信息，要求显示出学号、姓名、性 别、出生日期和专业名字段。select学号，姓名，性别，出生日期,专业名from xsqkwhere 出生日期=20020901;.查询成绩在60分到70分之间的学生信息。select * from CJwhere 成绩=60and 成绩二70;.查询xsqk表的记录，要求显示出学号、姓名、性别、出生日期和专业名，并按出生日 期升序排列。select学号，姓名，性别，出生日期,专业名from XSQKorder by 4;.查询CJ表中的记录，并先按课程号升序排列，当课程号相同时，再按成绩降序排列。select * from CJorder by课程号，成绩desc;.在CJ表中查询选修了课程号为“101”的记录，要求按成绩进行降序排列。select * from CJwhere 课程号=101 order by 成绩