MySql,视图,视图算法,order group, Procedure 等 (学习笔记)

 

Order by和Group by 

Order by : 对查询结果集asc(升序) desc(降序)  默认:升序
Group by : 细化聚集函数的作用队形;比如  money=5 ;group by money :所有money=5,会放在一起
           count sum avg max min:如不加group by 那么作用对象是所有值
Warning:    select 中 group by 要在 order by 前面
            

 View视图

View : 数据库只存储视图的定义，视图对应的数据仍基本表 
       定义后，可以CURD . 绑定基本表数据，视图改变，基本表也会随之改变，两者互通

定义: create view as select * from account;
      create view as select id as ceshi_id,name as ceshi_name : 指定名称 


双生视图: 不同插入数据，不能同时更新两个表,只能选择一个表的字段 进行插入 

 group by id desc order by money asc

select * from view_account group by id desc;  // group by id 降序                             
+----+------+-------+
| id | name | money |
+----+------+-------+
|  9 | ggg  |  5800 |
|  8 | bbb  |  5800 |
|  5 | ccc  |  1000 |
|  4 | ffff |  3000 |
|  3 | aaa  |  3000 |
select * from view_account order by money asc;  // money 升序
+----+------+-------+
| id | name | money |
+----+------+-------+
|  5 | ccc  |  1000 |
|  3 | aaa  |  3000 |
|  4 | ffff |  3000 |
|  8 | bbb  |  5800 |
|  9 | ggg  |  5800 |
+----+------+-------+
select * from view_account group by id desc order by money asc; //前两个合起来
+----+------+-------+                          
| id | name | money |
+----+------+-------+
|  5 | ccc  |  1000 |
|  3 | aaa  |  3000 |
|  4 | ffff |  3000 |    //和money 降序一样, 而且 money=3000相同的值，没有按 group降序排列
|  8 | bbb  |  5800 |
|  9 | ggg  |  5800 |   //说明 group by 和 order by 在一边，order by会覆盖 group by 


 

视图算法

视图算法：create algorithm=merge view view_name as select * from account order by id desc;
          1：merge:合并   
          2: temptable 临时表
          3：undefined 未定义 由mysql决定用 merge还是 temptable,默认 merge

merge: 合并
create algorithm=temptable view temp as select * from account  order by id desc;
    
select * from temp ||:order by 此order by 会和之前order 合并，而且在先于它
                   ||:group by 此group 会放在之前的 order前面 
                     group by money asc 此时 先分类，在升序，在按之前的order by 


temptable: 临时表  只能存在一个

create  algorithm=temptable view temp as select * from account  order by id desc;

select * from temp || :order by id asc; //此order by 会和之前order 合并，而且在先于它
                   || : group by money ;  //只有此group by 之前的order by没有了
                            

 Union 

UNION : 联合，值必须绝对相等
         union定义:  联合值，相同的去除
         union all 定义: 相同不相同都会合并 
  例如: select id,name from student01 union  select id,name from student02
      两个查询字段 必须数量相同,位置相同, 字段类型可以不相同，照样会合并

    select * from student01 union select * from student01 
     查询的 * 所有字段值完全相同，才不会被去除

Procedure

procedure : 程序 相当于一个方法
 
DELIMITER //
create procedure a()
begin 
select * from student01;
end
call a() //使用这段程序
drop procedure a   //删除程序
show create procedure a  //显示


create procedure aa(IN s int)  输入参数
call aa()


create procedure proc1(OUT s int) 输出参数
call proc1(@sum);  //使用,将输出值复制给 @sum
select @sum  //显示@sum

create procedure proc2(INOUT age int)
set @a=8;    //参数即使输入输出，所以先设置初始值
call proc2(@a)   //调用即可