MySQL基础4

1、视图

含义： 就是指虚拟表，和普通表一样使用
MySQL5.1版本出现的新特性，是通过表动态生成的
比如，舞蹈班（各班抽同学组建而成，晚会结束各回各班）和普通班的就类似于视图和普通表
视图可以提高数据的复用性，将普通表中经常使用到的数据封装起来，成为一个虚拟表

应用场景：
多个地方用到同样的查询结果，且该查询结果使用的 SQL 语句较复杂

案例：
查看部门名A开头的员工名和部门名

1、普通表查看

#普通表查看部门名A开头的员工名和部门名
SELECT 
  `last_name`,
  `department_name` 
FROM
  `employees` e 
  JOIN `departments` d 
    ON e.`department_id` = d.`department_id` 
WHERE d.`department_name` LIKE 'a%' ;

2、通过视图

#用视图将该部分数据封装
CREATE VIEW v1 AS 
SELECT 
  `last_name`,
  `department_name` 
FROM
  `employees` e 
  JOIN `departments` d 
    ON e.`department_id` = d.`department_id` ;

#视图查看部门名A开头的员工名和部门名
SELECT * FROM v1 
WHERE `department_name` LIKE 'a%' ;

1.1、视图的创建

注意：

1. 对于视图，只是保存了 SQL 逻辑，即 as 后面的语句
2. 对于表，实实在在的保存了数据
3. 视图一般仅用于查，不用于增删改

语法：

CREATE VIEW 视图名 AS 查询语句;

例题：

1. 查询员工名中包含a字符的员工名、部门名和工种信息

#创建视图
CREATE VIEW v1 AS 
SELECT 
  `last_name`,
  `department_name`,
  j.* 
FROM
  `employees` e 
  JOIN `departments` d 
    ON e.`department_id` = d.`department_id` 
  JOIN `jobs` j 
    ON j.`job_id` = e.`job_id` ;

#查看数据
SELECT 
  * 
FROM
  v1 
WHERE `last_name` LIKE '%a%' ;

2. 查询各部门的平均工资级别

#创建
CREATE VIEW v2 AS 
SELECT 
  AVG(`salary`) ag,
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` ;

#查看 
SELECT 
  `grade_level` 
FROM
  `job_grades` 
  JOIN v2 
    ON ag BETWEEN `lowest_sal` 
    AND `highest_sal` ;

3. 查询平均工资最低的部门信息

#创建
CREATE VIEW v2 AS 
SELECT 
  AVG(`salary`) ag,
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` ;

SELECT 
  `department_id` 
FROM
  v2 
ORDER BY ag ASC 
LIMIT 1 ;

SELECT 
  * 
FROM
  `departments` 
WHERE `department_id` = 
  (SELECT 
    `department_id` 
  FROM
    v2 
  ORDER BY ag ASC 
  LIMIT 1) ;

4. 查询平均工资最低的部门名和工资

CREATE VIEW v3 AS 
SELECT 
  * 
FROM
  `departments` 
WHERE `department_id` = 
  (SELECT 
    `department_id` 
  FROM
    v2 
  ORDER BY ag ASC 
  LIMIT 1) ;

SELECT 
  `department_name`,
  ag 工资
FROM
  v2 
  JOIN v3 
    ON v2.`department_id` = v3.`department_id` ;

5. 创建视图v4，要求查询电话以 011 开头的员工姓名和工资、邮箱

CREATE VIEW v4 AS 
SELECT 
  `last_name`,
  `salary`,
  `email` 
FROM
  `employees` 
WHERE `phone_number` LIKE '011%' ;

SELECT 
  * 
FROM
  v4 ;

6. 创建视图v5，要求查询部门的最高工资高于12000的部门信息

CREATE VIEW v5 AS 
SELECT 
  MAX(`salary`) mx,
  `department_id` 
FROM
  `employees` 
GROUP BY `department_id` ;

SELECT 
  `department_id` 
FROM
  v5 
WHERE mx > 12000 ;

