【2020Python修煉記】MySQL之表相關操作-有解無憂

【目錄】

一存盤引擎介紹

二表介紹

三創建表

四查看表

五修改表

六復制表

七洗掉表

八表資料型別

1、介紹

2、數值型別

3、日期型別

4、字串型別

5、列舉型別與集合型別

九表完整性約束

1、介紹

2、not null與default

3、 unique

4、primary key

5、auto_increment

6、foreign key

十表的關系

一、存盤引擎介紹

1、什么是存盤引擎

日常生活中檔案格式有很多中，并且針對不同的檔案格式會有對應不同存盤方式和處理機制(txt,pdf,word,mp4...)

針對不同的資料應該有對應的不同的處理機制來存盤

存盤引擎就是不同的處理機制.

資料庫中的表也應該有不同的型別，表的型別不同，會對應mysql不同的存取機制，表型別又稱為存盤引擎，

存盤引擎就是如何存盤資料、如何為存盤的資料建立索引和如何更新、查詢資料等技術的實作方
法，因為在關系型資料庫中資料的存盤是以表的形式存盤的，所以存盤引擎也可以稱為表型別（即存盤和
操作此表的型別）

在Oracle 和SQL Server等資料庫中只有一種存盤引擎，所有資料存盤管理機制都是一樣的，
而MySql資料庫提供了多種存盤引擎，用戶可以根據不同的需求為資料表選擇不同的存盤引擎，用戶也可以根據
自己的需要撰寫自己的存盤引擎，

什么是存盤引擎

2、MySQL主要存盤引擎

Innodb

是MySQL5.5版本及之后默認的存盤引擎

存盤資料更加的安全
myisam

是MySQL5.5版本之前默認的存盤引擎

速度要比Innodb更快但是我們更加注重的是資料的安全
memory

記憶體引擎(資料全部存放在記憶體中) 斷電資料丟失

blackhole

無論存什么，都立刻消失(黑洞)

show engines\G   #查看所有支持的存盤引擎
show variables like 'storage_engine%';   #查看正在使用的存盤引擎 （MySQL 一般是使用 InnoDB）

# ===============>
#InnoDB 存盤引擎
支持事務,其設計目標主要面向聯機事務處理(OLTP)的應用，其特點是行鎖設計、支持外鍵,
并支持類似 Oracle 的非鎖定讀,即默認讀取操作不會產生鎖，
從 MySQL 5.5.8 版本開始是默認的存盤引擎，
InnoDB 存盤引擎將資料放在一個邏輯的表空間中,這個表空間就像黑盒一樣由 InnoDB 存盤引擎自身來管理，
從 MySQL 4.1(包括 4.1)版本開始,可以將每個 InnoDB 存盤引擎的 表單獨存放到一個獨立的 ibd 檔案中，
此外,InnoDB 存盤引擎支持將裸設備(row disk)用 于建立其表空間，
InnoDB 通過使用多版本并發控制(MVCC)來獲得高并發性,并且實作了 SQL 標準 的 4 種隔離級別,默認為 REPEATABLE 級別,
同時使用一種稱為 netx-key locking 的策略來 避免幻讀(phantom)現象的產生，
除此之外,InnoDB 存盤引擎還提供了插入緩沖(insert buffer)、二次寫(double write)、自適應哈希索引(adaptive hash index)、預讀(read ahead) 等高性能和高可用的功能，
對于表中資料的存盤,InnoDB 存盤引擎采用了聚集(clustered)的方式,每張表都是按 主鍵的順序進行存盤的,
如果沒有顯式地在表定義時指定主鍵,InnoDB 存盤引擎會為每一 行生成一個 6 位元組的 ROWID,并以此作為主鍵，
InnoDB 存盤引擎是 MySQL 資料庫最為常用的一種引擎,Facebook、Google、Yahoo 等 公司的成功應用已經證明了
InnoDB 存盤引擎具備高可用性、高性能以及高可擴展性，對其 底層實作的掌握和理解也需要時間和技術的積累，
如果想深入了解 InnoDB 存盤引擎的作業 原理、實作和應用,可以參考《MySQL 技術內幕:InnoDB 存盤引擎》一書，

#MyISAM 存盤引擎
不支持事務、表鎖設計、支持全文索引,主要面向一些 OLAP 數 據庫應用,在 MySQL 5.5.8 版本之前是默認的存盤引擎(除 Windows 版本外)，
資料庫系統 與檔案系統一個很大的不同在于對事務的支持,MyISAM 存盤引擎是不支持事務的，究其根 本,這也并不難理解，
用戶在所有的應用中是否都需要事務呢?在資料倉庫中,如果沒有 ETL 這些操作,只是簡單地通過報表查詢還需要事務的支持嗎?
此外,MyISAM 存盤引擎的 另一個與眾不同的地方是,它的緩沖池只快取(cache)索引檔案,而不快取資料檔案,這與 大多數的資料庫都不相同，

#NDB 存盤引擎
2003 年,MySQL AB 公司從 Sony Ericsson 公司收購了 NDB 存盤引擎，
NDB 存盤引擎是一個集群存盤引擎,類似于 Oracle 的 RAC 集群,不過與 Oracle RAC 的 share everything 結構不同的是,
其結構是 share nothing 的集群架構,因此能提供更高級別的 高可用性，
NDB 存盤引擎的特點是資料全部放在記憶體中(從 5.1 版本開始,可以將非索引數 據放在磁盤上),
因此主鍵查找(primary key lookups)的速度極快,并且能夠在線添加 NDB 資料存盤節點(data node)以便線性地提高資料庫性能，
由此可見,NDB 存盤引擎是高可用、 高性能、高可擴展性的資料庫集群系統,其面向的也是 OLTP 的資料庫應用型別，

#Memory 存盤引擎
正如其名,Memory 存盤引擎中的資料都存放在記憶體中,資料庫重 啟或發生崩潰,表中的資料都將消失，（在重啟mysql或者重啟機器后，表內資料清空）
它非常適合于存盤 OLTP 資料庫應用中臨時資料的臨時表,也可以作為 OLAP 資料庫應用中資料倉庫的維度表，
Memory 存盤引擎默認使用哈希 索引,而不是通常熟悉的 B+ 樹索引，

