MySQL数据库函数

1、昨日回顾视图：视图是一个虚拟表，在使用与表相同，只是存储了SQL，实际的数据存储在基表中。创建视图：create view 视图名 as select * from 表名;show table/show views/desc/show columns from 表名/show create table 视图名删除视图：drop view 视图名对视图的操作：单表视图：可以进行操作，增、删、改、查多表视图：不可以进行操作。视图算法：算法：由于产生视图需有一个SQL，查询视图还需一个SQL。undefined：默认的，由MYSQL自行决定使用哪种算法。很多时候执行mergemerge：合并产生视图

2、的SQL与查询视图的SQL，再执行temptable：两个SQL分别执行。子查询：标量子查询产生的就是一个基本的值列子查询产生1多行，产生1个条件多个值的情况，使用in行子查询产生1行多列，产生多个条件的多个值，使用 =表子查询产生的是多行多列的结果集，只能作数据源。书写的顺序：1、先写最终产生的结果的SQL2、再将子查询放到1步的条件处。联合查询sql语句A union【all|distinct】 sql 语句B使用limit 99999连接查询交差连接cross join如果没有条件，迪卡尔集，内连接【inner】 join 如果没有条件产生迪卡尔集外连接必须有条件，条件使用on左外连接特

3、点：左边的表是主表，产生的结果，右外连接自然连接自动根据同名字段作为匹配条件。备份：1、文件备份innodbibdata1与.frm文件myisam.frm、myd、myi2、sql备份，备份的是sql语句mysqldump.exe工具，此工具的运行环境cmd备份：mysqldump -u -p 数据库名 表名 > 目标文件还原：mysql -u -p 数据名 表名 < 源文件source运行环境：mysqlsource 源文件3、数据备份，备份的是数据备份：select *或字段名 from 表名 into outfile '文件名'还原：load data lo

4、cal infile '文件名' into table 表名事务事务的本质就是延缓SQL语句对数据库的数据的更改现实中的事务，可能由多个步骤来完成，事务的成败，必须由多个步骤来同时决定。MySQL是解决现实的问题，现实中的事务成败，反应到MySQL中的也由多个步骤(SQL语句)组成，事务的成败就由多个SQL语句共同决定。示例：银行转账现实中的一件事：用户A向用户B转100MySQL中1、A帐户减少钱2、B帐户增加钱可能的结果：A帐户减少钱了，B帐户没有增加A帐户没有减少，B帐户增加了A帐户没减少，B帐户也没有增加A帐户减少，B账户增事务安全：事务由多条SQL组成，要么都成功，要

5、么都失败。事务分类MySQL中的事务分两类：自动事务、手动事务自动事务默认的，每一条SQL语句都会立即对数据库产生更改。绝对大多数情况，都是单条SQL语句执行。手动事务查看事务设置show variables like 'autocommit'示例：说明：on表示自动提交，此处的提交，是DBMS收到并执行SQL后，自动对数据库中的数据产生更改。更改autocommit来禁止自动提交示例：手动事务延缓事务的自动提交start transaction;开启事务commit;提交当使用start transaction开启的事务，使用commit后，事务就结束了，其后的sql都会自动

6、提交(前提autocommit=1);示例：示例：rollback 【to 回滚点】示例：savepoint设置回滚点由于事务中会有多条SQL，但是某一些sql的执行，就算事务中其后的sql没有成功，那么有也是有意义(需要对数据库进行更改)，此时就可以使用回滚点来保存事务中的某个位置。示例：事务的原理：自动事务原理：后动事务原理：事务的特点：ACID原子性Atomicity：事务中的所有的SQL被当成一个整体，即是整体就不可以再分，要么都成功，要么都失败。一致性Consistency：事务在执行的过程中，对数据库的没有影响隔离性Isolation：两个客户端中的事务执行时，数据互不影响。持久性

7、Durability：事务一旦提交，那么影响就是永久的。锁：变量MySQL是一门编程语言。所以存在变量、流程控制、函数、存储过程、触发器MySQL分系统变量，与自定义变量MySQL的某些功能是通过系统变量来实现的。例如：autocommit查看系统变量：show variables【like 'pattern'】;查看系统变量的值：select 系统变量名;设置系统变量：进程级设置只针对当前客户端，关闭之后就失效，对其他客户端没影响。语法：set 系统变量名=值;全局设置对设置之后的所有的新连接进来的客户端。(当前客户端也需要重新连接)语法：set global 系统变量名=值

8、;示例：触发器触发器的器就是绑定一组SQL语句，触发器的触发就是在某一时刻，某一对象，的某个范围上发生某一事件时，执行触发器绑定的那一组SQL语句。某一时刻：before、after某一事件：insert、update、delete某一对象：某个表某个范围：记录或表一个表上最多可以设置6个触发器创建触发器语法：第1步：更改结束符delimiter 符号第2步：定义触发器create triggers 触发器名 时刻 事件 on 对象 【for each row】;create triggers 触发器名 before|after insert|delete|update on 表 【for e

