余老师带你学习数据库

关系型数据库

数据库系统的分类

数据模型是数据库系统的核心和基础，通常由数据结构、数据操作和完整性约束3部分组成，各种DBMS数据库都是基于某种数据模型的，通常也按照数据模型的特点将传统数据库系统分成网状型数据库、层次型数据库和关系数据库3类数据库。（非关系型数据库NoSql，odbms）

网状型数据库

网状模型是最早出现的网状DBMS。世界上第一个网状数据库管理系统也是第一个DBMS是美国通用电气公司Bachman等人在1964年开发成功的ID（IntegratedDataStore）。

层次型数据库

层次型数据库管理系统是紧随网状数据库而出现的。层次模型是树状的。最著名最典型的层次数据库系统是IBM公司的IMS（Information Management System），这是IBM公司研制的最早的大型数据库系统程序产品。从60年代末产生起，如今已经发展到IMSV6。这个具有30年历史的数据库产品在如今的WWW应用连接、商务智能应用中扮演着新的角色（DB2，imformix）

关系型数据库 rdbms

关系型数据（RDBS，RELATIONAL DBS）是建立在关系模型上的数据库系统。关系模型就是指满足一定条件的二维表，表具有固定的列数和任意的行数，因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据库系统 ，结构和实体关系。

二维表是同类实体的各种属性的集合，每个实体对应于表中的一行，在关系中称为元组，相当于通常的一个记录；
表中的列表示属性，称为域，相当于通常记录中的一个数据项，也叫列、字段。
若二维表中有n个域，则每一行叫做一个n元组，这样的关系称为n度(元)关系。表的行对应于对象的实例，各个表的行列交点就用来存贮简单值。满足一定条件的规范化关系的集合，就构成了关系模型。
关系型数据库的组成
　　一个关系表必须符合某些特定的条件，才能成为关系模型的一部分

1、贮存在单元中的数据必须是原子的，每个单元只能贮存一条数据，这也叫信息原则。
　　2、贮存在列中的数据必须具有相同的数据类型。
　　3、每行是唯一的。
　　4、列没有顺序。
　　5、行没有顺序。
　　6、列有一个唯一性的名称。

关系型数据库的E-R模型

在设计关系型数据库时，首先需要为它建立逻辑模型。关系型数据库的逻辑模型可以通过实体和关系组成的图形来表示，这种图形称为E-R图，它可以实现现实世界中的实体和实体之间的联系转为逻辑模型，一个标准的E-R模型主要由实体、属性和联系3部分组成。

1. 实体和属性

  实体是一个数据对象，是指客观存在并可以相互区分的事物，如一个教师、一个学生、一个雇员等等。每个实体由一组属性来表示，如，一个具体的学生拥有学号、姓名、性别和班级等属性，其中学号可以唯一标识具体某个学生这个实体。具有相同属性的实体组合在一起就构成实体集—即实体集是实体的集合，而实体则是实体集中的某一个特例，例如，王大锤这个实体就是学生实体集中的一个特例。

2．联系

在实际应用中，实体之间是存在联系的，这种联系必须在逻辑模型中表现出来。在E-R模型中，联系用菱形表示，菱形框内写明“联系名”，并用“连接线”将有关实体连接起来，同时在“连接线”的旁边标注上联系的类型，两个实体之间的联系类型可以分为3类。

一对一：对于实体集A中的每一个实体，在实体集B中最多有一个实体与之相关，反之亦然，标记为1:1

一对多： 对于实体集A中的每一个实体，在实体集B中有多个实体与之关联，反之，对于实体集B中每一个实体，在实体集A中最多有一个实体与之相关，则称实体集A与实体集B具有一对多，标记为1:n

多对多： 对于实体集A中的每一个实体，在实体集B中有多个实体与之关联，反之，对于实体集B中每一个实体，在实体集A中也有多个实体与之相关，则称实体集A与实体集B具有多对多，标记为m:n

关系型数据库的设计范式

在数据库中，数据之间存在着密切的联系。关系型数据库由相互联系的一组关系所组成。在关系型数据库中的每个关系都需要规范化，使之达到一定的规范化程度，从而提高数据的结构化、共享性、一致性和可操作性。

规范化是把数据库组织成在保持存储数据完整性的同时最小化冗余数据的结构的过程。规范化的数据库必须符合关系模型的范式法则。范式可以防止在使用数据库时出现不一致的数据，并防止数据丢失。关系范式有：1NF，2NF，3NF，4NF，5NF，6NF和BCNF范式等多种。要求是从低到高逐渐递增。关系型数据库必须满足1NF，通常满足到3NF就可以了。

1、第一范式（1NF）：属性不可再分，字段保证原子性

2、第二范式（2NF）

