MySQL复习记录（二）：数据库与MySQL基础知识

一，数据库 （一）数据库的原理

如果将数据定义为描述事物的符号记录，那么数据库就是长期存储在计算机内的、有组织的、可共享的、大量的数据集合，

数据库中的数据按一定的数据模型组织、描述和存储。根据数据模型进行数据的组织、描述、获取和维护的角色就是处于用户与操作系统之间的一层软件即数据库管理系统。

由数据库、数据库管理系统、应用程序和数据库管理员组成存储、管理、处理和维护数据的系统就是数据库系统。

（二）数据模型

数据模型就是对实际数据特征的在计算机中的抽象。

这种抽象需要满足：

1. 较真实地模拟现实世界的事物。
2. 容易被人理解。
3. 便于在计算机中实现。

而实际上这三者难以同时达到全部满足，因此就在数据库系统中针对不同的使用对象和使用目的，采用不同的数据模型：

1. 概念模型：在数据库设计阶段，从用户的角度对数据与信息进行建模。
2. 逻辑模型和物理模型：分别与《数据结构》中数据的逻辑结构和存储结构的概念很象。

从真实世界到信息世界再到机器世界的这一系列的抽象活动，就是数据库系统技术的核心所在。

1，概念模型

概念模型是从真实世界到信息世界的抽象。

首先了解一下信息世界中的一些概念：

• 实体entity：客观存在并能相互区别的事物。如学生、课程、学生的一次选课、学生与课程的关系等。
• 属性attribute：实体所具有的某一特性。如学生实体可以由学号、姓名、性别、出生年月、所在院系、入学时间等属性组成。
• 码key：唯一标识实体的属性集。 例如学号是学生实体的码。
• 实体型entity type：用实体名及其属性名集合抽象的同类实体。例如学生（学号、姓名、性别、出生年月、所在院系、入学时间）就是一个实体型。
• 实体集entity set：同一类型实体的集合。 例如全体学生就是一个实体集。
• 联系relationship：不同实体集之间的联系。实体之间的联系有一对一、 一对多和多对多等多种类型。

概念模型的一种表示方法就是实体-联系方法，具体就是用 E-R 图来描述现实世界的概念模型，

2，逻辑模型

数据模型通常由数据结构、数据操作和数据的完整性约束条件三部分组成：

• 数据结构描述数据库的组成对象以及对象之间的联系。
• 数据操作是指对数据库中各种对象(型)的实例(值)允许执行的操作的集合，包括操作及有关的操作规则。
• 数据的完整性约束条件是一组完整性规则。 完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则， 用以限定符合数据模型的数据库状态以及状态的变化， 以保证数据的正确、 有效和相容。

数据库领域中主要的逻辑数据模型有：

• 层次模型(hierarchical model)
• 网状模型(network model)
• 关系模型(relational model)
• 面向对象数据模型(object oriented data model)
• 对象关系数据模型(object relational data model)
• 半结构化数据模型(semistructure data model)

关系模型是最重要的一种数据模型。 关系型数据库系统，比如说MySQL，就采用关系模型作为数据的组织方式，。

从用户角度看，关系模型由一组关系组成。 每个关系的数据结构是一张规范化的二维表：

学号	姓名	年龄	性别	系名	年级
2013004	王小明	19	女	社会学	2013
2013008	张文斌	18	女	法律	2013
…	…	…	…	…	…

• 实体以及实体间的联系都是用关系来表示的。 例如导师实体、研究生实体、导师与研究生之间的 X 对 X 联系都可以用一个关系来表示。

因为关系模型建立关系代数和关系演算等数学理论基础上，所以能使用所谓的一次一集合(set-at-a-time)的方式实现关系模型的数据操作，这供了 一组完备的高级关系运算，以支持对数据库的各种操作，主要包括查询、插入、删除和更新数据。

• 关系型数据库系统通常用结构化查询语言（Structured QueryLanguage，SQL）。

这些操作必须满足对关系的某种约束条件即关系的完整性约束条件:

• 实体完整性.
• 参照完整性.
• 用户定义的完整性

二，MySQL体系结构和存储引擎 （一）MySQL体系结构

首先需要知道的是，MySQL是一种网络数据库系统。网络应用中最典型的架构就是 C/S架构与 B/S架构。MySQL就采用 C/S架构：

从总体来看，MySQL可分为三层：

• 应用连接层：主要是客户端连接器（Client Connectors），主要负责连接管理、授权认证、安全等等。
• MySQL服务层：负责具体的查询处理、事务管理、存储管理、恢复管理，以及其他的附加功能。
• 存储引擎层：负责通过指定的存储引擎对数据进行存储与提取。
• 物理存储层：实际存储到存储介质中的数据与文件。

（1）客户端连接器（Client Connectors）

为JDBC、python等提供基于标准的驱动程序，使开发人员能够用自己选择的语言构建数据库应用程序。

每当一个客户端通过客户端连接器发来一条连接请求，服务器都会创建一条线程来维护通信服务。

参考：
Mysql查看连接数（连接总数、活跃数、最大并发数）

（2）连接池（Connection Pool）

为了在无限的客户端的求和有限的数据库客户端连接数量的要求下保持数据库访问性能，就需要一种用来管理MySQL客户端连接任务的线程的机制，这就是数据库连接池。

参考：
数据库连接池原理之(一)、MySql数据库连接池专题

（3）管理服务和工具组件（Services & utilities）

系统管理和控制工具，例如备份恢复、MySQL 复制、集群等 。

