Mysql常用数据库对象

最新推荐文章于 2023-01-04 15:33:07 发布

smilecb

最新推荐文章于 2023-01-04 15:33:07 发布

阅读量266

点赞数

分类专栏： Mysql 文章标签：触发器数据库

本文链接：https://blog.csdn.net/m0_45432976/article/details/115752110

版权

Mysql 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

文章目录

常用数据库对象简单版（视图、索引、存储过程、触发器）
扩展（有兴趣的同学可以好好看看）

常用数据库对象简单版（视图、索引、存储过程、触发器）

视图操作

视图是什么

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

为什么要使用视图

使用视图的大部分情况是为了保障数据安全性，提高查询效率。

如何创建视图

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);



CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);



CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    FOREIGN KEY(t_no) references teacher(t_no)
);


-- 学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

-- 教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

-- 添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

例子：创建单表视图

-- view 视图  v_stu1视图名，你自己取的名字   
-- 并没有在数据库中存储
create view v_stu1 as select * from student;


-- 查看视图
-- 查到的是虚拟表，不会在数据库中真实存储
-- 视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询
select * from v_stu1;



create view v_stu2 as select s_no, s_name from 
student;

select * from v_stu2;




create view v_stu3(学生编号, 学生姓名) as select s_no, s_name from student;

select * from v_stu3;

例子：创建多表关联视图

create view v_tc1 as
select t_name, c_name from teacher, course where teacher.t_no=course.t_no;

select * from v_tc1;

索引（了解）

MySQL的索引主要分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE) ，普通索引(INDEX)和全文索引(FULLTEXT) 。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，组合索引，即一个索引包含多个列。一个表可以有多个单列索引与这组合索引。

创建索引时，你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

过多的使用索引将会造成滥用。索引大大提高了查询速度，同时却会降低更新表的速度**，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

创建普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

创建表的时候直接指定

-- INDEX 索引   i_tc_9a 索引名  bbb字段名
create table tc_9(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  index i_tc_9a (bbb)
);

在这里插入图片描述

-- 以这种模式定义的索引，可以不指定索引名称。
create table tc_91(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  index  (bbb)
);

在这里插入图片描述

create table tc_92(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  index i_tc_9a (bbb, ccc)
);

在这里插入图片描述

在已存在的表上追加普通索引

create table tc_93(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime
);
create table tc_94(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime
);



CREATE INDEX i_tc9_a ON tc_93(bbb(15));

CREATE INDEX i_tc9_a ON tc_94(bbb(15), ccc)

查看表上的索引

-- 可以查看到创建在表上的所有索引名称。
show index from tc_93;

删除索引

-- 删除索引。

drop index i_tc9_a on tc_93;

show index from tc_93;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式

-- 创建唯一索引
CREATE UNIQUE INDEX i_tc_9b ON tc_94(bbb);

-- 创建表的时候直接指定
create table tc_95(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  unique i_tc_9a (bbb)
);

删除唯一索引的方法与删除普通索引相同。

主键索引

主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

创建主键索引

create table tc_10(
  aaa int,
  bbb varchar(20),
  ccc datetime
);
alter table tc_10 add primary key(aaa);

删除主键

可以使用 ALTER 命令删除主键：

ALTER TABLE tc_10 DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。而不能使用删除普通索引的方法来删除主键索引。

-- 这都是错误的。
drop index PRIMARY on tc_10;
drop primary key on tc_10;

全文索引

全文索引：FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持，FULLTEXT索引仅适用于 CHAR， VARCHAR和 TEXT列。

创建全文索引

drop table if exists tc_10;
create table tc_10(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  fulltext i_tc10_a(bbb)
);
alter table tc_10 add fulltext i_tc10_b(bbb);

删除全文索引，与删除普通索引一致。

drop index i_tc10_b on tc_10;

扩展（有兴趣的同学可以好好看看）

存储过程

MySQL 5.0 版本开始支持存储过程。

存储过程是一种在数据库中存储的复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

存储过程通常有以下优点：

存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

