MySQL索引、触发器、常用函数、存储过程和函数、数据备份与还原的介绍

-------------------索引----------------------

--索引的介绍

索引是在存储引擎上实现的,每种存储引擎的索引不一定完全相同,包括索引的类型及长度

MySql:MyISAM和InnoDB支持BTREE索引,MEMORY和HEAP支持HASH和BTREE索引

索引的作用：提高查询速度

--索引优点:

1通过创建唯一索引可以包装表中的每一行数据的唯一性

2可以大大提高查询效率

3在实现数据的参考的完整性方面,可以加速表与表之间的连接

4使用分组和排序子句进行查询时可以查询中分组和排序的时间

--索引缺点:

1创建和维护索引需要耗费时间,并且数据两越大,耗费时间越长

2索引需要占用磁盘空间,如果有大量的索引,可能索引文件比数据文件先达到最大文件尺寸

3当对表中的数据进行增删改时,索引需要动态维护,降低了数据的维护速度

--索引设计原则:

1并非索引越多越好,因为增删改时需要动态维护,且占磁盘空间

2避免多经常更新的列添加索引

3数据量小时不要创建索引

4条件表达式中经常用到的不同值较多的列上创建索引

5当唯一性是某种数据本身特征时创建唯一索引

6在频繁进行排序和分组的列上创建索引

--索引的分类

--查看索引

show index from t_user1;

show keys from t_user1;

--查询结果的属性解析(primary key default as an unique index)

--Table

表的名称

--Non_unique

如果索引不能包括重复词，则为0。如果可以，则为1。

--Key_name

索引的名称

--Seq_in_index

索引中的列序列号，从1开始

--Column_name

列名称

--Collation

列以什么方式存储在索引中。在MySQL中，有值‘A’(升序)或NULL(无分类)。

--Cardinality

索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。

--Sub_part

如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

--Packed

指示关键字如何被压缩。如果没有被压缩，则为NULL。

--Null

如果列含有NULL，则含有YES。如果没有，则该列含有NO。

--Index_type

用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)

--Comment

--1.普通索引

--创建表的时候创建索引，索引名称默认与列名一样

create table t_user1(

id int,

userName varchar(20),

password varchar(20),

index(userName)

);

--创建表后创建

create index index_userName on t_user4(userName);

--2.唯一性索引

--创建表时创建

create table t_user2(

id int,

userName varchar(20),

password varchar(20),

unique index(userName)--单列索引

);

--创建表后创建

create unique index index_userName on t_user2(userName);

--为索引取一个别名，别名不能为中文

create table t_user2(

id int,

userName varchar(20),

password varchar(20),

unique index index_userName(userName)--单列索引

);

--3.全文索引

--4.单列索引

--5.多列索引

--创建表时创建

create table t_user3(

id int,

userName varchar(20),

password varchar(20),

index index_userName_password(userName,password)--多列索引

);

--创建表后创建

create index index_userName_password on t_user2(userName,password);

--6.空间索引

创建空间索引的列必须指明not null, mysql中只有MyiSAM存储引擎支持空间索引，而mysql的默认引擎是InnoDB。

create table t_user3(

id int,

userName varchar(20),

password varchar(20),

SPATIAL index index_userName(userName)

);

--直接运行上面的语句会报错

--ERROR 1687 (42000):A SPATIAL index may only contain a geometrical type column

--添加索引

alter table t_user4 add index index_userName(userName);--添加单列索引

alter table t_user4 add unique index index_userName(userName);--添加唯一性索引

alter table t_user4 add index index_userName_password(userName,password);--添加多列索引

alter table t_user4 add spatial index index_userName(userName);--添加空间索引

--删除索引

alter table t_user2 drop index index_userName;

drop index index_userName on t_user2;

-------------------视图----------------------

--视图的引入

1，视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。

2，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

3，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

--视图的作用

1.使操作简便化

2.增加数据的安全性

3.提高表的逻辑独立性

--视图的一些规则

1.名字唯一

2.对于创建的视图数目是没有限制的

3.为了创建视图，必须具有足够的访问权限

4.视图是可以嵌套的，也就是可以利用从其他视图中检索数据的查询来构造一个视图

5.order　by　可以用再视图中，但如果从该视图检索数据SELECT中也含有ORDER BY,那么视图中的ORDER BY将被覆盖

6.视图不能索引，也不能有关联的触发器或者是默认值

7.视图可以和表一起使用。

--创建视图

--单表创建视图

CREATE [ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]

VIEW 视图名 [ ( 属性清单) ]

