MySQL基礎(chǔ)（命令）

（拓展）MySQL三大范式： 

補(bǔ)充：

 關(guān)系數(shù)據(jù)庫(kù)的幾種設(shè)計(jì)范式介紹

1、第一范式（1NF）

在任何一個(gè)關(guān)系數(shù)據(jù)庫(kù)中，第一范式（1NF）是對(duì)關(guān)系模式的基本要求，不滿足第一范式（1NF）的數(shù)據(jù)庫(kù)就不是關(guān)系數(shù)據(jù)庫(kù)。

所謂第一范式（1NF）是指數(shù)據(jù)庫(kù)表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值，即實(shí)體中的某個(gè)屬性不能有多個(gè)值或者不能有重復(fù)的屬性。如果出現(xiàn)重復(fù)的屬性，就可能需要定義一個(gè)新的實(shí)體，新的實(shí)體由重復(fù)的屬性構(gòu)成，新實(shí)體與原實(shí)體之間為一對(duì)多關(guān)系。在第一范式（1NF）中表的每一行只包含一個(gè)實(shí)例的信息。例如，對(duì)于圖3-2 中的員工信息表，不能將員工信息都放在一列中顯示，也不能將其中的兩列或多列在一列中顯示；員工信息表的每一行只表示一個(gè)員工的信息，一個(gè)員工的信息在表中只出現(xiàn)一次。簡(jiǎn)而言之，第一范式就是無(wú)重復(fù)的列。

2、第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來(lái)的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數(shù)據(jù)庫(kù)表中的每個(gè)實(shí)例或行必須可以被唯一地區(qū)分。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的唯一標(biāo)識(shí)。如圖3-2 員工信息表中加上了員工編號(hào)（emp_id）列，因?yàn)槊總€(gè)員工的員工編號(hào)是唯一的，因此每個(gè)員工可以被唯一區(qū)分。這個(gè)唯一屬性列被稱為主關(guān)鍵字或主鍵、主碼。

第二范式（2NF）要求實(shí)體的屬性完全依賴于主關(guān)鍵字。所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一部分的屬性，如果存在，那么這個(gè)屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來(lái)形成一個(gè)新的實(shí)體，新實(shí)體與原實(shí)體之間是一對(duì)多的關(guān)系。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的唯一標(biāo)識(shí)。簡(jiǎn)而言之，第二范式就是非主屬性非部分依賴于主關(guān)鍵字。

3、第三范式（3NF）

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

為什么要有數(shù)據(jù)庫(kù)：文件 管理數(shù)據(jù)的問(wèn)題：

1.數(shù)據(jù)冗余和不一致性

2.大數(shù)據(jù)訪問(wèn)困難

3.數(shù)據(jù)孤立

4.完整性和原子性

5.并發(fā)訪問(wèn)異常

6.安全性問(wèn)題

由此，有了數(shù)據(jù)庫(kù)。。。

數(shù)據(jù)庫(kù)概述

數(shù)據(jù)庫(kù)：指的是以一定方式存儲(chǔ)在一起、能為多個(gè)用戶共享，具有盡可能小的冗余度的特點(diǎn)、是與應(yīng)用程序彼此獨(dú)立的數(shù)據(jù)集合。

數(shù)據(jù)庫(kù)服務(wù)器的基本概念

數(shù)據(jù)庫(kù)

       DBMS 數(shù)據(jù)庫(kù)管理系統(tǒng)（能夠操作和管理數(shù)據(jù)庫(kù)的大型軟件）

       RDBMS 關(guān)系式數(shù)據(jù)庫(kù)（=DBMS）

              1.數(shù)據(jù)以表格的形式出現(xiàn)

              2.每行為各種記錄名稱

              3.每列為記錄名稱所對(duì)應(yīng)的數(shù)據(jù)域

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

              5.若干的表單組成database

數(shù)據(jù)表

       數(shù)據(jù)（記錄）

       字段（id name ....）

       類型（定義字段中的內(nèi)容）

       主鍵（檢索時(shí)用）

數(shù)據(jù)庫(kù)的三種基本形式

層次模型：

按照層次結(jié)構(gòu)的形式組織數(shù)據(jù)庫(kù)數(shù)據(jù)的模型

缺點(diǎn)：冗余數(shù)據(jù)

網(wǎng)狀模型:

按照網(wǎng)狀結(jié)構(gòu)的形式組織數(shù)據(jù)庫(kù)數(shù)據(jù)的模型

缺點(diǎn)：后期維護(hù)困難

關(guān)系模型：

按照關(guān)系結(jié)構(gòu)的形式組織數(shù)據(jù)庫(kù)數(shù)據(jù)的模型

sql語(yǔ)句：

結(jié)構(gòu)化查詢語(yǔ)句

sql類型：

DML      數(shù)據(jù)操作語(yǔ)言：用來(lái)操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)

       INSERT

       DELETE

       SELECT

       UPDATE

DDL       數(shù)據(jù)描述語(yǔ)言：用來(lái)建立數(shù)據(jù)庫(kù)、定義數(shù)據(jù)關(guān)系

       CREATE

       DROP

       ALTER

DCL        數(shù)據(jù)控制語(yǔ)言：用來(lái)控制數(shù)據(jù)庫(kù)組建的權(quán)限

       GRANT

       REVOKE

關(guān)系式數(shù)據(jù)庫(kù)結(jié)構(gòu)

文件邏輯關(guān)系：

上層：文件

底層：二進(jìn)制的方式存儲(chǔ)在硬盤的數(shù)據(jù)塊中

中間層：文件系統(tǒng)

數(shù)據(jù)庫(kù)邏輯關(guān)系：

上層：數(shù)據(jù)表

底層：文件

中間層：存儲(chǔ)引擎（提供存儲(chǔ)、創(chuàng)建、更新、查詢數(shù)據(jù)的實(shí)現(xiàn)方法）

mysql 支持的存儲(chǔ)引擎：

MYISAM

默認(rèn)引擎、插入和查詢速度較快

不支持事務(wù)、行級(jí)鎖和外鍵約束等功能

注：

事務(wù)：一段sql語(yǔ)句的批處理、為了保證數(shù)據(jù)原子性

鎖：行級(jí)鎖（沖突少、速度慢）；表級(jí)鎖（沖突多、速度快）；頁(yè)級(jí)鎖（折中方案）

                     約束

                            域約束：數(shù)據(jù)類型約束

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

                            主鍵約束：某字段能惟一標(biāo)識(shí)此字段所屬的實(shí)體，并且不允許為空

                            一張表中只能有一個(gè)主鍵

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

                            一張表中可以有多個(gè)

                            檢查性約束: age: int

INNODB  

              支持事務(wù)、行級(jí)鎖和外鍵約束等功能

MEMORY

工作在內(nèi)存中，通過(guò)散列保存數(shù)據(jù)，速度快、不能永久保存數(shù)據(jù)

數(shù)據(jù)的存儲(chǔ)和查詢

存儲(chǔ)管理器（實(shí)現(xiàn)存儲(chǔ)的功能，通過(guò)DDL創(chuàng)建數(shù)據(jù)表的結(jié)構(gòu)，再通過(guò)DML來(lái)保存數(shù)據(jù)）

事務(wù)管理器：提供事務(wù)功能

文件管理器：保存數(shù)據(jù)庫(kù)數(shù)據(jù)和文件的對(duì)應(yīng)關(guān)系

權(quán)限及完整性管理器：設(shè)置存儲(chǔ)權(quán)限

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

查詢管理器（實(shí)現(xiàn)查詢的功能，接收用戶的查詢請(qǐng)求、理解用戶查詢請(qǐng)求，將查詢請(qǐng)求提交給存儲(chǔ)管理器、實(shí)現(xiàn)最終存儲(chǔ)）

DDL 、DML解釋器

查詢執(zhí)行引擎

數(shù)據(jù)庫(kù)工作模式：

單進(jìn)程多線程的工作模式

守護(hù)線程

應(yīng)用線程（用戶線程）

補(bǔ)充：apache的工作模式:

一個(gè)進(jìn)程處理一個(gè)請(qǐng)求

一個(gè)線程處理一個(gè)請(qǐng)求

一個(gè)線程處理多個(gè)請(qǐng)求

mysql優(yōu)化

1.垂直擴(kuò)展

2.線程重用

3.緩存機(jī)制（nosql<memcache  redis  mongodb>）

缺點(diǎn)：

SMP      對(duì)稱多處理器架構(gòu)

3.  E-R模型

實(shí)體關(guān)系建模

實(shí)體：數(shù)據(jù)對(duì)象，即看得見摸得著