存储过程的缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校与撰写，受限于各种数据库系统。

创建语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
-- 声明过程名称、参数列表
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
-- 这是附加的一些存储过程描述信息，一般很少使用，这里不做详解
 
routine_body:
　　Valid SQL routine statement
-- SQL路由描述，一般也很少使用
 
[begin_label:] BEGIN
-- 开始存储过程标记
　　[statement_list]
-- 存储过程主体
　　　　……　　　
END [end_label]
-- 结束存储过程标记

一个最简单的无参数存储过程示例

我们来创建一个最简单的存储过程，没有入参出参、内部只有一条语句，向测试表中插入一条语句。

数据准备:

create table ptest_01(
  pid int primary key auto_increment,
	pname varchar(20)
);

创建存储过程

delimiter $
create procedure p_01()
begin
  insert into ptest_01(pname) value('bbb');
end$
delimiter ;

创建完成后，我们调用一下此存储过程，查看效果。

call p_01();

带有入参的存储过程示例

在存储过程中传参分 in ,out , inout 三种

in 可以输出从外部传入的变量不会改变传进变量本来的值

创建存储过程

delimiter $
create procedure p_02(in name varchar(20))
begin
  insert into ptest_01(pname) value(name);
  select name as name1;
  set name='hahaha';
  select name as name2;
end$
delimiter ;

创建完成后，我们调用一下此存储过程，查看效果。

set @name='www';
call p_02(@name);
select @name;
select * from ptest_01;

可以发现，in 参数在过程体中被改变后，改变的值不能带出过程体，过程体外还是原值。

带有出参的存储过程示例

out 不能输出从外部传进的值会改变传进变量本来的值

创建存储过程

delimiter $
create procedure p_03(out name varchar(20))
begin
  insert into ptest_01(pname) value(name);
  select name as name1;
  set name='hahaha';
  select name as name2;
end$
delimiter ;

创建完成后，我们调用一下此存储过程，查看效果。

set @name='rrr';
call p_03(@name);
select @name;
select * from ptest_01;

带有出入参的存储过程示例

inout 既可以从外部传进的值，也可以改变传进变量本来的值

创建存储过程

delimiter $
create procedure p_04(inout name varchar(20))
begin
  insert into ptest_01(pname) value(name);
  select name as name1;
  set name='hahaha';
  select name as name2;
end$
delimiter ;

创建完成后，我们调用一下此存储过程，查看效果。

set @name='rrr';
call p_04(@name);
select @name;
select * from ptest_01;

可以发现，inout 参数可以传值进入过程体，过程题内部被赋值后的值可以带出过程体。

查看存储过程

查看某个数据库中建立的存储过程

show procedure status where db='test';
select * from information_schema.routines
where routine_schema='test' and routine_type='PROCEDURE';

查看创建存储过程的语句

show create procedure p_06;

删除存储过程

drop procedure p_05; 
drop procedure if exists p_06;

存储过程体语法

变量的声明、引用、赋值

在MySQL编程中，存在两种变量：

用户变量（可以在过程体内外声明，生存周期为当前会话，两个不同会话之间，同名的变量，不会互相覆盖）。

局部变量（只能在过程体中声明，离开过程体之后，变量被销毁）。

用户变量不需要声明，可以直接通过赋值来创建变量

mysql> set @aaa = 'aaa';
Query OK, 0 rows affected (0.00 sec)

局部变量使用 declare 关键字声明，使用 set 关键字赋值

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

delimiter $
create procedure p_05()
begin
	declare abc int default 5;
	set abc = 8;
	select abc;
end$
delimiter ;
select abc;

除了使用 set 赋值，还可以通过 select 语句赋值；

select pname from ptest_01 limit 1 into @n1;
select pname into @n2 from ptest_01 limit 1;
select @n1, @n2;

delimiter $
create procedure p_06()
begin
	declare n3, n4 varchar(20) default '000';
	select pname from ptest_01 limit 1 into n3;
    select pname into n4 from ptest_01 limit 1;
    select n3, n4;
end$
delimiter ;
call p_06();

