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存儲引擎使用逗號分隔值格式將數據存儲在文本文件中?？梢允褂肅SV引擎以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] 客戶端通用配置