搞定PHP面試 - MySQL基礎知識點整理 - 數據類型和數據表管理

elarity 發布于2019-07-01 14:08 / 1739人閱讀

摘要：基礎知識點整理數據表管理數據類型數值數據類型數值數據類型存儲數值。支持多種數值數據類型，每種存儲的數值具有不同的取值范圍。是定長字符串，會直接根據定義字符串時指定的長度分配足夠的空間。

MySQL基礎知識點整理 - 數據表管理 〇、數據類型 1. 數值數據類型

數值數據類型存儲數值。
MySQL支持多種數值數據類型，每種存儲的數值具有不同的取值范圍。

整數

類型	大小	范圍（有符號）	范圍（無符號）
TINYINT	1 字節	(-128，127)	(0，255)
SMALLINT	2 字節	(-32768，32767)	(0，65535)
MEDIUMINT	3 字節	(-8388608，8388607)	(0，16777215)
INT或INTEGER	4 字節	(-2147483648，2147483647)	(0，4294967295)
BIGINT	8 字節	(-2^63^, 2^63^ - 1)	(0，2^64^)

長度 int(n) 與 zerofill

int(n) 只影響顯示字符的寬度，不限制數值的合法范圍。
int(3) 依然可以存儲 123456789 這么大的數值。
若設置了 zerofill 屬性，當 int(3) 存儲 12 時，會在前面補0，補足3位。即 012；當 int(5) 存儲 12 時，會在前面補三個0，補足5位。即 00012

有符號或無符號

所有數值數據類型（除 BIT 和 BOOLEAN 外）都可以有符號或無符號。有符號數值列可以存儲正或負的數值，無符號數值列只能存儲正數。默認情況為有符號，但如果你知道自己不需要存儲負值，可以使用 UNSIGNED 關鍵字，這樣做將允許你存儲兩倍大小的值。

小數

類型	大小	范圍（有符號）	范圍（無符號）
FLOAT	4 字節	(-3.402823466 E+38，-1.175494351 E-38)，0，(1.175494351 E-38，3.402823466351 E+38)	0，(1.175 494351 E-38，3.402823466 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的值

DECIMAL最常用的用法就是用來存儲貨幣，例如  DECIMAL(8, 2)。
DECIMAL還可以用于存儲比BIGINT還大的整數以及精確的小數。

2. 串數據類型

類型	大小	用途
CHAR	0 - 255 個字符	定長字符串
VARCHAR	0 - 65535 字節	變長字符串
TINYTEXT	0 - 255 字節	短文本字符串
TEXT	0 - 65535 字節	長文本數據（<64KB）
MEDIUMTEXT	0 - 16777215 字節	中等長度文本數據（<16MB）
LONGTEXT	0 - 4294967295 字節	極大文本數據（<4GB）

從 MySQL4.1 版本開始，char(n) 和 varchar(n) 中的 n 指字符長度，不再表示之前版本的字節長度。也就是說在不同字符集下，char類型列的內部存儲可能不是定長數據。

CHAR*

CHAR 是定長字符串，會直接根據定義字符串時指定的長度分配足夠的空間。
CHAR 適合存儲所有值長度相同的字符串或很短的字符串。

VARCHAR

VARCHAR 的最大長度是65535個字節，而 varchar(n) 中的 n 指字符長度，因此，n 的最大值是由當前字段的字符集決定的。當字符集是 utf8 時，n 的最大值為 21845。當字符集是 utf8mb4 時，n 的最大值為 16383。（但是實際上MySQL要求一個行的定義長度不能超過65535個字節，因此，除非表中只有這一個字段，否則 n 的值達不到上述的最大值）。

VARCHAR 使用1-2個額外字節記錄字符串長度，列長度小于等于255個字符時，使用1個字節記錄，否則使用2個字節。

最佳實踐

對于經常變更的數據， CHAR 比 VARCHAR 更好，CHAR 的磁盤空間利用率更高，不容易產生碎片。

當列中數據的長度相同時，選擇 CHAR；當列中數據長度參差不齊時，選擇 VARCHAR。

對于非常短的列，CHAR 比 VARCHAR 在存儲上更有效率。

只分配真正需要的空間，更長的列會消耗更多的內存。

盡量避免使用 BLOB/TEXT 類型，查詢時會使用臨時表，導致嚴重的性能開銷。如果一定要用，建議多帶帶建表存儲該字段。

3. 二進制數據類型

類型	大小	用途
TINYBLOB	0 - 255 字節	不超過 255 個字符的二進制字符串
BLOB	0 - 65535 字節	二進制形式的長文本數據（<64KB）
MEDIUMBLOB	0 - 16777215 字節	二進制形式的中等長度文本數據（<16MB）
LONGBLOB	0 - 4294967295 字節	二進制形式的極大文本數據（<4GB）

4. 日期和時間類型

類型	大小(字節)	范圍	格式	用途
YEAR	1	1901 / 2155	YYYY	年份值
DATE	3	1000-01-01 / 9999-12-31	YYYY-MM-DD	日期值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP	4	1970-01-01 00:00:00/2038 結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和時間值，時間戳

最佳實踐

盡量使用 TIMESTAMP，比 DATETIME 的空間利用率高。

一、創建數據表 CREATE TABLE

使用CREATE TABLE 創建表，必須給出下列信息：

表的名字，在關鍵字 CREATE TABLE 之后給出；

表中字段的名字和定義，用逗號分隔。

以下為創建MySQL數據表的SQL通用語法：

CREATE TABLE table_name (
    column1 datatype [NULL|NOT NULL] [DEFAULT ],
    column2 datatype,
);

實例

創建用戶表

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `username` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
      `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT "密碼",
      `status` tinyint(3) unsigned NOT NULL DEFAULT "0" COMMENT "狀態",
      `created_at` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `unq_email` (`email`) USING BTREE,
     KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT "用戶表";

實例解析

如果數據庫中不存在 user 表時，創建該表。存儲引擎為 InnoDB，默認字符集為utf8
ENGINE 設置存儲引擎，CHARSET 設置編碼。

CREATE TABLE IF NOT EXISTS `user` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT "用戶表"

創建名為 id 的字段，整型，非負數，不能為空，自增。

如果你不想字段為 NULL 可以設置字段的屬性為 NOT NULL，在操作數據庫時如果輸入該字段的數據為NULL ，就會報錯。
AUTO_INCREMENT定義列為自增的屬性，一般用于主鍵，數值會自動加1。

`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT

創建名為 username 的字段，字符串類型，最大長度為190個字符，字符集為 utf8mb4，不能為空

`username` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

將字段 id 設為主鍵，使用 BTREE 索引

PRIMARY KEY關鍵字用于定義列為主鍵。可以使用多列來定義主鍵，列間以逗號分隔。

PRIMARY KEY (`id`) USING BTREE,

為字段 email 添加唯一索引，索引名稱為 unq_email
設置了唯一索引的字段不能出現重復的值，但是如果字段可以為 null，則允許出現多個 null 值。

UNIQUE KEY `unq_email` (`email`) USING BTREE

為字段 username 添加普通索引，索引名稱為 idx_username

KEY `idx_username` (`username`)

二、查看數據表 1. 查看數據庫中的所有數據表

SHOW TABLES 用于查看數據庫中的所有數據表。

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.07 sec)

