mysql 學習基礎知識匯總

標籤：基礎知識   mysql   資料庫   

DBMS模型：

層次模型

網狀模型

關聯式模式

以檔案來存取資料的困難：資料冗餘和不一致性，資料訪問困難，資料孤立，資料完整性問題，原子性問題，並發訪問問題，安全性問題

XML(擴充標機語言)

關聯式模式：

關聯式模式

E-R實體-關聯式模式

物件模型

半結構化資料模型

檔案：對於檔案來說可以從三個層次來分析

1、展示層

   檔案

2、邏輯層

   檔案系統：儲存引擎

3、物理層

    中繼資料

    資料區塊

E-R圖的表示方法

實體：  矩形表示

屬性：   橢圓表示

聯絡：   菱形表示

各實體間的對應關心：1:1      1：n     m:n

資料庫的三級模式二級映像：

外模式 -------模式-------內模式

            外模式/模式映像

   模式/內模式映像

外模式：也稱為子模式或使用者模式，是對資料庫使用者看見和使用的部分資料的邏輯結構和特徵的描述。

模式：是對資料庫中全體資料的邏輯結構和特徵的描述，是所有使用者的公用資料檢視，資料庫模式以一種資料庫模型為基礎。

內模式:也成儲存模式，是對資料存放區和結構儲存的藐視，是資料在資料庫內部儲存的表示方法，一個資料庫只能有一個內模式

三級模式的特點：1、保證資料的獨立性（內模式和模式分開，保證資料物理獨立性，模式和外模式獨立性，保證資料邏輯獨立性）

                2、簡單使用者介面 （按照外模式編寫應用程式和輸入命令，不需要知道資料庫內部的儲存結構）

3、有利於資料共用

4、有利於資料的安全保密

資料庫管理系統應有四大功能：

1、資料定義功能，DDL語言來定義

2、資料操縱功能，DML語言來操縱

3、資料庫建立和維護功能（建立：資料載入、轉儲、重新組織。維護：資料庫結構的修改、變更、擴充）

4、資料庫的運行管理功能

關聯式模式中常用的關係操作有兩類：

1、查詢操作：選擇（select）投影（project）串連（join）除（divide）並（union）交（intersection）差（difference）

2、更新操作：增加（insert）刪除（delete）修改（update）

完整性條件約束條件包括：實體完整性、參照完整性、使用者自訂完整性，前兩個是關聯式模式必須滿足到的完整性條件，成為關係的兩個不變性

主鍵：用來唯一標示一個實體（元組）的一個或多個屬性，實體完整性要求主鍵不可為空。primary key 表示

外鍵：用於建立和加強兩個表資料之間的串連的一列或多列。用foreign key 表示

參照完整性是對於外鍵而言的。若F是關係R中對應關係S的外鍵，則對應R中的元組在F上必須滿足

1、F為空白值

2、F等於S中某個元組的主索引值

使用者自訂完整性：有check 、unique

集合運算子：並、交、差

比較子：大於、小於、等於、大於等於、小於等於、不等於

邏輯運算子：非、於、或

專門關係運算子：選擇、投影、串連、除、廣義笛卡爾積

sql語言分類

DDL:資料定義語言 (Data Definition Language)

CREATE / DROP / ALTER

DML:資料操縱語言

INSERT / DELETE /SELECT /UPDATE

DCL:資料控制語言

GRANT /REVOKE

完整性條件約束條件的值：not null 、unique、primary key 、foreign key 、check、 default

資料庫控制又叫資料庫保護，包括四個方面，即安全性控制、完整性控制、並發性控制、資料庫恢複

認證授權機制、

許可權：系統許可權、對象特權、隱含特權

庫、表、索引、視圖、使用者、預存程序、儲存函數、觸發器、事件調度器

約束條件：

域約束

外鍵約束

主鍵約束

唯一性限制式

檢查性約束

資料的儲存和查詢：

  儲存管理器

功能：許可權和完整性管理器

     交易管理員

     檔案管理工具

     緩衝區管理器

  查詢管理器

功能：DML解譯器

     DDL解譯器

     查詢執行引擎

mysql是單進程多線程的

守護進程

應用進程

對於資料庫的查詢會消耗大量的記憶體，往往是服務的瓶頸所在。

解決辦法：1、使用緩衝 2、線程重用  thread reuse

