一、檢視

1、檢視是什麼

        通俗的講，檢視就是一條SELECT語句執行後返回的結果集。所以我們在建立檢視的時候，主要的工作就落在建立這條SQL查詢語句上。

        檢視是對若干張基本表的引用，一張虛表，查詢語句執行的結果，不儲存具體的資料（基本表資料發生了改變，檢視也會跟著改變）；

        檢視方便操作，特別是查詢操作，減少複雜的SQL語句，增強可讀性；

2、定義檢視

將查詢的結果放入檢視中，建議檢視名稱以 v_ 開頭

create view 檢視名稱 as select 語句;

3、檢視檢視

檢視所有表的操作會將檢視也列出來

show tables;

4、使用檢視

使用檢視就是查詢操作

select * from v_stu_score;

5、刪除檢視

drop view 檢視名稱;

6、檢視的作用

        （1）、提高了重用性，就像一個函式

        （2）、對資料庫重構，卻不影響程式的執行

        （3）、提高了安全效能，可以對不同的使用者

        （4）、讓資料更加清晰

二、事務

1、事務的四大特性（ACID）

start transaction;

select balance from checking where customer_id = 10233276;

update checking set balance = balance - 200.00 where customer_id = 10233276;

update savings set balance = balance + 200.00 where customer_id = 10233276;

commit;

（1）、原子性（atomicity）

        一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性。

（2）、一致性（consistency）

        資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。（在前面的例子中，一致性確保了，即使在執行第三、四條語句之間時系統崩潰，支票賬戶中也不會損失200美元，因為事務最終沒有提交，所以事務中所做的修改也不會儲存到資料庫中。）

（3）、隔離性（isolation）

        通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。（在前面的例子中，當執行完第三條語句、第四條語句還未開始時，此時有另外的一個賬戶彙總程式開始執行，則其看到支票帳戶的餘額並沒有被減去200美元。）

（4）、久性（durability）

        一旦事務提交，則其所做的修改會永久儲存到資料庫。（此時即使系統崩潰，修改的資料也不會丟失。）

2、事務命令

表的引擎型別必須是innodb型別才可以使用事務，這是mysql表的預設引擎，檢視錶的建立語句，可以看到engine=innodb

（1）、開啟事務

begin;
或者
start transaction;

（2）、提交事務

commit;

（3）、回滾事務

rollback;

三、索引

1、什麼是索引

        索引是一種特殊的檔案(InnoDB資料表上的索引是表空間的一個組成部分)，它們包含著對資料表裡所有記錄的引用指標。

        更通俗的說，資料庫索引好比是一本書前面的目錄，能加快資料庫的查詢速度

2、索引的目的

        索引的目的在於提高查詢效率，

3、索引的使用

（1）、檢視索引

show index from 表名;

（2）、建立索引

        如果指定欄位是字串，需要指定長度，建議長度與定義欄位時的長度一致

        欄位型別如果不是字串，可以不填寫長度部分

create index 索引名稱 on 表名(欄位名稱(長度))

（3）、刪除索引

drop index 索引名稱 on 表名;

4、索引使用前後對比的案例

（1）、建立測試表，並向其中插入十萬條資料

create table test_index(title varchar(10));

from pymysql import connect

def main():
    # 建立Connection連線
    conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
    # 獲得Cursor物件
    cursor = conn.cursor()
    # 插入10萬次資料
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交資料
    conn.commit()

if __name__ == "__main__":
    main()

（2）、查詢

開啟執行時間監測

set profiling=1;

查詢第1萬調資料

select * from test_index where title='ha-99999';

檢視執行SQL的時間

show profiles;

為表test_index的titile列建立索引

create index title_index on test_index(title(10));

執行查詢語句

select * from test_index where title='ha-99999';

再次檢視執行時間

5、建立索引注意事項

        要注意的是，建立太多的索引將會影響更新和插入的速度，因為它需要同樣更新每個索引檔案。對於一個經常需要更新和插入的表格，就沒有必要為一個很少使用的where字句單獨建立索引了，對於比較小的表，排序的開銷不會很大，也沒有必要建立另外的索引。

        建立索引會佔用磁碟空間

四、賬戶管理

1、為什麼要進行賬戶管理

        在生產環境下運算元據庫時，絕對不可以使用root賬戶連線，而是建立特定的賬戶，授予這個賬戶特定的操作許可權，然後連線進行操作，主要的操作就是資料的crud。賬戶的操作主要包括建立賬戶、刪除賬戶、修改密碼、授權許可權等。

2、MySQL賬戶體系