聯(lián)系：表示一個(gè)或多個(gè)實(shí)體之間的關(guān)聯(lián)

屬性：實(shí)體的某一特性

數(shù)據(jù)庫(kù)—基本概述

數(shù)據(jù)庫(kù)的版本

1.社區(qū)版

2.企業(yè)版

3.集群版

數(shù)據(jù)庫(kù)的安裝

1. 專用軟件包管理器（二進(jìn)制）

deb 、rpm等

mysql                 MySQL客戶端程序和共享庫(kù)

mysql-server           MySQL服務(wù)器需要的相關(guān)程序

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

configure、cmake

數(shù)據(jù)庫(kù)常用的配置選項(xiàng)

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

-DMYSQL_DATADIR=/data/mysql                    ----mysql的數(shù)據(jù)文件路徑

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

-DWITH_INNOBASE_STORAGE_ENGINE=1        ----使用INNOBASE存儲(chǔ)引擎

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

-DWITH_BLACKHOLE_STORAGE_ENGINE=1      ----黑洞存儲(chǔ)引擎

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1  編譯過(guò)程中取消一些存儲(chǔ)引擎指令介紹

-DWITHOUT_FEDERATED_STORAGE_ENGINE=1

-DWITHOUT_PARTITION_STORAGE_ENGINE=1

-DWITH_READLINE=1      ----支持批量導(dǎo)入mysql數(shù)據(jù)

-DWITH_SSL=system      ----mysql支持ssl會(huì)話，實(shí)現(xiàn)基于ssl的數(shù)據(jù)復(fù)

-DWITH_ZLIB=system      ----壓縮庫(kù)

-DWITH_LIBWRAP=0        ----是否可以基于WRAP實(shí)現(xiàn)訪問(wèn)控制

-DMYSQL_TCP_PORT=3306                  ----默認(rèn)端口

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock      ----默認(rèn)套接字文件路徑

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

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

-DDEFAULT_CHARSET=utf8                  ----默認(rèn)編碼機(jī)制

-DDEFAULT_COLLATION=utf8_general_ci    ----設(shè)定默認(rèn)語(yǔ)言的排序規(guī)則

-DWITH_DEBUG=0                          ----DEBUG功能設(shè)置

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

3. 服務(wù)：mysqld

4. 端口：3306

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

6. 腳本：mysql_install_db

7. mysqld_safe

8. 數(shù)據(jù)目錄 ：/var/lib/mysql

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

10. 當(dāng)意外關(guān)閉數(shù)據(jù)庫(kù)時(shí)，再開啟時(shí)假如開啟不了，找到這個(gè)，刪除再啟動(dòng)

11. 進(jìn)程文件：/var/run/mysqld/mysqld.pid

登錄及退出mysql環(huán)境

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

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

-p 用戶密碼 

-h 登陸位置（主機(jī)名或ip地址）

-P 端口號(hào)（3306改了就不是了）

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

c)    退出              exit

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

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

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

當(dāng)管理員把自己密碼忘記了，怎么找回？？？

1）關(guān)閉數(shù)據(jù)庫(kù)

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

3）啟動(dòng)數(shù)據(jù)庫(kù)

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

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

5）刪除skip-grant-tables,重啟數(shù)據(jù)庫(kù)驗(yàn)證新密碼

SQL語(yǔ)句

關(guān)于庫(kù)的操作：

關(guān)于表的操作

注：庫(kù)和表的刪除用drop,記錄刪除用delete

權(quán)限

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

revoke 取消的權(quán)限1，取消的權(quán)限2，...... on 數(shù)據(jù)庫(kù).數(shù)據(jù)表 from 用戶@登錄位置；

show grants for 用戶@登錄位置；

備份和還原

冷備份：把數(shù)據(jù)庫(kù)關(guān)閉，離線備份（使用cp、tar等命令直接備份數(shù)據(jù)庫(kù)所存放的目錄）

FRM 結(jié)構(gòu)

MYI  索引

MYD  數(shù)據(jù)

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

邏輯備份：

mysqldump

備份：mysqldump -u 用戶名 -p 數(shù)據(jù)庫(kù)名  > /備份路徑/備份文件名（備份單個(gè)數(shù)據(jù)庫(kù)）

mysqldump -u 用戶名 -p 數(shù)據(jù)庫(kù)名 表名 > /備份路徑/備份文件名（備份數(shù)據(jù)表）

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

