MySQL笔记

（MySQL不区分大小写）
procedure  函数（创建存储过程的关键字：CREATE PROCEDURE）
trigger  触发（创建触发器的关键字：CREATE TRIGGER）
grant 分发 （分配权限的关键字）
--------------------------------------------------------------------存储过程-------------------------------------------------------
SELECT * FROM `employe`;
#创建存储过程 emp_test() 括号中可以加参数的
#BIGINT之后就是存储过程（sql语句）
#要定义一个结束符号（这里不定义结束符号也没事情，应该是版本升级了，这个免去了）
delimiter $
CREATE PROCEDURE emp_test2()
BEGIN     
        SELECT *FROM employe;
END $ #结束符$

#调用存储过程 CALL 函数 （括号是必须的）
CALL emp_test2();
#有参数的，参数类型有三种（注意这里说的类型不是数据类型。指的是作用不同的三种参数类型）：
#in：输入参数，携带数据到存储过程中
#out：输出参数，可以携带参数到存储过程外面（存储过程是没有java函数那样有返回值，这个out参数就起到返回值的作用。）
#inout：输入输出参数，既可以携带参数到存储过程，也可以携带参数到存储过程外面。

#in类型，参数怎么写？
#函数名(参数类型 参数名 参数数据类型)
CREATE PROCEDURE emp_in(IN id int)  #参数类型(in) 参数名（eid） 参数数据类型（int）
BEGIN
       SELECT *FROM employe WHERE eid = id ;
END

CALL emp_in(3);

#OUT类型
#
CREATE PROCEDURE emp_out(OUT o VARCHAR(20))  #和in一样
BEGIN
      SET o = '输出参数';
END
#如何接收out输出的参数
#定义变量去接收参数
#mysql数据库的三种变量
#1.全局变量：mysql内置的变量，mysql启动时就存在mysql关闭时全局变量才会失效。SHOW VARIABLES可以查看所有的全局变量。
#   查看全局变量：select @@变量名
#   修改/定义全局变量：set @@变量名=值    eg：set @@character_set_results=gbk; 让mysql以gbk编码格式输出（结果显示），
#注意：全局变量一旦定义就会一致保存在mysql的内存中，所以非必要不要随便定义全局变量。
#几个和乱码有关的全局变量
#  1.character_set_client ：mysql接收客户端的数据编码格式，也就是你往数据库存数据时的数据编码格式。如果你插入的数据格式和这个定义的不一样那么你插入数据时会报错。
#  2.character_set_results ：mysql输出给客户端的数据编码格式，也就是说如果你已经存入的数据格式和这个定义不得不一样，那么你查询时的结果显示肯定是乱码的。
#  
#2.会话变量：会话变量只在某次登录的会话中有效，意思就是在当前登录会话是有效的，当连接断开时变量数据也会随之失效。
#   查看会话变量：select @变量名
#   修改/定义会话变量：set @变量名=值  （注意：如果你修改的变量是原本没有的，那么这个语句就是创建变量）
#3.局部变量：在存储过程中定义的变量，存储过程结束局部变量就消失，和java中的方法变量是一样的。
#   查看局部变量：select 变量名;
#   定义某个局部变量：declare 变量名 数据类型;
#
#综上这里定义一个会话变量去接收out类型参数返回的值比较合适。

SET @TEST = 'test';#注意这个是可以省略的，可以直接执行CALL emp_out(@TEST);如果当前会话中没有会话变量@TEST它会自动创建的。

CALL emp_out(@TEST);#使用@TEST变量接收out类型的存储过程输出的值

SELECT @TEST;#查询@TEST发现@TEST参数值为'输出参数' 也就是函数返回的值。

#inout类型的存储过程
CREATE PROCEDURE emp_inout(INOUT io VARCHAR(20))
BEGIN
    #变量传到io就是局部变量了，所以查看的时候不需要@
    SELECT io;
    #修改io变量，因为是inout类型，所以这里最后还会返回io变量这个时候返回的变量是500了
    SET io='500';
END
#使用会话变量@num来传递参数
SET @num='100';
CALL emp_inout(@num);#执行这个存储过程，会先查询传入的会话变量，也就是会显示@num='100'然后它会在查询后修改变量为'500'然后返回给会话变量。
SELECT @num;#这样在执行这个语句时会发现变量变为500


#带有判断条件的存储过程
#需求：输入一个num整数，num=1 输出'星期一'，num=2 输出星期二，num=3输出星期三。否则输出参数错误。

CREATE PROCEDURE emp_if(in num INT,OUT str VARCHAR(20))
BEGIN
    IF num = 1 THEN 
        SET str='星期一';
    ELSEIF num = 2 THEN
        SET str='星期二';
    ELSEIF num = 3 THEN SET str ='星期三';
    ELSE set str = '错误参数';
    END IF; #这个是语法规定，以if开始，必须以end if结束就相当与方法中的{}
END

CALL emp_if(1,@str);#运行后会话变量@str变为星期一
SELECT @str;#查询结果为星期一

#带有循环条件的存储过程
#需求：输入一个num，计算从1到num的总和
#结构上和java的方法有些类似，但是一些细节上的规则不一样。
SELECT @num

CREATE PROCEDURE emp_num(in num INT,out score INT)
BEGIN
    #只有局部变量在定义的时候使用的是declare全局变量和会话变量都是set
    DECLARE i INT DEFAULT 1;#定义局部变量i，默认值为1。
    DECLARE result INT DEFAULT 0;
    WHILE i<=num DO
            SET result = result + i;#加完后的结果就在result中这个结果是要输出的。
            SET i = i + 1; #这里没有i++这样的java语法，但是可以通过set达到通样的效果。
    END WHILE;#这个是语法规定，while的结束标志
    SET score = result;#把结果result输出