#Infobright 存盤引擎
第三方的存盤引擎，其特點是存盤是按照列而非行的,因此非常 適合 OLAP 的資料庫應用，
其官方網站是 http://www.infobright.org/,上面有不少成功的資料 倉庫案例可供分析，

#NTSE 存盤引擎
網易公司開發的面向其內部使用的存盤引擎，目前的版本不支持事務,
但提供壓縮、行級快取等特性,不久的將來會實作面向記憶體的事務支持，

#BLACKHOLE
黑洞存盤引擎，可以應用于主備復制中的分發主庫，
往表內插入任何資料，都相當于丟入黑洞，表內永遠不存記錄，


MySQL 資料庫還有很多其他存盤引擎,上述只是列舉了最為常用的一些引擎，
如果 你喜歡,完全可以撰寫專屬于自己的引擎,這就是開源賦予我們的能力,也是開源的魅 力所在，

mysql支持的存盤引擎-詳解

3、SQL陳述句查看/使用存盤引擎

# 1 查看所有的存盤引擎

show engines\G; #查看所有支持的存盤引擎
show variables like 'storage_engine%'; #查看正在使用的存盤引擎（MySQL 一般是使用 InnoDB）
# 2 不同的存盤引擎在存盤表的時候異同點
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;

# 存資料
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

# 3 使用存盤引擎

==1 方法1：建表時指定

create table innodb_t1(id int,name char)engine=innodb;

==2 方法2：在組態檔中指定默認的存盤引擎

/etc/my.cnf

