视图、触发器、事务、存储过程、函数
1.视图
1.什么是视图?
每个查询语句的结果都是一张虚拟表,而将这些虚拟表保存下来,它就变成了一张视图。
2.为什么要使用视图?
当频繁的使用到某几个表的连表结果,我们可以事先把这几个连表弄成视图,需要的时候就可以直接调用,避免的重复的写操作连表的sql语句。
3.如何创建一个视图?
create view 视图名 as select * from 表1 inner join 表2 on 表1.字段 = 表2.字段
注意:
1).视图里面只有表结构,视图里面的数据完全来源于之间的表。
2).不要轻易改动视图里面的数据,
3).一般情况下不要频繁使用视图来写业务逻辑,因为视图的创建也会消耗内存资源。
2.触发器
1.定义:到达某个条件会自动触发。当对数据进行增,删,改的情况下便会触发触发器的运行。注意:没有查询。
2.触发器的六种情况
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
3.删除触发器
drop trigger 触发器的名字 ;
3.事务
1.定义:事务包含一大堆sql语句,并将这些sql语句作为原子操作,一旦其中某个出现错误,就要滚回到原来的状态,从而保证数据库数据的完整性。
2.事务的四大特性:A,C,I,D
A:原子性。C:一致性。I:隔离性。D:持久性(永久性)。
原子性:一个事务是一个不可分割的工作单位,事务里的操作要么都做,要么都不做。
一致性:事务必须要使数据库从一个一致性状态到另一个一致性状态。一致性与原子性是密切相关的。
隔离性:一个事务的执行是不能被其他事务干扰的。
持久性:指一个事务提交以后,对数据库里面的数据改变是永久性的。接下来的其他操作或者故障也不会对这个数据库产生影响。
3.三个需要记住的关键词:
①.如何开启事务:start transaction ②.事务回滚:rollback
③.永久性更改:commit。提交commit后,数据才会进入到硬盘里被存储。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); #原子操作 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 commit; #出现异常,回滚到初始状态 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到 rollback; commit; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | egon | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.00 sec)
4.存储过程
1.介绍:类似于python中的自定义函数。储存过程的内部封装了操作数据库的sql语句,之后想实现对应的操作,只需要调用存储过程即可。存储过程在哪个库下面定义,就只能在那个库下面使用。
5.函数
mysql中提供了许多内置函数,例如:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
一、数学函数 ROUND(x,y) 返回参数x的四舍五入的有y位小数的值 RAND() 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 二、聚合函数(常用于GROUP BY从句的SELECT查询中) AVG(col)返回指定列的平均值 COUNT(col)返回指定列中非NULL值的个数 MIN(col)返回指定列的最小值 MAX(col)返回指定列的最大值 SUM(col)返回指定列的所有值之和 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 三、字符串函数 CHAR_LENGTH(str) 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。 CONCAT(str1,str2,...) 字符串拼接 如有任何一个参数为NULL ,则返回值为 NULL。 CONCAT_WS(separator,str1,str2,...) 字符串拼接(自定义连接符) CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。 CONV(N,from_base,to_base) 进制转换 例如: SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 FORMAT(X,D) 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。 例如: SELECT FORMAT(12332.1,4); 结果为: '12,332.1000' INSERT(str,pos,len,newstr) 在str的指定位置插入字符串 pos:要替换位置其实位置 len:替换的长度 newstr:新字符串 特别的: 如果pos超过原字符串长度,则返回原字符串 如果len超过原字符串长度,则由新字符串完全替换 INSTR(str,substr) 返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len) 返回字符串str 从开始的len位置的子序列字符。 LOWER(str) 变小写 UPPER(str) 变大写 REVERSE(str) 返回字符串 str ,顺序和字符顺序相反。 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' 四、日期和时间函数 CURDATE()或CURRENT_DATE() 返回当前的日期 CURTIME()或CURRENT_TIME() 返回当前的时间 DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) DAYOFMONTH(date) 返回date是一个月的第几天(1~31) DAYOFYEAR(date) 返回date是一年的第几天(1~366) DAYNAME(date) 返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE); FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式,格式化UNIX时间戳ts HOUR(time) 返回time的小时值(0~23) MINUTE(time) 返回time的分钟值(0~59) MONTH(date) 返回date的月份值(1~12) MONTHNAME(date) 返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE); NOW() 返回当前的日期和时间 QUARTER(date) 返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE); WEEK(date) 返回日期date为一年中第几周(0~53) YEAR(date) 返回日期date的年份(1000~9999) 重点: DATE_FORMAT(date,format) 根据format字符串格式化date值 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', -> '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', -> '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00' 五、加密函数 MD5() 计算字符串str的MD5校验和 PASSWORD(str) 返回字符串str的加密版本,这个加密过程是不可逆转的,和UNIX密码加密过程使用不同的算法。 六、控制流函数 CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真,则返回resultN,否则返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等,则返回resultN,否则返回default IF(test,t,f) 如果test是真,返回t;否则返回f IFNULL(arg1,arg2) 如果arg1不是空,返回arg1,否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL;否则返回arg1
1.删除函数
drop function func_name;
2.执行函数
# 获取返回值 select UPPER('egon') into @res; SELECT @res; # 在查询中使用 select f1(11,nid) ,name from tb2;
6.流程控制
条件语句: if
循环语句: while repeat
7.索引
什么是索引?
索引在mysql中也叫做‘键’,是存储引擎用于快速查找记录的一种数据结构。索引也可以理解为是书的目录,它的作用是不断缩小查找数据的范围,筛选出自己所需要的结果。提高查找效率。