2. 查看數據表的建表SQL語句

SHOW CREATE TABLE 用于查看指定數據表的建表SQL語句

語法：

SHOW CREATE TABLE table_name

查看 user 表的建表語句

mysql> SHOW CREATE TABLE `user`G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT "密碼",
  `status` tinyint(3) unsigned NOT NULL DEFAULT "0" COMMENT "狀態",
  `created_at` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `unq_email` (`email`) USING BTREE,
  KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT="用戶表"
1 row in set (0.00 sec)

3. 查看數據表結構 DESCRIBE 和 DESC

DESCRIBE 可用于查看表結構，DESC 是 DESCRIBE 的縮寫。

語法：

DESCRIBE table_name

查看 user 表的表結構

mysql> DESCRIBE `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

EXPLAIN

EXPLAIN 也可以用于查看表結構。

語法：

EXPLAIN table_name

DESCRIBE 和 EXPLAIN 語句是同義詞，實際上在平時使用過程中 DESCRIBE 多用于獲取表結構的信息，而 EXPLAIN 多用于獲取SQL語句的執行計劃。

查看 user 表的表結構

mysql> EXPLAIN `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)

三、修改數據表 1. 重命名數據表

語法

RENAME TABLE old_name TO new_name;

將 user 表重命名為 consumer ，再改回 user

mysql> RENAME TABLE `user` TO `consumer`;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| consumer       |
+----------------+
1 row in set (0.06 sec)

mysql> RENAME TABLE `consumer` TO `user`;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.05 sec)

2. 增加字段

語法

ALTER TABLE table_name ADD column_name column_type

給 user 表添加一個字段 intro

mysql> ALTER TABLE `user` ADD `intro` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT "簡介" AFTER `email`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| intro      | varchar(255)        | YES  |     | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.06 sec)

3. 修改字段 修改字段名和屬性

語法

ALTER TABLE table_name CHANGE old_name new_name column_type;

將 user 表的 intro 字段名改為 about

