MySQL資料類型和限制條件

　一.資料庫操作資料的存儲引擎

INNODB:支援事務 行鎖  外鍵 查詢速度比MYSiam慢  但是保證了資料的安全性 5.1 版本之後
MYSIAM:老版本用 5.1版本之前 搜尋速度快 不支援事務 沒有外鍵

MEMERY:不需要存儲資料表隻在記憶體緩存 redis 用作大批量資料的記憶體緩存

應用Redis實作資料的讀寫，同時利用隊列處理器定時将資料寫入mysql。同時要注意避免沖突，
在redis啟動時去mysql讀取所有表鍵值存入redis中，往redis寫資料時，對redis主鍵自增并進行讀取，
若mysql更新失敗，則需要及時清除緩存及同步redis主鍵。這樣處理，主要是實時讀寫redis，
而mysql資料則通過隊列異步處理，
緩解mysql壓力，不過這種方法應用場景主要基于高并發，
而且redis的高可用叢集架構相對更複雜，對于資料的儲存不是永久的,
一些資料要求不嚴格的大公司會用,一般不是很推薦。

blackhole:任何寫入到此引擎的資料均會被丢棄掉，

　　不做實際存儲；

Select語句的内容永遠是空過濾資料記錄日志

　資料結構差別:

Innodb
            目前5.1之後MySQL版本預設的存儲引擎
            支援事務,行鎖,外鍵
            由于上面的支援 資料更安全
            
            建表的時候innodb會産生兩個檔案
                一個是表結構檔案
                一個是存儲資料檔案
        MyIsam
            5.1版本之前的MySQL的預設存儲引擎
            查詢速度較于Innodb要快
            建表的時候會産生三個檔案
                一個是表結構檔案
                一個是索引檔案
                    索引你先把它了解成是書的目錄,能夠幫助你更快的查詢資料
                一個是存儲資料檔案
        memory
            将資料存放于記憶體中 
            建表的時候都僅僅隻有一個表結構檔案
        blackhole
            任何寫入的資料都會消失
            建表的時候都僅僅隻有一個表結構檔案

MySQL資料類型和限制條件

查詢的内部的執行圖

MySQL資料類型和限制條件

　二.資料類型

　　SQL文法:

一.建立表的完整文法
create table 表名(
字段名1 類型[(寬度) 限制條件],
字段名2 類型[(寬度) 限制條件],
字段名3 類型[(寬度) 限制條件] 
);

　注意:

　　　　1.在同一張表中字段名不能相同

　　　　2.字段名和類型都是必須要的而寬度和限制條件都是可以選擇的

　　　　3.最後一個字段不能加逗号

　補充:

　　　　寬度指的是對存儲資料的限制

mysql> create  table user(age int not null);
        ERROR 1050 (42S01): Table 'user' already exists
        # 如果字段已經建立了 在建立其他字段是是不可以的
        # 建立的sql 語句是:alter table 表名(user) add(age int not null);

　2.限制調條件初識>>null與 not null

往表中插入資料的時候  可以指定字段進行插入 不需要全部都插
         insert into t17(name,id) values('egon',2);
          
         如:
            mysql> insert into user values(1,'jasom',null);
            ERROR 1048 (23000): Column 'age' cannot be null
            之前在表設計的時候我們設計的是不能為空 
                alter table user(age int not null);

　3.總結:

　　　　1.類型與限制條件的差別

　　　　2.類型:限制字段必須以什麼樣的資料類型進行存儲

　　　　3.限制條件:限制條件是在類型之外添加一種額外的限制

　基本資料類型

　　 - 整型

　　1.分類 TINYINT SMALLINT MEDIUMINT INT BIGINT

　 2.作用:存儲年齡,等級,id,各種号碼

類型存儲範圍：參考圖檔

MySQL資料類型和限制條件

　(1)整型:

驗證整形字段有無符号及範圍
        mysql> insert into t2 values(127);
        mysql> insert into t2 values(-128);
        # 有符号整形範圍:(-128,127) 
           +------+
           | x    |
           +------+
           |  127 |
           | -128 |
           +------+

　　# 無符号整形範圍

mysql> create table t3 (x tinyint unsigned);


 mysql> insert into t3 values(0),(255);

　　# 無符号int 類型的最大位數

mysql> insert into t4 values(12345678901); 報錯
        mysql> insert into t4 values(1234567890);
        範圍(0, 4294967295)

　　3.疑問:類型後面的寬度能否改變字段存儲的大小限制

mysql> create table t5(x int(8));


mysql> insert into t5 values (1234567890);OK

　　4.# 顯示時，不夠8位用0填充，如果超出8位則正常顯示

mysql> create table t6(x int(8) unsigned zero fill);
        mysql> insert into t6 values(1234567890);
        # 超出 隻要在10個字範圍内 都是正常顯示
        # 不夠的話用零填充
        mysql> insert into t6 values(1234);
        mysql> select*from t6;
        +------------+
        | x          |
        +------------+
        |  429496729 |
        | 1234567890 |
        |   00001234 |
        +------------+