[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1

二、表介紹

表相當于檔案，表中的一條記錄就相當于檔案的一行內容，不同的是，表中的一條記錄有對應的標題，稱為表的欄位，

三、創建表

 # 語法
create table 表名(
欄位名1 型別(寬度) 約束條件,
欄位名2 型別(寬度) 約束條件,
欄位名3 型別(寬度) 約束條件
)

create table 表名(
欄位1 型別[(寬度) 約束條件],
欄位2 型別[(寬度) 約束條件]
);  #最后一個欄位后面一定不能加逗號

#注意：
1. 在同一張表中，欄位名是不能相同
2. 寬度和約束條件可選
3. 欄位名和型別是必須要有的
4. 表中最后一個欄位后面一定不能加逗號

# -------------------------------------------》
create database db1 charset utf8; # 需要先創建資料庫，再建表
use db1;  # 然后 選擇所要使用的資料庫

create table t1(id int,name char); # 建表至少要有一個欄位
create table innodb_t1(id int,name char) engine=innodb; # 指定存盤引擎
create table t3(name varchar(10) unique); # 設定單列唯一，則該欄位不能有重復的值
create table t6(
    id int primary key auto_increment,   # 將id欄位 設為自增主鍵
    name varchar(5)    # 最后一個欄位，一定不能加逗號
);
create table t2(x int not null default 111); # 設定表t2的x欄位不為空，若為空 則取默認值 111

create table shirts (
    name varchar(40),
    size enum('x-small', 'small', 'medium', 'large', 'x-large')
); # 列舉型別的欄位
create table user (
    name varchar(16),
    hobbies set("read","chou","drink","tang")
); # 集合型別的欄位


show tables;  #查看db1庫下所有表名
desc t1;  # 查看 表 t1 的結構
select * from t1;  #顯示表 t1的全部資料，剛創建的表 是一張空表
select id from t1;

insert into t1 values (1,'cc'),(2,'mili'),(3,'mela');  # 給表里已有欄位 添加資料內容（與新增欄位要區分噢）
select * from t1;  #顯示表 t1的全部資料

insert into t1(id) values (4),(5);  # 指定欄位，新增資料
select * from t1;  #顯示表 t1的全部資料

insert into author2book(author_id,book_id) values
(1,1),  # 依次匹配關系表的欄位
(1,2),
(1,3)
;

創建表--詳解

【注意】

1 在同一張表中欄位名不能重復

2 寬度和約束條件是可選的(可寫可不寫) 而欄位名和欄位型別是必須的
約束條件寫的話也支持寫多個
欄位名1 型別(寬度) 約束條件1 約束條件2...,
create table t5(id); 報錯

3 最后一行不能有逗號

create table t6(
id int,
name char,
); 會報錯

【補充】
# 寬度
一般情況下指的是對存盤資料的限制
create table t7(name char); 默認寬度是1
insert into t7 values('jason');
insert into t7 values(null); 關鍵字NULL

【針對不同的版本會出現不同的效果】

5.6版本默認沒有開啟嚴格模式，規定只能存一個字符，你給了多個字符，那么我會自動幫你截取，

5.7版本及以上或者開啟了嚴格模式，那么規定只能存幾個，就不能超，一旦超出范圍立刻報錯 Data too long for ....

【嚴格模式到底開不開呢？】
MySQL5.7之后的版本默認都是開啟嚴格模式的，

【使用資料庫的準則】
能盡量少的讓資料庫干活就盡量少，不要給資料庫增加額外的壓力

【約束條件 null not null不能插入null】？？？？
create table t8(id int,name char not null);

【寬度和約束條件到底是什么關系】
寬度是用來限制資料的存盤
約束條件是在寬度的基礎之上增加的額外的約束

四、查看表結構

show tables;             # 查看所在的庫里的所有表
show create table t1;    # 查看指定的表的詳細結構 # 與 describe t1; 的列印格式不同，內容是一樣的
show create table t1\G;  #查看表詳細結構，可加\G

describe t1;            # 查看 表 t1 的結構（結果為表格形式）
desc t1;                # 查看 表 t1 的結構（簡寫）

select * from t1;        # 查看表的所有欄位以及記錄（查看表的資料）   # select * from 表名;
select name from t1;     # 查看表的name欄位下的資料   # select 欄位名 from 表名;

五、修改表 ALTER TABLE

# MySQL對大小寫是不敏感的

#1 修改表名
    alter table 表名 rename 新表名;

#2 增加欄位
    alter table 表名 add 欄位名 欄位型別(寬度)  約束條件;
    alter table 表名 add 欄位名 欄位型別(寬度)  約束條件 first;
    alter table 表名 add 欄位名 欄位型別(寬度)  約束條件 after 欄位名;

#3 洗掉欄位
    alter table 表名 drop 欄位名;

#4 修改欄位
    alter table 表名 modify 欄位名 欄位型別(寬度) 約束條件;
    
    alter table 表名 change 舊欄位名 新欄位名 欄位型別(寬度) 約束條件;

# 修改表名  rename
# alter table 原表名 rename 新表名;
alter table t1 rename tt1;   # 表名 命名要規范

# 修改欄位  modify / change
# alter table 表名 modify 目標欄位名 欄位的新型別;
# alter table 表名 change 目標欄位名 欄位的新名字 欄位的新型別;

alter table t1 modify id tinyint;  # 修改欄位的型別

create table t3(id int default 1);
alter table t3 modify id int not null default 1;  # 修改欄位不為空

alter table t1 change id ID tinyint;  # 修改欄位名 欄位型別  # 修改欄位名字，一定要重新指定欄位型別
alter table t1 change id ID tinyint,change name NAME char(4);   # 一次修改多個欄位名的屬性

# 增加欄位  add / add … after
# alter table 表名 add 欄位名 資料型別 [完整性約束條件…];

alter table t1 add gender char(4);        # 默認在表的末尾新增欄位
alter table t1 add gender char(4) first;  # 在表的第一位置新增欄位
alter table t1 add level int after ID;    # 在指定欄位后面，新增欄位

# 洗掉欄位 drop
# alter table 表名 drop 欄位名;
alter table t1 drop gender;

修改表-詳解2

六、復制表

"""
我們sql陳述句查詢的結果其實也是一張虛擬表
"""
create table 表名 select * from 舊表;  不能復制主鍵 外鍵 ...

create table new_dep2 select * from dep where id>3;

==復制表的結構和指定欄位內容
# create table 新表 select 目標欄位1,目標欄位2,目標欄位3 from 原表;

select user();
create table t2 select user,host,password from mysql.user;

【分析如下：】
（1） 選擇所需欄位
select user();
select user,host,password from mysql.user;
===最終結果為 虛擬表，
===虛擬表--查詢結果按照一定表格式顯示，但是不存在于硬碟里

（2）將虛擬表存入具體的一張表（存入硬碟檔案）
create table t2 select user,host,password from mysql.user;


==只復制表的結構
（1）增加使得查詢為空的條件
# create table 新表 select 目標欄位1,目標欄位2,目標欄位3 from 原表 where 一個結果為False的任意條件;
create table t3 select user,host,password from mysql.user where 1!=1;
# 這樣查詢結果為空，因為沒有滿足條件的資料；但是有表結構，是一張空表

（2）使用 like
create table t4 like 復制目標表的表名;
create table t4 like t1;

七、洗掉表

# 洗掉整張表（移除表，包括表結構和記錄）
drop table t1;  # drop table 表名;

# 洗掉表的資料內容（清空表資料，表結構還在）
delete from t1;  # delete一條一條地洗掉記錄，對于自增的欄位，在用delete洗掉后，再插入值，該欄位仍按照洗掉前的位置繼續增長
# =應該用truncate清空表，# 比起delete一條一條地洗掉記錄，truncate是直接清空表，且自增欄位會清空重新從1記錄，在洗掉大表時用它
truncate t1;

八、資料型別

=1、數字型別

==1、整型

整型型別的存盤寬度，不同整型型別是不一樣的，且有默認值，不需要指定存盤寬度，
表里的欄位型別  int(11) 里面的數字 是整型數字的顯示寬度限制，不是存盤寬度限制，整型的存盤寬度 是固定的，

整數型別：TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：存盤年齡，等級，id，各種號碼等

【栗子】
mysql> create table t5(id tinyint)

mysql> desc t4;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert t4 values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> insert t4 values(127);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t4;
+------+
| id   |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

==2、浮點型別
定點數型別  DEC （等同于DECIMAL）　
浮點型別：FLOAT DOUBLE
作用：存盤薪資、身高、體重、體質引數等


=2、字串

char （定長-字符長度）
varchar （變長-字符長度）   # InnoDB存盤引擎：建議使用VARCHAR型別

# char型別：定長，簡單粗暴，浪費空間，存取速度快
字符長度范圍：0 - 255（一個中文是一個字符，是utf8編碼的3個位元組）

存盤：
存盤char型別的值時，會往右填充空格來滿足長度
例如：指定長度為10，存 > 10個字符則報錯，存 < 10個字符則用空格填充直到湊夠10個字符存盤

檢索：
在檢索或者說查詢時，查出的結果會自動洗掉尾部的空格，除非我們打開pad_char_to_full_length
SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）

# varchar型別：變長，精準，節省空間，存取速度慢
字符長度范圍：0 - 65535（如果大于21845會提示用其他型別 ，
mysql行最大限制為65535位元組，字符編碼為utf - 8
參考：
https: // dev.mysql.com / doc / refman / 5.7 / en / column - count - limit.html）

存盤：
varchar型別存盤資料的真實內容，不會用空格填充，如果'ab  ', 尾部的空格也會被存起來

強調：
varchar型別會在真實資料前加1 - 2Bytes的前綴，該前綴用來表示真實資料的bytes位元組數（1 - 2Bytes最大表示65535個數字，正好符合mysql對row的最大位元組限制，即已經足夠使用）
如果真實的資料 < 255bytes則需要1Bytes的前綴（1Bytes = 8bit   2 ** 8最大表示的數字為255）
如果真實的資料 > 255bytes則需要2Bytes的前綴（2Bytes = 16bit  2 ** 16最大表示的數字為65535）

檢索：
尾部有空格會保存下來，在檢索或者說查詢時，也會正常顯示包含空格在內的內容


-------------》精簡筆記


char 定長，不夠則補全空格
    看起來特點：
        浪費空間
        讀取速度快

varchar 變長，預留1-2bytes來存盤真實資料的長度
    看起來特點：
        節省空間
        讀取速度慢

ps：在存盤的資料量剛好達到存盤寬度限制時，其實varchar更費空間

總結：大多數情況下存盤的資料量都達不到寬度限制，所以大多數情況下varchar更省空間
但省空間不是關鍵，關鍵是省空間 會帶來io效率的提升，進而提升了查詢效率

ab   |abc  |abcd |
1bytes+ab|1bytes+abc|1bytes+abcd|

【舉例】

create table t11(x char(5));
create table t12(x varchar(5));

insert t11 values("我擦嘞 ");  -- "我擦嘞  "
insert t12 values("我擦嘞 ");  -- "我擦嘞 "

t11=>字符個數 5  位元組個數 11
t12=>字符個數 4  位元組個數 10

set sql_mode="pad_char_to_full_length";
select char_length(x) from t11;
select char_length(x) from t12;


select length(x) from t11;
select length(x) from t12;


=3、時間型別
year
date
time
datetime
timestamp

注意：
1. 單獨插入時間時，需要以字串的形式，按照對應的格式插入
2. 插入年份時，盡量使用4位值
3. 插入兩位年份時，<=69，以20開頭，比如50,  結果2050
                >=70，以19開頭，比如71，結果1971

# ======================
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）

【舉例】

create table t8(y year,t time,d date,dt datetime,ts timestamp);
insert t8 values(now(),now(),now(),now(),now());

create table student(
    id int,
    name char(10),
    born_year year,
    bitrh date,
    reg_time datetime
);

insert student values
(1,"wangjing","1911","1911-11-11","1911-11-11 11:11:11"),
(2,"lxx","1988","1988-11-11","1988-11-11 11:11:11");

insert student values
(3,"wangjing","1911","19111111","19111111111111");


# 注意：timestamp應該用于記錄更新時間
create table t9(
    id int,
    name varchar(16),
    -- update_time datetime not null default now() on update now(),
    update_time timestamp,
    reg_time datetime not null default now()
);

insert into t9(id,name) values(1,"egon");


# 測驗效果
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | egon | 2020-09-01 16:45:51 | 2020-09-01 16:45:51 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update t9 set name="EGON" where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | EGON | 2020-09-01 16:46:50 | 2020-09-01 16:45:51 |
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)



