一、MySQL 約束

 ? 基本介紹

約束用于確保數(shù)據(jù)庫的數(shù)據(jù)滿足特定的商業(yè)規(guī)則。在mysql中，約束包括: not null，unique, primary key, foreign key, 和 check 五種。

1、主鍵 primary key

? 基本介紹

用于唯一的標示表行的數(shù)據(jù),當定義主鍵約束后，該列不能重復

? 細節(jié)

        ? primary key不能重復而且不能為null。

        ? 一張表最多只能有一個主鍵，但可以是復合主鍵。

        ? 主鍵的指定方式有兩種：

            1、直接在字段名后指定 : 字段名 primakry key

            2、在表定義最后寫 primary key(列名)
        ? 使用desc 表名, 可以看到primary key的情況

        ? 在實際開發(fā)中，每個表往往都會設計一個主鍵。

2、not null 

如果在列上定義了not null，那么當插入數(shù)據(jù)時，必須為列提供數(shù)據(jù)。

3、unique

當定義了唯一約束后，該列值是不能重復的。

CREATE TABLE 表名(
     id INT UNIQUE , -- 表示 id 列是不可以重復的.
    `name` VARCHAR(32) ,
     email VARCHAR(32)
   ); 

? 細節(jié)

• 如果沒有指定 not null , 則 unique 字段可以有多個 null 。如果一個列(字段)， 是 unique not null 使用效果類似 primary key
• 一張表可以有多個 unique 字段

4、外鍵 foreign key

? 基本介紹

用于定義主表和從表之間的關(guān)系:外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null。

? 細節(jié)

• 外鍵指向的表的字段，要求是primary key 或者是unique
• 表的類型是innodb,這樣的表才支持外鍵
• 外鍵字段的類型要和主鍵字段的類型一致(長度可以不同)
• 外鍵字段的值，必須在主鍵字段中出現(xiàn)過, 或者為null前提是外健字段允許為null
• 一旦建立主外鍵的關(guān)系, 數(shù)據(jù)不能隨意刪除了

5、check

? 基本介紹

用于強制行數(shù)據(jù)必須滿足的條件,假定在數(shù)值列上定義了check約束并要求數(shù)列值在1000~2000之間，如果不再1000~2000之間就會提示出錯。
        oracle 和 sql.server 均支持check，但是mysq15.7目前還不支持chck，只做語法校驗，但不會生效。

? 基本使用

CREATE TABLE 表名 (
    id INT PRIMARY KEY,
    `name` VARCHAR(32) ,
 
    sex VARCHAR(6) CHECK (sex IN('man','woman')),
 
    sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);

二、MySQL應用

1、自增長

?基本介紹

? 細節(jié)

• 一般來說自增長是和 primary key 配合使用的
• 自增長也可以單獨使用[但是需要配合一個unique]
• 自增長修飾的字段為整數(shù)型的(雖然小數(shù)也可以但是非常非常少這樣使用）
• 自增長默認從1開始, 你也可以通過如下命令修改alter table 表名 auto increment=新的開始值
• 如果你添加數(shù)據(jù)時，給自增長字段(列)指定的有值，則以指定的值為準，如果指定了自增長，一般來說.，就按照自增長的規(guī)則來添加數(shù)據(jù)

? 自增長使用

CREATE TABLE 表名 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(32) NOT NULL DEFAULT '',
    `name` VARCHAR(32) NOT NULL DEFAULT '');

2、MySQL索引

? 基本介紹

        說起提高數(shù)據(jù)庫性能，索引是最物美價廉的東西了。不用加內(nèi)存, 不用改程序, 不用調(diào)sql, 查詢速度就可能提高百倍干倍。

? 索引的原理

• 沒有索引為什么會慢?因為全表掃描
• 使用索引為什么會快?形成一個索引的數(shù)據(jù)結(jié)構(gòu)，比如二叉樹
• 索引的代價：

        ① 磁盤占用

        ② 對dml(update delete insert)語句的效率影響

? 索引的類型

• 主鍵索引，主鍵自動的為主索引(類型Primary key)
• 唯一索引(UNIQUE)
• 普通索引(INDEX)
• 全文索引(FULLTEXT) [適用于MyISAM]
• 一般開發(fā), 不使用mysql自帶的全文索引，而是使用:全文搜索Solr和 ElasticSearch(ES)

create table 表(
    id int primary key, --主鍵，同時也是索引，稱為主鍵索引。
    name varchar(32));
 
create table 表(
    id int unique-- id是唯一的，同時也是索引，稱為unique索引
    );

? 索引的使用

? 如果使用索引

        ? 較頻繁的作為查詢條件字段應該創(chuàng)建索引

            select * from emp where empno=1

        ? 唯一性太差的字段不適合單獨創(chuàng)建索引，即使頻繁作為查詢條件

            select * from emp where sex='男'

        ? 更新非常頻繁的字段不適合創(chuàng)建索引

            select * from emp where logincount= 1

        ? 不會出現(xiàn)在WHERE子句中字段不該創(chuàng)建索引