AS SELECT 语句

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

ALGORITHM 是可选参数，表示视图选择的算法;

“视图名”参数表示要创建的视图的名称;

“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与 SELECT 语句中查询的属性相同;

SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中;

WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内;

ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。其中，UNDEFINED 选项表示 MySQL 将

自动选择所要使用的算法;MERGE 选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分

取代语句的对应部分;TEMPTABLE 选项表示将视图的结果存入临时表，然后使用临时表执行语句;CASCADED

是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值;LOCAL 表示更新视图时，要

满足该视图本身的定义条件即可;

--example

create view v1 as select * from t_book;

create view v2 as select bookName,price from t_book;

create view v3(b,p) as select bookName,price from t_book;--可以为原先的列取别名

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

| b | p |

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

| Java编程思想 | 10.00 |

| Java从入门到精通 | 80.00 |

| 三剑客 | 70.00 |

| 生理学(第二版) | 24.00 |

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

4 rows in set (0.00 sec)

--多表创建视图

create view v4 as select tb.bookName,tby.bookTypeName from t_book tb,t_booktype tby where tb.bookTypeId = tby.id;

--查看视图：select * from v1;

1.DESCRIBE 语句查看视图基本信息

desc v4;

2.SHOW TABLE STATUS 语句查看视图基本信息

--可以将下面的两条语句的查询结果作一个比较，进而体会为什么view视图是虚表。

show table status like 'v2';

show table status like 't_book';

3.SHOW CREATE VIEW 语句查看视图详细信息

show create view v2;

4.在 views 表中查看视图详细信息

USE information_schema;--information_schema是系统自带的表(详细介绍：https://www.cnblogs.com/hzhida/archive/2012/08/08/2628826.html)

SELECT * FROM views;--这个的查询结果很长

这样 我们就能看到所有已经创建的视图的信息

--修改视图

1.CREATE OR REPLACE VIEW 语句修改视图

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

VIEW 视图名 [( 属性清单 )]

AS SELECT 语句

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

--

create or replace view v1(bookName,price) as select bookName,price from t_book;

2.ALTER 语句修改视图

ALTER [ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]

VIEW 视图名 [( 属性清单 )]

AS SELECT 语句

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

--

alter view v1 as select * from t_book;

--更新视图

更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚

拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。

超出了范围，就不能更新。

update v1 set bookName='java very good',price=200 where id = 5;

insert into v1 values(null,'java good',120,'feng',1);

--删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据;

DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]

drop view if exists v4;

--视图机制：

视图处理有两种机制，替换式和具化式;

替换式：操作视图时，视图名直接被视图定义给替换掉，

结果就变成select * from (select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id),

再提交给mysql执行;

具化式：mysql先得到了视图执行的结果，该结果形成一个中间结果暂时存在内存中。之后，外面的select语句就调用了这些中间结果(临时表)。

--看起来都是要得到结果，形式上有区别，好像没体会到本质上的区别。两种方式又有什么样的不同呢?

替换方式，将视图公式替换后，当成一个整体sql进行处理了。

具体化方式，先处理视图结果，后处理外面的查询需求。

替换方式可以总结为，先准备，后执行。

具体化方式总结理解为，分开处理。

--哪种方式好?不知道。mysql会自己确定使用哪种方式进行处理的。自己在定义视图的时候也可以指定使用何种方式。

--例子：

语法：CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名 [(属性清单)]

AS SELECT 语句

[WITH [CASCADED|LOCAL] CHECK OPTION];

ALGORITHM有三个参数分别是：merge、TEMPTABLE、UNDEFINED

merge：处理方式替换式，可以进行更新真实表中的数据;

TEMPTABLE：具化式，由于数据存储在临时表中，所以不可以进行更新操作!

当你的参数定义是UNDEFINED(没有定义ALGORITHM参数)。mysql更倾向于选择替换方式。是因为它更加有效。

--例子：

create ALGORITHM=merge view v_stu as select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id

update v_stu set c_name = '' where c_name ='';

执行成功

create ALGORITHM=TEMPTABLEview v_stu as select c.name as c_name ,s.name as stu_name from student s,class c where c.id = s.class_id

执行失败，不可以更新!

--Mysql中查询系统时间的方法(3种)

1.select current_date;

2.select now();

3.select sysdate();

-------------------触发器----------------------

--触发器的引入

触发器(TRIGGER)是由事件来触发某个操作。

这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。

当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

--创建与使用触发器

--1 创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE |AFTER 触发事件

