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