MySQL基礎（命令）

（拓展）MySQL三大范式： 

補充：

 關系數據庫的幾種設計范式介紹

1、第一范式（1NF）

在任何一個關系數據庫中，第一范式（1NF）是對關系模式的基本要求，不滿足第一范式（1NF）的數據庫就不是關系數據庫。

所謂第一范式（1NF）是指數據庫表的每一列都是不可分割的基本數據項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。如果出現重復的屬性，就可能需要定義一個新的實體，新的實體由重復的屬性構成，新實體與原實體之間為一對多關系。在第一范式（1NF）中表的每一行只包含一個實例的信息。例如，對于圖3-2 中的員工信息表，不能將員工信息都放在一列中顯示，也不能將其中的兩列或多列在一列中顯示；員工信息表的每一行只表示一個員工的信息，一個員工的信息在表中只出現一次。簡而言之，第一范式就是無重復的列。

2、第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數據庫表中的每個實例或行必須可以被唯一地區分。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。如圖3-2 員工信息表中加上了員工編號（emp_id）列，因為每個員工的員工編號是唯一的，因此每個員工可以被唯一區分。這個唯一屬性列被稱為主關鍵字或主鍵、主碼。

第二范式（2NF）要求實體的屬性完全依賴于主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性，如果存在，那么這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與原實體之間是一對多的關系。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。簡而言之，第二范式就是非主屬性非部分依賴于主關鍵字。

3、第三范式（3NF）

滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個數據庫表中不包含已在其它表中已包含的非主關鍵字信息。例如，存在一個部門信息表，其中每個部門有部門編號（dept_id）、部門名稱、部門簡介等信息。那么在圖3-2的員工信息表中列出部門編號后就不能再將部門名稱、部門簡介等與部門有關的信息再加入員工信息表中。如果不存在部門信息表，則根據第三范式（3NF）也應該構建它，否則就會有大量的數據冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性。

為什么要有數據庫：文件 管理數據的問題：

1.數據冗余和不一致性

2.大數據訪問困難

3.數據孤立

4.完整性和原子性

5.并發訪問異常

6.安全性問題

由此，有了數據庫。。。

數據庫概述

數據庫：指的是以一定方式存儲在一起、能為多個用戶共享，具有盡可能小的冗余度的特點、是與應用程序彼此獨立的數據集合。

數據庫服務器的基本概念

數據庫

       DBMS 數據庫管理系統（能夠操作和管理數據庫的大型軟件）

       RDBMS 關系式數據庫（=DBMS）

              1.數據以表格的形式出現

              2.每行為各種記錄名稱

              3.每列為記錄名稱所對應的數據域

              4.許多的行和列組成一張表單

              5.若干的表單組成database

數據表

       數據（記錄）

       字段（id name ....）

       類型（定義字段中的內容）

       主鍵（檢索時用）

數據庫的三種基本形式

層次模型：

按照層次結構的形式組織數據庫數據的模型

缺點：冗余數據

網狀模型:

按照網狀結構的形式組織數據庫數據的模型

缺點：后期維護困難

關系模型：

按照關系結構的形式組織數據庫數據的模型

sql語句：

結構化查詢語句

sql類型：

DML      數據操作語言：用來操作數據庫中的數據

       INSERT

       DELETE

       SELECT

       UPDATE

DDL       數據描述語言：用來建立數據庫、定義數據關系

       CREATE

       DROP

       ALTER

DCL        數據控制語言：用來控制數據庫組建的權限

       GRANT

       REVOKE

關系式數據庫結構

文件邏輯關系：

上層：文件

底層：二進制的方式存儲在硬盤的數據塊中

中間層：文件系統

數據庫邏輯關系：

上層：數據表

底層：文件

中間層：存儲引擎（提供存儲、創建、更新、查詢數據的實現方法）

mysql 支持的存儲引擎：

MYISAM

默認引擎、插入和查詢速度較快

不支持事務、行級鎖和外鍵約束等功能

注：

事務：一段sql語句的批處理、為了保證數據原子性

鎖：行級鎖（沖突少、速度慢）；表級鎖（沖突多、速度快）；頁級鎖（折中方案）

                     約束

                            域約束：數據類型約束

                            外鍵約束：引用完整性約束

                            主鍵約束：某字段能惟一標識此字段所屬的實體，并且不允許為空

                            一張表中只能有一個主鍵

                            惟一性約束：每一行的某字段都不允許出現相同值，可以為空

                            一張表中可以有多個

                            檢查性約束: age: int

