MySQL - 聯結、組合查詢、全文本搜尋、表操作

進階聯結

    使用表别名 AS

    自聯結

        查詢DTNTR的供應商的生産的其他物品

        SELECT prod_id, prod_name

        FROM products

        WHERE vend_id = (SELECT vend_id

                         FROM products

                         WHERE prod_id = 'DTNTR')

        SELECT p1.prod_id, p1.prod_name

        FROM products AS p1, products AS p2

        WHERE p1.vend_id = p2.vend_id

        AND p2.prod_id = 'DTNTR';

    自然聯結

        對表聯結，應該至少有一個列出現在不止一個表中(被聯結的列）

        自然聯結排除相同的列多次出現的問題，使每個列隻傳回一次

        自己通過通配符*實作，其他列明确列出

    外部聯結

        外部聯結包含了在相關表中沒有關聯行的行

        用RIGHT或LEFT指定包括其他所有行的表

        RIGHT指包含OUTER JOIN右邊的表

        LEFT指包含OUTER JOIN左邊的表

        檢索有訂單的客戶，用内連接配接。

        檢索所有客戶，包括哪些沒有訂單的客戶，用外連接配接

            SELECT customers.cust_id, orders.order_num

            FROM customers LEFT OUTER JOIN orders

            ON customers.cust_id = orders.cust_id;

    帶聚集函數的聯結

        檢索所有客戶及每個客戶下的訂單數(想包含那些沒有訂單的客戶用LEFT JOIN)

        SELECT customers.cust_name,

               customers.cust_id,

               COUNT(orders.order_num) AS num_ord

        FROM customers INNER JOIN orders

        ON customers.cust_id = orders.cust_id

        GROUP BY customers.cust_id;

組合查詢

    使用情況：

        在單個查詢中從不同的表傳回類似結構的資料

        對單個表執行多個查詢，按單個查詢傳回資料

    建立：UNION(放在2或2個以上SELECT語句之間，MySQL執行兩條SELECT語句，并把輸出組合成單個查詢結果集)

    UNION中的每個查詢必須包含相同的列、表達式或聚集函數

    列資料類型必須相容

    UNION從查詢結果集中自動去除重複的行（UNION ALL不去重）

    多個SELECT語句最後使用ORDER BY

全文本搜尋

    MyISAM 支援 解決LIKE、正規表達式的限制

    InnoDB 不支援

    FULLTEXT(列) mysql自動維護該索引，在增删改時索引自動更新

    不要在導入資料時使用FULLTEXT，更新索引慢，資料導入之後修改表

    Match(列) Against(要搜尋的表達式) 

    不區分大小寫，除非用BINARY

        SELECT note_text

        FROM productnotes

        WHERE Match(note_text) Against('rabbit');

插入資料庫

    INSERT 插入完整的行

        INSERT INTO customers(cust_name, cust_address, cust_city, cust_state) 

        VALUES('XXX','XX','XXXX','X');

    插入多行

        INSERT INTO customers(cust_name, cust_address, cust_city, cust_state) 

        VALUES('XXX','XX','XXXX','X')，

                ('XXX','XX','XXXX','X');

    插入檢索出的資料

        INSERT INTO customers(cust_name, cust_address, cust_city, cust_state) 

        SELECT cust_name, cust_address, cust_city, cust_state

        FROM custnew;

    提高整體性能

        如果資料檢索是最重要的，可以通過在INSERT LOW_PRIORITY INTO訓示mysql降低insert語句的優先級

        插入多行，比用多條insert處理插入更快

删除和更新資料

    更新表中特定行\更新表中所有行

        UPDATE customers

        SET cust_email = 'xxxx'

        WHERE cust_id = 10200;

        UPDATE customers

        SET cust_email = 'xxxx',

            cust_address = 'xx'

        WHERE cust_id = 10200;

        UPDATE customers

        SET cust_email = NULL

        WHERE cust_id = 10200;

    删除特定的行\删除所有行

        DELETE FROM customers

        WHERE cust_id = 10200;

    更快的删除表中所有行

        用TRUNCATE TABLE：删除原來的表并重新建立一個表

建立和操縱表

    建立表 CREATE TABLE 

    僅在表不存在時建立表 IF NOT EXISTS

    NULL 沒有值，不是空串，空串是一個有效的值

    主鍵 PRIMARY KEY(列1，列2)，主鍵不允許NULL，允許NULL的列不能作為唯一辨別

    AUTO_INCREMENT

    DEFAULT 預設值

    ENGINE 引擎類型

        InnoDB 可靠的事務處理引擎，不支援全文本搜尋

        MEMORY 資料存儲在記憶體（不是磁盤），速度很快，适合于臨時表

        MyISAM 性能極高，支援全文本搜尋，不支援事務處理

        外鍵不能跨引擎，即使用一個引擎的表不能引用具有使用不同引擎的表的外鍵

    更新表 ALTER TABLE

    理想狀态下 當表中存儲資料以後，表就不應該在被更新

    添加列

        ALTER TABLE vendors

        ADD vend_phone CHAR(20);

        ALTER TABLE vendors

        DROP COLUME vend_phone;

    定義外鍵

        ALTER TABLE orders

        ADD CONSTRAINT fk_orders_customers FOREIGN KEY(prod_id)

        REFERENCES products (prod_id);

    複雜表結構的更改一般需要手動删除

        用新的列布局建立一個新表

        使用INSERT SELECT從舊表複制資料到新表

        檢驗包含所需資料的新表

        重命名酒标

        用舊表原有名字命名新表

        根據需要，重新建立觸發器、存儲過程、索引和外鍵

    删除表 DROP TABLE

    重命名表 RENAME TABLE XX TO XXX