MySQL-資料庫和表的基本操作

2020 年 6 月 21 日
筆記
MySQL

資料庫和表的基本操作

資料庫基礎知識

創建資料庫

CREATE DATABASE 資料庫名稱 ;

查看資料庫（顯示資料庫名列表）

SHOW DATABASES ;

查看某資料庫資訊（顯示創建的資訊）

SHOW CREATE DATABASE 資料庫名稱 ；

修改資料庫編碼

ALTER DATABASE 資料庫名稱 
DEFAULT CHARACTER SET 編碼方式 COLLATE 編碼方式_bin ;

刪除資料庫

DROP DATABASE 資料庫名稱 ;

數據類型

整數類型

數據類型	位元組數	無符號數取值範圍	有符號數取值範圍
TINYINT	1	0~255	-128~127
SAMLLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
BIGINT	5	0~18446744073709551615	-9223372036854775808~9223372036854775807

浮點數類型和定點數類型

單精度浮點數類型FLOAT，4位元組；
雙精度浮點類型DOUBLE，8位元組；
定點數類型DECIMAL(D,B)，M+2位元組，M表示數據的長度，D表示小數點後的長度。

日期與時間類型

數據類型	位元組數	取值範圍	日期格式	零值	補充
YEAR	1	1901~2155	YYYY	0000	字元串格式’0’表示2000，數字格式0表示0000
DATE	4	1000-01-01	YYYY-MM-DD	0000-00-00	使用CURRENT_DATE或者NOW()表示當前系統日期
TIME	3	-838:59:59~838:59:59	HH:MM:SS	00:00:00	可以輸如’D HH:MM:SS’字元串，D表示0~34之間的值，相當於D*24+HH .使用CURRENT_DATE或者NOW()表示當前系統日期
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00	使用NOW()表示當前系統日期
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00	使用CURRENT_TIMESTAMP來輸入當前系統日期；輸入NULL時系統會輸入系統當前日期和時間；無任何輸入時，系統會輸入系統當前日期和時間。

字元串和二進位類型

數據類型	類型說明	補充
CHAR	用於表示固定長度的字元串
VARCHAR	用於表示可變長度的字元串
BINARY	用於表示固定長度的二進位數據
VARBINARY	用於表示固定長度的二進位數據
BLOB	用於表示二進位大數據
TEXT	用於表示大文本數據
ENUM	表示枚舉類型，只能存儲一個枚舉字元串值
SET	表示字元串對象，可以有零或多個值
BIT	表示位欄位類型

數據表的基本操作

選擇資料庫

USE 資料庫名 ;

創建數據表

CREATE TABLE 數據表名
(
欄位名1 數據類型[完整性約束條件] ,
欄位名2 數據類型[完整性約束條件] ,
...
欄位名n 數據類型[完整性約束條件]
) ;

查看此資料庫下的表（顯示錶名列表）

SHOW TABLES ;

查看數據表（顯示定義語句，字元編碼）

SHOW CREATE TABLE 表名 ;

整齊的查看數據表（顯示定義語句，字元編碼）

SHOW CREATE TABLE 表名\G

查看數據表（顯示欄位資訊）

DESCRIBE 表名 ;

或簡寫為：

DESC 表名 ;

執行結果如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(20)     | YES  |     | NULL    |       |
| grade    | float       | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

(1).NULL：表示該列是否可以存儲NULL值；
(2).Key：表示該列是否已經編製索引；
(3).Default：表示該列是否有默認值；
(4).Extra：表示獲取到的與給定列相關的附加資訊。
修改表名（to可以省略）

ALTER TABLE 舊錶名 
RENAME [To] 新表名 ;

修改欄位名

ALTER TABLE 表名 
CHANGE 舊欄位名 新欄位名 新數據類型 ;

修改欄位的數據類型

ALTER TABLE 表名
MODIFY 欄位名 數據類型 ;

添加欄位

ALTER TABLE 表名 
ADD 新欄位名 數據類型
[約束條件][FIRST | AFTER 已存在欄位名]

上述格式中，「FIRST」為可選參數，用於將新加欄位設為第一個欄位，「AFTER」也為可選參數，用於將新加欄位添加到指定「已存在欄位名」的後面。
修改欄位的排列位置
修改為第一個欄位：

ALTER TABLE 表名
MODIFY 欄位名1 數據類型 FIRST;

將欄位名1插入到欄位名2之後：

ALTER TABLE 表名
MODIFY 欄位名1 數據類型 AFTER 欄位名2 ;

刪除欄位

ALTER TABLE 表名
DROP 欄位名 ;

刪除數據表

DROP TABLE 表名 ;

表的約束

約束條件	說明
PRIMARY KEY	主鍵約束，用於唯一標識對應的記錄
FOREIGN KEY	外鍵約束
NOT NULL	非空約束
UNIQUE	唯一性約束
DEFAULT	默認值約束，用於設置欄位的默認值
AUTO_INCREMENT	欄位值自動增加

舉個例子：
id為主鍵且值自動增加，name欄位值唯一，grade欄位默認值為0。

CREATE TABLE TB_GRADE
(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE,
grade FLOAT DEFAULT 0
);

索引

創建表的時候創建索引

CREATE TABLE 表名 
(
欄位名 數據類型[完整性約束條件] ,
欄位名 數據類型[完整性約束條件] ,
...
欄位名 數據類型[完整性約束條件] ,
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
            [ 別名 ] (欄位名1 [ (長度)] [ ASC | DESC] )
) ;

上述相關語法解釋：
（1）UNIQUE：可選參數，表示唯一索引。
（2）FULLTEXT：可選參數，表示全文索引。
（3）SPATIAL：可選參數，表示空間索引。
（4）INDEX和KEY：用來表示欄位的索引，二者選一即可。
（5）別名：可選參數，表示創建的索引名稱。
（6）欄位名1：指定索引對應欄位的名稱（創建多列索引時，用逗號隔開）。
（7）長度：可選參數，用於表示索引的長度。
（8）ASC和DESC：可選參數，ASC代表升序排列，DESC代表降序排列。

在已經存在的表上創建索引

方法一：

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (欄位名1 [ (長度)] [ ASC | DESC] ) ；

方法二：

ALTER TABLE 表名
ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (欄位名1 [ (長度)] [ ASC | DESC] ) ；

刪除索引

方法一：

ALTER TABLE 表名
DROP INDEX 索引名 ;

方法二：

DROP INDEX 索引名
ON 表名 ;

Tags: MySQL