--all-databases —備份服務(wù)器中的所有數(shù)據(jù)庫(kù)內(nèi)容

還原：mysql 數(shù)據(jù)庫(kù) < 備份文件

mysqlhotcopy

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

還原：cp -rpf 備份目錄 數(shù)據(jù)目錄（/var/lib/mysql）

補(bǔ)充的備份機(jī)制

1.日志備份

>mysql show global variables like ‘%log%’

列出mysql中和日志相關(guān)的變量

錯(cuò)誤日志

服務(wù)器啟動(dòng)和關(guān)閉時(shí)的信息

服務(wù)器運(yùn)行過(guò)程中的錯(cuò)誤信息

從服務(wù)器啟動(dòng)從服務(wù)器進(jìn)程時(shí)產(chǎn)生的信息

log-error     錯(cuò)誤日志的路徑

一般日志（不啟用）

記錄用戶對(duì)數(shù)據(jù)庫(kù)的查詢操作

general-log=ON  啟動(dòng)一般查詢?nèi)罩?/p>

log=ON              全局日志開關(guān)

log-output         日志的記錄類型

慢查詢?nèi)罩?/p>

記錄需要較長(zhǎng)時(shí)間的查詢操作

log-slow-queries=保存路徑  啟動(dòng)慢查詢?nèi)罩?，并設(shè)置個(gè)路徑

二進(jìn)制日志

所有對(duì)數(shù)據(jù)庫(kù)狀態(tài)更改的操作（create、drop、update等）

log-bin=位置 啟動(dòng)二進(jìn)制日志

>mysql  show binary logs  查看當(dāng)前使用的二進(jìn)制日志

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

還原：(mysqlbinlog)

按時(shí)間還原：

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

 按文件大小還原：

--start-position

--stop-position

事務(wù)日志：記錄事務(wù)相關(guān)的日志信息

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

2.多機(jī)備份

主從配置：實(shí)時(shí)備份

主主配置：（配置了2遍的主從）實(shí)時(shí)備份、負(fù)載均衡

多從一主：實(shí)時(shí)備份（更多的備份節(jié)點(diǎn)）

多主一從：實(shí)時(shí)備份、節(jié)約成本

實(shí)驗(yàn)

1.創(chuàng)建mysql的登錄用戶yzh可以登錄mysql服務(wù)器

1. 創(chuàng)建登錄用戶

在另一臺(tái)虛擬機(jī)上測(cè)試

創(chuàng)建的用戶為自己更改密碼

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

root找回自己的密碼并修改

數(shù)據(jù)庫(kù)的增刪改查實(shí)驗(yàn)

創(chuàng)建數(shù)據(jù)庫(kù)

創(chuàng)建數(shù)據(jù)表

復(fù)雜一點(diǎn)的，，，

插入數(shù)據(jù)

將表a2的數(shù)據(jù)復(fù)制到表a1

刪除數(shù)據(jù)庫(kù)

刪除數(shù)據(jù)表

刪除表里的數(shù)據(jù)記錄

刪除年齡在23-25之間的

修改表中的數(shù)據(jù)

修改數(shù)據(jù)表的名稱

修改數(shù)據(jù)表的字段類型

修改數(shù)據(jù)表的字段

添加字段

刪除字段

給用戶授權(quán)

1.給用戶全部權(quán)限

取消yzh用戶的刪除庫(kù)、表、表中數(shù)據(jù)的權(quán)限

備份和還原數(shù)據(jù)庫(kù)文件

1.把數(shù)據(jù)庫(kù)aa 備份到/root目錄下

2.模擬數(shù)據(jù)庫(kù)aa丟失（刪除數(shù)據(jù)庫(kù)aa）

3.還原

備份多個(gè)數(shù)據(jù)庫(kù)（--databases）

還原

備份有規(guī)則的數(shù)據(jù)庫(kù)

模擬數(shù)據(jù)庫(kù)刪除

還原

關(guān)于二進(jìn)制日志還原

開啟二進(jìn)制日志

查看二進(jìn)制日志文件

按時(shí)間還原：

如果數(shù)據(jù)庫(kù)中的bb庫(kù)被刪，需要還原

查看二進(jìn)制日志內(nèi)容

還原并查看

按文件大小還原：

還原到bb庫(kù)被刪除的數(shù)據(jù)狀態(tài)

查看bb庫(kù)被刪除前后的文件大小

還原并查看