MySQL：如何实现排名以及视图查询

妖狐艹你老母 2022-03-27 12:08 521阅读 0赞

### 文章目录 ###

*  1、Rank
 *  2、视图
 *   *  ①什么是视图
     *  ②为什么要使用视图
     *  ③应用场景
     *  ④有关于视图更新

# 1、Rank #

> 给出不同的用户的分数，然后根据分数计算排名

mysql> create table test_rank(id int, score int);
    
    mysql> insert into test_rank values(1, 10), (2, 20), (3, 30), (4, 30), (5, 40), (6, 40);
    
    mysql> select * from test_rank;
    +------+-------+
    | id   | score |
    +------+-------+
    |    1 |    10 |
    |    2 |    20 |
    |    3 |    30 |
    |    4 |    30 |
    |    5 |    40 |
    |    6 |    40 |
    +------+-------+
    
    mysql> set @prev_value := NULL;  -- 假设比较到第N行，设置一个变量prev_value用于存放第N-1行score的分数
                                     -- 用于比较第N行的score和第N-1行的score
                                     -- prev_value可以理解为 是临时保存第N-1行的score的变量
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set @rank_count := 0;     -- 用于存放当前的排名
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select  id, score,
        -> case
        ->     when @prev_value = score then @rank_count
               -- 相等则prev_value不变， 并返回rank_count（第一次为NULL，不会相等，所以跳转到下一个when语句）
        ->     when @prev_value := score then @rank_count := @rank_count + 1
               -- 不等，则第N行的score赋值(:=)给prev_value。且rank_count增加1
        -> end as rank_column   -- case 开始的，end结尾
        -> from test_rank
        -> order by score desc;
    +------+-------+-------------+
    | id   | score | rank_column |
    +------+-------+-------------+
    |    5 |    40 |           1 |
    |    6 |    40 |           1 |
    |    3 |    30 |           2 |
    |    4 |    30 |           2 |
    |    2 |    20 |           3 |
    |    1 |    10 |           4 |
    +------+-------+-------------+
    6 rows in set (0.00 sec)
    
    -- case
    --   when [condition_1] then [do_something_1]
    --   when [condition_2] then [do_something_2]
    --   end
    -- 语法：如果 condition_1条件满足，则执行 do_something_1 然后就跳出,不会执行condition_2;
    --       如果 condition_1条件不满足，则继续执行到 condition_2。以此类推。
    
    --
    -- 使用一条语句写出Rank
    --
    mysql> select id, score,
        -> case
        ->     when @prev_value = score then @rank_count
        ->     when @prev_value := score then @rank_count := @rank_count + 1
        -> end as rank_column
        -> from test_rank, (select @prev_value:=NULL, @rank_count:=0) as t  -- 和RowNumber思路一样，增加一个表
        -> order by score desc;
    +------+-------+-------------+
    | id   | score | rank_column |
    +------+-------+-------------+
    |    5 |    40 | 1           |
    |    6 |    40 | 1           |
    |    3 |    30 | 2           |
    |    4 |    30 | 2           |
    |    2 |    20 | 3           |
    |    1 |    10 | 4           |
    +------+-------+-------------+
    6 rows in set (0.00 sec)

# 2、视图 #

> [官方文档][Link 1]

例子：