3、MySQL事務

? 基本介紹

        事務用于保證數(shù)據(jù)的一致性,它由一組相關(guān)的dml語句組成，該組的dml活句要么全部成功, 要么全部失敗。如:轉(zhuǎn)賬就要用事務來處理，用以保證數(shù)據(jù)的一致性。

? 事務和鎖

        當執(zhí)行事務操作時(dml語句) ,mysql會在表上加鎖,防止其它用戶改表的數(shù)據(jù)這對用戶來講是非常重要的。

-- 1. 創(chuàng)建一張測試表
CREATE TABLE t27 ( 
    id INT,
    `name` VARCHAR(32));
 
-- 2. 開始事務
START TRANSACTION
 
-- 3. 設置保存點
SAVEPOINT a
-- 執(zhí)行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
 
SAVEPOINT b
-- 執(zhí)行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');???????
 
-- 回退到 b
ROLLBACK TO b
 
-- 繼續(xù)回退 a
ROLLBACK TO a
 
-- 如果這樣, 表示直接回退到事務開始的狀態(tài).
ROLLBACK
COMMIT

? 回退事務

        在介紹回退事務前，先介紹一下保存點(savepoint).保存點是事務中的點，用于取消部分事務，當結(jié)束事務時(commit)，會自動的刪除該事務所定義的所有保存點當執(zhí)行回退事務時，通過指定保存點可以回退到指定的點。

? 提交事務

        使用commit語包可以提交事務.當執(zhí)行了commit語句子后,會確認事務的變化、結(jié)束事務、刪除保存點、釋放鎖，數(shù)據(jù)生效。當使用commit語句結(jié)束事務子后，其它會話[其他連接]將可以查看到事務變化后的新數(shù)據(jù)[所有數(shù)據(jù)就正式生效]

?事務細節(jié)

• 如果不開始事務，默認情況下，dml操作是自動提交的, 不能回滾
• 如果開始一個事務，你沒有創(chuàng)建保存點.你可以執(zhí)行rollback,默認就是回退到你事務開始的狀態(tài)
• 你也可以在這個事務中(還沒有提交時),創(chuàng)建多個保存點.比如: savepoint aaa; 執(zhí)行dml, savepoint bbb;
• 你可以在事務沒有提交前，選擇回退到哪個保存點
• mysql的事務機制需要innodb的存儲引擎才可以使用,myisam不好使
• 開始一個事務 start transaction, set autocommit=off

? 基本介紹

        多個連接開啟各自事務操作數(shù)據(jù)庫中數(shù)據(jù)時，數(shù)據(jù)庫系統(tǒng)要負責隔離操作，以保證各個連接在獲取數(shù)據(jù)時的準確性。

        如果不考慮隔離性，可能會引發(fā)如下問題: ① 臟讀；② 不可重復讀； ③ 幻讀

? 解釋

        臟讀(dirty read) : 當一個事務讀取另一個事務尚未提交的改變(update,insert,delete)時，產(chǎn)生臟讀
        不可重復讀(nonrepeatable read) : 同一查詢在同一事務中多次進行，由于其他提交事務所做的修改或刪除, 每次返回不同的結(jié)果集, 此時發(fā)生不可重復讀。

        幻讀(phantom read) : 同一查詢在同一事務中多次進行，由于其他提交事務所做的插入操作, 每次返回不同的結(jié)果集，此時發(fā)生幻讀。

? 隔離級別

        概念 : MySQL隔離級別定義了事務與事務之間的隔離程度。

? 設置隔離級別

? 查看當前會話隔離級別

        set transaction.sql select @@tx isolation

? 查看系統(tǒng)當前隔離級別

        select @@global.tx isolation

? 設置當前會話隔離級別

        set session transaction isolation level repeatable read

? 設置系統(tǒng)當前隔離級別

        set global transaction isolation leve repeatable read:

? mysql默認的事務隔離級別是 repeatable read一般情況下，沒有特殊要求，沒有必要修改(因為該級別可以滿足絕大部分項目需求)

? 事務 ACID

? 原子性(Atomicity)

        原子性是指事務是一個不可分割的工作單位，事務中的操作要么都發(fā)生，要么都不發(fā)生

? 致性(Consistency)

        事務必須使數(shù)據(jù)庫從一個一致性狀態(tài)變換到另外一個一致性狀態(tài)

? 隔離性 (lsolation)

        事務的隔離性是多個用戶#發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務，不能被其他事務的操作數(shù)據(jù)所干擾，多個并發(fā)事務之間要相互隔離。

? 持久性(Durability)

        持久性是指一個事務一#被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，接下來即使數(shù)據(jù)庫發(fā)生故障也不應該對其有任何影響