注意：使用 select 语句赋值时，返回结果集必须是单行单值。

程序控制结构 - 分支结构

if-then-else 语句

delimiter $
create procedure p_07(in n1 int)
begin
	if(n1 >= 0) then
	   insert into ptest_01(pname) value('n1 >= 0');
	else
	   insert into ptest_01(pname) value('n1 < 0');
	end if;
end$
delimiter ;
call p_07();

跟js中的语法类似，但是分支判断开始时，if的判断逻辑后面要加入 then ，结束时需要加入 end if;

case语句

delimiter $
create procedure p_08(in n1 int)
begin
	case(n1)
	  when(1) then
	    insert into ptest_01(pname) value('n1 = 1');
	    insert into ptest_01(pname) value('1111');
	    insert into ptest_01(pname) value('11111');
	  when(2) then
	    insert into ptest_01(pname) value('n1 = 2');
	    insert into ptest_01(pname) value('2222');
	    insert into ptest_01(pname) value('22222');
	  when(3) then
	    insert into ptest_01(pname) value('n1 = 3');
	    insert into ptest_01(pname) value('3333');
	    insert into ptest_01(pname) value('33333');
	  else
	    insert into ptest_01(pname) value('n1 = other');
	end case;
end$
delimiter ;
call p_08();

与 js 相比，没有 switch 关键字，由 case 代替 switch，when 后面加枚举值，在后面加 then。同时不需要break。结束时加入 end case。

程序控制结构 - 循环结构

while…end while 循环

delimiter $
create procedure p_11(in n1 int)
begin
	declare n2 int;
	set n2 = 0;
	while(n2 < n1 ) do
	  insert into ptest_01(pname) value(concat('n2 = ',n2));
	  set n2 = n2 + 1;
	end while;
	insert into ptest_01(pname) value('while is over.');
end$
delimiter ;
call p_11(3);

while(判断逻辑表达式) 后面要加 do，结束时写 end while; 类似 js 中的 while 循环。

repeat···· end repea 循环

delimiter $
create procedure p_12(in n1 int)
begin
	declare n3 int;
	set n3 = 0;
	repeat
	  insert into ptest_01(pname) value(concat('n3 = ',n3));
	  set n3 = n3 + 1;
	  until(n3 > n1)
	end repeat;
	insert into ptest_01(pname) value('repeat is over.');
end$
delimiter ;
call p_12(3);

类型 js 中的 do … while 循环，不判断条件，直接进循环体，在 until 时判断条件，为逻辑真时退出循环。结束时写 end repeat; 关键字。

loop ·····endloop 循环

delimiter $
create procedure p_13(in n1 int)
begin
	declare n4 int;
	set n4 = 0;
	loop_lable: loop
	  insert into ptest_01(pname) value(concat('n4 = ',n4));
	  set n4 = n4 + 1;
	  if(n4 > n1) then leave loop_lable;
	  end if;
	end loop;
	insert into ptest_01(pname) value('loop is over.');
end$
delimiter ;
call p_13(3);

loop 循环本质上是一个死循环，靠循环体中加入强制跳出循环语句，配合标号语句来标记 loop 头部，来结束循环。

触发器

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器的作用

触发器经常用于加强数据的完整性约束和业务规则等。触发器依赖于四个要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(内部执行逻辑)

完成作用是：

在写入数据前后，强制检验或者转换数据(保证护数据安全)

错误处理时：

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚

创建触发器

创建语法

delimiter $ //设置MySQL执行结束标志，默认为;
create trigger tg_name  -- 触发器名称
  after|before  -- 触发时间
  insert|update|delete  -- 监视事件 
  on table_name -- 监控表名
  for each row  -- 固定写法
  begin  -- 开始触发器内部逻辑
    update goods set num=num-2 where gid = 1;
end$  -- 结束触发器内部逻辑
delimiter ;  //重新将MySQL执行结束标志设置为;

