数据库高级操作

视图

视图就是一个虚拟的表

create view 视图名 select * from 数据库;

触发器

使用触发器可以定制用户对表进行增、删、改操作的前后行为。

1. 创建触发器

# 插入前
create trigger tri_before_insert_tb1 before insert on tb1 for each row
BEGIN
	...
END

# 插入后
create trigger tri_after_insert_tb1 after insert on tb1 for each row
BEGIN
	...
END

# 删除前
create trigger tri_before_delete_tb1 before delete on tb1 for each row
BEGIN
	...
END

# 删除后
create trigger tri_after_delete_tb1 after delete on tb1 for each row
BEGIN
	...
END

# 更新前
create trigger tri_before_update_tb1 before update on tb1 for each row
BEGIN
	...
END

# 更新后
create trigger tri_after_update_tb1 after update on tb1 for each row
BEGIN
	...
END

# 删除触发器
如: drop trigger tri_after_insert_cmd;

示例

create table cmd(
	id int primary key auto_increment,
	user varchar(32),
	priv varchar(10),
	cmd varchar(64),
	sub_time datetime,  # 提交时间
	success enum('yes', 'no')  # 0代表执行失败
);

create table errlog(
	id int primary key auto_increment,
	err_cmd char(64),
	err_time datetime
);

# 创建触发器
delimiter //
create trigger tri_after_insert_cmd after insert on cmd for each row
BEGIN
	if NEW.success = 'no' THEN
		insert into errlog(err_cmd, err_time) values(NEW.cmd, NEW.sub_time);
	END IF;
END//
delimiter ;

# 往cmd表中插入记录，触发触发器，根据IF的结果决定是否插入错误日志
insert into cmd(user, priv, cmd, sub_time, success) values('nihao', '1111', 'ls -alh', NOW(), 'yes');

# 查询错误日志
select * from errlog;

存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放在MySQL中，通过调用它的名字可以执行其内部的sql。

• 优点
  • 用于替代程序员写的sql语句，实现程序与sql解耦。
  • 基于网络传输，传别名的数据量小，而直接传sql数据量大。
• 缺点
  • 程序员想要扩展功能不便利

创建无参的存储过程

delimiter //
create procedure 存储过程名()
BEGIN
	select * from class;
END //
delimiter ;

# 查看创建的存储过程
show create procedure 存储过程名;

# 存储过程的调用
## mysql中
call 存储过程名();

## Pythonz中
cursor.callproc('存储过程名')

创建无参的存储过程

delimiter //
create procedure 存储过程名(in n1 int, in n2 int, out ret int)
BEGIN
	select * from teacher where tid > n1 and tid < n2;
	set ret=1;
END //
delimiter ;

# 存储过程的调用
## mysql中
set @x;
call 存储过程名(1, 3, @x);
select @x;

## Python中
cursor.callproc('存储过程名', (1, 3, 0))
cursor.execute('select @_存储过程名_2;')
cursor.fetchone()

事务

事务用于将某些操作的多条SQL作为原子性操作，一旦有某个出现错误，即可回滚到原来的状态，从而保证数据库中数据的完整性。

create table user(
	id int primary key auto_increment,
	name varchar(32),
	balance int
);

insert into user(name, balance) values('xxx', 1000), ('yyy', 1000);

# 开启事务
start transaction;
update user set balance=900 where name='xxx';
update user set balance=800 where name='xxx';
commit;

# 出现异常就回滚到初始状态
start transaction;
update user set balance=900 where name='xxx';
update user set balance=800 where name='xxx';
rollback;
commit;

函数

内置函数请百度。

自定义函数

delimiter //
create function f1(
	i1 int, 
	i2 int)
returns int
BEGIN
	declare num int;
	set num = i1 + i2;
	return(num);
END //
delimiter ;

delimiter //
create function f(
	i int
)
returns int
begin
	declare ret int default 0;
	if i = 10 then
		set ret = 100;
	elseif i=20 then
		set ret = 200;
	elseif i=30 then
		set ret = 300;
	else
		set ret = 400;
	end if;
	return ret;
end //
delimiter ;

删除函数

drop function func_name;

执行函数

# 获取返回值
select upper('xxx') into @res;
select @res;

# 查询中使用
select f1(i1, nid), name from tb2;

索引

索引是存储引擎中的一个键，通过减少帮助查询过程中的IO操作，从而提高查询效率。

• 建索引的字段尽量小
• 索引的最左匹配

聚集索引

InnoDB存储引擎表示索引组织表，表中数据按照主键顺序存放。聚集索引是按照每张表的主键构造一棵B+树，叶子结点存放的是整张表的行记录数据，我们将聚集索引的叶子结点成为数据页。聚集索引的这一特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

一张表只能有一个B+树。

辅助索引

表中除了聚集索引外其他索引都是辅助索引。

叶子结点除了包含键值外，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB存储引擎去哪找与索引相应的行数据。由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助引擎的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

聚集索引和辅助索引的区别

叶子结点是否包含行记录的全部数据。

索引是不是建的越多越好？

不是，索引加的多了会导致写入数据慢。

创建索引

1．ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2. CREATE INDEX
   CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

show index from table_name;
show keys from table_name;