视图
视图概念
视图是存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色。其实视图可以理解为一个表或多个表中导出来的表,作用和真实表一样,包含一系列带有行和列的数据 视图中,用户可以使用SELECT语句查询数据,也可以使INSERT,UPDATE,DELETE修改记录,视图可以使用户操作方便,并保障数据库系统安全,如果原表改名或者删除则视图也失效。
视图操作
创建视图
语法结构:
CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];
释义:
CREATE VIEW: 创建视图
OR REPLACE : 可选,如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name : 视图名称
SELECT_STATEMENT :SELECT语句
e.g.
create view c1 as select name,age from class_1;
视图表的增删改查操作
视图的增删改查操作与一般表的操作相同,使用insert update delete select即可,但是原数据表的约束条件仍然对视图产生作用。
删除视图
drop view [IF EXISTS] 视图名;
IF EXISTS 表示如果存在,这样即使没有指定视图也不会报错。
drop view c1;
修改视图
参考创建视图,将create关键字改为alter
alter view c1 as select name,age,score from class_1;
视图作用
作用
- 是对数据的一种重构,不影响原数据表的使用。
- 简化高频复杂操作的过程,就像一种对复杂操作的封装。
- 提高安全性,可以给不同用户提供不同的视图
- 让数据更加清晰
缺点 - 视图的性能相对较差,从数据库视图查询数据可能会很慢
- 表依赖关系处理麻烦,根据数据库的基础表创建一个视图。每当更改视图或者原表时,另一个也会修改。
函数和存储过程
存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合,调用存储过程和函数可以简化应用开发工作,提高数据处理的效率。
函数创建
delimiter 自定义符号 – 如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略
create function 函数名(形参列表) returns 返回类型 – 注意是retruns
begin
函数体 -- 函数语句集,set @a 定义变量
return val
end 自定义符号
delimiter ;
释义:
delimiter 自定义符号 是为了在函数内些语句方便,制定除了;之外的符号作为函数书写结束标志,一般用$$或者/
形参列表 : 形参名 类型 类型为mysql支持类型
返回类型: 函数返回的数据类型,mysql支持类型即可
函数体: 若干sql语句组成,如果只有一条语句也可以不写delimiter和begin,end
return: 返回指定类型返回值
e.g. 无参数的函数调用
delimiter $$
create function st() returns int
begin
return (select score from class_1 order by score desc limit 1);
end $$
delimiter ;
select st();
e.g. 含有参数的函数调用
delimiter $$
create function queryNameById(uid int(10))
returns varchar(20)
begin
return (select name from class_1 where id=uid);
end $$
delimiter ;
select queryNameById(1);
存储过程创建
创建存储过程语法与创建函数基本相同,但是没有返回值。
delimiter 自定义符号
create procedure 存储过程名(形参列表)
begin
存储过程 – 存储过程语句集,set @a 定义变量
end 自定义符号
delimiter ;
释义:
delimiter 自定义符号 是为了在函数内些语句方便,制定除了;之外的符号作为函数书写结束标志
形参列表 :[ IN | OUT | INOUT ] 形参名 类型
in 输入,out 输出,inout 可以输入也可以输出
存储过程: 若干sql语句组成,如果只有一条语句也可以不写delimiter和begin,end
e.g. 存储过程创建和调用
delimiter $$
create procedure st()
begin
select name,age from class_1;
select name,score from class_1 order by score desc;
end $$
delimiter ;
call st();
存储过程三个参数的区别
IN 类型参数可以接收变量也可以接收常量,传入的参数在存储过程内部使用即可,但是在存储过程内部的修改无法传递到外部。
OUT 类型参数只能接收一个变量,接收的变量不能够在存储过程内部使用(内部为
NULL),但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的,所以外部可以获取这个修改后的值。
INOUT类型参数同样只能接收一个变量,但是这个变量可以在存储过程内部使用。在存储过程内部的修改也会传递到外部。
设置变量方法: set @[变量名] = 值; 表示这是一个用户变量,使用时用@[变量名]。 在函数内部设置declare [变量名] [变量类型]为局部变量,局部变量可以使用set赋值或者着使用into关键字。
e.g. : 分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure p_out ( OUT num int )
begin
select num;
set num=100;
select num;
end $$
delimiter ;
set @num=10;
call p_out(@num)
存储过程和存储函数操作
-
调用存储过程
语法:
call 存储过程名字([存储过程的参数[,…]]) -
调用存储函数
语法:
select 存储函数名字([存储过程的参数[,…]])
- 使用show status语句查看存储过程和函数的信息
语法:
show {procedure|function} status [like’存储过程或存储函数的名称’]
显示内容:数据库、名字、类型、创建者、创建和修改日期
- 使用show create语句查看存储过程和函数的定义
语法:
show create {procedure|function} 存储过程或存储函数的名称
- 删除存储过程或存储函数
语法:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
函数和存储过程区别
- 函数有且只有一个返回值,而存储过程不能有返回值。
- 函数只能有输入参数,而存储过程可以有in,out,inout多个类型参数。
- 存储过程中的语句功能更丰富,实现更复杂的业务逻辑,可以理解为一个按照预定步骤调用的执行过程,而函数中不能展示查询结果集语句,只是完成查询的工作后返回一个结果,功能针对性比较强。
- 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
事务控制
事务概述
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,既需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,如果操作就必须同时操作成功,如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。
定义
一件事从开始发生到结束的过程
作用
确保数据操作过程中的一致性、完整性、准确性、有效性
事务四大特性
-
原子性(atomicity)
一个事务必须视为一个不可分割的最小工作单元,对于一个事务来说,不可能只执行其中的一部分操作,整个事务中的所有操作要么全部提交成功,要么全部失败回滚 -
一致性(consistency)
事务完成时,数据必须处于一致状态,数据的完整性约束没有被破坏。 -
隔离性(isolation)
数据库允许多个并发事务同时对其数据进行读写和修改的能力,而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。 -
持久性(durability)
一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不会丢失。
事务操作
-
开启事务
mysql>begin; # 方法1
mysql>start transaction; # 方法2 -
开始执行事务中的若干条SQL命令(增删改)
-
终止事务,若begin之后使用commit提交事务,使用rollback进行事务回滚。
mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!
事务隔离级别
事务四大特性中的隔离性是在使用事务时最为需要注意的特性,因为隔离级别不同带来的操作现象也有区别
隔离级别
读未提交:read uncommitted
事物A和事物B,事物A未提交的数据,事物B可以读取到
这里读取到的数据叫做“脏数据”
这种隔离级别最低,这种级别一般是在理论上存在,数据库隔离级别一般都高于该级别
读已提交:read committed
事物A和事物B,事物A提交的数据,事物B才能读取到
这种隔离级别高于读未提交
换句话说,对方事物提交之后的数据,我当前事物才能读取到
这种级别可以避免“脏数据”
这种隔离级别会导致“不可重复读取”
可重复读:repeatable read
事务A和事务B,事务A提交之后的数据,事务B读取不到
事务B是可重复读取数据
这种隔离级别高于读已提交
MySQL默认级别
虽然可以达到可重复读取,但是会导致“幻像读”
串行化:serializable
事务A和事务B,事务A在操作数据库时,事务B只能排队等待
这种隔离级别很少使用,吞吐量太低,用户体验差
这种级别可以避免“幻像读”,每一次读取的都是数据库中真实存在数据,事务A与事务B
串行,而不并发
数据库优化
数据库设计范式
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-
科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
各种范式呈递次规范,越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细,一个数据库中需要的表也就越多,此时多个表联接在一起的花费是巨大的,尤其是当需要连接的两张或者多张表数据非常庞大的时候,表连接操作几乎是一个噩梦,这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式,以避免数据操作异常,又不至于表关系过于复杂。
范式简介:
第一范式: 数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等组合的数据项。简单来说要求数据库中的表示二维表,每个数据元素不可再分。
例如: 在国内的话通常理解都是姓名是一个不可再拆分的单位,这时候就符合第一范式;但是在国外的话还要分为FIRST NAME和LAST NAME,这时候姓名这个字段就是还可以拆分为更小的单位的字段,就不符合第一范式了。
第二范式: 第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分,所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组,作为实体的唯一标识,每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。
第三范式: 在第二范式的基础上属性不传递依赖,即每个属性于其它非主属性。要求一个关系中不包含已在其它关系已包含的非主关键字信息。其实简单来说就是合理使用外键,使不同的表中不要有重复的字段就好了。
MySQL存储引擎
定义
mysql数据库管理系统中用来处理表的处理器
基本操作
1、查看所有存储引擎
mysql> show engines;
2、查看已有表的存储引擎
mysql> show create table 表名;
3、创建表指定
create table 表名(…)engine=MyISAM,charset=utf8,auto_increment=10000;
4、已有表指定
alter table 表名 engine=InnoDB;
常用存储引擎特点
InnoDB
1、支持行级锁,仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。
2、支持外键、事务、事务回滚
3、表字段和索引同存储在一个文件中
1、表名.frm :表结构
2、表名.ibd : 表记录及索引文件
MyISAM
1、支持表级锁,在锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许
2、表字段和索引分开存储
1、表名.frm :表结构
2、表名.MYI : 索引文件(my index)
3、表名.MYD : 表记录(my data)
如何选择存储引擎
1、执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2、执行写操作多的表用 InnoDB
CREATE TABLE tb_stu(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(30) DEFAULT NULL,
sex
varchar(2) DEFAULT NULL,
PRIMARY KEY (id
)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
字段数据类型选择
优先程度 数字 > 时间日期 > 字符串
同一级别 占用空间小的 > 占用空间多的
字符串在查询比较排序时数据处理慢
占用空间少,数据库占磁盘页少,IO处理就更快
少于50字节 char > varchar
对数据存储精确不要求 float > decimel
如果很少被查询可以用 TIMESTAMP(时间戳实际是整形存储)
键的设置
Innodb如果不设置主键也会自己设置隐含的主键,所以最好自己设置
尽量设置占用空间小的字段为主键
外键的设置用于保持数据完整性,但是会降低数据导入和操作效率,特别是高并发情况下,而且会增加维护成本
虽然高并发下不建议使用外键约束,但是在表关联时建议在关联键上建立索引,以提高查找速度