本文已参加「新人创作礼」活动,一起敞开创作之路
Mysql数据库基础系列
软件下载地址
提取码:7v7u
数据下载地址
提取码:e6p9
1、变量
在mysql中,能够运用变量来存储查询或计算成果,类似python中的变量。在mysql中首要分为:体系变量和自界说变量
1.1 体系变量
体系变量是在启动mysql服务时会给体系变量赋值,界说了当前mysql服务实例的一些特征,例如在事务中的autocommit
体系变量分为:
- 全局变量(global):全局变量对一切的衔接有用,可是重启会失效。
- 会话变量(session,mysql默认):会话变量,不能跨衔接
1.1.1检查体系变量
- 检查一切或许部分体系变量
# 检查一切全局变量
show global variables;
# 检查一切会话变量
show variables;
#检查部分需求的变量
show variables like '%aa%'
- 检查指定体系变量,运用@@
# 1.检查指定体系变量的值
select @@global.autocommit;
# 2.检查指定的会话变量的值
SELECT @@变量名;
- 修改体系变量值
set @@global.变量名 = 值;
set global 变量名=值
set @@session.变量名 = 值;
set session 变量名=值
1.2 自界说变量
自界说变量是用户自己界说的,根据其运用范围分为用户变量和局部变量
1.2.1 用户变量
- 变量界说
#1. 运用set
set @用户变量 = value;
#2. 运用select
select @用户变量 := 表达式;
select 表达式 Into @用户变量
- 变量赋值
set @用户变量 = value
- 变量运用
select @用户变量
1.2.2 局部变量
只能运用在begin end中,且只能放在第一个句子
- 变量界说
declare 变量 类型 [default 默认值]
- 变量赋值
set 变量名 = 值
select 值 into 变量名 from
- 变量运用
select 局部变量
案例:查询两个变量的和
1.运用用户变量
SET @m = 1;
SET @n = 1;
SET @sum = @m+@n;
SELECT @sum;
2.运用局部变量
DELIMITER $
CREATE PROCEDURE sum_2()
BEGIN
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM= m+n;
SELECT SUM;
END$
DELIMITER ;
1.2.3 差异
界说 效果域 界说方位
用户变量 需求用@ 当前会话 会话中的任何方位
局部变量 不需求@ 只用于begin-end中 begin-end的第一句话
2.存储进程
类似于python中的方法,将一组预先编译好的sql句子进行封装。
优点:
-1、存储进程能够一次编译屡次运用。存储进程只在创立时进行编译,之后的运用都不需求从头编译,这就提升了 SQL 的履行效率。
-2、能够削减开发工作量。将代码 封装 成模块,实际上是编程的中心思想之一,这样能够把杂乱的问题拆解成不同的模块,然后模块之间能够 重复运用 ,在削减开发工作量的一起,还能确保代码的结构清晰。
-3、存储进程的安全性强。咱们在设定存储进程的时分能够 设置对用户的运用权限 ,这样就和视图相同具有较强的安全性。
-4、能够削减网络传输量。因为代码封装到存储进程中,每次运用只需求调用存储进程即可,这样就削减了网络传输量。
-5、杰出的封装性。在进行相对杂乱的数据库操作时,原本需求运用一条一条的 SQL 句子,可能要衔接屡次数据库才干完成的操作,现在变成了一次存储进程,只需求 衔接一次即可 。
缺陷:
- 1、可移植性差。存储进程不能跨数据库移植,比方在 MySQL、Oracle 和 SQL Server 里编写的存储进程,在换成其他数据库时都需求从头编写。
- 2、调试困难。只有少量 DBMS 支撑存储进程的调试。对于杂乱的存储进程来说,开发和维护都不容易。尽管也有一些第三方东西能够对存储进程进行调试,但要收费。
- 3、存储进程的版别办理很困难。比方数据表索引发生变化了,可能会导致存储进程失效。咱们在开发软件的时分往往需求进行版别办理,可是存储进程本身没有版别控制,版别迭代更新的时分很麻烦。
- 4、它不适合高并发的场景。高并发的场景需求削减数据库的压力,有时数据库会采用分库分表的方
式,并且对可扩展性要求很高,在这种情况下,存储进程会变得难以维护, 添加数据库的压力 ,显然就不适用了。
2.1 分类
存储进程包括三类
1.没有参数
2.in:参数输入
3.out:参数输出
4.inout:参数既能够输入也能够输出
2.2 语法
2.2.1 创立存储进程
create procedure 存储进程姓名(参数)
begin
sql句子
end
注意事项:
1.BEGIN…END:BEGIN…END 中心包括了多个句子,每个句子都以(;)号为完毕符。
2.DECLARE:DECLARE 用来声明变量,运用的方位在于 BEGIN…END 句子中心,并且需求在其他句子运用之前进行变量的声明。
3. SET:赋值句子,用于对变量进行赋值。
4. SELECT… INTO:把从数据表中查询的成果存放到变量中,也就是为变量赋值。
5.存储进程的完毕能够运用 delimiter 从头设置
2.2.2 代码案例
1.不带任何参数:查询employees表中的一切数据
delimiter $ # 设置完毕符
create procedure select_all() # 创立存储进程
begin
select * from employees;
end $ # 完毕
delimiter ;
call select_all(); #调用存储进程
2.回来职工的平均薪酬
delimiter $ # 设置完毕符
create procedure avg_salary() # 创立存储进程
begin
select avg(salary) from employees;
end $ # 完毕
delimiter ;
call avg_salary();
3.out类型的存储进程:将employees表中最低薪酬通过ms参数输出
delimiter $ # 设置完毕符
create procedure min_salary(out ms double) # 创立out类型存储进程,ms为参数
begin
select min(salary) into ms
from employees;
end $ # 完毕
delimiter ;
call min_salary(@ms);
select @ms;
4.in类型的存储进程,检查某个职工的薪酬,参数ename
delimiter $ # 设置完毕符
create procedure name_salary(in ename varchar(20)) # 创立in类型存储进程,ename为参数
begin
select salary from employees
where last_name = ename;
end $ # 完毕
delimiter ;
call name_salary('Abel');
5.一起带in和out类型的存储进程。(1)检查某个职工的薪资(2)用out参数esalary输出
delimiter $ # 设置完毕符
create procedure name_salary2(in ename varchar(20), out esalary decimal(10,2)) # 创立in-out类型存储进程,ename,esalary为参数
begin
select salary into esalary #存入到额salary中
from employees
where last_name = ename;
end $ # 完毕
delimiter ;
call name_salary2('Abel',@esalary);
select @esalary;
6.带inout类型的存储进程,查询某个职工的办理者姓名
delimiter $ # 设置完毕符
create procedure manager_name(inout ename varchar(20)) # 创立inout类型存储进程,ename为参数
begin
select last_name into ename # 传入到inout参数
from employees
where employee_id = (
select manager_id from employees
where last_name = ename
); # 运用子查询得到办理者的id
end $ # 完毕
delimiter ;
call manager_name('Abel',@ename);
select @ename;
2.2.3 调用存储进程
- 格局
# 1.in类型
call sp('值')
#2.out类型
call sp(@name);
select @name;
#3.inout类型
set @name = '值';
call sp(@name);
select @name;
7.创立一个存储进程,计算1+…+n的和
DELIMITER $ # 设置完毕符
CREATE PROCEDURE sum_n(IN n INT ) # 创立in,n为参数
BEGIN
# 界说局部变量
DECLARE i INT;
DECLARE SUM INT;
SET i = 1;
SET SUM = 0;
WHILE i<=n DO # 敞开循环
SET SUM = SUM + i;
SET i = i + 1;
END WHILE;#完毕while循环
SELECT SUM;
END $ # 完毕
DELIMITER ;
CALL sum_n(100);
3.函数
函数一组预先编译好的SQL句子的调集,了解成批处理句子
-
优点:
1、提高代码的重用性
2、简化操作
3、削减了编译次数并且削减了和数据库服务器的衔接次数,提高了效率 -
与存储进程的差异:
存储进程:能够有0个回来,也能够有多个回来,适合做批量插入、批量更新
函数:有且仅有1 个回来,适合做处理数据后回来一个成果
3.1函数创立
create function func_name(参数) return 回来类型
[characteristics]
begin
函数句子
end
3.2注意事项:
1.参数列表 包括两部分:
参数名 参数类型,函数默认是in参数
2.函数体:必定会有return句子,假如没有会报错
假如return句子没有放在函数体的最终也不报错,但不主张
return 值;
3.函数体中仅有一句话,则能够省略begin end
4.运用 delimiter句子设置完毕符号
3.3函数调用
函数调用方法和体系函数相同,案例如下
1.空参数,名称为email_by_name(),该函数查询Abel的email,并回来,数据类型为字符串型。
delimiter $
create function email_by_name() returns varchar(20)
begin
return (select email from employees where last_name = 'Abel');
end $
delimiter ;
假如呈现 you might want to use the less safe log_bin_trust_function_creators variable
错误,将体系变量设置SET GLOBAL log_bin_trust_function_creators = 1;
即可,也能够添加函数特性
2.名称为email_by_id(),参数传入emp_id,该函数查询emp_id的email,并回来,数据类型为字符串型。
delimiter $
create function email_by_id(emp_id int) returns varchar(20)
begin
return (select email from employees where employee_id = emp_id);
end $
delimiter ;
select email_by_id(101);
3.创立存储函数count_by_id(),参数传入dept_id,该函数查询dept_id部门的职工人数,并回来,数据类型为整型。
delimiter $
create function count_by_id(dept_id int) returns int
begin
return (select count(*) from employees where department_id = dept_id);
end $
delimiter ;
select count_by_id(50);
3.4 函数检查
方式1. 运用SHOW CREATE句子检查存储进程和函数的创立信息
SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;
方式2. 运用SHOW STATUS句子检查存储进程和函数的状态信息
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'show_max_salary';
SHOW FUNCTION STATUS LIKE 'email_by_id';
3.5 函数删除
DROP FUNCTION IF EXISTS count_by_id;
DROP PROCEDURE IF EXISTS show_min_salary;