ON 表名 FOR EACH ROW 执行语句

--创建一个触发器，当向t_book执行插入操作后，这个触发器将对t_booktype进行更新操作，

--操作的内容是：新插入的那一行的书籍类型数据+1

--

create trigger trig_book after insert

on t_book for each row

update t_booktype set bookNum = bookNum + 1 where new.bookTypeId = t_booktype.id;

insert into t_book values(null,'java',100,'ka',1);

--2 创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE |AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN

执行语句列表

END

DELIMITER |

CREATE TRIGGER trig_book2 AFTER DELETE

ON t_book FOR EACH ROW

BEGIN

UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;

INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');

DELETE FROM t_test WHERE old.bookTypeId=t_test.id;

END |

DELIMITER ;

DELETE FROM t_book WHERE id=5;

--DELIMITER的解释

其实就是告诉MySQL解释器，该段命令是否已经结束了，MySQL数据库是否可以执行了。

默认情况下，delimiter是分号;。

在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL将会执行该命令。

--查看触发器

1.show triggers语句查看触发器信息

show triggers;

2.在trigger表中查看触发器信息

-- 查看约束

SELECT * FROM information_schema.`TABLE_CONSTRAINTS`;

-- 查看触发器

SELECT * FROM information_schema.`TRIGGERS`;

--删除触发器

drop trigger [触发器名];--没有中括号

-------------------MySQL常用函数----------------------

--注意：mysql 中没有nvl ()函数,使用ifnull代替

--日期和时间函数

1，CURDATE() 返回当前日期;

2，CURTIME() 返回当前时间;

3，MONTH(d) 返回日期 d 中的月份值，范围是 1~12。d的值是要从表中取的，并且d的数据类型必须是date类型。

select curdate(),curtime(),month(brithday) as 'month' from t_test;

alter table [tableName] change [column] [column] date;--修改字段类型

--字符串函数

1，CHAR_LENGTH(s) 计算字符串 s 的字符数;

select brithday,char_length(brithday) from t_test;

2，UPPER(s) 把所有字母变成大写字母;

select userName,upper(userName) from t_test;

3，LOWER(s) 把所有字母变成小写字母;

select userName,lower(userName) from t_test;

--数学函数

1，ABS(x) 求绝对值

select num,abs(num) from t_test where userName = 'xiaoming';

2，SQRT(x) 求平方根

select sort(4) from t_test;

3，MOD(x,y) 求余

select mod(9,3) from t_test;

--加密函数

1，PASSWORD(str)

--一般对用户的密码加密 不可逆

insert into t_test

values(

null,

'ccc',

'2017-11-14',

'xiaogang',

1,

password('123')

);

--ERROR 1406 (22001): Data too long for column 'password' at row 1

--password()函数执行后的结果数据太长了：

alter table t_test modify column password varchar(130);

2，MD5(str)

--普通加密 不可逆

insert into t_test

values(

null,

'ccc',

'2017-11-14',

'md5',

1,

md5('123')

);

3，ENCODE(str，pswd_str)

--加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它;

alter table t_test add erjinzhi blob;--增加字段

insert into t_test

values(

null,

'ccc',

'2017-11-14',

'encode',

1,

md5('123'),

encode('123','aa')

);

4，DECODE(crypt_str，pswd_str)

--解密函数

select decode(erjinzhi,'aa') from t_test where userName = 'encode';

-------------------存储过程和函数----------------------

--存储过程和函数的引入

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好

的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL

服务器中存储和执行的，可以减少客户端和服务器端的数据传输;

--创建存储过程和函数

--1.创建存储过程

CREATE PROCEDURE sp_name([proc_parameter[,...]])

[characteristic...] routine_body

sp_name 参数是存储过程的名称;

proc_parameter 表示存储过程的参数列表;

characteristic 参数指定存储过程的特性;

routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束。

proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。

[ IN | OUT | INOUT ] param_name type

其中，IN 表示输入参数;OUT 表示输出参数;INOUT 表示既可以是输入，也可以是输出;param_name 参数是

存储过程的参数名称;type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型;

Characteristic 参数有多个取值。其取值说明如下：

LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。

[ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每

次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的，相同的输入

可能得到不同的输出。默认情况下，结果是非确定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA| MODIFIES SQL DATA} ：指明子程序使用 SQL 语句的限制;

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句;NO SQL 表示子程序中不包含 SQL

语句;READS SQL DATA 表示子程序中包含读数据的语句;MODIFIES SQL DATA 表示子程序中包含写数据的

