mysql中的存储过程、触发器、视图的用法

建立存储过程

Create procedure、Create function

下面是它们的格式：

Create proceduresp_Name ([proc_parameter ])

routine_body

这里的参数类型可以是 IN OUT INOUTT ，意思和单词的意思是一样的，IN 表示是传进来的参数，

OUT 是表示传出去的参数，INOUT 是表示传进来但最终传回的参数。

Create functionsp_Name ([func_parameter ])

Returns type

Routine_body

Returns type 指定了返回的类型，这里给定的类型与返回值的类型要是一样的，否则会报错。

下面给出两个简单的例子来说明：

1、 显示 Mysql 当前版本

执行结果

mysql> use welefen;

Database changed

mysql> delimiter // #定义//作为结束标记符号

mysql> create procedure getversion(out param1 varchar(50)) #param1为传出参数

-> begin

-> select version() into param1; #将版本的信息赋值给 param1

-> end

-> //

Query OK, 0 rows affected (000 sec)

mysql> call getversion(@a); #调用getversion()这个存储过程

-> //

Query OK, 0 rows affected (000 sec)

mysql> select @a;

-> //

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

| @a |

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

| 5114-beta-community-nt |

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

1 row in set (000 sec)

2、 显示”hello world”

执行结果

mysql> delimiter //

mysql> create function display(w varchar(20)) returns varchar(50)

-> begin

-> return concat('hello ‘,w);

-> end

-> //

Query OK, 0 rows affected (005 sec)

mysql> select display("world");

-> //

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

| display("world") |

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

| hello world |

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

1 row in set (002 sec)

其他 *** 作存储过程的语句

前面我们已经知道了怎么创建存储过程，下面看看其他常用的用于 *** 作存储过程的语句。

Alter {procedure | function} sp_Name []

Alter 语法是用来改变一个过程或函数的特征,当你想改变存储过程或者函数的结构时可以使

用它。当然你也可以先 drop 它再 create。

Drop {procedure | function} [if exists] sp_Name

Drop 语法即用来删除一个存储程序或者函数，当你创建的一个存储过程或者函数的名字已经存

在时，你想把以前的给覆盖掉，那么此时你就可以使用 drop ,然后在创建。

Show create {procedure | function } sp_Name

Show 语法用来显示创建的存储过程或者函数的信息。这里的 show 用法跟数据表中的 show 用

法是很相似的。

Show {procedure | function} status [like 'partten']

它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，

根据你使用的语句，所有存储程序和存储函数的信息都被列出。

看了以上的几个语法，你是不是感觉跟对表的 *** 作很相象，那你就想对了，他们确实是很相似

的。带着一份激动心情我们继续往下看，你会发现很简单。

Begin End 语句

通过 begin end 可以来包含多个语句，每个语句以“;”结尾。

Declare

用Declare 来声明局部变量

Declarevar_Name type defaulevaule

Delare 条件

Declarecondition_Name CONDITION FOR condition_value

调用存储过程

Call

格式：

Callsp_Name [parameter ]

这里的 sp_Name 必须是由 create procedure 创建的名称。它可以通过声明的参数来传回值，

它也返回受影响的行数，在 MySQL 中可以通过 mysql_affected_rows() 来获得。

流程控制语句

IF 语句

IFsearch_condition THENstatement_list

[ELSEIFsearch_condition THENstatement_list]

[ELSEstatement_list]

END IF

CASE 语句

CASE case_value

WHEN when_value THENstatement_list

WHEN when_value THENstatement_list]

ELSEstatement_list]

END CASE

LOOP 语句

[begin_label:] LOOP

statement_list

END LOOP [end_label]

LOOP 实现了一个简单的循环，通过 LEAVE 来退出

LEAVE 语句

LEAVE lable

退出语句，一般可以用在循环中。

ITERATE 语句

ITERATE lable

ITERATE 一般出现在 LOOP、REPEATE、WHILE 里，意思是再次循环。

REPEATE 语句

[begin_label:] REPEAT

statement_list

UNTILsearch_condition

END REPEAT [end_label]

REPEAT 语句内的语句或语句群被重复，直至 search_condition 为真。

WHILE 语句

[begin_label:] WHILEsearch_condition DO

statement_list

END WHILE [end_label]

WHILE 语句内的语句或语句群被重复，直至 search_condition 为真。

