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

数据库

MySQL基础（二）数据库、表的创建及操作

發布時間：2025/3/17 数据库 15 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL基础（二）数据库、表的创建及操作小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

這一部分主要使用SQL中的DDL，數據庫定義語言（data definition language），對數據庫，表進行新建，修改，和刪除。

數據庫（database）

顯示所有數據庫

SHOW DATABASES;

創建數據庫

CREATE DATABASE db_name;

每創建一個數據庫，就會在Data目錄下新建一個以數據庫名字命名（db_name）的文件夾。說明：Data默認在程序的解壓目錄下，比如：C:\ProgramData\MySQL\MySQL Server 5.7\Data，可以通過my.ini配置文件來修改Data目錄的路徑，參考MySQL基礎（一）

創建數據庫時可以指定字符編碼：

CREATE DATABASE db_name CHARACTER SET utf8; -- 注意是utf8，而不是utf-8，否則報錯 CREATE DATABASE db_name CHARSET utf8; -- 也可以連寫；

那么在該數據庫下創建的表，默認都用這個編碼。

查看數據庫創建信息

SHOW CREATE DATABASE db_name;

修改字符編碼

ALTER DATABASE db_name CHARSET utf8;

刪除數據庫

DROP DATABASE db_name;

使用數據庫

USE db_name;

使用數據庫后（進入了Data下的對應數據庫目錄），就可以在該數據庫下進行表操作。沒有退出數據庫一說，都是通過USE在不同數據庫間切換。

表（table）

創建表（CREATE TABLE）

創建表的基本語法語法如下:

CREATE TABLE 表名 (字段名類型(寬度) [可選約束條件]，字段名類型(寬度) [可選約束條件]，字段名類型(寬度) [可選約束條件] )[可選表選項]；

比如創建一張顧客表

CREATE TABLE Customers (cust_id INT PRIMARY KEY AUTO_INCREMENT, -- 定義主鍵，并自增cust_name CHAR(50) NOT NULL, -- 各字段之間以逗號分隔cust_address CHAR(50) NULL,cust_phone CHAR(11) NOT NULL -- 最后一個字段不加逗號 )ENGINE=InnoDB; -- 語句以分號結束

說明

建議表名首字母大寫
創建語句時，縮進用空格，不要用tab鍵
對于整數類型（比如 INT），不需要指定寬度。不同的數字類型具有不同的存值范圍，具體下面會說。
對于字符串類型（比如 CHAR, VARCHAR），需要指定寬度，即可存字符串的最大長度。注意，這里的寬度是按字符來算的，一個中文漢字，一個英文字母，一個數字，都是一個字符（在MySQL4.1之前的老版本中寬度是按字節來算的，而一個中文漢字在utf8編碼中占3個字節）。存值時，如果字符串長度短于寬度，CHAR（定長字符串）類型會在字符串后面補空格，而VARCHAR（可變長字符串）則按實際字符串存儲。
往表中插入數據時，如果數字大小或字符串長度溢出，結果取決于當前Server SQL的模式（關于模式，請咨詢公司DBA，希望詳細了解的參考官網Server SQL Modes）
- 在嚴格模式下會報錯
- 在非嚴格模式下，數據會被截斷存儲：具體來說，如果該字段是數字類型，并且存值范圍是 0 ~ 255，而插入數字為256或更大，那么只會截斷存儲為255；如果該字段是字符串類型，指定寬度是10個字符，那么只會截斷存儲前10個字符

可選約束條件

多個約束條件之間以空格分隔

PRIMARY KEY 主鍵字段，非空且唯一
FOREIGN KEY 外鍵字段
NOT NULL 非空
NULL 允許為空（默認）
DEFAULT 默認值，比如設置默認性別：DEFAULT ‘male’
UNIQUE 字段的值唯一
AUTO_INCREMENT 自增，用于主鍵字段，要求字段類型必須為整數。定義自增后，插入數據可以不指定該字段的值。

可選表選項

多個表選項之間以空格分隔

ENGINE=InnoDB 指定存儲引擎
DEFAULT CHARSET=utf8 設置默認編碼，默認繼承數據庫的編碼，也可以在my.ini配置中設置默認編碼
AUTO_INCREMENT=2 指定自增步長為2

