关系型数据库

数据库系统的分类

　　数据模型是数据库系统的核心和基础，通常由数据结构、数据操作和完整性约束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平台包括两种数据库实训环境：

基于浏览器的SQL客户端，类似于navicat
集成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、查询所有不姓刘的学生的姓名、学号和性别

关系型数据库

数据库系统的分类

网状型数据库

层次型数据库

关系型数据库 rdbms

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

　　1. 实体和属性

　　2．联系

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

关系型数据库的设计范式

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

　　2、第二范式（2NF）

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

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

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

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

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

3、第三范式（3NF）

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

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

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

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

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

结构化编程语言

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

DDL

DML

DCL

TCL

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

She平台环境介绍

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

基于浏览器的SQL客户端，类似于navicat
集成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、查询所有不姓刘的学生的姓名、学号和性别

关系型数据库

数据库系统的分类

网状型数据库

层次型数据库

关系型数据库 rdbms

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

　　1. 实体和属性

　　2．联系

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

关系型数据库的设计范式

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

　　2、第二范式（2NF）

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

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

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

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

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

3、第三范式（3NF）

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

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

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

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

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

结构化编程语言

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

DDL

DML

DCL

TCL

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

She平台环境介绍

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

基于浏览器的SQL客户端，类似于navicat
集成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、查询所有不姓刘的学生的姓名、学号和性别

Quick SQL Cheatsheet

A quick reminder of all relevant SQL queries and examples on how to use them.

This repository is constantly being updated and added to by the community. Pull requests are welcome. Enjoy!

1. Finding Data Queries

SELECT: used to select data from a database

SELECT * FROM table_name;

DISTINCT: filters away duplicate values and returns rows of specified column

SELECT DISTINCT column_name;

WHERE: used to filter records/rows

SELECT column1, column2 FROM table_name WHERE condition;
SELECT * FROM table_name WHERE condition1 AND condition2;
SELECT * FROM table_name WHERE condition1 OR condition2;
SELECT * FROM table_name WHERE NOT condition;
SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);
SELECT * FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

ORDER BY: used to sort the result-set in ascending or descending order

SELECT * FROM table_name ORDER BY column;
SELECT * FROM table_name ORDER BY column DESC;
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

SELECT TOP: used to specify the number of records to return from top of table

SELECT TOP number columns_names FROM table_name WHERE condition;
SELECT TOP percent columns_names FROM table_name WHERE condition;
Not all database systems support SELECT TOP. The MySQL equivalent is the LIMIT clause
SELECT column_names FROM table_name LIMIT offset, count;

LIKE: operator used in a WHERE clause to search for a specific pattern in a column

% (percent sign) is a wildcard character that represents zero, one, or multiple characters
_ (underscore) is a wildcard character that represents a single character
SELECT column_names FROM table_name WHERE column_name LIKE pattern;
LIKE ‘a%’ (find any values that start with “a”)
LIKE ‘%a’ (find any values that end with “a”)
LIKE ‘%or%’ (find any values that have “or” in any position)
LIKE ‘_r%’ (find any values that have “r” in the second position)
LIKE ‘a_%_%’ (find any values that start with “a” and are at least 3 characters in length)
LIKE ‘[a-c]%’ (find any values starting with “a”, “b”, or “c”

IN: operator that allows you to specify multiple values in a WHERE clause

essentially the IN operator is shorthand for multiple OR conditions
SELECT column_names FROM table_name WHERE column_name IN (value1, value2, …);
SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);

BETWEEN: operator selects values within a given range inclusive

SELECT column_names FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM Products WHERE (column_name BETWEEN value1 AND value2) AND NOT column_name2 IN (value3, value4);
SELECT * FROM Products WHERE column_name BETWEEN #01/07/1999# AND #03/12/1999#;

NULL: values in a field with no value

SELECT * FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;

AS: aliases are used to assign a temporary name to a table or column

SELECT column_name AS alias_name FROM table_name;
SELECT column_name FROM table_name AS alias_name;
SELECT column_name AS alias_name1, column_name2 AS alias_name2;
SELECT column_name1, column_name2 + ‘, ‘ + column_name3 AS alias_name;

