mysql数据库事务，视图，索引，存储过程，函数，触发器，约束详细介绍（超细致哦）数据库面试宝
不论是做那种语言的编程，我们都离不开和数据库打交道，在面试过程中难免会问到一些和数据库相关的知识点，下面我来对常用并且比较重点的知识点进行梳理，大家在面试之前也可以看看，祝找到更好的工作，下面以mysql为例进行说明。
下面是供大家参考 的文章的目录，内容有些丰富哦，希望大家耐心的看完，对加强mysql的基础有重要的帮助。
目录
存储在数据库中的一段过程代码，多条sql语句的记录和，用于完成某一个功能,类似于一个方法；
1.约束
mysql中的约束有五种： 主键，外键，唯一，非空，默认，检查（mysql 没有实现）。
1.1 主键
是一行数据的唯一标识
不重复
不能为空
自动创建索
尽量使用业务无关的数据作为主键
1.1.1建表时添加主键
create table tb(
id int primary key.
);
create table tb(
id int,
...,
primary key(id)
);
双主键,字段组合作为主键,尽量不用
create table tb(
name varchar(20),
ip varchar(20),
primary key(name, ip)
);
1.1.2 修改表时添加主键
alter table tb4
add primary key(id);
1.1.3 查看主键约束
desc tb4;
show create table tb4\G
1.1.4删除主键约束
alter table tb4
drop primary key;
1.1.5 自增主键
向表中插入数据时,不需要提供自增主键的值,它会自动产生连续增加的值.
如果插入null,自动填入新的自增值.
如果插入较大值,以后会从插入的值继续向后递增.
1.1.6 添加自增主键
mysql中,只有主键才允许自增
create table tb5(
id int primary key auto_increment,..
);
alter table tb5
modify id int auto_increment;
1.1.7 获得刚刚插入的自增值
使用函数: last_insert_id() 函数.
没有执行过insert语句,函数返回0
多个客户端都产生了自增值,只查询当前会话的自增值
select last_insert_id();
set names gbk; -- 通知服务器客户端字符集
use test;
-- 同时向班学生表和练习方式表插入数据
insert into xuesheng(name)
values('张三');
insert into lianxi(xs_id,tel)
values(last_insert_id(),'234563462345');
select * from xuesheng;
select * from lianxi;1.2 外键
外键引用主键中存在的值,
外键允许重复,也允许null值,
外键自动出创建索引
外键会降低表的访问效率,在大型互联网应用中,数据库表一般都不添加外键约束,用程序来保证数据的正确性。
学生表
|
id(PK) |
姓名 |
性别 |
|
1 |
张三 |
男 |
|
2 |
李四 |
女 |
|
3 |
王五 |
男 |
学生练习方式表
|
学生id(FK) |
电话 |
|
|
1 |
1412341234 |
1234213 |
|
3 |
23423452345 |
3452345 |
1.2.1 建表时添加外键
create table tb5(
id int,
xs_id int, -- 学生id,引用学生表的主键id
...,
foreign key(xs_id) references xuesheng(id)
);
1.2.2 修改表时添加外键
alter table tb5
add foreign key(xs_id) references xuesheng(id);
1.2.3 外键测试：
-- 创建学生表
drop table xuesheng;
create table xuesheng(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
-- 创建班级表
drop table banji;
create table banji(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
-- 修改学生表,添加banji_id外键,引用班级的主键
alter table xuesheng
add (
banji_id int,
foreign key(banji_id) references banji(id)
);
insert into banji(name)
values('一班'),('二班'),('三班');
insert into xuesheng(name,banji_id)
values('张三',1) , ('李四',3)
, ('王五',3) , ('赵六',1);
select * from banji;
select * from xuesheng;1.2.4 删除外键约束
删除外键约束,不会自动删除它的索引
通过约束名(show create table看到的名字)删除外键:
alter table xuesheng
drop foreign key xuesheng_ibfk_1;
通过索引名再删除索引:
alter table xuesheng
drop index banji_id;
1.3 唯一约束
保证字段中的值不重复,但允许重复的null值
唯一约束会创建索引,如果取消唯一约束,通过删除索引来取消
1.3.1 创建表时添加唯一约束
create table tb5(
id int primary key auto_increment,
email varchar(100) unique,
...
);
create table tb5(
id int primary key auto_increment,
email varchar(100)
...,
unique key(email)
);
1.3.2 修改表时添加唯一约束
alter table tb5
modify email varchar(100) unique;
alter table tb5
add unique key(email);
1.3.3 添加字段组合唯一约束
create table tb5(
...,
name ...,
ip ...,
...,
unique key(name, ip)
);
alter table tb5
add unique key(name, ip);1.3.4 测试唯一约束
-- 班级表name唯一
-- 如果已经存在重复值,不能添加唯一约束
alter table banji
add unique key(name);
insert into banji(name)
values('a'),('b');
insert into banji(name)
values('a'),('b');1.3.5 删除唯一约束
使用唯一约束的索引名来删除
alter table tb5
drop index name;
1.4 非空约束
约束字段不能取null(空)值
1.4.1 添加非空约束
create table tb5(
...,
email varchar(100) not null,
...
);
alter table tb5
modify email varchar(100) not null;name非空
-- 如果字段中已经存在null值,
-- 不能添加非空约束
alter table xuesheng
modify name varchar(20) not null;
-- 失败,name 非空
insert into xuesheng(name,banji_id)
values(null, 2);
1.4.2 查看非空约束
desc xuesheng;
show create table xuesheng\G1.4.3 删除非空约束
alter table tb5
modify email varchar(100) null;
-- 不写也可以
alter table tb5
modify email varchar(100);1.5 检查约束
设置检查条件,判断存入字段中的值,是否符合要求
mysql支持检查约束的语法,但没有实现检查约束
对年龄范围进行检查: >=7 并且 <=60
对性别取值检查: ='男' 或者 ='女'
create table tb5(
...,
age int,
gender char(1),
...
check(age>=7 and age<L=60),
check(gender='男' or gender='女')
);1.6 默认值
设置了默认值的字段,如果插入数据时不提供数据,会自动插入默认值
create table tb5(
...,
deleted tinyint default 0,
...
)；值测试
-- 学生表添加status字段
-- 1-正常 2-休学 3-删除
alter table xuesheng
add status int default 1;
insert into xuesheng(name)
values('a'),('b');
select * from xuesheng;2 事务
- 事务由多个数据操作(增删改查)组合
- 事务是数据操作的最小单元
- 事务中所有操作全部成功,事务整体成功
- 当事务中一项操作失败,事务整体失败
例如,游戏中 A 账户向 B 账户转账
- A 更新,减掉金额
- B 更新,加金额失败(断电),转账事务要整体失败,回退到以前的状态
2.1 事务特性:ACID
A - 原子性 Atomic
C - 一致性 Consistency
转账前 a+b = 100
转帐后 a+b = 100
I - 隔离性 Isolation
一个事物进行中时，
另一事物不能操作数据
D - 持久性 Durancy
提交事务之后，
数据持久生效
2.2 mysql事务操作
2.2.1 开启事务
- begin
- start transactio
- Set-autocommit=0
- 设置关闭自动提交
2.2.2 提交事务
commit
2.2.3 回滚事务
rollback
2.2.3 事务的例子
-- 多对多关系 - kecheng表
|
会话1 |
会话2 |
|
begin; |
begin; |
|
insert into kecheng(name) values('a'),('b');
update kecheng set name='x' where id=1;
select * from kecheng; |
|
|
|
select * from kecheng; |
|
commit; |
|
|
|
select * from kecheng; |
|
|
commit; |
|
|
select * from kecheng; |
|
会话一 |
会话二 |
|
bigin; |
bigin; |
|
insert into kecheng(name) values('c'),('d');
update kecheng set name='y' where id=1;-- 锁定一行数据
select * from kecheng; |
|
|
|
-- 不能修改加锁数据,除非其它事务结束 update kecheng set name='k' where id=1; |
|
rollback; |
|
|
select * from kecheng; |
|
|
|
update kecheng set name='k' where id=1; |
|
|
commit; |
2.3 事务隔离
事务完全隔离,一个事务完成,另一个事务才能执行,效率低
事务不隔离,会有数据访问冲突,数据不安全
数据库当中,可以设置事务的隔离性,来兼顾安全和性能
2.3.1 数据访问冲突
- 脏读(一般不允许发生)： 一个会话未提交的数据,被另一个会话读取
- 不可重复读：同一行数据,再次执行相同查询,与第一次查询结果不一致
- 幻读
- 其它会话新插入并提交的数据,查询不到
- 其它会话删除并提交的数据,仍然能查询到
2.3.2 事务隔离级别
set tx_isolation='READ-UNCOMMITTED';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';
READ-UNCOMMITTED: 脏读,不可重复读,幻读
read-committed: 不可重复读,幻读
repeatable-read: 幻读
serializable: x
2.3.3 隔离级别测试
隔离级别
|
会话1 |
会话2 |
|
rollback; |
rollback; |
|
set tx_isolation='READ-UNCOMMITTED' |
|
|
begin; |
begin; |
|
|
select * from kecheng; |
|
insert into kecheng(name) values('e'),('f');
update kecheng set name='h' where id=1;
select * from kecheng; |
|
|
|
select * from kecheng; |
|
rollback; |
|
|
|
select * from kecheng; |
|
|
rollback; |
|
会话1 |
会话2 |
|
set tx_isolation='read-committed'; |
|
|
begin; |
begin; |
|
|
select * from kecheng; |
|
insert into kecheng(name) values('g'),('h');
update kecheng set name='w' where id=1;
select * from kecheng; |
|
|
|
select * from kecheng; |
|
commit; |
|
|
|
select * from kecheng;-- 不一致,不可重复读 |
|
|
commit; |
|
会话1 |
会话2 |
|
set tx_isolation='repeatable-read'; |
|
|
begin; |
begin; |
|
|
select * from kecheng; |
|
insert into kecheng(name) values('i'),('j');
update kecheng set name='v' where id=1;
select * from kecheng; |
|
|
|
select * from kecheng; |
|
commit; |
|
|
|
select * from kecheng;-- 事务日志查询 |
|
|
commit; |
|
|
select * from kecheng; |
|
会话1 |
会话2 |
|
rollback; begin; |
rollback; begin; |
|
|
select * from kecheng; |
|
insert into kecheng(name) values('k'),('l'); |
|
|
commit; |
|
|
|
select * from kecheng; |
|
|
selct count(*) from kecheng; |
|
|
update kecheng set name= concat('*',name); |
|
|
select * from kecheng; |
|
|
commit; |
|
会话1 |
会话2 |
|
rollback; begin; |
rollback; begin; |
|
|
select * from kecheng; |
|
delete from kecheng where name in('*k','*l','*i'); |
|
|
commit; |
|
|
|
select * from kecheng; |
|
|
update kecheng set name= concat(name,'*'); |
|
|
select * from kecheng; |
|
|
commit; |
|
|
select * from kecheng; |
3 视图
将查询语句保存到数据库,起个名字,就是视图,以后可以简单的从视图查询
为什么要是用视图
- 简化查询
- 安全 可以向用户之暴露视图,而不暴露具体的表
3.1 创建视图
create or replace view v1 as select ....;
set names gbk;
use hr;
create or replace view v1
as
select
e1.employee_id,e1.first_name,
d.department_name,
l.city,
e2.first_name dept_mgr,
e3.first_name mgr_name
from
employees e1
join departments d
on e1.department_id=d.department_id
join locations l
on d.location_id=l.location_id
join employees e2
on d.manager_id=e2.employee_id
join employees e3
on e1.manager_id=e3.employee_id;
select * from v1;3.2 查看视图
show tables;
desc v1;
show create table v1\G3.3 删除视图
drop view v1;4 索引
加速查询
索引为字段中数据,生成一份索引数据,通过索引数据,可以快速定位数据
mysql 的innodb和myisam数据表引擎,生成索引模式使用b-tree(平衡红黑树)数据结构
缺点: 在执行插入 更新 删除的操作时，索引会进行变动，影响了操作的效率。
4.1 创建索引
create index index1 on tb1(c1);
where c1='sdfsdf'
where c1 like 'a%'
where c1 like '%a' 不能使用索引
create index index1 on tb1(c1, c2);
where c1='sdf' and c2='sdf'
where c1='sdf'
where c2='sdfsdf'
创建索引
create index index_name
on employees(first_name, last_name);4.2 查看索引
show create table employees\G4.3 删除索引
alter table employees
drop index index_name;4.4 查询语句是否使用索引来查询
create index index_name
on employees(first_name, last_name);
explain select * from employees
where first_name='a' and last_name='b';5 数据库备份恢复
在操作系统命令行执行
- mysqldump 命令备份数据
- mysql 命令恢复数据
5.1 备份
一行命令:
mysqldump -uroot -p
--default-character-set=utf8 //表中存的是什么编码
stu>/home/soft01/stu.sql //库>文件5.2 恢复
- 新建hr2库
create database hr2 charset=utf8;
-- 数据恢复
mysql -uroot -p
--default-character-set=utf8
hr2<d:\hr.sql
-- 查看hr2库的数据
use hr2;
show tables;
select * from employees;数据库编程
6 变量
6.1 自定义会话变量
set @v1=43; -- 变量赋值必须set
select @v1;
set @v1=5;
select @v1;6.2 mysql系统环境变量
show variables;
show variables like 'tx\_%';
show variables like '%char%';
show variables like '%auto%';
set autocommit=off;
set @@autocommit=off;6.3 代码块中的局部变量
begin
declare i int;
declare i int default 0;
end;7 储过程存
存储在数据库中的一段过程代码，多条sql语句的记录和，用于完成某一个功能,类似于一个方法；
7.1 创建存储过程
7.1.1 基本语法
-- 自定义结束符
delimiter //
drop procedure if exists p1;
//
create procedure p1()
begin
select now();
end;
//7.1.2 参数
- in 输入参数
- out 输出参数
- inout 即可作为输入参数也可作为输出参数
- p1(in a int, out b int)
- call p1(5, @v1); 5作为入参传给a；@v1 变量作为输出参数,来获得存储过程的计算结果
7.2 调用存储过程
drop procedure if exists p2;
//
create procedure p2(in a int, out b int)
begin
set b = a*a;
end;
//
call p2(5, @v1); //
select @v1; //
call p2(3, @v1); //
select @v1; //
--调用
call p1();7.3 查看存储过程
show procedure status\G
所有存储过程
show procedure status where db='test'\G
查看指定库中的存储过程
show create procedure p1\G
7.4 删除存储过程
drop procedure if exists p1;7.5 分支判断
if 条件 then
...
end if;
if 条件 then
...
else
...
end if;
case
when 条件 then ...;
when 条件 then ...;
else
end case;
case 数据
when 值1 then ...;
when 值2 then ...;
else
end case;
分支测试
drop procedure if exists p3;
//
create procedure p3(in a int)
begin
if a=1 then
select 1;
end if;
case
when a=2 then select 2;
when a=3 then select 3;
else select 4;
end case;
end;
//
call p3(1); //
call p3(2); //
call p3(3); //
call p3(4); //
call p3(5); // 7.6 循环
while 条件 do
...
end while;
lp:loop -- 给循环起别名
...
if ... then
leave lp; -- 离开lp循环
end if;
end loop;
repeat
...
until 条件 end repeat;
循环测试
-- 创建一个测试表
create table t(
c int
);
-- 创建存储过程,循环向表中插入n行数据
create procedure p4(in n int)
begin
-- 定义一个控制循环次数的变量
-- declare 定义局部变量
declare i int default 0;
-- while
while i<5 do
insert into t values(i+1);
set i=i+1;
end while;
set i=0;
lp:loop
insert into t values(i+1);
set i=i+1;
if i>=5 then
leave lp;
end if;
end loop;
set i=0;
repeat
insert into t values(i+1);
set i=i+1;
until i>=5 end repeat;
end;
//
call p4(5);
//
8 函数
函数有返回值,存储过程是一个特殊的函数
8.1 创建函数
create or replace function fn1(a int)
returns varchar(255)
begin
declare r int;
set r = a*a;
return r;
end;8.2 调用函数
select fn1(3);
select employee_id,fn1(department_id)
from employees;8.3 查看函数
show function status\G
show function status where db='test'\G
show create function f\G8.4 删除函数
drop function if exists f;8.5 函数demo
-- a的b次方
drop function if exists fn_pow;
create function fn_pow(a bigint, b bigint)
returns bigint
begin
declare r bigint;-- 用来存结果
declare i int; -- 用来控制循环此时
set r = a; -- 从第一个a开始,向后乘b-1次
set i = 0;
while i<b-1 do
set r = r*a;
set i = i+1;
end while;
return r;
end;
//
select fn_pow(2,3);
//
select c, fn_pow(c,3) from t;
//产生随机字符串
-- 算长度* 随机产生[a, b)长度的字符串
* a+[0,b-a): floor(a+rand()*(b-a))
* 假设随机长度是 3,产生三个随机字符
* 从一组字符串s0随机选取 'abcdefg....'
* 随机定位j位置1+[0,char_length(s0)-1)
* 取一个字符substring(s0,j,1)
* 重复3次取三个字符连接
drop function if exists fn_randstr;
create function fn_randstr(a int, b int)
returns varchar(255)
begin
declare len int;-- 随机的长度
declare s0 varchar(600) default ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两*机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该铁价严龙飞';
declare j int; -- 从s0随机挑选的位置
-- 保存最终结果,从空串开始
declare s varchar(255) default '';
declare i int default 0;-- 控制次数
set len = floor(a+rand()*(b-a));
-- 循环len次挑选字符连接到s
while i<len do
-- 随机定位
set j =
floor(1+rand()*(char_length(s0)-1));
-- 拿出s0中j位置的一个字符连到s
set s=concat(s, substring(s0,j,1));
set i=i+1;
end while;
return s;
end;
//
select fn_randstr(3,6);
//
set names gbk;
//
-- 如果数据库字符集是latin1,设置gbk也不管用
-- 1.换库 2.改当前库的字符集属性
alter database 库名 charset utf8;
产生大量的数据
-- 创建一张内存表,大量暂时插入内存表中
-- 再将内存表数据,一次导入磁盘表
-- 内存表
create table tm(
c varchar(20)
)engine=memory charset=utf8;
//
-- 磁盘表
create table td(
id int primary key auto_increment,
c varchar(20)
)engine=innodb charset=utf8;
//
-- 批量产生数据的存储过程
drop procedure if exists gen_data;
//
create procedure gen_data(in n int)
begin
declare i int default 0;-- 控制次数
-- 每次产生的字符串
declare s varchar(255);
-- 向内存表插入n条数据
while i<n do
set s = fn_randstr(3,6);
insert into tm values(s);
set i=i+1;
end while;
-- 将内存表数据一次导入磁盘表
insert into td(c) select * from tm;
-- 清空内存表
delete from tm;
end;
//
call gen_data(50000);//
call gen_data(50000);//
call gen_data(50000);//
...9 触发器
在插入、修改和删除时，可以自动触发一段代码执行
- 操作之前触发
- 操作之后触发
- 六种触发器:
- 一张表中最多只能有6个触发器
- before insert
- before update
- before delete
- after insert
- after update
- after delete
- 两个隐含的对象
- new - 新数据
- old - 旧数据
- insert
- new 插入的新数据
- old 没有
- update
- new 修改之后的新数据
- old 修改之前的旧数据
- delete
- new 没有
- old 被删除的数据
- 访问新的数据行或旧的数据行:
- new.employee_id
- old.first_name
9.1 创建触发器
create trigger before_td_insert
before insert on td for each row
begin
...
end;
触发器测试
-- 新建tb1表
create table tb1(
id int primary key auto_increment,
c varchar(20),
created datetime,
updated datetime
)engine=innodb charset=utf8;
//
-- 新建触发器,插入数据时
-- 自动为created和updated填入当前时间
create trigger tb1_before_insert
before insert on tb1 for each row
begin
set new.created=now();
set new.updated=now();
end
//
insert into tb1(c) values('a');
//
select * from tb1;
//9.2 查看触发器
use information_schema
//
select * from triggers\G
//9.3 删除触发器
drop trigger 触发器名很感谢大家能够耐心的看到这里，是不是感觉自己又重新理清了一下对数据库知识的思路，哈哈，感谢大家的支持，你们的支持就是我的动力，如果大家感觉这整理的还不错的话，就转发出去吧，让我们一起在技术的海洋中遨游向前，达到胜利的彼岸！谢谢大家！
上一篇： 环境变量