语句。默认情况下，系统会指定为 CONTAINS SQL;

SQL SECURITY { DEFINER | INVOKER };指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行;

INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。

COMMENT ‘string’ ：注释信息;

delimiter && --定义一个结束符&&

create procedure pro_book(in bT int,out count_num int)

reads sql data

begin

select count(*) from t_book where bookTypeId = bT;

end

&&

delimiter ;--重新定义一个结束符;

call pro_book(1,@total);

--2.创建存储函数(相当于自定义了一个函数)

CREATE FUNCTION sp_name ( [func_parameter[,...]] )

RETURNS type

[ characteristic... ] routine_body

sp_name 参数是存储函数的名称;func_parameter 表示存储函数的参数列表;RETURNS type 指定返回值的

类型;characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的;routine_body 参数

是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束;

func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：

param_name type 其中，param_name 参数是存储函数的参数名称;type 参数指定存储函数的参数类型，

该类型可以是 MySQL 数据库的任意数据类型;

delimiter &&

create function func_book(bookId int)

returns varchar(20)

begin

return (select bookName from t_book where id = bookId);

end

&&

delimiter ;

select func_book(1);

--3.变量的使用

1.定义变量

DECLARE var_name [,...] type [ DEFAULT value ]

--自定义结束符：delimiter 新的结束符;

delimiter &&

create procedure pro_user1()

begin

declare a,b varchar(20);--只是定义而已，还未赋值

insert into t_user values(null,a,b);

end

&&

delimiter ;--这一行要加上，不然就相当于定义了一个结束符为&&。

call pro_user;--调用存储过程

2.为变量赋值

SET var_name = expr [,var_name=expr] ...

SELECT col_name[,...] INTO var_name[,...]

FROM table_name WHERE condition

delimiter &&

create procedure pro_user2()

begin

declare a,b varchar(20);

set a = 'a开始定义了', b = 'b也开始定义了';

insert into t_user values(a,b) where id = 1;

end

&&

delimiter ;

call pro_user2;

create table t_user5(

id2 int not null auto_increment,

userName2 varchar(20),

password2 varchar(20),

constraint pk primary key(id2)

);

delimiter &&

create procedure pro_user6()

begin

declare a,b varchar(20);

select userName2,password2 into a,b from t_user5

where id2 = 1;

insert into t_user values(null,a,b);

end

&&

delimiter ;

call pro_user6;

--4.游标的使用

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。

游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

1.声明游标

DECLARE cursor_name CURSOR FOR select_statement ;

2.打开游标

OPEN cursor_name;

3.使用游标

FETCH cursor_name INTO var_name [,var_name ... ];

4.关闭游标

CLOSE cursor_name;

delimiter &&

create procedure pro_user7()

begin

declare a,b varchar(20);

declare cur_t_user2 cursor for select userName2 ,password2 from t_user5;

open cur_t_user2;

fetch cur_t_user2 into a,b;

insert into t_user values(null,a,b);

close cur_t_user2;

end

&&

delimiter ;

--5.流程控制的使用

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP

语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。

1.if语句

IF search_condition THEN statement_list

[ ELSEIF search_condition THEN statement_list ]...

[ ELSE statement_list ]

END IF

--@:变量的定义

delimiter &&

create procedure pro_user8(in bookId int)

begin

select count(*) into @num from t_user where id = bookId;

if @num > 0 then update t_user set userName = 'java1234' where id = bookId;

else

insert into t_user values(null,'java1234','mima');

end if;

end

&&

delimiter ;

call pro_user8(4);

2.case语句

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]...

[ELSE statement_list ]

END CASE

delimiter &&

create procedure pro_user9(in bookId int)

begin

select count(*) into @num from t_user where id = bookId;

case @num

when 1 then update t_user set userName = 'java1234' where id = bookId;

when 2 then insert into t_user values(null,'java123456','password');

else insert into t_user values(null,'fadfa','fadfa');

end case;

end

&&

delimiter ;

call pro_user9(6);

3.loop ，leave语句

LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是 LOOP 语句本身没有停止循环

的语句，必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下：

[begin_label：]LOOP

Statement_list

END LOOP [ end_label ]

LEAVE 语句主要用于跳出循环控制。语法形式如下：

LEAVE label

delimiter &&

create procedure pro_user10(in totalNum int)

begin

aaa:loop

set totalNum = totalNum -1;

if totalNum = 0 then leave aaa;

else insert into t_user values(totalNum,'121212','21212');

end if;

end loop aaa;

end

&&

delimiter ;

