MySQL--进阶

数据库设计

需求分析

• 需求设计
• 概要设计
• 抽取实体:业务模型->实体模型(类)
• 数据库设计:业务模型/实体模型->数据模型()硬盘

详细设计

类详细,属性,方法

三大范式

• 设计原则:设计的表尽量遵守三大范式
• 第一范式:表中每个字段必须是不可分割的独立单元
• 第二范式:在第一范式的基础上,非主属性完全函数依赖于任何一个候选码
• 第三范式:在第二范式的基础上,非主属性既不传递依赖于码,也不函数依赖于码

MySQL存储过程

• 执行效率快,在服务端执行

• 移植性差

• 创建存储过程

DELIMITER $    声明结束符
CREATE PROCEDURE proname()
BEGIN
SELECT * FROM table_name;
...
END $

执行存储过程

CALL proname();

参数
IN 输入参数,可以携带数据到存储过程中
OUT 输出参数,可以从存储过程中返回结果
INOUT 输入输出参数,既有输入功能,也有输出功能
带有存储输入参数的存储过程

DELIMITER $
CREATE PROCEDURE proin(IN col  type)
BEGIN
SELECT * FROM table_name WHERE column=col;
END $

CALL proin(col);

带有输出参数的存储过程

DELIMITER $
CREATE PROCEDURE proout(OUT col type)
BEGIN
SET col = 'Hello MySQL!';
END $

删除存储过程

DROP PROCEDURE proout;

MySQL变量

• 全局变量(内置变量)

SHOW varibales; 查看所有全局变量
select @@变量名;查看某个全局变量
set 变量名=新值; 修改全局变量
character_set_client:mysql服务器的接收数据的编码
character_set_results:mysql服务器输出数据的编码

• 会话变量:只存在于当前客户端与数据库服务端的依次连接中,如果连接断开,那么会话变量全部丢失
  SET @var=‘value’;

• 局部变量:在存储过程中使用的变量

1)定义一个会话变量name

2)使用name会话变量接受存储过程的返回值

CALL proout(@name);
SELECT @name;

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

DELIMITER $
CREATE PROCEDURE proinout(INOUT var type)
BEGIN
SELECT var;
SET var = value;
END $
SET @var=value;
CALL proinout(@var)

带有条件判断的存储过程

输入整数,返回对应星期天

DELIMITER $
CREATE PROCEDURE proif(IN num INT, OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
SET str='星期一';
IF num=2 THEN
SET str='星期二';
IF num=3 THEN
SET str='星期三';
ELSE
SET str='输入错误';
END IF;
END $

CALL proif(1,@str);
SELECT @str;

带有循环功能的存储过程

输入数值,求从一到该数值的累加和

DELIMITER $
CREATE PROCEDURE prowhile(IN num INT, OUT result INT)
BEGIN
定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum=vsum+i;
SET i=i+1;
END WHILE;
SET result=vsum;
END $

CALL prowhile(122, @result)
SELECT @result;

MySQL触发器

当操作某张表时,同时触发一些动作

插入数据后,同时向日志表中插入

• 创建触发器(插入)

CREATE TRIGGER triname AFTER INSERT ON table_name FOR EACH ROW

INSERT INTO log_table_name(clomn,...) VALUES('INSERT');

• 创建触发器(修改)

CREATE TRIGGER triname AFTER UPDATE ON table_name FOR EACH ROW

INSERT INTO log_table_name(clomn,...) VALUES('update');

• 创建触发器(删除)

CREATE TRIGGER triname AFTER DELETEON table_name FOR EACH ROW

INSERT INTO log_table_name(clomn,...) VALUES('delete');

MySQL权限

• 修改密码

UPDATE USER SET PASSWORD=PASSWORD('new_passwd') WHERE USERN='USErname';

• 分配权限

GRANT SELECT ON DATABASE.table TO 'USEr'@'accounttype' IDENTIFIED BY 'password';

GRANT DELETE ON DATABASE.table TO 'USEr'@'accounttype' IDENTIFIED BY 'password';

MySQL备份和还原

• 不需要登录

MYSQLDUMP -u USErname -p DATABASE > path

MYSQL -u USErname -p DATABASE < path

• 复制表

CREATE TABLE table_name LIKE tablename;