1.视图
视图:是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据
视图有如下特点;
1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表(实表)产生的表(虚表)。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新(添加、删除和修改)直接影响基本表。
5. 当视图来自多个基本表时,不允许添加和删除数据。1.创建视图
1create
view
视图名称
as
sql 查询语句
2.使用视图
1select
*
from
视图名称;
3.更新视图
1alter
view
视图名称
AS
SQL语句
4. 删除视图
1drop
view
ren_view;
2.触发器-trigger
触发器:监视某种情况,并触发某种操作。
触发器创建语法四要素:1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
1.创建触发器语法
1234567create
trigger
triggerName
after
/before
insert
/
update
/
delete
on
表名
for
each row #这句话是固定的
begin
#需要执行的sql语句
end
注意1:
after
/before: 只能选一个 ,
after
表示 后置触发, before 表示前置触发
注意2:
insert
/
update
/
delete
:只能选一个
创建两张表
#商品表
create table goods(
id int primary key auto_increment,
name varchar(20),
num int
);
#订单表
create table order_table(
oid int primary key auto_increment,
gid int,
much int
);
添加3条商品数据
1insert
into
goods(
name
,num)
values
(
'商品1'
,10),(
'商品2'
,10),(
'商品3'
,10);
如果我们在没使用触发器之前:假设我们现在卖了3个商品1,我们需要做两件事
1.往订单表插入一条记录
1insert
into
order_table(gid,much)
values
(1,3);
2.更新商品表商品1的剩余数量
1update
goods
set
num=num-3
where
id=1;
现在,我们来创建一个触发器:
12345create
trigger
tg1
after
insert
on
order_table
for
each row
begin
update
goods
set
num = num -3
where
id = 1;
end
这时候我们只要执行:
1insert
into
order_table(gid,much)
values
(1,3);
会发现商品1的数量变为7了,说明在我们插入一条订单的时候,
触发器自动帮我们做了更新操作。
但现在会有一个问题,因为我们触发器里面num和id都是写死的,所以不管我们买哪个商品,最终更新的都是商品1的数量。比如:我们往订单表再插入一条记录:
1insert
into
order_table(gid,much)
values
(2,3);
执行完后会发现商品1的数量变4了,而商品2的数量没变,这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。
我们如何在触发器引用行的值,也就是说我们要得到我们新插入的订单记录中的gid或much的值。
对于insert而言,新插入的行用new来表示,行中的每一列的值用new.列名来表示。
所以现在我们可以这样来改我们的触发器:
12345create
trigger
tg2
after
insert
on
order_table
for
each row
begin
update
goods
set
num = num-new.much
where
id = new.gid;
end
第二个触发器创建完毕,我们先把第一个触发器删掉
1drop
trigger
tg1;
再来测试一下,插入一条订单记录:
1insert
into
order_table(gid,much)
values
(2,3)
执行完发现商品2的数量变为7了,现在就对了。
现在还存在两种情况:
1.当用户撤销一个订单的时候,我们这边直接删除一个订单,我们是不是需要把对应的商品数量再加回去呢?
对于delete而言:原本有一行,后来被删除,想引用被删除的这一行,用old来表示旧表中的值,old.列名可以引用原(旧)表中的值。
那我们的触发器就该这样写:
12345create
trigger
tg3 afert
delete
on
order_table
for
each row
bigen
update
goods
set
num = num + old.much
where
id = old.gid;
-- (注意这边的变化)
end
2.当用户修改一个订单的数量时,我们触发器修改怎么写?
12345create
trigger
tg4
after
update
on
order_table
for
each row
begin
update
goods
set
num = num+old.much-new.much
where
id = old.gid;
end
3.存储过程
MySQL数据库在5.0版本后开始支持存储过程,那么什么是存储过程呢?怎么创建、查看和删除存储过程呢?存储过程有什么优点?
存储过程:类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合,该语句集包括SQL语句(对数据的增删改查)、条件语句和循环语句等。
1. 查看现有的存储过程
1show
procedure
status;
2 .删除存储过程
1drop
procedure
存储过程名称;
3. 调用 存储过程
1call 存储过程名称(参数入/出类型 参数名 数据类型);
4.创建存储过程
1.体会封装
12345#1.体会封装
create
procedure
p1 ()
begin
select
*
from
account;
end
2.SQL 体会参数
123456789create
procedure
p2(
in
i
int
,
out
n
varchar
(50))
begin
select
name
into
n
from
account
where
id = i;
end
-- 调用
set
@
name
=
null
;
CALL p2(1,@
name
);
select
@
name
;
注意1: mysql中有三种出入参数类型:分别为:1. in 入参类型 2.out 出参类型 3. inout 出入参类型
注意2: into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量.
3.SQL 体会控制
123456789#3.SQL 体会控制
create
procedure
p3(
in
x
int
,
in
c
char
(1))
begin
if c =
'd'
then
select
*
from
account
where
money >x;
else
select
*
from
account
where
money <x;
end
if;
end
4.体会循环:计算1-100累加的和,并且返回计算结果.
1234567891011121314151617181920#4.体会循环:计算1-100累加的和,并且返回计算结果.
create
procedure
p4(inout n
int
)
begin
DECLARE
sum
int
default
0;
-- 设置总和变量,并且指定初始值0
declare
i
int
;
-- 声明变量
set
i = 0;
-- 通过set为变量设置值
while i<=n DO
-- 开始循环
set
sum
=
sum
+i;
set
i = i+1;
end
while;
-- 结束循环
select
sum
;
-- 提供结果
set
n =
sum
;
--将计算结果提供给 输出变量 n;
end
;
-- 调用:
set
@n = 100;
call p4(@n);
select
@n;
存储过程优点:
1、存储过程增强了SQL语言灵活性。存储过程可以使用控制语句编写,可以完成复杂的判断和较复杂的运算,有很强的灵活性;
2、减少网络流量,降低了网络负载。存储过程在服务器端创建成功后,只需要调用该存储过程即可,而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
3、存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译。一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点:1、扩展功能不方便
2、不便于系统后期维护
4.函数
MySQL提供的内建函数:
MySQL内建函数更多函数: 官方猛击这里
1、自定义函数
123456789CREATE
FUNCTION
fun1(i1
int
,i2
int
)
RETURNS
INT
//设置返回类型
BEGIN
DECLARE
sum
int
default
0;
set
sum
= i1+i2;
RETURN
(
sum
); //返回结果
end
2.调用自定义函数
12345#直接调用自定义函数
select
fun1(1,5);
#在sql语句中使用自定义函数
select
fun1(参数1,参数2),
name
from
表名
3.删除自定义函数
1DROP
FUNCTION
fun_name;
4.函数与存储过程的区别:
5.事物处理
一、 什么是事务
一组sql语句批量执行,要么全部执行成功,要么全部执行失败
二、为什么出现这种技术
为什么要使用事务这个技术呢? 现在的很多软件都是多用户,多程序,多线程的,对同一个表可能同时有很多人在用,为保持数据的一致性,所以提出了事务的概念。这样很抽象,举个例子:
A 给B 要划钱,A 的账户-1000元, B 的账户就要+1000元,这两个update 语句必须作为一个整体来执行,不然A 扣钱了,B 没有加钱这种情况很难处理。
三、事物的特性
80年代中国人结婚四大件:手表、自行车、缝纫机、收音机(三转一响)。要把事务娶回家同样需要四大件,所以事务很刻薄(ACID),四大件清单:原子性(Atom)、一致性(Consistent)、隔离性(Isolate)、持久性(Durable)。ACID就是数据库事务正确执行的四个特性的缩写。
- 原子性:要么不谈,要谈就要结婚!
对于其数据修改,要么全都执行,要么全都不执行。
- 一致性:恋爱时,什么方式爱我;结婚后还得什么方式爱我;
数据库原来有什么样的约束,事务执行之后还需要存在这样的约束,所有规则都必须应用于事务的修改,以保持所有数据的完整性。
- 隔离性:闹完洞房后,是俩人的私事。
一个事务不能知道另外一个事务的执行情况(中间状态)
- 持久性:一旦领了结婚证,无法后悔。
即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了,回头电话告诉我,服务器机房断电了,我的事务涉及到的数据修改可能没有进入数据库。
另外需要注意:
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
- 事务用来管理 insert,update,delete 语句
四、事务控制语句:
BEGIN 或 START TRANSACTION;显式地开启一个事务;
COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- SAVEPOINT : 保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时 可以指定在什么位置上进行回滚操作.
注意: SET AUTOCOMMIT=0 ;禁止自动提交 和 SET AUTOCOMMIT=1 开启自动提交.
五、例子: 鲁班转账给后羿
1.创建表
12345create
table
account(
id
int
(50)
not
null
auto_increment
primary
key
,
name
VARCHAR
(50)
not
null
,
money
DOUBLE
(5,2)
not
NULL
);
2.插入数据
1insert
into
account (
name
,money)
values
(
'鲁班'
,250),(
'后羿'
,5000);
3.执行转账
12345678910start
transaction
;
-- 开启事物
-- 执行sql语句操作
update
account
set
money = money - 500
where
id =
'1'
;
update
account
set
money = money+500
where
id =
'2'
;
commit
;
-- 手动提交事物
rollback
;
-- 回滚事物
-- 查看结果
select
*
from
account;
4.保存点使用
123456789START
TRANSACTION
;
insert
into
account (
name
,money)
values
(
'李元芳'
,1000);
SAVEPOINT s1;
-- 设置保存点
insert
into
account (
name
,money)
values
(
'张桂枝'
,1500);
ROLLBACK
to
s1;
-- 事物回滚到保存点<br>COMMIT; --提交事物
6.数据锁
需求: 有一个账户,两个人在同一时间要对此账户操作,A要对账户充值100块,B要从账户中取出100块.操作前都要先看一下账户的 余额然后再操作.
-- 窗口1 用户进行充值
-- 充值前 先查看余额
set @m=0;
SELECT money into @m from account where id = 1;
select @m;
-- 看到余额后 充值100 块
update account set money = @m + 100 where id = 1;
SELECT * from account;
--------------------------------------------------------------
-- 窗口2 用户进行取款
-- 取款前 先查看余额
set @m=0;
SELECT money into @m from account where id = 1;
select @m;
-- 看到余额后 取款100 块
update account set money = @m - 100 where id = 1;
SELECT * from account;
示例1. 锁的基本概念
当并发事务同时访问一个资源时,有可能导致数据不一致,因此需要一种机制来将数据访问顺序化,以保证数据库数据的一致性。
2. 锁的基本类型
多个事务同时读取一个对象的时候,是不会有冲突的。同时读和写,或者同时写才会产生冲突。因此为了提高数据库的并发性能,通常会定义两种锁:共享锁和排它锁。
2.1 共享锁(Shared Lock,也叫S锁)
共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。(如果试衣间的门还没被锁上,顾客都能够同时进去参观)
2.2 排他锁(Exclusive Lock,也叫X锁)
排他锁(X)表示对数据进行写操作。如果一个事务对 对象加了排他锁,其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了,其他顾客想要使用这个试衣间,就只有等待锁从里面给打开了).
3. 实际开发中常见的两种锁:
3.1悲观锁 顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.
注意:要使用悲观锁,我们必须关闭mysql数据库的自动提交属性.因为MySQL默认使用autocommit模式,也就是说,当你执行一个更新操作后,MySQL会立刻将结果进行提交。关闭自动提交命令为:set autocommit=0;
设置完autocommit后,我们就可以执行我们的正常业务了。具体如下:
1234567891011121314-- 0.开始事务
start
transaction
;
-- 1.查询账户余额
set
@m = 0;
-- 账户余额
select
money
into
@m
from
account
where
id = 1
for
update
;
select
@m;
-- 2.修改账户余额
update
account
set
money = @m -100
where
id = 1;
select
*
FROM
account
where
id = 1;
-- 3. 提交事务
commit
;
在另外的查询页面执行:
1234567891011121314-- 0.开始事务
start
transaction
;
-- 1.查询账户余额
set
@m = 0;
-- 账户余额
select
money
into
@m
from
account
where
id = 1
for
update
;
select
@m;
-- 2.修改账户余额
update
account
set
money = @m +100
where
id = 1;
select
*
FROM
account
where
id = 1;
-- 3. 提交事务
commit
;
会发现当前查询会进入到等待状态,不会显示出数据,当上面的sql执行完毕提交事物后,当前sql才会显示结果.
注意1:在使用悲观锁时,如果表中没有指定主键,则会进行锁表操作.
注意2: 悲观锁的确保了数据的安全性,在数据被操作的时候锁定数据不被访问,但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。
3.2 乐观锁, 顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。
使用乐观锁的两种方式:
1.使用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现 方式。何谓数据版本?即为数据增加一个版本标识,一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。当我们提交更新的时候,判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,则予以更新,否则认为是过期数 据。
代码示例:
12345678910111213-- 1.查询账户余额
set
@m = 0;
-- 账户余额
select
money
into
@m
from
account
where
id = 1 ;
select
@m;
-- 2.查询版本号
set
@version = 0;
-- 版本号
select
version
into
@version
from
account
where
id = 1 ;
select
@version;
-- 3.修改账户余额
update
account
set
money = @m -100,version=version+1
where
id = 1
and
version = @version;
select
*
FROM
account
where
id = 1;
2.乐观锁定的第二种实现方式和第一种差不多,同样是在需要乐观锁控制的table中增加一个字段,名称无所谓,字段类型使用时间戳 (datatime), 和上面的version类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致则OK,否则就是版本冲突。
悲观锁与乐观锁的优缺点:
两种锁各有其有点缺点,不能单纯的讲哪个更好.
乐观锁适用于写入比较少的情况下,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。
但如果经常产生冲突,上层应用会不断的进行重试操作,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适.
7.数据库备份
mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。然后通过这些语句,就能够创建表并插入数据。
1.使用mysqldump实现逻辑备份
12345678910111213#语法:
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
#示例:
#单库备份
mysqldump -uroot -p123456 db1 > c:/db1.sql
mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql
#多库备份
mysqldump -uroot -p123456
--databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql
#备份所有库
mysqldump -uroot -p123456
--all-databases > c:/all.sql
参数说明2.恢复逻辑备份
12345#在mysql命令下,用source命令导入备份文件:
mysql> USE 数据库名;
mysql> source 备份文件.sql;
注意:只能在cmd界面下执行source命令,不能在mysql工具里面执行source命令,会报错,因为cmd是直接调用mysql.exe来执行命令的。