MySQL中的數據類型

整數類型

類型存儲空間最小值最大值

	(Bytes)	(Signed/Unsigned)	(Signed/Unsigned)
TINYINT 小整數	1	-128	127
2的8次冪減一		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT 大整數	4	-2147483648	2147483647
2的32次冪減一		0	4294967295
BIGINT 極大整數	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

注意：默認類型是Signed有符號整數，但是也可以手動指定為Unsigned無有符號整數，比如：INT UNSIGNED

定點數和浮點數

類型存儲空間范圍說明適用

DECIMAL(M,D) 定點數	4	DECIMAL(5,2)：-999.99 ~ 999.99	M表示最大位數，范圍是1~65；D表示小數點后有幾位，范圍0~30，且必須小于M；精度始終準確	價格，工資，身高，體重等
FLOAT 單精度	4		隨著小數的增多，精度變得不準確
DOUBLE雙精度	8		隨著小數的增多，精度比float要高，但也會變得不準確

說明：

關于定點數存儲空間：每9位用4個字節存儲，剩余不足9位的，存儲空間遞減：7-9位使用4Bytes、5-6位使用3Bytes、3-4位使用2Bytes、1-2位使用1Bytes、0位使用0Bytes
對于精確數值計算時需要用到定點數，其能夠存儲精確值的原因在于其內部按照字符串存儲
FLOAT和DOUBLE也支持(M, D)

日期和時間類型

Data Type“Zero” Value

DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR	0000

DATETIME與TIMESTAMP區別：在實際應用的很多場景中，MySQL的這兩種日期類型都能夠滿足我們的需要，存儲精度都為秒，但在某些情況下，會展現出他們各自的優劣：

DATETIME的日期范圍是1001——9999年，TIMESTAMP的時間范圍是1970——2038年。
DATETIME存儲時間與時區無關，TIMESTAMP存儲時間與時區有關，顯示的值也依賴于時區。在mysql服務器，操作系統以及客戶端連接都有時區的設置。
DATETIME使用8字節的存儲空間，TIMESTAMP的存儲空間為4字節。因此，TIMESTAMP比DATETIME的空間利用率更高。
DATETIME的默認值為null；TIMESTAMP的字段默認不為空（not null）,默認值為當前時間（CURRENT_TIMESTAMP），如果不做特殊處理，并且update語句中沒有指定該列的更新值，則默認更新為當前時間。

字符串類型

類型寬度范圍說明特點

CHAR	0-255	定長字符串，存值時，如果字符串長度短于寬度，則在字符串后面補空格；查詢時，結果會自動刪除尾部的空格	速度快，浪費空間
VARCHAR	0-65535	可變長字符串，最大65KB。儲數據的真實內容，不會用空格填充；并且真實數據前有1-2Bytes的前綴，來表示真實數據的bytes字節數	節省空間，速度慢
TEXT	0-65535	多數情況下同VARCHAR。有更多長度選擇 TINYTEXT（255B）, TEXT,MEDIUMTEXT（16MB）, and LONGTEXT(4GB)，	不支持默認值；索引需要指定前綴長度
BINARY		同CHAR，只是存儲內容為二進制/字節字符串
VARBINARY		同VARCHAR，只是存儲內容為二進制/字節字符串
BLOB		字節字符串（byte strings），多數情況下同VARBINARY。更多長度選擇 TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.	不支持默認值；索引需要指定前綴長度

比較常用的就是CHAR 和 VARCHAR。出于性能優化，可以考慮以下幾點：

創建表時，定長的類型往前放（比如性別），變長的往后放（比如地址或描述信息）
過大的數據或者二進制文件不要直接保存到數據庫，比如圖片，視頻等，找一個文件服務器，數據庫中只存該文件的路徑或URL

枚舉和集合類型

枚舉（ENUM）是在給定的范圍內選一個值，而集合（SET）是可以選多個值