　　注意:

　　對于整型來說，資料類型後的寬度并不是存儲限制，

　　而是顯示限制，是以在建立表時，如果字段采用的是整型類型，完全無需指定顯示寬度，

　　預設的顯示寬度，足夠顯示完整當初存放的資料.

　- 浮點型

　　分類:float double decimal 精準度

　　應用場景: 身高.體重.薪資

　　字段限制特點(255,30)前一位表示所有的位數，後一位表示小數個數

　　三者最大整數位和小數位對比

#存儲限制
        float(255,30)
        double(255,30)
        decimal(65,30)

　　精準度的驗證

reate table t7(x float(255,30));

create table t8(x double(255,30));

create table t9(x decimal(65,30));

　insert into t7 values(1.111111111111111111111111111111);

　insert into t8 values(1.111111111111111111111111111111);

insert into t9 values(1.111111111111111111111111111111);

　　結果:

mysql> select * from t7;
        +----------------------------------+
        | x                                |
        +----------------------------------+
        | 1.111111164093017600000000000000 |
        +----------------------------------+
        
       mysql> select * from t8;
        +----------------------------------+
        | x                                |
        +----------------------------------+
        | 1.111111111111111200000000000000 |
        +----------------------------------+
        
        mysql> select  * from t9;
        +----------------------------------+
        | x                                |
        +----------------------------------+
        | 1.111111111111111111111111111111 |
        +----------------------------------+

總結:float <double< declmal 精準度越來越準确 
　　一般比較精确應用于航天 decimal 還有些直接用字元串存取
　　存多少就取多少

　　補充:嚴格模式

我們剛剛在上面設定了char,tinyint,存儲資料時超過它們的最大存儲長度，
發現資料也能正常存儲進去，
隻是mysql幫我們自動截取了最大長度。但在實際情況下，
我們應該盡量減少資料庫的操作，緩解資料庫的壓力，
讓它僅僅隻管理資料即可，這樣的情況下就需要設定安全模式

``python
        show variables like "%mode%";  # 檢視資料庫配置中變量名包含mode的配置參數
        # 修改安全模式
        set session # 隻在目前操作界面有效
        set global  # 全局有效

        set global sql_mode ='STRICT_TRANS_TABLES'
        # 修改完之後退出目前用戶端重新登陸即可
        ```

- 字元類型(char與varchar)

　　　　類型:

　　　　char(定長)

　　　　varchar(變長)

create table t10(name char(4))  # 超出四個字元報錯，不夠四個字元空格補全

create table t11(name varchar(4))  # 超出四個字元報錯，不夠四個有幾個就存幾個

# 驗證存儲限制
            insert into t12 values('hello');
            insert into t13 values('hello');
            # 驗證存儲長度
            insert into t12 values('a'); #'a    '
            insert into t13 values('a'); #'a'
            select * from t12
            select * from t13  # 無法檢視真正的結果

            select char_length(name) from t12
            select char_length(name) from t13  # 仍然無法檢視到真正的結果

            """首先應該肯定的是在硬碟上存的絕對是真正的資料，但顯示的時候mysql會自動将末尾的空格取掉"""
            # 如果不想讓mysql幫你做自動去除末尾空格的操作，需要再添加一個模式
            set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
            # 退出用戶端重新登陸
            select char_length(x) from t12; #4
            select char_length(y) from t13; #1

            # 針對char類型，mysql在存儲時會将資料用空格補全存放到硬碟中。
　　　　　　　　但是會在讀出結果的時候自動取掉末尾的空格

char

char
                # 優點:存取速度快
                直接按照固定的長度讀取,比較友善
                # 缺點:浪費空間
                name char(4) 
                Alice egon tank jaso n

varchar

varchar  
                name varchar(4)
                varchar變長
                
                1.節省空間
                2.存取速度慢(較于char比較慢)
                    1bytes+egon 1bytes+alex 1bytes+lxx  1bytes+jxx  1bytes+txx
                    存的時候 需要給資料講一個記錄長度的報頭
                    取的時候 需要先讀取報頭才能讀取真實資料

- 日期類型

　五.時間類型:

date:年 月 日 2019-8-19
           time:時 分 秒
           Datetime:年 月 日 時 分 秒 2019-8-19 11:30:10
           Year; 2019

實列:

　 insert into student values(1,'egon','2019','2019-05-    
    09','11:11:00','2019-         11-11 11:11:11');

- 枚舉與集合

　六.枚舉與集合類型

　　 1 .枚舉(enum) 限制某個字段能夠存儲的資料内容

　　　 enum 多選一

列子:
        mysql> create table techer(id int,
        -> name char(32),
        -> gender enum('male','female','other'));
        Query OK, 0 rows affected (0.03 sec)

　　mysql> insert into techer values(1,'koko','male');

mysql> select * from techer;
        +------+------+--------+
        | id   | name | gender |
        +------+------+--------+
        |    1 | koko | male   |
        +------+------+--------+

　　mysql> insert into techer values(1,'yyy',female');

mysql> select *from techer;
        +------+------+--------+
        | id   | name | gender |
        +------+------+--------+
        |    1 | koko | male   |
        |    2 | yyy  | female |
        +------+------+--------+

　　2.集合(set) 限制某個字段能夠存儲的資料内容

　　set 可以單選可以多選

1.mysql> create table student1(id int primary key auto_increment,
            ->  name char(32),
            ->  gender enum('male','female','other'),
            ->  hobby set('sing','dancing','read','play'));
                Query OK, 0 rows affected (0.03 sec)

　　插入:insert into student1(name,gender,hobby) values('koko','male','read,dancing,play');

　　Query OK, 1 row affected (0.00 sec)

mysql> select * from student1;;
                +----+------+--------+-------------------+
                | id | name | gender | hobby             |
                +----+------+--------+-------------------+
                |  1 | koko | male   | dancing,read,play |
                +----+------+--------+-------------------+
                1 row in set (0.00 sec)

　三.限制條件

七.限制條件

1.PRIMARY KEY (PK)

1.PRIMARY KEY (PK)    辨別該字段為該表的主鍵，可以唯一的辨別記錄
            FOREIGN KEY (FK)    辨別該字段為該表的外鍵
            NOT NULL    辨別該字段不能為空
            UNIQUE KEY (UK)    辨別該字段的值是唯一的
            AUTO_INCREMENT    辨別該字段的值自動增長（整數類型，而且為主鍵）
            DEFAULT    為該字段設定預設值

            UNSIGNED 無符号
            ZEROFILL 使用0填充

2.not null+default

create table user(
            id int,
            name char(16)
                );
            insert into user values(1,null)  # 可以修改

            alter table user modify name char(16) not null;
            insert into user(name,id) values(null,2);  # 報錯 插入資料可以在表名後面指定插入資料對應的字段

            create table student(
    id int,
  name char(16) not null,
  gender enum('male','female','others') default 'male'
            )
            insert into student(id,name) values(1,'jason')  # 成功

3.unique

```mysql
            # 單列唯一
            create table user1(
                id int unique, 
              name char(16)
            );