=4、列舉型別與集合型別

列舉型別enum("a","b","c","d") 多選1
enum 單選  只能在給定的范圍內選一個值，如性別 sex 男male/女female

集合型別set("a","b","c","d") 多選
set 多選 在給定的范圍內可以選擇一個或一個以上的值（愛好1,愛好2,愛好3...）

【舉例】
create table shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

INSERT INTO shirts(name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

create table user (
    name VARCHAR(16),
    hobbies set("read","chou","drink","tang")
);
insert user values("lxx","tang,chou");
insert user values("hxx","tangchou");

詳解版--mysql的常用資料型別---表欄位型別

1、整型

分類

TINYINT SMALLINT MEDUIMINT INT BIGINT

作用

存盤年齡、等級、id、號碼等等

"""
以TINYINT 
    是否有符號
        默認情況下是帶符號的
    超出會如何
        超出限制只存最大可接受值
"""
create table t9(id tinyint);
insert into t9 values(-129),(256);

# 約束條件之unsigned 無符號
create table t10(id tinyint unsigned);


create table t11(id int);
# int默認也是帶符號的  
# 整型默認情況下都是帶有符號的

# 針對整型 括號內的寬度到底是干嘛的
create table t12(id int(8));
insert into t12 values(123456789);

"""
特例:只有整型括號里面的數字不是表示限制位數
id int(8)
    如果數字沒有超出8位 那么默認用空格填充至8位
    如果數字超出了8位 那么有幾位就存幾位(但是還是要遵守最大范圍)
"""
create table t13(id int(8) unsigned zerofill);
# 用0填充至8位

# 總結:
針對整型欄位 括號內無需指定寬度 因為它默認的寬度以及足夠顯示所有的資料了

整型型別

嚴格模式

# 如何查看嚴格模式
show variables like "%mode";

模糊匹配/查詢
    關鍵字 like
        %:匹配任意多個字符
        _:匹配任意單個字符

# 修改嚴格模式
    set session  只在當前視窗有效
    set global   全域有效
    
    set global sql_mode = 'STRICT_TRANS_TABLES';
    
    修改完之后 重新進入服務端即可

2、浮點型

分類

FLOAT、DOUBLE、DECIMAL

作用

身高、體重、薪資

# 存盤限制
float(255,30)  # 總共255位 小數部分占30位
double(255,30)  # 總共255位 小數部分占30位
decimal(65,30)  # 總共65位 小數部分占30位

# 精確度驗證
create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));
"""你們在前期不要給我用反向鍵 所有的命令全部手敲！！！增加熟練度"""

insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);

float < double < decimal
# 要結合實際應用場景 三者都能使用

3、日期型別

分類：

date:年月日 2020-5-4

datetime:年月日時分秒 2020-5-4 11:11:11

time:時分秒11:11:11

Year:2020

create table student(
    id int,
    name varchar(16),
    born_year year,
    birth date,
    study_time time,
    reg_time datetime
);
insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');

4、字串型別

"""
char
    定長
    char(4)     資料超過四個字符直接報錯 不夠四個字符空格補全
varchar
    變長
    varchar(4)  資料超過四個字符直接報錯 不夠有幾個存幾個
"""
create table t18(name char(4));
create table t19(name varchar(4));

insert into t18 values('a');
insert into t19 values('a');

# 介紹一個小方法 char_length統計欄位長度
select char_length(name) from t18;
select char_length(name) from t19;
"""
首先可以肯定的是 char硬碟上存的絕對是真正的資料 帶有空格的
但是在顯示的時候MySQL會自動將多余的空格剔除
"""

# 再次修改sql_mode 讓MySQL不要做自動剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

分類

"""
char
    缺點:浪費空間
    優點:存取都很簡單
        直接按照固定的字符存取資料即可
        jason egon alex wusir tank 
        存按照五個字符存 取也直接按照五個字符取
        
varchar
    優點:節省空間
    缺點:存取較為麻煩
        1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank 
        
        存的時候需要制作報頭
        取的時候也需要先讀取報頭 之后才能讀取真實資料
        
以前基本上都是用的char 其實作在用varchar的也挺多
"""

補充:
    進來公司之后你完全不需要考慮欄位型別和欄位名
    因為產品經理給你發的郵件上已經全部指明了

char與varchar對比

5、列舉型別與集合型別

分類：

"""
列舉(enum) ：多選一
集合(set)：多選多
"""