SELECT 
  * 
FROM
  `departments` 
WHERE `department_id` IN 
  (SELECT 
    `department_id` 
  FROM
    v5 
  WHERE mx > 12000) ;

1.2、视图的修改、删除、查看

修改视图：

CREATE OR REPLACE VIEW 视图名 AS 查询语句;
ALTER VIEW 视图名 AS 查询语句;

删除视图：
用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限

DROP VIEW IF EXISTS 视图1,视图2...;

例如

#删除视图
DROP VIEW IF EXISTS v1;

查看视图：

DESC 视图名;
SHOW CREATE VIEW 视图名;   #该句显示的结果太长，适用于在DOS命令窗口中使用

例如

#查看视图
DESC v1;
SHOW CREATE VIEW v1;

1.3、视图数据的增删改

1、插入数据

INSERT INTO 视图名(字段名,字段名) VALUES(值,值);
INSERT INTO `test1` VALUES('张飞',10000);

注意，此时原表中的数据也会有插入

2、修改

UPDATE `test1` SET last_name='jerry' WHERE last_name='张飞';

注意，此时原表中的数据也会修改

3、删除

DELETE FROM `test1` WHERE last_name='jerry';

注意，此时原表中的数据也会删除

注意：
具备以下特点的视图不允许更新

1. 包含以下关键字的 SQL 语句：分组函数、distinct、group by、having、union或者union all
   因为以上条件生成的视图的数据是原表动态生成的，所以视图的更新无法同步更改原表，所以此类视图无法更新
2. 常量视图
   CREATE VIEW test2 AS SELECT 'tom';
3. Select中包含子查询
4. join（涉及到多表连接）
5. from一个不能更新的视图
   即拆那个视图的表来源是一个不可更新的视图
6. where子句的子查询引用了from子句中的表

from一个不能更新的视图
即拆那个视图的表来源是一个不可更新的视图

# 常量视图
CREATE VIEW test2 AS SELECT '100' 数学;
CREATE VIEW test3 AS SELECT * FROM test2;

where子句的子查询引用了from子句中的表

# 领导信息视图
CREATE VIEW test4 AS
SELECT 
  `last_name`,
  `salary` 
FROM
  `employees` 
WHERE `employee_id` IN 
  (SELECT 
    `manager_id` 
  FROM
    `employees` 
  WHERE `manager_id` IS NOT NULL) ;

2、变量

2.1、系统变量

变量由系统提供，不是用户自定义，属于服务器层面

分为全局变量和局部变量，global指全局变量，session指局部变量，不写默认为局部变量

语法：

1. 查看所有的全局变量
   SHOW GLOBAL VARIABLES;
2. 查看所有的会话变量
   SHOW SESSION VARIABLES;
3. 查看满足条件的部分系统变量
   SHOW GLOBAL|【SESSION】 VARIABLES LIKE '%auto_increment%';
SHOW VARIABLES LIKE 'time_zone'; 不写的默认是会话变量
4. 查看指定的某个系统变量的值
   SELECT @@GLOBAL|【SESSION】.系统变量名;
5. 为某个系统变量赋值
   第一种：SET GLOBAL|【SESSION】 系统变量名 = 值;
SET auto_increment_increment = 1;
   第二种：SET @@GLOBAL|【SESSION】.系统变量名 = 值;

全局变量作用域：
针对于所有的会话（连接）有效，但不能跨重启
服务器每次启动将为所有的全局变量赋初始值

会话变量作用域
仅仅对于当前会话（连接）有效

2.2、自定义变量

用户变量作用域：
仅仅对于当前会话（连接）有效，同于会话变量作用域

局部变量作用域：
仅仅在定义它的begin end 中有效

2.2.1、用户变量

自定义变量——用户变量
作用域：仅仅对于当前会话（连接）有效，同于会话变量作用域

声明并初始化

SET @用户变量名=值;
SET @用户变量名:=值;
SELECT  @用户变量名:=值;

赋值（更新用户变量的值）

