MYSQL数据库基本使用

数据库（database）：是数据的集合，它以一定的组织形式存于存储介质上

DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心

DBA：负责数据库的规划、设计、协调、维护和管理等工作

应用程序指以数据库为基础的应用程序

 

数据库管理系统的优点：

相互关联的数据的集合

较少的数据冗余

程序与数据相互独立

保证数据的安全、可靠

最大限度地保证数据的正确性

数据可以并发使用并能同时保证一致性

 

数据库管理系统的基本功能：

数据定理            数据处理               数据安全                 数据备份

数据库系统的架构：

单机架构           大型主机/终端架构             主从式架构（C/S）          分布式架构

 

主流的数据库是关系型数据库和结构化查询语言（sql语言）

关系：关系就是二维表（表中的行，列 次序不是很重要）

行 row  :表中的每一行，又称为一条记录

列 column ：表中的每一列，又称为属性，字段

主键 primary key ：用于为已确定一条记录的字段

RDBMS 关系数据库管理系统

MySQL: MySQL, MariaDB, Percona Server

PostgreSQL: 简称为pgsql，EnterpriseDB

Oracle：      MSSQL：      DB2

事务transaction：多个操作被当作一个整体对待

ACID:

A: 原子性             C：一致性               I: 隔离性                  D：持久性

事务的特性－原子性：
事务中的操作，要么全做成，要么都不做
事务是不可拆分的
事务必须以 Commit/Rollback 结束

事务的特性－一致性：
单独运行的事务，必须保证保持数据库的一致状态
从一个一致状态迁移到另一个一致状态
与原子性相关

事务的特性－隔离性：
多个并发事务之间不能相互干扰
并发不影响事务的执行

事务的特性－持久性：
一旦事务成功完成（Commit），它对数据库的更新应该是持久的
即使在写入磁盘之前，系统发生故障
在下次启动之后，也应保障数据更新的有效

实体-联系模型E-R

实体Entity

客观存在并可以相互区分的客观事物或抽象事件称为实体

在E-R图中用矩形框表示实体，把实体名写在框内

属性:   实体所具有的特征或性质

联系是数据之间的关联集合，是客观存在的应用语义链

实体内部的联系：指组成实体的各属性之间的联系

实体之间的联系：指不同实体之间联系

实体之间的联系用菱形框表示

 

联系类型

一对一联系(1:1)

一对多联系(1:n)

用外键foreign  key    简写  FK   每个表都有唯一的主键primary key   简写PK

多对多联系(m:n)

构建第三张表（外键），分别与第一张表（主键）和第二张表（主键）形成主外键关系  ，由此形成多对多

优点是节约磁盘空间              缺点是逻辑性复杂

数据三要素

数据结构：

包括两类，一类是与数据类型、内容、性质有关的对象，比如关系模型中的域、属性和关系等

另一类是与数据之间联系有关的对象，它从数据组织层表达数据记录与字段的结构

数据的操作：

数据提取：在数据集合中提取感兴趣的内容。（SELECT）选择

数据更新：变更数据库中的数据。INSERT 增、DELETE删、UPDATE 改

数据的约束条件 ：是一组完整性规则的集合

实体（行）完整性 Entity integrity

域（列）完整性 Domain Integrity  主键PK      唯一键UK

参考完整性 Referential Integrity  外键FK

 

简易数据规划流程

第一阶段：收集数据，得到字段

收集必要且完整的数据项

转换成数据表的字段

第二阶段：把字段分类，归入表，建立表的关联

关联：表和表间的关系

分割数据表并建立关联的优点

节省空间          减少输入错误            方便数据修改

第三阶段：

规范化数据库

 

数据库的正规化分析

RDMBS设计范式基础概念

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同

的范式，各种范式呈递次规范，越高的范式数据库冗余越小

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式

（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式

（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一

