DDL數據庫與表怎么創建及管理

今天小編給大家分享一下DDL數據庫與表怎么創建及管理的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

一、基本概念

從系統架構的層面來看，數據庫從大到小依次是數據庫服務器(上面安裝了DBMS和數據庫)、數據庫(也稱database或者schema)、數據表、數據表的行與列。

二、創建和管理數據庫

1、創建數據庫

• 直接創建：CREATE DATABASE 數據庫名;(使用默認的字符集)

• 創建時指明字符集：CREATE DATABASE 數據庫名 CHARACTER SET 'gbk';

• 創建時判斷數據庫是否已存在，如果不存在則創建：CREATE DATABASE IF NOT EXISTS 數據庫名 CHARACTER SET 'utf8';，推薦使用此種方式

2、管理數據庫

下面是常見的數據庫管理SQL

# 查看當前連接中都有哪些數據庫
SHOW DATABASES;
# 指定使用哪個數據庫或者切換數據庫
USE 數據庫名;
# 查看當前數據庫中保存的數據表
SHOW TABLES;
# 查看當時使用的數據庫
SELECT DATABASE() FROM DUAL;
# 查看指定數據庫下存在哪些表
SHOW TABLES FROM 數據庫名;

3、修改數據庫

一般情況下不建議做此操作，多發生在數據庫剛創建出來時

數據庫名一般不可以修改，某些可視化工具可以修改數據庫名，實際上它是新創建了一個數據庫，然后把舊數據庫的數據復制過去，再刪除舊庫

# 修改數據庫字符集
ALTER DATABASE 數據庫名 CHARACTER SET 字符集;

4、刪除數據庫

• 直接刪除：DROP DATABASE 數據庫名;

• 刪除前先判斷，如果存在則刪除：DROP DATABASE IF EXISTS 數據庫名;，推薦使用此方式

三、創建和管理表

1、創建表

1）常見數據類型

整數類型：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

2）創建表

方式一：從零開始創建

# 使用VARCHAR定義字段必須指明其長度，如果創建表時沒有指明字符集，那么使用所在數據庫默認的字符集
CREATE TABLE IF NOT EXISTS 表名(
id INT [約束條件] [默認值],
emp_name VARCHAR(15) [約束條件] [默認值],
birth DATE [約束條件] [默認值]
) ENGINE=InnoDB DEFAULT CHARSET = utf8mb3;
# 查看表結構
DESC 表名;
# 查看建表語句
SHOW CREATE TABLE 表名;

方式二：基于現有的表創建新表

新表的數據、結構都與SELECT語句的結果集相同

如果SELECT語句使用了別名，那么新表中的字段就使用別名

CREATE TABLE 新表名
AS
SELECT id,emp_name,birth
FROM t_decade_employee;

建表完成之后，不管是新表還是舊表發生變化，他們都不會互相影響

2、修改表

1）追加一個字段

語法格式為

ALTER TABLE 表名 ADD 字段名 字段類型 [FIRST|AFTER 字段名];

我們追加如下四個字段，看看FIRST、AFTER關鍵字的效果

ALTER TABLE t_decade_user ADD hobby VARCHAR(10);
ALTER TABLE t_decade_user ADD company_name VARCHAR(10) FIRST;
ALTER TABLE t_decade_user ADD salary VARCHAR(10) AFTER sex;
ALTER TABLE t_decade_user ADD address VARCHAR(10) AFTER name;

執行后，結果如下

2）修改字段的長度、數據類型、默認值

語法格式為

ALTER TABLE 表名 MODIFY 字段類型 [DEFAULT 默認值];

例如修改上面新加的兩個字段的長度和默認值，數據類型一般確定后就不會改變了

ALTER TABLE t_decade_user MODIFY salary VARCHAR(15);
ALTER TABLE t_decade_user MODIFY address VARCHAR(25) DEFAULT 'CHINA';
DESC t_decade_user;

3）重命名一個字段

語法格式為

ALTER TABLE 表名
CHANGE 舊字段名 新字段名 字段類型;

我們這里測試將薪酬salary的名字和字段類型進行調整

ALTER TABLE t_decade_user CHANGE salary month_salary DOUBLE(10,2);
DESC t_decade_user;

結果如下

4）刪除一個字段

語法格式為

ALTER TABLE 表名DROP COLUMN 要刪除的字段;

現在我們將剛剛添加的那些字段全部刪除

ALTER TABLE t_decade_user DROP COLUMN company_name;
ALTER TABLE t_decade_user DROP COLUMN month_salary;
ALTER TABLE t_decade_user DROP COLUMN hobby;
ALTER TABLE t_decade_user DROP COLUMN address;
DESC t_decade_user;

結果如下

3、重命名表

方式一：RENAME

RENAME TABLE 舊的表名 TO 新的表名;

方式二：ALTER，這里的TO可以省略

ALTER TABLE 舊的表名 RENAME [TO] 新的表名;

4、刪除表

刪除表結構的同時，也會刪除表中數據，釋放表的存儲空間

DROP TABLE [IF EXISTS] 表名1,表名2...

注意：刪除表之后，無法回滾，除非有備份等操作

5、清空表

清空表只會清空數據，表的結構會保存下來

TRUNCATE TABLE 表名

刪除表中所有數據

釋放表的存儲空間

對比TRUNCATE TABLE和DELETE FROM對比

• 相同點：都可以實現對表中所有數據的刪除，同時保留表結構

• 不同點：TRUNCATE 語句刪除數據無法回滾，而使用DELETE語句刪除數據可以回滾

DDL和DML的區別

• DDL操作一旦執行，就不可回滾，因為它不受SET autocommit = FALSE的影響

• DML的操作一旦執行，默認情況下不可回滾，但是，如果在執行DML之前，執行了SET autocommit = FALSE，執行的DML操作就可以回滾

我們拿某個表執行一次DELETE FROM 和 TRUNCATE TABLE進行比較

COMMIT;
SELECT * FROM t_decade_user;
SET autocommit = false;
# 如果執行TRUNCATE TABLE t_decade_user;那么數據就無法回滾到最近一次commit
DELETE FROM t_decade_user;
SELECT * FROM t_decade_user;
ROLLBACK;
SELECT * FROM t_decade_user;

發現只有DELETE FROM可以回滾

四、DCL中的COMMIT和ROLLBACK

1、commit

commit指提交數據，一旦執行commit，則數據就被永久的保存在數據庫中，意味著數據不可以回滾

2、rollback

rollback指回滾數據，一旦執行rollback，就可以實現數據的回滾，回滾到最近一次commit之后的狀態

五、MySQL8.0中DDL的原子化

MySQL8.0在DDL方面新增了一個原子化的新特性：DDL操作要么成功要么回滾

假設數據庫中只存在一個表t_decade_user

我們同時在MySQL5.7和MySQL8.0中執行DROP語句

DROP TABLE t_decade_user,t_decade_employee;

它們都會報錯，但是MySQL5.7會成功刪除掉t_decade_user

而MySQL8.0不會影響到t_decade_user

以上就是“DDL數據庫與表怎么創建及管理”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。