mysql學習筆記

標籤：

-----------------初學mysql時整理，隨時更新--------------


/* 資料操作 */ ------------------

-- 增    INSERT [INTO] 表名 [(欄位列表)] VALUES (值列表)[, (值列表), ...]        -- 如果要插入的值列表包含所有欄位並且順序一致，則可以省略欄位列表。        -- 可同時插入多條資料記錄！        REPLACE 與 INSERT 完全一樣，可互換。    
    INSERT [INTO] 表名 SET 欄位名=值[, 欄位名=值, ...]

-- 查    SELECT 欄位列表 FROM 表名[ 其他子句]        -- 可來自多個表的多個欄位        -- 其他子句可以不使用        -- 欄位列表可以用*代替，表示所有欄位
 
-- 刪    DELETE FROM 表名[ 刪除條件子句]        沒有條件子句，則會刪除全部
  
-- 改    UPDATE 表名 SET 欄位名=新值[, 欄位名=新值] [更新條件]

/* 字元集編碼 */ ------------------
-- MySQL、資料庫、表、欄位均可設定編碼
-- 資料編碼與用戶端編碼不需一致
SHOW VARIABLES LIKE ‘character_set_%‘    -- 查看所有字元集編碼項    
    character_set_client        用戶端向伺服器發送資料時使用的編碼    character_set_results        伺服器端將結果返回給用戶端所使用的編碼    character_set_connection    串連層編碼
    
SET 變數名 = 變數值    
 set character_set_client = gbk;
 set character_set_results = gbk;
 set character_set_connection = gbk;
SET NAMES GBK; -- 相當於完成以上三個設定
 
-- 校對集    
    校對集用以排序 SHOW CHARACTER SET [LIKE ‘pattern‘]/SHOW CHARSET [LIKE ‘pattern‘]    查看所有字元集 SHOW COLLATION [LIKE ‘pattern‘]        查看所有校對集 charset 字元集編碼        設定字元集編碼 collate 校對集編碼        設定校對集編碼

/* 資料類型（列類型） */ ------------

1. 數實值型別

-- a. 整型 ----------    
類型        位元組     範圍（有符號位）    
tinyint    1位元組    -128 ~ 127        無符號位：0 ~ 255
smallint   2位元組    -32768 ~ 32767
mediumint  3位元組    -8388608 ~ 8388607
int        4位元組    
bigint     8位元組    
        
 int(M)    M表示總位元    

   - 預設存在符號位，unsigned 屬性修改    
   - 顯示寬度，如果某個數不夠定義欄位時設定的位元，則前面以0補填，zerofill 屬性修改       例：int(5)    插入一個數‘123‘，補填後為‘00123‘   - 在滿足要求的情況下，越小越好。    
   - 1表示bool值真，0表示bool值假。MySQL沒有布爾類型，通過整型0和1表示。常用tinyint(1)表示布爾型。

-- b. 浮點型 ----------    
類型            位元組        範圍    
float(單精確度)   4位元組    
double(雙精確度)  8位元組  浮點型既支援符號位 unsigned 屬性，也支援顯示寬度 zerofill 屬性。      不同於整型，前後均會補填0.  定義浮點型時，需指定總位元和小數位元。
   float(M, D)     double(M, D)     M表示總位元，D表示小數位元。     M和D的大小會決定浮點數的範圍。不同於整型的固定範圍。     M既表示總位元（不包括小數點和加號或減號），也表示顯示寬度（所有顯示符號均包括）。     支援科學計數法表示。     浮點數表示近似值。

-- c. 定點數 ---------- decimal    -- 可變長度 decimal(M, D)    M也表示總位元，D表示小數位元。 儲存一個精確的數值，不會發生資料的改變，不同於浮點數的四捨五入。 將浮點數轉換為字串來儲存，每9位元字儲存為4個位元組。

2. 字串類型