UNION: set operator used to combine the result-set of two or more SELECT statements

Each SELECT statement within UNION must have the same number of columns
The columns must have similar data types
The columns in each SELECT statement must also be in the same order
SELECT columns_names FROM table1 UNION SELECT column_name FROM table2;
UNION operator only selects distinct values, UNION ALL will allow duplicates

INTERSECT: set operator which is used to return the records that two SELECT statements have in common

Generally used the same way as UNION above
SELECT columns_names FROM table1 INTERSECT SELECT column_name FROM table2;

EXCEPT: set operator used to return all the records in the first SELECT statement that are not found in the second SELECT statement

Generally used the same way as UNION above
SELECT columns_names FROM table1 EXCEPT SELECT column_name FROM table2;

ANY|ALL: operator used to check subquery conditions used within a WHERE or HAVING clauses

The ANY operator returns true if any subquery values meet the condition
The ALL operator returns true if all subquery values meet the condition
SELECT columns_names FROM table1 WHERE column_name operator (ANY|ALL) (SELECT column_name FROM table_name WHERE condition);

GROUP BY: statement often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns

SELECT column_name1, COUNT(column_name2) FROM table_name WHERE condition GROUP BY column_name1 ORDER BY COUNT(column_name2) DESC;

HAVING: this clause was added to SQL because the WHERE keyword could not be used with aggregate functions

SELECT COUNT(column_name1), column_name2 FROM table GROUP BY column_name2 HAVING COUNT(column_name1) > 5;

WITH: often used for retrieving hierarchical data or re-using temp result set several times in a query. Also referred to as "Common Table Expression"

WITH RECURSIVE cte AS (
  SELECT c0.* FROM categories AS c0 WHERE id = 1 # Starting point
  UNION ALL
  SELECT c1.* FROM categories AS c1 JOIN cte ON c1.parent_category_id = cte.id
)
SELECT *
FROM cte

2. Data Modification Queries

INSERT INTO: used to insert new records/rows in a table

INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name VALUES (value1, value2 …);

UPDATE: used to modify the existing records in a table

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
UPDATE table_name SET column_name = value;

DELETE: used to delete existing records/rows in a table

DELETE FROM table_name WHERE condition;
DELETE * FROM table_name;

3. Reporting Queries

COUNT: returns the # of occurrences

SELECT COUNT (DISTINCT column_name);

MIN() and MAX(): returns the smallest/largest value of the selected column

SELECT MIN (column_names) FROM table_name WHERE condition;
SELECT MAX (column_names) FROM table_name WHERE condition;

AVG(): returns the average value of a numeric column

SELECT AVG (column_name) FROM table_name WHERE condition;

SUM(): returns the total sum of a numeric column

SELECT SUM (column_name) FROM table_name WHERE condition;

4. Join Queries

INNER JOIN: returns records that have matching value in both tables

SELECT column_names FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
SELECT table1.column_name1, table2.column_name2, table3.column_name3 FROM ((table1 INNER JOIN table2 ON relationship) INNER JOIN table3 ON relationship);

LEFT (OUTER) JOIN: returns all records from the left table (table1), and the matched records from the right table (table2)

SELECT column_names FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

RIGHT (OUTER) JOIN: returns all records from the right table (table2), and the matched records from the left table (table1)

SELECT column_names FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

FULL (OUTER) JOIN: returns all records when there is a match in either left or right table

SELECT column_names FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Self JOIN: a regular join, but the table is joined with itself

SELECT column_names FROM table1 T1, table1 T2 WHERE condition;

5. View Queries

CREATE: create a view

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

SELECT: retrieve a view

SELECT * FROM view_name;

DROP: drop a view

DROP VIEW view_name;

6. Altering Table Queries

ADD: add a column

ALTER TABLE table_name ADD column_name column_definition;

MODIFY: change data type of column

ALTER TABLE table_name MODIFY column_name column_type;

DROP: delete a column

ALTER TABLE table_name DROP COLUMN column_name;

7. Creating Table Query

