內容概要

約束條件
表與表之間建立關系（約束）
修改表的完整語法
復制表

約束條件

default默認值

"""
# 補充：在插入資料的時候可以指定欄位
create table t1 (
    id int, 
    name varchar(16)
);

insert into t1 (name, id) values ('samuel', 1);

create table t2 (
    id int, 
    name varchar(16),
    gender enum('male', 'female', 'others') default 'male'
);

insert into t2 (id, name) values (1, 'samuel');
insert into t2 values (2, 'nagase', 'female');
"""

unique唯一

"""
# 單列唯一
create table t3 (
    id int unique,
    name varchar(16)
);

insert into t3 values (1, 'samuel'), (1, 'nagase');
insert into t3 values (1, 'samuel'), (2, 'nagase');

# 聯合唯一
# ip和port，單個可以重復，但是加在一起必須是唯一的
create table t4 (
    id int, 
    ip varchar(16),
    port int,
    unique (ip, port)
);

insert into t4 values (1, '192.168.1.1', 8000);
insert into t4 values (2, '192.168.1.1', 8001);
insert into t4 values (3, '192.168.1.2', 8000);
insert into t4 values (4, '192.168.1.1', 8000);
"""

primary key主鍵

"""
# 單單從約束效果上來看，primary key等價于not null + unique
# 非空且唯一！！！
create table t5 (
    id int primary key
);

insert into t5 values (null);
insert into t5 values (1), (1);
insert into t5 values (1), (2);

# 它除了有約束效果之外，它還是Innodb存盤引擎在組織資料的依據，
# Innodb存盤引擎在創建表的時候必須有primary key
# 因為它類似于書的目錄，能夠幫助提升查詢效率，并且也是建表的依據

# 1、一張表中，有且只有一個主鍵，如果沒有設定主鍵，那么會從上至下搜索，直到遇到一個非空且唯一的欄位將其升級為primary key
create table t6 (
    id int,
    name varchar(16), 
    age int not null unique, 
    addr varchar(32) not null unique
);
# 2、如果表中沒有主鍵也沒有其他任何的非空且唯一欄位，那么Innodb會采用自己內部提供的一個隱藏欄位作為primary key，隱藏意味著你無法使用它，進而就無法提升查詢效率

# 3、一張表中通常都應該有一個主鍵欄位，并且通常將id作為主鍵
# 單個欄位主鍵
create table t7 (
    id int primary key,
    name varchar(16)
);
# 聯合主鍵（多個欄位聯合起來作為表的主鍵，本質還是一個主鍵）
create table t7 (
    ip varchar(16),
    port int,
    primary key (ip, port)
);
"""

auto_increment自增

"""
# 當編號特別多的時候，人為的去維護太過繁瑣，因此這就涉及到了自增
create table t8 (
    id int primary key auto_increment,
    name varchar(16)
);

insert into t8 (name) values ('samuel'), ('nagase'), ('matthew');
# 注意：auto_increment通常都是加在primary key鍵上，不能給普通欄位加
"""

補充：洗掉表的兩種方法

"""
delete from 表名

truncate 表名
"""

兩種方法的區別：delete from 表名這種方法，在洗掉表中資料的時候，主鍵的自增不會停止，會繼續編號

然而，truncate 表名這種方式，除了清空資料表之外還會重置主鍵的自增，使其從1重新開始

表與表之間建立關系

"""
# 定義了一張員工表 表中有很多欄位
# id name gender dep_name dep_desc

# 分析目前的表所存在的問題：
# 1、該表的組織結構不是很清晰
# 2、浪費磁盤空間
# 3、資料的擴展性極差

# 那么該如何優化呢？那么多的欄位全部寫在了一張表里，那就相當于把所有的代碼全部寫在一個檔案中，這樣是有極大問題的，
# 因此我們應該將該表拆分為：員工表和部門表
"""

外鍵約束

# 外鍵就是用來幫助我們建立表與表中間關系的
foreign key

表與表之間的關系

# 表與表之間的關系只有4種

# 一對多關系
    # 注意：在MySQL中，沒有多對一關系這么一說，一對多、多對一 ，都是一對多
# 多對多關系
# 一對一關系
# 沒有關系

一對多關系

"""
判斷表與表中間關系的時候，一定要一步一步的判斷，使用正確的方法，那就是
“換位思考”分別站在兩張表的角度考慮問題

比如：員工表與部門表
先站在員工表
    思考一個員工能夠對應多個部門------不能！！！
再站在部門表
    思考一個部門可以不可以包含多個員工-------可以！！！
得出結論：
    員工表與部門表表示單向的一對多，所以表關系就是一對多，
"""

foreign key
【原則1】一對多關系的表 外鍵欄位要建立在多的那一方
【原則2】在創建表的時候，一定要先創建被關聯的那一方

# SQL陳述句
create table dep (
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);

