當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

数据库表操作、数据类型及完整性约束

發布時間：2023/12/13 数据库 29 豆豆

生活随笔收集整理的這篇文章主要介紹了数据库表操作、数据类型及完整性约束小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

數據庫表操作、數據類型及完整性約束

庫操作補充

數據庫命名規則：

可以由字母、數字、下劃線、＠、＃、＄
區分大小寫
唯一性
不能使用關鍵字如 create select
不能單獨使用數字
最長128位

表操作補充

#語法： create table 表名( 字段名1 類型[(寬度) 約束條件], 字段名2 類型[(寬度) 約束條件], 字段名3 類型[(寬度) 約束條件] );#注意： 1. 在同一張表中，字段名是不能相同 2. 寬度和約束條件可選、非必須，寬度指的就是字段長度約束，例如：char(10)里面的10 3. 字段名和類型是必須的

數據類型

sql_mode模式

MySQL的sql_mode合理設置：

sql_mode是個很容易被忽視的變量,默認值是空值,在這種設置下是可以允許一些非法操作的,比如允許一些非法數據的插入。在生產環境必須將這個值設置為嚴格模式,所以開發、測試環境的數據庫也必須要設置,這樣在開發測試階段就可以發現問題。

sql model 常用來解決下面幾類問題：

通過設置sql mode, 可以完成不同嚴格程度的數據校驗，有效地保障數據準備性。
通過設置sql model 為寬松模式，來保證大多數sql符合標準的sql語法，這樣應用在不同數據庫之間進行遷移時，則不需要對業務sql 進行較大的修改。
在不同數據庫之間進行數據遷移之前，通過設置SQL Mode 可以使MySQL 上的數據更方便地遷移到目標數據庫中。

sql_mode常用值如下:

ONLY_FULL_GROUP_BY:

對于GROUP BY聚合操作,如果在SELECT中的列,沒有在GROUP BY中出現,那么這個SQL是不合法的,因為列不在GROUP BY從句中
NO_AUTO_VALUE_ON_ZERO:

該值影響自增長列的插入。默認設置下,插入0或NULL代表生成下一個自增長值。如果用戶希望插入的值為0,而該列又是自增長的,那么這個選項就有用了。
STRICT_TRANS_TABLES:

在該模式下,如果一個值不能插入到一個事務表中,則中斷當前的操作,對非事務表不做限制
NO_ZERO_IN_DATE:

在嚴格模式下,不允許日期和月份為零
NO_ZERO_DATE:

設置該值,mysql數據庫不允許插入零日期,插入零日期會拋出錯誤而不是警告。
ERROR_FOR_DIVISION_BY_ZERO:

在INSERT或UPDATE過程中,如果數據被零除,則產生錯誤而非警告。如果未給出該模式,那么數據被零除時MySQL返回NULL
NO_AUTO_CREATE_USER:

禁止GRANT創建密碼為空的用戶
NO_ENGINE_SUBSTITUTION:

如果需要的存儲引擎被禁用或未編譯,那么拋出錯誤。不設置此值時,用默認的存儲引擎替代,并拋出一個異常
PIPES_AS_CONCAT:

將"||"視為字符串的連接操作符而非或運算符,這和Oracle數據庫是一樣的,也和字符串的拼接函數Concat相類似
ANSI_QUOTES:

啟用ANSI_QUOTES后,不能用雙引號來引用字符串,因為它被解釋為識別符
ORACLE的sql_mode設置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

sql_mode模式設置和修改

方式一：（當前會話生效）先執行select @@sql_mode,復制查詢出來的值并將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除，然后執行set sql_mode = '修改后的值'或者set session sql_mode='修改后的值';

例如：set session sql_mode='STRICT_TRANS_TABLES';改為嚴格模式
方式二：（全局服務生效）先執行select @@global.sql_mode,復制查詢出來的值并將其中的NO_ZERO_IN_DATE,NO_ZERO_DATE刪除，然后執行set global sql_mode = '修改后的值'。
方法三：（永久生效）在mysql的安裝目錄下，配置my.ini文件（蘋果系統my.cnf文件），新增 sql_mode，然后重啟服務
[mysqld] sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER # 數值類型

數值類型

對于整型來說，數據類型后面的寬度并不是存儲長度限制，而是顯示限制，假如：int(8)，那么顯示時不夠8位則用0來填充，夠8位則正常顯示，通過zerofill來測試，存儲長度還是int的4個字節長度。默認的顯示寬度就是能夠存儲的最大的數據的長度，比如：int無符號類型，那么默認的顯示寬度就是int(10)，有符號的就是int(11)，因為多了一個符號，所以我們沒有必要指定整數類型的數據，沒必要指定寬度，因為默認的就能夠將你存的原始數據完全顯示

類型大小范圍（有符號）范圍（無符號）用途

TINYINT	1 字節	(-128，127)	(0，255)	小整數值
SMALLINT	2 字節	(-32 768，32 767)	(0，65 535)	大整數值
MEDIUMINT	3 字節	(-8 388 608，8 388 607)	(0，16 777 215)	大整數值
INT或INTEGER	4 字節	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數值
BIGINT	8 字節	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數值
FLOAT	4 字節	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度浮點數值
DOUBLE	8 字節	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度浮點數值
DECIMAL	對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2	依賴于M和D的值	依賴于M和D的值	小數值

日期類型

類型：DATE，TIME，DATETIME ，IMESTAMP，YEAR