运用实例

下面通过几个例子来讲述他们的应用：

对网站用户的 *** 作

为了简单,用户表只有用户名和密码的信息在服务端，我们建立如下的表：

代码片段

Drop table if exists user;

Create table user(

Id int unsigned not null auto_increment,

Name varchar(20) not null,

Pwd char(32) not null,

Primary key(Id)

);

添加用户的存储过程：

代码片段

Delimiter //

Create procedure insertuser(in username varchar(20),in userpwd varchar(32))

Begin

Insert into welefenuser(Name,Pwd) values (username,md5(userpwd));

End

//

验证用户的存储过程：

代码片段

Delimiter //

Create procedure validateuser(in username varchar(20),out param1)

Begin

Select Pwd into param1 from welefenuser where Name=username;

End

//

修改密码的存储过程：

代码片段

Delimiter //

Create procedure modifyPwd(in username varchar(20),in userpwd varchar(32))

Begin

Update welefenuser set Pwd=md5(userpwd) where Name=username;

End

//

删除用户的存储过程：

代码片段

Delimiter //

Create procedure deleteuser(in username varchar(20))

Begin

delete from welefenuser where Name=username;

End

//

在客户端，我们给出如下的程序：

代码片段

文件名：ProcedureUserphp

<php

if (!mysql_connect("localhost","root","welefen")){

echo "连接数据库失败";

}

if (!mysql_select_db("welefen")){

echo "选择数据库表失败<br>";

}

$insert_user=array("welefen","welefen");//这里的welefen分别为用户名、密码

if (mysql_query("call insertuser('$insert_user[0]','$insert_user[1]')")){

echo "添加用户$insert_user[0]成功<br>";

}else {

echo "添加用户$insert_user[0]失败<br>";

}

$validate_user=array("welefen","welefen");//这里的welefen分别为用户名、密码

mysql_query("call validateuser('$validate_user[0]',@a)");

$Pwd=mysql_query("select @a");

$result=mysql_fetch_array($Pwd);

if ($result[0]==md5($validate_user[1])){

echo "用户$validate_user[0]验证正确<br>";

}else {

echo "用户$validate_user[0]验证错误<br>";

}

$modify_Pwd=array("welefen","weilefeng"); //welefen为用户名weilefeng为新密码

if (mysql_query("call modifyPwd('$modify_Pwd[0]','$modify_Pwd[1]')")){

echo "用户$modigy_Pwd[0]的密码修改成功<br>";

}else {

echo "用户$modigy_Pwd[0]的密码修改失败<br>";

}

$delete_user=array("welefen"); //welefen为用户名

if (mysql_query("call deleteuser('$delete_user[0]')")){

echo "用户$delete_user[0]删除成功<br>";

}else {

echo "用户$delete_user[0]删除失败<br>";

}

程序运行的结果：

执行结果

添加用户welefen 成功

用户welefen 验证正确

用户welefen 的密码修改成功

用户welefen 删除成功

以上的这个程序简单的说明了Mysql 中的存储过程结合PHP 的应用，当然在实际应用要比这个

复杂的多。

验证角谷猜想

角谷猜想：给定一个整数x,若x%2=1，则x=3x+1，否则x=x/2，如此循环下去，经过有限步骤必

能得到1。

例 如 ： 初 始 整 数 为 9 ， 则

9->28->14->7->22->11->34->17->52->26->13->40->20->10->5->16->8->4->2->1

为了说明存储过程中一些语法的应用，我们通过存储过程来实现它:

执行结果

mysql> delimiter //

mysql> create procedure jgguess(in number int)

-> begin

-> declare param1 int default 1;

-> set @a=concat(number);

-> jiaogu:loop #循环开始

-> set param1=number%2;

-> if param1=1 then set number=number3+1; #number 为奇数，将它乘3加 1

-> else set number=number/2;

-> end if;

-> set @a=concat(@a,'->',number);

-> if number>1 then iterate jiaogu; #number 不为 1，继续循环

-> end if;

-> leave jiaogu; #退出循环

-> end loop jiaogu;

-> end

-> //

Query OK, 0 rows affected (000 sec)

mysql> call jgguess(11);

-> //

Query OK, 0 rows affected (000 sec)

mysql> select @a//

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

| @a |

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

