基礎知識

在 MySQL 中，一個完整的數據存儲過程總共有 4 步，分別是：創(chuàng)建數據庫、確認字段、創(chuàng)建數據表、插入數據。

要先創(chuàng)建一個數據庫，而不是直接創(chuàng)建數據表：從系統(tǒng)架構的層次上看，MySQL 數據庫系統(tǒng)從大到小依次是：數據庫服務器、數據庫、 數據表、數據表的 行與列 。MySQL 數據庫服務器之前已經安裝。所以，從創(chuàng)建數據庫開始。

標識符命名規(guī)則

• 數據庫名、表名不得超過30個字符，變量名限制為29個；
• 必須只能包含 A–Z, a–z, 0–9, _共63個字符；
• 數據庫名、表名、字段名等對象名中間不要包含空格；
• 同一個MySQL軟件中，數據庫不能同名；同一個庫中，表不能重名；同一個表中，字段不能重名；
• 必須保證你的字段沒有和保留字、數據庫系統(tǒng)或常用方法沖突。如果堅持使用，請在SQL語句中使 用 ' （著重號）引起來；
• 保持字段名和類型的一致性：在命名字段并為其指定數據類型的時候一定要保證一致性，假如數據類型在一個表里是整數，那在另一個表里可就別變成字符型了。

MySQL中的數據類型

類型	說明
整數類型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮點類型	FLOAT、DOUBLE
定點數類型	DECIMAL
位類型	BIT
日期時間類型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串類型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚舉類型	ENUM
集合類型	SET
二進制字符串類型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON類型	JSON對象、JSON數組
空間數據類型	單值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

數據類型	描述
INT	從-2^31 到 2^31-1的整型數據。存儲大小為 4個字節(jié)
CHAR(size)	定長字符數據。若未指定，默認為1個字符，最大長度255
VARCHAR(size)	可變長字符數據，根據字符串實際長度保存，必須指定長度
FLOAT(M,D)	單精度，占用4個字節(jié)，M=整數位+小數位，D=小數位。 D<=M<=255,0<=D<=30，默認M+D<=6
DOUBLE(M,D)	雙精度，占用8個字節(jié)，D<=M<=255,0<=D<=30，默認M+D<=15
DECIMAL(M,D)	高精度小數，占用M+2個字節(jié)，D<=M<=65，0<=D<=30，最大取值范圍與DOUBLE相同。
DATE	日期型數據，格式’YYYY-MM-DD’
BLOB	二進制形式的長文本數據，最大可達4G
TEXT	長文本數據，最大可達4G

創(chuàng)建和管理數據庫

創(chuàng)建數據庫

方式1： 創(chuàng)建數據庫

CREATE DATABASE 數據庫名;

CREATE DATABASE mytest1;
SHOW CREATE DATABASE mytest1;

方式2： 創(chuàng)建數據庫并指定字符集’ ‘

CREATE DATABASE 數據庫名 CHARACTER SET 字符集;

方式3： 判斷數據庫是否已經存在，不存在則創(chuàng)建數據庫（ 推薦 ）

CREATE DATABASE IF NOT EXISTS 數據庫名;

DATABASE 不能改名。一些可視化工具可以改名，它是建新庫，把所有表復制到新庫，再刪舊庫完成的。

使用數據庫

查看當前所有的數據庫
查看當前連接中的數據庫都有哪些

SHOW DATABASES; #有一個S，代表多個數據庫

查看當前正在使用的數據庫

SELECT DATABASE();  #使用的一個 mysql 中的全局函數

查看指定庫下所有的表

SHOW TABLES FROM 數據庫名;

查看數據庫的創(chuàng)建信息

SHOW CREATE DATABASE 數據庫名;
# 或者：
SHOW CREATE DATABASE 數據庫名\G

使用/切換數據庫

USE 數據庫名;

要操作表格和數據之前必須先說明是對哪個數據庫進行操作，否則就要對所有對象加上“數據庫名.”。

修改數據庫

更改數據庫字符集

ALTER DATABASE 數據庫名 CHARACTER SET 字符集;  #比如：gbk、utf8等

刪除數據庫

方式1： 刪除指定的數據庫

DROP DATABASE 數據庫名;

方式2： 刪除指定的數據庫（ 推薦 ）

DROP DATABASE IF EXISTS 數據庫名;

創(chuàng)建表

創(chuàng)建方式1

必須具備：
1.CREATE TABLE權限
2.存儲空間
語法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 數據類型 [約束條件] [默認值],
字段2, 數據類型 [約束條件] [默認值],
字段3, 數據類型 [約束條件] [默認值],
……
[表約束條件]
);

加上了IF NOT EXISTS關鍵字，則表示：如果當前數據庫中不存在要創(chuàng)建的數據表，則創(chuàng)建數據表；如果當前數據庫中已經存在要創(chuàng)建的數據表，則忽略建表語句，不再創(chuàng)建數據表。

必須指定：
1.表名
2.列名(或字段名)，數據類型，長度
可選指定：
1.約束條件
2.默認值

