Mysql 筆記之基礎知識

標籤：mysql

                                       Mysql 筆記之基礎知識

Mysql中的字元的大小寫

      1、SQL關鍵字和內建函數名不區分大小寫，比如SELECT,可寫成select要遵循一種風格。

      2、資料庫、表、索引及視圖的名稱是是否區分大小寫取決於低層的os和FS。

      3、預存程序，儲存函數及事件調度器不區分字元大小寫，但觸發器區分大小寫；

      4、表名不區分大小寫

      5、欄位中字元資料，類型binary、blog、varbinary區分大小寫

      >CREATE DATABASE IF NOT EXISTS tb_test;

      >SHOW GLOBAL VARIABLES LIKE ‘%char%’;

      在mysql用戶端與伺服器端

CHARACTER SET [=] charset

在DDL中CREATE/ALTER/DROP

資料字典就是存取資料中繼資料的資料集，

DROP DATABASE IF EXISTS db_name

表的建立

       約束有  主鍵唯一鍵外鍵（約束）

       索引：特殊的資料結構，用於快速找到資料記錄，鍵可用做索引（是一種特殊的索引），但並非所有索引都是鍵。

 索引類型：B-Tree索引，(B+ Tree，B- Tree），hash（雜湊索引）講一個資料切成n段放到桶中，尋找效能非常好，FullText（全文索引）某一個條目被使用者訪問的比較大，

 >SELECT * FROM tb_test WHERE NAME LIKE ‘tom%’

使用索引優點：

    1、大大減小伺服器需要掃描的資料量；

    2、索引可以幫伺服器盡量避免進行排序及使用暫存資料表；

    3、索引可以將隨機I/O轉換成順序I/O。

        B-Tree索引的使用情境：1、全值匹配：比較操作符=，<=>；2、左首碼匹配：LIKE ‘tom%’

在姓名和性別上做索引，列首碼匹配；3、匹配範圍值；只訪問索引的查詢。覆蓋索引，從索引就可以直接得到最終結果。

雜湊索引適用情境：

雜湊只包含雜湊值和行指標；不是按照索引值順序儲存的因此無法用於排序，不支援部分索引尋找。雜湊索引只支援等值比較查詢，包含=，IN（）,<=>。雜湊值有可能衝突，出現碰撞需二次尋找。

叢集索引和非叢集索引，叢集索引是指索引和資料一起存放，索引循序檔。非聚集所以，索引和資料分開存放的，資料記錄未必順序存放，但索引資料一般順序存放，

建立表：1、手動建立CREATE TABLE tmp1 2、查詢其他表資料來建立表3、複製其它表的表結構

 暫存資料表不能超過16M 有系統管理權限才可以建立，

 >CREATE TABLE tb （name CHAR(30) PRIMARY KEY,gender ENUM(‘m’,’f’)）;

 >CREATE TABLE tb (name CHAR(30),gender ENUM(‘m’,’f’),PRIMARY KEY (name));這個可以建立多個主鍵，後接ENGINE ‘MYISAM’指明儲存引擎。

 Table_option  ENGINE=’innoDB’

 >SHOW GLOBAL VARIABLE LIKE ‘%storage%’;查看儲存引擎

 >SELECT @@SESSION.default_storage_engine;

 AUTO_INCREMENT[=]value 自動成長

 COMMANT[=]    注釋

 >SHOW TABLE STATUS LIKE ‘tb2’\G

 >CREATE TABLE tb_test2(name CHAR(20),gender ENUM(‘m’,’f’));//建立表tb_test2

 >INSERT INTO tb_test2 VALUES(‘tom’,’m’),(‘jerry’,’f’);//插入資訊

 >CREATE TABLE tb_test3 SELECT * FROM tb_test2;//查詢表2資訊建立表3。（第二種建立方法）

 >CREATE TABLE tb_test4 LIKE tb_test2;//基於表2建立表4。（第三種建立方法）

刪除表：DROP

 ALTER [ONLINE | OUTLINE] TABLE db_name

 >ALTER TABLE tb_test5 ADD id INT UNSIGND NOT NULL AUTO_INCREMENT PRIMARY KEY;

 >ALTER TABLE tb_test5 DROP PRIMARY KEY;  //刪除索引

 >ALTER TABLE tb_test5 MODIFY name CHAR(30) NOT NULL; 修改表5中的name資訊。

 >ALTER TABLE tb_test5 CHANGE name Name CHAR(30) NOT NULL; 修改名

        >ALTER TABLE tb_test5 ADD INDEX (Name);添加name欄位上索引。刪除的話ADD 換成DROP 就行了。

        >ALTER TABLE tb_test5 RENAME TO students;//修改表名為students    //需謹慎

        >RENAME TABLE students TO tb_test5;//再改過來

修改字元集

CONVERT TO CHARACTER SET charset_name

InnoDB表的資料表空間管理

>DISCARD TABLESPACE //刪除資料表空間

>IMPORT TABLESPACE //匯入新的資料表空間

不可以隨便修改儲存引擎

ALTER TABLE 可實現添加刪除索引

CREATE  INDEX  index_name //也可以建立索引

SHOW INDEXS {FROM | IN} tb_name {FROM | IN } db_name;//查看索引

DROP {ONLINE | OFFLINE}INDEX index_name ON tb_name;//刪除索引

索引建立的基本法則，基於搜尋鍵來建立索引，SELECT 的WHERE 子句的查詢條件中的欄位。

索引使用原則

        1、要使用獨立的列，索引列不是運算式的一部分

          SELECT name FROM students WHERE Age> 20;

        2、首碼索引

               索引選擇性：單獨索引項目與資料記錄的總數的比值，索引選擇性越高查詢效率就越高反之則就越低。

       3、多列索引

               查詢條件，如果要用AND串連儘可能使用複合式索引，如果是ALL則儘可能使用多個簡單索引。

       4、合適的次序，將選擇性最高的列寫在最左側。

       5、聚簇索引：

             好處  資料訪問塊

              缺點   跟新局促索引的代價很高

        6、覆蓋索引

 

Mysql 筆記之基礎知識