中山市原創(chuàng)動(dòng)力網(wǎng)絡(luò)科技有限公司
創(chuàng)您所想與您同在
免費(fèi)互聯(lián)網(wǎng)品牌營銷咨詢服務(wù)
立刻咨詢
13450931319來源： Onion_cy 發(fā)布時(shí)間：2018-11-24 10:00:01 閱讀量：1563
目錄
1、創(chuàng)建表的完整操作
2、mysql中的數(shù)據(jù)類型
3、約束條件
1、創(chuàng)建表的完整操作
創(chuàng)建表的完整語法：
create table 表名(
id int primary key auto_increment,
字段1 類型[(寬度) 約束條件],
字段1 類型[(寬度) 約束條件],
字段1 類型[(寬度) 約束條件],
...
);
注意問題：
1、字段名不能重復(fù)
2、字段的類型必須要有，但是寬度與約束條件可選
3、最后一個(gè)字段后不能加逗號(hào)
2、mysql中的數(shù)據(jù)類型
為什么要給數(shù)據(jù)分類?
1.描述數(shù)據(jù)更加準(zhǔn)確
2.節(jié)省內(nèi)存空間
1.數(shù)值類型
整形
整數(shù)類型：TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：存儲(chǔ)年齡，等級(jí)，id，各種號(hào)碼等
常用的整形類型介紹:
tinyint[(m)] [unsigned] [zerofill]
小整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-128 ～ 127
無符號(hào)：
0～ 255
PS： MySQL中無布爾值，使用tinyint(1)構(gòu)造。
========================================
int[(m)][unsigned][zerofill]
整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-2147483648 ～ 2147483647
無符號(hào)：
0 ～ 4294967295
========================================
bigint[(m)][unsigned][zerofill]
大整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-9223372036854775808 ～ 9223372036854775807
無符號(hào)：
0～ 18446744073709551615
整形的使用:
默認(rèn)的整形在創(chuàng)造出來時(shí)都是有符號(hào)的
create table t1(x tinyint);
create table t2(x tinyint unsigned); 在創(chuàng)建的時(shí)候可以指定整形的類型和約束條件
create table t5(x int(8) unsigned zerofill); 顯示時(shí)，不夠8位用0填充，如果超出8位則正常顯示
整形類型的介紹:以int為例
int的存儲(chǔ)寬度是4個(gè)Bytes，即32個(gè)bit，即2**32
無符號(hào)最大值為：4294967296-1
有符號(hào)最大值：2147483648-1
有符號(hào)和無符號(hào)的最大數(shù)字需要的顯示寬度均為10，而針對有符號(hào)的最小值則需要11位才能顯示完全，所以int類型默認(rèn)的顯示寬度為11是非常合理的
最后：整形類型，其實(shí)沒有必要指定顯示寬度，使用默認(rèn)的就可以
注意：為該類型指定寬度時(shí)，僅僅只是指定查詢結(jié)果的顯示寬度，與存儲(chǔ)范圍無關(guān),其實(shí)我們完全沒必要為整數(shù)類型指定顯示寬度，使用默認(rèn)的就可以了,默認(rèn)的顯示寬度，都是在最大值的基礎(chǔ)上加1
整形的存儲(chǔ)范圍詳細(xì)說明:
有符號(hào)和無符號(hào)的區(qū)別,有符號(hào)的需要占用一個(gè)比特位來表示正負(fù)
浮點(diǎn)型
浮點(diǎn)型共有三種類型:float、double、decimal
create table t1(x float(255,30));
create table t1(x double(255,30));
create table t1(x decimal(65,30));
float與double能支持的十進(jìn)制數(shù)的總位數(shù)都是255，其中小數(shù)都可以達(dá)到30位，但double類型的精度比float更高
decimal能支持的十進(jìn)制數(shù)的總位數(shù)是65，其中小數(shù)都可以達(dá)到30位，但ddecimal類型的精度比float和double更高，
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
M 表示總長度 D 表示小數(shù)部分的長度
長度表示不是數(shù)據(jù)存儲(chǔ)范圍 而是字符長度
例如:10.12 總長為4 小數(shù)部分為2
定義：
單精度浮點(diǎn)數(shù)（非準(zhǔn)確小數(shù)值），m是數(shù)字總個(gè)數(shù)，d是小數(shù)點(diǎn)后個(gè)數(shù)。m最大值為255，d最大值為30
有符號(hào)范圍：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
無符號(hào)范圍：
1.175494351E-38 to 3.402823466E+38
精確度：
**** 隨著小數(shù)的增多，精度變得不準(zhǔn)確 ****
適用于大部分場景
======================================
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定義：
雙精度浮點(diǎn)數(shù)（非準(zhǔn)確小數(shù)值），m是數(shù)字總個(gè)數(shù)，d是小數(shù)點(diǎn)后個(gè)數(shù)。m最大值為255，d最大值為30
有符號(hào)范圍：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
無符號(hào)范圍：
2.2250738585072014E-308 to 1.7976931348623157E+308
精確度：
****隨著小數(shù)的增多，精度比float要高，但也會(huì)變得不準(zhǔn)確 ****
適用于計(jì)算要求精度較高的場景
======================================
decimal[(m[,d])] [unsigned] [zerofill]
定義：
準(zhǔn)確的小數(shù)值，m是數(shù)字總個(gè)數(shù)（負(fù)號(hào)不算），d是小數(shù)點(diǎn)后個(gè)數(shù)。 m最大值為65，d最大值為30。
精確度：
**** 隨著小數(shù)的增多，精度始終準(zhǔn)確 ****
對于精確數(shù)值計(jì)算時(shí)需要用此類型
decaimal能夠存儲(chǔ)精確值的原因在于其內(nèi)部按照字符串存儲(chǔ)。
適用于科學(xué)計(jì)算和銀行的資金管理系統(tǒng)中
浮點(diǎn)型驗(yàn)證
mysql> create table t1(x float(255,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)
mysql> create table t2(x double(255,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)
mysql> create table t3(x decimal(65,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
rows in set (0.00 sec)
mysql> insert into t1 values(1.1111111111111111111111111111111); #小數(shù)點(diǎn)后31個(gè)1
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(1.1111111111111111111111111111111);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1.1111111111111111111111111111111);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t1; #隨著小數(shù)的增多，精度開始不準(zhǔn)確
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
row in set (0.00 sec)
mysql> select * from t2; #精度比float要準(zhǔn)確點(diǎn)，但隨著小數(shù)的增多，同樣變得不準(zhǔn)確
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
row in set (0.00 sec)
mysql> select * from t3; #精度始終準(zhǔn)確,d為30，于是只留了30位小數(shù)
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
row in set (0.00 sec)
小結(jié):
相同點(diǎn)
1、對于三者來說，都能存放30位小數(shù)，
不同點(diǎn)：
1、精度的排序從低到高：float,double,decimal
2、float與double類型能存放的整數(shù)位比decimal更多
3.字符串類型
char、varchar
創(chuàng)建字符串類型:(字符串在創(chuàng)建時(shí)varchar必須制定字符長度,char有默認(rèn)長度)
create table t1(name char(6))
create table t1(name varchar(6))
注意：char和varchar括號(hào)內(nèi)的參數(shù)指的都是存儲(chǔ)的最大字符長度,
char類型：定長，簡單粗暴，浪費(fèi)空間，存取速度快
字符長度范圍：0-255（一個(gè)中文是一個(gè)字符，是utf8編碼的3個(gè)字節(jié)）
存儲(chǔ)：
存儲(chǔ)char類型的值時(shí)，會(huì)往右填充空格來滿足長度
例如：指定長度為10，存>10個(gè)字符則報(bào)錯(cuò)，存<10個(gè)字符則用空格填充直到湊夠10個(gè)字符存儲(chǔ)
檢索：
在檢索或者說查詢時(shí)，查出的結(jié)果會(huì)自動(dòng)刪除尾部的空格，除非我們打開pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
varchar類型：變長，精準(zhǔn)，節(jié)省空間，存取速度慢
字符長度范圍：0-65535（如果大于21845會(huì)提示用其他類型 。mysql行最大限制為65535字節(jié)，字符編碼為utf-8
存儲(chǔ)：
varchar類型存儲(chǔ)數(shù)據(jù)的真實(shí)內(nèi)容，不會(huì)用空格填充，如果'ab ',尾部的空格也會(huì)被存起來
強(qiáng)調(diào)：varchar類型會(huì)在真實(shí)數(shù)據(jù)前加1-2Bytes的前綴，該前綴用來表示真實(shí)數(shù)據(jù)的bytes字節(jié)數(shù)（1-2Bytes最大表示65535個(gè)數(shù)字，正好符合mysql對row的最大字節(jié)限制，即已經(jīng)足夠使用）
如果真實(shí)的數(shù)據(jù)<255bytes則需要1Bytes的前綴（1Bytes=8bit 2**8最大表示的數(shù)字為255）
如果真實(shí)的數(shù)據(jù)>255bytes則需要2Bytes的前綴（2Bytes=16bit 2**16最大表示的數(shù)字為65535）
檢索：
尾部有空格會(huì)保存下來，在檢索或者說查詢時(shí)，也會(huì)正常顯示包含空格在內(nèi)的內(nèi)容
字符串類型的mysql詳解: https://dev.mysql.com/doc/refman/5.7/en/char.html
length：查看字節(jié)數(shù)
char_length:查看字符數(shù)
字符串類型在查詢時(shí)的特殊之處:
1. char填充空格來滿足固定長度，但是在查詢時(shí)卻會(huì)自動(dòng)的刪除尾部的空格（裝作自己好像沒有浪費(fèi)過空間一樣），但是可以通過修改sql_mode讓其現(xiàn)出原形,在讀出結(jié)果的時(shí)候自動(dòng)去掉末尾的空格,因?yàn)槟┪驳目崭裨谝韵碌膱鼍爸惺菬o用的:
select * from t14 where name="lxx"; # name字段明確地等于一個(gè)值，該值后填充空格是沒有用
select * from t14 where name like "lxx"; # name字段模糊匹配一個(gè)值，該值后填充空格是有用的
2. 雖然 CHAR 和 VARCHAR 的存儲(chǔ)方式不太相同,但是對于兩個(gè)字符串的比較,都只比 較其值,忽略 CHAR 值存在的右填充,即使將 SQL _MODE 設(shè)置為 PAD_CHAR_TO_FULL_ LENGTH 也一樣,,但這不適用于like,like匹配是一種匹配方式,在小節(jié)末尾 我們會(huì)介紹like和=的異同.
SQL語句中l(wèi)ike和=的區(qū)別(點(diǎn)擊前往)
SQL語句中的模糊匹配(點(diǎn)擊前往)
4.集合和枚舉類型
字段的值只能在給定范圍中選擇，如單選框，多選框
enum 單選 只能在給定的范圍內(nèi)選一個(gè)值，如性別 sex 男male/女female
set 多選 在給定的范圍內(nèi)可以選擇一個(gè)或一個(gè)以上的值（愛好1,愛好2,愛好3...）
示例:
mysql> create table consumer(
-> name varchar(50),
-> sex enum('male','female'),
-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范圍內(nèi)，多選一
-> hobby set('play','music','read','study') #在指定范圍內(nèi)，多選多
-> );
mysql> insert into consumer values
-> ('egon','male','vip5','read,study'),
-> ('alex','female','vip1','girl');
mysql> select * from consumer;
+------+--------+-------+------------+
| name | sex | level | hobby |
+------+--------+-------+------------+
| egon | male | vip5 | read,study |
| alex | female | vip1 | |
+------+--------+-------+------------+
驗(yàn)證
5.日期類型
DATE TIME DATETIME TIMESTAMP YEAR
作用：存儲(chǔ)用戶注冊時(shí)間，文章發(fā)布時(shí)間，員工入職時(shí)間，出生時(shí)間，過期時(shí)間等
日期類型介紹:()中為范圍
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 年某時(shí)）
日期類型的使用
create table student(
id int,
name char(16),
born_year year,
birth date,
class_time time,
reg_time datetime
);
insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');
注意:
1. 單獨(dú)插入時(shí)間時(shí)，需要以字符串的形式，按照對應(yīng)的格式插入
2. 插入年份時(shí)，盡量使用4位值
3. 插入兩位年份時(shí)，<=69，以20開頭，比如50, 結(jié)果2050
>=70，以19開頭，比如71，結(jié)果1971
日期類型中datetime和timestamp的區(qū)別
在實(shí)際應(yīng)用的很多場景中，MySQL的這兩種日期類型都能夠滿足我們的需要，存儲(chǔ)精度都為秒，但在某些情況下，會(huì)展現(xiàn)出他們各自的優(yōu)劣。下面就來總結(jié)一下兩種日期類型的區(qū)別。
1.DATETIME的日期范圍是1001——9999年，TIMESTAMP的時(shí)間范圍是1970——2038年。
2.DATETIME存儲(chǔ)時(shí)間與時(shí)區(qū)無關(guān)，TIMESTAMP存儲(chǔ)時(shí)間與時(shí)區(qū)有關(guān)，顯示的值也依賴于時(shí)區(qū)。在mysql服務(wù)器，操作系統(tǒng)以及客戶端連接都有時(shí)區(qū)的設(shè)置。
3.DATETIME使用8字節(jié)的存儲(chǔ)空間，TIMESTAMP的存儲(chǔ)空間為4字節(jié)。因此，TIMESTAMP比DATETIME的空間利用率更高。
4.DATETIME的默認(rèn)值為null；TIMESTAMP的字段默認(rèn)不為空（not null）,默認(rèn)值為當(dāng)前時(shí)間（CURRENT_TIMESTAMP），如果不做特殊處理，并且update語句中沒有指定該列的更新值，則默認(rèn)更新為當(dāng)前時(shí)間。
datetime與timestamp的區(qū)別
3、約束條件
約束條件是一種對數(shù)據(jù)限制
約束的作用是?
為了保證數(shù)據(jù)的正確性,完整性
例如要存儲(chǔ)密碼 char(20) 只限制了類型和長度, 無法保證數(shù)據(jù)的正確性
額外的約束
語法:
創(chuàng)建時(shí)指定約束
create table 表名稱(字段名 類型(長度) 約束名稱1 約束名稱n,....)
后期修改的方式添加 約束
alter table 表名稱 modify 字段名 類型(長度) 約束名稱1 約束名稱n,....
約束條件與數(shù)據(jù)類型的寬度一樣，都是可選參數(shù)
作用：用于保證數(shù)據(jù)的完整性和一致性
mysql中約束條件主要分為以下幾種:
PRIMARY KEY (PK) 標(biāo)識(shí)該字段為該表的主鍵，可以唯一的標(biāo)識(shí)記錄
FOREIGN KEY (FK) 標(biāo)識(shí)該字段為該表的外鍵
NOT NULL 標(biāo)識(shí)該字段不能為空
UNIQUE KEY (UK) 標(biāo)識(shí)該字段的值是唯一的
AUTO_INCREMENT 標(biāo)識(shí)該字段的值自動(dòng)增長（整數(shù)類型，而且為主鍵）
DEFAULT 為該字段設(shè)置默認(rèn)值
UNSIGNED 無符號(hào)
ZEROFILL 使用0填充
1. 是否允許為空，默認(rèn)允許為NULL，可設(shè)置NOT NULL，字段不允許為空，必須賦值
2. 字段是否有默認(rèn)值，初始的默認(rèn)值是NULL，如果插入記錄時(shí)不給字段賦值，此字段使用默認(rèn)值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必須為正值（無符號(hào)） 不允許為空 默認(rèn)是20
3. 是否是key
主鍵 primary key
外鍵 foreign key
索引 (index,unique...)
接下來對約束條件進(jìn)行詳細(xì)的介紹
NOT NULL和DEFAULT(默認(rèn)值約束)
NOT NULL(非空約束)
限制該字段的值不能為空
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
DEFAULT(默認(rèn)值約束)
默認(rèn)值，創(chuàng)建列時(shí)可以指定默認(rèn)值，當(dāng)插入數(shù)據(jù)時(shí)如果未主動(dòng)設(shè)置，則自動(dòng)添加默認(rèn)值
設(shè)置id字段有默認(rèn)值后，則無論id字段是null還是not null，都可以插入空，插入空默認(rèn)填入default指定的默認(rèn)值
create table tb1(
id int not null defalut 2,
num int not null
)
驗(yàn)證:
==================not null====================
mysql> create table t1(id int); #id字段默認(rèn)可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空
mysql> create table t2(id int not null); #設(shè)置字段id不為空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value
==================default====================
#設(shè)置id字段有默認(rèn)值后，則無論id字段是null還是not null，都可以插入空，插入空默認(rèn)填入default指定的默認(rèn)值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;
UNIQUE(唯一性約束)
限制該字段的值是唯一的,不能出現(xiàn)重復(fù)
分為單列唯一和聯(lián)合唯一(聯(lián)合唯一不推薦使用)
單列唯一:
只限制這一個(gè)字段,保證該字段中的數(shù)據(jù)類型是具有唯一性的
create table t16(
id int unique,
name char(16)
);
設(shè)置單列唯一的第二種辦法(了解)
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);
聯(lián)合唯一:
聯(lián)合唯一表示兩個(gè)或多個(gè)字段合起來是唯一的,意思是他們的組合形式是具有唯一性的,但不代表單列不會(huì)出現(xiàn)相同的值
create table server(
id int unique,
ip char(15),
port int,
unique(ip,port)
);
我們在這里可以思考一下, 如果not null unique一起使用回出現(xiàn)什么樣的效果(單列情況下)?
create table t1(id int not null unique); 他們一起使用就會(huì)使得該字段的值不為空且唯一
PRIMARY KEY(很重要!!!!)
首先簡單了解一下key:key是mysql中一種特殊的數(shù)據(jù)結(jié)構(gòu)中,是用來加速查詢的
mysql中存在一種專門的數(shù)據(jù)結(jié)構(gòu)，叫key，又稱為索引，通過該數(shù)據(jù)結(jié)構(gòu)可以減少io次數(shù)，從而加速查詢效率
我們目前為止已經(jīng)學(xué)過的key類型:
index key ： 只有加速查詢的效果，沒有約束的功能
unique key：不僅有加速查詢的效果，還附加了一種約束的功能
primary key：不僅有加速查詢的效果，還附加了一種約束的功能，并且innodb存儲(chǔ)引擎會(huì)按照主鍵字段的值來組織表中所有的數(shù)據(jù)，所 以一種inndob表中必須有、而且只能有一個(gè)主鍵，通常是該表的id字段
primary key:單單從約束角度去看，primary key就等同于not null unique,但是他們是有區(qū)別的:
UNIQUE NOT NULL 與 主鍵的區(qū)別
UNIQUE NOT NULL 不能被其他表引用 (不能作為其它表的外鍵)
UNIQUE NOT NULL 約束一個(gè)表中可以有多個(gè) 但是主鍵只能有一個(gè)
強(qiáng)調(diào)（******）
1、一張表中必須有，并且只能有一個(gè)主鍵
2、一張表中都應(yīng)該有一個(gè)id字段，而且應(yīng)該把id字段做成主鍵
create table t17(
id int primary key,
name char(16),
age int,
sex char(6)
)engine=innodb;
當(dāng)我們建立一張表時(shí),mysql會(huì)首先看創(chuàng)建的字段中有沒有那個(gè)字段被指定為主鍵,如果有就直接使用,如果沒有回從上到下依次尋找一個(gè)不為空且唯一的一個(gè)字段作為主鍵的替代品作為主鍵,如果沒有不為空且唯一的主鍵,就會(huì)創(chuàng)建一個(gè)隱藏的字段作為主鍵,但是隱藏字段不可見,所以就是去了加速查詢的效果,也就失去了存儲(chǔ)引擎的很大的優(yōu)勢,所以,在創(chuàng)建表格的時(shí)候一定要指定一個(gè)字段作為主鍵,一般表格中都會(huì)有id字段,我們習(xí)慣將id字段作為主鍵
聯(lián)合主鍵(不推薦使用)
create table t19(
ip char(15),
port int,
primary key(ip,port)
);
AUTO_INCREMENT(自增長)
該約束條件,會(huì)有一個(gè)初始值,并且在向表中添加字段的時(shí)候會(huì)自動(dòng)增長(初始步長為1),在使用的時(shí)候可以不指定也可以輸入空,
通常auto_increment和primary一起使用
primary key auto_increment
create table t20(
id int primary key auto_increment,
name char(16)
)engine=innodb;
auto_increment注意點(diǎn)：
1、通常與primary key連用，而且通常是給id字段加
2、auto_incremnt只能給被定義成key（unique key，primary key）的字段加
---------------------
在線
客服
客服
熱線
7*24小時(shí)客服服務(wù)熱線
關(guān)注
微信
關(guān)注官方微信
微信登錄
QQ登錄
微博登錄
售前咨詢