create table user(
    id int,
    name char(16),
    gender enum('male','female','others')
);
insert into user values(1,'jason','male');  正常
insert into user values(2,'egon','xxxxooo');  報錯
# 列舉欄位 后期在存資料的時候只能從列舉里面選擇一個存盤 


create table teacher(
    id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','DBJ','hecha')
);
insert into teacher values(1,'jason','male','read');  正常
insert into teacher values(2,'egon','female','DBJ,hecha');  正常
insert into teacher values(3,'tank','others','生蠔'); 報錯
# 集合可以只寫一個  但是不能寫沒有列舉的

具體使用

補充：

MySQL 檢查資料長度，可用 SQL 語言來查看：

　　select length(欄位名) from 表名

九、表完整性約束

約束條件與資料型別的寬度一樣，都是可選引數
作用：用于保證資料的完整性和一致性

主要分為：
PRIMARY KEY (PK)    標識該欄位為該表的主鍵，可以唯一的標識記錄
FOREIGN KEY (FK)    標識該欄位為該表的外鍵
NOT NULL    標識該欄位不能為空
UNIQUE KEY (UK)    標識該欄位的值是唯一的
AUTO_INCREMENT    標識該欄位的值自動增長（整數型別，而且為主鍵）
DEFAULT    為該欄位設定默認值

UNSIGNED 無符號
ZEROFILL 使用0填充

說明：
1. 是否允許為空，默認NULL，可設定NOT NULL，欄位不允許為空，必須賦值
2. 欄位是否有默認值，預設的默認值是NULL，如果插入記錄時不給欄位賦值，此欄位使用默認值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必須為正值（無符號） 不允許為空 默認是20
3. 是否是key
主鍵 primary key
外鍵 foreign key
索引 (index,unique...)


# --------------------------------------------------------
=1  not null [default 默認值]  #不為空/默認值

null表示空，非字串 （不宣告是否為空，則默認可以為空）
not null - 不可空
null - 可空
# --------------》
create table t1(id int); #id欄位默認可以插入空
insert into t1 values(); #可以插入空

create table t1(x int not null);  # 設定表t1的x欄位不為空
insert into t1 values();  #不能插入空
# ERROR 1364 (HY000): Field 'id' doesn't have a default value

#設定id欄位有默認值后，則無論id欄位是null還是not null，都可以插入空，插入空默認填入default指定的默認值
create table t2(x int not null default 111); # 設定表t2的x欄位不為空，若為空 則取默認值 111
insert into t2 values();

=2 unique

# 單列唯一
# 方法一： 表名（欄位名 欄位型別 unique）
create table t3(name varchar(10) unique); # 設定單列唯一，則該欄位不能有重復的值
insert into t3 values('egon');
insert t3 values('mili');

mysql> insert into t3 values("egon");
# ERROR 1062 (23000): Duplicate entry 'egon' for key 'name'

# 方法二：constraint uk_name unique(欄位名)
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


# 聯合唯一
create table server(
    id int,
    name varchar(10),
    ip varchar(15),
    port int,
    unique(ip,port),  # 設定 ip 和 port，聯合唯一
    unique(name)      # 設定 name 為 單列唯一
);

# 驗證
insert into server values (1,"web1","10.10.0.11",8080);
insert into server values (2,"web2","10.10.0.11",8081);

mysql> insert into server values(4,"web4","10.10.0.11",8081);
# ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip'


=3 not null 和 unique 的化學反應=>會被識別成表的主鍵

create table t4(id int,name varchar(10) not null unique);  # 設定欄位 name 單列唯一，且不為空==》 主鍵
create table t5(id int,name varchar(10) unique);   # 設定欄位 name單列唯一


=4 主鍵  primary key

=在查詢時，盡量使用主鍵欄位為查詢依據
=在建表時，自己設定主鍵欄位，一般以 id 作為主鍵
=主鍵的約束效果：不為空，且唯一 / not null+unique
=主鍵primary key是innodb存盤引擎組織資料的依據，innodb稱之為索引組織表，
一張innodb表中必須有且只有一個主鍵,但是該主鍵可以是聯合主鍵


# 單列做主鍵
# =方法一   not null+unique
create table department1(
id int not null unique, #主鍵
comment varchar(100)
);

# =方法二  在某一個欄位后用primary key，也可加上 自增 auto_increment
create table t6(
    id int primary key auto_increment,  # id 作為主鍵，且自增
    name varchar(5)
);

insert into t6(name) values ("egon"),("tom"),("to1"),("to2");

# =方法三  在所有欄位后單獨定義primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id)); #創建主鍵并為其命名pk_name


# 聯合主鍵（了解）
create table t7(
    id int,
    name varchar(5),
    primary key(id,name)  # 聯合主鍵
);


=5 外鍵 foreign key （在虛擬表欄位 用 MUL 表示外鍵）

=先創建 被關聯的表（外鍵所指向的表，又被稱為父表），再創建 包含外鍵的表（又被稱為子表）
例如--- 設定 A表關聯B表，外鍵欄位設在A表；則在創建表時，先創建 B表（父表）并插入資料，再創建 A表（子表） 插入資料

=外鍵一般設定在欄位多的一方，關聯少的一方
=表型別必須是innodb存盤引擎，且被關聯的欄位，即references指定的另外一個表的欄位，必須保證唯一

=級聯更新（更新列（表頭標題為橫向的時候）），指的是有關聯的欄位名包含的所有記錄 同步更新（都變為同樣的記錄）；
 級聯洗掉（洗掉行（表頭標題為橫向的時候）），則是包含關聯欄位的所有記錄 同步洗掉
【栗子】
#刪父表department，子表employee中對應的記錄跟著刪
mysql> delete from department where id=3; # 與之關聯的employee表中，dpt_id為3的記錄行 都被洗掉
mysql> select * from employee;
+----+-------+--------+
| id | name  | dpt_id |
+----+-------+--------+
|  1 | egon  |      1 |
|  2 | alex1 |      2 |
|  3 | alex2 |      2 |
|  4 | alex3 |      2 |
+----+-------+--------+

