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

declare count int;

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;

posted on 2023-12-20 20:14 兮宇閱讀(699) 評論(0) 收藏舉報

刷新頁面返回頂部

公告