Mysql数据库、触发器，变量，作用域，结构，存储过程，用户管理

触发器事先写好一段代码与某个表中的某条记录进行绑定然后在记录中发生某些指定的事情的受这段代码就会自动的执行。需求商品库存管理商品表和订单表订单会包含商品信息如果订单增加意味着商品库存就应该减少订单退单商品库存应该增加。真实项目数据库设计思路1.先新增订单信息获取订单id2.通过订单id得到当前订单信息可以省略3.通过订单信息修改商品库存触发器组成触发时间事件类型要做的事情触发对象创建触发器语法create trigger 触发器名字 触发时间 触发事件 on 表名触发对象 for each rowbegin要做的事情//要做的事情都是SQL语句每行SQL语句都需要结束符号分号end触发时间前和后before数据被操作之前写数据表之前after数据被操作之后数据写入到数据表之后触发事件增删改insert插入数据delete删除数据update修改数据一张表中最多可以有6个触发器。一张表中只能由一种同类型触发器。语句结束符delimiter 临时语句结束符//所有内容都不会以分号结束系统直到碰到临时语句结束符delimiter ;//系统又重新已分号作为语句结束符使用触发器触发器不需要手动的调用只要对应表的操作满足条件之后系统会自动的调用触发器执行里面的内容。查看触发器语法show triggers;查看单独触发器show triggers like ‘pattern’查看触发器创建语句show create trigger 触发器名字修改触发器触发器不能修改只能先删除后新增删除触发器语法drop trigger 触发器名字;获取触发语句内容触发器中系统会自动的获取到当前被操作记录的原始值和操作后的新值old和newold代表当前表中尚未执行SQL语句前的原始记录新增数据没有old记录在before insert触发器中不能使用old关键字new代表当前表中的记录已经SQL执行修改过后的数据删除数据没有new记录在after delete中不能使用new关键字即便当前SQL并没有真正执行new也能得到执行后的结果访问记录中的字段的语法old/new.字段名效果作业1.一张表最多能放多少个触发器6个2.哪些触发器中不可以使用old或者new3.insert on duplicate会触发多少个触发器假设6个都全。replace into又会触发多少个变量在SQL中变量分为两种系统变量和自定义变量系统变量系统在启动的时候已经定义好的变量用户只需要使用就行了。1.有哪些系统变量show variables;2.如何读取系统变量的值语法select 系统变量名;3.如何修改系统变量语法set 变量名 值;以上修改只有当前用户当次连接是有效的断开连接就失效。会话级别全局生效整个服务器的环境不管是哪个客户端什么时候连接都生效set global.变量名 值;如果想要全局修改最好的办法是修改配置文件my.ini自定义变量用户自己定义的变量。定义变量语法1普通设置set 变量名 值;语法2使用专门赋值语法set 变量名 : 值;语法3批量定义select 字段列表 from 表名 into 变量列表;select 字段列表 into 变量列表 from 表名;select变量 : 字段,列表 from 表名;使用以上三种方式定义变量的必须要满足条件1.字段数与变量的数量必须一致2.select查询得到的记录结果必须只能是一条记录注意虽然系统会保存但是系统会自动将第一条记录对应的结果给变量赋值访问变量与系统变量访问的方式类似。select 变量名;修改变量set 变量名 值;删除变量SQL中不能删除变量只能将变量置空函数1.什么是函数实现某一功能的代码块能够允许被重复利用。2.函数要素有哪些关键字函数名参数形参和实参函数体返回值SQL是一种强类型语言函数一定需要指定返回值。函数分为系统函数和自定义函数。自定义函数创建函数语法create function 函数名(参数列表[参数列表必须指定参数类型]) returns 返回值类型begin//函数体//返回值//如果一个函数体触发器和存储过程里面只有一条语句可以不使用begin和end进行包含如果没有begin和end那么也不需要使用delimiter来指定临时语句结束符end调用函数函数一定有返回值所以函数的调用就是使用select语法select 函数名(实参列表);查看函数语法show function status;查看具体函数show function status like ‘pattern’;修改函数函数也不能被修改只能先删除后新增删除函数drop function 函数名;作用域在SQL中作用域分为两种全局作用域和局部作用域全局作用域所有地方都可以使用对应的变量set 变量全局作用域局部作用域只能能函数内部使用的变量。语法在函数内部定义declare 变量名 数据类型 default 值;结构指的是代码执行的顺序。在mysql中也有三种结构顺序分支和循环分支结构有多个代码块但是只能根据一个条件来确定到底执行具体的哪一个。mysql中用if分支来实现按条件执行代码语法if 条件 then//要执行的符合条件的代码块else//不符合条件的代码块end if;局部变量只能在函数内部使用循环结构让一堆代码重复执行N次while循环和do-while循环语法while 条件 do//循环体//条件变更end while;循环中断在SQL中没有continue也没有break有自己的类似语法iterate与continue一致当前循环以下的代码不再执行leave与break一样跳出循环不再执行语法iterate/leave 循环名字;整体语法循环名字:while//循环体//中断语法iterate/leave 循环名字;end while;参数传递当函数在调用的时候使用实参给形参赋值。在SQL中函数只能是值传递不能是引用传值。但是可以在函数内部使用外部的全局变量而且能够对外部全局变量进行修改。系统函数定义全局变量substring字符串截取char_length获取字符串的字符数length获取字符串的字节数instr判断一个子字符串是否在字符串中出现lpad填充左填充字符串到指定长度insert替换目标字符串中的部分字符串strcmp比较字符串的大小按照校对集存储过程一个本质与函数差不多的东西存储过程是为了解决某个问题而存在比函数更有针对性。存储过程与函数的区别1.存储过程没有返回值函数必须有2.调用方式不一样3.传参方式不一样创建存储过程语法create procedure 过程名字(参数列表)begin//过程体凡是在函数中能使用的基本在过程中都能使用end调用存储过程存储过程没有返回值不能使用select语法call 过程名字(实参列表);修改存储过程alter procedure 存储过程名字 过程体不能修改存储过程应该先删除后新增查看存储过程语法show procedure status [like ‘pattern’];删除存储过程drop procedure 过程名字存储过程参数存储过程有自定义的三个参数类型在原来的参数的数据类型之外in表示外部的数据可以在内部使用内部的赋值不会对外部变量产生修改值传递out表示数据是过程内部对其进行赋值不能直接使用外部传进来的数据外部传进来的任何数据都会先置空内部的赋值可以供外部使用引用传递inout集合了in和out的特性允许在内部使用也允许内部对其进行修改引用传递语法在定义的时候必须申明参数的参数类型in/out/inout 参数名 数据类型效果1都采用数值常量作为实参效果2都采用变量作为实参存储过程使用全局变量定义存储过程访问效果存储过程参数赋值的过程索引索引指的是一种文件文件里面保存了一个数据表中按照某种组合形成的一种规则。索引的作用是通过快速定位找到对应的数据记录从而节省查询或者操作的时间。mysql中的索引唯一索引unique key主键索引primary key普通索引index全文索引fulltext针对一篇很多内容的文章如何快速的定位到要查询的关键字。分词将一段话分成一个一个的词语英文分词很简单直接使用空格中文分词很麻烦。中文分词系统sphinx斯芬克斯mysql用户管理默认的mysql只对外提供了一个用户root拥有的是最大的权限超级管理员权限。创建用户语法create user 用户 identified by ‘明文密码’;用户用户名主机地址主机地址可以是ip也可以是域名可以使用%进行模糊匹配明文密码密码系统会自动进行加密sha1老版本加密的md5加密网传新的mysql所谓的sha1其实是md5两次加密系统发生了什么1.系统创建了用户mark2.系统保存了mark用户的信息mysql-user表使用用户登录只有客户端电脑的ip在192.168.3.%区间之内的用户能够登录系统权限分配语法grant 权限列表 to 用户 on 库.表(*表示全部表)效果权限列表给定全部权限grant all privileges on 库.表 to 用户回收权限语法revoke 权限列表 on 库.表* from 用户强制刷新权限flush privileges;删除用户语法drop user 用户;删除的用户需要退出之后才会生效注意一般情况下一个大公司数据库有很多项目数据库由专门DBA管理DBA会创建一个admin用户拥有全部权限但是不使用rootDBA都会用admin用户来管理数据库。root用户只是用于做不时之需。如果root用户密码忘了怎么办找回root密码方案一重新安装mysql安装前备份数据不到万不得已不会采用这种方式方案二重置root用户密码1.关闭mysql服务2.重启mysql服务mysqld.exe通过命令跳过权限验证。mysqld.exe –skip-grant-tables3.进入数据库使用无用户进入4.修改root用户密码5.重启服务器net start mysql6.使用新用户密码登录注意1.修改root用户密码很不安全a)在修改root的时候选择了跳过权限验证所有的用户只要能连接该ip地址主机都能通过mysql命令进入数据库而且拥有最高权限。通常开启防火墙不允许外部用户进行访问要在访问量最小的时候晚上进行操作做操作先做全库的备份备份数据库文件。