#更新父表department，子表employee中對應的記錄跟著改
mysql> update department set id=22222 where id=2; # 修改id，則與之關聯的表中 與原id相同的id 也會跟著改
mysql> select * from employee;
+----+-------+--------+
| id | name  | dpt_id |
+----+-------+--------+
|  1 | egon  |      1 |
|  3 | alex2 |  22222 |
|  4 | alex3 |  22222 |
|  5 | alex1 |  22222 |
+----+-------+--------+


=6 auto_increment

（1）
create table t6(
    id int primary key auto_increment,  # id 作為主鍵，且自增
    name varchar(5)
);
# 約束欄位為自動增長，被約束的欄位必須同時被key約束
# 插入欄位資料時如果不指定id，則id會自動增長；也可以指定id，

（2）
# 清空表資料
# =對于自增的欄位，在用delete洗掉后，再插入值，該欄位仍按照洗掉前的位置繼續增長
delete from t1;  # 洗掉表的資料內容，表結構還在

# =應該用truncate清空表，# 比起delete一條一條地洗掉記錄，truncate是直接清空表，且自增欄位會清空重新從1記錄，在洗掉大表時用它
truncate t1;

（3） 自定義“自增”
# =1 設定 自增起始值
#在創建完表后，修改自增欄位的起始值
create table t6(
    id int primary key auto_increment,  # id 作為主鍵，且自增
    name varchar(5)
);

alter table t6 auto_increment=3;

#也可以創建表時指定auto_increment的初始值，注意初始值的設定為表選項，應該放到括號外
create table student(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') default 'male'
)auto_increment=3;


# =2 設定自增步長

# sqlserver：自增步長
# 基于表級別
create table t1（
    id int，，，
）engine = innodb, auto_increment = 2 步長 = 2 default charset = utf8

# mysql自增的步長：
show session variables like 'auto_inc%';
# 基于會話級別
set session auth_increment_increment = 2  # 修改會話級別的步長
# 基于全域級別的
set global auth_increment_increment=2  # 修改全域級別的步長（所有會話都生效）

# 注意：
auto_increment_offset 的值 需要小于 auth_increment_increment 的值，
否則 auto_increment_offset 的值會被忽略

表的完整性約束--詳解版

1、介紹

約束條件與資料型別的寬度一樣，都是可選引數

作用：用于保證資料的完整性和一致性主要分為：

PRIMARY KEY (PK) 標識該欄位為該表的主鍵，可以唯一的標識記錄

FOREIGN KEY (FK) 標識該欄位為該表的外鍵

NOT NULL 標識該欄位不能為空

UNIQUE KEY (UK) 標識該欄位的值是唯一的

AUTO_INCREMENT 標識該欄位的值自動增長（整數型別，而且為主鍵）

DEFAULT 為該欄位設定默認值

UNSIGNED 無符號

ZEROFILL 使用0填充

說明：

1. 是否允許為空，默認NULL，可設定NOT NULL，欄位不允許為空，必須賦值

2. 欄位是否有默認值，預設的默認值是NULL，如果插入記錄時不給欄位賦值，此欄位使用默認值

sex enum('male','female') not null default 'male'

age int unsigned NOT NULL default 20 #必須為正值（無符號）不允許為空默認是20

3. 是否是key

主鍵 primary key

外鍵 foreign key

索引 (index,unique...)

2、not null與default

是否可空，null表示空，非字串 not null - 不可空 null - 可空

默認值，創建列時可以指定默認值，當插入資料時如果未主動設定，則自動添加默認值

create table tb1( nid int not null defalut 2, num int not null )

3、 unique

============設定唯一約束 UNIQUE===============
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技術');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技術');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

設定唯一約束 UNIQUE

mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

not null+unique的化學反應

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #聯合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

聯合唯一

4、primary key

從約束角度看primary key欄位的值不為空且唯一，那我們直接使用not null+unique不就可以了嗎，要它干什么？

主鍵primary key是innodb存盤引擎組織資料的依據，innodb稱之為索引組織表，一張表中必須有且只有一個主鍵，

一個表中可以：

單列做主鍵多列做主鍵（復合主鍵）