（4）SQL 接口（SQL Interface）

接受用户的 SQL 命令，并且返回用户需要查询的结果。

（5）查询解析器（Parser）

SQL 命令传递到解析器的时候会被解析器验证和解析（权限、语法结构）。

（6）查询优化器（Optimizer）

SQL 语句在查询之前会使用查询优化器对查询进行优化。

（6）缓存（Caches）

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据 。

参考：
一图搞定MySQL体系架构、MySQL 体系结构及存储引擎

（二）存储引擎 1，什么是MySQL存储引擎

MySQL属于关系型数据库，而关系型数据库的数据存储是以数据表的形式进行的，MySQL存储引擎就是完成表的创建、数据的存储、检索、更新等技术的实质的方法。

• 在关系数据库中，数据库表是一系列二维数组的集合，用来存储数据和操作数据的逻辑结构。它由纵向的列和横向的行组成。行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性，有相应的描述信息，如数据类型、数据宽度等。

插件式的存储引擎选择是MySQL区别于其他数据库的一个最重要特性。从MySQL数据库5.5.8版本开始，默认使用 InnoDB。

由于MySQL数据库的开源特性，用户可以根据MySQL预定义的存储引擎接口编写自定义存储引擎。

2，常用的存储引擎 （1）InnoDB

InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计、支持外键。

InnoDB提供了良好的事务管理、崩溃修复和并发控制能力，但读写效率相对较差、数据空间占用相对较大。

（2）MyISAM

MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向联机分析处理（OLAP）数据库应用。

MyISAM提供了良好的数据表读性能，但写性能相对较差。

（3）MEMORY

MEMORY存储引擎将表结构保存在磁盘文件中，将表的内容是存储在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。

虽然MEMORY存储引擎速度非常快，但在使用上还是有一定的限制，比如，只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型等。

3，如何选择存储引擎

存储引擎各有优势，选择适合的才是最重要的。

4，设置表的存储引擎

1，查看数据库支持那些存储引擎：

mysql> SHOW ENGINES;	# 查询支持的存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

2，查看数据库默认使用的存储引擎：

mysql> SHOW VARIABLES LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

3，创建表时指定存储引擎：

mysql> CREATE TABLE t1(...) ENGINE=MyISAM;

4，查看某张表使用的存储引擎：

mysql> SHOW CREATE TABLE t1;

5，修改某表的存储引擎：

mysql> ALTER TABLE t1 ENGINE=InnoDB;

• 或编辑配置文件中的 default-storage-engine 参数并重启MySQL。

三，SQL 语言与MySQL数据库的基本操作 （一）SQL 语言

结构化查询语言(Structured Query Language, SQL)是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。

SQL 语句主要可以划分为以下三类：

• DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。
• DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和 select 等。
• DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的 语句关键字包括 grant、revoke 等

（二）创建并查看现有的数据库

CREATE DATAbase dbname； # 创建数据库
SHOW DATAbaseS; # 查看现有数据库

• 数据库命不要重名、不要使用关键字、最好使用小写命名。

mysql> CREATE DATAbase example;

mysql> SHOW DATAbaseS;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• mysql存储了系统的用户权限信息。
• information_schema主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
• performance_schema主要用于收集数据库服务器性能参数。
• sys主要存储系统的元数据信息，为方便地查询重要信息与性能优化提供更直观的数据。

（三）选择与删除指定的数据库

USE dbname； # 选择数据库
DROP DATAbase dbname； # 删除数据库

mysql> USE example;
Database changed	# 表示选择数据库成功
mysql> DROP DATAbase example;
Query OK, 0 rows affected (0.22 sec)	# 表示删除数据库成功
mysql> SHOW DATAbaseS;	# 删除后再查看一下
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

四，使用帮助

可以使用 MySQL 中的帮助文档以便快速查找各种语法定义：

mysql> help;	# 查看命令行命令

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (?) Synonym for `help'.
clear     (c) Clear the current input statement.
connect   (r) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
ego       (G) Send command to mysql server, display result vertically.
exit      (q) Exit mysql. Same as quit.
go        (g) Send command to mysql server.
help      (h) Display this help.
notee     (t) Don't write into outfile.
print     (p) Print current command.
prompt    (R) Change your mysql prompt.
quit      (q) Quit mysql.
rehash    (#) Rebuild completion hash.
source    (.) Execute an SQL script file. Takes a file name as an argument.
status    (s) Get status information from the server.
system    (!) Execute a system shell command.
tee       (T) Set outfile [to_outfile]. Append everything into given outfile.
use       (u) Use another database. Takes database name as argument.
charset   (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (W) Show warnings after every statement.
nowarning (w) Don't show warnings after every statement.
resetconnection(x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.

For server side help, type 'help contents'

mysql> help contents	# 查看所有可供查询的的分类
You asked for help about help category: "Contents"
For more information, type 'help ', where  is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help metadata
   Language Structure
   Plugins
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> help SHOW	# 查看SHOW命令的用法
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATAbase db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATAbaseS [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW {REPLICAS | SLAVE HOSTS}
SHOW {REPLICA | SLAVE} STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where: {
    LIKE 'pattern'
  | WHERe expr
}

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERe clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

mysql> help CREATE DATAbase		# 查看CREATE DATAbase命令的用法
Name: 'CREATE DATAbase'
Description:
Syntax:
CREATE {DATAbase | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

CREATE DATAbase creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATAbase.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html