2、视图练习
(1)建立视图v_xs_1,要求包含男生的学号,姓名,性别,出生日期,班级编号,专业名称字段,并要求视图操作数据时进行检查。使用select命令查询创建的视图。
create view v_xs_1 as select xh,xm,xb,csrq,bjbh,zymc
from xsjbxxb
where xb='男'
with check option;
建立一个学院教师的视图v_xyjs,包含部门号,部门名称,教师姓名字段。使用select命令查询创建的视图。
create view v_xyjs as select bmdmb.bmh,bmmc,jsxm
from bmdmb join jsjbxxb
on bmdmb.bmh=jsjbxxb.bmh;
在jwgl数据库中,创建学生的选课信息视图v_xs_xk,包括学生的学号,姓名,性别,专业名称,课程名称,成绩字段。使用select命令查询创建的视图。
create view v_xs_xk as select xsjbxxb.xh,xm,xb,zymc,kcmc,cj
from xsjbxxb join xsxkb on xsjbxxb.xh=xsxkb.xh
join kcdmb on kcdmb.kcdm=xsxkb.kcdm;
创建一个计算每门课程平均成绩的视图v_kc_avg,要包含课程名称,课程平均成绩字段。使用select命令查询创建的视图。
create view v_kc_avg as select kcdmb.kcmc,avg(cj)
from kcdmb join xsxkb
on kcdmb.kcdm=xsxkb.kcdm
group by kcdmb.kcmc;
通过视图v_xs_xk,查询男同学的选课信息。
select * from v_xs_xk where xb='男';
修改视图v_xs_xk,要求视图包含2000年及以后出生的男生信息。
alter view v_xs_xk as
select xs.xh,xm,xb,zymc,kcmc,cj from xsjbxxb as xs join xsxkb
on xs.xh=xsxkb.xh
join kcdmb
on kcdmb.kcdm=xsxkb.kcdm
where xb='男' and year(csrq)>=2000;
利用视图v_kc_avg查询微机原理与应用这门课程的课程平均分。
select * from v_kc_avg
where kcmc='微机原理与应用';
输入一条数据:('201920505101','王红','女','2001-4-9','2019205051','财务管理')验证视图v_xs_1的WITH CHECK OPTION功能。
insert into v_xs_1 values('2303210516','孙晓峰','女','2002-1-15','2019205051','财务管理');
删除视图v_xyjs。
Drop view v_xyjs;
(1)使用变量查询学号为201820109101的学生的姓名和出生日期。
SET @number='201820109101';
SELECT xm, csrq
FROM xsjbxxb
WHERE xh=@number;
(2)使用IF语句编写一个存储过程xsxk。查询根据输入的课程名称查询该课程上课人数,如果人数多于或等于30人,则显示选课结果为“选课成功”,否则显示选课结果为“选课失败”。调用存储过程,查询“微机原理与汇编语言”课程上课人数,选课结果。
DELIMITER $$
create procedure xsxk(cname char(50))
begin
select count(*) into count
from xsxkb join kcdmb on xsxkb.kcdm=kcdmb.kcdm
where kcmc=cname;
if count>=30 then
select count as 选课人数,'选课成功' as 选课结果;
else
select count as 选课人数,'选课失败' as 选课结果;
end if;
end $$
调用存储过程:
call xsxk('微机原理与汇编语言')
$$
(3)使用带有简单 CASE 语句编写一个存储过程xsxb。根据学生学号查询学生性别,显示性别分类信息,如果性别是“男”,显示“该生是男同学”;如果性别是“女”,显示“该生是女同学”。调用存储过程,查询“201720409101”同学的性别分类信息。
create procedure xsxb(xsxh varchar(20))
begin
declare xbfl char(2);
select xb into xbfl from xsjbxxb where xh=xsxh;
case xbfl
when '男' then select xbfl as 性别,'该生是男同学' as 性别分类;
when '女' then select xbfl as 性别,'该生是女同学' as 性别分类;
end case;
end
$$
调用存储过程:
call xsxb('201720409101')
$$
(4)使用CASE语句编写程序:建立一个存储过程getGradeCase,该存储过程通过学生学号(stu_no)和课程编号(cour_no)查询其成绩(grade),返回成绩和成绩的等级,成绩大于等于90分的为优秀,小于90分大于等于80分的为良好,小于80分大于等于70分的为中等,小于70分大于等于60分的为及格,小于60分为不及格。调用存储过程getGradeCase,查看学号为“201720505101”,课程代码为“00202117”的成绩及成绩等级。
Create procedure getGradeCase(stu_no varchar(20),cour_no varchar(10))
begin
declare stu_grade float;
select cj into stu_grade from xsxkb where xh=stu_no and kcdm=cour_no;
case
WHEN stu_grade >=90 THEN
select stu_grade as '成绩','优秀' as '等级';
WHEN stu_grade <90 and stu_grade >=80 THEN
select stu_grade as '成绩','良好' as '等级';
WHEN stu_grade <80 and stu_grade >=70 THEN
select stu_grade as '成绩','中等' as '等级';
WHEN stu_grade <70 and stu_grade >=60 THEN
select stu_grade as '成绩','及格' as '等级';
else
select stu_grade as '成绩','不及格' as '等级';
end case;
end
$$
调用存储过程:
call getGradeCase('201720505101','00202117');
(5)使用REPEAT语句编写一个存储过程oddsum,求1到任意数以内各奇数的和。调用存储过程sum,求100以内的奇数和。
create procedure oddsum(a int)
begin
declare sum int default 0;
declare i int default 1;
repeat
set sum=sum+i;
set i=i+2;
until i>a
end repeat;
select sum;
end
$$
调用存储过程:
call oddsum(100)
$$
(6)使用WHILE语句编写一个存储过程multiply,实现1到任意数的累积。调用存储过程multiply,求1*2*3……*20的乘积。
create procedure multiply(a int)
begin
declare mul bigint default 1;
declare i int default 1;
while i<=a DO
set mul=mul*i;
set i=i+1;
end while;
select mul;
end
$$
调用存储过程:
call multiply(20)$$
2.运算符和表达式的使用方法:(每题5分,共30分)
(1)运算符准备,执行代码如下:
mysql> CREATE TABLE t1(a INT,s CHAR(10));
mysql> INSERT INTO t1 VALUE(20,'beijing');
(2)执行如下SQL运算符代码,并分析结果。
①SELECT a,a+5,a*2 FROM t1;
②SELECT a,a/3,a DIV 3,a%5,MOD(a,5) FROM t1;
③SELECT a,a=24,a<12,a>40,a>=24,a<=24,a!=24,a<>24,a<=>24 FROM t1;
④SELECT s,s LIKE 'beijing',s LIKE 'b%g',s LIKE 'bei_',s LIKE '%jing' FROM t1;
三、实验步骤 1. MySQL系统内置函数的使用: (1)SELECT ABS(0.5), ABS(-0.5), PI(); (2)SELECT CEIL(2.3), CEIL(-2.3), CEILING(2.3), CEILING(-2.3); (3)SELECT ROUND(2.3), ROUND(2.5), ROUND(2.53,1), ROUND(2.55,1); (4)SELECT SUBSTRING('helloworld',1,5); (5)SELECT LENGTH('helo'); (6)SELECT CONNECTION_ID(); (7)SELECT DATABASE(), SCHEMA(): (8)SELECT USER(), SYSTEM_USER(), SESSION_USER(); 2. MySQL自定义函数的创建和调用: (1)创建一个无参数的自定义函数并调用该函数。(例题9.16) 代码为: DROP FUNCTION IF EXISTS hello; DELIMITER $$ CREATE FUNCTION hello() RETURNS VARCHAR(255) BEGIN RETURN 'Hello world,i am mysql'; END $$ DELIMITER ; 调用hello函数:SELECT hello(); (2)创建带参数的自定义函数formatDate,实现简单调用DATE_FORMAT(date,format)函数功能,并调用该函数。(例题9.17) DELIMITER $$ DROP FUNCTION IF EXISTS formatDate $$ CREATE FUNCTION formatDate(fdate datetime) RETURNS VARCHAR(255) BEGIN DECLARE x VARCHAR(255) DEFAULT ''; SET x= date_format(fdate,'%Y年%m月%d日%h时%i分%s秒'); RETURN x; END $$ DELIMITER ; 调用formatDate函数。输入SQL语句: select formatDate(now()); (3)使用数据库中的xsxkb表、kcdmb表,创建自定义函数num_func,统计指定课程名称的选课人数。 mysql> delimiter $$ mysql> create function num_func(kcmc varchar(20)) -> returns int -> begin -> declare num int; -> select count(*) into num from xsxkb,kcdmb -> where xsxkb.kcdm=kcdmb.kcdm and kcmc=kcdmb.kcmc; -> return num; -> end $$ 调用函数num_func ,查看“计算机网络技术”的选课人数。 select num_func('计算机网络技术'); (4)删除函数num_func 。 drop function num_func; |
(一)创建并调用存储过程:
1.在jwgl数据库系统中,创建一个名为proc_select存储过程,实现查询所有学生基本信息。调用存储过程查看学生基本信息。
创建函数代码如下:
mysql> delimiter $$
mysql> create procedure proc_select()
-> begin
-> select * from xsjbxxb;
-> end $$
调用存储过程:
mysql> delimiter ;
mysql> call proc_select();
2. 在jwgl数据库系统中,创建一个名为proc_kc _cj存储过程,要求实现如下功能:根据学生的姓名,查询该学生选课成绩(包括xh、xm、kcmc、cj字段)。调用存储过程,查询杨丽娟和唐宇坤的选课成绩。
创建存储过程代码如下:
delimiter $$
create procedure proc_kc_cj(in name varchar(50))
begin
select xsjbxxb.xh,xm,kcmc,cj from xsjbxxb,xsxkb,kcdmb
where xsjbxxb.xh=xsxkb.xh
and kcdmb.kcdm=xsxkb.kcdm
and xm=name;
end $$
delimiter ;
调用存储过程查看:
call proc_kc_cj('杨丽娟');
call proc_kc_cj('唐宇坤');
3.在jwgl数据库系统中创建存储过程,存储过程名proc_kc_cjcx,要求实现如下功能:根据课程名称,查询该课程的选课情况,如果该课程没有学生选课,则输出“某某课程没有学生选课”信息,否则输出该门课程所有学生选课的相关消息,包括学生姓名、班级名称、课程名称和成绩等。通过调用存储过程proc_kc_cjcx,显示选修“网络数据库应用技术” 课程的学生情况。
创建存储过程代码如下:
Delimiter $$
CREATE PROCEDURE proc_kc_cjcx(IN CourseName varchar(30))
begin
if((select count(kcdmb.kcdm)
from kcdmb,xsxkb
where kcdmb.kcdm=xsxkb.kcdm and kcdmb.kcmc=CourseName)=0) then
select "该课程没有学生选课";
else
select xm,bjzwmc,kcmc,cj from xsjbxxb,xsxkb,kcdmb,bjdmb
where xsxkb.xh=xsjbxxb.xh and kcdmb.kcdm=xsxkb.kcdm and xsjbxxb.bjbh=bjdmb.bjbh
and kcmc=CourseName;
end if;
end $$
Delimiter ;
调用存储过程查询:
Call proc_kc_cjcx(‘网络数据库应用技术’);
4.在jwgl数据库系统中创建存储过程,存储过程名stu_grcount,要求实现如下功能:当输入一个学生的学号时,通过返回输出参数获取该学生选修课程的门数。执行存储过程 stu_grcount,显示学号为 201720909101的选课门数。
创建存储过程代码如下:
Delimiter $$
create procedure stu_grcount(in xuehao varchar(20),out num int)
begin
select count(*) into num from xsxkb
Where xh=xuehao;
End $$
delimiter ;
调用存储过程:
mysql> call stu_grcount('2017209091011',@num);
mysql> select @num;
5. 删除jwgl数据库中的存储过程proc_kc_cjcx。(只写代码,不执行)
DROP PROCEDURE IF EXISTS proc_kc_cjcx;
(二)触发器的应用
1. 在jwgl数据库系统创建触发器trigger_delete,实现如下的功能:当在jwgl数据库系统中的表xsjbxxb中删除某个学生时,同时更新对应表xsxkb中相应学生的选课记录。
代码如下:
delimiter $$
DROP TRIGGER IF EXISTS trigger_delete$$
CREATE TRIGGER trigger_delete
AFTER DELETE ON xsjbxxb
FOR EACH ROW
BEGIN
delete from xsxkb where xh=old.xh;
END$$
delimiter ;
验证:
mysql> delete from xsjbxxb where xh='201720409101';
mysql> select * from xsxkb where xh='201720409101';
mysql> select * from xsjbxxb where xh='201720409101';
2. 查看触发器trigger_delete的文本定义。
mysql> SHOW TRIGGERS WHERE `TRIGGER` LIKE 'trigger_delete'\G;
3. 对表xsjbxxb创建名为 trigger_update的触发器,当修改表xsjbxxb中某一条记录时,同时更新对应表xsxkb中相应学生的选课记录。
代码如下:
CREATE TRIGGER trigger_update
AFTER UPDATE ON xsjbxxb
FOR EACH ROW
BEGIN
update xsxkb set xsxkb.xh=new.xh where xsxkb.xh=old.xh;
END$$
delimiter ;
验证:
mysql> update xsjbxxb set xh='2003210514' where xm='马文慧';
4.删除触发器xsjbxxb_deleted. (只写代码,不执行)
DROP TRIGGER jwgl.xsjbxxb_deleted;
标签:练习题,xh,选课,数据库,存储,c203,xsxkb,xsjbxxb,select From: https://www.cnblogs.com/mhyweb/p/17917406.html