SET @用户变量名=值;
SET @用户变量名:=值;
SELECT  @用户变量名:=值;

SELECT 字段 INTO @变量名 FROM 表;

使用（查看用户变量的值）

SELECT @用户变量名;

2.2.2、局部变量

自定义变量——局部变量
作用域：仅仅在定义它的begin end 中有效
只能用在begin end中，且是作为第一句话

声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

赋值（更新变量的值）

SET 局部变量名=值;
SET 局部变量名:=值;
SELECT  局部变量名:=值;

SELECT 字段 INTO 局部变量名 FROM 表;

使用（查看变量的值）

SELECT 局部变量名;

案例：
使用变量计数员工个数

SET @count =1;
SELECT COUNT(*) INTO @count FROM `employees`;
SELECT @count;

3、存储过程

含义：事先经过编译并存储在数据库中的一段 SQL 语句的集合

存储过程也可以用 SQLyog执行，只不过每句都要先用DELIMITER 结束标记

创建的所有存储过程和函数在mysql库中的proc表格里都有体现

3.1、增删查改

创建：

CREATE PROCEDURE 存储过程名(参数列表) 
 BEGIN 
   存储过程体(一组合法的SQL语句) 
 END 结束标记

注意：

1. 参数列表包含三个部分：参数模式、参数名、参数类型
   参数模式：IN、OUT、INOUT
   IN：该参数可以作为输入，也就是该参数需要调用方传入值
   OUT：该参数可以作为输出，也就是该参数可以作为返回值
   INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2. 如果存储过程体仅有一句话，BEGIN END可省略
3. 存储过程体中的每条SQL语句以分号结尾
4. 存储过程的结尾可以使用 DELIMITER 重新设置，DELIMITER 结束标记

调用（执行）：

CALL 存储过程名(实参列表);

删除：
DROP PROCEDURE 存储过程名1,存储过程名2;

查看：
SHOW CREATE PROCEDURE 存储过程名;

3.2、空参的存储过程

案例：
插入到 stu 表中3条数据

#插入到 stu 表中3条数据
DELIMITER $
CREATE PROCEDURE p1 () 
BEGIN
  INSERT INTO stu 
  VALUES
    (1, 'jerry'),
    (2, 'tom'),
    (3, 'timi') ;
END $
DELIMITER $
CALL p1()$
SELECT * FROM stu;

3.3、带in模式的存储过程

案例1：
创建存储过程实现：根据女神名查询对应的男神信息

#创建存储过程实现：根据女神名查询对应的男神信息
USE girls;
DELIMITER $
CREATE PROCEDURE p1 (IN beautyName VARCHAR (20)) 
BEGIN
  SELECT 
    bo.* 
  FROM
    `beauty` b 
    LEFT JOIN `boys` bo 
      ON b.`boyfriend_id` = bo.`id` 
  WHERE b.`name` = beautyName ;
END $
DELIMITER $
CALL p1('热巴')$

案例2：
创建存储过程实现 用户是否登录成功

#创建存储过程实现 用户是否登录成功
DELIMITER $
CREATE PROCEDURE p2 (
  IN username VARCHAR (20),
  IN `password` INT
) 
BEGIN
  SELECT 
    COUNT(*) INTO @answer   #用户自定义变量可以不用初始化，直接用
  FROM
    `admin` a 
  WHERE a.`username` = username 
    AND a.`password` = `password` ;
END $
DELIMITER $
CALL p2('john',8888)$
DELIMITER $
SELECT IF(@answer>0,'登录成功','登录失败') AS 提示$

3.4、带out模式的存储过程

案例1：
创建存储过程实现，根据女神名，返回对应的男神名

#创建存储过程实现，根据男神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE p3 (
  IN `beautyName` VARCHAR (20),
  OUT `boyName` VARCHAR (20)
) 
BEGIN
  SELECT 
    bo.`boyName` INTO `boyName` 
  FROM
    `beauty` b 
    LEFT JOIN `boys` bo 
      ON bo.`id` = b.`boyfriend_id` 
  WHERE b.`name` = `beautyName` ;
