實訓案例名稱：視圖一、任務(wù)介紹視圖是一條SELECT語句返回的結(jié)果集，這個結(jié)果集可以從一張表中查詢出來，也可以從多張表中查詢出來。SELECT語句使用的表可以當成基本表，而結(jié)果集則構(gòu)成一張?zhí)摂M表。虛擬表也是表，可以進行增、刪、改、查操作，但是有條件限制，它可以存放SELECT語句查詢的結(jié)果，但是不存放具體數(shù)據(jù)，基本表里的數(shù)據(jù)變化會影響視圖虛擬表查詢的結(jié)果。視圖的好處在于方便查詢，只查詢出想要的字段。同時，使用視圖也更加安全，基本表查詢不能限定特定的列和行，而有不想顯示出來的存在敏感信息的列時，視圖對其可以靈活地控制。二、實現(xiàn)思路1.創(chuàng)建視圖#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#創(chuàng)建員工表employeemysql>CREATETABLEemployee(idintnotnullAUTO_INCREMENT,namevarchar(255),sexvarchar(10),ageint,deptIdint,primarykey(id));QueryOK,0rowsaffected(0.34sec)#創(chuàng)建部門表departmentmysql>CREATETABLEdepartment(idintnotnullAUTO_INCREMENT,deptNamevarchar(255),managervarchar(255),primarykey(id));QueryOK,0rowsaffected(0.04sec)#創(chuàng)建工資表payrollmysql>CREATETABLEpayroll(idintnotnullAUTO_INCREMENT,empIdint,salaryvarchar(255),grantDatedate,primarykey(id));QueryOK,0rowsaffected(0.46sec)#批量插入員工數(shù)據(jù)mysql>INSERTINTOemployeeVALUES(1,'張明','男',30,1),(2,'孫浩','男',25,1),(3,'張靜','女',28,2),(4,'趙穎','女',32,2),(5,'劉帥','男',28,2);QueryOK,5rowsaffected(0.01sec)#批量插入部門數(shù)據(jù)mysql>INSERTINTOdepartmentVALUES(1,'軟件開發(fā)部','王洋'),(2,'人力資源部','吳剛');QueryOK,2rowsaffected(0.01sec)#批量插入工資數(shù)據(jù)mysql>INSERTINTOpayrollVALUES(1,1,'13500','2023-11-15'),(2,1,'16500','2023-12-15'),(3,2,'9500','2023-11-15'),(4,2,'10500','2023-12-15'),(5,3,'11000','2023-11-15'),(6,3,'12000','2023-12-15'),(7,4,'8000','2023-11-15'),(8,4,'8500','2023-12-15'),(9,5,'6000','2023-11-15'),(10,5,'6500','2023-12-15');QueryOK,10rowsaffected(0.01sec)#查看員工表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+-------+------+------+--------+|id|name|sex|age|deptId|+----+-------+------+------+--------+|1|張明|男|30|1||2|孫浩|男|25|1||3|張靜|女|28|2||4|趙穎|女|32|2||5|劉帥|男|28|2|+----+-------+------+------+--------+5rowsinset(0.00sec)#查看部門表department的數(shù)據(jù)mysql>SELECT*FROMdepartment;+----+----------------+---------+|id|deptName|manager|+----+----------------+---------+|1|軟件開發(fā)部|王洋||2|人力資源部|吳剛|+----+----------------+---------+2rowsinset(0.00sec)#查看工資表payroll的數(shù)據(jù)mysql>SELECT*FROMpayroll;+----+-------+--------+------------+|id|empId|salary|grantDate|+----+-------+--------+------------+|1 |1|13500|2023-11-15||2 |1|16500|2023-12-15||3 |2|9500|2023-11-15||4 |2|10500|2023-12-15||5 |3|11000|2023-11-15||6 |3|12000|2023-12-15||7 |4|8000|2023-11-15||8 |4|8500|2023-12-15||9 |5|6000|2023-11-15||10 |5|6500|2023-12-15|+----+-------+--------+------------+10rowsinset(0.00sec)#創(chuàng)建視圖edp_view，獲取員工id、姓名、部門名稱、工資、發(fā)放日期mFROMemployeee,departmentd,payrollpWHEREe.deptId=d.idANDe.id=p.empId;QueryOK,0rowsaffected(0.10sec)#查看視圖edp_view的數(shù)據(jù)mysql>SELECT*FROMedp_view;+----+------+------------+--------+------------+|id|name|deptName|salary|grantDate|+----+------+------------+--------+------------+|1|張明|軟件開發(fā)部|13500|2023-11-15||1|張明|軟件開發(fā)部|16500|2023-12-15||2|孫浩|軟件開發(fā)部|9500|2023-11-15||2|孫浩|軟件開發(fā)部|10500|2023-12-15||3|張靜|人力資源部|11000|2023-11-15||3|張靜|人力資源部|12000|2023-12-15||4|趙穎|人力資源部|8000|2023-11-15||4|趙穎|人力資源部|8500|2023-12-15||5|劉帥|人力資源部|6000|2023-11-15||5|劉帥|人力資源部|6500|2023-12-15|+----+------+------------+--------+------------+10rowsinset(0.01sec)#查看視圖edp_view中軟件開發(fā)部的數(shù)據(jù)mysql>SELECT*FROMedp_viewWHEREdeptName='軟件開發(fā)部';+----+------+------------+--------+------------+|id|name|deptName|salary|grantDate|+----+------+------------+--------+------------+|1|張明|軟件開發(fā)部|13500|2023-11-15||1|張明|軟件開發(fā)部|16500|2023-12-15||2|孫浩|軟件開發(fā)部|9500|2023-11-15||2|孫浩|軟件開發(fā)部|10500|2023-12-15|+----+------+------------+--------+------------+4rowsinset(0.00sec)#查看視圖edp_view中工資超過10000元的數(shù)據(jù)mysql>SELECT*FROMedp_viewWHEREsalary>10000;+----+------+------------+--------+------------+|id|name|deptName|salary|grantDate|+----+------+------------+--------+------------+|1|張明|軟件開發(fā)部|13500|2023-11-15||1|張明|軟件開發(fā)部|16500|2023-12-15||2|孫浩|軟件開發(fā)部|10500|2023-12-15||3|張靜|人力資源部|11000|2023-11-15||3|張靜|人力資源部|12000|2023-12-15|+----+------+------------+--------+------------+5rowsinset(0.00sec)#查看視圖edp_view的結(jié)構(gòu)mysql>DESCedp_view;+-----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+--------------+------+-----+---------+-------+|id |int|NO||0|||name |varchar(255)|YES||NULL|||deptName|varchar(255)|YES||NULL|||salary|varchar(255)|YES||NULL|||grantDate|date|YES||NULL||+-----------+--------------+------+-----+---------+-------+5rowsinset(0.01sec)2.修改視圖#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#使用CREATEORREPLACEVIEW語句修改視圖mysql>CREATEORREPLACEVIEWedp_viewASSELECTe.id,,d.deptName,d.manager,p.salary,p.grantDateFROMemployeee,departmentd,payrollpWHEREe.deptId=d.idANDe.id=p.empId;QueryOK,0rowsaffected(0.09sec)#查看視圖edp_view的結(jié)構(gòu)mysql>DESCedp_view;+-----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+--------------+------+-----+---------+-------+|id|int|NO||0|||name|varchar(255)|YES||NULL|||deptName|varchar(255)|YES||NULL|||manager|varchar(255)|YES||NULL|||salary|varchar(255)|YES||NULL|||grantDate|date|YES||NULL||+-----------+--------------+------+-----+---------+-------+6rowsinset(0.00sec)#查詢視圖edp_view的數(shù)據(jù)mysql>SELECT*FROMedp_view;+----+------+------------+---------+--------+------------+|id|name|deptName|manager|salary|grantDate|+----+------+------------+---------+--------+------------+|1|張明|軟件開發(fā)部|王洋|13500|2023-11-15||1|張明|軟件開發(fā)部|王洋|16500|2023-12-15||2|孫浩|軟件開發(fā)部|王洋|9500|2023-11-15||2|孫浩|軟件開發(fā)部|王洋|10500|2023-12-15||3|張靜|人力資源部|吳剛|11000|2023-11-15||3|張靜|人力資源部|吳剛|12000|2023-12-15||4|趙穎|人力資源部|吳剛|8000|2023-11-15||4|趙穎|人力資源部|吳剛|8500|2023-12-15||5|劉帥|人力資源部|吳剛|6000|2023-11-15||5|劉帥|人力資源部|吳剛|6500|2023-12-15|+----+------+------------+---------+--------+------------+10rowsinset(0.00sec)#使用ALTER語句修改視圖mysql>ALTERVIEWedp_viewASSELECTe.id,,e.sex,d.deptName,d.manager,p.salary,p.grantDateFROMemployeee,departmentd,payrollpWHEREe.deptId=d.idANDe.id=p.empId;QueryOK,0rowsaffected(0.11sec)#查看視圖edp_view的結(jié)構(gòu)mysql>DESCedp_view;+-----------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------+--------------+------+-----+---------+-------+|id|int|NO||0|||name|varchar(255)|YES||NULL|||sex|varchar(10)|YES||NULL|||deptName|varchar(255)|YES||NULL|||manager|varchar(255)|YES||NULL|||salary|varchar(255)|YES||NULL|||grantDate|date|YES||NULL||+-----------+--------------+------+-----+---------+-------+7rowsinset(0.00sec)#查詢視圖edp_view的數(shù)據(jù)mysql>SELECT*FROMedp_view;+----+------+------+------------+---------+--------+------------+|id|name|sex|deptName|manager|salary|grantDate|+----+------+------+------------+---------+--------+------------+|1|張明|男|軟件開發(fā)部|王洋|13500|2023-11-15||1|張明|男|軟件開發(fā)部|王洋|16500|2023-12-15||2|孫浩|男|軟件開發(fā)部|王洋|9500|2023-11-15||2|孫浩|男|軟件開發(fā)部|王洋|10500|2023-12-15||3|張靜|女|人力資源部|吳剛|11000|2023-11-15||3|張靜|女|人力資源部|吳剛|12000|2023-12-15||4|趙穎|女|人力資源部|吳剛|8000|2023-11-15||4|趙穎|女|人力資源部|吳剛|8500|2023-12-15||5|劉帥|男|人力資源部|吳剛|6000|2023-11-15||5|劉帥|男|人力資源部|吳剛|6500|2023-12-15|+----+------+------+------------+---------+--------+------------+10rowsinset(0.00sec)mysql>3.更新視圖數(shù)據(jù)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#查詢視圖edp_view的數(shù)據(jù)mysql>SELECT*FROMedp_view;+----+------+------+------------+---------+--------+------------+|id|name|sex|deptName|manager|salary|grantDate|+----+------+------+------------+---------+--------+------------+|1|張明|男|軟件開發(fā)部|王洋|13500|2023-11-15||1|張明|男|軟件開發(fā)部|王洋|16500|2023-12-15||2|孫浩|男|軟件開發(fā)部|王洋|9500|2023-11-15||2|孫浩|男|軟件開發(fā)部|王洋|10500|2023-12-15||3|張靜|女|人力資源部|吳剛|11000|2023-11-15||3|張靜|女|人力資源部|吳剛|12000|2023-12-15||4|趙穎|女|人力資源部|吳剛|8000|2023-11-15||4|趙穎|女|人力資源部|吳剛|8500|2023-12-15||5|劉帥|男|人力資源部|吳剛|6000|2023-11-15||5|劉帥|男|人力資源部|吳剛|6500|2023-12-15|+----+------+------+------------+---------+--------+------------+10rowsinset(0.00sec)#更新視圖edp_view中部門名稱為軟件開發(fā)部的部門經(jīng)理為李姝mysql>UPDATEedp_viewSETmanager='李姝'WHEREdeptName='軟件開發(fā)部';QueryOK,1rowaffected(0.06sec)Rowsmatched:1Changed:1Warnings:0#更新視圖數(shù)據(jù)成功，將軟件開發(fā)部的部門經(jīng)理改為李姝mysql>SELECT*FROMedp_view;+----+------+------+------------+---------+--------+------------+|id|name|sex|deptName|manager|salary|grantDate|+----+------+------+------------+---------+--------+------------+|1|張明|男|軟件開發(fā)部|李姝|13500|2023-11-15||1|張明|男|軟件開發(fā)部|李姝|16500|2023-12-15||2|孫浩|男|軟件開發(fā)部|李姝|9500|2023-11-15||2|孫浩|男|軟件開發(fā)部|李姝|10500|2023-12-15||3|張靜|女|人力資源部|吳剛|11000|2023-11-15||3|張靜|女|人力資源部|吳剛|12000