create table emp (
    id int primary key auto_increment,
    name varchar(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 ('教學部', '教書育人'), ('技術部', '技術能力有限'), ('外交部', '外交');

insert into emp (name, dep_id) values ('samuel', 2), ('nagase', 1), ('matthew', 1), ('garnet', 3);

# 修改dep表的id欄位
update dep set id = 200 where id = 2;    操作失敗

# 洗掉dep表里的資料
delete from dep;    操作失敗

以上兩個陳述句都失敗了，是因為dep表被emp表所參考，因此，dep表不可以被洗掉，解決辦法如下：
# 1、先洗掉教學部對應的員工資料，之后再洗掉部門
 這樣做太過繁瑣

# 2、真正做到資料之間有關系，更新就同步更新，洗掉就同步洗掉
# 那也就是級聯更新、級聯洗掉，那么如何做到呢？？？
# 解決辦法：在創建表的時候添加額外的約束
create table dep (
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);

create table emp (
    id int primary key auto_increment,
    name varchar(16),
    gender enum('male', 'female', 'others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) on update cascade on delete cascade
);

多對多關系

"""
判斷表與表之間關系的時候，一定要使用正確的方法，那就是“換位思考”，分別站在兩張表的角度考慮

例如：圖書表與作者表
先站在圖書表的角度考慮，思考一個圖書可不可以有多個作者-----可以！！！
再站在作者表的角度考慮，思考一個作者可不可以出版多本圖書----可以！！！
得出結論：
圖書表與作者表是雙向的一對多，所以表關系就是“多對多”關系
"""

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
);

# 如果按照以上的陳述句創建表，結果是：：：失敗的！！！
回想我們的兩大原則：
【原則1】針對于一對多關系的表，外鍵欄位要建立在多的那一方
【原則2】在創建表的時候，一定要先創建被關聯的那一方
針對于圖書表與作者表，圖書表參考了作者表的id，對于圖書表來說，作者表是被關聯的那一方；那么反過來，對于作者表來說，圖書表是被關聯的那一方，如此一來，雙方都是被關聯的那一方，那，，，，怎么辦，，，雙方就這么拖著，，，于是乎兩張表均創建失敗！！！

# 其實我們只是想記錄圖書與作者的關系
# 針對多對多的關系，不能在兩張原表中創建外鍵
# 我們需要單獨創建一張表出來專門用于存盤兩張表之間的關系
create table book (
    id int primary key auto_increment,
    title varchar(16),
    price int
);

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

create table book2author (
    id int primary key auto_increment, 
    book_id int, 
    author_id int,
    foreign key(book_id) references book(id) on update cascade on delete cascade,
    foreign key(author_id) references author(id) on update cascade on delete cascade
);

一對一關系

"""
id name age gender addr phone hobby email...
如果一張表的欄位特別多，每次查詢又不是所有的欄位都能用到，于是我們將表一分為二
用戶表
    id name age
用戶詳情表
    id addr phone hobby email...

“換位思考”的方法：
先站在用戶表的角度考慮：一個用戶能否對應多個用戶詳情----不可以！！！
再站在用戶詳情的角度考慮：一個用戶詳情是否屬于多個用戶---不可以！！！
得出結論：單向的一對多都不成立，那么這個時候兩者之間的關系要么就是一對一的關系要么就是沒有關系
注意，針對于一對一的關系，外鍵欄位要建立在查詢頻率高的那張表中
"""

create table user_detail (
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);

create table user (
    id int primary key auto_increment,
    name varchar(16),
    age int,
    user_detail_id int unique,
    foreign key(user_detail_id) references user_detail(id) on update cascade on delete cascade 
);

總結

"""
表關系的創建需要用到foreign key
    一對多
        外鍵欄位建立在多的一方
    多對多
        自己開設第三張表
    一對一
        建立在任意一張表中都可以，但是建議在查詢頻率高的表中建立
        
建立表之間關系的方式
    換位思考！！！
        員工 與 部門
        圖書 與 作者
        用戶 與 用戶詳情
"""

修改表

# 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 舊欄位名 新欄位名 欄位型別(寬度) 約束條件;

復制表

# 我們在執行SQL陳述句的時候，顯示的結果是一張虛擬的表
create table 新表名 select * from 表名; # 但是不可以復制主鍵、外鍵、索引...只是復制了結構和資料而已

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

標籤：MySQL

上一篇：===========SQL Server 磁區表的問題===================

下一篇：求資料庫系統課設檔案小區物業管理系統

😎P03 DB 資料庫的約束條件、表關系、修改表語法以及復制表😎

內容概要

約束條件

default默認值

unique唯一

primary key主鍵

auto_increment自增

補充：洗掉表的兩種方法

表與表之間建立關系

外鍵約束

表與表之間的關系

一對多關系

多對多關系

一對一關系

總結

修改表

復制表