MySQL学习笔记
视图：一个虚拟表；
概述
| 1. 视图中的数据是对现有表数据的引用；
2. 视图是从一个表或者多个表中导出的；
3. 视图中可以使用：select,insert,update,delete,,
4. 视图可以从现有视图中创建，视图一经定义便存储于数据库中；
5. 通过视图看到的数据只是基本表中的数据；
6. 视图中数据修改，基本表中数据也同时修改；
7. 基本表中的数据修改，视图中的数据也同时修改；
| 创建
| 视图创建基于select语句和基本表数据；
create [or replace] [algorithm={undefined|merge|templateble}]
view viewName[{columnList}]
as select statement
[with [cascaded|local] check option]
create 创建新视图，replace替换已有视图；
algorithm选择的算法；
viewName视图名称 ；columnList属性列；select statement选择语句；
[with [cascaded|local] check option]视图更新时保证在视图的权限范围内；
l undefined自动选择算法|
l merge将使用的视图语句和视图定义结合起来，使得视图定义的某一部分取代语句对应的部分|
l templateble将视图结果存入临时表，然后用临时表来执行语句；
Ø cascaded更新视图时需要满足所有相关视图和表的条件；（满足所有条件）
Ø local表示可更新视图时满足该视图本身定义的条件即可；（满足自身条件）
要求具有create view权限和select权限，如果replace要求drop视图权限；
视图属于数据库，默认在当前数据库创建；
要在指定数据库创建视图，需要使用完全限定名daName.viewName;
create view myView as select quantity,price,quantity*price from t;
create view myView(quantity,price,total) as select quantity,price,quantity*price from t;
create view class(id,name,class) as select student.id,student.name,stuInfo.class from student,stuInfo where student.id=stuInfo.id;
| 查看
| describe viewName;
show table status like’viewName’;show table status like ‘viewT’\g;
show create view viewName;show create view myView;
select*from information_schema.views;
| 修改
| create or replace view myView as select*from t;
可以用create or replace修改，语法同创建；
alter view myView as select quantity from t;
| 更新
| update view myView set quantity=5;
insert into t values(3,5);更新表，视图会跟随更新；
delete from myView where price=5;
视图中的删除是通过删除基本表中的数据实现的；
| 删除
| drop view [if exists]viewName[,...] [restrict|cascade]
可以同时删除多个视图，逗号隔开；必须要有drop权限；
drop view if exists myView;
|
存储程序=存储过程+存储函数
创建
| create procedure create function
■■■存储过程
create procedure pName([pParameter])[characteristics...]routineBody;
create procedure 过程名([参数])[特性...]过程体;
参数：[in|out|inout]pParameter输入、输出、可入可出
特性：characteristics
[table] | ①语言特性language sql ②结果是否确定[not]deterministic
③使用SQL语句的限制：{constrains sql|no sql|reads sql data|modifies sql data}
l constrains sql表明子程序包含SQL语句，但是不包含读写数据的语句；（默认）
l no sql表明子程序不包含SQL语句；
l reads sql data子程序包含读写数据的语句；
l modidies sql data包含读写数据的语句；
④指明谁拥有权限来执行sql security{definer|invoker}
l definer表示只有定义者才能执行；（默认）
l invoker表示拥有权限的调用者可以执行；
⑤comment ‘string’注释信息，用来描述存储过程或函数；
|
示例
create prodedure avgAge()
begin
select avg(age) as avgAge from student;
end;
| delimiter //
create procedure proc()
begin
select*from student;
end//
delimiter;
|
delimiter//将MySQL结束符设置为//，保证procedure的完整性，完事后，再用delimiter；将结束符设置为默认值分号；delimiter也可以指定其他符号作为结束符；
delimiter应该避免使用反斜杠\,因为\是SQL的转义字符；
create procedure CountProc (out count int)
begin
select count(*)into count from student;
end;
| create funtion myFunc() returns char(50)
return (select name from student where age=22);
|
■■■存储函数
create function funcName([parameter]) returns type [characteristic...]routineBody
create function funcName([参数]) returns type [特性]函数体
如果定义的返回值类型与实际返回值类型不同，系统会自动转型；
只能对procedure指定参数类型in/out/inout;
不能对function指定参数类型in/out/inout，function参数默认in;
[/td][/tr][tr][td=1,1,93]变量
[/td][td=1,1,796]■■■变量
声明语句：declare varName[,varName2]...dataType[default value];
declare 变量名 dataType[default 默认值];
declear count int default 0;
默认值可以指定为一个常量，也可以指定为一个表达式；
如果不指定默认值，初始值就是null；
赋值语句：set varName=expr[,varName2=expr2]...;
declare a,b,c int;set a=1,b=2;set c=a+b;
select colName[,...]into varName[,...] tableExpr;
declare fruitName char(50);
declare fruitPrice decimal(8,2);
select name,price into fruitName,fruitPrice
from fruits where id=’a1’;
|
[/td][/tr][tr][td=1,1,93]异常
[/td][td=1,1,796]■■■异常条件：将一个名字和一个错误条件相关联
意义：保证过程/函数在遇到警告或错误时能够继续运行，避免程序异常停止；
declare conditionName condition for [conditionType]
l [conditionType]:sqlstate[value]sqlstateValue|mysqlErrorCode
l conditionName 条件名称conditionType条件类型
l sqlstateValue错误代码-长度为5的字符串
l mysqlErrorCode错误代码-数值类型
declare command_not_allowed condition for sqlstate’42000’;
declare command_not_allowed condition for 1148;
■■■定义异常处理程序：
declare handlerType handler for conditionValue[,...] spStatement
handlerType:continue|exit|undo
conditionValue:sqlstate[value]sqlstateValue
|conditionName|sqlWarning|notFound|sqlException|mySqlErrorCode
handlerType错误处理方式，参数可取3个值continue|exit|undo
l continue表示不处理错误，继续执行|
l exit表示遇到错误马上推出|
l undo表示遇到错误后，撤回之前的操作，MySQL中断暂时不支持这样的操作；
conditionValue表示错误类型，可以取值如下：
l sqlstate[value]sqlstateValue包含5个字符的错误字符串；
l conditionName表示declare condition定义的错误条件名称；
l sqlWarning匹配所有以01开头的sqlstate错误代码；
l notFound匹配所有以02开头的sqlstate错误代码；
l sqlException匹配所有未被sqlWarning和notFound匹配的错误代码；
l mySqlErrorCode匹配数值型错误代码；
① 捕获字符串错误码sqlStateValue
declare continue handler for sqlstate’42s02’set@info=’no_such_table’;
② 捕获数字错误码mysqlErrorCode
declare continue handler for 1146 set@info’no_such_table’;
③ 捕获自定义错误码
declare no_such_table conditon for 1146;
delare continu handler for no_such_table set@info=’no_such_table’;
④ 捕获警告错误码sqlWarning
declare exit handler for sqlwarning set@info=’error’;
⑤ 捕获未找到错误码notFound
declare exit handler for not found set@info=’no_such_table’;
⑥ 捕获数据库异常错误码sqlException
declare exit handler for sqlexception set @info=’error’;
[/td][/tr][tr][td=1,1,93]光标
[/td][td=1,1,796]光标：用来逐条读取大量数据
顺序：声明条件和变量→声明光标→声明处理程序
顺序：声明declare→打开open→使用fetch→关闭close
declare cursorName cursor for selectStatement
declare cursorFruit sursor for select name,price from fruits;
open cursorName;open cursorFruit;
fetch cursorName into varName[,varName2...]
fetch cursorFruit into name ,price;
close cursorName;close cursorFruit;
[/td][/tr][tr][td=1,1,93]流程
[/td][td=1,1,796]流程控制：条件转移语句
if,case,loop,while,leave,iterate,repeat,,,
每个流程控制，可以包含单个语句，可以包含复合语句（begin end），可以嵌套；
if exprCondition then statement;
[elseif exprCondition then statement2]...;
[else statementN];
end if;
| if val is null
then select ‘val is null’;
else select ‘val is not null’;
end if;
| case expr
when value1 then statement1
[when value2 then statement2]...
[else statementN]
end case;
| case val
when 1 then select’val is 1’;
when 2 then select’val is 2’;
else select ‘val is not 1 or 2’;
end case;
| case
when expr then statement
[when expr2 then statement2]...
[else statementN]
end case;
| case
when val is null then select’val is null’;
when val0 then select’val is greater than 0’;
else select’val is 0’;
end case;
| [loopLabel:]loop
statement
end loop [loopLabel]
| declare id int default 0;
addLoop:loop
set id=id+1;
if id>=10 then leave addLoop;
end if;
end loop addLoop;
|
leave用来退出任何流程控制构造；leave label;
iterate用来跳转到开头处；可以放在loop,while,repeate内，表示再次循环；iterate label;
[repeatLabel:]repeat
statement
until expr
end repeat[repeatLabel]
| declare id int default 0;
repeat
set id=id+1;
until id>=10
end repeat;
|
until后面是循环结束的条件，如果条件为真，则结束循环，条件为假，继续循环；
[table][tr][td=1,1,389][whileLabel:]while expr do
statement
end while[whileLabel]
[/td][td=1,1,389]declare i int default 0;
while i |