5、表類型和存儲引擎

? 基本介紹

• MySQL的表類型由存儲引擎(Storage Engines) 決定，主要包括MylSAM，innoDB、 Memory等。
• MySQL 數(shù)據(jù)表主要支持六種類型，分別是:CSV、 Memory，ARCHIVE，MRG MYISAM、 MYISAM，InnoBDB。
• 這六種又分為兩類, 一類是”事務安全型”(transaction-safe)，比如：InnoDB。其余都屬于第二類，稱為”非事務安全型”(non-transaction-safe)[mysiam 和 memory]。

? 特點

? 細節(jié)

1. MylSAM不支持事務、也不支持外鍵，但其訪問速度快，對事務完整性沒有要求
2. InnoDB存儲引擎提供了具有提交、 回滾和崩潰恢復能力的事務安全。但是比MyISAM存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。
3. MEMORY存儲引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。 每個MEMORY表只實際對應個個磁盤文件。 MEMORY類型的表訪問非常得快, 因為它的數(shù)據(jù)是放在內(nèi)存中的,并且默認使用HASH索引。但是一旦MySQL服務關(guān)閉，表中的數(shù)據(jù)就會丟失掉，表的結(jié)構(gòu)還在

? 如何選擇存儲引擎

• 如果你的應用不需要事務, 處理的只是基本的CRUD操作，那么MyISAM是不二選擇,速度快
• 如果需要支持事務，選擇InnoDB。Memory 存儲引擎就是將數(shù)據(jù)存儲在內(nèi)存中，由于沒有磁盤I/0的等待。速度極快。 但由于是內(nèi)存存儲引擎，所做的任何修改在服務器重啟后都將消失。(經(jīng)典用法，用戶的在線狀態(tài))

? 修改存儲引擎

6、視圖

? 基本介紹

        視圖是一個虛擬表，其內(nèi)容由查詢定義。同真實的表一樣，視圖包含列，其數(shù)據(jù)來自對應的真實表(基表)

? 基本使用

• create view視圖名 as select語句
• alter view視圖名 as select語句 --更新成新的視圖
• SHOW CREATE VIEW 視圖名
• drop view 視圖名1,視圖名2

? 細節(jié)

• 創(chuàng)建視圖后, 到數(shù)據(jù)庫去看, 對應視圖只有一個視圖結(jié)構(gòu)文件(形式 : 視圖名.frm)視圖的數(shù)據(jù)變化會影響到基表，基表的數(shù)據(jù)變化也會影響到視圖[insert update delete]
• 視圖中可以再使用視圖，數(shù)據(jù)仍然來自基表..

? 視圖最佳實踐

1. 安全。一些數(shù)據(jù)表有著重要的信息。有些字段是保密的，不能讓用戶直接看到。這時就可以創(chuàng)建一個視圖，在這張視圖中只保留一部分字段。這樣，用戶就可以查自己需要的字段，不能查看保密的字段。
2. 性能。關(guān)系數(shù)據(jù)庫的數(shù)據(jù)常常會分表存儲，使用外鍵建立這些表的之間關(guān)系。這時，數(shù)據(jù)庫查詢通常會用到連接(UOIN)。這樣做不但麻煩，效率相對也比較低。如果建立一個視圖，將相關(guān)的表和字段組合在一越，就可以避免使用JOIN查詢數(shù)據(jù)。
3. 靈活。如果系統(tǒng)中有一張舊的表，這張表由于設計的問題，即將被廢棄。然而，很多應用都是基于這張表，不易修改。這時就可以建立一張視圖，視圖中的數(shù)據(jù)直接映射到新建的表。這樣，就可以少做很多改動，也達到了升級數(shù)據(jù)表的目的。

7、MySQL 管理

? MySQL 用戶

• host : 允許登錄的“位置”localhost表示該用戶只允許本機登錄，也可以指定ip地址, 比如:192.168.1.100
• user : 用戶名
• authentication string : 密碼，是通過mysql的password0畫數(shù)加密后的密碼。

? 創(chuàng)建用戶

? 刪除用戶

? 用戶修改密碼

? MySQL 權(quán)限

 ? 給用戶授權(quán)

? 回收用戶權(quán)限

? 權(quán)限生效指令

 ? 細節(jié)說明

• 在創(chuàng)建用戶的時候, 如果不指定Host，則為%, %表示表示所有IP都有連接板create user Xxx；
• 你也可以這樣指定 create user 'xxx'@'192.168.1.% 表示XXX用戶在 192.168.1.*的ip可以登錄mysql
• 在刪除用戶的時候, 如果host 不是%，需要明確指定用戶@'host值

總結(jié)

到此這篇關(guān)于MySQL約束和事務知識點歸納的文章就介紹到這了,更多相關(guān)MySQL約束和事務內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論