9、ach row】;begin- sql语句end/第3步：更改结束符delimiter ;需求：一个定单表，库存表创建触发器查看触发器语法：show triggers;查看触发器创建的语句show create trigger 触发器名示例：触发器的使用：触发器的记录当触发器执行时，对触发的表的记录进行操作时，会进行相应记录；操作之前会记录在一个对象上(old或:old)，操作之后也会记录一个对象(new或:new)。操作：oldnewinsertnull新的记录update原记录信息新记录的信息delete原记录信息null如何获取原记录或新记录的字段信息？语法：old或new.字段名old

10、.idold.numnew.idnew.num示例：操作：【for each row】表示每一行都会触发一次。触发触发器的sql可能一次更改多条记录。for each row表示每产生或更新一条记录触发器执行一次。如果省略，表示无论更改多少条记录，只触发一次。删除触发器drop trigger 触发器名函数MySQL函数分为系统函数与自定义函数系统函数：pow(m,n)求m的n次方rand()随机获取0至1之间的小数floor();向下取整ceil();向上取整sqrt()平方根自定义函数第1步：修改结束符delimiter 结束符;第2 步：定义函数语法：create function 函数

11、名(参数1 类型,参数2 类型 .) returns 类型begin/函数体return 值;end结束符第3步：还原结束符。delimiter 结束符示例：查看函数语法：show function status;示例：查看函数的创建语句show create function 函数名;函数的调用select 函数名(实参);示例：自定义变量：语法：set 变量名=值;示例：变量赋值"="在MySQL叫赋值符号，也叫比较符号方法1：方法2：使用select结果为变量赋值方法3：使用:=s示例：注意：函数内部不能有select语句，如下示例作用域：MySQL分全局作域与局部作

12、用域全局作用域，在函数外部定义的变量其作用域称之为全局作用域，作用范围就函数内部与函数外部示例：set v=100;局部作用域：在函数内部使用declare声明的变量其作用域为局部作用域，作用范围在函数内。语法：declare 变量名 类型 【default 值】;示例1：全局变量示例2：局部变量函数的删除drop function 函数名;示例：流程控制分支结构if分支结构语法：if 条件then- 语句体else- 缺省语句体end if;示例：循环结构while循环语法：while 表达式 do- 循环体end while;示例：需一个数的累加和注意：对任何变量的更改必须使用set，没有

13、自操作运算符存储过程将完成某一功能的多个步骤存储起来，达到一次定义多次调用。就是没有返回值的函数。创建第1步：更改结束符第2步：创建过程create procedure 过程名(方向 参数1 类型,方向 参数2 类型.) begin- 过程体end;第3 步：还原结束符形参的方向：insoutinout示例：查看存储过程：show procedure status;示例：存储过程的调用语法;call 过程名(实参);示例：过程的形参方向in表示只能将数据传递给形参，不能传出out表示只能将数据传递出来，但不能接收传递进来的数据inout即可以接收传递进来的数据，又可以将数据传递出去。out与i

14、nout是引用传方式，所以对于out与inout类型的形参对应实形必须使用变量。示例1：函数与存储过程的区别：1、函数必须有return2、存储过程不能有return3、函数体内不可以有select4、存储过程体内可以有select5、函数形参没有方向6、存储过程的形参具有方向权限管理创建用户语法：create user '用户名'【'主机名'】【identified by '密码'】;示例：说明：用户名必须使用引号'主机名'可以是以下几种：ip表示只允许新创建的用户在指定ip上登陆localhost表示只允许新创建的用户在服务器

15、上登陆%表示没有限制密码必须加引号新创建的用户没有任何权限添加权限语法：grant 权限 on 数据库【.数据表】 to 用户;示例：权限：所有权限all或all privilegs结构权限create、alter、drop数据权限update、delete、insert查询权限select外键权限references移除权限语法：revoke 权限 on 数据库【.数据表】 from 用户;sql代码：drop table if exists goods;create table goods(id tinyint unsigned primary key auto_increment,bra

16、nd char(16),goodName char(16),channel char(8),price decimal(10,2),inventory smallint unsigned);insert into goods values(default,'huawei','mate','3G',2300,30);insert into goods values(default,'apple','6plus','4G',4500,43);insert into goods values(defaul

17、t,'apple','6splus','4G',5100,20);insert into goods values(default,'samsung','galaxy6','4G',3800,28);insert into goods values(default,'huawei',null,'3G',3200,15);insert into goods values(default,'apple','5c','3G',1800,38);insert into goods values(default,'huawei','mate8','4G',3500,51);insert into goods values(default,'huawei','p8Max','4G',2700,27);insert into goods values(default,'apple','5s',&