在满足1NF的基础上，要求表中的每条记录必须被唯一的区分。为实现区分各行记录通常需要为表设置一个“区分列”，用以存储各个实体的唯一标识。在学生信息表中，设置了学号列，由于每个学员的学号都是唯一的，因此每个学生可以被唯一的区分（即使学生存在重名的情况下），那么这个唯一属性列被称为主关键字或主键。

2NF要求实体属性应该完全依赖于主关键字。而不能是对主关键字形成部分函数依赖，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

例如，这里以“员工工资信息表”为例，若以（员工编码、岗位）为组合关键字（即复合主键），就会存在如下决定关系。

（员工编码，岗位）→（决定）（姓名、年龄、学历、基本工资、绩效工资、奖金）

在上面的决定关系中，还可以进一步拆分为如下两种决定关系。

（员工编码）→（决定）（姓名、年龄、学历）

（岗位）→（决定）（基本工资）

3、第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

例如，这里以员工信息表（EMPLOYEE）为例，该表中包含员工编号、员工姓名、年龄、部门编码、部门经理等信息，该关系 表的关键字为“员工编号”，因此存在如下决定关系：

（员工编码）→（决定）（员工姓名、年龄、部门编码、部门经理）

上面的这个关系表是符合第二范式的，但它不符合第三范式，因为该关系表内部隐含着如下决定关系：

（员工编码）→（决定）（部门编码）→（决定）（部门经理）

对于关系型数据库的设计，理想的设计目标是按照“规范化”原则存储数据，因为这样能够消除数据冗余、更新异常、插入异常和删除异常。

结构化编程语言

结构化编程语言(Structured Query Language)，入门教程可参考：SQL 教程

DDL

DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.
DDL是SQL语言的四大功能之一。用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束DDL不需要commit.CREATEALTERDROPTRUNCATECOMMENTRENAME

DML

DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.
由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。DML分成交互型DML和嵌入型DML两类。依据语言的级别，DML又可分成过程性DML和非过程性DML两种。需要commit.SELECTINSERTUPDATEDELETEMERGECALLEXPLAIN PLANLOCK TABLE

DCL

DCL（Data Control Language）数据库控制语言 授权，角色控制等GRANT 授权REVOKE 取消授权
4.TCL（Transaction Control Language）事务控制语言SAVEPOINT 设置保存点ROLLBACK 回滚SET TRANSACTION

TCL

TCL（Transaction Control Language）事务控制语言SAVEPOINT 设置保存点ROLLBACK 回滚SET TRANSACTION

She平台环境介绍

She平台包括两种数据库实训环境：

1. 基于浏览器的SQL客户端，类似于navicat
   

2. 集成SQL客户端、mysql、HBase的环境，其中SQL客户端只能连接本地的mysql。
   

创建完成后mysql数据库自动启动，点击"dbadmin"进入在线SQL客户端环境，

点击"mysql/New terminal"，并切换到root用户，可进入"命令行客户端"(mysql数据库root的密码为 Yhf_1018)，

Training

问题

1、查询全体学生的学号与姓名
2、查询全体学生的姓名及其出生年份
3、查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名
4、查询所有姓刘的学生的姓名、学号和性别
5、某些学生选修课程后没有参加考试，所有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号
6、计算选修1号课程的学生平均成绩
7、查询平均成绩大于等于90分的学生学号和平均成绩
8、查询每个学生及其选修课程的情况
9、查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
10、查询每一门的间接先修课（即先修课的先修课）
11、找出每个学生超过他自己选修课程平均成绩的课程号
12、查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄
13、查询计算机科学系的学生与年龄不大于19岁的学生的差集
14、查询考试成绩不及格的学生的学号
15、查询所有不姓刘的学生的姓名、学号和性别

Mysql架构

索引

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下:

从索引存储结构划分:B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分:普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分:主键索引、辅助索引(二级索引)
从数据存储和索引键值逻辑关系划分:聚集索引(聚簇索引)、非聚集索引(非聚簇索引)

总结：创建表时无主键，查询顺序根据输入顺序；再创建索引，查询顺序也是根据输入顺序。
创建表时有主键，索引自动创建关于主键的，查询顺序是升序的。

读锁会阻塞写，但是不会阻塞读，而写锁则会把读写都阻塞

例子

1、新建数据库RUNOOB。

2、选择SQL语句操作。

3、在RUNOOB的数据库中创建emp_pay表，无主键。
命令：

CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay int NOT NULL,
 commission decimal(2, 2) NOT NULL
)

4、插入数据
命令：

INSERT INTO emp_pay VALUES (1, 500, .10);
INSERT INTO emp_pay VALUES (6, 800, .07);
INSERT INTO emp_pay VALUES (5, 1500, .03);
INSERT INTO emp_pay VALUES (9, 750, .06);

5、查询表。
命令：SELECT * FROM emp_pay