============單列做主鍵===============
#方法一：not null+unique
create table department1(
id int not null unique, #主鍵
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：在某一個欄位后用primary key
create table department2(
id int primary key, #主鍵
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：在所有欄位后單獨定義primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #創建主鍵并為其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

單列主鍵

==================多列做主鍵================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

多列主鍵

5、auto_increment

約束欄位為自動增長，被約束的欄位必須同時被key約束

#不指定id，則自動增長
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#對于自增的欄位，在用delete洗掉后，再插入值，該欄位仍按照洗掉前的位置繼續增長
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#應該用truncate清空表，比起delete一條一條地洗掉記錄，truncate是直接清空表，在洗掉大表時用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)

auto_increment

6、foreign key

外鍵就是用來幫助我們建立表與表之間關系的

foreign key

十、表的關系

=1、如何找出兩張表之間的關系？
---一句話---》左顧右盼，再下定論
分析步驟：
#1、先站在左表的角度去找
是否左表的多條記錄可以對應右表的一條記錄，如果是，則證明左表的一個欄位foreign key 右表一個欄位（通常是id）

#2、再站在右表的角度去找
是否右表的多條記錄可以對應左表的一條記錄，如果是，則證明右表的一個欄位foreign key 左表一個欄位（通常是id）

#3、總結：
#多對一：
如果只有步驟1成立，則是左表多對一右表
如果只有步驟2成立，則是右表多對一左表
#多對多
如果步驟1和2同時成立，則證明這兩張表時一個雙向的多對一，即多對多,需要定義一個這兩張表的關系表來專門存放二者的關系
#一對一:
如果1和2都不成立，而是左表的一條記錄唯一對應右表的一條記錄，反之亦然，這種情況很簡單，就是在左表foreign key右表的基礎上，將左表的外鍵欄位設定成unique即可


=2、建立表之間的關系

==1、多對一（一對多）----- foreign key
foreign key(press_id) references press(id) on delete cascade on update cascade
# foreign key(press_id) references press(id)  #設定 press_id 為外鍵欄位，
# on delete cascade   # 設定級聯洗掉
# on update cascade   # 設定級聯更新

【栗子1】
表：出版社，書
一對多（或多對一）：一個出版社可以出版多本書
關聯方式：foreign key

create table book (
id int primary key auto_increment,
name varchar(10),
press_id int not null,
foreign key(press_id) references press(id) on delete cascade on update cascade
);

insert into press(name) values
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識產權沒有用出版社');

insert into book(name,press_id) values
('九陽神功',1),
('九陰真經',2),
('九陰白骨爪',2),
('獨孤九劍',3),
('降龍十巴掌',2),
('葵花寶典',3);

==2、多對多-----foreign key+一張新的表 A2B

constraint fk_author foreign key(book_id) references book(id) on delete cascade on update cascade,
primary key(author_id,book_id)    # 聯合主鍵


【栗子2】
表：作者資訊，書
多對多：一個作者可以寫多本書，一本書也可以有多個作者，雙向的一對多，即多對多
關聯方式：foreign key+一張新的表 A2B

create table author(
id int primary key auto_increment,
name varchar(20)
);

# 再建一張表，用于存放關系
# 這張表就存放 作者表 與 書表 的關系，即查詢二者的關系查這表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,

constraint fk_author foreign key(book_id) references book(id) on delete cascade on update cascade,
primary key(author_id,book_id)    # 聯合主鍵
);

insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

insert into author2book(author_id,book_id) values
(1,1),  # 依次匹配關系表的欄位
(1,2),
(1,3)
;

==3、一對一 ----- foreign key+unique

seat_id int unique,   #該欄位一定要是唯一的
foreign key(seat_id) references seat(id) on delete cascade on update cascade
# 外鍵的欄位一定要保證unique

【栗子】
兩張表：座位表和客戶表
一對一：一個座位只能坐一個客戶，一個客戶只能有一個座位
關聯方式：foreign key+unique

create table seat(
id int primary key auto_increment,
area varchar(10)
);

create table client(
id int primary key auto_increment,
name varchar(10),
seat_id int unique,  #外鍵的欄位一定要保證unique
foreign key(seat_id) references seat(id) on delete cascade on update cascade
);

表的關系--詳解版2

表與表之間最多只有四種關系：
1、一對多關系（在MySQL的關系中沒有/多對一/一說，一對多多對一都叫一對多）
2、多對多關系
3、一對一關系
4、沒有關系

"""
判斷表與表之間關系的時候 前期不熟悉的情況下 一定要按照我給你的建議
換位思考  分別站在兩張表的角度考慮

員工表與部門表為例
    先站在員工表
        思考一個員工能否對應多個部門(一條員工資料能否對應多條部門資料)
            不能！！！
            (不能直接得出結論 一定要兩張表都考慮完全)
    再站在部門表
        思考一個部門能否對應多個員工(一個部門資料能否對應多條員工資料)
            能！！！
    得出結論
        員工表與部門表示單向的一對多
        所以表關系就是一對多
"""
foreign key
    1 一對多表關系   外鍵欄位建在多的一方
    2 在創建表的時候 一定要先建被關聯表 
    3 在錄入資料的時候 也必須先錄入被關聯表
# SQL陳述句建立表關系
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教學部','教書育人'),('外交部','多人外交'),('nb技術部','技術能力有限部門');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);


# 修改dep表里面的id欄位
update dep set id=200 where id=2;  不行
# 洗掉dep表里面的資料
delete from dep;  不行

# 1 先洗掉教學部對應的員工資料 之后再洗掉部門
    操作太過繁瑣
    
# 2 真正做到資料之間有關系
    更新就同步更新
    洗掉就同步洗掉
"""
級聯更新   >>>   同步更新
級聯洗掉   >>>   同步洗掉
"""
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步洗掉
);
insert into dep(dep_name,dep_desc) values('sb教學部','教書育人'),('外交部','多人外交'),('nb技術部','技術能力有限部門');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

一對多

"""
圖書表和作者表
"""
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步洗掉
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步洗掉
);
"""
按照上述的方式創建 一個都別想成功！！！
其實我們只是想記錄書籍和作者的關系
針對多對多欄位表關系 不能在兩張原有的表中創建外鍵
需要你單獨再開設一張 專門用來存盤兩張表資料之間的關系
"""
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);
create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id) 
    on update cascade  # 同步更新
    on delete cascade,  # 同步洗掉
    foreign key(book_id) references book(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步洗掉
);

多對多

"""
id name age addr phone hobby email........
如果一個表的欄位特別多 每次查詢又不是所有的欄位都能用得到
將表一分為二  
    用戶表
        用戶表
            id name age
        用戶詳情表
            id addr phone hobby email........
    
    站在用戶表
        一個用戶能否對應多個用戶詳情   不能！！！
    站在詳情表
        一個詳情能否屬于多個用戶      不能！！！
    結論:單向的一對多都不成立 那么這個時候兩者之間的表關系
        就是一對一
        或者沒有關系(好判斷)

客戶表和學生表
    在你們報名之前你們是客戶端
    報名之后是學生(期間有一些客戶不會報名)
"""

一對一 外鍵欄位建在任意一方都可以 但是推薦你建在查詢頻率比較高的表中
create table authordetail(
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步洗掉
)

一對一

總結：

"""
表關系的建立需要用到foreign key--------
    一對多
        外鍵欄位建在多的一方
    多對多
        自己開設第三張存盤
    一對一
        建在任意一方都可以 但是推薦你建在查詢頻率較高的表中

判斷表之間關系的方式--------
    換位思考！！！
        員工與部門
    
        圖書與作者
    
        作者與作者詳情
"""

參考資料：

https://zhuanlan.zhihu.com/p/113334455

https://www.cnblogs.com/linhaifeng/articles/7232894.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/46554.html

標籤：MySQL

上一篇：【2020Python修煉記】MySQL之庫相關操作

下一篇：2020python練習——表的關系

【2020Python修煉記】MySQL之 表相關操作

【目錄】

一 存盤引擎介紹

二 表介紹

三 創建表

四 查看表

五 修改表

六 復制表

七 洗掉表

八 表資料型別

1、介紹

2、數值型別

3、日期型別

4、字串型別

5、列舉型別與集合型別

九 表完整性約束

1、介紹

2、not null與default

3、 unique

4、primary key

5、auto_increment

6、foreign key

十 表的關系

一 、存盤引擎介紹

1、什么是存盤引擎