INNODB  

              支持事務、行級鎖和外鍵約束等功能

MEMORY

工作在內存中，通過散列保存數據，速度快、不能永久保存數據

數據的存儲和查詢

存儲管理器（實現存儲的功能，通過DDL創建數據表的結構，再通過DML來保存數據）

事務管理器：提供事務功能

文件管理器：保存數據庫數據和文件的對應關系

權限及完整性管理器：設置存儲權限

緩沖管理器：管理緩沖空間

查詢管理器（實現查詢的功能，接收用戶的查詢請求、理解用戶查詢請求，將查詢請求提交給存儲管理器、實現最終存儲）

DDL 、DML解釋器

查詢執行引擎

數據庫工作模式：

單進程多線程的工作模式

守護線程

應用線程（用戶線程）

補充：apache的工作模式:

一個進程處理一個請求

一個線程處理一個請求

一個線程處理多個請求

mysql優化

1.垂直擴展

2.線程重用

3.緩存機制（nosql<memcache  redis  mongodb>）

缺點：

SMP      對稱多處理器架構

3.  E-R模型

實體關系建模

實體：數據對象，即看得見摸得著

聯系：表示一個或多個實體之間的關聯

屬性：實體的某一特性

數據庫—基本概述

數據庫的版本

1.社區版

2.企業版

3.集群版

數據庫的安裝

1. 專用軟件包管理器（二進制）

deb 、rpm等

mysql                 MySQL客戶端程序和共享庫

mysql-server           MySQL服務器需要的相關程序

2. 源代碼軟件包（編譯安裝）

configure、cmake

數據庫常用的配置選項

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql    ----指定殘可安裝路徑（默認的就是/usr/local/mysql）

-DMYSQL_DATADIR=/data/mysql                    ----mysql的數據文件路徑

-DSYSCONFDIR=/etc                                     ----配置文件路徑

-DWITH_INNOBASE_STORAGE_ENGINE=1        ----使用INNOBASE存儲引擎

-DWITH_ARCHIVE_STORAGE_ENGINE=1         ----常應用于日志記錄和聚合分析，不支持索引

-DWITH_BLACKHOLE_STORAGE_ENGINE=1      ----黑洞存儲引擎

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1  編譯過程中取消一些存儲引擎指令介紹

-DWITHOUT_FEDERATED_STORAGE_ENGINE=1

-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_READLINE=1      ----支持批量導入mysql數據

-DWITH_SSL=system      ----mysql支持ssl會話，實現基于ssl的數據復

-DWITH_ZLIB=system      ----壓縮庫

-DWITH_LIBWRAP=0        ----是否可以基于WRAP實現訪問控制

-DMYSQL_TCP_PORT=3306                  ----默認端口

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock      ----默認套接字文件路徑

-DENABLED_LOCAL_INFILE=1                ----是否啟用LOCAL_INFILE功能

-DEXTRA_CHARSETS=all                                  ----是否支持額外的字符集

-DDEFAULT_CHARSET=utf8                  ----默認編碼機制

-DDEFAULT_COLLATION=utf8_general_ci    ----設定默認語言的排序規則

-DWITH_DEBUG=0                          ----DEBUG功能設置

 -DENABLE_PROFILING=1                    ----性能分析功能是否啟用

3. 服務：mysqld

4. 端口：3306

5. 主配置文件：/etc/my.cnf

6. 腳本：mysql_install_db

7. mysqld_safe

8. 數據目錄 ：/var/lib/mysql

9. 套接字文件：/var/lib/mysql/mysql.sock

10. 當意外關閉數據庫時，再開啟時假如開啟不了，找到這個，刪除再啟動

11. 進程文件：/var/run/mysqld/mysqld.pid

登錄及退出mysql環境

a)     設置密碼 mysqladmin     -uroot          password ‘123’

b)     登錄              mysql  -u 用戶名 -p

-p 用戶密碼 

-h 登陸位置（主機名或ip地址）

-P 端口號（3306改了就不是了）

-S 套接字文件（/var/lib/mysql/mysql.sock）

c)    退出              exit

d)   創建登錄用戶      create user 用戶名@’%’ identified by ‘密碼’

e)    修改密碼       set password=password(‘新密碼’)

set password for 用戶@登錄位置=password(‘新密碼’)      — — — root用戶為其他用戶找回密碼

當管理員把自己密碼忘記了，怎么找回？？？