|2023-12-15

||4|趙穎|女|人力資源部|吳剛|8000

|2023-11-15

||4|趙穎|女|人力資源部|吳剛|8500

|2023-12-15

||5|劉帥|男|人力資源部|吳剛|6000

|2023-11-15

||5|劉帥|男|人力資源部|吳剛|6500

|2023-12-15

|+----+------+------+------------+---------+--------+------------+10rowsinset(0.00sec)#將部門表department中軟件開發(fā)部的部門經(jīng)理改為李姝，聯(lián)動視圖的修改，對基本表也進行修改mysql>SELECT*FROMdepartment;+----+------------+---------+|id|deptName|manager|+----+------------+---------+|1|軟件開發(fā)部|李姝||2|人力資源部|吳剛|+----+------------+---------+2rowsinset(0.00sec)#與多表關(guān)聯(lián)的視圖edp_view，插入數(shù)據(jù)會失敗mysql>INSERTINTOedp_viewVALUES(6,'王鑫','男','辦公室','王宇','7000',

'2023-12-15');E#與多表關(guān)聯(lián)的視圖edp_view，刪除數(shù)據(jù)會失敗mysql>DELETEedp_viewWHEREid=1;ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'WHEREid=1'atline1#創(chuàng)建基于單表的視圖emp_view，包含員工id、姓名mysql>CREATEVIEWemp_viewASSELECTid,nameFROMemployee;QueryOK,0rowsaffected(0.12sec)#查看視圖emp_view的結(jié)構(gòu)mysql>DESCemp_view;+-------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+--------------+------+-----+---------+-------+|id|int|NO||0|||name|varchar(255)|YES||NULL||+-------+--------------+------+-----+---------+-------+2rowsinset(0.00sec)#查詢視圖emp_view的數(shù)據(jù)mysql>SELECT*FROMemp_view;+----+--------+|id|name|+----+--------+|1|張明