日常生活中檔案格式有很多中，并且針對不同的檔案格式會有對應不同存盤方式和處理機制(txt,pdf,word,mp4...)

針對不同的資料應該有對應的不同的處理機制來存盤

存盤引擎就是不同的處理機制.

2、MySQL主要存盤引擎

Innodb

是MySQL5.5版本及之后默認的存盤引擎

存盤資料更加的安全

myisam

是MySQL5.5版本之前默認的存盤引擎

速度要比Innodb更快 但是我們更加注重的是資料的安全

memory

記憶體引擎(資料全部存放在記憶體中) 斷電資料丟失

blackhole

無論存什么，都立刻消失(黑洞)

3、SQL陳述句 查看/使用 存盤引擎

# 1 查看所有的存盤引擎

show engines\G; #查看所有支持的存盤引擎

# 2 不同的存盤引擎在存盤表的時候 異同點create table t1(id int) engine=innodb;create table t2(id int) engine=myisam;create table t3(id int) engine=blackhole;create table t4(id int) engine=memory;

# 存資料insert into t1 values(1);insert into t2 values(1);insert into t3 values(1);insert into t4 values(1);

# 3 使用存盤引擎

==1 方法1：建表時指定

create table innodb_t1(id int,name char)engine=innodb;

==2 方法2：在組態檔中指定默認的存盤引擎

/etc/my.cnf

[mysqld]

default-storage-engine=INNODB

innodb_file_per_table=1

二 、表介紹

表相當于檔案，表中的一條記錄就相當于檔案的一行內容，不同的是，表中的一條記錄有對應的標題，稱為表的欄位，

三 、創建表

【注意】

1 在同一張表中欄位名不能重復

2 寬度和約束條件是可選的(可寫可不寫) 而欄位名和欄位型別是必須的 約束條件寫的話 也支持寫多個 欄位名1 型別(寬度) 約束條件1 約束條件2..., create table t5(id); 報錯

3 最后一行不能有逗號

create table t6( id int, name char, ); 會報錯

【補充】# 寬度 一般情況下指的是對存盤資料的限制 create table t7(name char); 默認寬度是1 insert into t7 values('jason'); insert into t7 values(null); 關鍵字NULL

【針對不同的版本會出現不同的效果】

5.6版本默認沒有開啟嚴格模式，規定只能存一個字符，你給了多個字符，那么我會自動幫你截取，

5.7版本及以上或者開啟了嚴格模式，那么規定只能存幾個，就不能超，一旦超出范圍立刻報錯 Data too long for ....

【嚴格模式到底開不開呢？】MySQL5.7之后的版本默認都是開啟嚴格模式的，

【使用資料庫的準則】 能盡量少的讓資料庫干活就盡量少，不要給資料庫增加額外的壓力

【約束條件 null not null不能插入null】？？？？create table t8(id int,name char not null);

【寬度和約束條件到底是什么關系】 寬度是用來限制資料的存盤 約束條件是在寬度的基礎之上增加的額外的約束

四 、查看表結構

五 、修改表 ALTER TABLE

六 、復制表

七 、洗掉表

八 、資料型別

1、整型

分類

TINYINT SMALLINT MEDUIMINT INT BIGINT

作用

存盤年齡、等級、id、號碼等等

嚴格模式

2、浮點型

【2020Python修煉記】MySQL之表相關操作

一存盤引擎介紹

二表介紹

三創建表

四查看表

五修改表

六復制表

七洗掉表

八表資料型別

九表完整性約束

十表的關系

一、存盤引擎介紹

速度要比Innodb更快但是我們更加注重的是資料的安全

3、SQL陳述句查看/使用存盤引擎

# 2 不同的存盤引擎在存盤表的時候異同點
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;

# 存資料
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

二、表介紹

三、創建表

2 寬度和約束條件是可選的(可寫可不寫) 而欄位名和欄位型別是必須的
約束條件寫的話也支持寫多個
欄位名1 型別(寬度) 約束條件1 約束條件2...,
create table t5(id); 報錯

create table t6(
id int,
name char,
); 會報錯

【補充】
# 寬度
一般情況下指的是對存盤資料的限制
create table t7(name char); 默認寬度是1
insert into t7 values('jason');
insert into t7 values(null); 關鍵字NULL

【嚴格模式到底開不開呢？】
MySQL5.7之后的版本默認都是開啟嚴格模式的，

【使用資料庫的準則】
能盡量少的讓資料庫干活就盡量少，不要給資料庫增加額外的壓力

【約束條件 null not null不能插入null】？？？？
create table t8(id int,name char not null);

【寬度和約束條件到底是什么關系】
寬度是用來限制資料的存盤
約束條件是在寬度的基礎之上增加的額外的約束

四、查看表結構

五、修改表 ALTER TABLE

六、復制表

七、洗掉表

八、資料型別

"""
列舉(enum) ：多選一
集合(set)：多選多
"""

　　select length(欄位名) from 表名

九、表完整性約束

作用：用于保證資料的完整性和一致性主要分為：

`PRIMARY KEY (PK) 標識該欄位為該表的主鍵，可以唯一的標識記錄`

`FOREIGN KEY (FK) 標識該欄位為該表的外鍵`

`NOT NULL 標識該欄位不能為空`

`UNIQUE KEY (UK) 標識該欄位的值是唯一的`

`AUTO_INCREMENT 標識該欄位的值自動增長（整數型別，而且為主鍵）`

`DEFAULT 為該欄位設定默認值`

`UNSIGNED 無符號`

`ZEROFILL 使用0填充`

`1. 是否允許為空，默認NULL，可設定NOT NULL，欄位不允許為空，必須賦值`

`2. 欄位是否有默認值，預設的默認值是NULL，如果插入記錄時不給欄位賦值，此欄位使用默認值`

`sex enum('male','female') not null default 'male'`

`age int unsigned NOT NULL default 20 #必須為正值（無符號）不允許為空默認是20`

`3. 是否是key`

`主鍵 primary key`

`外鍵 foreign key`

`索引 (index,unique...)`

單列做主鍵多列做主鍵（復合主鍵）

十、表的關系

表與表之間最多只有四種關系：
1、一對多關系（在MySQL的關系中沒有/多對一/一說，一對多多對一都叫一對多）
2、多對多關系
3、一對一關系
4、沒有關系