CREATE: create a table

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
column4 datatype,
);

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

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的数据，因为执行了回滚，所以数据没有插入。

例子2

分别以dbadmin和命令行客户端执行事务与回滚操作：

实验1：在事务中向Student表插入一条记录，然后先后回滚、全表查询、事务提交

实验2：在事务中向Student表插入一条记录，然后先后全表查询、回滚、全表查询、事务提交

观察操作结果，并分析总结。

锁

在命令行客户端中(不能在sql操作界面中)将Course表加上读锁、Student表加上写锁，然后实验分别在两个表中增加一条记录和全表查询，观察操作结果，并分析总结。

mysql -uroot -p

mysql> lock table Course read;
mysql> lock table Student write;

死锁案例:

创建数据库

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Client A起事务，以share 锁模式读取数据

mysql> start transaction;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM t WHERE i = 1 lock in SHARE mode;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Client B另起事务，删除表t中的数据

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上面的这个超时错将会在命令执行一段时间之后，才出现。其超时时间默认设置:

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.03 sec)

视图

概念

什么是视图

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

视图特点

优点：

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

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

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

缺点：

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

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

视图的创建

下面给各位小伙伴介绍视图的创建语法，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原理

HBase是什么

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

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

与传统数据库的对比

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

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

2、HBASE优势：

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

HBase集群中的角色

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

多个从节点，HregionServer；

HBase依赖项，zookeeper；

HBase数据模型

在这里插入图片描述

HBase的存储机制

表是行的集合。

行是列族的集合。

列族是列的集合。

列是键值对的集合。

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

HBase 和 RDBMS的比较

在这里插入图片描述

Row Key 行键

通过单个row key访问；

通过row key的range（正则）

全表扫描

Columns Family 列族

Cell

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

Time Stamp 时间戳

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' 注：在删除表之前要先禁用表

在这里插入图片描述

学苑网课中心

松鼠学苑发展历程

在这里插入图片描述

主营业务

在这里插入图片描述

She教研解决方案

1、远端浏览器，从Google、GitHub、Maven等获取技术资源不再有羁绊。

2、基于Devfile的、一键式、无差别构建，基于账号的隔离独享环境，基于浏览器的全新开发模式，让你和你的小伙伴的软件工程环境精准的一致，而且能随心所欲的创建一个新的属于你的环境。

3、全系列、分步骤镜像，让你的大数据能够从任一成功阶段继续，从裸Linux到Zookeeper、Hadoop、Tez、 Hive、Spark、Oozie、HBase，到Kafka、Flink、Ambari，All in One的Jupyter，最新版本的TensorFlow，使用你擅长的任一语言，Python、Java、Scala、R、Julia。

4、无需任何配置，只要bind任一端口，你的应用便自动地expose出去，自动配置域名。

She平台架构

在这里插入图片描述

She是构建在docker/k8s之上、用于软件开发调试的大数据平台，平台本身是架构在大数据集群之上的分布式系统，包括三层：计算资源管理层、She核心调度层、应用层，应用层集合了所有课程环境，Devfile 和Workspace是其中两个核心概念：

1.Devfile是开展某项软件类开发任务所需环境的定义，那么将这个草稿建设起来的就是Workspace，即Workspace是物理的、而Devfile是逻辑的、是静态的：Workspace包括了物理上运行的各容器或物理机实体、端口、命名等一干看得见摸得着的资源，所以Devfile定义了某个实训任务的资源需求情况，如CPU、GPU、Memory、Disk等，而运行中的Work space的则实际占有了这些资源，因此，从这个意义上看，具体的实训任务决定了She平台的硬件配置需求。

2.Devfile是She平台的预置环境，即其对应的Workspace中已经安装了一系列版本号确定的工具，这些工具集的选择是根据这项开发任务的通用需求而定的，是通用的；但是我们可以根据需要卸载、升级、安装相应工具。

HFS三节点集群拓扑结构

在这里插入图片描述

为了降低实训成本，我们以三节点为例搭建HFS集群，但这个集群理论上可以水平扩展到10万点的规模。

余老师带你学习数据库