串連資料庫的四種方式：1、應用程式、2、DBA(使用資料庫管理工具) 3、sql使用者（使用資料庫用戶端串連）  4、程式員（使用資料庫介面API）

關係運算：

投影

選擇

自然串連

笛卡爾積

並

如何使用程式設計語言和RDBMS進行互動

嵌入式sql：與動態sql類似，但其語言必須程式編譯時間就完全確定下來

   例如:ODBC

動態sql：程式設計語言使用函數或方法與RDBMS伺服器建立串連，並進行互動，通過建立的串連相sql伺服器發送查詢語句，並儲存知道變數中而後進行處理

   例如:JDBC

MYSQL伺服器的基主要組織結構：

連線管理員、解析器、緩衝器、最佳化器、儲存引擎

mysql的儲存引擎在5.5.8前用MYISAM(不支援事物操作，使用於多查詢的情境，如資料倉儲)，而在mysql5.5.8之後，儲存引擎採用innoDB(支援事物處理)

表管理器：負責建立修改讀取表定義檔案，維護表描述符告訴緩衝，管理表鎖

表修改模組：表建立修改、刪除插入、移除、更新等操作

表維護模組：檢查、修改、備份、恢複最佳化（磁碟重組）及解析

行：定長，變長

檔案中記錄組織：

堆檔案組織：一條記錄可以放在檔案中的任何位置

循序檔組織：根據“搜尋碼”值順序存放

散列檔案組織：

表結構定義檔案

表資料檔案

B樹索引

R樹索引

mysql資料開來源站點點

MariaDB：MariaDB資料庫管理系統是MySQL的一個分支，主要由開源社區在維護，採用GPL授權許可。開發這個分支的原因之一是:

甲骨文公司收購了MySQL後，有將MySQL閉源的潛在風險，因此社區採用分支的方式來避開這個風險。    MariaDB的目的是完全相容MySQL，

包括API和命令列，使之能輕鬆成為MySQL的代替品。在儲存引擎方面，使用XtraDB（英語:XtraDB）來代替MySQL的InnoDB。   

MariaDB由MySQL的創始人Michael Widenius（英語:Michael Widenius）主導開發，他早前曾以10億美元的價格，將自己建立的公司MySQL AB賣給了SUN，

此後，隨著SUN被甲骨文收購，MySQL的所有權也落入Oracle的手中。MariaDB名稱來自Michael Widenius的女兒Maria的名字。MariaDB基於事務的Maria儲存引擎，

替換了MySQL的MyISAM儲存引擎，它使用了Percona的 XtraDB，InnoDB的變體，分支的開發人員希望提供訪問即將到來的MySQL 5.4 InnoDB效能。這個版本還包括了

PrimeBase XT (PBXT) 和 FederatedX儲存引擎。

Percona：Percona 為 MySQL 資料庫伺服器進行了改進，在功能和效能上較 MySQL 有著很顯著的提升。該版本提升了在高負載情況下的 InnoDB 的效能、

為 DBA 提供一些非常有用的效能診斷工具；另外有更多的參數和命令來控制伺服器行為。

版本選擇：

alpha：

beta：

RC:

GA:

mysql的更改密碼三種方式：

1、mysqladmin -u username @ host password ‘new-password‘ (-p)

2、mysql>set password for ‘username‘@‘host‘ =password(‘new_password‘);

3、mysql>update mysql.user set password=password(‘new_password‘) where conditions;

  第三中方法需要flush privileges ；使資料庫重讀user表

mysql用戶端和server進行同行方式：

在同一個主機上：linux主機上基於mysql.sock進行通訊

               windows主機上基於memory（pipe）來進行通訊

不在同一主機上通訊採用TCP/IP進行通訊

mysql的專用用戶端工具：

    mysql、mysqldump、mysqladmin、mysqlcheck、mysqlimport

mysql非用戶端工具

    myisamchk、myisampack

伺服器端：

msyqld/mysqld_safe/mysqld_mutil(多執行個體)

muysl伺服器尋找設定檔可以從多個路徑尋找，順序是：

/etc/my.cnf-->/etc/mysql/my.cnf-->安裝路徑下的/my.cnf-->--defult-extra=/path  -->~./my.cnf  如果能同時找到幾個設定檔，預設是順序靠後的檔案先生效

【client】

-u -h -p --prot --protocol --database 參數DATABASE

