MySQL完整性約束

来源:https://www.cnblogs.com/fengqiang626/archive/2019/08/28/11427082.html
-Advertisement-
Play Games

[TOC] 1.介紹 約束條件與數據類型的寬度一樣，都是可選參數 作用：用於保證數據的完整性和一致性 主要分為： 說明： 2.not null 和default 是否可空，null表示空，非字元串 not null 不可空 null 可空 default預設值，創建列時可以指定預設值，當插入數據時如 ...


目錄

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 - 可空

default預設值，創建列時可以指定預設值，當插入數據時如果未主動設置，則自動添加預設值
create table tb1(
id int not null defalut 2 ,
num int not null
)

3.unique約束（唯一性約束）

單列唯一

-----1.單列唯一---------
create table t2(
id int not null unique,
name char(10)
);
insert into t2 values(1,'egon');
insert into t2 values(1,'alex');
#上面創建表的時候把id設置了唯一約束。那麼在插入id=1，就會出錯了

多列唯一

-----2.多列唯一---------
#255.255.255.255
create table server(
id int primary key auto_increment,
name char(10),
host char(15), #主機ip
port int, #埠
constraint host_port unique(host,port) #constraint host_port這個只是用來設置唯一約束的名字的，也可以不設置預設就有了
);
insert into server(name,host,port) values('ftp','192.168.20.11',8080);
insert into server(name,host,port) values('https','192.168.20.11',8081); #ip和埠合起來唯一
select * from server;

4.primary key （主鍵約束）

primary key欄位的值不為空且唯一

一個表中可以：

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

但一個表內只能有一個主鍵primary key

單列主鍵

 1 ============單列做主鍵===============
 2 #方法一：not null+unique
 3 create table department1(
 4 id int not null unique, #主鍵
 5 name varchar(20) not null unique,
 6 comment varchar(100)
 7 );
 8 
 9 mysql> desc department1;
