数据库其他对象

1 变量

1.1 系统变量

定义：变量由系统定义，不是用户定义，属于服务器层面 - 注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

1.1.1 系统变量分类

1.1.1.1 全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启
使用
- 查看所有系统变量
  show global variables;
- 查看满足条件的部分系统变量
  show global variables like '%char%';
- 查看指定的系统变量的值
  select @@global .系统变量名;
  select @@global.autocommit;
- 为某个系统变量赋值
  方法1：set global 系统变量名=值;
  方法2：set @@global.系统变量名=值;

1.1.1.2 会话变量

作用域：针对于当前会话（连接）有效
使用
- 查看所有系统变量
  show [session] variables;
- 查看满足条件的部分系统变量
  show [session] variables like '%char%';
- 查看指定的系统变量的值
  - select @@[session.] 系统变量名;
    select @@session.autocommit;
为某个系统变量赋值
方法1：set [session] 系统变量名=值;
方法2：set @@[session.]系统变量名=值;

1.2 自定义变量

定义：变量由用户自定义，而不是系统提供的
使用步骤
1. 声明
2. 赋值
3. 使用（查看、比较、运算等）

1.2.1 分类

1.2.1.1 用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量
赋值操作符
=
:=
声明并初始化
SET @变量名=值;
SELECT @变量名:=值;
SELECT 字段 INTO @变量名 FROM 表;
赋值（修改）
SET @变量名=值; SET @变量名:=值;
SELECT @变量名:=值;
SELECT 字段 INTO @变量名 FROM 表;
使用
SELECT @变量名;

1.2.1.2 局部变量

作用域
仅仅在定义它的begin end块中有效，只能应用在 begin end中的第一句话
声明
DECLARE 变量名类型;
DECLARE 变量名类型【DEFAULT 值】;
赋值（更新变量的值）
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
SELECT 字段 INTO 局部变量名 FROM 表;
使用（查看变量的值）
SELECT 局部变量名;

--用户变量
SET @m=1; SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
--局部变量
DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

1.2.1.3 用户变量和局部变量的对比

作用域
用户变量：当前会话
局部变量:定义它的BEGIN END中
定义位置
用户变量：会话的任何地方
局部变量：BEGIN END的第一句话
语法
用户变量：不用指定类型
局部变量：一般不用加@,需要指定类型

2 存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
语法
CREATE PROCEDURE 存储过程名(参数列表) BEGIN
存储过程体（一组合法的SQL语句）
END
- 参数列表中的每个参数包含三个部分
  - 参数模式
    - in
      该参数可以作为输入，也就是该参数需要调用方传入值
    - out
      该参数可以作为输出，也就是该参数可以作为返回值
    - inout
      该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
  - 参数名
  - 参数类型
  - 举例：in stuname varchar(20)
- 说明
  - 如果存储过程体仅仅只有一句话，begin end可以省略存储过程体中的每条sql语句的结尾要求必须加分号。
  - 存储过程的结尾可以使用 delimiter 重新设置。有些客户端看到‘;’直接会提交，但是写在存储过程中的SQL语句，在我们创建存储过程的过程中是不能执行的，就像是我们在声明方法时，方法里面的代码不能执行一样，只有调用的时候才能执行呢。
  - 语法： delimiter 结束标记
  - 案例：delimiter $
调用方法
call 存储过程名(实参列表)
案例

-- 1 空参列表
DELIMITER $ CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`) 
VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
CALL myp1()$

-- 2 带in模式参数
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyName;
END $
call   myp2('柳岩')$

CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20)) BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
CALL myp3('张飞','8888')$

-- 3 带out模式参数
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20)) BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName ;
END $
call   myp6('柳岩',@boyN); select @boyN;

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys 
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
CALL myp7('小昭',@name,@cp)$ SELECT @name,@cp $

-- 4 带inout模式参数
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT) BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=10$ SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

-- 5 删除存储过程
drop procedure 存储过程名;

-- 查看存储过程信息
SHOW CREATE PROCEDURE myp2;

3 函数

3.1 报错

使用函数时可能会报错：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

原因：mysql的设置默认是不允许创建函数，这是因为我们开启了bin-log, 我们就必须指定我们的函数是否是
1. DETERMINISTIC 不确定的
2. NO SQL 没有SQl语句，当然也不会修改数据
3. READS SQL DATA 只是读取数据，当然也不会修改数据
4. MODIFIES SQL DATA 要修改数据
5. CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决办法
- 1 直接设置这个全局变量为1：
  set global log_bin_trust_function_creators=1;
  - 缺点：数据库重启后失效
- 2 在my.ini里面设置 log-bin-trust-function-creators=1
  - 不过这个需要重启服务
  - windows操作系统下：
    1. 由于我们使用MySql 时，需要修改mysql 的 my.ini 的配置文件。
    2. 但是 mysql 5.7 的 my.ini 位置并不在 C:\Program Files\MySQL\MySQL Server 5.7 此目录的my-default.ini 并不是我们要的：
    3. 如果安装在C盘的：我们要进入 C:\ProgramData\MySQL\MySQL Server 5.7 此目录即可看到my.ini 此时你可以修改次配置啦！

3.2 含义

函数是一组预先编译好的SQL语句的集合，理解成批处理语句

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

3.3 和存储过程的区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

3.4 语法

创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END;
- 注意
  1. 参数列表包含两部分：参数名参数类型
  2. 函数体：肯定会有return语句，如果没有会报错；如果return语句没有放在函数体的最后也会报错
  3. 函数体中仅有一句话，则可以省略begin end
  4. 使用 delimiter语句设置结束标记
调用语法
SELECT 函数名(参数列表)

3.5 案例

-- 1 无参函数案例
CREATE FUNCTION myf1() RETURNS INT BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#赋值
FROM employees;
RETURN c;
END $
SELECT myf1()$

-- 2 有参函数案例
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE BEGIN
SET @sal=0;#定义用户变量 
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
SELECT myf2('k_ing') $


CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $
SELECT myf3('IT')$

-- 3 不查表案例
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_fun1(1,2)$

-- 4 查看函数信息
SHOW CREATE FUNCTION myf3;

-- 5 删除函数
DROP FUNCTION myf3;

4 视图

含义
- 虚拟表，和普通表一样使用
- mysql5.1版本出现的新特性，是通过表动态生成的数据
- 只是保存了sql逻辑
语法
create view 视图名
as
查询语句;

day5_数据库其他对象

day5_数据库其他对象

数据库其他对象

1 变量

1.1 系统变量

1.1.1 系统变量分类

1.1.1.1 全局变量

1.1.1.2 会话变量

1.2 自定义变量

1.2.1 分类

1.2.1.1 用户变量

1.2.1.2 局部变量

1.2.1.3 用户变量和局部变量的对比

2 存储过程

3 函数

3.1 报错

3.2 含义

3.3 和存储过程的区别

3.4 语法

3.5 案例

4 视图

推荐阅读更多精彩内容