6、将employeeID为1的更改为7。
命令：update emp_pay set employeeID=7 where employeeID=1;

7、查询表。
命令：SELECT * FROM emp_pay

8、创建索引。
命令：CREATE INDEX employeeID_ind ON emp_pay (employeeID)

9、查询表。
命令：SELECT * FROM emp_pay

10、将employeeID等于6的更改为8。
命令：update emp_pay set employeeID=8 where employeeID=5;

11、查询表。
命令：SELECT * FROM emp_pay

12、在RUNOOB数据库中创建emp表，employeeID为主键。
命令：

CREATE TABLE emp
(
 employeeID int NOT NULL PRIMARY KEY,
 base_pay int NOT NULL,
 commission decimal(2, 2) NOT NULL
)

13、插入数据。
命令：

INSERT INTO emp VALUES (1, 500, .10);
INSERT INTO emp VALUES (6, 800, .07);
INSERT INTO emp VALUES (5, 1500, .03);
INSERT INTO emp VALUES (9, 750, .06);

14、查询表。
命令：SELECT * FROM emp;

15、将employeeID为1的值改为7。
命令：update emp set employeeID=7 where employeeID=1;

16、查询表。
命令：SELECT * FROM emp;

总结：创建表时无主键，查询顺序根据输入顺序；再创建索引，查询顺序也是根据输入顺序。
创建表时有主键，索引自动创建关于主键的，查询顺序是升序的。

事务

ACID

ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）
事务前后数据的完整性必须保持一致。

隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

具有ACID的特性的数据库支持强一致性，强一致性代表数据库本身不会出现不一致，每个事务是原子的，或者成功或者失败，事物间是隔离的，互相完全不影响，而且最终状态是持久落盘的，因此，数据库会从一个明确的状态到另外一个明确的状态，中间的临时状态是不会出现的，如果出现也会及时的自动的修复，因此是强一致的。

例子

1、新建数据库RUNOOB。

2、选择SQL语句操作。

3、创建数据表。
命令：CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;

4、每次操作之前需要清除上次执行的语句。

5、查询数据表。
命令：select * from runoob_transaction_test;

6、开始事务。
命令：begin;

7、在表中插入1行数据，值为5。
命令：insert into runoob_transaction_test value(5);

8、在表中插入1行值为6的数据。
命令：insert into runoob_transaction_test value(6);

9、提交事务。
命令：commit;

10、查询表。
命令：select * from runoob_transaction_test;

11、回滚。
命令：

      begin;   #开始事务
      insert into runoob_transaction_test values(7);     #插入值为7的数据
      rollback;     #回滚
      select * from runoob_transaction_test;    #查询表
      commit;     #提交事务

查询结果没有7的数据，因为执行了回滚，所以数据没有插入。

视图

概念

1. 什么是视图

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

2. 视图特点

优点：

● 简单化，数据所见即所得。

● 安全性，用户只能查询或修改他们所能见到得到的数据。

● 逻辑独立性，可以屏蔽真实表结构变化带来的影响。

缺点：

● 性能相对较差，简单的查询也会变得稍显复杂。

● 修改不方便，特变是复杂的聚合视图基本无法修改。

3. 视图的创建

下面给各位小伙伴介绍视图的创建语法，as后面就是SELECT查询的SQL语句。是不是很简单呢。

语法：CREATE VIEW 视图名 AS 查询数据源表语句;

例子

1、在RUNOOB数据库中创建car表
命令：

CREATE TABLE car(
   Id int,
   Name varchar(50),
   Cost int 
);

2、插入以下数据

3、创建视图，创建了一个廉价汽车视图，价格低于25000的车
命令：CREATE VIEW CheapCars AS SELECT Name FROM car WHERE Cost<25000;

4、通过视图查找
命令：SELECT * FROM CheapCars;

5、创建低于30000元车的视图并查找
命令：ALTER VIEW CheapCars AS SELECT Name FROM car WHERE Cost<30000;
SELECT * FROM CheapCars;
注：覆盖了之前25000的视图。

6、删除car表
命令：DROP TABLE car;

7、查询视图
命令：SELECT * FROM CheapCars;
注：当表删除时，视图就无法查询了。

8、将CheapCars视图删除
命令：DROP VIEW CheapCars;

HBase原理

HBase是什么

HBASE是一个高可靠性、高性能、面向列、可伸缩的分布式存储系统，利用HBASE技术可在廉价PC Server上搭建起大规模结构化存储集群。

HBASE的目标是存储并处理大型的数据，更具体来说是仅需使用普通的硬件配置，就能够处理由成千上万的行和列所组成的大型数据。

HBASE是Google Bigtable的开源实现，但是也有很多不同之处。比如：Google Bigtable使用GFS作为其文件存储系统，HBASE利用Hadoop HDFS作为其文件存储系统；Google运行MAPREDUCE来处理Bigtable中的海量数据，HBASE同样利用Hadoop MapReduce来处理HBASE中的海量数据；Google Bigtable利用Chubby作为协同服务，HBASE利用Zookeeper作为协同服务。