--
    -- 创建视图
    --
    mysql> create view view_rank as select * from test_rank;  -- 针对上面的test_rank创建一个视图
    Query OK, 0 rows affected (0.03 sec)
    
    -- 也可以对select结果增加条件进行过滤后，再创建视图
    
    mysql> select * from view_rank;  -- 可以直接查询该视图得结果
    +------+-------+
    | id   | score |
    +------+-------+
    |    1 |    10 |
    |    2 |    20 |
    |    3 |    30 |
    |    4 |    30 |
    |    5 |    40 |
    |    6 |    40 |
    +------+-------+
    6 rows in set (0.00 sec)
    
    -- 视图的作用是，可以对开发人员是透明的，可以隐藏部分关键的列
    -- 视图在mysql是虚拟表。根据视图的定义，还是取执行定义中的select语句。
    
    
    -- 只开放部分列
    mysql> create view view_rank_1 as select id from test_rank; -- 只开放id列
    
    mysql> select * from view_rank_1;  -- 即使 select * ，也只能看到id列，具有隐藏原来表中部分列的功能
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    |    6 |
    +------+
    
    -- 不要取用select * from 去创建视图，因为mysql会把*逐个解析成列。
    -- 当原来的表结构发生变化时，视图的表结构是不会发生变化的，视图在创建的瞬间，便确定了结构。
    -- 比如，当你alter原来的表 增加列(add columns)时,再去查询该视图,新增加的列是不存在的。
    
    mysql> alter table test_rank add column  c int default 0; -- 增加一列名字为c，默认值为0
    
    mysql> select * from test_rank;  -- 查询原表，新的列c出现了
    +------+-------+------+
    | id   | score | c    |
    +------+-------+------+
    |    1 |    10 |    0 |
    |    2 |    20 |    0 |
    |    3 |    30 |    0 |
    |    4 |    30 |    0 |
    |    5 |    40 |    0 |
    |    6 |    40 |    0 |
    +------+-------+------+
    
    mysql> select * from view_rank; -- 尽管view_rank用select * 创建，但当时没有列c，所以无法得到c列的值
    +------+-------+
    | id   | score |
    +------+-------+
    |    1 |    10 |
    |    2 |    20 |
    |    3 |    30 |
    |    4 |    30 |
    |    5 |    40 |
    |    6 |    40 |
    +------+-------+
    
    -- 注意：mysql中的视图都是虚拟表。不像Oracle可以物化成真实存在的表。
    --      每次查询视图，实际上还是去查询的原来的表，只是查询的规则是在视图创建时经过定义的。
    
    
    
    ># 查看视图
    SELECT * FROM view_rank;
    DESC view_rank;
    SHOW CREATE VIEW view_rank;
     
    //---------------------------------------
    mysql> show create table test_rank\G
    *************************** 1. row ***************************
           Table: test_rank
    Create Table: CREATE TABLE `test_rank` (    -- 得到的是表结构
      `id` int(11) DEFAULT NULL,
      `score` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    
    mysql> show create table view_rank\G  -- 他是以一张表的形式存在的，可通过show tables看到
    *************************** 1. row ***************************
                    View: view_rank
             Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_rank` AS select `test_rank`.`id` AS `id`,`test_rank`.`score` AS `score` from `test_rank`
                          -- 和真正的表不同的是，这里show出来的是视图的定义
    character_set_client: utf8
    collation_connection: utf8_general_ci
    
    ># 删除视图
    DROP VIEW view_rank;

CREATE ALGORITHM=UNDEFINED，此为视图的算法，有三种方式：

*  `UNDEFINED`：默认方式，由MYSQL来判断使用下面的哪种算法
 *  `MERGE` ：`每次`通过`物理表`查询得到结果，把结果merge(合并)起来返回
 *  `TEMPTABLE`：产生一张`临时表`，把数据放入临时表后，客户端再去临时表取数据(`不会缓存`)
    
    > `TEMPTABLE 特点`：即使访问条件一样，第二次查询还是会去读取物理表中的内容，并重新生成一张临时表，并不会缓存之前的表。*（临时表是Memory存储引擎，默认放内存，超过配置大小放磁盘）*  
    > 当查询有一个较大的结果集时，使用`TEMPTABLE`可以快速的结束对该物理表的访问，从而可以快速释放这张物理表上占用的资源。然后客户端可以对临时表上的数据做一些耗时的操作，而不影响原来的物理表。
    
    **所以一般我们使用`UNDEFINED`，由MySQL自己去判断**

## ①什么是视图 ##

*  从SQL的角度来看，视图和表是相同的，两者的区别在于**表中保存的是实际的数据，而视图中保存的是SELECT语句**(视图本身并不存储数据)
    
     *  在创建表时，会通过INSERT语句将数据保存到数据库中，而数据库中的数据实际上会被保存到计算机的存储设备中。因此，我们通过SELECT子句查询数据时，实际上是从存储设备中读取数据，进行各种计算之后，再将结果返回给用户。
     *  使用视图时并不会将数据保存到存储设备中，而且也不会将数据保存到其他任何地方。实际上视图保存的时SELECT语句。我们会视图中读取数据时，视图会在内部执行该SELECT语句并创建出一个临时表。  
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]

## ②为什么要使用视图 ##

*  重用SQL语句
 *  简化复杂的SQL操作。在编写查询之后，可以方便地重用它而不必知道它地基本查询细节
 *  保护数据。可以给用户授权表地特定部分地访问权限而不是整个表地访问权限
 *  视图可以节省存储设备的容量，因为视图不保存实际的数据。可以将频繁使用的SELECT子句保存成视图。
 *  因为每次从视图中查询语句时，实际上是执行SELECT语句，因此可以保证数据会随原表自动更新。

## ③应用场景 ##

*  当多个地方用到了同样的查询结构
 *  该查询结果使用的SQL语句比较复杂

## ④有关于视图更新 ##

一般，应该将视图用于检索，而不用于更新[INSERT, UPDATE和DELETE],它们实际上是对基表进行更新的

*  规则和限制
 *  与表一样，视图必须唯一命名
 *  对于可以创建的视图数据没有限制
 *  为了创建视图，必须具有足够的访问权限，这些限制通常由数据库管理人员授予
 *  视图不能索引，也不能有关联的触发器或者默认值
 *  视图可以和表一起使用。也可以删除，过滤，排除等，不过这些**操作都在基表中进行**
 *  定义视图中不要使用ORDER BY子句
 *  注意：
    
     *  **视图一般用于检索**，不建议修改视图
     *  视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从
     *  其他表中检索出来的。在添加和更改这些表的数据时，视图将修改基表。
     *  在部署使用大量视图应用前，应该进行性能测试
     *  避免在视图的基础上创建视图，多重视图会降低SQL的性能
     *  报表一般用视图做。但是视图不能传参
 *  语法：
    
     *  创建视图：
        
         *  create view 视图名  
            as  
            查询语句;
     *  创建视图：
        
         *  create or replace view 视图名  
            as  
            查询语句;
         *  alter view 视图名  
            as  
            查询语句;
         *  如果存在就修改，不存在就创建\[**极度不推荐使用**\]
     *  删除视图：
        
         *  drop view 视图名,视图名,…;

[Link 1]: http://dev.mysql.com/doc/refman/5.7/en/create-view.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: /images/20220327/645a8ff6cc78480fb68fac517f7a4f49.png