MySql高级教程视图1、视图的定义2、视图的作用3、查询视图4、修改视图5、删除视图6、查看视图结构7、查看所有视图8、视图与表的关系9、
视图的算法广州传智播客教育gz.itcast.cn1、视图的定义视图的定义：视图是由查询结果形成的一张虚拟表，是表通过某种运算得
到的一个投影。创建视图的语法：createviewviem_nameasselect语句2、视图的作用（1）可以简化查询
。案例：查询平均价格前3高的栏目。原来select语句selectcat_id,avg(shop_price)aspjfr
omgoodsgroupbycat_idorderbypjdesclimit3;创建视图createview
lmjasselectcat_id,avg(shop_price)aspjfromgoodsgroupbyc
at_id;查询视图的方式。selectfromlmjorderbypjdesclimit32、视图的作用（2
）可以进行权限控制把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据，比如某张表，用户表为例，2个网站搞合作，可以查询对
方网站的用户，需要向对方开放用户表的权限，但是呢，又不想开放用户表中的密码字段。比如不让看shop_price。3、查询视图语法：
selectfrom视图名[where条件]视图和表一样，可以添加where条件4、修改视图alterviewv
iew_nameasselectXXXX5、删除视图dropview[ifexists]view_name,[vie
w2,view3,…];6、查看视图结构desc视图名称7、查看视图showtables8、视图与表的关系视图是表的查询结果，
自然表的数据改变了，影响视图的结果。（1）视图的数据与表的数据一一对应时，可以修改。（2）视图增删该也会影响表，但是视图并不是总是
能增删该的。createviewlmjasselectcat_id,max(shop_price)aslmjfro
mgoodsgroupbycat_id;mysql>updatelmjsetlmj=1000wherecat_
id=4;ERROR1288(HY000):ThetargettablelmjoftheUPDATEisno
tupdatable（3）对于视图insert还应注意，视图必须包含表中没有默认值的列。9、视图算法algorithm=mer
ge/temptable/undefinedmerge：当引用视图时，引用视图的语句与定义视图的语句合并（默认）。temptabl
e：当引用视图时，根据视图的创建语句建立一个临时表。undefined：未定义，自动让系统帮你选。merge：意味着，视图只是一个
语句规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并，分析，形成一条select语句
。temptable：是根据创建语句瞬间创建一张临时表，然后查询视图的语句，从该临时表查数据。#在创建视图时的语句：wheres
hop_price>1000;#查询视图时，whereshop_price<3000;#那么查此视图时，真正发生的是where
(selectwhere)and(viewwhere)#whereshop_price<3000andshop_p
rice>1000;#分析出最终语句还是去查goods表SQL编程1、变量声明2、运算符3、语句块包含符4、if判断5、cas
e判断6、循环SQL编程1、变量声明（1）会话变量定义形式：set@变量名=值；说明：1，跟php类似，第一次给其赋
值，就算定义了2，它可以在编程环境和非编程环境中使用！3，使用的任何场合也都带该“@”符号。SQL编程1、变量声明（2）普通变量
定义形式：declare变量名类型【default默认值】；说明：1、它必须先声明（即定义），此时也可以赋值；2、赋值
跟会话变量一样：set变量名=值；3、它只能在编程环境中使用！！！说明：什么是编程环境？SQL编程1、变量声明（3）变
量赋值形式语法1：set变量名=表达式;#此语法中的变量必须先使用declare声明语法2：set@变量名=表达式；#此方
式可以无需declare语法声明，而是直接赋值，类似php定义变量并赋值。语法3：select@变量名:=表达式；#此语句会给该
变量赋值，同时还会作为一个select语句输出‘结果集’。语法4：select表达式into@变量名;#此语句虽然看起来是s
elect语句，但其实并不输出‘结果集’，而是给变量赋值。SQL编程2、运算符（1）算术运算符+、-、、/、%注意：mysq
l没有++和—运算符（2）关系运算符>、>=、<、<=、=（等于）、<>（不等于）（3）逻辑运算符and（与）、or（或）、no
t（非）SQL编程3、语句块包含符所谓语句块包含符，在js或php中，以及绝大部分的其他语言中，都是大括号：{}它用在很多场合：
if，switch,for,function而mysql编程中的语句块包含符是。SQL编程4、if判断MySQL支
持两种判断，第一个是if判断，第二个case判断if语法单分支if条件then //代码endif;双分支if条件
then 代码1else 代码2endif;多分支if条件then 代码1elseif条件then 代
码2elseendif;SQL编程5、case判断case变量when值then语句;when值then语句
;else语句;endcase;SQL编程6、循环MySQL支持的循环有loop、while、repeat循环（1）
loop循环标签名:loop leave标签名--退出循环endloop;SQL编程6、循环MySQL支持的循环有loo
p、while、repeat循环（2）while循环[标签：]while条件do //代码endwhile;SQL编程6
、循环MySQL支持的循环有loop、while、repeat循环（3）repeat循环repeat //代码until条
件endrepeat;存储过程1、概念2、存储过程的优点3、创建存储过程4、调用存储过程5、删除存储过程6、创建复杂的存储过程
7、declare声明局部变量8、用户变量9、系统变量1声明是存储过程存储过程(procedure)概念类似于函数，就是把一段代码
封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。在封装的语句体里面，可以同if/else,case,while
等控制结构。可以进行sql编程。查看现有的存储过程。showprocedurestatus广州传智播客教育gz.itcast.
cn2存储过程的优点存储过程（StoredProcedure）是在大型http://baike.baidu.com/view/7
809.htm数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过
指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序
都应该用到存储过程。（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,
所以使用存储过程可提高数据库执行速度。（2）当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Del
ete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。（3）存储过程可以重复使用,可减少数据库开发人员的
工作量（4）安全性高,可设定只有某些用户才具有对指定存储过程的使用权广州传智播客教育gz.itcast.cn3创建存储create
procedure存储过程名(参数,参数,…)begin //代码end存储过程的参数分为输入参数(in)、输出参数(out)
、输入输出参数(inout),默认是输入参数。如果存储过程中就一条语句,begin和end是可以省略的。案例1：查询一个表里面某些
语句案例2：第二个存储过程体会参数广州传智播客教育gz.itcast.cn3、调用存储过程语法：call存储过程()4、删除存储
过程语法：dropprocedure[ifexists]存储过程名广州传智播客教育gz.itcast.cn5、创建复杂的存
储过程案例3：带输出参数的存储过程案例4：带有输入输出参数的存储过程6、declare声明局部变量（1）declare声明局部变量
的（2）语法：declare变量名数据类型[defaultvalue];（3）通过select字段into变量或s
et给变量赋值（4）变量名不可以和表的字段名一样。体会循环，计算1到n的和广州传智播客教育gz.itcast.cn7、用户变量用户
变量只要在前面加一个@符即可set@name=’李白’;select@name;8、系统变量MySQL启动的时候就存在的变量，
以@@开头的都是系统变量select@@version$函数1、自定义函数2、系统函数函数1、自定义函数（1）定义语法creat
efunction函数名(参数)returns返回值类型begin //代码end函数1、自定义函数（2）调用跟系统函数
调用一样：任何需要数据的位置，都可以调用该函数。?案例1：返回两个数的和案例2：定义一个函数，返回1到n的和。函数2、系统函数（1
）数字类mysql>selectrand();//返回0到1间的随机数mysql>selectfromit_goods
orderbyrand()limit2;//随机取出2件商品mysql>selectfloor(3.9)//输出3my
sql>selectceil(3.1)//输出4mysql>selectround(3.5)//输出4四舍五入函数2、系统
函数（4）时间类mysql>selectunix_timestamp()//--时间戳mysql>select
from_unixtime(unix_timestamp())//--将时间戳转成日期格式curdate();返回今天的时间
日期：mysql>selectnow()// --取出当前时间函数2、系统函数（4）时间类其他函数：find_in_s
et(str1,str2)函数扩展，如何取出昨天或者指定某个时间的电影：date_sub和date_add函数：基本用法：date
_sub(时间日期时间,interval数字时间单位)说明：（1）时间单位：可以是yearmonthdayhourmi
nutesecond（2）数字：可以是正数和负数。比如：取出昨天的日期：mysql>selectdate_sub(curda
te(),interval1day);触发器1、简介2、触发器四要素3、创建触发器4、删除触发器5、查看触发器6、before
和after的区别1触发器简介（1）触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动
执行的代码块。（2）触发器必须定义在特定的表上。（3）自动执行，不能直接调用，作用：监视某种情况并触发某种操作。触发器的思路：监
视it_order表，如果it_order表里面有增删改的操作，则自动触发it_goods里面里面增删该的操作。比如新添加一个订单
，则it_goods表，就自东减少对应商品的库存。比如取消一个订单，则it_goods表，就自动增加对应商品的库存减少的库存。2触
发器4要素3创建触发器创建触发器的语法：createtriggertrigger_nameafter/beforein
sert/update/deleteon表名foreachrowbeginsql语句：（触发的语句一句或多句）end
案例1：第一个触发器，购买一头猪，减少1个库存。案例2：购买商品，减少对应库存案例3：取消订单时，减掉的库存要添加回来案例4：修
改订单时，库存也要做对应修改(修改购买数量)案例5：修改订单时，库存也要做对应修改(修改商品类型)3创建触发器如果在触发器中引用行
的值。对于insert而言，新增的行用new来表示，行中的每一列的值，用new.列名来表示。删除触发器的语法：droptrig
gertg13创建触发器目前mysql不支持多个具有同一个动作，同一时间，同一事件，同一地点，的触发器查看创建的触发器show
triggers\G4删除触发器语法：droptrigger触发器的名称5、查看触发器语法：showtriggers6be
fore与after的区别after是先完成数据的增删改，再触发，触发器中的语句晚于监视的增删改，无法影响前面的增删该动作。就类似
于先吃饭，再付钱。before是先完成触发，再增删改，触发的语句先于监视的增删改发生，我们有机会判断修改即将发生的操作。就类似于先
付钱，再吃饭典型案例：对于已下的订单，进行判断，如果订单的数量>5，就认为是恶意订单，强制把所定的商品数量改成5案例2查询积压货
款前3高的栏目，案例3：查询出商品表，以及所在的栏目名称；createviewit_goods_v2asselectid
,goods_namefromit_goods;grantselectonwhshop.it_goods_v2to
''xiaowei''@''%''identifiedby''1234'';createtableuser(idintnot
null,namevarchar(32)notnull,agetinyintnotnull)enginemyi
samcharsetutf8;insertintouservalues(1,''xiaobai'',12),(2,''xiao
feng'',23);案例：接收4个数字，如果输入1则输出春天，2=》夏天3=》秋天4=》冬天其他数字=》出错create
procedurep7(numint)beginifnum=1thenselect‘春天’as‘季节’;elsei
fnum=2thenselect‘夏天’as‘季节’;elseifnum=3thenselect‘秋天’as
‘季节’;elseifnum=4thenselect‘冬天’as‘季节’;elseselect‘无法无天’as
‘季节’;endif;end$案例1：接收4个数字，返回季节createprocedurep8(seasonint)be
gincaseseasonwhen1thenselect''春天''as''季节'';when2thensele
ct''夏天''as''季节'';when3thenselect''秋天''as''季节'';when4thensel
ect''冬天''as''季节'';elseselect''输入不正确''as''季节'';endcase;end$drop
procedureifexistsproc;createprocedureproc(numint)begindecl
aretotalintdefault0;declareiintdefault1;aa:loopsettot
al=total+i;seti=i+1;ifi>numthenleaveaa;endif;endloop;
selecttotal;end//dropprocedureifexistsproc;createprocedure
proc()begindeclareiintdefault10;whilei>0doselectconcat(
i,'':锄禾日当午'')as''标题''fromdual;seti=i-1;endwhile;end//案例：drop
procedureifexistsproc;createprocedureproc(numint)begindecl
aretotalintdefault0;declareiintdefault1;repeatsettota
l=total+i;seti=i+1;untili>numendrepeat;set@num=total;end/
/说明：（1）存储过程中，可有各种编程元素：变量，流程控制，函数调用；（2）还可以有：增删改查等各种mysql语句；（3）其中se
lect（或show，或desc）会作为存储过程执行后的“结果集”返回；（4）形参可以设定数据的“进出方向”：注意：通常情况下，“
；“表示SQL语句结束，同时向服务器提交并执行。但是存储过程中有很多SQL语句，每一句都要以分号隔开，这时候我们就需要使用其他符号
来代替向服务器提交的命令。通过delimiter命令更改语句结束符。第二个存储过程，体会参数createprocedurep2
(numint)beginselectfromgoodswheregoods_id=num;end$mysql>c
allp2(23)$案例1，体会“控制结构”createprocedurep3(numint,jchar(1))begi
nifj=''h''thenselectfromgoodswheregoods_id>num;elsesele
ctfromgoodswheregoods_idcallp3(20
,''3'')$案例2:体会“循环”,计算1到n的和？createprocedurep4(nsmallint)begindecl
areiint;declaresint;seti=1;sets=0;whilei<=ndosets=s+i;se
ti=i+1;endwhile;selects;end$mysql>callp4(100)$案例3带输出参数的存储过程d
ropprocedureifexistsproc;createprocedureproc(numint,out
resultint)beginsetresult=numnum;end//callproc(10,@res);selec
t@res$案例4带有输入输出参数的存储过程createprocedureproc(inoutnumint)begins
etnum=numnum;end$set@res=5;callproc(@res);select@res$说明：（1）函
数内部可以有各种编程语言的元素：变量，流程控制，函数调用；（2）函数内部可以有增删改等语句！（3）但：函数内部不可以有select
（或show或desc）这种返回结果集的语句！注意：创建的函数，是隶属于数据库的，只能在创建函数的数据库中使用。（2）大小写转换m
ysql>selectucase(''Iamaboy!'')//--转成大写mysql>selectlcase
(''Iamaboy!'')//--转成小写（3）截取字符串mysql>selectleft(''abcde'',3)
// --从左边截取mysql>selectright(''abcde'',3)//--从右边截取mysql
>selectsubstring(''abcde'',2,3)//--从第二个位置开始，截取3个，位置从1开始mysql>
selectconcat(10,'':锄禾日当午'')//--字符串相连mysql>selectcoalesce(null
,123);coalesce(str1,str2)：如果第str1为null，就显示str2mysql>selectstuna
me,stusex,coalesce(writtenexam,''缺考''),coalesce(labexam,''缺考'')from
stuinfonaturalleftjoinstumarks//mysql>selectlength(''锄禾日当午''
)//输出10显示字节的个数mysql>selectchar_length(''锄禾日当午'')//输出5显示字符的个数my
sql>selectlength(trim(''abc''))//trim用来去字符串两边空格mysql>selec
treplace(''abc'',''bc'',''pache'')//将bc替换成pachemysql>selectyear(now()
)年,month(now())月,day(now())日,hour(now())小时,minute(now())分钟
,second(now())秒//mysql>selectdatediff(now(),''1997-7-1'')//两个日
期相距多少天if(表达式,值1，值2)：类似于三元运算符mysql>selectconcat(10,if(10%2=0,''偶数
'',''奇数''))//比如：取出前天的日期：mysql>selectdate_sub(curdate(),interval2
day);比如：取出后天的日期：mysql>selectdate_sub(curdate(),interval-1day)
;比如：取出一个小时之前：mysql>selectdate_sub(now(),interval1hour);mysql>selectdate_add(now(),interval-1hour);取出前两天添加的电影selectfromdede_archiveswherefrom_unixtime(senddate,''%Y-%m-%d'')=date_add(curdate(),interval-2day);注意：如果在触发器中引用行的值。对于insert而言，新增的行用new来表示，行中的每一列的值，用new.列名来表示。注意：目前mysql不支持多个具有同一个动作，同一时间，同一事件，同一地点，的触发器it_order表里面删除的行我们使用old来表示，行中的列用old.字段名称来表示。createtriggerg1afterinsertonit_orderforeachrowbeginupdateit_goodssetgoods_number=goods_number-1whereid=1;end$修改前的数据，用old来表示，old.列名引用被修改之前行中的值。修改后的数据，用new来表示，new.列名引用被修改之后行中的值createtriggerg6beforeinsertonit_orderforeachrowbeginifnew.much>5thensetnew.much=5;endif;end$ |
|
