从0学习MySQL系列(三)概念篇

概要


在篇文章中提过:概念:数据库管理系统(Database Management System)一些语法的汇总点。

增删改查是基本的操作语法。对于一些概念,主键,外键,表,事务,锁,存储过程,视图,触发器,分区等概念,请参考上面的文章。概念是晦涩难看,但是有时候又忍不住多看几眼。

内容:主键,外键,视图,存储过程,触发器,事务,锁。

主外键


使用表:Department,Employee

主键是唯一标识一张表的数据,不允许为NULL,不允许重复,保证了数据的完整性。

外键是另一张表的主键,外键可以为NULL,可以有重复,与其他表建立联系。

图中有两个表,分别是:Department,Employee表,其中,DeptID、EmpID分别是这两个表的主键,DeptID是Employee表的外键。

代码:

create database DB_Company;
use DB_Company;

-- 创建Department 部门表
create table Department(
    DeptID int primary key auto_increment not null,
    DeptName varchar(20) not null,
    Description varchar(100)
)

-- 创建员工表
create table Employee (
    EmpID int primary key auto_increment not null,
    EmpName varchar(10) not null,
    Gender varchar(4) not null,
    Age int(2) not null,
    foreign key(EmpID) references Department(DeptID)
)         

视图


创建视图,视图是一种虚拟存在的表,对于使用视图的用户来说是透明的。视图具有简单、安全、数据独立等优点。

创建视图employee_view的语法:

create or replace view employee_view as select * from Employee

MySQL视图的定义有一些限制。from后面不能包含子查询。如果输入是从别的数据库迁移过来的,可以先将子查询的内容定义为视图。

另外,包含聚合函数(SUM(),MIN(),MAX(),COUNT())、DROUP BY、HAVING、UNION等的视图不可更新。

包含常量视图,Select中包含子查询的,JION连接查询的视图都是不可更新的。

drop view employee_view; -- 删除视图

查看视图,从MySQL5.1版本以上,没有show views 这个查看视图的命令了,使用show table status

show table status like "employee_view" 

-- 如果要查询employee_view视图的定义
show create view employee_view 

存储过程


什么是存储过程呢?什么是函数呢?存储过程和函数,其实是事先经过编译的、并存储在数据库中的一段SQL语句的集合。

函数嘛,要有返回值,而存储过程不需要返回值。存储过程的参数可以使用IN,OUT,INOUT,但是函数只能用IN类型的。

存储过程中可以执行提交,或者是回滚。提交即确认之前的修改,回滚即放弃之前的修改。存储过程不允许执行LOAD DATA INFILTE 语句,此外,存储过程可以调用其他的存储过程。

create procedure employee_in_stock(in EmpID int, in EmpName varchar,
 in Gender varchar, in Age int)
Reads SQL Data
BEGIN
     ......
     ......
     .......
     
END

直接执行SQL和调用存储过程的效果是一样的,使用存储过程的优点在于处理逻辑封装在数据库中,调用的时候不需要知道真正去了解中间的处理逻辑。即使中间的逻辑发生了变化,修改即可,对程序没有预知内的影响。其余知识点不赘述。(包括变量的使用、流程的控制、时间调度器、存储过程的删除等)

事务


事务具有四种性质:

原子性:一组事务,要么成功;要么撤回。

稳定性 : 有非法数据(外键约束之类),事务撤回。

隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。

可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

索引


MySQL索引。首先,MySQL的索引是在存储引擎层面实现的,并不是在服务器层面实现的。所以每一种的存储引擎的索引都是不是完全相同的。MySQL目前提供了一下4种索引。

B-Tree索引:最常见的搜索引擎。大部分的搜索搜索引擎是支持B-Tree。

Hash索引:这个搜索引擎只有Memory引擎支持。

R-Tree索引:又称为空间索引。空间索引要是MyISAM的一个特殊搜索引擎。

Full-text:全文索引。全文索引也是MyISAM的一个特殊的索引类型。InnoDB 从MySQL 5.6版本开始提供了对全文的支持。

三种引擎对四种索引的支持情况

索引

MyISAM引擎

Memory引擎

InnoDB引擎

B-Tree

Y

Y

Y

Hash

N

Y

N

R-Tree

Y

N

N

Full-text

Y

N

N

原文发布于微信公众号 - 数据库SQL(SQLdba)

原文发表时间:2018-02-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏匠心独运的博客

大型分布式业务平台数据库优化方法(上)

文章摘要:一个小小的MySQL数据库B-Tree索引可能会带来意想不到的性能优化提升……

794
来自专栏菩提树下的杨过

linq to sql的多条件动态查询(下)

借助老外写的一个扩展表达式的类,可以把上篇中的代码写得更优雅 这是PredicateBuilder的源文件 public static class Predic...

1789
来自专栏程序你好

MySQL中MyISAM与InnoDB存储的区别

781
来自专栏乐沙弥的世界

Buffer cache 的调整与优化(二)

Buffer cache 实际上细分为多个不同的Buffer cache,如keep pool,recycle pool,default pool,下面描述不...

482
来自专栏Laoqi's Linux运维专列

Mysql中MyISAM引擎和InnoDB引擎的比较

结论 如果不清楚自己应该用什么引擎,那么请选择InnoDB,Mysql5.5+的版本默认引擎都是InnoDB,早期的Mysql版本默认的引擎是MyISAM --...

3386
来自专栏Java Web

Java 面试知识点解析(六)——数据库篇

1233
来自专栏idba

死锁案例之五

死锁其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友...

714
来自专栏匠心独运的博客

数据库技术之记一次排查DB死锁的分析与思考

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到...

521
来自专栏枕边书

PHP中的数据库一、MySQL优化策略综述

前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身,而在于数据库。我们都知道,PHP开发中,数据的增删改查是核心。为了提升PHP的运行效率,程序员不光需...

2138
来自专栏散尽浮华

MySQL存储引擎之Myisam和Innodb总结性梳理

Mysql有两种存储引擎:InnoDB与Myisam,下表是两种引擎的简单对比 MyISAM InnoDB 构成上的区别: 每个MyISAM...

1745

扫描关注云+社区