般说来，数据库只需满足第三范式(3NF）即可

范式

1NF：无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能

有多个值或者不能有重复的属性。除去同类型的字段，就是无重复的列

说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库

2NF：属性完全依赖于主键，第二范式必须先满足第一范式，要求表中的每个行必须可以被唯一地区分。                 通常为表加上一个列，以存储各个实例的唯一标识PK，非PK的字段需要与整个PK有直接相关性

3NF：属性不依赖于其它非主属性，满足第三范式必须先满足第二范式。第三范式要求一个数据库表中不               包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系

SQL概念

SQL: Structure Query Language

结构化查询语言        SQL解释器

数据存储协议：应用层协议，C/S

S：server, 监听于套接字，接收并处理客户端的应用请求

C：Client

程序接口      CLI      字符界面 命令行          GUI      图像界面

应用编程接口 （数据库的开发接口）

ODBC：Open Database Connectivity    微软

JDBC：Java Data Base Connectivity

 

约束：constraint，表中的数据要遵守的限制

主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，即NOT

NULL，一个表只能有一个

惟一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL，一个表可以存

在多个

外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据

检查：字段值在一定范围内

索引

索引：将表中的一个或多个字段中的数据复制一份另存，并且此些需要按特定次序排序存储

关系运算：

选择：挑选出符合条件的行（SELECT）

投影：挑选出需要的字段 （列 row ）

连接：表间字段的关联（外键 foreign  key ）

数据模型

数据抽象：

物理层：数据存储格式，即RDBMS在磁盘上如何组织文件

逻辑层：DBA角度，描述存储什么数据，以及数据间存在什么样的关系

视图层：用户角度，描述DB中的部分数据

关系模型的分类：

关系模型

基于对象的关系模型

半结构化的关系模型：XML数据是扩展性的标记，标签语言【元数据  TOMCAT】

 

MySQL历史

1979年：TcX公司 Monty Widenius，Unireg
1996年：发布MySQL1.0，Solaris版本，Linux版本
1999年：MySQL AB公司，瑞典
2003年：MySQL 5.0版本，提供视图、存储过程等功能
2008年：sun 收购
2009年：oracle收购sun
2009年：Monty成立MariaDB

 

MySQL和MariaDB

官方网址：https://www.mysql.com/            http://mariadb.org/

官方文档:   https://dev.mysql.com/doc/      https://mariadb.com/kb/en/

版本演变：MySQL：5.1 –> 5.5 –> 5.6 –> 5.7               MariaDB：5.5 –>10.0–> 10.1 –> 10.2 –> 10.3

MySQL体系结构

MariaDB的特性插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有

差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎

MyISAM ==> Aria        InnoDB ==> XtraDB

单进程，多线程       诸多扩展和新特性            提供了较多测试组件            开源

存储引擎比较：    https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html

MyISAM引擎特点：

不支持事务         表级锁定           读写相互阻塞，写入不能读，读时不能写            只缓存索引

不支持外键约束        不支持聚簇索引      读取数据较快，占用资源较少

不支持MVCC（多版本并发控制机制）高并发            崩溃恢复性较差

MySQL5.5.5前默认的数据库引擎

 

适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）

MyISAM引擎文件：tbl_name.frm: 表格式定义     tbl_name.MYD: 数据文件      tbl_name.MYI: 索引文件

InnoDB引擎特点：

支持事务，适合处理大量短期事务         行级锁           读写阻塞与事务隔离级别相关

可缓存数据和索引       支持聚簇索引         崩溃恢复性更好              支持MVCC高并发

从MySQL5.5后支持全文索引                从MySQL5.5.5开始为默认的数据库引擎

InnoDB数据库文件

所有InnoDB表的数据和索引放置于同一个表空间中

表空间文件：datadir定义的目录下

数据文件：ibddata1, ibddata2, …

每个表单独使用一个表空间存储表的数据和索引

启用：innodb_file_per_table=ON

两类文件放在数据库独立目录中

数据文件(存储数据和索引)：tb_name.ibd

表格式定义：tb_name.frm

Performance_Schema:Performance_Schema数据库

Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。

适用存放临时数据。引擎以前被称为HEAP引擎

MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为

一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库

Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级

锁和专用缓存区

Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户

端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务

器能力的，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境

BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性

Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还

要求具有最高的正常工作时间和可用性

CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导

出其他软件和应用程序之间的数据交换

BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设

计，数据自动复制，但不是本地存储

example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目

的是作为例子来说明如何开始编写新的存储引擎

MariaDB支持的其它存储引擎

OQGraph    SphinxSE      TokuDB       Cassandra      CONNECT       SQUENCE

管理存储引擎

查看mysql支持的存储引擎:

show engines;

查看当前默认的存储引擎:
show variables like ‘%storage_engine%’;

设置默认的存储引擎：vim   /etc/my.conf.d （centos7）     vim   /etc/my.conf  （centos6）

[mysqld]
default_storage_engine= InnoDB;

 

安装Mariadb

1、源代码：编译安装

2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

3、程序包管理器管理的程序包

项目官方：https://downloads.mariadb.org/mariadb/repositories/

Rpm安装mariadb

Rpm包安装    CentOS 7：安装光盘直接提供

mariadb-server 服务器包           mariadb 客户端工具包

提高安全性           mysql_secure_installation  脚本

1，输入当前root口令： Enter current password for root (enter for none):

2，设置数据库管理员root口令   Set root password?

输入密码  New password:         再次输入密码 Re-enter new password:

3，删除anonymous用户帐号      Remove anonymous users?

4,  是否禁止root远程登录     Disallow root login remotely?

5，删除test数据库     Remove test database and access to it?

6，是否让你的设置生效？  Reload privilege tables now?

MariaDB程序

mysql: 交互式的CLI工具

mysqldump: 备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操

作语句保存文本文件中

mysqladmin：基于mysql协议管理mysqld

mysqlimport: 数据导入工具

MyISAM存储引擎的管理工具：

myisamchk：检查MyISAM库

myisampack：打包MyISAM表，只读

服务器端程序

mysqld_safe

mysqld 获取默认设置：mysqld –print-defaults

mysqld_multi：多实例 ，示例：mysqld_multi –example

用户账号

mysql用户账号由两部分组成：     ‘USERNAME’@’HOST‘

HOST限制此用户可通过哪些远程主机连接mysql服务器

支持使用通配符： % 匹配任意长度的任意字符    172.16.0.0/255.255.0.0 或 172.16.%.%

_ 匹配任意单个字符

Mysql 客户端

mysql使用模式：

交互式模式

可运行命令有两类：

客户端命： \h, help     \u，use    \s，status     \!，system

服务器端命令：      SQL, 需要语句结束符；

脚本模式：

mysql –uUSERNAME -pPASSWORD < /path/somefile.sql

mysql> source /path/from/somefile.sql

mysql客户端可用选项：

-A, –no-auto-rehash 禁止补全         -u, –user= 用户名,默认为root

-h, –host= 服务器主机,默认为localhost

-p, –passowrd= 用户密码,建议使用-p,默认为空密码               -P, –port= 服务器端口

-S, –socket= 指定连接socket文件路径                                        -D, –database= 指定默认数据库

-C, –compress 启用压缩   （远程主机登录）                             -e   “SQL“        执行SQL命令

-V, –version 显示版本                                                                    -v –verbose 显示详细信息

–print-defaults 获取程序默认使用的配置

socket地址

服务器监听的两种socket地址：

ip socket: 监听在tcp的3306端口，支持远程通信

unix sock: 监听在sock文件上，仅支持本机通信    如：/var/lib/mysql/mysql.sock

服务器端配置

服务器端(mysqld)：工作特性有多种配置方式

1、命令行选项：

2、配置文件：类ini格式

集中式的配置，能够为mysql的各应用程序提供配置信息

[mysqld]      [mysqld_safe]  服务器端

[mysqld_multi]服务器多线程

[mysql] 数据库      [mysqldump]备份

[server] 服务器端通用配置     [client] 客户端通用配置