数据库(视图,事件,触发器,函数,存储,变量)

07.21自我总结

一.视图

1.什么是是视图

​ 本质上是一个虚拟的表,即看得见但是不实际存在

• 视图内修改了内容,原表的内容也会受到影响
• 原表修改了内容视图同样也会受到影响

目的:简化sql语句

2.视图的增删改查

• 增

  create view 视图名称 as select语句

• 删

  drop view 视图名称

• 改

  alter view 视图名称 as 新的select语句

• 查

  desc view_name

  show create view 视图名称

二.触发器

1.什么是触发器

当表格内发生,增,删,改对立面数据有变动时,我们可以给他特定的变动内容,除法某些select语句,以及逻辑判断

2.触发器的增删改查

• 增

  create trigger 触发器名称 时间 事件 on 表名称 for each row

  begin

  sql语句。。。。。：

  end

  • 时间:事件发生前 before | 事件发生后 after
  • 事件:update delete insert

• 删

  drop trigger 触发器名称

• 查

  show triggers;查看所有触发器

  show create trigger 触发器名称

• 改

  建议删了后再添加

3.举例

python # 将结束符设置为| delimiter | create trigger cmd_insert after insert on cmd for each row begin if new.success = "no" then insert into errlog values(null,new.cmd,new.sub_time); end if; end| # 在还原之前的结束符 delimiter ;

4.ord与new

• ord:update，delete中可用
• new:update,insert 中可用

三.事物

1.什么是事物

​ 事务就是一系列sql语句的组合,是一个整体

2.事物的特点

• 原子性,指的是这个事务中的sql语句是一个整体,不能拆分,要么都执行,要么全都失败

• 一致性, 事务执行结束后,表的关联关系一定是正确的,不会发送数据错乱

• 隔离性,事务之间相互隔离,数据不会互相影响,即使操作了同一个表 , 本质就是加锁,根据锁的粒度不同分为几个隔离级别

• 持久性,事务执行成功后数据将永久保存,无法恢复

3.事物的语法

mysql #开启事务 start transaction #sql 语句...... #sql 语句...... #sql 语句...... commit #提交事务 一旦提交就持久化了

4.归档操作

1.savepoint

设置归档的存点sql 语句savepoint 档案名称

2.返回归档点

• rollback to 保存点名称
• rollback 返回事件一开始
• read committed 上传修改信息

注意:如果一个事件提交了,将没法返回归档点

四,存储过程

1.什么是存储过程

是任意的sql语句的组合,被放到某一个存储过程中,类似于一个函数,有一个函数,有参数,还是函数体

2.语法

1.创建

create procedure p_name(p_type p_name p_date_type)
begin
sql.......
end

• p_type :有两种 in输入,out输出,inout输入输出
• p_name:参数名称
• p_date_type:参数类型

2.调用

call add1(参数);

其中in对于的是传入的有具体值参数,out对于数据库中的变量名

3.删除

drop procedure 名称;

4.查找

• 查找全部select name from mysql.proc where db =库名 and type = 'procedure';
• 查找个别:show create procedure 名称;

五.定义变量

set @变量名 = 值

六.函数

一、数学函数

函数	介绍
abs(x)	返回x的绝对值
bin(x)	返回x的二进制（oct返回八进制，hex返回十六进制）
ceiling(x)	返回大于x的最小整数值
exp(x)	返回值e（自然对数的底）的x次方
floor(x)	返回小于x的最大整数值
greatest(x1,x2,...,xn)	返回集合中最大的值
least(x1,x2,...,xn)	返回集合中最小的值
ln(x)	返回x的自然对数
log(x)y)	返回x的以y为底的对数
mod(x)y)	返回x/y的模（余数）
pi()	返回pi的值（圆周率）
rand()	返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
round(x,y)	返回参数x的四舍五入的有y位小数的值
sign(x)	返回代表数字x的符号的值
sqrt(x)	返回一个数的平方根
truncate(x,y)	返回数字x截短为y位小数的结果

二、聚合函数(常用于group by从句的select查询中)

函数	介绍
avg(col)	返回指定列的平均值
count(col)	返回指定列中非null值的个数
min(col)	返回指定列的最小值
max(col)	返回指定列的最大值
sum(col)	返回指定列的所有值之和
group_concat(col)	返回由属于一组的列值连接组合而成的结果

三、字符串函数

函数	介绍
ascii(char)	返回字符的ascii码值
bit_length(str)	返回字符串的比特长度
concat(s1,s2...,sn)	将s1,s2...,sn连接成字符串
concat_ws(sep,s1,s2...,sn)	将s1,s2...,sn连接成字符串，并用sep字符间隔
insert(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
find_in_set(str,list)	分析逗号分隔的list列表，如果发现str，返回str在list中的位置
lcase(str)或lower(str)	返回将字符串str中所有字符改变为小写后的结果
left(str,x)	返回字符串str中最左边的x个字符
length(s)	返回字符串str中的字符数
ltrim(str)	从字符串str中切掉开头的空格
position(substr in str)	返回子串substr在字符串str中第一次出现的位置
quote(str)	用反斜杠转义str中的单引号
repeat(str,srchstr,rplcstr)	返回字符串str重复x次的结果
reverse(str)	返回颠倒字符串str的结果
right(str,x)	返回字符串str中最右边的x个字符
rtrim(str)	返回字符串str尾部的空格
strcmp(s1,s2)	比较字符串s1和s2
trim(str)	去除字符串首部和尾部的所有空格
ucase(str)或upper(str)	返回将字符串str中所有字符转变为大写后的结果

四、日期和时间函数

函数	介绍
curdate()或current_date()	返回当前的日期
curtime()或current_time()	返回当前的时间
date_add(date,interval int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_add(current_date,interval 6 month);
date_format(date,fmt)	依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_sub(current_date,interval 6 month);
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)

五.自定义函数

语法

create function f_name(paramters)
returns datatype;
return value;

说明:
paramters 只能是in 输入参数 参数名 类型
必须有返回值
不能呢加begin 和end
returns 后面是返回值的类型 这里不加分号
return 后面是要返回的值

注意

函数只能返回一个值
函数一般不涉及数据的增删改查 就是一个通用的功能
调用自定义的函数 与调用系统的一致 不需要call 使用select 可获得返回值
函数中不能使用sql语句
就像在java中不能识别sql语句一样

案例

delimiter |

create function addfuntion(a int,b int)
returns int return a + b;

delimiter ;

执行函数

select addfuntion(1,1);

七.关键字delimiter

delimiter改变命令行语言执行的方法
/
如默认是;进行运行

而delimiter | 表示末尾输入|表示执行