delimiter &&

create procedure pro_user11(in totalNum int)

begin

aaa:loop

if totalNum = 0 then leave aaa;

else delete from t_user where id = totalNum;

end if;

end loop aaa;

end

&&

delimiter ;

4.iterate语句

ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次循环。

基本语法：

ITERATE label ;

delimiter &&

create procedure pro_user12(in totalNum int)

begin

aaa:loop

set totalNum = totalNum -1;

if totalNum = 0 then leave aaa;

elseif totalNum = 3 then iterate aaa;

end if;

insert into t_user values(totalNum,'231231','231231');

end loop aaa;

end

&&

delimiter ;

5.repeat语句

REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。

REPEAT 语句的基本语法形式如下：

[ begin_label : ] REPEAT

Statement_list

UNTIL search_condition

END REPEAT [ end_label ]

--delete from t_user;--先置空表

delimiter &&

create procedure pro_user13(in totalNum int)

begin

repeat

set totalNum = totalNum -1;

insert into t_user values(totalNum,'231231','231231');

until totalNum = 1;

end repeat;

end

&&

delimiter ;

6.while语句

[ begin_label : ] WHILE search_condition DO

Statement_list

END WHILE [ end_label ]

delimiter &&

create procedure pro_user13(in totalNum int)

begin

while totalNum > 0 do

insert into t_user values(totalNum,'231231','231231');

set totalNum = totalNum -1;

end while;

end

&&

delimiter ;

--调用存储过程和函数

--1.调用存储过程

CALL sp_name( [parameter[,...]] )

--2.调用存储函数

fun_name( [parameter[,...]] )

--查看存储过程和函数

--SHOW STATUS 语句查看存储过程和函数的状态

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’] ;

show procedure status like 'pro_user';

show procedure status like 'pro_user%';

--SHOW CREATE 语句查看存储过程/函数的定义

SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;

show create procedure pro_user;

--从information_schema.Routines 表中查看存储过程和函数的信息

desc information_schema.Routines;

SELECT * FROM information_schema.Routines

WHERE ROUTINE_NAME=' sp_name ' ;

--修改存储过程和函数

ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]

characteristic :

{ CONTAINS SQL } NO SQL | READS SQL DATA| MODIFIES SQL DATA}

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT ‘string’

alter procedure pro_user comment '我来测试一个comment';

show procedure status like 'pro_user';

1.sp_name :参数表示存储过程或函数的名称;

2.characteristic: 参数指定函数的特性;

3.CONTAINS SQL :表示子程序包含SQL语句，但不包含读或写数据的语句;

4.NO SQL :表示子程序中不包含SQL语句;

5.READS SQL DATA :表示子程序中包含数据的语句;

6.MODIFIES SQL DATA :表示子程序中包含写数据的语句;

7.SQL SECURITY{ DEFINER | INVODER } : 指明谁有权限来执行;

8.DEFINER :表示只有定义者自己才能够执行;

9.INVODER :表示调用者可以执行;

10.COMMENT 'string' :是注释信息。

--删除存储过程和函数

DROP {PROCEDURE | FUNCTION } sp_name ;

问题：如何一次性把所有的存储过程都删除了?

drop procedure pro_user1;

--ERROR 1357 (HY000): Can't drop or alter a PROCEDURE from within another stored routine

--错误 1357 (HY000): 不能从另一个存储过程删除或修改其他存储过程

-------------------数据备份与还原----------------------

备份数据可以保证数据库中数据的安全，数据库管理员需要定期的进行数据库备份;

--数据备份

1.使用 mysqldump 命令备份

mysqldump -u username -p dbname table1 table2 ... > BackupName.sql

dbname 参数表示数据库的名称;

table1 和 table2 参数表示表的名称，没有该参数时将备份整个数据库;

BackupName.sql 参数表示备份文件的名称，文件名前面可以加上一个绝对路径。

通常以 sql 作为后缀。

以管理员身份启动cmd.exe,进入到mysql的bin目录:

mysqldump -u root -p db_book > c:\db_book.sql

mysqldump -u root -p db_book t_user t_user5 > c:\db_book.sql

2.使用 sqlyog 图形工具备份

没有右键解决不了的

--数据还原

1.使用 mysql 命令还原

mysql -u root -p [dbname] < backup.sql--中括号不要

mysql -u root -p db_book < c:\db_book_t.sql

dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。

指定数据库名时，表示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。

而备份文件中有创建数据库的语句。

2.使用 sqlyog 图形工具还原

没有右键解决不了的