串連使用的協議有  tcp、socket、pipe、memory

程式串連資料的方式：

動態sql：

嵌入式sql：

JDBC/ODBC

儲存引擎：

myisam ：每個表中有類檔案   。frm  表結構檔案  。MYD 表資料檔案  .MYI 表索引檔案

innodb：所有表預設使用一個資料表空間，可以開啟功能使每個表使用單獨的一個資料表空間

show engines; 查看當前伺服器支援的儲存引擎

show table status [like ‘關鍵字‘];

總結資料庫錯誤情況：

1、此前伺服器未關閉

2、資料初始化失敗

3、資料目錄位置錯誤

4、資料目錄許可權

mysql資料類型：

數值型

  精確數值

    int

    decimal  十進位

  近似數值

     float

     double

     real  實數

字元型

    定長  char,  binary(區分大小寫)

    變長  varchar，varbinary(區分大小寫)

    enum 枚舉

    set  集合

日期時間型

             date

    time

    datetime

    timestamp

資料類型的意義：

 1、儲存資料值的類型

 2、資料佔據的空間

 3、定長還是變長

 4、如何排序以及索引

 5、是否能夠索引

常用命令：

mysql> show character set; 顯示支援的字元集

mysql> show collation；顯示字元集定序

sql模型：

伺服器變數：使用者變數（使用 @ 表示）和系統變數（使用 @@ 表示）

   範圍：分為兩類

全域變數：show global variables

會話變數： show 【session】variables

    生效時間“

        動態：可以隨時生效的變數

靜態：需要寫在設定檔中或者通過參數傳遞給mysqld

   動態調整參數的生效方式：

       對於全域的變數來說：對當前會話無效，只對建立會話有效

       對於會話變數來說：及時生效，但只對當前會話有效

  設定變數使用 set  global|session  變數名=‘值’

mysql伺服器模式：

查看當前伺服器模式：show global|session variables like ‘sql_mode‘;

           select @@global|session.sql_mode;

主要的伺服器模式常用的幾種：

INT(5) ZEROFILL

FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。M表示精度，D表示標度（即小數點後面幾位，多餘的採用四捨五入的方法）

為了保證最大可能的可移植性，需要使用近似數值資料值儲存的代碼應使用FLOAT或DOUBLE PRECISION，不規定精度或位元

DECIMAL(M,D)和NUMERIC(M,D)類型在MySQL中視為相同的類型。它們用於儲存必須為確切精度的值，例如貨幣資料。當聲明該類型的列時，可以(並且通常要)指定精度和標度；M預設值是10。

BIT資料類型可用來儲存位欄位值。BIT(M)類型允許儲存M位值。M範圍為1到64

要指定位值，可以使用b‘value‘符。value是一個用0和1編寫的二進位值。例如，b‘111‘和b‘100000000‘分別表示7和128

如果為BIT(M)列分配的值的長度小於M位，在值的左邊用0填充。例如，為BIT(6)列分配一個值b‘101‘，其效果與分配b‘000101‘相同

當要在一個數值列內儲存一個超出該列允許範圍的值時，MySQL的操作取決於此時有效SQL模式。如果模式未設定，

MySQL將值裁剪到範圍的相應端點，並儲存裁減好的值。但是，如果模式設定為traditional(“strict 模式”)，超出範圍的值將被拒絕並提示錯誤，並且根據SQL標準插入會失敗

儲存函數用select調用：例select database（）；

預存程序用curl調用

數值型修飾符有：not null/  mull /default/unsigned/unique/auto_increment

AUTO_INCREMENT

整形

非空

無符號

主鍵或唯一鍵

內建函數select last_insert_id（）；

mysql伺服器關閉過程：

1、啟動關閉進程、

2、伺服器根據需要建立關閉線程

3、伺服器停止接收串連

4、伺服器終止當前活動

5、儲存引擎被停掉或者關閉

6、伺服器退出

sql語句：

show character set；查看字元集

show  collation；查看字元排序方式

建立資料庫：help create database

   create database db_name character set ‘‘ collate ‘‘;

   表示建立資料庫的同事指定資料庫字元集合字元排序方式

例：

 create database if not exists mydb3 character set ‘gbk‘ collate ‘gbk_chinese_ci‘;

建立表：三種方式 help create table

1、直接定義空表

2、從其他白哦中查詢出資料，並以之建立