1）關閉數據庫

2）修改主配置文件（/etc/my.cnf）<------ skip-grant-tables

3）啟動數據庫

4）空密碼登錄并修改密碼

update  mysql.user set password=password(‘新密碼’) where user=’root’;

5）刪除skip-grant-tables,重啟數據庫驗證新密碼

SQL語句

關于庫的操作：

關于表的操作

注：庫和表的刪除用drop,記錄刪除用delete

權限

grant 權限1，權限2，...... on 數據庫.數據表 to 用戶@登錄位置 (identified by ‘密碼’)；

revoke 取消的權限1，取消的權限2，...... on 數據庫.數據表 from 用戶@登錄位置；

show grants for 用戶@登錄位置；

備份和還原

冷備份：把數據庫關閉，離線備份（使用cp、tar等命令直接備份數據庫所存放的目錄）

FRM 結構

MYI  索引

MYD  數據

快照備份：（利用邏輯卷）

邏輯備份：

mysqldump

備份：mysqldump -u 用戶名 -p 數據庫名  > /備份路徑/備份文件名（備份單個數據庫）

mysqldump -u 用戶名 -p 數據庫名 表名 > /備份路徑/備份文件名（備份數據表）

--databases 庫1，庫2 (此時還原--->mysql < 備份文件)

--all-databases —備份服務器中的所有數據庫內容

還原：mysql 數據庫 < 備份文件

mysqlhotcopy

備份：mysqlhotcopy --flushlog -u=’用戶’ -p=’密碼’ --regexp=正則 備份目錄

還原：cp -rpf 備份目錄 數據目錄（/var/lib/mysql）

補充的備份機制

1.日志備份

>mysql show global variables like ‘%log%’

列出mysql中和日志相關的變量

錯誤日志

服務器啟動和關閉時的信息

服務器運行過程中的錯誤信息

從服務器啟動從服務器進程時產生的信息

log-error     錯誤日志的路徑

一般日志（不啟用）

記錄用戶對數據庫的查詢操作

general-log=ON  啟動一般查詢日志

log=ON              全局日志開關

log-output         日志的記錄類型

慢查詢日志

記錄需要較長時間的查詢操作

log-slow-queries=保存路徑  啟動慢查詢日志，并設置個路徑

二進制日志

所有對數據庫狀態更改的操作（create、drop、update等）

log-bin=位置 啟動二進制日志

>mysql  show binary logs  查看當前使用的二進制日志

>mysql show binlog events in ‘二進制日志(mysql-bin.000001)’ 查看二進制日志的內容

還原：(mysqlbinlog)

按時間還原：

mysqlbinlog  --start-datetime ‘YY-MM-DD HH:MM:SS’ --stop-datetime ‘YY-MM-DD HH:MM:SS’ 二進制日志 | mysql(-urot -p)   

 按文件大小還原：

--start-position

--stop-position

事務日志：記錄事務相關的日志信息

中繼日志：記錄從服務器的備份信息

2.多機備份

主從配置：實時備份

主主配置：（配置了2遍的主從）實時備份、負載均衡

多從一主：實時備份（更多的備份節點）

多主一從：實時備份、節約成本

實驗

1.創建mysql的登錄用戶yzh可以登錄mysql服務器

1. 創建登錄用戶

在另一臺虛擬機上測試

創建的用戶為自己更改密碼

root用戶為其他用戶找回密碼

root找回自己的密碼并修改

數據庫的增刪改查實驗

創建數據庫

創建數據表

復雜一點的，，，

插入數據

將表a2的數據復制到表a1

刪除數據庫

刪除數據表

刪除表里的數據記錄

刪除年齡在23-25之間的

修改表中的數據

修改數據表的名稱

修改數據表的字段類型

修改數據表的字段

添加字段

刪除字段

給用戶授權

1.給用戶全部權限

取消yzh用戶的刪除庫、表、表中數據的權限

備份和還原數據庫文件

1.把數據庫aa 備份到/root目錄下

2.模擬數據庫aa丟失（刪除數據庫aa）

3.還原

備份多個數據庫（--databases）

還原

備份有規則的數據庫

模擬數據庫刪除

還原

關于二進制日志還原

開啟二進制日志

查看二進制日志文件

按時間還原：

如果數據庫中的bb庫被刪，需要還原

查看二進制日志內容

還原并查看

按文件大小還原：

還原到bb庫被刪除的數據狀態

查看bb庫被刪除前后的文件大小

還原并查看