| 11->34->17->52->26->13->40->20->10->5->16->8->4->2->1 |

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

1 row in set (002 sec)

在这个存储过程中，你传入的参数不能超过int 型数据的范围，否则就会报错。

触发器

触发器是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。例如当我们向

某个表插入一行数据时发生一个事件或者删除某个记录时触发某个事件。

语法：

CREATE TRIGGER trigger_Name trigger_time trigger_event

ON tbl_Name FOR EACHROW trigger_stmt

trigger_time 是触发器的动作时间。它可以是 BEFORE 或 AFTER ，以指明触发器是在激活它的

语句之前或之后触发。

trigger_event 指明了激活触发器的语句的类型。trigger_event 可以是下述值之一：

INSERT：将新行插入表时激活触发器，例如，通过 INSERT、LOADDATA 和 REPLACE 语句；

UPDATE：更改某一行时激活触发器，例如，通过UPDATE语句；

DELETE：从表中删除某一行时激活触发器，例如，通过 DELETE 和 REPLACE 语句。

例如当我们向上面的user 表中增加一个用户名为“welefen ”时，我们把记录用户数的表的值增

加 1;

代码片段

Create table numuser(

Num int not null default 0

);

Delimiter //

Create trigger testnum after insert on welefenuser for each row

Begin

Update welefennumuser set Num=Num+1;

End

//

视图

当我们想得到数据表中某些字段的信息，并想把他们保存时我们就可以用视图。

语法：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_Name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

例如我们想对上面的用户表使用视图，可以这样：

Create viewwelefenuserview as select fromwelefenuser;

查看视图的信息可以使用:

Select fromwelfenuserview;

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]\x0d\ VIEW view_name [(column_list)]\x0d\ AS select_statement\x0d\ [WITH [CASCADED | LOCAL] CHECK OPTION]\x0d\\x0d\该语句能创建新的视图，如果给定了OR REPLACE子句，该语句还能替换已有的视图。select_statement是一种SELECT语句，它给出了视图的定义。该语句可从基表或其他视图进行选择。\x0d\该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。\x0d\视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_nameview_name。\x0d\mysql> CREATE VIEW testv AS SELECT FROM t;\x0d\\x0d\表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。\x0d\视图必须具有唯一的列名，不得有重复，就像基表那样。默认情况下，由SELECT语句检索的列名将用作视图列名。要想为视图列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的ID。column_list中的名称数目必须等于SELECT语句检索的列数。\x0d\SELECT语句检索的列可以是对表列的简单引用。也可以是使用函数、常量值、 *** 作符等的表达式。\x0d\对于SELECT语句中不合格的表或视图，将根据默认的数据库进行解释。通过用恰当的数据库名称限定表或视图名，视图能够引用表或其他数据库中的视图。\x0d\能够使用多种SELECT语句创建视图。视图能够引用基表或其他视图。它能使用联合、UNION和子查询。SELECT甚至不需引用任何表。在下面的示例中，定义了从另一表选择两列的视图，并给出了根据这些列计算的表达式：\x0d\mysql> CREATE TABLE t (qty INT, price INT);\x0d\mysql> INSERT INTO t VALUES(3, 50);\x0d\mysql> CREATE VIEW v AS SELECT qty, price, qtyprice AS value FROM t;\x0d\mysql> SELECT FROM v;\x0d\+------+-------+-------+\x0d\| qty | price | value |\x0d\+------+-------+-------+\x0d\| 3 | 50 | 150 |\x0d\+------+-------+-------+\x0d\\x0d\视图定义服从下述限制：\x0d\· SELECT语句不能包含FROM子句中的子查询。\x0d\· SELECT语句不能引用系统或用户变量。\x0d\· SELECT语句不能引用预处理语句参数。\x0d\· 在存储子程序内，定义不能引用子程序参数或局部变量。\x0d\· 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。\x0d\· 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。\x0d\· 在视图定义中命名的表必须已存在。\x0d\· 不能将触发程序与视图关联在一起。\x0d\在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。\x0d\对于定义中的其他选项或子句，它们将被增加到引用视图的语句的选项或子句中，但效果未定义。例如，如果在视图定义中包含LIMIT子句，而且从特定视图进行了选择，而该视图使用了具有自己LIMIT子句的语句，那么对使用哪个LIMIT未作定义。相同的原理也适用于其他选项，如跟在SELECT关键字后的ALL、DISTINCT或SQL_SMALL_RESULT，并适用于其他子句，如INTO、FOR UPDATE、LOCK IN SHARE MODE、以及PROCEDURE。\x0d\如果创建了视图，并通过更改系统变量更改了查询处理环境，会影响从视图获得的结果：\x0d\mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));\x0d\Query OK, 0 rows affected (000 sec)\x0d\ \x0d\mysql> SET NAMES 'latin1';\x0d\Query OK, 0 rows affected (000 sec)\x0d\ \x0d\mysql> SELECT FROM v;\x0d\+-------------------+---------------------+\x0d\| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |\x0d\+-------------------+---------------------+\x0d\| latin1 | latin1_swedish_ci |\x0d\+-------------------+---------------------+\x0d\1 row in set (000 sec)\x0d\ \x0d\mysql> SET NAMES 'utf8';\x0d\Query OK, 0 rows affected (000 sec)\x0d\ \x0d\mysql> SELECT FROM v;\x0d\+-------------------+---------------------+\x0d\| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |\x0d\+-------------------+---------------------+\x0d\| utf8 | utf8_general_ci |\x0d\+-------------------+---------------------+\x0d\1 row in set (000 sec)\x0d\\x0d\可选的ALGORITHM子句是对标准SQL的MySQL扩展。ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。\x0d\对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。\x0d\对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。\x0d\对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。\x0d\明确选择TEMPTABLE的1个原因在于，创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。\x0d\视图算法可以是UNDEFINED，有三种方式：\x0d\· 在CREATE VIEW语句中没有ALGORITHM子句。\x0d\· CREATE VIEW语句有1个显式ALGORITHM = UNDEFINED子句。\x0d\· 为仅能用临时表处理的视图指定ALGORITHM = MERGE。在这种情况下，MySQL将生成告警，并将算法设置为UNDEFINED。\x0d\正如前面所介绍的那样，通过将视图定义中的对应部分合并到引用视图的语句中，对MERGE进行处理。在下面的示例中，简要介绍了MERGE的工作方式。在该示例中，假定有1个具有下述定义的视图v_merge：\x0d\CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS\x0d\SELECT c1, c2 FROM t WHERE c3 > 100;\x0d\\x0d\示例1：假定发出了下述语句：\x0d\SELECT FROM v_merge;\x0d\\x0d\MySQL以下述方式处理语句：\x0d\· v_merge成为t\x0d\· 成为vc1、vc2，与c1、c2对应\x0d\· 增加视图WHERE子句\x0d\所产生的将执行的语句为：\x0d\SELECT c1, c2 FROM t WHERE c3 > 100;\x0d\\x0d\示例2：假定发出了下述语句：\x0d\SELECT FROM v_merge WHERE vc1 100) AND (c1 回答于 2022-11-16

查询数据库中所有表名有两种方法：

1、select table_name from information_schematables where table_schema='当前数据库';

2、show tables；

其中，information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面。

扩展资料：

Mysql的INFORMATION_SCHEMA数据库包含了一些表和视图，提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

下面对一些重要的数据字典表做一些说明：

SCHEMATA表：提供了关于数据库的信息。

TABLES表：给出了关于数据库中的表的信息。

COLUMNS表：给出了表中的列信息。

STATISTICS表：给出了关于表索引的信息。

USER_PRIVILEGES表：给出了关于全程权限的信息。该信息源自mysqluser授权表。

SCHEMA_PRIVILEGES表：给出了关于方案（数据库）权限的信息。该信息来自mysqldb授权表。

TABLE_PRIVILEGES表：给出了关于表权限的信息。该信息源自mysqltables_priv授权表。

COLUMN_PRIVILEGES表：给出了关于列权限的信息。该信息源自mysqlcolumns_priv授权表。

CHARACTER_SETS表：提供了关于可用字符集的信息。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。

TABLE_CONSTRAINTS表：描述了存在约束的表。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。

VIEWS表：给出了关于数据库中的视图的信息。

TRIGGERS表：提供了关于触发程序的信息。

以上就是关于mysql中的存储过程、触发器、视图的用法全部的内容，包括:mysql中的存储过程、触发器、视图的用法、如何在MySQL中创建视图、查询mysql数据库中所有表名等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！