||2|孫浩

||3|張靜

||4|趙穎

||5|劉帥

|+----+--------+5rowsinset(0.00sec)#查詢員工表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+------+------+------+--------+|id|name|sex|age|deptId|+----+------+------+------+--------+|1|張明|男|30|1||2|孫浩|男|25|1||3|張靜|女|28|2||4|趙穎|女|32|2||5|劉帥|男|28|2|+----+------+------+------+--------+5rowsinset(0.00sec)#在視圖emp_view中插入數(shù)據(jù)，插入數(shù)據(jù)成功mysql>INSERTINTOemp_viewVALUES(6,'劉濤');QueryOK,1rowaffected(0.00sec)#查詢視圖emp_view的數(shù)據(jù)，可以看到插入數(shù)據(jù)成功mysql>SELECT*FROMemp_view;+----+-------+|id|name|+----+-------+|1|張明||2|孫浩||3|張靜||4|趙穎||5|劉帥||6|劉濤|+----+-------+6rowsinset(0.00sec)#查詢員工表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+------+------+------+--------+|id|name|sex|age|deptId|+----+------+------+------+--------+|1|張明|男|30|1||2|孫浩|男|25|1||3|張靜|女|28|2||4|趙穎|女|32|2||5|劉帥|男|28|2||6|劉濤|NULL|NULL|NULL|+----+------+------+------+--------+6rowsinset(0.00sec)#刪除視圖emp_view中id為3的數(shù)據(jù)，刪除數(shù)據(jù)成功mysql>DELETEFROMemp_viewWHEREid=3;QueryOK,1rowaffected(0.00sec)#查詢視圖emp_view的數(shù)據(jù)mysql>SELECT*FROMemp_view;+----+--------+|id|name

