数据库的高级应用

一、存储过程简介

1、存储过程简介

       SQL语句需要先编译，后执行；

       而存储过程（stored Procedure）是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

• 数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式
• 存储过程是可编译的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。

2、存储过程的优点：

        （1）增强SQL语言的功能和灵活性：

                        存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算

        （2）标准组件式编程：

                      存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以                 随时对存储过程进行修改，对应程序源代码毫无影响

        （3）较快的执行速度：

                     如果某一操作包含大量批处理的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快                很多。因为存储过程是预编译的。

                      在首次运行一个存储过程时查询，优化器对齐进行分析优化，并且给出最终被存储在系统中的执行计划。而批处理                的 Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些

        （4）减少网络流量：

                      针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过                  程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低网络负载。

        （5）作为一种安全机制来充分利用：

                         通过对执行某一存储过程的权限进行限制，能够实现对应的数据访问权限的限制，避免了非授权用户对数据的访                   问，保证了数据的安全 。                        

二、存储过程

建库建表

 create database tianbo charset=utf8;               //创建数据库tianbo；

create table student (id int primary key auto_increment,name text)charset=utf8;               //创建表：student

insert into student (id,name) values (1,'天欧巴'),(2,'好困');                              //往表里插入两条数据

create table teacher (id int primary key auto_increment,tname text,tage int)charset=utf8;      //建表：teacher

insert into teacher (tname,tage) values('天天',24),('珂珂',15);                           //往teacher表中插入两个数据

1、创建存储过程

      （1）分隔符

                         MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程                   会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会                   执行这些代码；“DELIMITER ;”的意为把分隔符还原。

       （2）参数的类别

                    1）参数类型有三种：

                            输入参数（in）：IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

                            输出参数（out）：OUT:该值可在存储过程内部被改变，并可返回

                            输入输出（inout）：INOUT:调用时指定，并且可被改变和返回

                     2）过程体

                              过程体的开始与结束使用BEGIN与END进行标识。

      （3）语法：

               delimiter **                                //将结束符设置为**

                    create procedure 存储过程名（in/outinout 参数 参数类型，……）

                        begin

                          SQL语法；                   //如果存储过程中就一句SQL语句，begin……end两个关键字，可以省略

                       end **

                delimiter ；                              //把结束符再设置会‘ ；’                   

               call 存储过程名（[参数]）；     //调用存储过程

   例如：

                 mysql> delimiter //

                 mysql> create procedure pro1(in param int)           #in:代表的是输入参数，可以省略

                            -> begin

                            -> select * from student where id=param;

                             -> end //

                  Query OK, 0 rows affected (0.00 sec)

                   mysql> set @param=2；                       #用户变量一般以@开头，注意：滥用用户变量会导致程序难以理解及管理

                  Query OK, 0 rows affected (0.00 sec)

                  mysql> call pro1(@param);

                  +----+--------+

                   | id | name   |

                  +----+--------+

                   |  2 | 好困   |

                  +----+--------+

                  1 row in set (0.00 sec)

                   Query OK, 0 rows affected (0.00 sec)         

2、删除存储过程：

            语法：drop    procedure [if exists] 存储过程名

            例如：drop procedure if exists pro1；                                             

3、查看存储过程的信息：

             语法：select name from mysql.proc where db=“数据库名”；

                          select name from mysql.proc where db="tianbo";

                                +------+

                               | name |

                                +------+

                               |  pro1  |

                                +------+

                               1 row in set (0.00 sec)

            语法： show procedure status where db=“数据库名”；

            查询存储过程的详细信息：SHOW CREATE PROCEDURE 数据库.存储过程名;

4、局部变量

        语法：declare 变量名 数据类型 [初始值]

         通过：select……into……或set命令给变量赋值

          例如：通过id查询姓名和年龄

                 delimiter //

                 mysql> create procedure pro3(in sid int)

                         -> begin

                         -> declare sname text;

                         -> select name into sname from student where id=sid;

                         -> select sname from dual;

                         -> end //

                  Query OK, 0 rows affected (0.00 sec)

 

                  mysql> delimiter ;

                  mysql> call pro3(1);

                  +-----------+

                 | sname     |

                  +-----------+

                 | 天欧巴    |

                 +-----------+

                 1 row in set (0.00 sec)

                 Query OK, 0 rows affected (0.00 sec)

        注意：声明的变量名不能和列名（字段名）同名

5、全局变量（用户变量）      

       全局变量前面必须有一个@，全局变量的数据类型取决于变量的值。如果一个变量没有赋值，他的数据类型为null；

        set @sname=“可以”；

       select * from student where name=@name;