-- a. char, varchar ---------- char  定長字串，速度快，但浪費空間    
 varchar  變長字串，速度慢，但節省空間的  M表示能儲存的最大長度，此長度是字元數，非位元組數。  不同的編碼，所佔用的空間不同。    
  char,最多255個字元，與編碼無關.
  varchar,最多65535字元，與編碼有關。  一條有效記錄最大不能超過65535個位元組。    utf8 最大為21844個字元，gbk 最大為32766個字元，latin1 最大為65532個字元    

  varchar 是變長的，需要利用儲存空間儲存 varchar 的長度，如果資料小於255個位元組，則採用一個位元組來儲存長度，反之需要兩個位元組來儲存。    
  varchar 的最大有效長度由最大行大小和使用的字元集確定。    最大有效長度是65532位元組，因為在varchar存字串時，第一個位元組是空的，不存在任何資料，然後還需兩個位元組來存放字串的長度，所以有效長度是64432-1-2=65532位元組。    例：若一個表定義為 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 問N的最大值是多少？ 答：(65535-1-2-4-30*3)/3

-- b. blob, text ----------    
 blob 二進位字串（位元組字串）    tinyblob, blob, mediumblob, longblob    
 text 非二進位字串（字元字串）    tinytext, text, mediumtext, longtext    
 text 在定義時，不需要定義長度，也不會計算總長度。    
 text 類型在定義時，不可給default值
 
-- c. binary, varbinary ----------    
    類似於char和varchar，用於儲存二進位字串，也就是儲存位元組字串而非字元字串。    
    char, varchar, text 對應 binary, varbinary, blob.

3. 日期時間類型    一般用整型儲存時間戳記，因為PHP可以很方便的將時間戳記進行格式化。    

datetime    8位元組   日期及時間  1000-01-01 00:00:00 到 9999-12-31 23:59:59
date        3位元組   日期        1000-01-01 到 9999-12-31
timestamp    4位元組  時間戳記      19700101000000 到 2038-01-19 03:14:07
time        3位元組   時間        -838:59:59 到 838:59:59
year        1位元組   年份        1901 - 2155

datetime    “YYYY-MM-DD hh:mm:ss”
timestamp    “YY-MM-DD hh:mm:ss”            “YYYYMMDDhhmmss”            “YYMMDDhhmmss”            YYYYMMDDhhmmss            YYMMDDhhmmssdate        “YYYY-MM-DD”            “YY-MM-DD”            “YYYYMMDD”            “YYMMDD”            YYYYMMDD            YYMMDDtime        “hh:mm:ss”            “hhmmss”            hhmmss
year        “YYYY”            “YY”            YYYY            YY
        
4. 枚舉和集合
-- 枚舉(enum) ----------

enum(val1, val2, val3...)    在已知的值中進行單選。最大數量為65535.    枚舉值在儲存時，以2個位元組的整型(smallint)儲存。每個枚舉值，按儲存的位置順序，從1開始逐一遞增。    表現為字串類型，儲存卻是整型。    NULL值的索引是NULL。    Null 字元串錯誤值的索引值是0。

-- 集合（set） ----------
 
set(val1, val2, val3...)

    create table tab ( gender set(‘男‘, ‘女‘, ‘無‘) );    
    insert into tab values (‘男, 女‘);    最多可以有64個不同的成員。以bigint儲存，共8個位元組。採取位元運算的形式。    當建立表時，SET成員值的尾部空格將自動被刪除。

/* 選擇類型 */
-- PHP角度
1. 功能滿足
2. 儲存空間盡量小，處理效率更高
3. 考慮相容問題

-- IP儲存 ----------
 
1. 只需儲存，可用字串

2. 如果需計算，尋找等，可儲存為4個位元組的無符號int，即unsigned    
  1) PHP函數轉換     ip2long可轉換為整型，但會出現攜帶符號問題。需格式化為無符號的整型。     利用sprintf函數格式化字串     sprintf("%u", ip2long(‘192.168.3.134‘));     然後用long2ip將整型轉回IP字串
  2) MySQL函數轉換(無符號整型，UNSIGNED)     INET_ATON(‘127.0.0.1‘) 將IP轉為整型     INET_NTOA(2130706433) 將整型轉為IP

mysql學習筆記