insert into user1 values(1,'jason'),(1,'egon') # 報錯

insert into user1 values(1,'jason'),(2,'egon') # 成功

聯合唯一
            create table server(
                id int,
              ip char(16),
              port int,
              unique(ip,port)
            )

insert into server values(1,'127.0.0.1',8080);

insert into server values(2,'127.0.0.1',8080); # 報錯

insert into server values(1,'127.0.0.1',8081);

#### primary key+auto_increment

```mysql
            # 單從限制角度來說primary key就等價于not null unique
            create table t11(id int primary key);
            desc t11;
            insert into t11 values(1),(1);  # 報錯
            insert into t11 values(1),(2);

            # 除了限制之外，它還是innodb引擎組織資料的依據，提升查詢效率

"""

強調:

1.一張表中必須有且隻有一個主鍵，如果你沒有設定主鍵，那麼會從上到下搜尋直到遇到一個非空且唯一的字段自動将其設定為主鍵

"""

create table t12(
              id int,
              name char(16),
              age int not null unique,
              addr char(16) not null unique
              )engine=innodb;
              desc t12;

"""

2.如果表裡面沒有指定任何的可以設定為主鍵的字段，那麼innodb會采用自己預設的一個隐藏字段作為主鍵，隐藏意味着你在查詢的時候無法根據這個主鍵字段加速查詢了

索引：類似于書的目錄，沒有主鍵就相當于一頁一頁翻着查

3.一張表中通常都應該有一個id字段，并且通常将改id字段作成主鍵

"""
            # 聯合主鍵：多個字段聯合起來作為表的一個主鍵，本質還是一個主鍵
            create table t18(
                ip char(16),
              port int,
              primary key(ip,port)
            );
            desc t18;

# 主鍵id作為資料的編号，每次最好能自動遞增

create table t13(
                id int primary key auto_increment,
              name char(16)
            );

insert into t13('jason'),('jason'),('jason'); # id字段自動從1開始遞增

# 注意：auto_increment通常都是加在主鍵上，并且隻能給設定為key的字段加

```

補充:

delete from tb1;

強調：上面的這條指令确實可以将表裡的所有記錄都删掉，但不會将id重置為0，

是以收該條指令根本不是用來清空表的，delete是用來删除表中某一些符合條件的記錄

delete from tb1 where id > 10;

如果要清空表，使用truncate tb1；

作用：将整張表重置,id重新從0開始記錄

轉載于:https://www.cnblogs.com/mofujin/p/11380347.html

MySQL資料類型和限制條件

繼續閱讀

2021年危險化學品經營機關安全管理人員考試題庫及危險化學品經營機關安全管理人員考試技巧

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

PAT 1089 Insert or Merge[難]

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

無人機--飛控科普

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述