与传统数据库的对比

1、传统数据库遇到的问题：

1）数据量很大的时候无法存储；
　　2）没有很好的备份机制；
　　3）数据达到一定数量开始缓慢，很大的话基本无法支撑；

2、HBASE优势：

1）线性扩展，随着数据量增多可以通过节点扩展进行支撑；
　　2）数据存储在hdfs上，备份机制健全；
　　3）通过zookeeper协调查找数据，访问速度快。

HBase集群中的角色

一个或者多个主节点，Hmaster；

多个从节点，HregionServer；

HBase依赖项，zookeeper；

HBase数据模型

HBase的存储机制

HBase是一个面向列的数据库，在表中它由行排序。表模式定义只能列族，也就是键值对。一个表有多个列族以及每一个列族可以有任意数量的列。后续列的值连续存储在磁盘上。表中的每个单元格值都具有时间戳。总之，在一个HBase：

表是行的集合。

行是列族的集合。

列族是列的集合。

列是键值对的集合。

这里的列式存储或者说面向列，其实说的是列族存储，HBase是根据列族来存储数据的。列族下面可以有非常多的列，列族在创建表的时候就必须指定。

HBase 和 RDBMS的比较

Row Key 行键

与nosql数据库一样，row key是用来表示唯一一行记录的主键，HBase的数据时按照RowKey的字典顺序进行全局排序的，所有的查询都只能依赖于这一个排序维度。访问HBASE table中的行，只有三种方式：

通过单个row key访问；

通过row key的range（正则）

全表扫描

Row key 行键（Row key）可以是任意字符串(最大长度是64KB，实际应用中长度一般为10-1000bytes)，在HBASE内部，row key保存为字节数组。存储时，数据按照Row key的字典序(byte order)排序存储。设计key时，要充分排序存储这个特性，将经常一起读取的行存储放到一起。(位置相关性)

Columns Family 列族

列簇：HBASE表中的每个列，都归属于某个列族。列族是表的schema的一部分(而列不是)，必须在使用表之前定义。列名都以列族作为前缀。例如courses：history，courses：math 都属于courses这个列族。

Cell

由{row key，columnFamily，version} 唯一确定的单元。cell中的数据是没有类型的，全部是字节码形式存储。

Time Stamp 时间戳

HBASE中通过rowkey和columns确定的为一个存储单元称为cell。每个cell都保存着同一份数据的多个版本。版本通过时间戳来索引。时间戳的类型是64位整型。时间戳可以由HBASE(在数据写入时自动)赋值，此时时间戳是精确到毫秒的当前系统时间。时间戳也可以由客户显示赋值。如果应用程序要避免数据版本冲突，就必须自己生成具有唯一性的时间戳。每个cell中，不同版本的数据按照时间倒序排序，即最新的数据排在最前面。

为了避免数据存在过多版本造成的管理(包括存储和索引)负担，HBASE提供了两种数据版本回收方式。一是保存数据的最后n个版本，而是保存最近一段时间内的版本(比如最近7天)。用户可以针对每个列族进行设置。

HBase系统架构体系

HBase操作

1、创建hbase中的New terminal

2、以root用户登录
命令：sudo /bin/bash

3、启动HBase终端
命令：hbase shell

4、查看HBase运行状态
命令：status

5、创建一个User表，并且有一个info列族
命令：create ‘User’,‘info’

6、查看所有表
命令：list

7、查看User表的详情
命令：describe ‘User’

8、判断User表是否存在
命令：exists ‘User’

判断Stu表是否存在
命令：exists 'Stu’注：不存在

9、启动或禁用表
命令：is_disabled ‘User’

命令：is_enabled ‘User’

10、添加记录，即插入数据
命令：

put 'User','row1','info:name','xiaoming'
put 'User','row2','info:age','18'
put 'User','row3','info:sex','man'

11、根据rowKey查询某个记录
命令：

get 'User','row2'
get 'User','row3','info:sex'
get 'User','row1',{COLUMN=> 'info:name'}

12、查询所有记录
命令：scan ‘User’

13、查询前2条
命令：scan ‘User’,{LIMIT => 2}

14、范围查找
命令：scan ‘User’,{STARTROW => ‘row2’,ENDROW => ‘row3’}

15、统计表记录
命令：count ‘User’

16、删除列
命令：delete ‘User’,‘row1’,‘info:age’

17、删除行
命令：deleteall ‘User’,‘row2’

18、删除User表中的内容
命令：truncate ‘User’

19、禁用表
命令：

disable 'User'
describe 'User'

20删除表
命令：drop ‘User’
注：在删除表之前要先禁用表