视图
视图是一种虚拟存在的表,行和列的数据来自自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。应用场景:1.多个地方用到同样的查询结果。2.该查询结果使用的sql语句较为复杂。
创建视图:
#sql语句
create view 视图名
as
查询sql语句;
视图的优点:
- 重用sql语句
- 简化复杂的sql操作,不必知道它的查询细节
- 保护数据,提高安全性
修改视图:
#方式一:
create or replace view 视图名
as
查询语句;
#方式二:
alter view 视图名
as
查询语句;
删除视图:
drop view 视图名,视图名,....;
查看视图:
方式一:desc 视图名;
方式二:show create view 视图名;
虽然我们可以使用视图时很像是对表操作,但是对于视图的更新操作,在绝大多数情况下时不允许的。具备以下特点的视图不允许更新。
- 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
- 常量试图
- select中包含子查询
- join(涉及连接的)
- from一个不能更新的视图(创建视图的时候)
- where子句的子查询引用了from子句中的表
变量
变量分为系统变量(全局变量、会话变量)和自定义变量(用户变量、局部变量)
系统变量
系统变量:变量由系统提供,不是用户定义,属于服务器层面。
全局变量作用域:服务器每次启动将为所有的全局变量赋初值,针对于所有的会话(连接)有效,但不能跨重启。
会话变量作用域:仅仅针对于当前会话(连接)有效
#语法使用
#1.查看系统变量
show global | session variables;
#2.查看满足条件的部分系统变量
show global | session variables like '%char%';
#3.查看指定的某个系统变量的值
select @@global|session.系统变量名;
#4.为某个系统变量赋值
方式一:
set global|session 系统变量名 = 值;
方式二:
set @@global|session.系统变量名 = 值;
注:如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不写,则默认session
自定义变量
自定义变量:变量是由用户自定义的,不是由系统定义的。
一、用户变量
使用步骤:声明、赋值、使用(查看、比较、运算等)
作用域:针对于当前会话(连接)有效,用在begin end内外都可
#1.声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
set @用户变量名:=值; #三种方式都可
#2.赋值(更新用户变量的值)
方式一:跟声明并初始化的方式一样
方式二:通过select into
select 字段 into 变量名
from 表;
#3.使用
select @用户变量名;
二、局部变量
作用域:仅仅在定义它的begin end中有效
#1.声明
declare 变量名 类型;
declare 变量名 类型 default 值;
#2.赋值
方式一:
set @局部变量名=值;
set @局部变量名:=值;
set @局部变量名:=值; #三种方式都可
方式二:通过select into
select 字段 into 变量名
from 表;
#3.使用
select 局部变量名;
存储过程
含义:一组预先编译好的sql语句的集合,可以理解为批处理语句
优点:
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
一:创建语法:
create procedure 存储过程名(参数列表)
begin
方法体(一组合法的sql语句)
end
参数列表包含三部分(参数模式、参数名、参数类型)例:in name varchar(20)
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
注:如果存储过程体仅仅只有一句话,begin end 可以省略,如果有多条语句,那么每条sql语句的结尾要求必须加分号,存储过程的结尾可以使用delimiter重新设置
delimiter 结束标志
例如:delimiter $
二:调用语法
call 存储过程名(实参列表);
#1.空参示例:
delimiter $
create procedure myp1()
begin
insert into admin(name,password)
values('name1','0000'),('name2','0001'),('name3','0002');
end $
#调用
call myp1()$
三、查看存储过程的信息和删除存储过程
#查看语法:show create procedure 存储过程名;
#删除语法:drop procedure 存储过程名;
注:删除只能删一个,不能同时删多个
函数
含义:一组预先编译好的sql语句的集合,可以理解为批处理语句
优点:
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
可以看到,对于存储过程和函数的含义与优点是一样的,但是他们还是有区别的。
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意事项:
1.参数列表包含两部分:参数名 参数类型
2.函数体:必须要有return语句,没有回报错。如果return语句没有放在函数体的最后也不报错,但不建议
3.begin end用法与存储过程相同,
二、调用语法
select 函数名(参数列表)
创建与调用案例:
create function myf1() returns int
begin
declare c int default 0;#定义变量
select count(*) into c#赋值
from employees;
return c;
end $
select myf1()$
三、查看函数
查看语法:show create function 函数名;
删除语法:drop function 函数名;