END $
DELIMITER $
CALL p3('周芷若',@boyName)$
DELIMITER $
SELECT @boyName$

案例2：
根据女神名，返回对应的男神名和男神魅力值

#根据女神名，返回对应的男神名和男神魅力值
DELIMITER $
CREATE PROCEDURE p4 (
  IN `beautyName` VARCHAR (20),
  OUT `boyName` VARCHAR (20),
  OUT `userCP` INT
) 
BEGIN
  SELECT 
    bo.`boyName`,
    bo.`userCP` INTO `boyName`,
    `userCP` 
  FROM
    `beauty` b 
    LEFT JOIN `boys` bo 
      ON bo.`id` = b.`boyfriend_id` 
  WHERE b.`name` = `beautyName` ;
END $
DELIMITER $
CALL p4('热巴',@boyName,@userCP)$
DELIMITER $
SELECT @boyName 男神名,@userCP 男神魅力值$

3.5、带inout模式的存储过程

案例： ★★★★
传入a和b；两个值，最终a和b都翻倍并返回

#传入a和b；两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE p5 (INOUT a INT, INOUT b INT) 
BEGIN
  SET a = a * 2 ;SET b = b * 2 ;
END $
DELIMITER $
SET @c=2$
DELIMITER $
SET @d=8$
DELIMITER $
CALL p5(@c,@d);    
DELIMITER $
SELECT @c,@d$

3.6、练习

1. 创建存储过程，实现传入用户名和密码，插入到admin表中

#创建存储过程，实现传入用户名和密码，插入到admin表中
USE `girls`;
DELIMITER $
CREATE PROCEDURE p1 (
  IN `username` VARCHAR (20),
  IN `password` VARCHAR (20)
) 
BEGIN
  INSERT INTO admin (
    `admin`.`username`,
    `admin`.`password`
  ) 
  VALUES
    (`username`, `password`) ;
END $
DELIMITER $
CALL p1('amy',1111)$

2. 创建存储过程，实现传入女神编号，返回女神名和电话

#创建存储过程，实现传入女神编号，返回女神名和电话
DELIMITER $
CREATE PROCEDURE p2 (
  IN `beautyID` INT,
  OUT `name` VARCHAR (20),
  OUT `phone` VARCHAR (20)
) 
BEGIN
  SELECT 
    `beauty`.`name`,
    `beauty`.`phone` INTO `name`,
    `phone` 
  FROM
    `beauty` 
  WHERE `beauty`.`id` = `beautyID` ;
END $

DELIMITER $
CALL p2(8,@id,@phone) $
SELECT @id,@phone;

3. 创建存储过程，实现传入两个生日，返回大小

#创建存储过程，实现传入两个生日，返回大小
DELIMITER $
CREATE PROCEDURE p3 (
  IN data1 DATE,
  IN data2 DATE,
  OUT diff INT
) 
BEGIN
  SELECT 
    DATEDIFF(data1, data2) INTO diff ;
END $
DELIMITER $
CALL p3('2021-12-1','2021-11-30',@diff)$
SELECT @diff;

4. 创建存储过程，实现传入一个日期，格式化xx年xx月xx日并返回

#创建存储过程，实现传入一个日期，格式化xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE p4 (IN data1 DATE, OUT dd VARCHAR (20)) 
BEGIN
  SELECT 
    DATE_FORMAT(data1, '%Y年%m月%d日') INTO dd ;
END $
DELIMITER $
CALL p4('2021-12-1',@dd)$
SELECT @dd;

5. 创建存储过程，实现传入女神名，返回女神 AND 男神 格式的字符串

#创建存储过程，实现传入女神名，返回女神 AND 男神 格式的字符串
DELIMITER $
CREATE PROCEDURE p5 (
  IN bname VARCHAR (20),
  OUT nandn VARCHAR (20)
) 
BEGIN
  SELECT 
    CONCAT(
      `beauty`.`name`,
      'AND',
      `boys`.`boyName`
    ) INTO nandn 
  FROM
    `beauty` 
    JOIN `boys` 
      ON `beauty`.`boyfriend_id` = `boys`.`id` 
  WHERE `beauty`.`name` = bname ;