10 +---------+--------------+------+-----+---------+-------+
11 | Field   | Type         | Null | Key | Default | Extra |
12 +---------+--------------+------+-----+---------+-------+
13 | id      | int(11)      | NO   | PRI | NULL    |       |
14 | name    | varchar(20)  | NO   | UNI | NULL    |       |
15 | comment | varchar(100) | YES  |     | NULL    |       |
16 +---------+--------------+------+-----+---------+-------+
17 rows in set (0.01 sec)
18 
19 #方法二：在某一個欄位後用primary key
20 create table department2(
21 id int primary key, #主鍵
22 name varchar(20),
23 comment varchar(100)
24 );
25 
26 mysql> desc department2;
27 +---------+--------------+------+-----+---------+-------+
28 | Field   | Type         | Null | Key | Default | Extra |
29 +---------+--------------+------+-----+---------+-------+
30 | id      | int(11)      | NO   | PRI | NULL    |       |
31 | name    | varchar(20)  | YES  |     | NULL    |       |
32 | comment | varchar(100) | YES  |     | NULL    |       |
33 +---------+--------------+------+-----+---------+-------+
34 rows in set (0.00 sec)
35 
36 #方法三：在所有欄位後單獨定義primary key
37 create table department3(
38 id int,
39 name varchar(20),
40 comment varchar(100),
41 constraint pk_name primary key(id); #創建主鍵併為其命名pk_name
42 
43 mysql> desc department3;
44 +---------+--------------+------+-----+---------+-------+
45 | Field   | Type         | Null | Key | Default | Extra |
46 +---------+--------------+------+-----+---------+-------+
47 | id      | int(11)      | NO   | PRI | NULL    |       |
48 | name    | varchar(20)  | YES  |     | NULL    |       |
49 | comment | varchar(100) | YES  |     | NULL    |       |
50 +---------+--------------+------+-----+---------+-------+
51 rows in set (0.01 sec)

多列主鍵

 1 ==================多列做主鍵================
 2 create table service(
 3 ip varchar(15),
 4 port char(5),
 5 service_name varchar(10) not null,
 6 primary key(ip,port)
 7 );
 8 
 9 
10 mysql> desc service;
11 +--------------+-------------+------+-----+---------+-------+
12 | Field        | Type        | Null | Key | Default | Extra |
13 +--------------+-------------+------+-----+---------+-------+
14 | ip           | varchar(15) | NO   | PRI | NULL    |       |
15 | port         | char(5)     | NO   | PRI | NULL    |       |
16 | service_name | varchar(10) | NO   |     | NULL    |       |
17 +--------------+-------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
19 
20 mysql> insert into service values
21     -> ('172.16.45.10','3306','mysqld'),
22     -> ('172.16.45.11','3306','mariadb')
23     -> ;
24 Query OK, 2 rows affected (0.00 sec)
25 Records: 2  Duplicates: 0  Warnings: 0
26 
27 mysql> insert into service values ('172.16.45.10','3306','nginx');
28 ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

5.auto_increment （自增約束）

步長increment與起始偏移量offset:auto_increment_increment,auto_increment_offset

3.--------偏移量:auto_increment_offset---------
==============沒有設置偏移量的時候
create table dep(
id int primary key auto_increment,
name char(10)
);
insert into dep(name) values('IT'),('HR'),('EFO');
select * from dep;

================設置自增的時候以10開頭
create table dep1(
id int primary key auto_increment,
name char(10)
)auto_increment = 10;
insert into dep1(name) values('IT'),('HR'),('EFO');
select * from dep1;

===============auto_increment_increment:自增步長
create table dep3(
id int primary key auto_increment,
 name char(10)
);
會話：通過客戶端連到服務端(一次鏈接稱為一次會話)
set session auto_increment_increment = 2; #會話級，只對當前會話有效
set global auto_increment_increment=2; #全局，對所有的會話都有效
insert into dep3(name) values('IT'),('HR'),('SALE'),('Boss');

-----------查看變數----------
show variables like '%auto_in%';#查看變數。只要包含auto_in就都查出來了

=========auto_increment_offset:偏移量+auto_increment_increment:步長===========
註意：如果auto_increment_offset的值大於auto_increment_increment的值，
則auto_increment_offset的值會被忽略

set session auto_increment_offset=2;
set session auto_increment_increment=3;
show variables like '%auto_in%';

create table dep4(
id int primary key auto_increment,
name char(10)
);
insert into dep4(name) values('IT'),('HR'),('SALE'),('Boss');

6.foreign key （外鍵約束）

員工信息表有三個欄位：工號 姓名 部門

公司有3個部門，但是有1個億的員工，那意味著部門這個欄位需要重覆存儲，部門名字越長，越浪費

解決方法：

我們完全可以定義一個部門表

然後讓員工信息表關聯該表，如何關聯，即foreign key

如下圖簡單的表示了一下員工表與部門表的關係，即員工表的(dep_id）要關聯部門表的id欄位

多對一（一個表多條記錄的某一欄位關聯另一張表的唯一一個欄位）:員工有部門，部門又有好多信息，所以
分開建了一張部門表，部門表的id 和員工表裡面
的dep_id相關聯。(dep_id要關聯部門表的id欄位
（註意：1.先建被關聯的表，
        2.被關聯的欄位必須唯一
        3.先給被關聯的表插入記錄
 ）

先建張部門表（被關聯表）
create table dep(
id int not null unique,
#id int primary key auto_increment,
name varchar(50),
comment varchar(100)
);

再建張員工表（關聯表）
create table emp_info(
id int primary key auto_increment,
name varchar(20),
dep_id int,
constraint FK_depid_id foreign key(dep_id) references dep(id) #references :關聯
on delete cascade  #關聯的表刪了，被關聯的表也刪了
on update cascade  #關聯的表修改了，被關聯的表也修改了
);
#先給被關聯的表初始化記錄
insert into dep values
(1,'歐德博愛技術有限事業部','說的好...'),
(2,'艾利克斯人力資源部','招不到人'),
(3,'銷售部','賣不出東西');
# 級聯:級聯有幾個模式
1.嚴格模式(預設的),外鍵有強制約束效果,被關聯欄位不能隨意刪除和修改
2.模式(cascade):外鍵有強制約束效果,被關聯欄位刪除或者修改,關聯他的那麼欄位數據會隨之刪除或者修改
constraint fk_t1_publish foreign key(pid) references publish(id) on delete cascade on update cascade;
3.set null模式: 被關聯欄位刪除時,關聯他的欄位數據會置成null 



insert into emp_info values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'劉飛機',3),
(7,'張火箭',3),
(8,'林子彈',3),
(9,'加特林',3);

#修改
update dep set id =301 where id = 2;
select * from dep;
delect * from em_info;


如果部門解散了，員工也就走吧，就是部門表沒了，
員工表也就沒有了。

運行結果如下圖：

查看創建的表

修改id=301

查看被關聯表和關聯表


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 搭建SFTP伺服器，允許一個或多個用戶擁有一個或多個目錄的rwx許可權
    2019-08-28 1、引言 sftp可以為傳輸文件提供一種安全的網路的加密方法。sftp 與 ftp 有著幾乎一樣的語法和功能。SFTP 為 SSH的其中一部分，是一種傳輸檔案至 Blogger 伺服器的安全方式。其實在SSH軟體包中，已經包含了一個叫作SFTP(Secure File Tran ...
  • Kibana 管理界面使用教程
    使用瀏覽器訪問 ip:5601 預設埠，進入首頁 Discover：日誌管理視圖 Visualize：統計視圖 Dashboard：儀錶視圖 Timelion：時間軸視圖 APM：性能管理視圖 DevTools：開發者命令視圖 Monitoring：健康視圖 Management：管理視圖 使用瀏 ...
  • 彙編語言初探
    彙編語言初探 0x00 參考 "阮一峰的網路日誌之彙編語言入門教程" "ARM彙編語言學習筆記（一） ARM彙編的程式結構" 0x01 要點概述 標準化以後，稱為 assembly language，縮寫為 asm，中文譯為彙編語言。 彙編語言是二進位指令的文本形式，與指令是一一對應的關係。 每一種 ...
  • Linux系統啟動流程（重要!）
    Linux系統啟動流程 從上至下為： BIOS MBR:Boot Code 執行引導程式-GRUB（操作系統） 載入內核 執行init run level 1.BIOS（Basic Input Output System） 基本輸入輸出系統，一般保存在主板上的BIOS的晶元中，負責檢查硬體/查找可啟 ...
  • NUC970 Linux CAN 驅動問題及解決辦法之二
    開發平臺介紹： NUC970 + 內置CAN控制器（雙通道CAN1\CAN2） + 官方Linux_Kernel（少量修改） 名詞： 終端，使用NUC970的硬體 異常表現： 1、當CAN收發器（VP251）斷電時，此時再恢復CAN外部通訊，終端表現為能收到全部的CAN數據，發送函數返回成功，但CA ...
  • Linux文本編輯器的常用命令
    Vi文本編輯器 首先介紹命令模式，命令模式是在進入文本編輯器的時候的當前狀態；輸入模式（插入模式）是在插入文本時候按下的鍵，比如 大小 i 鍵，a鍵、o鍵；底行模式是在命令模式的狀態直接輸入冒號 ":" ，從插入模式到命令模式，按下Esc鍵即可 ①定位游標 命令模式下 向上 k 向下 j 向左 h ...
  • Linux網路配置(10)
    Linux網路配置原理圖（NAT模式） 查看網路IP和網關： CentOS7：ip addr CentOS6：ifconfig Ping測試主機之間網路的連通性：ping [www.baidu.com] 網路環境配置： /etc/sysconfig/network-scripts/ifcfg-eth ...
  • Windows10下載mysql詳解
    mysql版本分為企業版（Enterprise）和社區版（Community），其中社區辦是通過GPL協議授權的開源軟體，可以免費使用，而企業版是需要收費的商業軟體。 mysql官網 https://www.mysql.com/ ，進去點擊 download 下拉滾動到最後，看到 MySQL Com ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類