一、case when
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。以此类推
示例：
mysql> create table test_rank(id int, score int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test_rank values(1, 10), (2, 20), (3, 30), (4, 30), (5, 40), (6, 40);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test_rank;
+------+-------+
| id | score |
+------+-------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 30 |
| 5 | 40 |
| 6 | 40 |
+------+-------+
6 rows in set (0.00 sec)
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 -- case 开始的，end结尾
-> from test_rank
-> order by score desc;
+------+-------+-------------+
| id | score | rank_column |
+------+-------+-------------+
| 5 | 40 | NULL |
| 6 | 40 | NULL |
| 3 | 30 | NULL |
| 4 | 30 | NULL |
| 2 | 20 | NULL |
| 1 | 10 | NULL |
+------+-------+-------------+
6 rows in set (0.00 sec)
mysql>
同时，上述示例也是一个rank的示例 给出不同的用户的分数，然后根据分数计算排名
case
--相等则prev_value不变， 并返回rank_count（第一次为NULL，不会相等，所以跳转到下一个when语句）
when @prev_value = score then @rank_count
--不等，则第N行的score赋值(:=)给prev_value。且rank_count增加1
when @prev_value := score then @rank_count := @rank_count + 1
end as rank_column -- case 开始的，end结尾
二、视图
1.视图的作用
视图的作用是，可以对开发人员透明，可以隐藏部分关键的列 视图在MySQL中是虚拟表。根据视图的定义，还是取执行定义中的select语句。
2.示例
对上面的test_rank创建一个视图。也可以通过where进行过滤再创建。
mysql> create view view_rank as select * from test_rank;
Query OK, 0 rows affected (0.01 sec)
//真正的表通过 show table 得到的是表结构
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)
//而视图通过 show table 得到的是视图的定义，不会得到表结构
mysql> show create table view_rank \G
*************************** 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`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
可以正确查询到结果集
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> create view view_rank_1 as select id from test_rank;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from view_rank_1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
创建视图最好是对每个列进行明确列举，因为即使使用seect * from去创建视图，MySQL也会逐个去解析成列。
而当原来的表结构发生变化时，视图的表结构是不会发生变化的，视图在创建的瞬间，便确定了结构。
例如，当alter原来的表 增加列(add columns)时,再去查询该视图,新增加的列是不存在的。
//增加一列名字为c，默认值为0
mysql> alter table test_rank add column c int default 0;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
//查询原表，新的列c出现了
mysql> select * from test_rank;
+------+-------+------+
| id | score | c |
+------+-------+------+
| 1 | 10 | 0 |
| 2 | 20 | 0 |
| 3 | 30 | 0 |
| 4 | 30 | 0 |
| 5 | 40 | 0 |
| 6 | 40 | 0 |
+------+-------+------+
6 rows in set (0.00 sec)
//尽管view_rank用select * 创建，但当时没有列c，所以无法得到c列的值
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中的视图都是虚拟表。Oracle中的视图也类似，而Oracle提供了物化视图，可以将视图变为真实存在的表。 每次查询视图，实际上还是去查询的原来的表，只是查询的规则是在视图创建时经过定义的。
3.视图的算法
视图的算法( ALGORITHM )有三种：
- UNDEFINED ：默认方式，让MySQL来判断使用下面的哪种算法
- MERGE ： 每次通过物理表 查询得到结果，把结果merge(合并)起来返回
- TEMPTABLE ： 产生一张临时表，把数据放入临时表后，客户端再去临时表取数据（不会缓存）
- TEMPTABLE 特点 ：即使访问条件一样，第二次查询还是会去读取物理表中的内容，并重新生成一张临时表,并不会取缓存之前的表。 （临时表是 Memory 存储引擎，默认放内存，超过配置大小放磁盘）
- 当查询有一个较大的结果集时，使用 TEMPTABLE 可以快速的结束对该物理表的访问，从而可以快速释放这张物理表上占用的资源。然后客户端可以对临时表上的数据做一些耗时的操作，而不影响原来的物理表。
一般我们使用 UNDEFINED ，由MySQL自己去判断。
三、UNION 与 UNION ALL
- UNION 的作用是将两个查询的结果集进行合并。
- UNION必须由两条或两条以上 的SELECT语句组成，语句之间用关键字 UNION 分隔。
- UNION中的每个查询必须包含相同的列（ 类型相同或可以隐式转换 ）、表达式或聚集函数。
- 如果知道数据本身具有唯一性，没有重复，建议使用 UNION ALL，因为 UNION 会做去重操作，性能会比 UNION ALL要低
mysql> create table test_union_1(a int, b int);
Query OK, 0 rows affected (0.02 sec)
mysql> create table test_union_2(a int, c int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test_union_1 values(1, 2), (3, 4), (5, 6), (10, 20);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test_union_2 values(10, 20), (30, 40), (50, 60);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_union_1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 10 | 20 |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from test_union_2;
+------+------+
| a | c |
+------+------+
| 10 | 20 |
| 30 | 40 |
| 50 | 60 |
+------+------+
3 rows in set (0.00 sec)
mysql> select a, b from test_union_1 union select * from test_union_2;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 10 | 20 |
| 30 | 40 |
| 50 | 60 |
+------+------+
6 rows in set (0.00 sec)
mysql> select a, b from test_union_1
-> union all
-> select * from test_union_2;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 10 | 20 |
| 10 | 20 |
| 30 | 40 |
| 50 | 60 |
+------+------+
7 rows in set (0.00 sec)
mysql> select a, b from test_union_1 where a > 2
-> union
-> select * from test_union_2 where c > 50;
+------+------+
| a | b |
+------+------+
| 3 | 4 |
| 5 | 6 |
| 10 | 20 |
| 50 | 60 |
+------+------+
4 rows in set (0.00 sec)