作用：存儲用戶注冊時間，文章發布時間，員工入職時間，出生時間，過期時間等

YEARYYYY（范圍：1901/2155）2018DATEYYYY-MM-DD（范圍：1000-01-01/9999-12-31）例：2018-01-01 TIMEHH:MM:SS（范圍：'-838:59:59'/'838:59:59'）例：12:09:32DATETIMEYYYY-MM-DD HH:MM:SS（范圍：1000-01-01 00:00:00/9999-12-31 23:59:59 Y）例： 2018-01-01 12:09:32TIMESTAMPYYYYMMDD HHMMSS（范圍：1970-01-01 00:00:00/2037 年某時）

字符串類型

類型：char，varchar

作用：名字，信息等等

mysql提供的兩個方法：

? length(字段)：查看該字段數據的字節長度

? char_length(字段)：查看該字段數據的字符長度

char和varchar區別：

char和varchar最大的不同就是一個是固定長度,一個是可變長度.由于是可變長度,因此存儲的是實際字符串再加上一個記錄字符串長度的字節。如果分配給char或varchar列的值超過列的最大長度,則對值進行裁剪.

varchar(M)和char(M),M都表示字符數.varchar的最大長度為65535個字節(字符長度為utf8下21845),不同的編碼所對應的最大可存儲的字符數不同.char最多可以存放255個字符,不同的編碼最大可用字節數不同.

類型大小用途

CHAR	0-255字節	定長字符串
VARCHAR	0-65535 字節	變長字符串
TINYBLOB	0-255字節	不超過 255 個字符的二進制字符串
TINYTEXT	0-255字節	短文本字符串
BLOB	0-65 535字節	二進制形式的長文本數據
TEXT	0-65 535字節	長文本數據
MEDIUMBLOB	0-16 777 215字節	二進制形式的中等長度文本數據
MEDIUMTEXT	0-16 777 215字節	中等長度文本數據
LONGBLOB	0-4 294 967 295字節	二進制形式的極大文本數據
LONGTEXT	0-4 294 967 295字節	極大文本數據

枚舉類型和集合類型

字段的值只能在給定范圍中選擇，如單選框，多選框，如果你在應用程序或者前端不做選項限制，在MySQL的字段里面也能做限制

枚舉類型（enum） An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) 示例： 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'); 集合類型（set） A SET column can have a maximum of 64 distinct members. 示例： CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

完整性約束

約束條件與數據類型的寬度一樣，都是可選參數

作用：用于保證數據的完整性和一致性

PRIMARY KEY (PK) 標識該字段為該表的主鍵，可以唯一的標識記錄 FOREIGN KEY (FK) 標識該字段為該表的外鍵 NOT NULL 標識該字段不能為空 UNIQUE KEY (UK) 標識該字段的值是唯一的 AUTO_INCREMENT 標識該字段的值自動增長（整數類型，而且為主鍵） DEFAULT 為該字段設置默認值UNSIGNED 無符號 ZEROFILL 使用0填充

not null 不為空

create table t1(name char(10) not null);

注意一點：如果是非嚴格模式，int類型不傳值的話會默認為0，因為null不是int類型的，字段是int類型，所以他會自動將null變為0

default 默認值

create table t1(name char(10) not null default 'xx');

unique 唯一,字段數據不能重復

create table t1(name char(10) unique);

primary key 主鍵約束效果:不為空且唯一 not null + unique

create table t1(id int primary key);

auto_increment 自增

create table t1(id int primary key auto_increment); 前面必須是個key

foreign key

foreign key（外鍵其實就是標明表和表之間的關系，表和表之間如果有關系的話就三種：一對一，多對一，多對多)

1、多對一

create table Anime(id int primary key auto_increment,name char(10)); insert into Anime (name) values ('龍珠'),('火影忍者'),('秦時明月'); create table people(id int primary key auto_increment,name char(10),aid int not null,foreign key(aid) references Anime(id)); insert into people (name,aid) values ('蓋聶',3),('天明',3),('鳴人',2),('悟空',1),('小櫻',2); select * from Anime; select * from people;

2、一對一

create table skill(id int primary key auto_increment,name char(10)); insert into skill (name) values('烤山雞'),('螺旋丸'),('龜派氣功'),('怪力'),('地爆天星'),('百步飛劍'); create table people(id int primary key auto_increment,name char(10),sid int not null,foreign key(sid) references skill(id)); insert into people (name,sid) values ('蓋聶',6),('天明',1),('鳴人',2),('悟空',3),('小櫻',4); select * from skill; select * from people;

3、多對多

`mysql create table people(pid int primary key auto_increment,name char(10)); insert into people (name) values ('鳴人'),('自來也'),('木葉丸'),('阿斯瑪'); create table skill(sid int primary key auto_increment,name char(10)); insert into skill (name) values ('螺旋丸'),('影分身'),('手里劍'),('灰燼燒'),('仙人模式'); create table peopletoskill(id int primary key auto_increment,pid int,sid int,foreign key(pid) references people(pid),foreign key(sid) references skill(sid)); insert into peopletoskill (pid,sid) values (1,1),(1,2),(1,3),(1,5),(2,1),(2,5),(3,1),(3,2),(3,4),(4,3),(4,4); select * from people; select * from skill; desc peopletoskill; select * from peopletoskill;

轉載于:https://www.cnblogs.com/lifangzheng/p/11438235.html

總結

以上是生活随笔為你收集整理的数据库表操作、数据类型及完整性约束的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。