6、系统变量：通过两个@@开头的都是系统变量

          select @@version from dual;

系统命令	作用
@@version	版本号
current_date	当前日期
current_time	当前时间
current_timestamp	当前日期和时间

7、带有输出参数的存储过程

mysql> delimiter //

mysql> create procedure pro5(in age int,out result int)

    -> begin

    -> set result=age*age;

    -> end //

Query OK, 0 rows affected (0.05 sec)

 

mysql> call pro5(15,@result)//

Query OK, 0 rows affected (0.05 sec)

 

mysql> select @result from dual //

+---------+

| @result |

+---------+

|     225 |

+---------+

1 row in set (0.00 sec)

8、带有输入输出参数的存储过程

create procedure pro6(inout age int)

    -> begin

    -> set age=age*age;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> set @age=10 //

Query OK, 0 rows affected (0.00 sec)

 

mysql> call pro6(@age)//

Query OK, 0 rows affected (0.00 sec)

 

mysql> select @age //

+------+

| @age |

+------+

|  100 |

+------+

1 row in set (0.00 sec)

9、SQL编程（了解）

（1）if-elesif-else语句

语法：

          if 条件 then

                 //SQL代码

       elseif  条件  then

                 //SQL代码

        end if；

举例：

 

create procedure pro7(in sid int)

    -> begin 

    -> if sid =1 then

    -> select '金牌会员' as '等级';

    -> elseif sid=2 then

    -> select '普通会员' as '等级';

    -> else 

    -> select '游客' as '等级';

    -> end if;

    -> end //

Query OK, 0 rows affected (0.07 sec)

 

mysql> call pro7(3) //

+--------+

| 等级   |

+--------+

| 游客   |

+--------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

（2）case-when语句

create procedure pro8(in sid int)

    ->      begin 

    ->      case sid

    ->      when 1 then select '看见了健康' as '气质';

    ->      when 2 then select '屌丝' as '气质';

    ->      when 3 then select '正常人' as '气质';

    ->      when 4 then select '贵族' as '气质';

    ->      else select '输入不正确' as '气质';

    ->      end case;

    ->      end //

Query OK, 0 rows affected (0.00 sec)

 

mysql> call pro8(0) //

+-----------------+

| 气质            |

+-----------------+

| 输入不正确      |

+-----------------+

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

（3）loop循环

create procedure proc(in num int)

    -> begin

    -> declare total int default 0;

    -> declare i int default 0;

    -> sign:loop

    -> set total =total+i;

    -> set i=i+1;

    -> if i>num then

    -> leave sign; #leave=break

    -> end if;

    -> end loop;

    -> select total from dual;

    -> end //

Query OK, 0 rows affected (0.00 sec)

 

mysql> call proc(100) //

+-------+

| total |

+-------+

|  5050 |

+-------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

#如果没有设置标签名,leave loop

#sign是循环名,用于结束循环,可以自己随意取名字

（4）while循环

  语法：

           while 条件 do

                SQL代码

          end while

 举例：

create procedure pro10(in num int)

    -> begin

    -> declare total int default 0;

    -> declare i int default 0;

    -> while num>=i do

    -> set total=total+i;

    -> set i=i+1;

    -> end while;

    -> select total from dual;

    -> end //

Query OK, 0 rows affected (0.00 sec)

 

mysql> call pro10(100) //

+-------+

| total |

+-------+

|  5050 |

+-------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

（5）repeat循环

         语法：

           repeat

                  SQL代码

                  until 条件        --知道重复到条件为true才结束

          end repeat

           举例：

create procedure pro11(in num int)

    -> begin 

    -> declare total int default 0;

    -> declare i int default 0;

    -> repeat

    -> set total=total+i;

    -> set i=i+1;

    -> until i>num

    -> end repeat;

    -> select total from dual;

    -> end //

Query OK, 0 rows affected (0.00 sec)

 

mysql> call pro11(100) //

+-------+

| total |

+-------+

|  5050 |

+-------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

（6）leave和iterate

          leave类似于break，iterate类似于continue

          举例：

create procedure pro12()

    -> begin

    -> declare i int default 0;

    -> sign:while i<5 do

    -> set i=i+1;

    -> if(i=3) then

    -> leave sign;

    -> end if;

    -> select i from dual;

    -> end while;

    -> end //

Query OK, 0 rows affected (0.00 sec)

 

mysql> call pro12() //

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

 

+------+

| i    |

+------+

|    2 |

+------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

10、内置函数

 （1）数字类

语句	含义
select rand() from dual;	随机数
select * from student order by rand();	随机排序
select round(5.6);	四舍五入
select ceil(5.3);	向上取整
select floor(5.6);	向下取整

