MySQL数据库程序设计大题

4002

1

创建名为“testdb”的数据库，在保证不存在时才创建；查看当前用户可使用的数据库；删除数据库stu

create database if not exists st;
show databases;
drop database stu;

2

使用creat语句创建一个数据表stu，包括sid，sname，ssex，sdate四个字段，相应的字段类型分别为整型、字符串型（char（10））和（char（2））、和date，引擎为MyISAM

create table stu(sid int,sname char(10),sdate date)engine=MyISAM;

3

查看stu表的结构

show columns from stu;

4

查看当前数据库中的所有数据表

show tables;

4003

1

通过复制stu表的表结构创建新表st，通过复制stu表创建新表stud（包括结构与数据）

create table st like stu;
create table stud as select * from stu;

2

删除stu和score表

drop table stu,score;

3

为score表增加如下字段：考试日期exdate（日期型），考试地点exclassroom(char(5))

alter table score add column exdate date,add column exclassroom char(5);

4

删除表stu中的stuschool字段

alter table stu drop stuschool;

5

利用change子句修改score表的stuid字段名为sid，且改变其字段类型为char（11）；利用modify子句修改stu表的stusex字段类型为char（1）

alter table score change column stuid sid char(11);
alter table stu modify column stusex char(1);

6

利用alter子句删除stu表中stuid字段的默认值；利用alter子句为stu表的stusex字段设置默认值‘M’

alter table stu alter column stuid drop default;
alter table stu alter column stusex set default'M';

7

利用alter table命令将表stu更名为stud

alter table stu rename to stud;

8

利用modify子句将stud表中stubirth字段移到stuname字段之后

alter table stud modify column stubirth date after stuname;

4004

1

利用values子句向表score中添加如下记录：20160511011号同学D1000160号课程成绩为89.5；利用set子句向表course中添加如下新课：课程号为H2220750，课程名为“python程序设计”

insert into score values('20160511011','D1000160',89.5);
insert into course set courseid='H2220750',coursename='python程序设计';

2

复制stu表的表结构，建立新表nstu，利用values子句按下列顺序插入姓名、学号、性别:(Alice,20170511005,女)；2) 利用select子句向表nstu中添加stu表中所有女生的数据

create table nstu like stu;
insert into nstu(stuname,stuid,stusex)  values('Alice',20170511005,'女');
insert into nstu select * from stu where stusex='女';

3

1)利用delete命令删除stu表所有数据；2)利用truncate命令删除course表所有数据；3）利用delete命令删除course表中所有48学时的课程

delete from stu;
truncate table course;
delete from course where coursetime='48';

4

1)将大学物理课程的学时改为60学时；2)将所有选修课程E2200440的同学成绩提高1分

update course set coursetime=60 where coursename='大学物理';
update score set score=score+1 where courseid='E2200440';

5

1)20160411033号同学转专业到运输管理学院，修改其数据；2)修改所有男生的性别为M

update stu set stuschool='运输管理学院' where stuid='20160411033';
update stu set stusex='M' where stusex='男';

4005

1

从course表中查询全部记录信息(用*代表所有字段)

select * from course;

2

从stu表中查询所有同学的stuname,stuschool信息，结果列名分别为姓名、学院

select stuname as 姓名,stuschool as 学院 from stu;

3

从score表查询所有成绩在75分(含75分)以上的同学的情况(所有列）

select * from score where score>=75;

4

从stu表中查询”技术学院”所有男同学的stuname,stuschool信息，结果列名分别为姓名、学院

select stuname as 姓名,stuschool as 学院 from stu where stusex='男';

5

从score表中查询成绩在75到80分之间的同学信息(使用*代表全部结果列，使用between描述条件)

select * from score where score between 75 and 80;

6

利用IN关键字，从course表查询”大学物理”、“高等数学”、“操作系统”三门课程的情况(所有列)

select * from course where coursename in('大学物理','高等数学','操作系统');

7

从stu表中按学号降序查询所有女生基本情况

select * from stu where stusex='女' order by stuid desc;

8

从score表中按学号升序查询所有成绩，若学号相同则按成绩降序排列

select * from score order by stuid,score desc;

4006

1

从stu表中统计出全校男同学的人数(count(*))，结果列名为“男生人数”

select count(*) as 男生人数 from stu where stusex='男';

2

从score表中查询学生‘20160511011’所考课程的总分及平均分信息，结果列名为“学号”，“总分”，“平均分”

select stuid as 学号,sum(score) as 总分,avg(score) as 平均分 from score where stuid='20160511011';

3

从course表中查询最高课时和最低课时信息，结果列名分别为“最高课时”，“最低课时”

select max(coursetime) as 最高客时,min(coursetime) as 最低课时 from course;

4

从stu表中分别统计男、女生的人数，结果列为“性别”，“人数”2列

select stusex as 性别,count(*) as 人数 from stu group by stusex;

5

从stu表中查询各学院学生的人数，查询结果包含“学院”，“学生人数”2列

select stuschool as 学院,count(stuid) as 学生人数 from stu group by stuschool;

6

从score中计算每门课程的平均分，将平均分大于等于80分的科目信息输出(用having筛选)，结果包含“课程编号”、“平均分”两列

select courseid as 课程编号,avg(score) as 平均分 from score group by courseid having 平均分>=80;

7

查询学生的stuid，stuname以及所修课程的courseid，score信息，查询结果按学号升序排列。

select stu.stuid,stuname,courseid,score from stu inner join score on score.stuid=stu.stuid;

8

从course表中查询课时(coursetime)跟“数据库原理”课程一样的所有课程的信息

select * from course where coursetime=(select coursetime from course where coursename='数据库原理');

4007

1

创建一个名为tr_sc_ins的触发器，要求在向score表插入数据前，判断其score值，若小于0或大于100，则将score置为0

use student;
delimiter //
create trigger tr_sc_ins before insert
on score for each row
if new.score<0 or new.score>100
	then set new.score=0;
end if //

2

创建一个名为tr_stu_del的触发器，要求在删除stu表中某学生信息时，可自动删除该学生在score表中的信息。

use student;
create trigger tr_stu_del after delete
one stu for each row
delete from score where stuid=old.stuid;

3

创建一个名为tr_id_update的触发器，要求在修改course表的课程号后，同时修改score表中对应的课程号

use student;
create trigger tr_id_update after update
on course for each row
update score set courseid=new.courseid
where courseid=old.courseid;

4

1)查看当前数据库中定义的所有触发器的详细信息；2）仅查看触发器tr_sc_ins的详细信息；3）删除触发器tr_id_update

