DDL數(shù)據(jù)庫與表怎么創(chuàng)建及管理

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

一、基本概念

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

二、創(chuàng)建和管理數(shù)據(jù)庫

1、創(chuàng)建數(shù)據(jù)庫

• 直接創(chuàng)建：CREATE DATABASE 數(shù)據(jù)庫名;(使用默認(rèn)的字符集)

• 創(chuàng)建時指明字符集：CREATE DATABASE 數(shù)據(jù)庫名 CHARACTER SET 'gbk';

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

2、管理數(shù)據(jù)庫

下面是常見的數(shù)據(jù)庫管理SQL

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

3、修改數(shù)據(jù)庫

一般情況下不建議做此操作，多發(fā)生在數(shù)據(jù)庫剛創(chuàng)建出來時

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

# 修改數(shù)據(jù)庫字符集
ALTER DATABASE 數(shù)據(jù)庫名 CHARACTER SET 字符集;

4、刪除數(shù)據(jù)庫

• 直接刪除：DROP DATABASE 數(shù)據(jù)庫名;

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

三、創(chuàng)建和管理表

1、創(chuàng)建表

1）常見數(shù)據(jù)類型

整數(shù)類型：TINYINT、SMALLINT、MEDIUMINT、INT(或者INTEGER)、BIGINT

浮點類型：FLOAT、DOUBLE

定點數(shù)類型：DECIMAL

位類型：BIT

日期時間類型：YEAR、TIME、DATE、DATETIME、TIMESTAMP

文本字符串類型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

枚舉類型：ENUM

集合類型：SET

二進(jìn)制字符串類型：BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

JSON類型：JSON對象、JSON數(shù)組

空間數(shù)據(jù)類型

• 單值：GEOMETRY、POINT、LINESTRING、POLYGON

• 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

2）創(chuàng)建表

方式一：從零開始創(chuàng)建

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

方式二：基于現(xiàn)有的表創(chuàng)建新表

新表的數(shù)據(jù)、結(jié)構(gòu)都與SELECT語句的結(jié)果集相同

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

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

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

2、修改表

1）追加一個字段

語法格式為

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

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

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;

執(zhí)行后，結(jié)果如下

2）修改字段的長度、數(shù)據(jù)類型、默認(rèn)值

語法格式為

ALTER TABLE 表名 MODIFY 字段類型 [DEFAULT 默認(rèn)值];

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

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的名字和字段類型進(jìn)行調(diào)整

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

結(jié)果如下

4）刪除一個字段

語法格式為

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

現(xiàn)在我們將剛剛添加的那些字段全部刪除

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;

結(jié)果如下

3、重命名表

方式一：RENAME

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

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

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

4、刪除表

刪除表結(jié)構(gòu)的同時，也會刪除表中數(shù)據(jù)，釋放表的存儲空間

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

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

5、清空表

清空表只會清空數(shù)據(jù)，表的結(jié)構(gòu)會保存下來

TRUNCATE TABLE 表名

刪除表中所有數(shù)據(jù)

釋放表的存儲空間

對比TRUNCATE TABLE和DELETE FROM對比

• 相同點：都可以實現(xiàn)對表中所有數(shù)據(jù)的刪除，同時保留表結(jié)構(gòu)

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

DDL和DML的區(qū)別

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

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

我們拿某個表執(zhí)行一次DELETE FROM 和 TRUNCATE TABLE進(jìn)行比較

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

發(fā)現(xiàn)只有DELETE FROM可以回滾

四、DCL中的COMMIT和ROLLBACK

1、commit

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

2、rollback

rollback指回滾數(shù)據(jù)，一旦執(zhí)行rollback，就可以實現(xiàn)數(shù)據(jù)的回滾，回滾到最近一次commit之后的狀態(tài)

五、MySQL8.0中DDL的原子化

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

假設(shè)數(shù)據(jù)庫中只存在一個表t_decade_user

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

DROP TABLE t_decade_user,t_decade_employee;

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

而MySQL8.0不會影響到t_decade_user

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