初識MySQL（一）

    數據庫（DATABASES）

     數據庫是一個單位或是一個應用領域的通用數據處理系統，存儲有企業和事業部門、團體和個人的有關數據的集合，該集合中數據是從全局觀點出發建立的，按一定的數據模型進行組織、描述和存儲。其結構基于數據間的自然聯系，從而可提供一切必要的存取路徑，且數據不再針對某一應用，而是面向全組織，具有整體的結構化特征，為眾多用戶所共享其信息而建立的，已經擺脫了具體程序的限制和制約。不同的用戶可以按各自的用法使用數據庫中的數據；多個用戶可以同時共享數據庫中的數據資源，即不同的用戶可以同時存取數據庫中的同一個數據。數據共享性不僅滿足了各用戶對信息內容的要求，同時也滿足了各用戶之間信息通信的要求。 

     數據庫分類

     按數據結構模型的分類，數據庫可分為如下幾種：

     1、層次結構模型：

       層級模型是一種有根節點的定向有序樹，根節點唯一，其余為分支。按照層次結構建立的數據庫，稱為層次模型數據庫。例如IMS（Information Management System）數據庫。

    2、網狀結構模型：

      用網絡結構表示實體類型以及實體之間聯系模型。顧名思義是一個事務和其它多個事務都有聯系進而構成一張網狀圖。按照網狀結構模型建立的數據庫，稱為網狀數據庫。例如DBTG（Database Task Group）數據庫。 

    3、關系結構模型：

       關系型數據結構把一些復雜的數據結構歸結為二元關系（即二維表格形式），對數據的操作幾乎全部建立在一個或多個關系表格上，通過對這些關系表格的分類、合并、連接或選取等運算來實現數據管理。當前主流的關系型數據庫有Oricle、DB2、Microsoft SQL Server、Microsoft Access、MySQL等。

    4、對象-關系型結構模型：

      對象關系型數據庫會將數據以對象形式組織起來，而不像關系型數據庫以表的形式組織數據，亦能在必要時提供ACID等事務特點，可以處理更復雜的數據類型以及復合數據類型，并建立索引，數據查詢時不需要表格join，通過指針和索引查找數據；數據間可以有繼承關系等等顯著特點。

    5、非關系型結構模型：

       以鍵值存儲，且結構不固定，每個元組可以有不固定的字段，每個元組可以根據需要增加一些自己的鍵值對，不需要對表進行關聯查詢（因為它不依賴表結構），類似于memchache，只需要根據ID取出相應的value即可完成查詢。例如Redis數據庫。

    關系型數據庫

     針對關系型數據庫作如下解釋：

    1、關系型數據庫可以簡單的理解為二維數據庫，表格式類似于excel表格，我們平時我接觸的數據據，一般都是關系型數據庫。

    2、關系型數據庫不是唯一的高級數據庫模型，也不是最優的一種，但是，關系型數據庫是現今使用最廣泛、最易于理解和使用的數據庫模型。

    3、關系：可以理解成一張二維表，每個關系都有一個關系名，即表名。

      元組：可以理解成二維表中的一行，在數據庫中常被稱作記錄。

      屬性：可以理解成二維表中的一列，在數據庫中常被成為字段。

      域：屬性的取值范圍，也是數據庫中某一列的取值限制。

      關鍵字：一組可以唯一標識元組的屬性。數據庫中常稱為主鍵有一個或多個列組成。

      關系模式：對關系的描述，在數據庫中通常稱之為表結構。

    4、關系型數據庫特點：

      容易理解：二維表結構是非常貼近邏輯世界的一個概念，相對與網狀、層次以及其它模型更容易理解。

      使用方便：通過SQL語言程序員和數據管理員可以很方便的在邏輯層面操作數據庫而不必理解其底層實現。

      易于維護：豐富的完整性（實體完整性、參照完整性、用戶自定義完整性）降低數據冗余和數據不一致的概率。       

    5、關系操作：

      數據查詢：選擇、投影、連接、并、交、差、除

       數據操作：增加、刪除、修改、查詢

    6、SQL(Structured Query Language):結構化查詢語言，一種特殊目的的編程語言，數據庫查詢和程序設計語言，用于存儲數據以及查詢、更新和管理關系型數據庫。

      SQL查詢語言組成部分：

         DDL(Data Definition Languages)語句：數據定義語句，用于定義不同的數據段、數據庫、表、列、索引等數據庫的對象定義。常用關鍵字主要包括create、drop、alter等。

         DML(Data Manipulation Language)語句：數據操縱語句，用于添加、刪除、更新和查詢數據庫記錄，并檢查數據完整性。常用關鍵字包括insert、delete、update和select等。

         DCL(Data Control Language)語句：數據控制語句，用于控制不同數據段直接的許可和訪問級別的語句，用于定義數據庫、表、字段、用戶的訪問權限和安全級別。常用關鍵字有grant、revoke等。        

    MySQL數據庫

     一、MySQL數據庫實現模型

    

     二、MySQL的核心組件（參照上圖）

    1、連接池：用于完成用戶認證、線程重用、并發連接數限制、內存檢測以及連接緩存。

    2、SQL接口：使用SQL結構查詢語言對數據庫就行操作的接口（DDL DML等）

    3、Parser：分析器，用來進行查詢轉換、權限分析獲取的操作。

    4、Optimizer：優化器，生成并選擇最佳的訪問路徑。

    5、Cache & Buffers：與存儲引擎相關的I/O性能提升工具。

    6、存儲引擎（真正執行數據查詢操作的工具）：MyISAM,InnoDB,NDB,Archive,Federated,Memory,Merge,Partner,Community,Custom等等。

    三、MySQL數據軟件安裝（本文使用編譯安裝方式安裝，后續涉及實際操作部分均基于本安裝版本）

    1、源碼文件以及版本

      

    2、實驗環境

      平臺：VMware Workstation 10

      主機A：IP地址：192.168.5.205    系統：CentOS 6.5 x86_64 編譯安裝mariadb-10.0.12（演示用主機）

      主機B：IP地址：192.168.5.206    系統：CentOS 6.5 x86_64 編譯安裝mariadb-10.0.12（mysql備份還原時用到）

    3、實驗拓撲

      

    4、實驗目的

      演示mariadb數據庫的安裝、使用、管理與維護相關操作。

    5、創建邏輯卷mydata并掛載至/mydata/data目錄下，作為mariadb的data目錄

      # fdisk -l /dev/sdb

      

      # pvcreate /dev/sdb1

        Physical volume "/dev/sdb1" successfully created

      # vgcreate myvg /dev/sdb1

        Volume group "myvg" successfully created

      # lvcreate -n mydata -L 10G myvg 

        Logical volume "mydata" created

      # mke2fs -t ext4 -L mydata -b 4096 -m 3 /dev/myvg/mydata

       編輯/etc/fstab，實現mariadb的data目錄的自動開機掛載

      

       # mount -a 

      //掛載成功

    6、安裝必要組件以及依賴程序

      # yum -y install cmake //mariadb使用cmake編譯安裝

      # yum -y groupinstall Development Tools

      # yum -y groupinstall "Server Platform Development"

      # yum -y install libxml2-devel   //默認支持libxml2，以及需要安裝libxml-devel開發組件

       注意：cmake的使用方式不同于./config 編譯方式，為方便理解，做對應如下

       ./configure        cmake .

      ./configure –help   cmake -LH 或者 ccmake 

      清理編譯生成的文件

      make clean 或者 rm CMakeCache.txt

      指定安裝路徑時使用的選項：

      -DCMAKE_INSTALL_PREFIX=/usr/local/mysql

      -DMYSQL_DATADIR=/mydata/data

      -DSYSCONFDIR=/etc

       編譯的存儲引擎，使用如下格式：

       -DWITH_INNOBASE_STORAGE_ENGINE=1

       -DWITH_ARCHIVE_STORAGE_ENGINE=1

       -DWITH_BLACKHOLE_STORAGE_ENGINE=1

       -DWITH_FEDERATED_STORAGE_ENGINE=1

       若要明確指定不編譯某存儲引擎，可以使用類似如下的選項：

       -DWITHOUT_<ENGINE>_STORAGE_ENGINE=1

       比如：

       -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1

       -DWITHOUT_FEDERATED_STORAGE_ENGINE=1

       -DWITHOUT_PARTITION_STORAGE_ENGINE=1

       如若要編譯進其它功能，如SSL等，則可使用類似如下選項來實現編譯時使用某庫或不使用某庫：

       -DWITH_READLINE=1

       -DWITH_SSL=system

       -DWITH_ZLIB=system

       -DWITH_LIBWRAP=0

       其它常用的選項：

       -DMYSQL_TCP_PORT=3306

       -DMYSQL_UNIX_ADDR=/tmp/mysql.sock

       -DENABLED_LOCAL_INFILE=1

       -DEXTRA_CHARSETS=all

       -DDEFAULT_CHARSET=utf8

       -DDEFAULT_COLLATION=utf8_general_ci

       -DWITH_DEBUG=0

       -DENABLE_PROFILING=1    

    7、創建mysql用戶并將mariadb的data目錄的屬主數組改為響應用戶和組

      # useradd -r mysql

      # chmod -R mysql:mysql /mydata/data

      注意：以下編譯安裝操作均在~/mariadb-10.0.12/ 目錄下執行

    8、編譯安裝mariadb

       執行cmake -LH查看默認項

      // install prefix

       CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql  //默認安裝路徑即可

      # cmake . -DMYSQL_DATADIR=/mydata/data -DWITH_SSL=system -DWITH_SPHINX_STORAGE_ENGINE=1

       # make

      # make install 

      注意：以下操作在/usr/local/mysql/下執行（必須在此目錄下，否則mysqld服務不能啟動）

      # cp support-files/mysql.server /etc/rc.d/init.d/mysqld //提供mysql服務啟動文件。

      # chmod +x /etc/rc.d/init.d/mysqld  //賦予mysql服務啟動文件執行權限。

      # cp support-files/my-large.cnf /etc/my.cnf //為mysql提供配置文件

      # vim /etc/my.cnf //修改配置文件，在mysqld段中指定mysql的data目錄

      

      # scripts/mysql_install_db –user=mysql –datadir=/mydata/data   //初始話mysql

      # vim /etc/profile.d/mysq.sh  //導出到環境變量

      

      # . /etc/profile.d/mysql.sh     //重讀以生效

      # chkconfig mysqld on         //配置mysqld永久開機啟動

      # service mysqld start        //啟動mysqld服務

      # ss -tnl                 //查看TCP 3306端口是否已監聽

      

      使用mysql客戶端登陸服務器端

      、

      并且，此時mysql的data目錄中已有初始化生成的相關數據

      

      查看默認數據庫信息

      > SHOW DATABASES;   //此命令顯示當前數據庫服務器已創建的數據庫

         

       information_schema數據庫 

      information_schema數據庫是MariaDB自帶的數據庫，它提供了訪問數據庫元數據的方式。元數據是關于數據的數據，如數據庫名、表明，列的字段類型，訪問權限，也稱之為“數據詞典”和“系統目錄”。information_schema是一個信息數據庫，將其它數據庫的信息（如表名、數據庫名、權限等）放在數個只讀的數個表中，此類表實際是視圖而不是基本表，無法看到與之相關的文件。 

      information_schema數據庫表說明:

      SCHEMATA表：提供了當前mysql實例中所有數據庫的信息。是show databases的結果取之此表。

      TABLES表：提供了關于數據庫中的表的信息（包括視圖）。詳細表述了某個表屬于哪個schema，表類型，表引擎，創建時間等信息。是show tables from schemaname的結果取之此表。

      COLUMNS表：提供了表中的列信息。詳細表述了某張表的所有列以及每個列的信息。是show columns from schemaname.tablename的結果取之此表。

      STATISTICS表：提供了關于表索引的信息。是show index from schemaname.tablename的結果取之此表。

      USER_PRIVILEGES（用戶權限）表：給出了關于全程權限的信息。該信息源自mysql.user授權表。是非標準表。

      SCHEMA_PRIVILEGES（方案權限）表：給出了關于方案（數據庫）權限的信息。該信息來自mysql.db授權表。是非標準表。

      TABLE_PRIVILEGES（表權限）表：給出了關于表權限的信息。該信息源自mysql.tables_priv授權表。是非標準表。

      COLUMN_PRIVILEGES（列權限）表：給出了關于列權限的信息。該信息源自mysql.columns_priv授權表。是非標準表。

      CHARACTER_SETS（字符集）表：提供了mysql實例可用字符集的信息。是SHOW CHARACTER SET結果集取之此表。

      COLLATIONS表：提供了關于各字符集的對照信息。

      COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校對的字符集。這些列等效于SHOW COLLATION的前兩個顯示字段。

      TABLE_CONSTRAINTS表：描述了存在約束的表。以及表的約束類型。

      KEY_COLUMN_USAGE表：描述了具有約束的鍵列。

      ROUTINES表：提供了關于存儲子程序（存儲程序和函數）的信息。此時，ROUTINES表不包含自定義函數（UDF）。名為“mysql.proc name”的列指明了對應于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

      VIEWS表：給出了關于數據庫中的視圖的信息。需要有show views權限，否則無法查看視圖信息。

      TRIGGERS表：提供了關于觸發程序的信息。必須有super權限才能查看該表 

      performance_schema數據庫  

       此數據庫用來手機服務器性能參數，用戶不能刪除創建該數據庫中的表，該數據庫提供一下功能。

        A、提供進程等待的詳細信息，包括鎖、互斥變量、文件信息。

        B、保存歷史事件匯總信息，為提供MySQL服務器性能提供判斷參考。

        C、易于監控新增和刪除事件點，可隨意更改數據庫監控周期     

      Performance的開啟很簡單，在my.cnf中[mysqld]加入performanc_schema，檢查性能數據庫是否啟動的命令:

          SHOW VARIABLES LIKE ‘performance_schema’;

      若是返回的 值為ON，則說明性能數據庫正常開啟狀態。 

       mysql數據庫

      mysql服務器核心數據庫，負責存儲數據的用戶、權限設置、關鍵字等mysql自己需要和管理信息，不能也不應去修改該數據庫中的信息。

               

    9、由于安裝完成后，root用戶沒有密碼，并且默認擁有匿名登陸用戶，會有安全風險，因此需要賦予root用戶密碼并刪除匿名用戶，一下分部進行分步操作      

      mysql的用戶信息存儲于mysql數據庫中的user表中，查看如下

      >SELECT user,host,password FROM mysql.user;

      

       上圖看以看到用戶信息 

      為root用戶設置密碼（值得注意的是，mysql用戶信息的表示方式為 'username'@'host'格式）。     

        A、SET PASSWORD FOR 'username'@'host' = PASSWORD('yourpassword');

        例：

        SET PASSWORD FOR 'root'@'localhost' = PASSWORD('peter');

        

        B、UPDATE mysql.user SET password=PASSWORD('yourpassword') WHERE user='username' and host='hostname or ip';

        例：

        UPDATE mysql.user SET password=PASSWORD('peter') WHERE user='root' and host='127.0.0.1';

        

        C、使用mysqladmin客戶端軟件進行修改

        用法：# mysqladmin -u USERNAME -h{HOSTNAME|IP} -p password 'newpassword'

        例：

        # mysqladmin -uroot -h127.0.0.1 -p password '@5server'

        

     刪除匿名用戶

        A、DROP USER ''@'HOST';

        例：

        DROP USER ''@'localhost';

        

        B、DELETE FROM user WHERE user='' AND host='HOSTNAME';

        例：

        DELETE FROM user WHERE user='' AND host='nod4.nod.com';

        

    Mysql程序類別以及簡介

    1、服務器端程序：啟動并監聽在套接字上，有mysqld,mysqld_safe,mysqld_multi

    2、客戶端程序：可以通過mysql協議連入服務器并發出請求，有mysql,mysqlbinlog,mysqladmin,mysqldump等。

      客戶端程序通用選項

      -u –user    指定用戶名，例如：-u root,-uroot,–user=root

      -h –host    指定主機名稱,例如：-h localhost,-hlocalhost,–host=localhost

      -p –password 指定密碼

      –protocol={tcp|socket}   //unix,linux系統指定通訊時使用的通訊協議

      –protocol={pipe|memory}  //windows系統指定通訊使用的協議，其中pipe命名管道；memory共享內存。

      –port              //當–protocol=tcp時指定使用的端口號

       –socket             //相當于–protocol=socket

      客戶端程序mysql

      功能特性：

      （1）、命令歷史

      （2）、命令行編輯功能          

         Ctrl+a //快速移動光標至行首

         Ctrl+e //快速移動光標至行尾

         Ctrl+w //刪除光標之前的單詞

         Ctrl+u //刪除行首至光標處的所有內容

         Ctrl+y //粘貼使用Ctrl+w或Ctrl+u刪除的內容

       （3）、提示符           

         mariadb>等待輸入命令

          ->：續行

         ‘>:還需要補全后半部的單引號

         “>：還需要補全后半部的雙引號

         ·>:還需補全后半部的反引號      

       （4）、客戶端命令：help列出所有命令

          clear      (\c) Clear the current input statement                   //清除當前命令行命令

          ego       (\G) Send command to mysql server, display result vertically     //豎排顯示

          go        (\g) Send command to mysql server                       //取代終止符

          delimiter   (\d) Set statement delimiter                          //定義分隔符

          exit      (\q) Exit mysql. Same as quit                          //退出客戶端程序  ，quit      (\q) Quit mysql

          help      (\h) Display this help                               //顯示幫助信息

          prompt     (\R) Change your mysql prompt                          //定義命令提示符

          source     (\.) Execute an SQL script file. Takes a file name as an argument （\. /path/from/somefile.sql),相當于mysql < /path/from/somefile.sql）

          system     (\!) Execute a system shell command                       //運行shell命令

          use       (\u) Use another database. Takes database name as argument       //指定默認庫

      （5）、mysql -e選項，在shell命令行模式下執行mysql客戶端命令，而后退出，例如：

        # mysql -e 'SHOW DATABASES;' //顯示當前mysql服務器上的數據庫信息，注意，此處-e后面跟的參數必須用單引號或雙引號引起來，否則不生效

        

      （6）、服務器端命令：

        使用help KEYWORD可以得到命令幫助，信息，最重要的是執行SQL語句：

        DDL

        DML 

      客戶端工具mysqladmin

      （1）、格式mysqladmin [options] command [command-arg] [command] [command-arg]…

      （2）、mysaladmin [options] command 中的command列表

        

        

      （3）、mysqladmin命令解釋

        status 顯示mysql的簡要狀態信息

         

          專用選項

         –sleep # 間隔#秒查看一次

          –count # 顯示多少次

         

        extended-status：顯示所有mysql服務器狀態變量相當于 mysql -e 'SHOW GLOBAL STATUS;'        

         flush-privileges：mysql -e 'FLUSH PRIVILEGES;'刷新授權表

        flush-hosts：清除被拒絕的主機緩存列表

        flush-logs：滾動日志，二進制日志和中繼日志

        flush-status：重置mysql服務器狀態變量

        flush-tables：關閉當前打開的所有文件的句柄，如果句柄正在使用則等待其結束。

        flush-threads：重置線程緩存   mysql -e 'SHOW PROCESSLIST;'

        kill：殺死指定進程

        password：修改設定密碼

        ping：查看服務器存活狀態

        processlist：顯示當前服務器的進程列表，不用進入客戶端模式

        refresh：相當于執行flush-hosts和flush-logs

        shutdown：關閉服務進程

        start-slave,stop-slave：啟動和關閉從服務器線程

        variables：顯示服務器變量

    3、工具程序：運行于服務器進程所在的主機，實現一些管理和維護操作，例如MyISAMcheck。

    MySQL數據庫的數據類型

    1、數值類型

      MySQL支持多種數值類型

      整型：

      

      注意：在mysql中沒有專用布爾型數據類型，BOOL 是TINYINT(1)的別名

           整型修飾符：UNSIGNED（無符號），NULL（空值）,NOTNULL（非空）,DEFAULT#（默認值為#）,AUTO_INCREMNET:自動增長字段，產生唯一標識符或順序值時使用此屬性，只用于整數，一般由1開始逐行加1，應用此屬性的列應該具有NOTNULL并定義為PRIMARY KEY或UNIQUE鍵。

          在整數類型中，按照取值范圍和存儲方式的不同，分為TINYINT,SMALLINT,MEDIUMINT,INT、INTEGER、BIGINT,如操作取值范圍會報“out of range”錯誤提示

          MySQL支持在類型名稱后面的小括號內指定顯示寬度，例如INT（5）

          

      浮點數值型、定點數類型：

      

      注意：浮點數類型還有REAL，是FLOAT或DOUBLE的別名，具體取決于sql_mode類型。

       

      注意：浮點數值型和定點數值型用于表示小數，浮點數值型分為FLOAT(單精度）和DOUBLE（雙精度）類型，定點數值只有DECIMAL一種類型。定點數在MySQL內部使用字符串形式存放，比浮點數更精確，適合用于表示貨幣等精度高的數據。

          浮點數和定點數使用類型名稱后加“（M,D)”表示該值一共顯示M位數字（整數+小數），其中有D位位于小數點之后；DECIMAL在不指定精度時，默認整數位10小數位為0。

      修飾符：UNSIGNED（無符號），NULL（空值）,NOTNULL（非空）,DEFAULT#（默認值為#）、AUTO_INCREMENT

      位類型：

      

      注意：位類型用于存放位字段值，最大存放值為64位，最小為1

          修飾符：DEFAULT,NULL,NOTNULL

    2、時間日期類型

      

      修飾符：NULL,NOTNULL

    3、字符串類型

      

      注意：CHAR和VARCHAR很相似，都用來保存MySQL中的較短字符串，不同的是CHAR列的長度固定為創建表時聲明的長度，而VARCHAR列中的值為可變長字符串。

    4、內建類型（隸屬于字符類）

      ENUM：該類型通常表示為ENUM(string1,string2,string3,..)，僅能從其取值中選其一。

      SET：該類型通常表示為(string1,string2,string3,…),表示允許存儲其取值元素中的元素的組合字符。

       注意：修飾符：NULL,NOTNULL,DEFAULT

    服務器變量

    1、全局變量：

      對所有會話生效；所有會話建立時均從全局繼承，繼承完成后每個會話會維護自己的會話變量；修改全局變量，僅對修改后新建的會話生效；修改全局變量需要管理員權限。      

    2、會話變量：會話變量的修改僅對當前會話生效；修改后即時生效；會話級別變量的修改不需要管理員權限。

    3、服務器變量的修改方式：

      動態修改：會話級別，立即生效；全局級別，新建立的會話生效，重啟服務后失效。

      >SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';

      

      靜態修改：需要修改配置文件或修改傳遞給mysqld的選項的值，重啟后生效。

      # vim /etc/my.cnf

      

      

    4、查看服務器變量（在mysql客戶端模式下）

      >SHOW {GLOBAL|SESSION} VARIABLES [LIKE clause];

      

      

      或

      >SELECT @@{GLOBAL|SESSION}.VARIABLE_NAME;

      

      或

      >SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='';

      

      >SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='';