show triggers;
show create trigger tr_sc_ins;
drop trigger tr_id_update;

5

设计一个名为ev_cre_tb的事件，在3秒后创建表

use student;
set global event_scheduler=on;
create event ev_cre_tb
on schedule at now()+interval 3 second
do create table;

6

设计一个名为ev_ins的事件，每2秒向test表插入一条当前时间的记录，且10秒后事件结束。

use student;
set global event_scheduler=on;
create event ev_ins
on schedule every 2 second
starts now() ends now()+interval 10 second
do insert into test set b=now();

7

1

利用关键字on，开启事件调度器

set global event_scheduler=on;

2

关闭事件evins

alter event ev_ins disable;

3

修改事件evbak的名字为event bake

alter event ev_bak rename to event_bake;

4

删除事件ev_ins

drop event ev_ins;

4008

1

1)创建无参数存储过程get_score_proc，要求该存储过程能查询student.score表中score列大于等于90分的记录；2）用call命令调用1中建立的过程get_score_proc

delimiter //     /*修改命令结束符为//
create procedure get_score_proc()
begin
	select * from student.score where score>=90;
end //
delimiter ;
call get_score_proc();

2

1）创建带有IN类型参数的存储过程getin_score_proc，要求该存储过程能根据输入的分数值，查询student.score表中score列大于等于该值的记录。2）用call命令调用1中建立的过程getin score proc查询score中85分以上的记录信息

delimiter //
create procedure getin_score_proc(in num float)
begin
	select * from student.score where score>=num;
end //
delimiter ;

3

1）创建带有OUT类型参数的存储过程getout_score_proc，要求该存储过程能统计student.score表中score列大于等于85分的记录有多少个；2）用call命令调用1中建立的过程getout_score_proc

delimiter //
create procedure getout_score_proc(out num int)
begin
	select count(*) into num from student.score where score>=85;
end//
delimiter ;
call getout_score_proc(@x);
select @x;

4

1、创建带有INOUT类型参数的存储过程score_proc，要求该存储过程完成如下功能:

1)当输入参数为1时，统计student.score表中score列的最大值。

2)当输入参数为0时，统计student.score表中score列的最小值。

2）用call命令调用1中建立的过程score_proc，求出score列的最大最小值

delimiter //
create procedure score_proc(inout  num  int)
begin
	if num=1  then
		select max(score) into num from student.score;
	elseif num=0  then 
		select min(score) into num from student.score;
	end if;
end//
delimiter ;
/*求成绩最高分*/
set @x=1;
call score_proc(@x);
select @x;
/*求成绩最低分*/
set @x=0;
call score_proc(@x);
select @x;

5

删除数据库中已有的存储过程getin_score_proc

drop procedure getin_score_proc;

6

创建一个函数circle_area_fun，该函数功能为

，输出圆的面积。

set global log_bin_trust_function_creators = 1;
delimiter //
create function  circle_area_fun(x float) 
returns float
begin
	declare y float;
	set y=pi()*x*x;
	return (y);
end//
delimiter ;

7

创建一个函数sele_fun，该函数功能为

.score表中该学生所选课程的平均分，如果没有该学生则返回“查无此人!”。

set global log_bin_trust_function_creators = 1;
delimiter //
create function sele_fun(id varchar(11))
returns varchar(30)
begin
	declare avgscore varchar(30);
	select avg(score) into avgscore from student.score where stuid=id group by stuid;
	if avgscore  is null then
		return('查无此人！');
	else
		return(avgscore);
	end if;
end//
delimiter ;

8