（2）大小写转换

语句	含义
select ucase(‘i am lyb’);	大写
select lcase(‘I AM LYB’);	小写

（3）截取字符串

语句	含义
select left(‘abcdefg’,3);	截取左边的3位
select right(‘abcdefg’,3);	截取右边3位
select substring(‘abcdefg’,2,3);	从第2位开始取3个字符，起始位置从1开始

（4）字符串拼接

select concat(tname,tage) from teacher //

+--------------------+

| concat(tname,tage) |

+--------------------+

| 天天24             |

| 珂珂15             |

+--------------------+

2 rows in set (0.00 sec)

（5）coalesce（str1，str2）：如果str1不为null显示str1，否则显示str2

（6）length（字节长度，查的是几个字节）、char_length（字符长度，查的是字符）、trim（去两边空格）、repace（替代）

                   MySQL 5.0以上的版本：

                   1）一个汉字占多少长度与编码有关：

                         UTF－8：一个汉字＝3个字节

                         GBK：一个汉字＝2个字节

                   2）varchar(n)表示n个字符，无论汉字和英文，Mysql都能存入n个字符，仅是实际字节长度有所区别

                    3）MySQL检查长度，可用SQL语言：

                          select LENGTH(fieldname) from tablename 来查看

 

举例：select length('很纠结');      /查的是字节

+---------------------+

| length('很纠结')    |

+---------------------+

|                   9 |

+---------------------+

1 row in set (0.07 sec)

 

 举例： select replace('pgone','one','two');

+------------------------------+

| replace('pgone','one','two') |

+------------------------------+

| pgtwo                        |

+------------------------------+

1 row in set (0.06 sec)

（7）时间戳：unix_timestamp

         举例：select unix_timestamp();

+------------------+

| unix_timestamp() |

+------------------+

|       1535369254 |

+------------------+

1 row in set (0.02 sec)

（8）将时间戳转化成当前时间

            举例：select from_unixtime(354654654);

+--------------------------+

| from_unixtime(354654654) |

+--------------------------+

| 1981-03-29 03:10:54      |

+--------------------------+

1 row in set (0.00 sec)

（9）获取当前时间

（10）dayname(),monthname(),dayofyear()

（11）datediff（结束日期，开始日期）

       举例：计算自己活了多少天？

（12）md5（）:md5加密

三、自定义函数

    语法；

            create function 函数名（形参）returns 返回的数据类型

            begin

                  //函数体

            end 

      select 函数名（实参）； 

四、触发器

1、   简介：

                    （1）触发器是一个特殊的存储过程

                    （2）不需要直接使用，在MYSQL自动调用

                    （3）是一个事务，可以回滚

2、触发器的类型（触发事件）

          （1）insert触发器

          （2）update触发器

          （3）delete触发器

3、创建触发器

     语法：

              create trigger 触发器名称 触发时间 [before][after]触发事件 on 表名 for each row

               begin

                    //代码

               end

4、new表和old表

    （1）这两个表是个临时表

    （2）当触发器触发的时候再内存中自己创建，触发器执行完毕后自动销毁

    （3）他们的表结构和触发器触发的表结构一样

    （4）只读、不能修改

    （5）打开文件，内存中需要加载，会随机分配一个空间用来保存文件的所有数据，-》old 

    （6）在新的一轮操作后，内存会生成新的空间，这个空间里面保存了新的数据变化-》new

5、insert触发器

     #在stuinfo中插入一个值，就会自动在student中插入一条数据

     #after insert 表示的是在insert动作执行完毕以后触发 

     #on stuinfo for each row 针对的stuinfo表,并且可以读取到每一行的变化

     #触发器中定义的局部变量不能与表中的字段名一致,否则会发生字段识别问题(识别不出到底是字段,还是变量)

                

https://blog.csdn.net/beichen0518/article/details/80820335

6、update触发器

    

7、delete触发器

8、查看和删除触发器

show triggers;

drop trigger if exists trig1;

（了解）五、用户管理

1、创建用户

    语法: create user '用户名' @ ‘允许登录的主机地址’ identified by 密码

          %代表数据库的库名

      create user 'tttttt' @ '%' identified by '123456';

2、删除用户

        语法：drop user 用户；

        drop user tttttt;      

3、增加用户权限

            将teacher的所有表的select权限付给tttttt用户

        

           将所有数据库中的所有表的所有权限付给tttttt用户  

        

           创建用户并授权

         

          创建后啊用户以后，刷新mysql用户权限表

           

             删除select权限

                   revoke select on teacher.* from 'tttttt'@'%';

              删除所有权限

              revoke all privileges on *.* from 'tttttt'@'%';