-- 枚舉類型 CREATE TABLE Shirts (name CHAR(30),size ENUM('x-small', 'small', 'medium', 'large', 'x-large') ); -- 插入數據時，只能從上述幾個尺寸中選擇 INSERT INTO Shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small'); -- 集合類型 CREATE TABLE Investigation ( name CHAR(30), hobby SET('reading', 'running', 'swimming', 'hiking') );INSERT INTO Investigation (name, hobby) VALUES ('Lena', 'reading, swimming'); -- 如果插入數據不在集合內或拼寫錯誤，會報錯 Data truncated for column 'hobby'... -- 插入結果如下： +------+------------------+ | name | hobby | +------+------------------+ | Lena | reading,swimming | +------+------------------+

查看表

查看所有表

SHOW TABLES;

查看表創建信息

將打印出創建表時使用的語句

SHOW CREATE TABLE Customers;

查看具體表結構信息

修改表（ALTER TABLE）

理想情況下，不要在表中包含數據時對表進行修改。應該在表的設計過程中充分考慮未來可能的需求，避免今后對表的結構做大的改動。使用ALTER TABLE對表進行修改前，應做好表結構和數據的備份。

新增/刪除主鍵

刪除主鍵（DROP）

如果該字段有自增約束（AUTO_INCREMENT），需要先刪除自增約束，才能刪除主鍵。

刪除自增：

-- 取消自增 ALTER TABLE Customers MODIFY cust_id INT PRIMARY KEY NOT NULL; -- ERROR 1068 (42000): Multiple primary key defined -- cust_id 在鍵建表時已經是主鍵，修改時不能再次指定為主鍵，所以報錯ALTER TABLE Vips MODIFY cust_id INT NOT NULL;

刪除主鍵：

ALTER TABLE Customers DROP PRIMARY KEY; -- 可以刪除主鍵或聯合主鍵

新增主鍵（ADD）

ALTER TABLE Customers ADD PRIMARY KEY (cust_id);

新增聯合主鍵（ADD）

主鍵通常定義在一列上，但也可以使用多個列作為主鍵，只要列值的組合能唯一標識表示表中的一行即可。

對已有主鍵增加自增約束

ALTER TABLE Customers MODIFY cust_id INT NOT NULL AUTO_INCREMENT;

增加字段（ADD）

新增字段時，要注意所增加字段的數據類型，并考慮使用NULL和DEFAULT約束

格式：

ALTER TABLE 表名 ADD 新增字段名類型 [可選約束]; -- 新增字段，默認新增至最后 ADD 新增字段名類型 [可選約束] [FIRST]; -- 新增字段放到第一列 ADD 新增字段名類型 [可選約束] [AFTER 字段名]; -- 新增字段放到某列之后

示例：

-- 為 Customers 表增加 cust_email 字段 ALTER TABLE Customers ADD cust_email CHAR(255) NULL;

修改字段類型和約束條件（MODIFY）

-- 修改 Customers 表 cust_email 字段類型為VARCHAR(255) 約束為NOT NULL ALTER TABLE Customers MODIFY cust_email VARCHAR(255) NOT NULL;

修改字段名（CHANGE）

格式：

ALTER TABLE 表名 CHANGE 舊字段名舊字段名類型 [約束條件];

示例：

-- 修改字段名 cust_email 為 cust_memo，約束為 NULL ALTER TABLE Customers CHANGE cust_email cust_memo VARCHAR(255) NULL;

刪除字段（DROP）

ALTER TABLE 表名 DROP 字段名;

修改表字符編碼

格式：

ALTER TABLE 表名 CHARSET 字符編碼；

示例：

-- 修改Customers表字符編碼為 gbk ALTER TABLE Customers CHARSET gbk;

修改完成后，可以通過SHOW CREATE TABLE Customers;語句查看修改結果。

修改表名

-- 修改 Customers 表名為 VipsRENAME TABLE Customers to Vips;

修改完成后可以通過 SHOW TABLES; 語句查看修改結果

修改存儲引擎

-- 修改 Customers 表存儲引擎為 MyISAM ALTER TABLE Customers ENGINE=MyISAM;

刪除表

DROP TABLE 表名；

總結

以上是生活随笔為你收集整理的MySQL基础（二）数据库、表的创建及操作的全部內容，希望文章能夠幫你解決所遇到的問題。

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

上一篇： FTP服务器之pure-ftpd常见问题
下一篇： CentOS 6.4 安装RBTools