注意：因为mysql的执行结束标识默认是;。如果运行以上的sql语句，mysql碰到;时会自动停止执行，然后end语句就执行不到了。所以我们需要先将mysql的结束标识符改为其他的字符，一般都选用 $或者$ $，这里选用$ 来作为执行的结束标识。

触发时间：当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

before：表中数据发生改变前的状态
after：表中数据发生改变后的状态
PS：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)

触发事件：触发器是针对数据发送改变才会被触发，对应的操作只有

INSERT
DELETE
UPDATE

注意事项：
触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器，每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update

简单触发器示例1

订单货品示例，数据准备

create table goods(
  gid int,
  name varchar(20),
  num smallint
);
create table ord(
  oid int,
  gid int,
  much smallint
);
insert into goods values(1,'cat',40);
insert into goods values(2,'dog',63);
insert into goods values(3,'pig',87);

业务目标

在ord订单表新增一条时，将goods表中 gid=1 的货品数量减少2。

触发器逻辑分析

监视谁：ord（订单表）
监视动作：insert（插入操作）
触发时间：after（在插入操作后触发）
触发事件：update（触发更新操作）

创建触发器

delimiter $
create trigger t1 
  after insert
  on ord
  for each row
  begin
    update goods set num=num-2 where gid = 1;
end$

我们来检查触发器有没有生效。

简单触发器示例2

学生成绩示例，数据准备

create table stu (
  stu_id int PRIMARY key auto_increment,
  stu_code int,
  stu_name varchar(20)
);
create  table sc (
  sc_id int primary key auto_increment,
  stu_code int,
  stu_score decimal(8,2)
);

业务目标：

在插入学生表一条记录的时候，插入成绩表一条记录，写入学生编号，成绩为80

触发器逻辑分析

监视谁：stu（学生表）
监视动作：insert（插入操作）
触发时间：after（在插入操作后触发）
触发事件：insert（触发更新操作）

创建触发器

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中。

需要注意的是，old 和 new 不是所有触发器都有
INSERT型触发器没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
DELETE型触发器没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据

create  table sc_h (
  sc_id int primary key auto_increment,
	stu_code int,
  before_update_code int,
	after_update_code int,
  before_update_score decimal(8,2),
	after_update_score decimal(8,2)
);

delimiter $
create trigger t2
  after insert
	on stu for each row
	begin
	  insert into sc(stu_code,stu_score) values(new.stu_code, 80.0);
end$

delimiter $
create trigger t2a
  after update
	on sc for each row
	begin
	  insert into sc_h(stu_code, before_update_code, after_update_code,
		before_update_score,  after_update_score)
		values(new.stu_code, old.stu_code, new.stu_code,
		old.stu_score, new.stu_score);
end$

检查一下触发器是否正常工作。

带传值的触发器

升级简单触发器1

在 2.2 简单触发器1 中，我们将固定商品的数量减少2，现在我们将升级这个逻辑，将订单中对应商品的数量建少订单中对应的数据。

delimiter $
create trigger t3
  after insert
  on ord
  for each row
  begin
    -- new 代表 ord 表中新增的数据
    update goods set num = num - new.much where gid = new.gid;
end$

进一步升级简单触发器1

如果在订单数量大于商品库存数量时，该怎么办？

这里需要检查库存量与订货数量，当订货量大于库存量时，放弃订单。

delimiter $
create trigger t4
  before insert
  on ord
  for each row
  begin
    -- 取出 goods 表中对应 id 的库存
    -- new 代表 orders 表中新增的数据
    select num from goods where gid = new.gid into @num;    
    -- 用即将插入的 orders 表中的库存和 goods 表中的库存进行比较
    -- 如果库存不够，中断操作
    if @num < new.num then
        -- 中断操作：暴力解决，主动出错
        insert into xxx values(xxx);
    end if;
end$

如果在触发器中出现错误，那么前面的已经执行的操作也会全部回滚

升级简单触发器1的另一种方法

如果在订单数量大于商品库存数量时，我们还可以强制修改订单订货数量为库存数量。