根據賬戶所具有的許可權的不同，MySQL的賬戶可以分為以下幾種：

        服務例項級賬號：啟動了一個mysqld，即為一個資料庫例項；如果某使用者如root,擁有服務例項級分配的許可權，那麼該賬號就可以刪除所有的資料庫、連同這些庫中的表

        資料庫級別賬號：對特定資料庫執行增刪改查的所有操作

        資料表級別賬號：對特定表執行增刪改查等所有操作

        欄位級別的許可權：對某些表的特定欄位進行操作

        儲存程式級別的賬號：對儲存程式進行增刪改查的操作

3、注意

        進行賬戶操作時，需要使用root賬戶登入，這個賬戶擁有最高的例項級許可權。

        通常都使用資料庫級操作許可權

4、檢視所有使用者

所有使用者及許可權資訊儲存在mysql資料庫的user表中

desc user;

主要欄位說明：

        Host 表示允許訪問的主機

        User 表示使用者名稱

        authentication_string 表示密碼，為加密後的值

5、建立賬戶、授權

        建立賬戶及授權需要使用root賬戶（超級管理員）進行操作

        常用許可權主要包括：create、alter、drop、insert、update、delete、select

        如果分配所有許可權，可以使用 all privileges

（1）、建立賬戶及授權語法

grant 許可權列表 on 資料庫 to '使用者名稱'@'訪問主機' identified by '密碼';

說明：

        可以操作python資料庫的所有表，方式為：python.* ；如果是具體的表，方式為：python.表名

        訪問主機通常使用 百分號 %  表示此賬戶可以使用任何 ip 的主機登入訪問此資料庫⭐⭐⭐

        訪問主機可以設定成 localhost或具體的ip，表示只允許本機或特定主機訪問

（2）、檢視使用者有哪些許可權

show grants for 使用者名稱@訪問主機;

（3）、示例：

5、修改（新增）許可權

（1）、修改許可權語法

grant 許可權名稱 on 資料庫 to 賬戶@主機 with grant option;

（2）、示例：

6、修改密碼

注意：修改密碼需要使用 root 登入，修改mysql資料庫的user表

（1）、使用password( ) 函式進行密碼加密

update user set authentication_string=password('新密碼') where user='使用者名稱';

（2）、修改完成後需要重新重新整理許可權

重新整理許可權：flush privileges

（3）、示例

7、遠端登入mysql

        如果向在一個Ubuntu中使用msyql命令遠端連線另外一臺mysql伺服器的話，通過以下方式即可完成，但是此方法僅僅瞭解就好了，不要在實際生產環境中使用（此方法慎用）

（1）、建立使用者時

        訪問主機通常使用 百分號 %  表示此賬戶可以使用任何 ip 的主機登入訪問此資料庫

mysql> grant all privileges on jing_dong.* to 'lis'@'%' identified by 'lis';

（2）、修改ubuntu的mysql配置檔案

        將ubuntu中的 /etc/mysql/mysql.conf.d/mysqld.cnf 路徑下的配置檔案中繫結本地ip的配置註釋掉，然後重啟ubuntu中的mysql服務，即可進行遠端訪問

（3）、如果依然連結不上，解決方案如下：

網路不通

        通過 ping xxx.xxx.xx.xxx可以發現網路是否正常

檢視資料庫是否配置了bind_address引數

      本地登入資料庫檢視my.cnf檔案和資料庫當前引數show variables like 'bind_address'。如果設定了bind_address=127.0.0.1 那麼只能本地登入

檢視資料庫是否設定了skip_networking引數

        如果設定了該引數，那麼只能本地登入mysql資料庫

埠指定是否正確

8、刪除賬戶

語法1：

drop user '使用者名稱'@'主機';

語法2：

delete from user where user='使用者名稱';

注意：操作之後需要重新重新整理許可權

五、MySQL主從同步配置

1、主從同步的定義

        主從同步使得資料可以從一個資料庫伺服器複製到其他伺服器上，在複製資料時，一個伺服器充當主伺服器（master），其餘的伺服器充當從伺服器（slave）。因為複製是非同步進行的，所以從伺服器不需要一直連線著主伺服器，從伺服器甚至可以通過撥號斷斷續續地連線主伺服器。通過配置檔案，可以指定複製所有的資料庫，某個資料庫，甚至是某個資料庫上的某個表。

2、使用主從同步的好處：

        通過增加從伺服器來提高資料庫的效能，在主伺服器上執行寫入和更新，在從伺服器上向外提供讀功能，可以動態地調整從伺服器的數量，從而調整整個資料庫的效能。

        提高資料安全，因為資料已複製到從伺服器，從伺服器可以終止複製程式，所以，可以在從伺服器上備份而不破壞主伺服器相應資料

        在主伺服器上生成實時資料，而在從伺服器上分析這些資料，從而提高主伺服器的效能

