视图 + 变量

  • 视图的修改
    • 方式1：create or replace view 视图名 as 查询语句；
    • 方式2：alter view 视图名 as 查询语句；
  • 删除视图
    • 语法：drop view 视图名，视图名，…；
  • 查看视图
    • 语法：

show create view 视图名；

desc 视图名；

  • 案例1：创建一个视图emp_v1，要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW emp_v1 AS
SELECT
last_name,
salary,
email
FROM
employees
WHERE phone_number LIKE '011%' ;

  • 案例2：创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

CREATE OR REPLACE VIEW emp_v2 AS
SELECT
MAX(salary) mx,
department_id
FROM
employees
GROUP BY department_id
HAVING MAX(salary) > 12000 ;

SELECT
*
FROM
emp_v2 ;

SELECT
d.*,
m.mx
FROM
departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id` ;

  • 视图的更新
    • 增、删、改（视图基于的表也会发生更改）

CREATE OR REPLACE VIEW myv1 AS
SELECT
last_name,
email
FROM
employees;

SELECT * FROM myv1;

# 插入
INSERT INTO myv1 VALUES('张飞', 'zhangfei');

# 修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name = '张飞';

# 删除
DELETE FROM myv1 WHERE last_name='张无忌';

  • 视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。（注意：视图一般用于查询，而不是更新。）
    • 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或union all
    • 常量视图
    • select中包含子查询
    • join
    • from 一个不能更新的视图
    • where子句的子查询应用了from子句中的表
  • 视图和表的对比

 

 

 

  • 测试题
    • 题1：创建表

CREATE TABLE book (
bid INT PRIMARY KEY,
bname VARCHAR (20) UNIQUE nut NULL,
price FLOAT DEFAULT 10,
btypeid INT,
FOREIGN KEY (btypeid) REFERENCES booktype (id)
) ;

    • 题2：开启事务，向表中插入1行数据，并结束

SET autocommit = 0 ;
INSERT INTO book(bid, bname, price, btypeid)
VALUES(1, '小李飞刀', 100, 1);
COMMIT;

    • 题3：创建视图，实现查询价格大于100的书名和类型名

CREATE VIEW myv1 AS
SELECT
bname,
NAME
FROM
book b
INNER JOIN booktype t
ON b.btypeid = t.id
WHERE price > 100 ;

    • 题4：修改视图，实现查询价格在90-120之间的书名和价格

CREATE OR REPLACE VIEW myv1 AS
SELECT
bname,
price
FROM
book
WHERE price BETWEEN 90
AND 120 ;

    • 题5：删除刚才创建的视图

DROP VIEW myv1;


变量

  • 系统变量
    • 说明：变量由系统提供，不是用户定义，属于服务器层面
    • 注意：如果是全局级别，则需要加global；如果是会话级别，则需要加session；如果不写，则默认session
    • 使用的语法：
      • 查看所有的系统变量

SHOW GLOBAL|【SESSION】 VARIABLES;

      • 查看满足条件的部分系统变量

SHOW GLOBAL|【SESSION】 VARIABLES LIKE '%char%';

      • 查看指定的某个系统变量的值

SELECT @@GLOBAL|【SESSION】.系统变量名;

      • 为某个系统变量赋值
        • 方式一

set GLOBAL|【SESSION】 系统变量名 = 值;

        • 方式二

set @@GLOBAL|【SESSION】.系统变量名 = 值;

    • 分类：
      • 全局变量
        • 服务器层面上的，必须拥有super权限才能为系统变量赋值。
        • 作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启。
        • 查看所有的全局变量

SHOW GLOBAL VARIABLES;

        • - 查看部分的全局变量

SHOW GLOBAL VARIABLES LIKE ‘%char%’;

        • - 查看指定的全局变量的值

SELECT @@global.autocommit;
SELECT @@global.tx_isolation;


- 为某个指定的全局变量赋值

- 方式1：

```
set global autocommit=0;
```

- 方式2：

```
SET @@global.autocommit=0;
```

    • 会话变量
      • 服务器为每一个连接的客户端都提供了系统变量。
      • 作用域：仅仅针对于当前会话（连接）有效。
      • 查看所有的会话变量

SHOW 【SESSION】 VARIABLES;
```

查看部分的会话变量

SHOW 【SESSION】 VARIABLES LIKE ‘%char%’;
```

查看指定的某个会话变量

SELECT @@【SESSION.】autocommit;
```

为某个会话变量赋值

方式1：

set session autocommit=0;
```

- 方式2：

```
SET @@【session.】autocommit=0;
```

  • 自定义变量
    • 变量是用户自定义的，不是由系统定义的
    • 使用步骤：声明 赋值 使用（查看、比较、运算等）
    • 分类
      • 用户变量
        • 作用域：针对于当前会话（连接）有效，等同于会话变量的作用域
        • 应用在任何地方，也就是begin end里面或begin end的外面

声明并初始化（三种方式）

set @用户变量名=值；
set @用户变量名:=值；（推荐）
select @用户变量名:=值；

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

1.方式1：通过set或select（同上）

set @用户变量名=值；
set @用户变量名:=值；（推荐）
select @用户变量名:=值；

案例1：

SET @name='John';
SET @name=100;

2.方式2：通过select into

select 字段 into 变量名
from 表；

案例1：

SELECT
COUNT(*) INTO @count
FROM
employees ;

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

select @用户变量名；

局部变量

作用域：仅仅在定义它的begin end中有效
应用在begin end中的第一句话

 

1.声明

declare 变量名 类型；

declare 变量名 类型 default 值；

2.赋值

方式1：通过set或select（同上）

set 局部变量名=值；
set 局部变量名:=值；（推荐）
select @局部变量名:=值；


方式2：通过select into

select 字段 into 局部变量名
from 表；

3.使用

select 局部变量名；

  • 对比用户变量和局部变量：

 

 

 

 

  • 案例1：声明两个变量并赋初始值，求和，并打印
    • 用户变量

SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;

    • 局部变量

# 报错
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

原文链接：https://blog.csdn.net/qq_21579045/article/details/98111827

posted @ 2021-04-03 16:04  小码农2  阅读(226)  评论(0)    收藏  举报