3、以其他表為模板建立一個表

例：

 方式一：

 create table tb1 (id int unsigned not null auto_increment ,

                   name char(20) not null ,

                   age tinyint not null ,

  primary key(id,age),

  unique key(name),

  index(age)) engine=engine_name; 建立表的同時制定表的儲存引擎

方式二：

mysql> create table testcourse select * from courses where cid <=2;

方式三：

create table test like courses;

修改表定義：help alter table

innodb儲存引擎支援外鍵約束

建立索引

單表查詢：

distinct  表示去掉重複的項

select distinct cid2 from students where cid2 is not null;

select cname from courese where cid not in (select distinct cid2 from students where cid2 is not null);

視圖：

一般情況下不能向視圖中插入資料

有些資料庫支援物化視圖：就是將視圖儲存下來

mysql不支援物化視圖，不支援視圖建立索引

show create  顯示建立某個對象的時候使用的語句

連線管理員：

  接受請求

  建立線程

  認證使用者

  建立安全連線

並發控制問題：指的是同一時刻多個使用者訪問同一資源將引發並發控制問題。

多版本並發控制概念：簡稱MVCC

鎖類型：

讀鎖：共用鎖定 read  簡稱s鎖

寫鎖：獨佔鎖（也叫排他型封鎖） write ，簡稱x鎖

 手動添加鎖：lock table table_name lock_type read或者write

 解鎖： unlock table

封鎖協議：按照不同的規則進行的封鎖規則叫做封鎖協議。可分為

1、一級封鎖協議：加x鎖，只能解決丟失更新問題，而不能結局汙讀和重讀問題

2、二級封鎖協議 ：在一級封鎖協議基礎上再加s鎖，能結局汙讀問題，但是還不能解決重讀問題

3、三級封鎖協議：加上s協議後不立即釋放s鎖，而是在事物結束後才結束s鎖，可解決重讀問題。

鎖粒度：從大到小，mysql伺服器僅支援表級鎖，行鎖需要儲存引擎支援完成

 表鎖：

 頁鎖

 行鎖

事物：事物是資料庫系統中執行的一個獨立工作單位，它由使用者定義一個或多個語句構成的一組操作序列，這組序列要麼全做，要麼全部做

事物的三個步驟：

begin transaction

commit

rollback

事物的4個特徵：簡稱ACID準則

1、原子性：一個事物是一個不可分割的單位，要不全做，要麼全不做，如果執行事物因為某些故障導致失敗，已執行的操作也會被撤銷。

2、一致性：事物對資料庫作用是資料庫從一個一致狀態轉變到另一個一致狀態。一致狀態指的是資料庫滿足約束條件。一致性和原子性密不可分。

3、隔離性：如果多個事物並發的執行，應該想單個事物獨立執行時一樣，每個事物的執行不能被其他事物幹擾，即一個事物內部操作及使用的資料對並發的其他事物是隔離的，並發控制就是為了保證事物的隔離性。

4、持久性：指事物一旦提交，對資料庫中資料改變就是持久性的，即使資料庫因為故障受到破壞，DBMS也應該能夠恢複。

隔離性：隔離等級

read uncommitted  ：讀未提交

read committed  ：讀提交

repatable read：可重讀

seriablizable：可串列

查看當前資料庫的隔離等級：

mysql> show golbal variables like ‘%iso%‘;

資料庫的並發控制和恢複控制就是為了保證事物並發執行時和在體統出現故障是扔能滿足事物的ACID特性。

事物的執行方式：串列執行、並行存取

串列執行就是按事物的先後順序依次執行。

並行存取就是多個事物在時間上交叉執行，並發存取資料庫。

並行作業導致資料庫資料不一致性的原因：1、丟失更新、2、汙讀 3、不可重讀

事物集調度分為串列調度和並行調度。如果串列調度和並行調度執行的結構一致，則成為可序列化調度。

目前的DBMS普遍採用封鎖方法，

事物是由儲存引擎支援的，myisam 不支援事物，innodb支援事物。

事物日誌：

     重做日誌：redo log  每次執行前先寫到日誌中，在執行操作

     撤銷日誌：undo log   每次執行前記錄下執行前的狀態，

本文出自 “七顆星” 部落格，請務必保留此出處http://qikexing.blog.51cto.com/7948843/1672128

mysql 學習基礎知識匯總