END

CALL emp_num(100,@numResult);#计算1加到100结果，把结果值放到会话变量@numResult中。
SELECT @numResult;#结果为1050

#携带数据库中的数据到存储过程外面。也就是在存储过程中查询数据库，把结果通过out输出。
#需求：传入员工id，查询对应的员工，输出员工姓名。

CREATE PROCEDURE emp_findName(in id INT,out empname VARCHAR(20))
BEGIN
  SELECT ename INTO empname FROM employe WHERE eid = id;#关键就在于如何把结果放到变量中，这就要使用into这个关键字了。这个关键字有拷贝的意思。
END

CALL emp_findName(3,@empname);
SELECT @empname;

#删除存储过程emp_test2
DROP PROCEDURE emp_test2;
------------------------触发器-----------------------
触发器相当于一个事件模型。触发就像时间监听事件，当满足既定条件时会有对应的反应发生。
如需求：当往员工表插入/修改/删除一条数据的时候，要求同时在日志表记录下来。
这时就要使用触发器完成。

#创建员工日志表
CREATE TABLE emp_log(
    id int PRIMARY KEY auto_increment,
    content VARCHAR(20)
)
#需求：当向员工表插入一条数据时，向员工日志插入一条记录。
#创建触发器            在    向employe中插入  每一条记录   之后
CREATE TRIGGER emp_add AFTER INSERT ON employe FOR EACH ROW
        INSERT INTO emp_log VALUE('员工表被插入一条记录');#执行这个sql语句（这个sql语句有个错误，在emp_log应该指定字段的，因为Value后面只有一个值，而这张表是有两个字段。所以要么在前面指定插入的字段，要么在value后面写两个值。）
SELECT * FROM `employe`;
SELECT * FROM emp_log;
INSERT INTO employe(eid,ename)VALUES(6,'测试');
INSERT INTO VALUES
#这里插入数据的时候一直报错[Err] 1136 - Column count doesn't match value count at row 1
#意思是前面insert关键字后面写的字段和Value后面插入的值不匹配，数量上不匹配。
#但是检查后发现employe的插入语句是没问题的，但是触发器中的sql语句写的不对，所以才一直报错。
#删除原来的触发器，修改后重新创建触发器
DROP TRIGGER emp_add;
CREATE TRIGGER emp_add AFTER INSERT ON employe FOR EACH ROW
        INSERT INTO emp_log(content) VALUE('员工表被插入一条记录');#执行这个sql语句
SELECT *FROM employe
#成功
#更新数据库时同样创建触发器向日志表中写入数据
#这样分几段写是为了方便理解关键词的意思
CREATE TRIGGER emp_update AFTER  #创建触发器（after：在事件发生后触发）
    UPDATE ON employe #触发事件为：update on employe（有对employe表进行update的操作）
    FOR EACH ROW  #触发事件对所有employe表中所有记录有效
        INSERT INTO emp_log(content) VALUES('员工表被修改了一条记录'); #事件发生后将执行该条sql语句

#删除操作的触发器
CREATE TRIGGER emp_delete AFTER DELETE ON employe FOR EACH row
    INSERT INTO emp_log(content) VALUES('员工表被删除了一条记录');
#验证触发器
UPDATE employe SET ename = 'update测试' where ename='测试';
DELETE FROM employe WHERE ename='update测试'
#测试成功

------------------------------------------mysql的权限问题----------------------------
root用户：超级管理员。什么权限都有。
     *增删改查（数据库，表，数据）
     *可以赋予其他普通用户不同程度的权限。
 跟用户相关的信息在哪儿管理？
     *这些配置信息都放在名为mysql的数据库中。
 查看一下USER表即知：
USE mysql;
#用户表
SELECT *FROM USER;
字段分析：
Host:可以连接这台数据库的主机信息（ip），你只能通过这个地址来访问数据库。如果要远程登录就要添加ip地址。如果这个字段有%这个值，就是允许所有ip地址访问该数据库。
User：用户
Password：加密后的密码（使用md5算法加密--单向加密，无法解密的）

修改用户密码：
UPDATE USER SET PASSWORD = '1234' WHERE user='root';这样写是错误的，这样写结果就是password这个字段的值变成了'1234'但是mysql数据库登录时匹配的是加密的36位的字符。所以要使用加密函数加密赋值
UPDATE USER SET PASSWORD = `PASSWORD`('1234') WHERE user='root';这样才是正确的。
#想要验证结果得重启mysql服务。

分配权限给不同的用户
需求：分配查询test数据库的employe表的权限给tb用户，密码为1234
语法：
GRANT SELECT ON 数据库.表 TO '用户名'@'用户类型' IDENTIFIED BY '用户密码';

分配用户的同时分配权限
GRANT SELECT ON test.employe TO 'tb'@'localhost' IDENTIFIED by '1234';

删除用户
DELETE FROM USER WHERE USER='tb';

给tb用户追加delete权限，再执行下面的语句即可
GRANT DELETE ON test.employe TO 'tb'@'localhost' IDENTIFIED by '1234';

追加update权限也是一样的，注意这个语句也是创建用户和密码的。

----------------------备份和恢复---------------------
备份和恢复的命令都是在命令窗口执行的。
注意：备份和恢复都是在登录之前进行操作。因为它是系统命令，这些命令我们可以写个bat文件，然后再写个定时每天去触发。

备份命令：将test数据库备份到D:/bak.sql目录
mysqldump -u root -p test>D:/bak.sql    (这里是以root用户登录的)

执行这个命令后会让你输入密码，输入密码后即执行备份操作。

清空test数据库中的数据，模拟数据损坏场景。

恢复命令：将备份文件bak.sql恢复到test数据库中
mysql -u root -p test<D:/bak.sql