mysql 传ASC 动态排序方法 mysql视图传参

一、视图(view)
    视图工作：提供临时表的操作,只能用于查询，不能进行增删改

- 创建
        create view view1 as SQL
        视图是虚拟表
    - 修改
        alter view view1 as SQL
    - 删除
        drop view view1
    - 查询
        select * from view1

View Code

 

二、触发器(trigger)
    当对某张表做增删改查时，可以使用触发器自定义关联行为

# 首先需要修改终止符
    # delimiter 可以修改mysql中的终止符，默认是分号。
    delimiter //
    # before after 可以设置触发器在 insert/update/delete 等操作之前还是之后触发
    create trigger 触发器名字 before/after insert/update/delete on 表名(....) for each row
    # begin中是触发器执行的操作
    begin
        # new，代指新数据 用于 insert 和 update
        # old，代指旧数据 用于 delete 和 update
        # 此处的数据指的都是触发事件中新增或者删除的数据
        insert into 表名(...) values (new.sname);
    end//
    delimiter ;

View Code

三、函数(function)

    内置函数：

#执行函数(获得当前的日期)：
        select curdate()；
        #部分内置函数：
            #查询字符串长度：
                select char_length(str)；
            #拼接字符串：
                select concat(str,str,str);
            #时间格式化(将当前时间改为年-月，可用于博客文章按时间分类的操作中)：
                date_format(ctime,"%Y-%m");

View Code

内置函数官方文档
    
   自定义函数：

dilimiter//
        create function f(
            # 设置参数的名字和类型
            i1 int,
            i2 int)
        # 设置参数的返回值
        return int
        begin
            # 如果要返回值，需要declare一个新的变量
            declare num int default 0;
            set num = i1 + i2;
            return(num)；
        end\\
        delimiter;

View Code

四、存储过程(procedure)
    保存在MYSQL上的一个别名，保存了一堆SQL语句。
　　存储过程和视图的区别：视图只能用于查询，而存储过程可以进行各种操作用于替代程序员写SQL语句

　　操作数据库的三种方式：
    　　方式一：
        　　MYSQL：存储过程
        　　程序 ：调用存储过程
   　　 方式二：
        　　MYSQL：不写存储过程
        　　程序：SQL语句
    　　方式三：
       　　 MYSQL：不写存储过程
        　　程序：类和对象(最终也会转化成SQL语句)
    存储过程结构：

       1.简单：

delimiter //
        create procedure p1()
        begin
            select * from student;
            insert into teacher(tname) values("ct")；
        end//
        delimiter ;
        MYSQL中使用：call p1();
        pymysql中使用：r1 = cursor.callproc("p1")

View Code

2.传参数 in：

delimiter //
        create procedure p2(
            in n1 int,
            in n2 int
        )
        begin
            select * from student where sid > n1;
        end//
        delimiter ;
        MYSQL中使用：call p2(12,2);
        pymysql中使用：r1 = cursor.callproc("p2",(12,2))

View Code

3.传参数 out(常用于标识存储过程的执行结果)：

delimiter //
        create procedure p3(
            in n1 int,
            out n2 int
        )
        begin
            set n2 = 123123;
            select * from student where sid > n1;
        end//
        delimiter ;

        MYSQL中使用：
        set @v1 =0; 实际上@v1的值是传不进去存储过程中的
        call p3(12,@v1);
        select @v1;
        pymysql中使用：获得第二个参数值，需要再进行一次fetchall操作
        r1 = cursor.callproc("p3",(12,2))
        r2 = cursor.execute("select @_p3_0,@_p3_1")

View Code

4.inout 同时包含上面两种参数的属性，即可以传入也可以传出

       5.事务：
       用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

delimiter //
        create procedure p5(
        out p_return_code tinyint)
        # 在每一个begin end 可以定义一个退出处理程序处理不同的异常
        begin
            declare exit handler for sqlexception
            begin
            # SQL操作异常时执行下列语句
            set p_return_code =1;
            rollback;
            end;

            DECLARE exit handler for sqlwarning
            BEGIN
            set p_return_code = 0;
            rollback;
            END;

            start transaction;
                delete from tb1;
                insert into tb2(name)values('seven');
                commit;
        # SQL操作成功时执行下列语句
            set p_return_code = 2;
        end//
        delimiter ;
        MYSQL中使用：
            set @i =0;
            call p5(@i);
            select @i;

View Code

6.游标

1.声明游标
        2.获取A表中的数据
        3.循环插入B表
        delimiter //
        create procedure p6()
        begin
            declare row_id int; -- 自定义变量1
            declare row_num int -- 自定义变量2
            declare done int default false；
            declare temp int；

            declare my_cursor cursor for select id,num from A;
            declare continue handler for not found set done = true;

            open my_cursor;
             loop
                    fetch my_cursor into row_id,row_num;
                    if done then
                        leave;
                    end if;
                    set temp = row_id + row_num;
                    insert into B(num) values(temp);
                end loop ；
            close my_cursor;
        end //
        delimiter;

View Code

7.动态执行SQL(防止SQL注入)

delimiter //
        create procedure p7(
            in arg int
        )
        begin
            1.预检测SQL语句合法性
            2.格式化
            3.执行SQL

            set @xo =arg;
            prepare xxx from 'select * from student where sid>?';
            execute xxx using @xo;
            deallocate prepare prod;

        end//
        delimiter ;
#mysql客户端使用:
    call p7('select * from student where sid>?',9)

View Code