END $

DELIMITER $
CALL p5('热巴',@nn)$
SELECT @nn;

6. 创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录，用LIMIT

#创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录，用LIMIT
DELIMITER $
CREATE PROCEDURE p6 (IN size INT, IN `offset` INT) 
BEGIN
  SELECT 
    * 
  FROM
    `beauty` 
  LIMIT `offset`, size ;
END $
DELIMITER $
CALL p6(5,2)$

4、函数

存储过程：
可以有0个返回值，也可以有多个返回值，适合批量插入、批量更新

函数：
有且仅有1个返回值，适合做处理数据后返回一个结果

创建的所有存储过程和函数在mysql库中的proc表格里都有体现

4.1、增删查改

创建：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
 BEGIN 
   函数体
 END 结束标记

注意：

1. 参数列表只有2部分：参数名、参数类型
2. 函数体一定有return语句，没有会报错，一般放在函数体最后RETURN 值;
3. 如果函数体仅有一句话，BEGIN END可省略
4. 使用 DELIMITER 设置结束标记，DELIMITER 结束标记

调用（执行）：
SELECT 函数名(参数列表);

查看：
SHOW CREATE FUNCTION 函数名;

删除：
DROP FUNCTION 函数名;

创建的所有存储过程和函数在mysql库中的proc表格里都有体现

4.2、练习

1. 无参有返回

案例：
返回公司的员工数

#返回公司的员工数
USE `myemployees`;
DELIMITER $
CREATE FUNCTION f1 () RETURNS INT 
BEGIN
  DECLARE c INT DEFAULT 0 ;#定义变量
  SELECT 
    COUNT(*) INTO c #赋值 
  FROM
    `employees` ;RETURN c ;
END $
DELIMITER $
SELECT f1() $

2. 有参有返回

案例1：
根据员工名，返回他的工资

#根据员工名，返回他的工资
DELIMITER $
CREATE FUNCTION f2 (`last_name` VARCHAR (20)) RETURNS DOUBLE 
BEGIN
  DECLARE salary DOUBLE DEFAULT 0 ;
  SELECT 
    e.`salary` INTO `salary` 
  FROM
    `employees` AS e 
  WHERE e.`last_name` = `last_name` ;
  RETURN `salary`;
END $
DELIMITER $
SELECT f2('Zlotkey') 工资$

案例2：
根据部门名，返回该部门的平均工资

#根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION f3 (`department_name` VARCHAR (20)) RETURNS DOUBLE 
BEGIN
  SET @avg = 0 ;
  SELECT 
    AVG(`salary`) INTO @avg 
  FROM
    `departments` d 
    LEFT JOIN `employees` e 
      ON e.`department_id` = d.`department_id` 
  WHERE d.`department_name` = `department_name` ;
  RETURN @avg;
END $
DELIMITER $
SELECT f3('Acc')$

案例3：
创建函数，传入两个float，返回二者之和

#创建函数，传入两个float，返回二者之和
DELIMITER $
CREATE FUNCTION f4 (f1 FLOAT, f2 FLOAT) RETURNS FLOAT 
BEGIN
  SET @f = f1 + f2 ;RETURN @f ;
END $
DELIMITER $
SELECT f4(5,8) 二者之和$

5、流程控制结构

分类：
顺序结构
分支结构
循环结构

5.1、分支结构

5.1.1、if 函数

语法：
IF(expr1,expr2,expr3)

5.1.2、case 结构

第一种，相当于Java中的Switch-case语句，一般用于等值判断

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要返回的值1或语句1;   #注意这里的分号，是返回值就不要用
WHEN 常量2 THEN 要返回的值2或语句2;   #显示值     语句;
...
ELSE 要返回的值n或语句n;       #else这一句可以省略
END

