MySQL-Step3

发表于   |   分类于   |     |   阅读次数

熟练掌握使用MySQL

前面的话

本人使用的MySQL 5.1版本，图形化界面用的SQLyog，本文主要介绍有如下内容：

  • MySQL常用函数
  • 触发器
  • 存储过程
  • 数据备份与恢复

MySQL常用函数

日期和时间函数

  1. curdate() 返回当前日期（current date）
  2. curtime() 返回当前时间
  3. month(d) 返回日期d中的月份值，范围是1~12
SELECT CURDATE(),CURTIME(),MONTH(birthday)
FROM t_user3;

字符串函数

  1. char_length(s) 计算字符串s的字符数
  2. upper(s) 把字符串s中所有字母变成大写字母
  3. lower(s) 把所有字母变成小写字母
SELECT userName,CHAR_LENGTH(userName),UPPER(userName),LOWER(userName)
FROM t_user3 GROUP BY id;

数学函数

  1. abs(x) 求绝对值
  2. sqrt(x) 求平方根
  3. mod(x,y) 求余
SELECT num,ABS(num),SQRT(4),MOD(9,2)
FROM t_user3;

加密函数

  1. password(str) 一般对用户的密码加密 不可逆
  2. md5(str) 普通加密 不可逆
  3. encode(str,pswd_str) 加密函数，结果是一个二进制数，必须使用blob类型的字段来保存它
  4. decode(crypt_str,pswd_str) 解密函数
INSERT INTO t_user3 (PASSWORD,password2) VALUES(PASSWORD("123456"),NULL);

INSERT INTO t_user3 (PASSWORD,password2) VALUES(MD5("123456"),ENCODE("123456","aa"));

SELECT DECODE(password2,"aa") FROM t_user3 WHERE id=6;

触发器

触发器(trigger)是由事件来触发某个操作。这些事件包括insert语句、update语句和delete语句。当数据库系统执行这些事件时，就会激活触发器执行响应的操作。

创建与使用触发器

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

create trigger 触发器名 before|after 触发事件
    on 表名 for each row 执行语句

/* 格式 */
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `db_book`.`trig_book` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `db_book`.`<Table Name>`
    FOR EACH ROW BEGIN

    END$$

DELIMITER ;


/* 手动创建 */
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,'sfasf',100,'sf',1)

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

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 ;

/* 数据库创建 */
DELIMITER $$

USE `db_book`$$

DROP TRIGGER IF EXISTS `trig_book2`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    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 ;

查看触发器

show triggers 语句查看所有触发器信息

SHOW TRIGGERS;
SHOW CREATE TRIGGER trig_book2;

在trigger表中查看触发器信息

USE information_schema;
SELECT * FROM TRIGGERS;

删除触发器

drop trigger 触发器名


DROP TRIGGER trig_book;
DROP TRIGGER trig_book2;

存储过程和函数

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

存储过程和函数可以避免开发人员重复编写相同的SQL语句，而且存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输。

创建存储过程和函数

创建存储过程

create procedure sp_name([proc_parameter[,...]])
    [characteristic...] routine_body

sp_name: 存储过程的名称
proc_parameter: 存储过程的参数列表
characteristic: 存储过程的特性
routine_body: 是SQL代码的内容，可以用begin...end来标志SQL代码的开始和结束。

proc_parameter中的每个参数由3部分组成（输入输出类型、参数名称和参数类型）：
[in|out|inout] param_name type
in表示输入参数，out表示输出参数，inout表示既是输入也是输出参数；
param_name参数是参数的名称；
type是参数的类型。

characteristic：    参数有多个取值，取值说明如下：

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

[NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。（相同输入，输出是否确定？）

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用的SQL的限制：
CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句；
NO SQL：表示子程序中不包含SQL语句；
READS SQL DATA：表示子程序中包含读数据的语句；
MODIFIES SQL：表示子程序中包含写数据的语句；
默认情况下，系统会指定CONSTRAINT SQL；

SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。
DEFINER：表示只有定义者自己才能够执行；
INVOKER：表示调用者可以执行。
默认情况下的权限是DEFINER。

COMMENT 'string'：注释信息。

/* 格式 */
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `db_book`.`pro_book2`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

    END$$

DELIMITER ;    


/* 手动创建 */
DELIMITER $$
CREATE PROCEDURE pro_book(IN bt INT, OUT coun_num INT)
    READS SQL DATA
    BEGIN
        SELECT COUNT(*) FROM t_book WHERE booktypeId=bt;
    END
$$
DELIMITER ;

/* 调用存储过程 */
CALL pro_book(1,@total)

/* 数据库创建 */
DELIMITER $$

USE `db_book`$$

DROP PROCEDURE IF EXISTS `pro_book`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_book`(IN bt INT, OUT coun_num INT)
    READS SQL DATA
    BEGIN
        SELECT COUNT(*) FROM t_book WHERE booktypeId=bt;
    END$$

DELIMITER ;

创建存储函数

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
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `db_book`.`func_book`()
    RETURNS TYPE
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN

    END$$

DELIMITER ;    

/* 手动创建 */
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(2);

/* 数据库修改存储函数 */
DELIMITER $$

USE `db_book`$$

DROP FUNCTION IF EXISTS `func_book`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `func_book`(bookId INT) RETURNS VARCHAR(20) CHARSET utf8
BEGIN
        RETURN (SELECT bookName FROM t_book WHERE id=bookId);
    END$$

DELIMITER ;

变量的使用

定义变量

declare var_name[,…] type [default value]

/* 创建存储过程并定义变量 */
DELIMITER $$
CREATE PROCEDURE pro_user()
    BEGIN
        DECLARE a,b VARCHAR(20);
        INSERT INTO t_user VALUES (NULL,a,b);
    END
$$
DELIMITER ;

/* 调用存储过程 */
CALL pro_user();
为变量赋值

1）
set var_name=expr[,var_name=expr]…

/* 创建存储过程，定义变量并为通过set为变量赋值 */
DELIMITER $$
    CREATE PROCEDURE pro_user2()
        BEGIN
            DECLARE a,b VARCHAR(20);
            SET a="Hello",b="World";
            INSERT INTO t_user VALUES (NULL,a,b);
        END
$$
DELIMITER ;

/* 调用存储过程 */
CALL pro_user2();

2）
select col_name[,…] into var_name[,…]
from table name
where condition

/* 创建存储过程，定义变量并为通过select为变量赋值 */
DELIMITER $$
CREATE PROCEDURE pro_user2_2()
    BEGIN
        DECLARE a,b VARCHAR(20);
        SELECT userName2,password2 INTO a,b 
        FROM t_user2_2 WHERE id2=1;
        INSERT INTO t_user VALUES (NULL,a,b);
    END
$$
DELIMITER ;

CALL pro_user2_2();

游标的使用

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

游标的使用包括声明游标、打开游标、使用游标和关闭游标。

游标必须声明在处理程序之前，并且声明在变量和条件之后。

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_user3()
    BEGIN
        DECLARE a,b VARCHAR(20);
        /* 声明游标 */
        DECLARE cur_user2 CURSOR FOR
            SELECT userName2,password2 FROM t_user2_2;
        /* 打开游标 */
        OPEN cur_user2;
        /* 使用游标，从游标中取数据 */
        FETCH cur_user2 INTO a,b;
        INSERT INTO t_user VALUES(NULL,a,b);
        /* 关闭游标 */
        CLOSE cur_user2;
    END
$$
DELIMITER ;

/* 调用存储过程 */
CALL pro_user3();

流程控制的使用

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

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

IF语句

if search_condition then statement_list
[else if search_condition then statement_list]
…
[else statement_list]
end if

DELIMITER $$
CREATE PROCEDURE pro_user4(IN bookId INT)
    BEGIN
        SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
        IF @num>0 THEN  UPDATE t_user SET userName='yyy',PASSWORD='yyy' WHERE id=bookId;
        ELSE
            INSERT INTO t_user VALUES(NULL,'nnn','nnn');
        END IF ;
    END
$$
DELIMITER ;
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_user5(IN bookId INT)
    BEGIN
        CASE bookId
        WHEN 1 THEN
            UPDATE t_user SET userName="1",PASSWORD="1" WHERE id=bookId;
        WHEN 2 THEN
            INSERT INTO t_user VALUES(NULL,"2","2");
        ELSE
            INSERT INTO t_user VALUES(NULL,"other","other");
        END CASE ;
    END
$$
DELIMITER ;
LOOP，LEAVE语句

loop语句可以使某些特定的语句重复执行，实现一个简单的循环。但是loop语句本身没有停止循环的语句，必须是遇到leave语句才能停止循环。

[begin_label:] loop
statement_list;
end loop [end_label]

leave 语句主要用于跳出循环控制，语法如下：
leave label

DELIMITER $$
CREATE PROCEDURE pro_user6(IN totalNum INT)
    BEGIN
        a:LOOP
            IF totalNum=0
                THEN LEAVE a;
            ELSE
                INSERT INTO t_user VALUES(totalNum,"afa","faf");
            END IF ;
            SET totalNum=totalNum-1;
        END LOOP a;
    END
$$
DELIMITER ;
ITERATE语句

iterate 语句也是用来跳出循环的语句，但是，iterate 语句是跳出本次循环，然后直接进入下一次循环，语法如下：
iterate label;

DELIMITER $$
CREATE PROCEDURE pro_user7(IN totalNum INT)
BEGIN
    a:LOOP
        SET totalNum=totalNum-1;
        IF totalNum=0 THEN LEAVE a;
        ELSEIF totalNum=3 THEN ITERATE a;
        END IF;
        INSERT INTO t_user VALUES(totalNum,'aaa','aaa');
    END LOOP a;
END
$$
DELIMITER ;
REPEAT语句

repeat 语句是有条件控制的循环语句。
当满足特定条件时，就会跳出循环语句。
repeat语句的基本语法如下：

[begin_label:]repeat
statement_list
until search_condition
end repeat[end_label]

DELIMITER $$
CREATE PROCEDURE pro_user8(IN totalNum INT)
BEGIN
    REPEAT
        INSERT INTO t_user VALUES(totalNum,"xxx","xxx");
        SET totalNum=totalNum-1;
        UNTIL totalNum=0
    END REPEAT;
END
$$
DELIMITER ;
WHILE语句

[begin_label:]while search_condition do
statement_list
end while[end_label]

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `db_book`.`pro_user9`(IN totalNum INT)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    WHILE totalNum>0 DO
        INSERT INTO t_user VALUES(totalNum, 'zzz', 'zz');
        SET totalNum=totalNum-1;
    END WHILE ;
    END$$

DELIMITER ;

调用存储过程和函数

调用存储过程

call sp_name([parameter[,…]])

调用存储函数

fun_name([parameter[,…]])

查看存储过程和函数

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

show {procedure | function} status [like ‘pattern’]

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

show create {procedure | function } sp_name;

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

select * from information_schema.Routines;

修改存储过程和函数

alter {procedure | function} sp_name [characteristic…]

ALTER PROCEDURE pro_user9 COMMENT "我来增加一个comment";

删除存储过程和函数

drop {procedure | function} sp_name;

DROP PROCEDURE pro_book;

数据备份与还原

数据备份

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

使用mysqldump命令备份

mysqldump -u username -p dbname table1 table2 …>BackupName.sql
dbname 参数表示数据库的名称；
table1和table2参数表示表的名称，没有该参数时将备份整个数据库；
BackupName.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以sql作为后缀。

使用sqlyog图形工具备份

略

数据还原

使用mysql命令数据还原

mysql -u username -p [dbname]<backup.sql
dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。

使用sqlyog图形工具还原

略

结束语

至此，我们的MySQL已经全部通关了，接下来可以通过项目经验的积累让我们对数据库有更深的认识，以及更深的追问。

坚持原创技术分享，您的支持将鼓励我继续创作