delimiter $
create trigger t5
  before insert
  on ord
  for each row
  begin
    declare restNum int;
    select num into restNum from goods where gid = new.gid;
    if new.much > restNum then
      set new.much = restNum;
    end if;
    update goods set num=num-new.much where gid=new.gid;
end$

注意：这里如果使用的是after就会报错，如果使用的是after，就会先执行insert操作，也就是插入订单操作，然后在进行判断下单数量和库存量，得出新的下单数量，可是已经执行了下单操作了，所以就会报错。这里必须使用before操作。

管理触发器

查看触发器

show triggers;
select * from information_schema.TRIGGERS;

还可以带上数据库名、表名的条件查询

select * from information_schema.TRIGGERS where trigger_schema='test' and event_object_table='ord';

查看创建触发器的语句

show create trigger 触发器名称;

删除触发器

drop trigger 触发器名称;

触发器的使用限制

触发器不是程序员手工调用的，是DBMS自动调用，由对应语句触发。

MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错

for each row是干什么的？

在oracle触发器中，触发器分为行触发器和语句触发器

比如：

create trigger tn
    after update
    on xxtable
    for each row
    #每一行受影响，触发事件都执行，叫做行触发器
    begin
      sqlN;
end$

执行：

update` `xxtable ``set` `xxx=xxx ``where` `id>100;

该修改操作假设100行，那么sqlN，会触发100次。

在oracle中，for each row如果不写，无论update语句一次影响了多少行，都只执行一次触发事件。
比如：1人下了订单，买了5件商品，insert 5次，可以用行级触发器，修改5次库存；用语句级触发器触发，insert一条发货提醒。
遗憾的是mysql目前不支持语句级触发器。

优点

触发器可以通过数据库中的关联表实现级联更改，即一张表数据的改变会影响其他表的数据
可以保证数据安全，并进行安全校验

缺点

过分依赖触发器，影响数据库的结构，增加数据库的维护成本

MySQL的主从复制

mysql数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份的数据库中。实现mysql数据库的热备份。
热备份与冷备份的区别：冷备份必须在关停服务下备份数据，热备份可以在服务上线时备份数据。

要实现热备mysql的版本都高于3.2。还有一个基本的原则就是作为从数据库的数据版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。

做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

随着架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。
读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

主从复制热备份的好处

1–在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）
2–在从服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）
3–当主服务器出现问题时，可以切换到从服务器。（提升安全性）

主从复制的具体操作方法

首先至少安装两个MySQL数据库。

然后配置主服务器的 my.ini 文件。
在这里插入图片描述

3306是mysql默认端口号，这里master实例中可以不用修改；server-id用来指定唯一id，不同的mysql实例不重复即可；binlog-do-db指定需要复制的数据库；log-bin用来开启二进制日志文件

再来配合从服务器的 my.ini 文件。
在这里插入图片描述

replicate-do-db：需要同步的数据库名称，与master上的配置保持一致。

在从服务器上进行设置
在这里插入图片描述

在从服务器上start slave
在这里插入图片描述

在从服务器上查看主从状态 show slave status （/G,将内容竖向显示）
在这里插入图片描述

验证

主库File和position与从库一致，则证明主从同步了
在这里插入图片描述

在这里插入图片描述

在master上创建一个专门用于复制的账户，并给它授予主从复制权限
在这里插入图片描述
]

授权完成后，注意执行 flush privileges。

主从配置完成后，所有对主服务器的操作，都会复制到从服务器上。

smilecb

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql常用数据库对象

文章目录常用数据库对象简单版（视图、索引、存储过程、触发器）视图操作视图是什么为什么要使用视图如何创建视图索引（了解）创建普通索引查看表上的索引删除索引唯一索引主键索引全文索引扩展（有兴趣的同学可以好好看看）存储过程创建语法一个最简单的无参数存储过程示例带有入参的存储过程示例带有出参的存储过程示例带有出入参的存储过程示例查看存储过程删除存储过程存储过程体语法变量的声明、引用、赋值程序控制结构 - 分支结构if-then-else 语句case语句程序控制结构 - 循环结构while...end while
复制链接

扫一扫