|+----+--------+|1|張明

||2|孫浩

||4|趙穎

||5|劉帥

||6|劉濤

|+----+--------+5rowsinset(0.00sec)#查詢員工表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+-------+------+------+--------+|id|name|sex|age|deptId

|+----+-------+------+------+--------+|1|張明|男|30|1

||2|孫浩|男|25|1

||4|趙穎|女|32|2

||5|劉帥|男|28|2

||6|劉濤|NULL|NULL|NULL

|+----+-------+------+------+--------+5rowsinset(0.00sec)mysql>4.刪除視圖和數(shù)據(jù)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#查看依賴于單張基本表建立的視圖emp_view的數(shù)據(jù)mysql>SELECT*FROMemp_view;+----+-------+|id|name

|+----+-------+|1|張明

||2|孫浩

||4|趙穎

||5|劉帥

||6|劉濤

|+----+-------+5rowsinset(0.00sec)#依賴于單張基本表建立的視圖的數(shù)據(jù)允許直接刪除mysql>DELETEFROMemp_viewWHEREid=5;QueryOK,1rowaffected(0.00sec)#查詢視圖emp_view的數(shù)據(jù)mysql>SELECT*FROMemp_view;+----+-------+|id|name|+----+-------+|1|張明||2|孫浩||4|趙穎||6|劉濤|+----+-------+4rowsinset(0.00sec)#查詢基本表employee的數(shù)據(jù)mysql>SELECT*FROMemployee;+----+-------+------+------+--------+|id|name|sex|age|deptId|+----+-------+------+------+--------+|1|張明|男|30|1||2|孫浩|男|25|1||4|趙穎|女|32|2||6|劉濤|NULL|NULL|NULL|+----+-------+------+------+--------+4rowsinset(0.00sec)#如果視圖存在，則刪除視圖emp_viewmysql>DROPVIEWIFEXISTSemp_view;QueryOK,0rowsaffected(0.00sec)#查看依賴于多張基本表建立的視圖edp_view的數(shù)據(jù)mysql>SELECT*FROMedp_view;+----+-------+------+---------------+---------+--------+-----------+|id|name|sex|deptName|manager|salary|grantDate|+----+-------+------+---------------+---------+--------+-----------+|1|張明|男|軟件開發(fā)部|李姝|13500|2023-11-15||1|張明|男|軟件開發(fā)部|李姝|16500|2023-12-15||2|孫浩|男|軟件開發(fā)部|李姝|9500|2023-11-15||2|孫浩|男|軟件開發(fā)部|李姝|10500|2023-12-15||4|趙穎|女|人力資源部|吳剛|8000|2023-11-15||4|趙穎|女|人力資源部|吳剛|8500|2023-12-15|+----+-------+------+---------------+---------+--------+-----------+6rowsinset(0.00sec)#依賴于多張基本表建立的視圖的數(shù)據(jù)是不能直接刪除的mysql>DELETEFROMedp_viewWHEREid=1;ERROR1395(HY000):Cannotdeletefromjoinview'shop.edp_view'#刪除視圖edp_viewmysql>DROPVIEWedp_view;QueryOK,0rowsaffected(0.00sec)mysql>實訓案例名稱：游標一、任務(wù)介紹游標是一個存儲在MySQL數(shù)據(jù)庫里用來查詢的對象，它可以每次從結(jié)果集里獲取一行數(shù)據(jù)，進行相應(yīng)的處理。有了游標，就可以滾動查詢滿足條件的數(shù)據(jù)，可以做一些復雜的處理。二、實現(xiàn)步驟1.使用游標#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#創(chuàng)建員工表employee_infomysql>CREATETABLEemployee_info(idintnotnullAUTO_INCREMENT,namevarchar(255),salaryint,primarykey(id));QueryOK,0rowsaffected(0.36sec)#向員工表employee_info插入數(shù)據(jù)mysql>INSERTINTOemployee_infoVALUES(1,'小明',3700),(2,'小紅',5700),(3,'小藍',7800);QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0#查詢員工表employee_info的數(shù)據(jù)，使用SELECT語句，一次獲取滿足條件的所有數(shù)據(jù)mysql>SELECT*FROMemployee_info;+----+------+--------+|id|name|salary|+----+------+--------+|1|小明|3700||2|小紅|5700||3|小藍|7800|+----+------+--------+3rowsinset(0.01sec)#修改結(jié)束符$$mysql>DELIMITER$$#創(chuàng)建存儲過程p2()，定義游標cursor2，讀取每一行數(shù)據(jù)mysql>CREATEPROCEDUREp2()BEGINDECLAREc_idint;DECLAREc_namevarchar(20);DECLAREc_salaryint;DECLAREcursor2CURSORFORSELECTid,name,salaryFROMemployee_info;OPENcursor2;FETCHcursor2INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;FETCHcursor2INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;FETCHcursor2INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;CLOSEcursor2;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程p2()mysql>CALLp2();#使用游標一次，輸出一次結(jié)果+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|1|小明 | 3700|+------+--------+----------+1rowinset(0.00sec)#使用游標一次，輸出一次結(jié)果+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|2|小紅 | 5700|+------+--------+----------+1rowinset(0.01sec)#使用游標一次，輸出一次結(jié)果+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|3|小藍|7800|+------+--------+----------+1rowinset(0.02sec)mysql>2.游標的WHILE循環(huán)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#查詢員工表employee_infomysql>SELECT*FROMemployee_info;+----+------+--------+|id|name|salary|+----+------+--------+|1|小明|3700||2|小紅|5700||3|小藍|7800|+----+------+--------+3rowsinset(0.00sec)#修改結(jié)束符$$mysql>DELIMITER$$#定義一個存儲過程，先計算出結(jié)果總條數(shù)，再使用WHILE循環(huán)mysql>CREATEPROCEDUREp3()BEGINDECLAREc_idint;DECLAREc_namevarchar(20);DECLAREc_salaryint;DECLAREsumintdefault0;DECLAREcursor3CURSORFORSELECTid,name,salaryFROMemployee_info;SELECTcount(*)INTOsumFROMemployee_info;OPENcursor3;WHILE(sum>0)DOFETCHcursor3INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary,sum;SETsum=sum-1;ENDWHILE;CLOSEcursor3;END$$#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程，輸出每次循環(huán)的結(jié)果mysql>CALLp3();+------+--------+----------+------+|c_id|c_name|c_salary|sum|+------+--------+----------+------+|1|小明|3700|3|+------+--------+----------+------+1rowinset(0.01sec)+------+--------+----------+------+|c_id|c_name|c_salary|sum|+------+--------+----------+------+|2|小紅|5700|2|+------+--------+----------+------+1rowinset(0.02sec)+------+--------+----------+------+|c_id|c_name|c_salary|sum|+------+--------+----------+------+|3|小藍|7800|1|+------+--------+----------+------+1rowinset(0.02sec)QueryOK,0rowsaffected(0.04sec)#刪除存儲過程mysql>DROPPROCEDUREp3;#修改結(jié)束符$$mysql>DELIMITER$$#mysql>CREATEPROCEDUREp3()BEGINDECLAREc_idint;DECLAREc_namevarchar(20);DECLAREc_salaryint;DECLAREflagintdefault0;DECLAREcursor3CURSORFORSELECTid,name,salaryFROMemployee_info;DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=1;OPENcursor3;WHILE(flag=0)DOFETCHcursor3INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;ENDWHILE;CLOSEcursor3;END$$#修改結(jié)束符mysql>DELIMITER;/*設(shè)置為1時，存儲過程會調(diào)用CONTINUE命令繼續(xù)查詢一次，但是調(diào)用EXIT命令時不會繼續(xù)查詢*/mysql>CALLp3();+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|1|小明|3700|+------+--------+----------+1rowinset(0.00sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|2|小紅|5700|+------+--------+----------+1rowinset(0.01sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|3|小藍|7800|+------+--------+----------+1rowinset(0.01sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|3|小藍|7800|+------+--------+----------+1rowinset(0.02sec)QueryOK,0rowsaffected(0.03sec)#刪除存儲過程mysql>DROPPROCEDUREp3;#修改結(jié)束符$$mysql>DELIMITER$$#使用EXIT進行變量判斷，如果找不到，則設(shè)置變量值為1mysql>CREATEPROCEDUREp3()BEGINDECLAREc_idint;DECLAREc_namevarchar(20);DECLAREc_salaryint;DECLAREflagintdefault0;DECLAREcursor3CURSORFORSELECTid,name,salaryFROMemployee_info;DECLAREEXITHANDLERFORNOTFOUNDSETflag=1;OPENcursor3;WHILE(flag=0)DOFETCHcursor3INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;ENDWHILE;CLOSEcursor3;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程mysql>CALLp3();+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|1|小明|3700|+------+--------+----------+1rowinset(0.00sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|2|小紅|5700|+------+--------+----------+1rowinset(0.01sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|3|小藍|7800|+------+--------+----------+1rowinset(0.02sec)QueryOK,0rowsaffected(0.03sec)mysql>3.游標的REPEAT循環(huán)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#查詢員工表employee_infomysql>SELECT*FROMemployee_info;+----+--------+--------+|id|name|salary|+----+--------+--------+|1|小明|3700||2|小紅|5700||3|小藍|7800|+----+--------+--------+3rowsinset(0.00sec)#修改結(jié)束符$$mysql>DELIMITER$$#定義存儲過程p4()，定義游標cursor4，使用REPEAT循環(huán)mysql>CREATEPROCEDUREp4()BEGINDECLAREc_idint;DECLAREc_namevarchar(20);DECLAREc_salaryint;DECLAREflagintdefault0;DECLAREcursor4CURSORFORSELECTid,name,salaryFROMemployee_info;DECLAREEXITHANDLERFORNOTFOUNDSETflag=1;OPENcursor4;REPEATFETCHcursor4INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;UNTILflag=1ENDREPEAT;CLOSEcursor4;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程p4()，循環(huán)輸出結(jié)果mysql>CALLp4();+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|1|小明|3700|+------+--------+----------+1rowinset(0.00sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|2|小紅|5700|+------+--------+----------+1rowinset(0.01sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|3|小藍|7800|+------+--------+----------+1rowinset(0.04sec)QueryOK,0rowsaffected(0.05sec)mysql>4.游標的LOOP循環(huán)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#查看員工表employee_info的數(shù)據(jù)mysql>SELECT*FROMemployee_info;+----+--------+--------+|id|name|salary|+----+--------+--------+|1|小明|3700||2|小紅|5700||3|小藍|7800|+----+--------+--------+3rowsinset(0.00sec)#修改結(jié)束符$$mysql>DELIMITER$$#創(chuàng)建存儲過程p5()，定義游標cursor5，使用LOOP循環(huán)mysql>CREATEPROCEDUREp5()BEGINDECLAREc_idint;DECLAREc_namevarchar(20);DECLAREc_salaryint;DECLAREflagintdefault0;DECLAREcursor5CURSORFORSELECTid,name,salaryFROMemployee_info;DECLAREEXITHANDLERFORNOTFOUNDSETflag=1;OPENcursor5;loop_label:LOOP#循環(huán)開始FETCHcursor5INTOc_id,c_name,c_salary;SELECTc_id,c_name,c_salary;IF(flag=1)THENLEAVEloop_label;#循環(huán)終止ENDIF;ENDLOOP;CLOSEcursor5;END$$QueryOK,0rowsaffected(0.00sec)#修改結(jié)束符mysql>DELIMITER;#調(diào)用存儲過程p5()mysql>CALLp5();+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|1|小明|3700|+------+--------+----------+1rowinset(0.00sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|2|小紅|5700|+------+--------+----------+1rowinset(0.02sec)+------+--------+----------+|c_id|c_name|c_salary|+------+--------+----------+|3|小藍|7800|+------+--------+----------+1rowinset(0.04sec)QueryOK,0rowsaffected(0.08sec)mysql>實訓