mysql> ALTER TABLE `user` CHANGE `intro` `about` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT "簡介";
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| about      | varchar(255)        | YES  |     | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.07 sec)

修改字段屬性

語法

ALTER TABLE table_name MODIFY column_name column_type;

將 user 表的 about 字段字符串最大長度改為200個字符

mysql> ALTER TABLE `user` MODIFY `about` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT "簡介";
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| about      | varchar(200)        | YES  |     | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.09 sec)

4. 刪除字段

語法

ALTER TABLE table_name DROP COLUMN column_name;

從 user 表中刪除 about 字段

mysql> ALTER TABLE `user` DROP COLUMN `about`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.08 sec)

5. 添加索引

語法

ALTER TABLE `user` ADD [ KEY | UNIQUE KEY | PRIMARY KEY] idx_name (column_name);

為 user 表中的 created_at 字段添加普通索引，索引名為 idx_created_at

mysql> ALTER TABLE `user` ADD KEY `idx_created_at` (`created_at`);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  | UNI | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   | MUL | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.07 sec)

6. 刪除索引

語法

ALTER TABLE `user` DROP KEY idx_name;

刪除 user 表中的 created_at 和 email 兩個字段的索引，索引名為 idx_created_at 和 unq_email

mysql> ALTER TABLE `user` DROP KEY `idx_created_at`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `user` DROP KEY `unq_email`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC `user`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| username   | varchar(190)        | NO   | MUL | NULL    |                |
| email      | varchar(255)        | YES  |     | NULL    |                |
| password   | varchar(255)        | YES  |     | NULL    |                |
| status     | tinyint(3) unsigned | NO   |     | 0       |                |
| created_at | int(11) unsigned    | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)

四、刪除數據表

可以使用 DROP TABLE 命令刪除一個或者多個數據表。

在使用  DROP TABLE 刪除數據表時，要刪除的數據表必須存在，否則會報錯。

1. 刪除一個數據表

DROP TABLE tablename

2. 批量刪除數據表

DROP TABLE tablename1,tablename2,tablename3

刪除 user 表

mysql> DROP TABLE `user`;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC `user`;
1146 - Table "test.user" doesn"t exist

mysql> SHOW TABLES;
Empty set

云服務器 GPU云服務器 c語言數據類型整理 php數據類型和變量 php數據類型和長度 Mysql基礎知識

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/31836.html

搞定PHP面試 - MySQL基礎知識點整理 - 存儲引擎

摘要：支持崩潰后的安全恢復。的使用場景更新密集的表存儲引擎特別適合處理多重并發的更新請求。外鍵約束支持外鍵的存儲引擎只有。引擎是及之前版本的默認存儲引擎。文件存儲表的索引。引擎存儲引擎是引擎的變種。 MySQL基礎知識點整理 - 存儲引擎 0. 查看 MySQL 支持的存儲引擎可以在 mysql 客戶端中，使用 show engines; 命令可以查看MySQL支持的引擎： mysql> ...

whatsns 2019-07-01 14:04 評論0 收藏0

發表評論

登陸后可評論

0條評論

elarity

男|高級講師

我要關注我要私信

TA的文章

iWebFusion：$99/月-Dual E5-2670,128G內存,500G SSD硬盤,20

閱讀 600·2021-10-08 10:20
Capoae惡意軟件使用多個漏洞攻擊Linux系統和Web應用程序

閱讀 1490·2021-09-23 11:22
前端實用小工具

閱讀 3214·2019-08-30 15:55
把鼠標、觸摸屏、觸控筆統一起來，Pointer Events介紹

閱讀 1581·2019-08-28 18:25
CSS 模塊

閱讀 1857·2019-08-28 18:14
eval()不是魔鬼，只是被誤解了（翻譯）

閱讀 1228·2019-08-26 11:37
你可能不知道的前端小技巧

閱讀 2893·2019-08-26 10:18
js事件循環

閱讀 2419·2019-08-23 18:39

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

搞定PHP面試 - MySQL基礎知識點整理 - 數據類型和數據表管理

相關文章

**搞定PHP面試 - MySQL基礎知識點整理 - 存儲引擎**

發表評論

0條評論

elarity

男|高級講師

TA的文章

iWebFusion：$99/月-Dual E5-2670,128G內存,500G SSD硬盤,20

Capoae惡意軟件使用多個漏洞攻擊Linux系統和Web應用程序

前端實用小工具

把鼠標、觸摸屏、觸控筆統一起來，Pointer Events介紹

CSS 模塊

eval()不是魔鬼，只是被誤解了（翻譯）

你可能不知道的前端小技巧

js事件循環

最新活動