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;