CREATE TABLE IF NOT EXISTS myemp1(   #需要用戶具備創(chuàng)建表的權限。
id INT,
emp_name VARCHAR(15), #使用VARCHAR來定義字符串，必須在使用VARCHAR時指明其長度。
hire_date DATE
);
#查看表結構
DESC myemp1;
#查看創(chuàng)建表的語句結構
SHOW CREATE TABLE myemp1; #如果創(chuàng)建表時沒有指明使用的字符集，則默認使用表所在的數據庫的字符集。
#查看表數據
SELECT * FROM myemp1;

創(chuàng)建方式2

使用 AS subquery 選項，將創(chuàng)建表和插入數據結合起來

指定的列和子查詢中的列要一一對應
通過列名和默認值定義列

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 創(chuàng)建的emp2是空表

CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;DESC myemp2;
DESC employees;SELECT *
FROM myemp2;

查看數據表結構

在MySQL中創(chuàng)建好數據表之后，可以查看數據表的結構。MySQL支持使用 DESCRIBE/DESC 語句查看數據表結構，也支持使用 SHOW CREATE TABLE 語句查看數據表結構。

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE語句不僅可以查看表創(chuàng)建時的詳細語句，還可以查看存儲引擎和字符編碼。

修改表

修改表指的是修改數據庫中已經存在的數據表的結構。

• 使用 ALTER TABLE 語句可以實現：
• 向已有的表中添加列；
• 修改現有表中的列；
• 刪除現有表中的列；
• 重命名現有表中的列。

追加一個列

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段類型 【FIRST|AFTER 字段名】;

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

修改一個列

可以修改列的數據類型，長度、默認值和位置
修改字段數據類型、長度、默認值、位置的語法格式如下：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段類型 【DEFAULT 默認值】【FIRST|AFTER 字段名2】;

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

重命名一個列

使用 CHANGE old_column new_column dataType子句重命名列。語法格式如下：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新數據類型;

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

刪除一個列

刪除表中某個字段的語法格式如下：

ALTER TABLE 表名 DROP [COLUMN] 字段名
SHOW DATABASES;

ALTER TABLE myemp1
DROP COLUMN my_email;

重命名表

方式一： 使用RENAME

RENAME TABLE emp
TO myemp;

方式二：

ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略

刪除表

• 在MySQL中，當一張數據表 沒有與其他任何數據表形成關聯(lián)關系 時，可以將當前數據表直接刪除；
• 數據和結構都被刪除；
• 所有正在運行的相關事務被提交；
• 所有相關索引被刪除；
• 語法格式：

IF EXISTS 的含義為：如果當前數據庫中存在相應的數據表，則刪除數據表；如果當前數據庫中不存在相應的數據表，則忽略刪除語句，不再執(zhí)行刪除數據表的操作。
舉例：DROP TABLE 語句不能回滾。

清空表

TRUNCATE TABLE語句：

• 刪除表中所有的數據；
• 釋放表的存儲空間
  舉例：TRUNCATE TABLE detail_dept;
TRUNCATE語句不能回滾，而使用 DELETE 語句刪除數據，可以回滾

DCL中COMMIT與ROLLBACK的使用

COMMIT：提交數據。一旦執(zhí)行COMMIT，則數據就被永久的保存在了數據庫中，意味著數據不可以回滾。
ROLLBACK：回滾數據。一旦執(zhí)行ROLLBACK,則可以實現數據的回滾。回滾到最近的一次COMMIT之后。

DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務日志資源少，但 TRUNCATE 無事務且不觸發(fā) TRIGGER，有可能造成事故，故不建議在開發(fā)代碼中使用此語句。
說明：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。

DDL 和 DML 的說明

① DDL的操作一旦執(zhí)行，就不可回滾。指令SET autocommit = FALSE對 DDL 操作失效。(因為在執(zhí)行完 DDL 操作之后，一定會執(zhí)行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影響的。)
② DML的操作默認情況，一旦執(zhí)行，也是不可回滾的。但是，如果在執(zhí)行DML之前，執(zhí)行了 SET autocommit = FALSE，則執(zhí)行的 DML 操作就可以實現回滾。

拓展

表刪除 操作將把表的定義和表中的數據一起刪除，并且MySQL在執(zhí)行刪除操作時，不會有任何的確認信息提示，因此執(zhí)行刪除操時應當慎重。在刪除表前，最好對表中的數據進行 備份 ，這樣當操作失誤時可以對數據進行恢復，以免造成無法挽回的后果。
同樣的，在使用 ALTER TABLE 進行表的基本修改操作時，在執(zhí)行操作過程之前，也應該確保對數據進行完整的 備份 ，因為數據庫的改變是 無法撤銷 的，如果添加了一個不需要的字段，可以將其刪除；相同的，如果刪除了一個需要的列，該列下面的所有數據都將會丟失。

新特性

在MySQL 8.0版本中，InnoDB表的DDL支持事務完整性，即 DDL操作要么成功要么回滾 。DDL操作回滾日志寫入到data dictionary數據字典表mysql.innodb_ddl_log（該表是隱藏的表，通過show tables無法看到）中，用于回滾操作。通過設置參數，可將DDL操作日志打印輸出到MySQL錯誤日志中。

數據庫學習視頻：
【MySQL數據庫入門到大牛，mysql安裝到優(yōu)化，百科全書級，全網天花板】