3、主從同步機制

        Mysql伺服器之間的主從同步是基於二進位制日誌機制，主伺服器使用二進位制日誌來記錄資料庫的變動情況，從伺服器通過讀取和執行該日誌檔案來保持和主伺服器的資料一致。

        在使用二進位制日誌時，主伺服器的所有操作都會被記錄下來，然後從伺服器會接收到該日誌的一個副本。從伺服器可以指定執行該日誌中的哪一類事件（譬如只插入資料或者只更新資料），預設會執行日誌中的所有語句。

        每一個從伺服器會記錄關於二進位制日誌的資訊：檔名和已經處理過的語句，這樣意味著不同的從伺服器可以分別執行同一個二進位制日誌的不同部分，並且從伺服器可以隨時連線或者中斷和伺服器的連線。

        主伺服器和每一個從伺服器都必須配置一個唯一的ID號（在my.cnf檔案的[mysqld]模組下有一個server-id配置項），另外，每一個從伺服器還需要通過CHANGE MASTER TO語句來配置它要連線的主伺服器的ip地址，日誌檔名稱和該日誌裡面的位置（這些資訊儲存在主伺服器的資料庫裡）

4、配置主從同步的基本步驟

有很多種配置主從同步的方法，可以總結為如下的步驟：

        ⭐在主伺服器上，必須開啟二進位制日誌機制和配置一個獨立的ID

        ⭐在每一個從伺服器上，配置一個唯一的ID，建立一個用來專門複製主伺服器資料的賬號

        ⭐在開始複製程式前，在主伺服器上記錄二進位制檔案的位置資訊

        ⭐如果在開始複製之前，資料庫中已經有資料，就必須先建立一個資料快照（可以使用mysqldump匯出資料庫，或者直接複製資料檔案）

        ⭐配置從伺服器要連線的主伺服器的IP地址和登陸授權，二進位制日誌檔名和位置

5、詳細配置主從同步的方法

        主和從的身份可以自己指定，我們將虛擬機器ip為（192.168.37.128）的Ubuntu中MySQL作為主伺服器，將Linux虛擬機器中的MySQL作為從伺服器。 在主從設定前，要保證Ubuntu與Windows間的網路連通。

（1）、備份主伺服器原有資料到從伺服器

        如果在設定主從同步前，主伺服器上已有大量資料，可以使用mysqldump進行資料備份並還原到從伺服器以實現資料的複製。

/usr# mysqldump -uroot -pmysql --all-databases --lock-all-tables > /usr/local/ubuntu_db.sql

說明：

        -u ：使用者名稱

        -p ：示密碼

        --all-databases ：匯出所有資料庫

        --lock-all-tables ：執行操作時鎖住所有表，防止操作時有資料修改

        /usr/local/ubuntu_db.sql :匯出的備份資料（sql檔案）位置，可自己指定

在從伺服器（Linux）上進行資料還原

（2）、配置主伺服器master（ubuntu中的MySQL）

⭐編輯設定mysqld的配置檔案，設定log_bin和service_id

root@ubuntu:/usr# sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

⭐重啟主伺服器中的MySQL

root@ubuntu:/usr# service mysql restart

⭐登入ubuntu中的MySQL，建立用於從伺服器同步資料的賬戶

GRANT REPLICATION SLAVE ON *.* TO '使用者名稱'@'%' identified by '密碼';

FLUSH PRIVILEGES;

⭐獲取主伺服器中的二進位制日誌資訊

SHOW MASTER STATUS;

注意：從伺服器的配置需要日誌資訊中的資料

File為使用的日誌檔名字，Position為使用的檔案位置，這兩個引數須記下，配置從伺服器時會用到

（3）、配置從伺服器（Linux中的MySQL）

⭐：找到Linux中的MySQL配置檔案並修改配置檔案

⭐：重啟MySQL服務

6、進入從伺服器（Linux）的mysql，設定連線到master主伺服器

change master to master_host='192.168.37.128', master_user='slave', master_password='slave',master_log_file='mysql-bin.000001', master_log_pos=590;

注：

        master_host：主伺服器Ubuntu的ip地址

        master_log_file: 前面查詢到的主伺服器日誌檔名

        master_log_pos: 前面查詢到的主伺服器日誌檔案位置

7、從伺服器（Linux）的MySQL中開啟同步，檢視同步狀態

start slave;

show slave status \G;

8、測試主從同步

在主表中更新資料，檢視從表中是否已經更新