第二种，相当于Java中的多重 if 语句，一般用于区间判断

CASE 
WHEN 条件1 THEN 要返回的值1或语句1;  #显示值     语句;
WHEN 条件2 THEN 要返回的值2或语句2;  #注意这里的分号，是返回值就不要用
...
ELSE 要返回的值n或语句n;       #else这一句可以省略
END

特点：

1. 作为表达式，可嵌套在其他语句中使用，可放在任何地方
2. 作为独立语句，只能放在 BEGIN END中

作为表达式

作为独立语句

案例：
创建存储过程，根据传入成绩，来显示等级，90-100 显示A，80-90 显示B，60-80 显示C，否则，显示D

5.1.3、if 结构

可实现多重分支

只可用在 BEGIN END 结构中

语法：

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
【ELSE 语句n;】 
END IF;

案例：
根据传入成绩，来显示等级，90-100 显示A，80-90 显示B，60-80 显示C，否则，显示D

5.2、循环结构

分类：
while、loop、repeat

iterate：继续，结束本次循环，继续下一次，类似于continue

leave：跳出，结束当前所在的循环，类似于break

注意：使用以上两个关键字时，就要结合循环结构标签
iterate 标签、leave 标签

循环结构只可放于 BEGIN END 结构中

5.2.1、while

语法：

【标签:】
WHILE
  循环条件 DO 循环体 ;
END WHILE 【标签】 ;

类似于Java中：while (循环条件) {循环体;}

案例1：
批量插入，根据次数插入到admin表中多条数据

#批量插入，根据次数插入到admin表中多条数据
#java中 int i = 1 ;while(i <= 5) { 插入语句 ;i + + ;}
DELIMITER $
CREATE PROCEDURE p7 (IN i INT) 
BEGIN
  SET @i = 1 ;
  WHILE
    @i <= i DO 
    INSERT INTO `admin` 
    VALUES
      (NULL, 'jerry', CONCAT('11', @i)) ;SET @i = @i + 1 ;
  END WHILE ;
END $
DELIMITER $
CALL p7(3)$

添加leave语句
案例2：
批量插入，根据次数插入到admin表中多条数据，当次数>20，循环终止

#批量插入，根据次数插入到admin表中多条数据，当次数>20，循环终止
#java中 int i = 0 ;while(i <= x) { i++; if i>20 break; else 插入语句;}
DELIMITER $
CREATE PROCEDURE p8 (IN i INT) 
BEGIN
  SET @i1 = 0 ;
  a :
  WHILE
    @i1 <= i DO SET @i1 = @i1 + 1 ;
    IF @i1 > 20 
    THEN LEAVE a ;
    ELSE 
    INSERT INTO `admin` 
    VALUES
      (NULL, CONCAT('jerry', @i1), '11') ;
    END IF ;
  END WHILE ;
END $
DELIMITER $
CALL p8 (21) $

添加iterate语句
案例3：
批量插入，根据次数插入到admin表中多条数据，只插入偶数次

#批量插入，根据次数插入到admin表中多条数据，只插入偶数次
#java中 int i = 0 ;while(i <= x) { i++; if i%2=1 continue; else 插入语句;}
DELIMITER $
CREATE PROCEDURE p9 (IN i INT) 
BEGIN
  SET @i2 = 0 ;
  a :
  WHILE
    @i2 <= i DO SET @i2 = @i2 + 1 ;
    IF MOD(@i2,2) != 0
    THEN ITERATE a ;
    ELSE 
    INSERT INTO `admin` 
    VALUES
      (NULL, CONCAT('jerry', @i2), '11') ;
    END IF ;
  END WHILE ;
END $
DELIMITER $
CALL p9 (21) $

5.2.2、loop

可以模拟简单的死循环

语法：

【标签】 :
LOOP
  循环体 ;
END LOOP 【标签】 ;

5.2.3、repeat

语法：

【标签】 :
REPEAT
  循环体 ;UNTIL 结束循环的条件 
END REPEAT 【标签】 ;